Hi, I have a stored procedure which simply does a Select count(*) from sometable where ID=@SomeID whereas @SomeID is a parameter of this sp. Since the table is tiny (5 rows) and only has a couple of columns execution should be pretty fast. Oddly though it takes the sp almost 2-3 sec. return...more >>

ok, i'm at an impase regarding t-sql procedural progamming. I'm trying to determine whether code certain logic in an stored procedure or a dll. SP is nice idea maybe cause I can modify it more easily than a dll. However, I would like to use 'case' instead of 'if ... then' Unfortunately, I wo...more >>

Hi, I just figured out that a sp has been deleted on my SQL Server 2000 and I want to know who did it. Is there any possibility to find out? Thank you! Daniel ...more >>

Hello, Is it possible to SELECT INTO a Temporary table that has been declared as a variable? I can only seem to insert into session temproary tables declared with a # sign. Thanks. -- Lucas Tam (REMOVEnntp@rogers.com) Please delete "REMOVE" from the e-mail address when replyin...more >>

This is going to be one of those questions I'm affraid... I was trying to tune a user defined table value function that I am writing. In the pursuit of this, I copied my function code and pasted it into a fresh query analyzer window, changed the parameters and return statments to declares, a...more >>

hi, how could i make IN behave like LIKE operator. for instance when you say WHERE tbl.A IN("blabla","bla") it looks for definite equality. what i want to do is WHERE tbl.A IN('%blabla%','%bla%') regards... ...more >>

Hi, guys I try to add some error check and transaction and rollback function on my insert stored procedure but I have an error "Error converting data type varchar to smalldatatime" after I excute the code. if i don't use /*error check*/ code, everything went well and insert a row into contract ta...more >>

Hi! I wrote a piece of code: DECLARE @tr char(30), @tab char(30) DECLARE c1 CURSOR FOR SELECT trname=a.name, tabname=b.name FROM SYSOBJECTS a JOIN SYSOBJECTS b ON a.parent_obj=b.id WHERE a.xtype='TR' OPEN c1 FETCH NEXT FROM c1 INTO @tr, @tab WHILE @@fetch_status = 0 BEGIN ALTER TABLE...more >>

Dear all, This query, which extracts unique rows from one temp table to another, seems to have performance problems. Is it the 'not in' statement that's the problem? Is there a better way to write this? select * into #matched from #matched_raw where id_number not in ( select id_numbe...more >>

Can I use case during update ? however, i got several column may need to be updated (which depends on the case parameter) it seems wrong syntax if my code like this :- update mytable case @type when 'A' then set a = a + 1 when 'B' then set b = b + 1 end Thanks from agnes ...more >>

Hi all, I have 'MyDB' database is created with collation : Latin1_General_CP1_CI_AS Default Sql Server Collation : SQL_Latin1_General_CP1_CI_AS Within a stored proc in MyDB I create a #TempTable with 'COLLATE DATABASE_DEFAULT' statement. As I know #temporary table will follow collation of ...more >>

I am trying to write to the registry via query analyzer with an sql server extended stored procedure (xp_regwrite) which, on all other on a different domain server, works without incident. However, on all servers on one particular domain, the following error results: Server: Msg 2200...more >>

Hello: I have my .NET application and my SQL database. I am using stored procedures in SQL, and for these ones I am setting the ISOLATION LEVEL READ COMMITED, this fact in some moments could delete my table? I am losing records in my database, and there's no reason for that, so I am tr...more >>

Hello. I have the following sql sequence in my ASP file: conn.Execute "INSERT INTO OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=c:\myfolder\;Extensions=asc,csv,tab,txt')...myfile.csv SELECT Autoid FROM tbl_CLASSES" where "conn" is a connec...more >>

Hi, What is the formula I should use to auto-insert the current timestamp into a smdt field? Similar to newguid(), but for the current timestamp. Is there a website that contains the available formulas that I can reference? Thanks Brian ...more >>

