I have a table that contains records in various categories. Let's call the possible categories A, B, C, D, E, etc. I need to pull just a list of the categories that are in the table. On a particular day there may be hundreds of records but only in categories A, D, and E. I don't want all the ...more >>

hi, can any body tell me what is the diff. between function and stored proc.what is usefull to create my application good. ...more >>

HI, The existing query which is biult is an asp app is as follows select ID,Name from [view_ReportTracK] with (NOLOCK) where ( (id in (select id from view_orderchanges with (NOLOCK) where ODate >= convert(datetime, '1/1/2005') and ODate < (convert(datetime, '2/1/2005') + 1))) ...more >>

Can someone tell me why this is always not equal? And how to convert to be equal? declare @str nvarchar(3) set @str = '123' if (@str = str(123) ) select 'equals' = str(123) else select 'not equals' = str(123) ????...more >>

Hi! I have a table with about 18M rows. Every DDL or DML or select I run on the table takes forever. Are there any specific table settings or something that I can use to speed things up? I have tuned my SQLs the best that I could and they seem more or less optimal at this point. Any suggest...more >>

Hi all, I'm developing a stored procedure that uses temporary tables to store support data to reuse in different part of the procedure. I frequently use a table stored in tempdb and I've always had good performances, but I know that I can also use different approaches and I want to under...more >>

Hi, I just finished reading this article about WITH(NOLOCK) here http://www.sql-server-performance.com/lock_contention_tamed_article.asp I was wondering what others think? Is it a good idea to use that? I ask this because I have some tables which are only used as data warehouse to creat...more >>

Hi, I've been using views to simplify my queries. Since I discovered I can put indexes on them, I started to use this and it improved the performance a lot, even though I already have indexes on several field in their table. I have complex queries on tables with millions of data. Without i...more >>

Hi All I am getting an 'Error converting data type varchar to numeric' when i run a query to select all the records and adding an additional column 'Comm' which is an Amonut column copy but, inserts a 'DataError' message whereever the amount is a zero. I would like to keep the table struct...more >>

Hi, all... I'm involved in a debate with someone over the benefits and pitfalls of stored procedures, versus other methods [mainly, SQL pass-through]. [yes, i know, this has been debated for years] As an applications developer, I use SPs for most database operations, and always use them as ...more >>

I want to have a function to return the minimum of 4 (or N) parameters. Any good way to implement it? For example, dbo.min4(23, 3, 54, 5) return 3....more >>

I've always created numeric PK's using idenity columns where necessary in tables. I'm also careful to index columns that are used in joins, sorts, etc. But something just occurred to me. If I have a table Custs with PK of CustID Identity 1,1 unique constriant, is it also indexed? or do I need to ...more >>

Hi, When I log into Windows XP I use my name 'Shelley' I am trying to set a default value in a field in a table (SQL 2005) to be the value 'Shelley' or another persons name if they are logged in. I have tried suser_sname result (server name\admin) system_user result: (server name\admin) user...more >>

I can't figure out how to get a list of all users with their last login. Does this require a sub query? Self join? Distinct? This has my brain exploding. Table Users: UserId(PK), UserName dnowitski,Dirk Nowitski mmodono,Mike Modono snash,Steve Nash Table Logins: UserId (FK), UserName...more >>

Im trying to run a query that will pull distinct data not for the entire select query but for a single column. Here is my sql statement below. Why is it not working? It is probably something stupid. Thanks for your help in advance SELECT Count(MastrQualityTbl.[WORK NUMBER]) AS [TOTAL WORK RECE...more >>

Hi, I have made a sql to alter a table if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Temp]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) Begin alter table Temp add LastUpdatedBy nvarchar(50), LastUpdatedDate datetime End But this script is good for first tim...more >>

I just ran the package using BIDS and it failed with some connection errors: [Execute SQL Task] Error: Failed to acquire connection "localhost.master". Connection may not be configured correctly or you may not have the right permissions on this connection. Data Source=(local);Initial Catal...more >>

I can compile on a 2K box, however; in 2005 I get the following error: Msg 4104, Level 16, State 1, Procedure gsp_Volunteer_Search_ByRegions, Line 22 The multi-part identifier "dboGroup_Region.RegionId" could not be bound. See anything in this that should be changed: CREA...more >>

How do you connect to a different SQL Server/Instance, execute a query and get resultset from that server using TSQLin SQL Server 2005 ? Earilier DMO was used for this. Thanks ...more >>

Is there a way to set a default value for a particular column in unmatched rows that would normally return null? Left Join ...more >>

ExecuteImmediate with the SQLDMO sometimes returns an error with err.number -2147199229, while err.description is empty (nullstring). The command appears to be correct, since a repeated execution of the same command usually succeeds. I wonder what kind of error that is. Any ideas? Joachim Gra...more >>

