I need to run a dts package from a command line that accepts a parameter. then opens a text file on my c: \files folder and reads the contents of the file into a table in a database ...more >>

Why does the following insert statement produce the error that follows the code. ============================= INSERT INTO diary ( di_user, di_notes, di_dest_pk, di_dest_fm ) SELECT froi.fr_efroi, 'Claim: '+froi.fr_claimno, froi.fr_mco_itn, ...more >>

Situation: Table 1 has car records with a field called 'color'. Table 2 has a list of all the current 'color's available. However, the current list in table 2 may not have all the colors available in table 1........say a certain color is no longer offered, however there are still cars from ...more >>

Hi there, I have written a .net application that performs various extracts from our SQL Server 2000 database. I'm using bcp commands for each extract. Now, we currently have a DTS extract solution in place, but it's slow and difficult to maintain. This new .net application should definitely h...more >>

I am trying to get a result that looks like this:- 52513, Fred Bloggs, 26/04/2004, 762.16, 2856.21 Now the query below brings back the above line, but this is only because I state which Customer number to bring back, if I leave the customer number out of the statment it brings back ever...more >>

Hi there, I'm trying to create a scheduler table with options to specify daily, hourly, weekly and monthly jobs, including multiple schedules and from--to variables. Is there an exposed table structure of sql's great scheduler? the one used for sqlagent jobs. If not, does anyone has a white...more >>

Hi, I have a serious deadlock problem. I'm using the Profiler with Lock:Deadlock, Lock:Deadlock Chain, TSQL:Batch Completed and TSQL:STMT Completed event classes. Im the very load circunstances, the CPU goes to 100% and SQL Compilations/sec are over 300 ... In the situation I cannot see ...more >>

I read somewhere that to avoid recompiles and compile locks that one should create all procedures with a qualified owner name. eg create procedure dbo.myproc Would it also be true that for EXEC's from within a procedure that one should qualify the owner on the exec call. eg create proce...more >>

Trying to do a "whodunnit" query: I need one SELECT statement to find out which UserId(s) changed the STATUS to "CLOSED" from a previous STATUS *other than* "PAID"? JACK changed the Claim Status on 2004-04-27 to "CLOSED". However, I want the row where BILLY changed the Status to "CLOSED" on...more >>

I've got a simple problem, but the answer has eluded me I need to pass in a maximum number of records to retrieve to a stored procedure. I had thought I would just be able to do - Create Procedure uspEasySelect a @max as in SELECT TOP @max FROM tblMyTable ORDER BY CreateDate DES -...more >>

Is it possible to rename a database table from a stored procedure? If so, how can this be done? Regards Tore G. ...more >>

Column location_id is an int, but because of other fields I need to display it as a text element. So I use convert(): SELECT convert(varchar,location_id)... FROM mytable ORDER BY location_id 1 2 3 10 22 23 24 Perfect. But later development mandated the use of SELECT DISTINCT. ...more >>

