Hi- I am sending several queries simultaneously to SS; I declare a variable, and do several conditional selects to set the value of the variable. At the end, I select the value of the variable as the return value of the complete transaction. But, the query ends up returning multiple recordsets...more >>

I am careless to 'delete from myTable' any other method to recover the data ??? thanks a lot.. ...more >>

Hi all, I need to search for all the procs that have hard code database name and need some recommendation from you. Example if I create the following sp in DatabaseA and need to find all the procs that reference DatabaseB. Does anyone has some sort script to do that or know any tools that...more >>

Hi, I am having a table with a timestamp column. The data in this table is assumed to accumulate over a period of time. On an avarage every second a row is inserted into this table. I using time stamp column for two reasons: - Ensure that the value of timestamp is ever incrementing. ...more >>

Hi, My application creates 4-5 SQL Server jobs on the production server. I just want to know what is the impact of creating larger number of jobs on production server. Will it hamper it performance, except that SQL Server Agent service should be running? Thanks in advance, Pushkar ...more >>

Hello Is data fields of varchar type internally encoded as UTF-8? How is cyrillic text stored in varchar data fileds, as UTF-8 or not? ...more >>

I am getting inconsistent responses from SQL Server 2000 and do not know why. I populate the controls on an unbound form by executing a command object that calls a sproc that returns a recordset. On SQL Server 2000 the srpoc returns the recordset used to fill the form's controls plus an out...more >>

Is there a way to determine what filegroup a table belongs to by looking at the entry in the SysObjects or another system table? I found it easily enough for indexes, but can't seem to find it for Table entries. TIA, -Steve- ...more >>

SELECT DISTINCT [column1] AS A, COUNT([column2]) AS B FROM table1 WHERE [column2] <> '' GROUP BY [column2] ORDER BY COUNT([column2]) DESC union SELECT 'total' AS A, COUNT(*) AS B FROM table1 error??????????...more >>

Hello all, The query below, has been succesfully working in sql 2000 for months. While I recongnize that the isnumeric attribute is reference twice (this has since been corrected). My concern is why did this generate an error in sql 2005 and not sql 2000. Error = (Duplicate column names ar...more >>

I have a stored procedure that is something like this : SELECT TableA.*, value = dbo.functionA(TableA.id, x,x) FROM TableA WHERE TableA.id = 'SomeValue' AND dbo.functionA(TableA.key, x,x) > 0 This procedure is executed over 1000 times in a hour, and the function calls in the proc is calle...more >>

Hello all, I need some sort of a solution to sum up a DateTime field. I want to retrieve the total number of hours and minutes. Obviously, if Hours is of type DateTime, this won't work: SELECT SUM(Hours) FROM Activity Any ideas? Thanks! ...more >>

i'm creating the tables to hold in the database copies of information that are sent electronically to the Canadian government (www.fintrac.gc.ca if you're interested). This information is sent in the form of flat text files, with fixed field lengths for values. An example of some are: ...more >>

How do I test for the existence of a temporary stored procedure (or get its id)? Testing for existence of a regular stored procedure is easy: IF EXISTS (SELECT 1 FROM dbo.sysobjects WHERE name = 'sp' and id = ... and objectproperty( ... isprocedure) ...) Testing for existence of a tempora...more >>

select top 3 iUserID , sFirstName , sLastName from tblUser UNION select -9999 , 'Fake', 'User' Hi all: I realize that this is strictly speaking not an ASP.NET question, but since most of you people out there SQL gurus as well, I hope you will bear with me on this occasion: I wou...more >>

Earlier rowterminator for the text file was '\r\n', I was albe to bulk insert with rowterminator Now it is changed to '\n', When I use the following syntax create table #tmp1 ([rows] varchar(5000) ) BULK INSERT #tmp1 from 'c:\test_file.txt' with (rowterminator = '\n') I am getting follow...more >>

I have a "can this be done" question.... I currently have a stored procedure that builds a string into a SQL Statement and runs it. This is an abreviated version as an example: CREATE PROCEDURE stp_ClaimInfo @DFTAX# VARCHAR(12), @TCLAIM varchar(1000) AS BEGIN DECLARE @SQLstr varcha...more >>

I saw “Create Login “ be used to associate the asymmetric key to the newly created login using: Create Login from ASYMMETRIC KEY asym_key_name But how can I associate ASYMMETRIC KEY to an existing login? I fail to see any option out there in Alter Login… Thanks...more >>

I have a table with 6 columns; state, county, city, cityID, zipcode, zipcodeID I have duplicates that I would like to eliminate by using 'Select distinct state,county,city ' but I want to retain the id's and zipcode. How can I achieve this but still retain the other column values? Thank ...more >>

I am trying to use a case in a where clause, which I read could be done but did not see any examples, but I am getting the following error. Can someone show me what is wrong with this statement. The error I am getting is: Line 4: Incorrect syntax near '='. SELECT * FROM vwSystemAccountInf...more >>

Using SS 2000 I have a user defined type with zero in the Allow Nulls column in Enterprise Manager. If I look at a column in a table that has the user defined type it has a checkmark in the Nulls column. I can update a row that has a null in the column so nulls are allowed. Does the not null o...more >>