I need to add rows to a #temp table that I create from a dataAdapeter.SelectCommand ... Create Table #temp(col1 int, col2 int) While I have the connection open that the #temp table was created on - it remains alive. I have data in an ado.net table (in memory) that I need to transfer that d...more >>

Hello, I have three tables- TBLINSPECTION with the following fields- BARCODE ITEMTYPE PASS DATEINSPECTED 1000 x YES 01/12/2006 1001 x YES 02/12/2004 1002 x YES 03/12/2006 1003 y ...more >>

Hello: I currently have a query that compares the results of a bulk upload to data already loaded into a SQL Server table, tblCDR. Any data that does not currently exist in the table tblCDR is then appended into tblCDR. I am using DTS and Temp tables to accomplish this task. However, I...more >>

Hi I have a program that transfers data from one db to another. I need to transfer them in order so I don't have referencial integrity problems, in other words a master table must be transfered before its child. Is there a stored proc or view that allows me to get that order? Thks...more >>

I have a MS SQL server table that I would like copied (or linked) to MS Access. I've tried doing a 'SELECT * INTO accesstable IN c:\mydatabase.mdb FROM sqltable', but I only get error messages (Error in destination table specification. unable to parse query text). I can copy the table if I lea...more >>

I have tried to import from a spreadsheet into SQL an I get this error message Error 0xc00470fe: Data Flow Task: The product Level is insufficient for the componet ...more >>

Hi all, I want to get list of directories of ftp folder in stored procedure. So i am trying to use xp_cmdshell. command which i am trying to get list of file is as below. exec xp_cmdshell 'FTP.exe testftp test pwd dir /users//test/ quit '; Here hostname is testftp username is test ...more >>

All, Due to a problem with sp_fix_user_logins not wanting to update a SQL Server login that is set as DBO on a database, when I restore my production DB to our Dev/Test/QC environments, I change the dbowner to a domain account, then run through the logins fix. This part works fine. Howeve...more >>

It may seem strange to WANT to cause a lock but I am developing a perl script to analyse locking issues over a given time period. I need to cause a database lock at will on a SQL Server 2000 database in order to test this perl script is working. Can anyone think of a simple method of causing...more >>

Hi how can I add the numbers 1-5 to this to correspond to the days monday through friday. Thanks SET @BackupFile = N'C:\Backups\MyBackup.bak' ...more >>

Is there a way to easily validate a SP variable that will be used to insert a UK postcode into the database. I have a regular expression for the input mask but dont know how to apply it to the variable. ...more >>

Hi, I have the following sql query being executed. StrSQL = "" StrSQL = StrSQL & "Update MyTable Set " StrSQL = StrSQL & "Field_1=1, " StrSQL = StrSQL & "Field_2='" & text1.text & "' " StrSQL = StrSQL & "WHERE ID=" & idUpdate Conn.Execute StrSQL After the query is executed, I want to k...more >>

Hi, I am about to develop an ASP.NET app that updates tables in an existing SQL Server Database. These same records will be updated by another application. In terms of updates of the same records at the same time in both applications what do I need to consider? Thanks in Advance....more >>

Hi Experts, How to take the last record in the table1 Table1 Item_code Date Qty 111 20061020 12:00 200 111 20061021 13:00 150 111 20061023 14:00 100 111 20061023 15:00 75 112 20061020 12:00 300 112 ...more >>

Hello All, Is it possible to store the date as ' 02 - Apr - 2006 ' in the SQL datetime field? If it is please tell how to do. Thanks, karthick ...more >>

Hello, I wonder why SQL Server does not accept a syntax like this in dynamic SQL, i mean why can't MS simply add this simple method, =========== Declare @Wherestr varchar(100) SET @Wherestr=' Column2=100' SELECT a.Column1 , a.Column2 , b.Column3 FROM Table1 a Join Table2 b on a..ID...more >>

Hello, I want to insert a Single/Float type value into a table using the following SQL statement. I'm executing this from a VB6 app using ADO 2.7. myValue = "0.5074655" (Entered by the user into a textbox) strSQL = "INSERT INTO myTable VALUES (1,'1A',csng(myValue)) conn.Execute stqSQL T...more >>

I have the need for a query that takes as a parameter a number of valid values for a column, say 1,2 and 3. What I want to do is then to have a query like SELECT col1,col2 FROM mytable WHERE col3 IN (1,2,3) but the problem is the values 1,2 and 3 must be specified in a parameter and the numbe...more >>

Hi all, We experienced some deadlocks in our servers and to resolve those we used nolock query hints in some of our select statements. Afterwards we are experiencing "There is insufficient system memory to run this query." errors for queries which previously ran well. These queries also doe...more >>