I have folowing situation: DDL: CREATE TABLE MyTree ( NodeID int NOT NULL , NodeName varchar (255) , ParentNodeID int NULL , Lvl int NULL /* indicates node depth */, OrderNumber int NOT NULL /* indicates order in leaf */, CONSTRAINT PK_MyTree PRIMARY KEY CLUSTERED (NodeID), CON...more >>

I don't think it's possible to use parameter with OPENQUERY,following query have problem. DECLARE @emp_id int SET @emp_id = 5 select * from OPENQUERY(SERVER1, 'SELECT * FROM TABLE1 WHERE id =@emp_id') ...more >>

I get wierd looking timestamps out of T-SQL ::fn_virtualstats. Is there some way to draw sense of out those ? Thanks in advace. _Sourabh...more >>

Hi, I'm in process with some code, so please ignore the fact that it's so messy (see below). Anyway, I've noticed that the code will not execute unless I set the variable @freeSpace to some value, like 0. If it's a NULL, the call to the "FreeSpace" property will yield the error: "Error in s...more >>

Hi guys, If you're a guy who is doing interview to hire sql developer, what kind of questions I should ask them? just give me some opinions.. ...more >>

Hi, I am using the systables objects to list the fields in a table. How do you list the fields of a table in the order they appear in Enterprise manager? Thanks in advance, Stu ...more >>

Hello, I am writing a SP which inserts or updates into a table. The SP has to decide wether to update or inert... which in theory causes less load on the dbase server? declare @rows numeric select @rows = count(*) from answers_attributes_2 where id_attribute = 81 and id_entity = 2 i...more >>

I am trying to list data from the previous working week e.g. Today 30/04/04 would return two dates 19/04/04 and 23/04/04. Regards ...more >>

I am trying to run a select into Query from one DB Server into another DB Server. Here is the Statement. exec sp_addlinkedserver 'srvuser3', N'SQL Server' select * into srvuser3.echda.dbo.tblaccounttypes from dbo.TblAccountTypes I am getting this error The object name 'srvuser3....more >>

I am wondering if anyone knows why one method works over the other: EX. This query does not work: select * from ORADEV..NTBK.ADDRESS_STATUS_HISTORY@LDM_LINK error message: Server: Msg 7314, Level 16, State 1, Line 1 OLE DB provider 'ORADEV' does not contain table '"NTBK"."ADDRESS_STATU...more >>

How do you remove an identity constraint on a table once it has alraedy been placed on a table in SQL? (not using EM) thanks ...more >>

Hello Here is the scenerio. I have a 'Sales' table which includes sales information such as date sold, item#, amount....and so on. I would like to get a record that has 10th highest sales amount. All I can think of is to get top 10, reverse the recordset and read the top one (which is the 10th ...more >>

I have got a group of data like this Row1 10 Row2 2 Row3 6 Row4 9 Row5 12 Row6 8 and I want to calculate median for it. Thanks for your help. Reza ...more >>

Hi, When trying to use DBCC SHRINKDATABASE, I get the following message for all my databases. Cannot shrink log file 2 because requested size is larger than the start of the last logical log file What does it mean? Is this an error? How can I fix it? Thanks Guys. ...more >>

Hi, we would like to have users use the profiler but we do not want those people to have sa priviledges or changed their current permissions. is that possible or the only way to do this is grant them sysadmin role? ...more >>

Hi all, I have a text field in a SQL Server table that stored HTML. I need to strip out the HTML and return only the text from the field. I wrote a VB6 dll to do this. But I am not blae to use it with the text field because a SQL Server variable can store only 8000 characters. So it does not ...more >>

Any pointers or documentation on writing SQL and code to take advantage of a database server with 8 CPUs?...more >>

HI There, I am trying to execute one stored procedure inside another and concatenate the output of the select statement (if it returns rows) in the body of the email message, could someone help me out with a little syntax please. Sean - Thank you in advance Select domain_name,hosting_du...more >>

Hi, I looked at the SQL Server online book and couldn't figure out how to insert and retrieve text and image data type in SQL Server 2000. Would you please give some sample code regarding this issue? Thank you hb ...more >>

I would like to be able to use T-SQL to determine a day's month/year based on a nonstandard calendar. Specifically, I would like to represent the broadcast calendar, in which a month begins on the monday of the week in which the first day of the month on the gregorian calendar falls. The definition ...more >>

Hi, I perform the following query (SQL2000): start transaction update test set row1=5 where row2=6 while executing the query table TEST is locked! When I execute: select * from test this query is not responding until I commit transaction. How to solve this strange table locking? R...more >>

I have a simple stored procedure that executes with no problem. It contains multiple count queries all based on difference criteria for the same table. I only receive one return value from the first query. I have tried setting up multiple output parameters with no luck. A sample of this would great...more >>

hi there, please help me to set constraint for the following logic: Two columns A and B in a table, for a given tupple, one AND only one column of A and B can be NULL. In other word, if A is NULL, B must not NULL. if B is NULL, A must not NULL. And A and B cannot be not NULL simultaneousl...more >>

The 3 tables defined below implement a many-to-many relationship between users and roles. Beyond that obvious relationship, roles may exist in other roles (and that relationship is determined by the value of tblRoles.[ParentRoleID]. What I need help with is coming up with a query that will ret...more >>

Can anybody tell me why the following update statement will not work ? I have declared an IN statement to get the results from the subquery but I get the message "Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is u...more >>

Hi does anyone know of a cmd that will truncate all the tables in a database of all its data while keeping table structures in place? Thanks for any help Mikey...more >>

I have a table where I'm using the Getdate() default on a DTS load to a field in a table so I can compare yesterday with today. I need to look at today's record compare to yesterday's and based upon certain fields create a report of the differences. fields in table: 1) emplid 2) useri...more >>

Good Morning I have a SELECT statement and i have to show a consecutive number in the SELECT like this Sample Query SELECT SomeThing AS Consecutive, Field1, Field2 FROM Table Order By Field Sample Results Consecutive Field1 Field -------------- ------- ------ 1 ...more >>

I have a SP in oracle, and am finding myself in a bit of a difficult situation, trying to convert this particular statement to T-sql: counter := (24 * 60 * 60 * to_number(to_char(tsTime,'J'))) + to_number(to_char(tsTime,'SSSSS')); Thanks for any feedback. Alex Ivascu ...more >>

I have an application with an Access front-end and SQL 2000 back-end. The tables and views are linked via ODBC. The application opens and closes an ADODB connection very frequently in the application. Is there any benefit or problems with just opening the connection in a startup module and sim...more >>

We use SQLDMO.Application.ListAvailableSQLServers to show a list of all visible SQL Servers. This works great, until the user disconnects his network cable. Now I'd expect to lose the servers on other computers, but we also can't see the servers running on the local box. After some googl...more >>

Is it better performance wise to build separate stored procedures for each table written, or to group all the writes into one major procedure? Also, do you take a performance hit if you leave a connection open throughout an apps life, or should the connection be opened and closed for each tra...more >>

Folks I have a table that has an image column Currently that column does not accept NULL I want to change that, so the column does accept NULL So, basically I want to do something like ALTER TABLE MyTable ALTER COLUMN MyColumn image NUL The problem is that (according to Books Online) the al...more >>

Hi! I was looking at the "bulk insert" command and I was wondering, can you use this command to upload an .xls file into SQL server? I have been trying with a very small file (one column and three rows) but it doesn't seem to find where the row ends. Is there a special character I would have to ...more >>

Hi I have a load of contact data in SQL Server. Is there any way I can expose this data in Exchange TIA...more >>

Hi everyone I was testing random key in trigger on insert and got these results Please see the results in code 'Notes : Am I misinterpreting this information and the updatebatch is handling it's own errors while doing batch update and then the failure result is the actually primary key ...more >>

We are accessing a table in SQL Serever DB with around 50000 records. We need to display the same on to a DBGrid. The application is multiuser application so grid must display the latest data from database In the given scenarion when we are refreshing or making the datasource active it takes too mu...more >>

Hi Every bod I need to execute Stored Proc from VB...more >>