Hi! Is there anyway to loop through the records on a temp table or a table variable without using a cursor? Because of performance and memory issue, one of the requirement for my current project is that we are not allow to use cursor. I am trying to archive the following task: For each ro...more >>

Server Info: W2k Adv, WSSE 2k, 4 Proc, 4G RAM Our DBA reports that he has to continually recompile stored procedures to keep our application running. The SPs do not change during the day, so what would cause it to need to be recompiled. Basically, our app times out on queries - fixed by r...more >>

Hi, I'm kinda new to this SQL Server world so please bear with me. I'm using SQL Server 2000. I'd like to get some help with the following example. Consider the following tables: CARS ---- CAR_ID (pk) CAR_NAME OWNER_ID (fk) COUNTRY_ID (fk) (manufacturing country) OWNERS ------ ...more >>

I have a table called Jobs where one of the fields is [Position Description]. I want to be able to full-text search this field so I enabled the full-text indexing: EXEC sp_fulltext_database 'enable' Then I created the index on the key: CREATE UNIQUE INDEX job_id ON jobs (job_id) then ...more >>

Hi, I having a remote data file. I want use it in the bulk insert. how i can specify the path. Babz ...more >>

Hi, I have an accounting system in SQL Server that has some time sharing data with a system which uses an Oracle database. The Oracle DBA's gave me an account/pass, table structures so, I can send them data directly into their tables. I did this by simply setting up the Oracle connection (O...more >>

Hello All, I have written one sql script how do I call another sql script from it. Thanks for your help. regards, Abhishek. ...more >>