Hi all, I'm executing 3 command batch scripts against a database with the Database.ExecuteImmediate method. Before I call the method 3 times with each different script, I issue a Server.BeginTransaction and obviously end it with a suitable Commit or Rollback as necessary. I want to apply...more >>

Hi All, We are running SQL Server 2000 at Windows 2000 Server. I Just realize that with 'sa' login from query analyzer from client computer , it can execute sp_cmdshell and some 'Net bla bla comannd' create new Windows user , assign administrator to it and then become 'god' with that user acc...more >>

Hello all ! Let's say we have Calendar table like this : CREATE TABLE #Calendar ( Day int PRIMARY KEY IDentity, Date datetime NOT NULL ) GO --I need to fill it with dates within certain range e.g. '01-01-2004' and '05-01-2004' --I know how to do this using cursor or WHILE. Somethin...more >>

Hi all, I have a table that has a customerkey column and a factorkey column. I need to query to find out how many of the same customers are associated with Factorkey 1, and Factorkey 2. Can someone point me in the right direction? TIA. ...more >>

Hi, I've got this problem: I use a stored procedure to retrieve a ResultSet...I have to call this stored procedure inside another one, how can I manage this ResultSet?? What's the T-SQL type to manage the RsultSet??? TIA. ...more >>

How can I set filed to identity by using sql script? ...more >>

I'm not sure if this makes much of a difference, but the question came up at work today and I don't have a solid answer and am hoping someone out there can explain. For years of using MS SQL, I have seen and generally preffer to use the equal sign when aliasing fields in the SELECT clause. For ex...more >>

Hi, Is it possible to make view (I need it to be view because of client application) which will add row to empty recordset with text "No records returned" when query returns no records? I'm using SS2000 Enterprise edition SP3 on Win2000 Advanced Server SP4 Thanks in advance Nikola Milic ...more >>

Hi guys I have multiple values, and I need to insert each of them into a table this way: insert ( ... val1) values (... val1) insert ( ... val2) values (... val2) insert ( ... val3) values (... val3) I want to create a stored proc that would accept all these values as a parameter, so that...more >>

I would like to ask how to check if a table exsit in the database I use this query if exists (select * from Test) drop table Test; It does not work. Thanks for pointing out my error ...more >>

Using the Copy SQL Server Objects Task inside a DTS package - I am = attempting to refresh my DEV, TEST and Pre-Prod environments from my = Production environment. I only want to refresh the data. This task seems best suited once I = click off all the dependancies such as copying the accompan...more >>

Hi! I'm very 'fresh' in DB programming and have one problem... The problem is that i am to write a function to disable all triggers for some time. My idea is to store triggers' creation codes in a seperate table using sp_helptext, delete triggers and after some time use those codes to restore ...more >>

