sql

The user is not associated with a trusted SQL Server connection.

i wrote the code like this to connect sqlserver database.i load the driverclass,but iam not getting connection. Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver"); System.out.println("class Loaded"); connection = DriverManager.getConnection("jdbc:sqlserver://10.171.160.114:2001","XXXXXX","XXXXXX"); ...

Foreignkey constraint may cause cycles or multiple cascade paths?

I have a problem when i try to add constraints to my tables i get the error: Introducing FOREIGN KEY constraint 'FK74988DB24B3C886' on table 'Employee' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints. My constraint is between a Code table and an empl...

How to use Union method or left outer join?

i can not join #Temp with scr_SecuristLog. How can i do it? CREATE TABLE #Temp (VisitingCount int, [Time] int ) DECLARE @DateNow DATETIME,@i int,@Time int set @DateNow='00:00' set @i=1; while(@i<48) begin set @DateNow = DATEADD(minute, 30, @DateNow) set @Time = (datepart(hour,@DateNow)*60+datepart(minute,@D...

Count distinct and Null value is eliminated by an aggregate

I'm using SQL Server 2005. With the query below (simplified from my real query): select a,count(distinct b),sum(a) from (select 1 a,1 b union all select 2,2 union all select 2,null union all select 3,3 union all select 3,null union all select 3,null) a group by a Is there any way to do a count distinct without getting "Warning: Null...

How to generate this sql query?

"0" is VisitingCount ---Numeric Value Datepart, For example: 00:00--1, 00:30--2, 01:00--2, 01:30--3 CREATE TABLE #Temp (VisitingCount int, [Time] int ) DECLARE @DateNow DATETIME,@i int,@Time int set @DateNow='00:00' set @i=1; while(@i<48) begin set @DateNow = DATEADD(minute, 30, @DateNow) set @Time = (datepart...

What indexes optimize this query with four joins?

I have an sql query with inner joins of four tables that takes more than 30 seconds with the current indexes and query structure. I would like to make it as fast as possible; at least faster than 5 seconds. I first thought about denormalizing, but read here that generally it should be possible to optimize via correct indexes etc. I cann...

What is the optimal indexing strategy for a relation table?

A relation table is the common solution to representing a many-to-many (m:n) relationship. In the simplest form, it combines foreign keys referencing the two relating tables to a new composite primary key: A AtoB B ---- ---- ---- *id *Aid *id data *Bid data How should it be indexed to provide opti...

Are LEFT JOIN subquery table arguments evaluated more than once?

Hello. I have a query that looks like this: SELECT * FROM employees e LEFT JOIN ( SELECT * FROM timereports WHERE date = '2009-05-04' ) t ON e.id = t.employee_id As you can see, my LEFT JOIN second table parameter is generated by a a subquery. Does the db evaluate this subquery o...

whats the difference between a stored procedure and a table valued function?

whats the difference between a stored procedure and a table valued function? they seem to serve the same functions ...

SQL Creating a stored proc for returning age bands

I have a table called Person that contain a field called PersonAge. I need to group the ages by age bands ie '12 and under', '13-17', '18-25', '25 and over' and return this resultset using a stored procedure. Ideally I need to get returned 2 fields , 'Age Band', 'Total' like so Age band Total 12 and under 5 13 - 17 ...

How can evaluate more than once data from a query?

CREATE TABLE #Temp (VisitingCount int, [Time] int ) DECLARE @DateNow DATETIME,@i int,@Time int set @DateNow='00:00' set @i=1; while(@i<48) begin set @DateNow = DATEADD(minute, 30, @DateNow) set @Time = (datepart(hour,@DateNow)*60+datepart(minute,@DateNow))/30 insert into #Temp(VisitingCount,[Time]) va...

persons where the children are grouped for their parent

I have an SQLite database with a simple table of Persons. Each row represents a person and has four columns: the primary key (id), one for the name, one for the age and one for the parent (points to the primkey id, is NULL if the person has no parent). Now, I just want to list all those persons sorted on age and have the children group...

Sum a subquery and group by customer info

I have three tables something like the following: Customer (CustomerID, AddressState) Account (AccountID, CustomerID, OpenedDate) Payment (AccountID, Amount) The Payment table can contain multiple payments for an Account and a Customer can have multiple accounts. What I would like to do is retrieve the total amount of all payments on...

Queue SQL implementation with a single query.

I have an SQL table that looks like: TABLE QUEUE ID DATA POSITION Field POSITION holds the potition of the record relative to a Queue. So, it can be: 0 not in any position (not queued). 1 first in the queue (next in line). > 1 the position in the queue. I need a (MS) SQL query that will move the queue up one position...

SQL Server - Storing Sensitive Data

What is a secure way of storing an a username and password (not asp.net membership details) within a database table that needs to be pulled out and decrypted to use for passing to a webservice. Each way I think about the problem I find security holes as the username and password need to be in plain text before being passed to the webse...

Is this execution plan a motivation for re thinking my primary keys

When I entered my current (employer's) company a new database schema was designed and will be the base of a lot of the future tools that are/will be created. With my limited SQL knowledge I think the table is rather well designed. My only concern is that almost every table has a multy-part primary key. Every table has at least a Customer...

How to solve Conversion failed when converting the nvarchar value?

CREATE TABLE #Temp (VisitingCount int, [Time] int, [Date] nvarchar(50) ) DECLARE @DateNow DATETIME,@i int,@Time int, @Date nvarchar(50) set @DateNow='00:00' set @i=1; while(@i^60;48) begin set @DateNow = DATEADD(minute, 30, @DateNow) set @Time = (datepart(hour,@DateNow)*60+datepart(minute,@DateNow))/30 s...

Does the number of columns returned affect the speed of a query?

If I have two queries SELECT Id, Forename, Surname FROM Person WHERE PersonName Like(‘%frank%’) And SELECT * FROM Person WHERE PersonName Like(‘%frank%’) Which query will run faster? Is the where clause / table joining the biggest factor, or the number of columns returned? I’m asking because I’m building a series of objects that m...

How can i evaluate this table?

i need second table. CREATE TABLE #Temp (VisitingCount int, [Time] int, [Date] nvarchar(50) ) DECLARE @DateNow DATETIME,@i int,@Time int, @Date nvarchar(50) set @DateNow='00:00' set @i=1; while(@i<48) begin set @DateNow = DATEADD(minute, 30, @DateNow) set @Time = (datepart(hour,@DateNow)*60+datepart(minute,@...

how to manage sql structure updates

How do people recommend managing database structure updates? e.g. new features are added that require existing tables being altered, new ones added etc. Is there a good piece of software or is it a case of writing ALTER type statements. ...