hi How can I use an API function in a stored procedure in sql server 2000? tanx...more >>

Hi I get an error when I try to limit the "digits" I want to summerize, I have only added the line "and digits in (select digits from series)" when it there the it fails and gives the errpr Msg 446, Level 16, State 9, Line 8 The only thing I can imagine is that it's because I'm "joining" w...more >>

Hi, For slightly convoluted reasons I'd like to be able to terminate a users connection from within a stored procedure, in a similar way as if I'd raised an error with a severity of 20 or above. Is this possible? The circumstances are that I have a number of security tests that I run at ...more >>

Rc = SQL_Query( "SELECT TerminalOptions, RRDays, InstallerId, IcePakPhone, Snak, ScreenSetID, TerminalConfiguration, MaskOptions " "FROM Terminal " "WHERE (CustomerID=?) AND (TerminalID=?)", SQL_C_LONG, &lMerchatIdenity, sizeof(long), SQL_C_CHAR, Field[F_TERMID], strlen(Field[F_TERM...more >>

Hi All, I have Downloaded both MSXML 4.0 SP2 and SQLXML. Are they are same. If not can any one please tell me what for that both are used? Thanks Prabhat ...more >>

Hi all, can someone help with the following problem? TBL_PRODUCTS -------------------- Prod_code A B C D .... TBL_FCST ------------ Prod_code FCST_QTY FCST_GRP FCST_PERIOD A 10 1 1 A 20 ...more >>

Hi, Why would a trigger work from everywhere except from where you really want it to work? I have a table with a trigger set for insert. the trigger updates a column from another table. When i insert a row from enterprise manager, query analyzer, another stored procedure, vbscript - ...more >>

Hi there i was wondering if theres a statement where it will compare the items tht has already been selected and saved . I came across "NOT IN" but its not exactly the one i need. Any Advice? P.s. is there such a thing as " IsNotIn" ? Thx. Rgds ...more >>

When I run the below (sp_OACreate) in SQL Query Analyzer it runs fine. When I add the same to a stored proc my app hangs and I get a timeout. Any help is greatly appreciated. I really don't want to create a job that runs this. EXEC @hr = sp_OACreate 'MyLocalEXE.Host', @object OUT, 4 IF ...more >>

I need to take about 300 SP's and strip out whatever permissions are assigned to them - then add one role: db_procexecutor_write to each one. For example - at the bottom of this SP is: ..... go GRANT EXECUTE ON dbo.p_CnsAct_i TO WINADMIN go GRANT EXECUTE ON dbo.p_CnsAct_i TO WINCNS go GR...more >>

Having ported to use SQL server, we're having a pretty severe problem with RI.. to the point that we can't use it. The problem is that we have lots of tables using the following kind of relationship.. paint { paintID int, paintDesc varchar(40) } panel { panelID int, ...more >>

Hi, Could somebody tell me how to import access-files to a sql server database? I would like to import all tables from access to the temp, do some data-manipulation and then insert into a database. I know that dts can import, but i want to address the tempdb so i don't have to create tempta...more >>

Hi (SQL Server 2000) I use the following SQL to retrieve data: SELECT sum(case when LEFT(TR.ACCOUNT,1) = '3' then -TR.AMOUNT else 0 end) Revenue, sum(case when LEFT(TR.ACCOUNT,1) = '4' then TR.AMOUNT else 0 end) Cost, RTRIM(TR.DIM_4) Workorder FROM ...more >>

Hey, Can anybody tell me how to disable a Foreign Key contraint from executing while a Trigger is executing on DELETE event? Best regards, Bill ...more >>

Hi - I have an SP called from ASP.Net, which has a where statement which should read: CREATE PROCEDURE getActivitiesAvailFromList ( @activitylist varchar(4000) ) AS SELECT activity.activityid, activity.activity, activity.description, activity.costperperson, activity.duration FROM ...more >>

We have an application deployed on two seperate machines both reading from the same database. The applications poll the database every few seconds and find the first record in a table that has a bit field set to 0x0. The application then updates the field to a value of 0x1 and then do some pro...more >>

Newbie Question. While using a query/subquery to get a percentage I keep getting zero. I assume this is becuase the number returned is less than 1. How can I make this work right? Example Select 123/12345 returns 0 when it should equal 0.00996 I have tried converting using decimal(...more >>

Hi NG, My indented Bill of Materials (BOM) report has been working for some time now. It is time to deal with the issue of multiuser operations and concurrency. My approach has been to use the tree spanning algorithm suggested by Steve Kass. This stored procedure builds a table called ...more >>

I have a table with around 38000 records in it. I'm trying to delete records (about 3000) from it but it takes forever! There has been several million rows added and removed from this table before but now it's taking way too long (currently 7 min and running). I'm doing delete from my table ...more >>

Hello, I want to make an ALTER COLUMN to change a column's type (say VARCHAR to TEXT), and I need to check first if the given column has not been changed yet (the command will be in a script that will likely be runned more than once and I don't want SQL Server to raise an error if the colum...more >>

Hi, I'm using multiple select statements in MS-Access with logical expressions like (ID=10)*50 instead of an if statement. In this manner i'm able to get the same results as with if statements (if ID=10 then result=50) only faster (in MS-Access that is). when trying to get the same sql stat...more >>

I have a command line utility that calls the MapPoint.NET web service that we use to geocode addresses, during the process I have a table that gets reseeded using DBCC CHECKIDENT, I insert the results of the command line utility into the table.. For some reason the identity column on the table...more >>

I am wondering what is the best way to perform a query where there MIGHT BE a where clause used. I do not want to use an if statement For instance: I have a query where there might be a vendor specific sort If @Vendor <> null then Select blah blah From tblX Where vendor = @Ve...more >>

Hi all, I read some message in FAQ about my problem but i doesn't work any more... I have 2 SQL-Servers that use sql-server and windows security integrity. On first Server, i add a linkServer to the other by using sp_addlinkedserver and use a specified user ... So i can use select com...more >>

I'm an MS person, I love SQL Server, but the one feature I would wish MS would implement is an easy way to get hierarchical information out of SQL Server! Maybe I'm missing something, but I've read a few articles about trees like this one (http://www.developerfusion.co.uk/show/4633/) and al...more >>

create table A ( GroupNum int, Dt smalldatetime, Score int ) go insert A values (1, '1/1/2005', 24) insert A values (1, '1/2/2005', 28) insert A values (2, '2/1/2005', 27) insert A values (2, '2/2/2005', 24) insert A values (2, '2/2/2005', 26) For each GroupNum, report the Score for...more >>

i see a Table Spool/Lazy Spool operation that is turning 20,879 rows into 34,512,987. And then after a Nested Loop/Left Outer Join to 1,653 rows, it is turned into 1,653 rows. My question is, what is the Table Spool/Lazy Spool trying to do? To help explain my question, i can give a similar...more >>

hi , i am querying for data in which i compare nulls . And null = null for my scenario. Thus i need to either use extra where conditions in the query to check if the two compared fields are nulls. This i can do either by using is null or nullif function. Which of the two options will be more ...more >>

Hi all. I am looking at some profiler logging and gets a lot of the lines looking like: exec sp_cursorfetch 180150024, 2, 0, 1 This comes after running a simple prosedure that select some lines. All i VB6. I guess it has something to do on how we retrieve data (ADO). Does anyone have...more >>

We got a new database server (also a Win2003 domain server). Got the databases over there fine, up and running fine. Ultimately we named it the same as the previous server to simplify some of our processes. That worked fine too, and it can be referenced on our intranet as "Server" via SQL, W...more >>

If a VIEW and a STORED PROCEDURE exist and have the same identical name - is there a particular call sequence that SQL SERVER uses - in other words if the particular name is called up in code - does SQL pull the view first or the SP first....more >>

I have a stored procedure that contains a table variable. Declare @xxxx table (columname columtype) In the stored procedure there is a select against the table variable after it has rows inserted into it. When the stored procedure is called via .Net code, we are seeing in Profiler 208 e...more >>

Hi, I was wondering if the amount of comments inside a stored procedure could hinder in any way the performance of the same sp. Does anyone know? regards, ...more >>

Hi, I've been using SQL Server for a while, but I'm fairly new to this area. I would like to log every error that occurs in every stored procedure that I call (actually from an ASP page, but I don't think this is relevant). One of my stored procedures sometimes (as far as I can determine, quite ...more >>

I have a backup of a database, (same structure, data, [updated index]stats), but the execution plan of a stored procedure is completely different on a different machine that I restore it to. I didn't think the query processor used any environment variables during the query optimisation phas...more >>

I have a field which is the primary key in table A, and in table B i have the same field which is the foreign key.When it comes to deleting a record I should kill the child first.If i try to delete the parent then i get an error, which is expected. But if i turn the delete cascade option on, i...more >>

We have a complex query with many aggregate functions. For one column where we return MIN, we would like to return the minimum non-zero value. I don't think using HAVING is going to work, because we don't want to exclude any rows from the result set. We're thinking of doing something like MIN(c...more >>

I have a large SQL table, called Event, that has 4.6 million records in it. My problem is that one particular query on the table is taking over 30 seconds to run. All the other queries that I have take under 1 second to run. I have tried numerous index changes, but it seems that nothing hel...more >>

I imported MySQL tables into an Access MDB and confirmed that the Access table preserved the embedded new line (0x0A) characters within a memo field. I then used an Access append query to insert data into a linked MS-SQL 2000 table consisting of an NTEXT field. The embedded newline character...more >>

Can anyone give me some ideas of how to transfer all the logins and roles from one server to another? I tried running the sql script but doesn't work properly and gives me errors and doesn't show up in enterprise manager tree. now when i try to connect using the login,it gives errors and wh...more >>

Hello folks. I am running a series of queries in a stored procedure. For example, my first query might return a recordset like this Apples Oranges Pears Turnips I want to put those reults in variables So I might have Declare @Fruit1 char(10),@Fruit2 char(10),@Fruit3 char(10),@Fr...more >>

I am a true newbie in T-SQL. I want to write a store procedure to return a row of a table where the date field is less than today. Can someone help please? HS...more >>

My data is like: Proj1 Task1 5 Proj1 Task2 4 Proj2 Task1 Null Proj2 Task2 Null Proj3 Task1 8 Proj3 Task 2 Null I already have figured out the WHERE clause to exclude Proj3. What I want to happen is an UPDATE so that Proj1 Task1 5 ...more >>

Hi, I need to update a table which has almost 300 million records and it is growing. Initially I had clustered index on the primary key (int column) later I changed it to nonclustered index because I don't want to rebuild that every night. But now one of my update statement is taking long t...more >>

Is it possible to access query designer through an interface like sqldmo? Thanks Kurt...more >>

Can Anyone tell me what is Shutdown Sequence 0. I have had three SQL jobs cancelled with this event and am trying to find what caused it. Thanks...more >>

Hey all, I have an Access app that need to be able to Query a Linked Server. The Linked Server points to an Access Database. When I put the Access App on the SQL Box where SQL 2000 and the Linked Database reside, I am able to Query the Linked Server from the Access App with a SELECT or ...more >>

Im trying to write a query that will return the next daily sub number and Volume number for our submissions. the volume number is easy as it is incremented each time, but the Daily sub number needs to be reset each day. Each time a submission is made the numbers are incememted ive come up...more >>

Hello All, I had posted this in the SQLServer.Server group as well. I just want to add that after a reboot of the machine, the files of databases that were in Suspect mode disappeared. I had copied the same file in other location while I was trying to restore the databases. However, even ...more >>

Hi I want to know that is there a system sp or table or somewhere in sql server that I can get ip address or mac address of clients that connect to sql server? thanx...more >>