Hi. I cannot believe this is the case; but it looks like the name of the file to bulk insert cannot be parameterized: -- McGeeky http://mcgeeky.blogspot.com ...more >>

I know that it is good practice not to use data as keys. Would one think of a smalldatetime as data ? My situation is that i collect historical information on financial instruments. I have a price table with a composit primary key of instumentId and smalldatetime(but no time). Besides the k...more >>

If you have an identity column (i.e. 1-N) and you have 5, what is the most efficent query to get the next (and prev) row? Gaps could happen so there may not be a 6, but the next in order may be 8 for example. TIA -- William Stacey [MVP] ...more >>

Dear all, I am using SQL 2000 with vs2003. I need to create a store procedure which get the LAST record which has been written in the database. What is teh way to get that record ? thnaks for your help regards serge...more >>

Hi , can any one help in exporting data to a flat file by using BCP, iam trying to use BCP but iam getting following error Error : native error 18456 Error = login failed for user when using BCP thanks well in advance ...more >>

Hi, I am using SQL Server 2005 Express trying to learn to write a stored procedure. In my database, I right-clicked on progammability -> stored procedures -> new stored procedure. I put in my procedure, hit execute, but it just keeps saying: Msg 208, Level 16, State 6, Procedure GetProductsB...more >>

I need to be able to check if the first 4 characters in a varchar field are a valid int. Is there anything similar to a NAN() function in TSQL that I could use, or can anyone think of another way to do this? Below is some sample code I was using to play with this. Obviously the NAN() functi...more >>

Hi, I have a SP where I grab a value from an Identity field and then I want to convert it to a String and add it to another string. Only the @lname stores in the @CustNum. From what I understand STR should change it to a string. Any help would be great. Here is the example Declare @l...more >>

Can anyone tell me what's wrong in this portion of a SQL statement? The relevant fields are simple integers and char(1) This works fine: CONVERT(SMALLINT, SUM(CASE WHEN a.field1 = 1 AND a.field2 = 0 THEN 1 ELSE 0 END)) AS count1, And this works fine: CONVERT(SMALLINT, SUM(CASE WH...more >>

I have a sql job that executes a stored procedure. The procedure just iterates through a cursor and performs an update or insert and this doesn't occur within a transaction. If an insert or update fails will the job step quit on that first failure? I would like it to continue processing th...more >>

I have a VB.Net application that needs to create about 5 stored procedures and a couple of functions on SQL Server 2005 Express. Currently I can execute all of them in one window of Sql Server Management Studio, just separate each of them with a "GO" statement. Is there a way I can accomplish th...more >>

Hi everyone, I was hioping to obtain some help with viewing sql data whilst within a transaction, from OUTSIDE the transaction itself. For example, if i do the following steps: 1) -- Do this in Query Analyzer session 1 window -- Note: there is no commit transaction below begin trans...more >>

Hello, which sys table hold dependencies between database objects? thx ...more >>

I've a treeview structure saved in a db as follow id_node id_ref_node node_label node_type 1 0 root standard 2 1 folder1 standard 3 1 folder2 standard 4 ...more >>

Does anyone know if SQL 2005 will support the *= for left joins? Thanks, -Steve- ...more >>

Hello When I run the following select statement I receive records that begin with ET or WR even though I specified in my condition not to include them. What is the problem and how do I correct? The ITEMNMBR data type is char(31). select ITEMNMBR, LOCNCODE, ORDRPNTQTY, ORDRUPTOLVL from IV0...more >>

Hello, I have created a stored procedure and in the result window it had it print 'Created Nested Stored Procedure'. I have a table called SPHistory I want to popluate this name in that table and do this for all sp's....more >>

I developed the T-SQL code to extract a sum of minutes by week from my database. The code works fine in the Query Analyzer but when running the Procedure from Access, I recieve no data. No errors are present either. It appears to be running only the first iteration of code. I figure it's ...more >>

It's possible ? From SQLServer1, execute sp_addsubscriber of SQLServer2 ? Thanks, PePiCK ...more >>

Hello, I created the "MyDb" database using Microsoft SQL 2005 Server Management Studio and I added the table "dbo.Surveys". Then I right clicked on Store Procedures and created a new procedure. When I close it I am asked to save it. I save it and gave the file a name. However my stored p...more >>

