Hi, I have a table with Seq_No, City, and Customers' Disposition. CREATE TABLE CUSTOMER_DATA( Seq_No bigint IDENTITY(1,1) NOT NULL PRIMARY KEY, City varchar(10), Disposition varchar(10) ) INSERT INTO CUSTOMER_DATA VALUES('DEL','BUSY') INSERT INTO CUSTOMER_DATA VALUES('MUM','FREE')...more >>

ID MATH BIO CHEM ENG 1 8 8 8 8 2 7 7 7 7 I want to convert into : SUBJECT VALUE ID MATH 8 1 BIO 8 1 CHEM 8 1 ENG 8 1 MATH 7 2 BIO 7 2 CHEM 7 ...more >>

I have the following columns and data in my table: Col A Col B Col C A1 B1 C1 A1 B2 C2 A1 B3 C3 A2 B1 C4 A2 B2 C5 A3 B3 C6 I would like a query to return my data as follows Col A ...more >>

I can use the following successfully: SELECT OBJECT_ID('myDB..myTable') but on a linked server I cannot get this working. Is this the right syntax? Should it work? The server name is box. This gives me a syntax error near myDB. Select * From Openquery(box, 'SELECT OBJECT_ID('myDB..myT...more >>

i have two queries but not sure which one would perform faster, and why. first: select id from students where arrivaldate(year,arrival) = 2000 second: select id from students where arrivaldate > '31/12/1999' and arrivaldate < '1/1/2001' Thank you for your help. ...more >>

hi all how can I Declare Variable As Table Like Cursor DECLARE parameter_cursor CURSOR FOR SELECT top 1 c.name, t.name FROM sysobjects o JOIN syscolumns c on c.id = o.id JOIN systypes t on c.xtype = t.xtype WHERE o.name like 'Test' order by o.name, c.colid Tanks ...more >>

This is very simple... but I can't get it. I have a table which logs every operation of every user. ID_OPERATION OPERATION USER DATETIME A simple example is: 1 LOGIN jack 14/6/2005 23:34:00 2 LOGIN jim 14/6/2005 21:34:00 3 LOGIN billy 14/6/2005 20:34:00 4 ...more >>

Hi, I have an in-house Windows application, utilizing SQL Server Express 2005 (over OLEDB), which needs to be upgraded to a multi-user paradigm. The solution will require collecting the same kind of data from multiple users in one central, shared database. Due to a sensitive nature of the d...more >>

Hi, I have a page (that I cannot change) that enters currency without a decimal place i.e.. user enters 200 which equals £2.00. I need the SQL db to see these figures and assume that 200 = 2.00 or 1080 = 10.80 etc and add the decimal point. Anyone help? Thanks in advance Simon ...more >>

EXEC sp_addlinkedserver @server='myServer', @srvproduct='', @provider='SQLOLEDB', @datasrc='myServer', @provstr='DRIVER={SQL Server};SERVER=myServer;UID=sa;PWD=myPassword' I am trying to simply add a linked SQL server that ...more >>

Hi All, I have a question that might be a little long, but if anyone can take it... I've encountered a sql server database recently that attempts to recreate an object oriented style of programming within SQL. So functions are overloaded by selecting the next function with a couple of extr...more >>

Hello, I have a table with a primary key having IDENTITY (ID). In case of an INSERT I want to fill another Column C1 with the new Identity value. How can I achive this? By a Trigger maybe? Thank you very much Joachim...more >>

The following query runs on Access and returns a result table that removes duplicate account numbers even if they have different names. SELECT AcctNum, First(t.AccountName) AS AccountName FROM tblAcctChart t GROUP BY AcctNum ORDER BY AcctNum; 101 N1 101 N2 101 N3 results in 101 ...more >>

How does one do the following considering that an identical table in Database2 also exists in Database1? INSERT INTO Database1.Table1 SELECT * FROM Database2.Table2 WHERE rows in table2 do not exist in table1 I don't have DDL as I need a general solution for moving data. ...more >>

I have a temp table like this: TableName: #TempProduct PartNumber Column1 Column2 Column3 Column4 ...... AP-321-00 NULL 0 23 NULL ...... AP-321-01 NULL 5 21 ...more >>

Hoping you can help me here.... I have a situation where I need to provide a script that can be run through query analyzer to return the number of records in each table in my DB. I would like it to be dynamic, as the number of tables in the DB changes. Pretty simple query (Select count(...more >>

If I update a record in a table that is joined to another table that holds a number of related records, which record will SQL pick to update from? Some DDL: CREATE TABLE Lookup ( Accnum INT, Value INT ) CREATE TABLE Warehouse ( Accnum INT, Value INT ) INSERT INTO Lookup VALUES (1...more >>

SQL 2005. and SQL Management studio I have a user 'myuser' in a SQL DB 'mydatabase' under security users. When I try to delete him, I get error says DB principal owns schema and cant be dropped. I try to remove his role as DBOwner and it says login name must be specified. (The window above has...more >>

Hi, I need to know the ID of the last entry so that I can use it in a trigger. How do I get the ID of the last INSERT'ed record? Do I use Scope_Identity() for that? -- Thanks, Sam...more >>

Hi, I'm confused about how the backup system works in SQL Server. I'm using the BULK LOGGED recovery model, I do regular full backups but my log file is growing very large. BOL says: Full database backup, which backs up the entire database including the transaction log and it also ...more >>

I have a table of dates and a second column for bit. I need to know the date of X business days ago declare @dateIn datetime, @days int set @datein = '5-17-2006' set @days=5 select A.bdate From BusinessCALendar as A Inner Join BusinessCALendar as B on A.bdate <= B.bdate and...more >>

I have 2 different servers, and I wish to join tables from 2 different databases on 2 different servers. I looked into linked servers a little bit. Is this the technology to use? I am connected via a VPN with both servers. Any slick way to do a join like this? Derek ...more >>

I have two tables, Admission and RecordSummary. The Admission table holds one row for each resident at our facility and the RecordSummary table holds one row for each admission the resident has (could be more than 1). They are linked using a Register Number, which is a 6 digit number. The ...more >>

I'm working on an old system (win2k & SQLServer2K) writing an import routine to periodically process datafiles from even older systems. As part of the process, I want to execute sp_columns on a table to retrieve the column names. But, during the execution of the vbs shell script (written in ...more >>

Hi all, I'm new to this so bare with me. I am using ms sql 2000. I am inserting into tblTest3 4 columns, three of the inserts are done with the INSERT statement as part of a stored procedure, the other insert takes place in a trigger that I need to fire as each Row is inserted. The reason for ...more >>

Hello All, I am writing a SQL Stored Proc that will query some information from Active Directory. I have setup the linked server and I can get the OpenQuery to get the information that I require. However, when when I try to run a query that where the ADSI query generates an error I canno...more >>

I have a question about GUIDs. I am in no way a sql expert, but at our company, our DBA has said we need to use GUIDs for replication. Sometimes we have stand alone databases that we create that we need to resync back to our server. But I read a discussion that seemed to frown on using GUIDs...more >>

Hello, From a Visual Basic program how would I turn on IDENTITY_INSERT? Are there any warnings in doing this? What is the right time to do it? How often should I do it? When should I turn it off? Discussion started in microsoft.public.dotnet.languages.vb: http://groups.google.com/grou...more >>

I have a database that could have multiple entries per Site. What I need is the last record added to the database. I'm guessing Group By and Distincts are needed somewhere, but I'm not familiar enough with them and don't have any wizards to help build it--I'm open to wizards, if anyone can rec...more >>

Hi, ive been in msdn and doing some tutorials about reporting services, and im a little confused. All i wanted was a way to send to the client email the data from a table in excel format, every friday at six, all automated without having to export manually. I think its possible by this report...more >>

I have a string like this select @feed5 = @name+', '+@work_name +', '+isnull( @workedyearfrom, '')+' '+ isnull(@workedyearto, '')+', '+ isnull(@height, '')+'x'+isnull(@width, '')+'x'+isnull(@depth, '')+' '+ @measuretype+' '+'Editions: '+' ' +@edition+.+ Here some of the values will be n...more >>

I have stored procedure that is timing when ran in a application. When is executed in query analyzer, it takes 43 seconds to complete but if I run just the script it only takes 2 or 3 seconds to complete. We ran a trace and cannot determine where exactly is the problem within the procedure ...more >>

Hi, I executed this query: DECLARE @S1 TABLE ( [Distribution Center] VARCHAR(8000)) INSERT @S1 SELECT [Distribution Center] FROM [my_VIEW] GROUP BY [Distribution Center] DECLARE @S2 TABLE ( [Sub Category] VARCHAR(8000)) INSERT @S2 SELECT [Sub Category] FROM [my_...more >>

We are looking at moving most of our class libraries (dlls) that are used by our applications (web,web services and console apps) to stored procedures. We feel like it may be better that trying to keep track of whether the app has the appropriate DLL associated with it and to make deployment ...more >>

hi people, my query goes something like this... select a.*,b.*,c.* from table1 a ,table2 b,table3 c where a.col1 = b.col2 and a.col1 = c.col4 for xml auto, elements,base binary64 everything works fine except tht the elements of tables table2,table3 are being added as child/nested nodes....more >>

We have an app that use ODBC and works on SQL server 2000 but when running the same app and same ODBC driver on 2005 sp 1 we get Invalid Descriptor Index Why? Regards, David ...more >>

hi all, have some TSQL question here, hope can get advise fr here ..currently i have 2 tables, and i want the output to be in 1 set: table A grp name subgroup Grp A Jan 2005 Grp A Feb 2005 Grp A Mar 2005 Grp B ...more >>

I like to print the values of all input parameters dynamically... I get an error: "Must declare the variable '@PARAMETER1'." Is it possible to run sql with variable variable-names??? Here is my test-code CREATE PROCEDURE Test ( @PARAMETER1 char(8), @PARAMETER2 char(4) ) as begin ...more >>

Hi, I need to trace where in a program I am working on it read a particular column from a SQL database. Is there a way to use Profiler to do this. IE. If a table (Members) contains ID, Forename, Surname, Age, Sex, Email Can I use profiler to show me each call that includes a SELECT Sex F...more >>

When one of our clients tries to attach a database (about 6GB in size) she gets a message saying file size limit exceeded. She is using Sql Server 2000. Is there a limitation on size for Sql Server 2000? I know for MSDE the size can not be more than 2GB. Can you shed any light on the issue p...more >>

Hello, I could use some help with the following problem: I'm trying to select the following: select max(counter), name, date from table1 group by name, date this works fine, except for the fact that I would like to see the maximum value of the counter and the day that it is reached. T...more >>

I have been trying to expand on a query given to me to insert records from one SQL2000 server table to a SQL2005 Server table. The query works like a dream, however i would like to expand it slightly to become an UPDATE query Orig Query INSERT INTO BossData.dbo.BacsHdrYearly se...more >>

How can i write a Query to find out how many weeks are there in a month, I tried Datediff(wk, startofMonth ,EndOfMont) this is not giving correct output for example : Feb 1998 = 4weeks july 2006 = 6 weeks lara ...more >>

I have a table where one of the field is having formula, where UDF is used. Is there any way by which formula can be removed and any other method is used like trigger, Because we can not have index on a formula based field ...more >>

Hi all, I have a situation that I need some help with as soon as possible. If you’re good at logic puzzles then please help as soon as you have a minute :-) As some of you know I have a situation in which I need to copy data from a table in QuickRes, to an identical table in a new databa...more >>

Hello I want to execute a Sql Transaction. But When I use this command(sp_executesql) , I have to use NVarchar variable that has got a limit that 4000 characters. I need more than 4000. How can i execute my transaction, using sp_executesql. Thanks very much. ...more >>

I am using ADO to execute a DROP DATABASE command. Before that I run some query against the DB to do some checks. However I get the meesage "Cannot drop the database XXX because it is currently in use". If I do not run the query before the DROP works. I have made sure that all connections to t...more >>

Hello all, I started to develop my own data flow component with custom UI. SSIS is great technology, but i have a little bit problem with debuging of this. For debuging in design time, is recomended to have opened two instances of VS2005. First instance is the DLL project of my PipelineCom...more >>

Can anyone offer me a solution to this , i have a table that hold QI AN Quantity Price Order Refer Area 28 96229392 15 83.98 1 A1 Level 1 28 960004877 55 192.68 2 ...more >>

How do I know the percentage done or left when command like DBCC REINDEX or ALTER TABLE on a huge table? Thanks in advance. ...more >>