I have been working on converting a set of procedures accessing data on a SQL 6.5 box to a completely different set of data on a SQL 7 box. A problem that I have ran into plays out as follows. This is the schema of the SQL 6.5 table: CREATE TABLE dbo.Prop ( Type char(5) NOT...more >>

i want to get a unique id back from sql server (in a thread safe manner). code examples would be easiest: THIS WORKS: _ConnectionPtr con; con->CreateInstance(CLSID_Connection); con->Open("Foo ConString...", "", "", adConnectUnspecified); con->Execute("INSERT blah...", NULL, adExecuteNoRecord...more >>

Hi all - I am trying to generate a query that returns a subset of data from a table. Here is the table: S_N DATE_TIME 001 8/8/03 10:00 AM 001 8/8/03 9:00 AM 001 8/8/03 8:00 AM 001 8/7/03 9:00 AM 001 8/7/03 8:00 AM 002 8/8/03 9:00 AM 002 8/8/03 8:00 AM 002 8/7/03 8:00 AM 003 8/8/03 10...more >>

His guys, Is it true that I cannot use sp_executesql from inside table data type udf. My udf looks like: CREATE FUNCTION [dbo].[MixLiqtest] ( @cPlant char(3), @dtStartdatetime datetime, @dtEndDatetime datetime, @Res varchar(20) ) RETURNS @tblMixLiq table ([datetime] smalldatetime,...more >>

Hi, I want to search for how many stored procedures a table has been called in SQL Server 7.0. Is there any way I can run a query to get the result against the system tables? Appreciate any help! Shane...more >>

I've defined a UDF that takes 1 DATETIME param & returns an INT. This UDF is used in 2 SPROCS (and works fine). This morning I changed the UDF to accept 2 DATETIME parameters. I immedediately changed the SPROCS to pass in the 2 required params. Now, when I run the SPROCS I get erro...more >>

thank Andrew and Raydan first for answering my previous question. My new question is, can a UDF/SP call/exec itself? for instance: function checkSomething(@para1 Datetime) return @result as datetime begin declare @mDate1 datetime declare @mDate2 datetime select @mDate=someField1, @mDate2=so...more >>

Can I lock a bit column to be ONLY 1 and NEVER 0 for WHATEVER reason ? Thx :) -- == fabriZio ...more >>

sp_lock returns an objectid I would like to see the object name, anyone have a nice wrapper for sp_lock that uses the result set returned to do a join on sysobjects to get the name.. ? ...more >>

Hi, I know how to use BEGIN TRAN before SQL statements in the Query Analyser (SQL 2000) and the either COMMIT or ROLLABCK as required. I have a VB application that will be updating large amounts of data in our database. My Ideal solution would be to programmatically BEGIN TRAN before the u...more >>

Me.SqlInsertCommand2.CommandText = "BULK INSERT [ [ 'Mink'.] [ 'TBL_Catalog' ].] { 'TBL_Catalog' FROM 'c:\Catalog1.bcp'" What is wrong with the syntax of this? Trint .Net programmer trintsmith@hotmail.com *** Sent via Developersdex http://www.developersdex.com *** Don't just partici...more >>

Hi, I need to display some datetime fields from a SQL 2000 database on my web page using aspx....The time value keeps showing up with the date. Unfortunately the code I am working with has not used SP's and has inline SQL. I am trying to avoid the time from displaying with the date wi...more >>

I want to use a temporary table in an IF clause. For example: Declare @Marker bit set @Marker = 1 IF @Marker =1 BEGIN select * into #temptable from Jobs where JobId= 1234 END ELSE BEGIN select * into #temptable from Jobs where JobId= 4321 END However this always gives the er...more >>

I am to create a stored procedure where I intend to receive one parameter that is used to order: CREATE PROCEDURE teste @ordem nvarchar(30) AS SELECT * FROM minhaTabela ORDER BY @ordem but this doesn't work, some exists it sorts things out of to implement this solution? ...more >>

Hi, I have a table that has two varchar fields: fldLowerCode and fldUpperCode, and a int field: fldValue sample rows could look like this: A0021,A0099,25 E2244, G2299, 50 i need to find out how to find a row if the argument passed is say "A0055", which should return 25 (the arguemen...more >>

Hi All, I read and read the SQL reference book on ALTER TABLE but could not figure out how to add a PRIMARY KEY constraint to a column. For example, this one will fail ALTER TABLE XXXX ALTER COLUMN ZZZZ ADD CONSTRAINT [PK_XXXX] PRIMARY KEY NONCLUSTERED. Basically, I want ...more >>

Hello All, I have two SQL servers (Server1, Server2) . Server1 has stored procedure that can call another procedure from Server2. The performance of the processing is very low. But if I call the same procedure from the Server2 then performance is good. Could anyone explain how to find out...more >>

Hi, In SQL how to convert 1/7/2003 8:55:00 AM to 1/7/2003? SF ...more >>

If I have my own formatted string date "yyyyMMddHHmmss" what kind of performance am I loosing as compared to using the native DateTime for my SQLce db. The database isn't that big Anyone ever do this? ...more >>

Hi. I'm trying to work with the MSXML2 object in a stored procedure (using sp_OA calls). I can create the XML document instance, like so: EXEC @hr=sp_OACreate 'Msxml2.DOMDocument.4.0', @xml OUT But when I try to load a string into the document object, I get an error. Specifically: ...more >>

I can't figure out the syntax to execute sp_executesql command when in the from clause I have a case statement selecting the appropriate udf instead of a table name. Following runs fine: declare @vcSQL nvarchar(4000), @res varchar(50), @Plant char(3), @sdt smalldatetime, @sdt1 smalldatet...more >>

HI, I've seen it before but can't re-create it... How do you save a query result run in Query Analyzer to an XL spreadsheet, with each record being inputted in an individual cell? I have tried the 'results to file' option on the QA tool bar but unfortunately when asking for the file na...more >>

If i need to run ONLY ONE update, delete or insert. Do I need to use begin transaction and commit or rollback ? Why ? Thanks, JC...more >>

Hi, How can I get the system's date without date and time part. Select GetDate() returns the time too but I would like to strip it. -- Thank you, Doru ...more >>

I have the following query where I am checking type. INSERT type(type,[id]) SELECT DISTINCT type,[id] FROM main WHERE type NOT IN (SELECT type FROM type) How do I need to modified a query if I need to check 3 parameters: type,description,flag? ...more >>

I'm am looking to find out if there is a way to write a script that will lock all but one user out of a database. We have a reporting database that if queried while the database is being updated would yield inacurate / incomplete results. logically, I can figure it out: If users not X...more >>

Hi SQL Gurus, I have the problem : DATABASE(SUSPECT). This error has happen 3 times on 3 different database. This a Development server where I have about 40 databases, including the system database. Does the Error Log that I attach below give any clue what cause the error ? Many thanks...more >>

I have a .txt file located in a shared directory named 'share' on a server named 'SRV' I would like use the extended procedure xp_fileexist like this: declare @resp as integer exec xp_fileexist '\\SRV\share\filename.txt', @resp output select @resp as response what's the correct syntax...more >>

Hiya Friends! I am currently not at a pc with sql server 2000 so this may seem like a silly question but will the trigger below work? I am trying to create a validation Trigger that is attached to the 'Email' field in the table 'tblPatients', which will raise an error if a user enters an ...more >>