I have two queries: SELECT DISTINCT c1.hin, c1.refer_phys_id, c1.refer_phys_last, c1.refer_phys_first FROM network_charts n1 INNER JOIN claims c1 ON c1.hin = n1.hin WHERE (c1.visit_date = (SELECT MAX(c2.visit_date) ...more >>

DBCC SHOWCONTIG(information) I GOT: DBCC SHOWCONTIG scanning 'information' table... Table: 'information' (1675921092); index ID: 1, database ID: 14 TABLE level scan performed. - Pages Scanned................................: 51390 - Extents Scanned..............................: 6467 - Ex...more >>

Whenever I join to more than one table with my FOR XML, it ends up nesting the nodes more levels than what I want. For instance... SELECT Member.memberID, Member.memberName, Job.jobID, Job.jobName, Agency.agencyID, Agency.agencyName FROM Member INNER JOIN Job ON Job.jobID =...more >>

On Sql Server 2000, I have a stored procedure that I am trying to get data back to my other programs and have used various methods to get this. The following 2 send it back fine. select STR(scope_identity()) print "at the end of the routine " + STR(@lastIdentity) But I get nothing back...more >>

Hi, I am trying to run some vbscripts on my computer, but I always get the following failure. Can anybody suggest a way out of this problem? TIA Bob The Error popup contains Script: small.vbs Line: 4 Char: 1 Error: 0x80041002 Code: 80041002 Source: null I am running ...more >>

Hi All, I have set the recovery model to bulk logged and when I run insert into select from another table(there is about 100 Million rows to be inserted) - the transaction log fills up. I have also tried with recovery model simple. The only way that I have been able to get data into the ...more >>

Hi! We are running our SQL Server on a computer with eight CPUs. We would like some of our larger stored procedures to be run in parallel using all the processors. How would I need to change SQL within the stored procedures to do so please? (max degree of parallelism on the SQL Server is alre...more >>

Greetings, I am computing number of hours depending on the date speficied. Here is my sample SQL: Select ID_Jobs, Convert(Float, Max(StopTime) - Min(StartTime),) as Duration from Jobs Group by ID_Jobs But it is not resulting to number of hours passes just a decimal point what must...more >>

I notice a lot of questions here each week regarding how to number the rows returned from a query. These questions confuse me, as I can't think of any legitimate business purpose for dyanmically numbered rows. I've worked on a variety of financial, CRM, and e-commerce apps and have never had th...more >>

This is my first Transaction and would like to know if I am on the right track: Begin Transaction Insert Statement If (@@Error <> 0) Goto OnError Second Insert Statement If (@@Error <> 0 ) Goto OnError Commit Transaction OnError: If @@Transcount > 0 ROLLBACK TRANSACTION I ha...more >>

Howdy, We've been having an odd erratic problem that I hope someone has an idea where to look for the cause: sometimes a procedure runs quickly and sometimes it takes (really) hours. Ok, it's a fairly intensive view update routine that we run in the middle of the night, works basicall...more >>

Is there a good way to change ownership of many tables, views, udfs and sprocs all at the same time? ...more >>

I like the result set using sp_helpindex 'tablename' How can I retrieve these results for, as an example, all the tables in the Northwind db?...more >>

Hi, I encounter an error with the codes below: Create table #tmp (rt_dt datetime, eod_typ char(6), cur_cde char(3), day_unit char(9), no_unit integer, dy integer, b_rt numeric(11,4), o_rt numeric(11,4), end_dt datetime, frz integer, tm datetime, clndr_bs char(6)) Insert into #tmp select ...more >>

I have a select statement that runs in two seconds on one server and does not complete running after twenty minutes on another server. I copied the database from the first server and restored to a new server. The two servers have the same SQL 2000 sp3, and I ran DBCC USEROPTIONS and received the s...more >>

I have a sproc that returns 2 columns; name and id. I'm currently using an "order by id" in the sproc. I need to change it to "order by name" but with one exception. My query looks similar to this: select name, id from table union select name='none', id=-1 order by id The reason I'm so...more >>

I have the following query: select Ethnicity, Count(CaseNumber) as CountOfCaseNumber, PalmName from tblClerkshipDataClean group by Ethnicity, PalmName order by Ethnicity, CountOfCaseNumber, PalmName It returns the following results: <Ethnicity> <CountOfCaseN...more >>

I have SP: CREATE PROCEDURE sp_Upload @idOrder varchar(10), @result text AS UPDATE t_narocilnica SET description=@result+description WHERE ID=@idOrder Description is text field in table t_narocilnica. I get an error message: error 403: invalid operator for data type. Operator equals ...more >>

Hello all, This part "datediff("mi",Starttime,Endingtime) as [Hours]" returns total minutes in the below query but, I want it to give me hours and minutes like this. Examples: The first two numbers hours the second set of two are minutes. 01:25, 03:45, 14:55, 00:45, SELECT Examda...more >>

Hello, I have a stored procedure in SqlServer which contains the following code: INSERT INTO TBLASFAL(COLA) EXEC sp_executesql @QUERY @QUERY IS A VARIABLE CONTAINING A SELECT STATEMENT THE PROBLEM IS I WANT TO INSERT A VALUE IN THE SECOND COLUMN OF THE TBLASFAL TABLE WHICH IS ALSO ...more >>

Hello all, we are currently evaluating the use of INSTEAD OF Trigger for our Application and found out that it could simplify some of our work. BUT: I have a table with an INSTEAD-OF trigger for insert. Every now and again I get strange Errors from SQL Server and the Connection closes dow...more >>

Hi, I' like to ask if someone kows how could be saved an image filepath in DB instead the whole IMAGE using ASP.NET/S#/MS SQL? Let's say we have: PicturesTBL ---------------- ID INT PIC IMAGE Could be use PIC VARCHAR(50) or something to save the filepath and to store the ...more >>

Background: sql 2k on nt5. MS access 2000 I have a column in MS Access 2000 table, data type is Double. Value is 0.29. I imported the record into a SQL 2000 table, data type was set up as Real in sql. When I looked at the record in EM table itself, it shows 0.29. But when I switch...more >>

My apologies if this has been asked a thousand times already... I'm building a simple document management system. My current solution stores documents directly in the database. However, I'm wondering what the advantages (disadvantages) are to this method vs. storing the documents in the file s...more >>

Hello Experts, I use a stored procedure to do multiple inserts in lots of tables which tables have triggers that do some checks, I do some selects to hold maximum codes and etc. All these are in a simple transaction (not distributed), at the end of the sp and if batch completed successful...more >>

Hello, I have a query extracting data from one table, which returns inconsistant data every time its run. The query is working on just one table (no joins) and the data is fairly static (not being constantly updated/addded to etc). I think i have narrowed it down to the IN clause : f...more >>

Im recently started using stored procedures and have inherited many for a project im taking part in. I'm having difficulty working out all of the statements tho. Could someone please tell me what the following means when it is placed at the bottom of a stored procedure. IF @@ERROR = 0 RETURN ...more >>

Hi, I want to learn about performance tuning in SQL and therefore need some test data. I thought about inserting data in a table as follows: This approach unfortunately performs very badly and I already got stuck by creating my test data :-( declare @i int declare @j in...more >>

Can someone please tell me what I'm doing wrong with this statement! Thanks! ...:: CODE CREATE PROCEDURE PageDelete ( @ID Int ) AS DELETE FROM tblOfficePageContent FROM tblPageContent P INNER JOIN tblOfficePageContent O ON P pageID = O pageID WHERE P pageID = @ID...more >>

Hi I have sql stored procedure like this begin tran sql statements.... if @error <> 0 begin Rollback Tran end else commit tran What should happen is that if there is an error then the rollback will happen i.e no tables will be updated. Now what happens if a rollback gets aborted? ...more >>

Hi We have a weird problem with our SQL Server 2000 Enterprise Edition SP3a. We have several parallel running procedures. They are using Cursors (always declared as local), Temp tables, User Defined Functions and so on…. Now we have under some circumstances the problem, that some of the S...more >>

Considering the number of solutions for this type of problem in the newsgroups, I feel a little dumb asking this question. Howerver, I am trying to parse each character out of a string of up to 10 characters. I can put each character into its own colum but I need them in a row, and there has t...more >>

I have a table with 3 columns: Name, Date1, Date2 I also have stored procedure which calculates time interval between Date1 and Date3 and returns it in hours as 2 colums: Name, Hours I would like to build a query which can return me aggregated data in two columns: col1, col2 'names with values b...more >>

i am new to SQL Profiler. Can i trace all the activities for a particular table ? Also specify any links for Profiler basics Student ...more >>

Hi, Can someone tell me how I Delete a record from multiple tables! I have a linked record which I'm not sure how to delete. tblpage pageID PK Content tblOffice OfficeID PK Name tblOfficePage pageID Office ID I know how to do a basic delete but I don't know how to get link recor...more >>