Here's a good one. Does any one out there know how to tell if a "particular" (by name) = stored procedure is currently being executed? I have a store procedure that does some processing that takes a long = time to execute (20-30 min) I need to restrict the calling of this stored procedure...more >>

I've got the following to convert my seconds field to date/hours, but now I'd like to convert the NewDate to local time. Select DATEADD(ss, LastOccurTime, '19700101') as NewDate, LastOccurTime Found this on the newsgroup, but I need to go the other way: SELECT DATEADD(HOUR, DATEDIFF(HOUR...more >>

anyone know what the exact meaning of this message? >From my debuging print, it happens just after or before Fetch Next From PNLAGG INTO @RowNo, @Totaling thank you in advance. ...more >>

I am running into an issue trying to accuire information using a linked server and dynamic sql. --- this is the query for local SELECT @@SERVERNAME AS [SERVER], [NAME], [DBID], [SID], ...more >>

Hi , I am wondering as to what are the pros and cons of using IN,BETWEN and >= and <= for a requirement such as x has to fall between a range of values e.g x should be from 1 to 10.So I can write in the following ways:- select a where x in(1,2,3,4,5,6,7,8,9,10) select a where x between a...more >>

Thanks for replying. This syntax somehow is incorrect. I tried several things, I still get an = error. Are CTEs allowed in this case. declare @conID int set @conID =3D 601; =20 WITH con AS ( SELECT con.conID, con.conParentID, 1 as Lvl FROM dbo.Container con W...more >>

Hello, I am new to SQL Server 2000 but proficient in Access, and the switch is giving me a challenge. I am trying to do a simple parameter query, which I believe is now called a View in SQLServer. In Access, I would have entered in Criteria of RecordEntry this statement: Between [Beginning...more >>

I am trying to delete 500000 rows at a time from a big table. I am trying to loop through it. I am attaching my code, what am I doing wrong. It either will delete only 500000 rows and exit ot it will keep on looping. I am asking it to delete data older than a certain date. If there are 3000000...more >>

I've written an extended stored procedure using VS 2005/C++. The sp can be added and executed on my local SQL Server 2000. However, after I copied to production server. I can sp_addextendedproc it but executing shows error: Msg 0, Level 16, State 0, Procedure xp_calc, Line 1 Cannot load the ...more >>

Hi , I have this stored procedure: create sp_process as begin tran -- Part 1 ... set of querys including a cursor ... --Part 2 exec sp_Load1 @error output if @error <> 0 begin rollback tran return end --Part 3 exec sp_Load2 @error output ...more >>

I would like to create a stored procedure who will do multiple query until it returns a result/recordset, then stop querying. But, I want the result that is passed back to me to be just 1 recordset. If I do something like the following queries, and say it finds a recordset on the 3rd query, i...more >>

I have a DTS package already created on the server. I want to call it in a sp using the xp_cmdshell 'dtsrun ...' I'm getting "xpsql.cpp: Error 87 from GetProxyAccount on line 604" Any ideas how to fix this? Thanks Mike ...more >>

I want to create a view to do this Select Contact_Name, If [Chk_Gender] = 1 then 'Man' else 'Woman' endif as Gender from Contacts it doesn't work. Is there something wrong in it? ...more >>

A non-SQL application appends messages to a plain text file. I want to read the rows of this text file into a table. The text is in a general format and should be considered one column. The lines are terminated with a typical CR/LF. I'd like to do more analysis on the text after getting it into ...more >>

Hi, Is there a query I can run to get the date and time of the last transaction to a SQL database? Kind of like the Last Modified date of an Acess Database. Thanks, Ron ...more >>

Is there a way to list an object name in a query where it's in another database? What I would like to do is have a select statement that lists processes running (list locks actually) and have a column in the query for dbname + object name. I'm not sure if you can do this in the select list t...more >>

I have an appication that feeds a SQL Server 2005 database with records. I have another application that should treat the records inserted by this first application. I know you can achieve this by incorporating .NET code in SQL Server 2005. However is there another possibility that SQL Server...more >>

Hello, when I imported data from ACCPAC to MS SQL Server 2000, I noticed there were fields with a SQUARE character appended to the end of the values. I am guessing these are NEW LINE CHARACTERs. My question is how can I query these characters out because I want to delete them. I do not wan...more >>

Hi all, I have a flat file that isn't as flat as I'd like it to be :) The rule for the data insert into the flat file is that the column widths are set (say to 8 characters for example) but may be longer if the data is longer than 8 characters (no data loss is paramount). The columns are the...more >>

'Hi, I have a question regarding option (keep plan) I am using Option(keep plan0 in one of the stored procedure (SP1) to minimize recompiles . But there are couple physical tables which are referenced in the stored proc (SP1) is having lot of physical deletes and inserts (outsied of S...more >>

Hello: I have a process that's been stuck for two days.. It's a stored procedure that runs as part of a scheduled SqlAgent job. I tried to kill the process which put it into a rollback. Kill with statusonly returns: SPID 52: transaction rollback in progress. Estimated rollback completion: ...more >>

Help, I have the following procedure declared as a test for dynamic sql. The resulting SQL statment is correct but I am getting syntax errors when the dynamic SQL runs. taking the body of the procedure and running it from SQL Analyzer window creates the correct result. If I rewrite i...more >>

Two questions First, I need to insert the dbid into the table so I can cross reference the log files with other data. That being said, I can't seem to get the update field to work properly. As always, it is most likely something pretty obvious that I am missing. The second question. The "db...more >>

How can I check the value of a local variable to see whether it reurned a record after setting it to a SELECT Query? I also need to be able use the value as an integer if it finds a match Declare @Found as Int SET @Found= ( SELECT TOP 1 ID FROM tbl WHERE col1=@col1 AND col2=@col2 ) IF @F...more >>

hey all, i have a 2 table join select statement that i would like to turn into an update statement? is this possible? thanks, rodchar...more >>

Good day, We are using bulk insert with a formatfile to load a text file into sqlexpress. One field in the text file contains non-ascii (unicode) charaters and the corresponding database field is nvarchar. When the record and row are specified in the format file as: <FIELD ID=3D"23" xsi:...more >>

Dear all, How to identity all those changes done in a db for any user in a specific period of time? I am not talking about put triggers in each object or something like that (on the other hand such actions never will reach exhaustely all the actions commited) For example: developer1 mo...more >>

Hello, I'm working on a C# application that uses the DTS object library via COM interop. Until now it has been working nicely. Now I would like to get a reference to what it seems a special task category: TransferLogin, TranferJobs, etc. The CustomTaskId I'm getting from those tasks are ...more >>

Hello All I am stuck in silly point Please help me and excuse me for a bad Knowledege of SQL store Procedurte Programming. My date are store in DB as 10/17/2005 5:37:07 PM How Do I fetch the Name of month from this. Thanking You With regards Tarun Sinha ...more >>

Hi to everyone! It's my first mission in DTS. I fill global variables with e-mail addresses (maybe with empty string) of my accounts. If I have an e-mail(not empty), I need to create an excel file with data to this account and send messages to him. I can do it to some alone account, but I have...more >>

I am developing a web application that will host many companies' data in one database. And there will be the possibility that the company may decide to take their data at some point and host it themselves. So my question relates to the table structure and relationships. If a company decid...more >>

Hello all, I baddly need to create my own datatype, that will be able to generate itself as unique id. 1. my user defined data type is based on char(14). 2. structure of this type is YYYYMMDDSCIDCONT YYYY = year of record creation MM = month of record creation DD =...more >>

is it possible to have a copy of sent mail by Sqlmail at the outbox of outlook? thx. ...more >>

I have a stored procedure that queries for all bookings in between a date range, also returning any records that don't have an DateOut to default to a certain number of days ahead from the Date In. Here is the code: PROCEDURE dbo.BizRule_GetBookedRooms ( @DateIn datetime, @DateOut dat...more >>

hello, i get this error Microsoft OLE DB Provider for SQL Server error '80040e14' The log file for database 'autosmotos' is full. Back up the transaction log for the database to free up some log space. So i try to delete the LOF file with this command: ALTER DATABASE [autosmotos] ...more >>