Hello, I have one question: does SQL Server 2005 express edition support SQL authentication or not? I tryed to create a new SQL authentication, but I can't use it:-( ... maybe SQL authentication is not supported and I should know it. Thank you in advance! AndreiC ...more >>

Does anyone know for sure if tableA a join tableB b on a.key = b.key is better than tableA a, tableB b where a.key = b.key Thanks. ...more >>

Outside of security considerations, are there any performance issues that should be addressed when using cross database queries on the same server?...more >>

I have 2 tables [TABLE1] ID PROD QTY 1 123 1 2 456 4 3 547 2 [TABLE1] ID PROD QTY 1 123 2 2 456 2 7 577 2 How can I update [TABLE2] qty based on the qty to that of [TABLE1] with matching ID i...more >>

Since I often post questions, here's a cute little technique that I use with dynamicSQL If for example I have a report qry that must be run against many different databases or different criteria for different groups, say one that uses annual income and another that uses monthly, quarterly or w...more >>

Hi all, hoping you can help me. Here's some sample data: DATE PHONE DUPE 1/27/2006 8888888888 0 2/22/2006 8888888888 0 2/25/2006 8888888888 0 3/30/2006 8888888888 0 2/10/2006 ...more >>

How do I insert data from a flat file or .csv file into an existing SQL database??? Here what I've come up with thus far and I but it doesn't work. Can someone please help? Let me know if there is a better way to do this... Idealy I'd like to write straight to the sql database and skip the ...more >>

Dear fellows, From my workstation I've got QA and its help files, in spanish. I'd like to have in english and so I would avoid to use Terminal Server (against production servers) for consult that info in English... Is there any way for to change this? Thanks a lot for any input or advice, -...more >>

I have a table that has about 10 different foreign key and trigger contraints. I am wanting to setup a delete that will delete records that do not have a contraint problem. The problem is that once it hits one record with a contraint problem it stops I want it to continue on to the next record. ...more >>

Using the following, to generate some e-mail from production server to outside recipients... Declare @MyRecipients varchar (255) Declare @MyMessage varchar (255) select @MyRecipients = toaddress, @MyMessage = message from tbl_NAMEEmailLog where pk_emailid = 46099 and status = 'failed...more >>

Hi, I have a products table that can store different versions of each product. The combination of product name and start date is a unique constraint. I a query that will list those products where more than 1 by the same name have a start date < today and the end date > today, showing me where...more >>

Hello I am using SQL Server 2000 SP4 In my stored procedure I create table variable that contains one varchar field and join that table variable with one of the database tables. I keep getting error "Cannot resolve collation conflict for equal to operation.". Both fields should have same...more >>

Hi, This is a followup question to a previous post. Is it probable that the below query will use all three indexes X, Y and Z? : CREATE TABLE T (C1 INT NOT NULL PRIMARY KEY, C2 INT, C3 INT, C4 INT, C5 INT) CREATE INDEX X ON T (C2) CREATE INDEX Y ON T (C3) CREATE INDEX Z ON T (C4) SE...more >>

Help this was working. Moved on to another DB. Whenever I use checksum_agg on the result of Binary checksum (with a field list) always returns zero. When I try with * - all columns it works. Cannot use all columns - too slow select distinct binary_checksum('sp1,sp2,sp3,sp4,sp5,sp6') f...more >>

I work for a company that has a very large code base made up of mostly vb6 code. In almost every application there is ad-hoc sql queries that do not conform to the ANSI standard (joins expressed in the where clause mostly). The SQL Server 2005 upgrade advisor tells me that Outer Join Operator...more >>

im trying to copy data from a table on one server onto another server the scenerio is Source SQL1 ( server ) TestData ( database) MyTable ( table ) Destionation SQL2 ( server ) TestData ( database) MyTable ( table ) However there is a twist, i dont want any of the records duplicate...more >>

Hi All, There is a measurement data which storing some measurement values. Now i want to write a trigger on this to achive following goal. insert in a history table if the measurement.DataValue is increase by some predefine value. also insert the time for this. update in the time in sam...more >>

Hi There, I'm using following script to restore Restore Database adminsys_ex_avc from disk = '\\ppml31\DBBkups\fromPPML0112\adminsys_ex_avc.bak' with replace is it possible can i ignore the some table on restore, for e.g audit_log table and i don't want to use any logs -- Thanks ...more >>

Hello All. How to detect the current settings of ANSI_NULLS at run-time of script? For example: SET ANSI_NULLS OFF - sets ANSI_NULLS to OFF GO GET ANSI_NULLS - gets the settings of ANSI_NULLS. ...more >>

hi, Which the best version is? Both produces the same execution plan from table left join table2 on table2.FIELD=FIELD or from table left join table2 on table2.FIELD=table.FIELD ...more >>

Hi guys, I've been struggling with this for over 4 hours now but can't seem the resoltuion anywhere. I have a stored procedure that inserts into a table via a linked server and I get the following error [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionCheckForData (CheckforData())...more >>

Hello, I have a InstallShield program that installs MSDE and then creates a new database in it. To set up the new database on MSDE I run OSQL. Most of the time, OSQL works OK. But, sometimes it fails and doesn't print out any error messages (in the output file). Has anyone seen this inte...more >>