Using SQL Server 2005, I'm trying to convert the following Stored Procedure to a Function. Is this allowable (Exec string) in a Function. TIA Stored Procedure that works CREATE PROCEDURE [dbo].[ExecuteMyTableFormula] ( @appid varchar(50), @parm varchar(2500) ) AS declare @cmd varch...more >>

Hello everybody, How can i solve this problem ? Thanks. USE EXPERT GO sp_createstats 'fullscan' GO sp_updatestats 'resample' GO Server: Msg 170, Level 15, State 1, Line 2 Line 2: Incorrect syntax near 'GO'. use Master GO DECLARE @NAME NVARCHAR(255), @SQL NVARCHAR(1000),...more >>

If one stored procedure has user raised error, how do I catch the message from another stored procedure? This is can easily done in SQL server 2005 by using BEGIN TRY.. BEGIN CATCH... but, is this can be done in SQL Server 2000? Thanks Bob *** Sent via Developersdex http://www.deve...more >>

I have a "parent" table and "child" table - a one to many relationship. "Child" has 44 columns. I have been requested to add 25 more columns at the child table level. But they'll only be used about a third of the time, so I'm considering a separate table - I'll call it "new". Thinking ahea...more >>

Is there a crafty way, using SQL Server 2000, to use an index seek along with the DIFFERENCE clause. The following clause "WHERE DIFFERENCE(LNAME, @LNAME) > x" performs an index scan on the index of the LNAME column. This takes quite a long time to complete. Thanks in advance. ...more >>

I have 2 tables that have a foreign key constraint. Table1 - Col1 (PK), Col2, Col3 Table2 - Col4(PK), Col1(FK), Col 5, Col6 There is a one-to-one relationship. I now would like to delete rows from Table2 where Col6 is null. At the same time, I would like to delete rows from Table1 based on ...more >>

I have question about @@fetch_status.. if I have a bunch of procedures using cusors and we have a bunch of users executing different procedures, what will happen to @@fetch_status? I think @@fetch_status is global and shared by all users. but this mean it could mix up since procedure execute...more >>

SQL 2K I was looking at the system tables to find out the size of all my stored procedures and couldn't find one. I found the undocumented system SP :- sp_MSindexspace , but this will work only for tables and indexes. Any help will be appreciated. Thanks Mike ...more >>

Hello, I am writing an app in VB2005 which mostly pulls lists of data for data analysts - concurrently. The old app writes to several physical tables while processing the queries/criteria/filters... I want to reduce I/O on the server and reduce the number of intermediate tables/SPs... I ...more >>

Hi all, Have a table with over 5 million rows, and I want to delete 1.5 million rows based on a date. What would be the best way to do this other then? Delete from tablename where date is <= somedate I don't want to bog the system down, how would I do it in chunks of li...more >>

Need some help. I will post more table info if needed but i think i can explain enough to omit that information. I need to divide the sum of 2 numbers. For the following query part A has a recqty of 10 on one day and a rec qty of 5 another. For that same part there is a paintqty of 9 one da...more >>

I am using a MS-SQL 7 database, and have a query that pulls information by a start and end date. The start and end date in the query are definied by variables @startdate, @ enddate. What I would like to do is have the code setup so that the startdate will be always be the current date at 12:00...more >>

Hello, I have two tables- TBLINSPECTION with the following fields- BARCODE ITEMTYPE PASS DATEINSPECTED 1000 x YES 01/12/2006 1001 x YES 02/12/2004 1002 x YES 03/12/2006 1003 y NO ...more >>

I have a SQL 2000 table in development and in production (two different servers). Other than doing a sp_help on each and eyeballing them, is there a way I can compare the two to ensure they're the same? Thanks....more >>

I am trying to execute a query, but its giving error. Please find below the sql query SELECT pollvalue,(SELECT COUNT(pollvalue) AS Expr1 FROM tblibcpollresults GROUP BY pollvalue) AS Expr2, (SELECT COUNT(pollvalue) AS Expr3 FROM tblibcpollresults AS tbl...more >>

I have a table with a bit column for each Day of the Week. I am setting a schedule up and need to exclude this row if that bit column for that DOW = 0. Below is some test code to determine which col I want: select DATEPART ( dw , getdate() ) , case DATEPART ( dw , getdate() ) when 1 t...more >>

SQL Server 2000 I have a database containing over a dozen tables. Each table has dozens of dependent query objects (views, stored procedures, functions). It's easy to rename a table using Enterprise Manager by directly editing the name. SQL Server internally knows which objects depend upon th...more >>

Hi, I have a problem with updating a single line. This is very slow because when it updates the field « fk_tbl_gmt_id », it recreates all indexes of the database, including the views! That's my statement: update tbl_users set companyName = 'None', name = 'Steph', password = 'pa...more >>

Hi I have the following ASP.NET code to try to set time out to 10 miniutes. But when I run this code, it will time out within only 2 minutes. How to solve this issue? Help please. ' get ExistingconnString from some existing connection connectionString = ExistingconnString + ";Connect Time...more >>

Which is normally a good thing but in this instance I need to do some testing. I have tried these two commands in two different Query Analysers, and executing them both concurrently gives the expected dead lock. BEGIN TRAN update sf_covernote set uid = (select case uid WHEN 'uid1' T...more >>

Sorry for what I'm sure must be a dumb question, but how does mssql know when it has reached the end of a command? For instance, how does it know the difference between UPDATE mytable SET myfield = 0 and UPDATE mytable SET myfield = 0 WHERE <some condition> How does it know the di...more >>

Hi --- In the query below, what does the capital N do before the strings? (i.e. name = N'vw_aspnet_MembershipUsers' AND SELECT N'aspnet_Membership') Thanks! IF ((@TablesToCheck & 1) <> 0 AND (EXISTS (SELECT name FROM sysobjects WHERE (name = N'vw_aspnet_MembershipUsers') AND (typ...more >>

Sorry if this has already been asked elsewhere. I need to be able to select a field from the row in the inserted and deleted tables within an update after trigger so that I can send the value of that field as a primary key onto a stored proc. example: @field_id = SELECT field_id FROM inse...more >>

In asp.NET, I submit a select query using SqlCommand. It will generate a S-lock in database, after that I submit a update request that will cause a X-lock. If the select query cause a long time, it will cause Update to wait the S-lock to be release, otherwise it can't apply X-lock on same o...more >>

I have a cmd file that calls winrar to unzip some files downloaded nightly. I can call the file, but sql can't seem to find the winrar program. It keeps trying to force a direct path to the windows directory. Anyone else have this issue? -- thanks (as always) some day i''m gona pay this foru...more >>

I'm reading 'Inside Microsoft SQL Server 2005: T-SQL Programming' and am looking at the Auditing Example in Chapter 8. **************************************** create table dbo.t1 ( keycol int not null primary key, intcol int null, varcharcol varchar(10) null ); go create table dbo.t1A...more >>

Hi if any one can help me with these two issue's I would greatfull. 1st Issue I am trying to joint two tables together from our Sage MMS sql database this I have no problem with The tables I am Joining are Stockitem & Warehouseitem my question is once the tables are joined together I am tryin...more >>

Hello :) Can anyone help me write an SQL query please ? I got this table of events done by users: =20 PK_Event FK_User Event_Time =20 1 John 19:51 =20 2 Peter 19:52 =20 3 John 19:53 =20 ...more >>

Hello :) Can anyone help me write an SQL query please ? I got this table of events done by users: PK_Event FK_User Event_Time 1 John 19:51 2 Peter 19:52 3 John 19:53 4 Ann 19:54 5 ...more >>

Hi, Is it possible to combine multiple databases into a single backup set, instead of backing up and restoring them separately? I would like to treat the "catalog" of databases as a single entity when it comes to making and restoring backups. Thanks, Robin ...more >>

I have a SQL CLR .NET stored procedure, I need to report back an error code if something goes wrong, how would I do that in SQL CLR? I know RAISE ERROR in T-SQL but is there a specific way to do it in SQL CLR Procedures? Also how would I pick up the error raised in a .NET application? thanks! ...more >>

HI all, Is there a way to create a global trigger for all tables in a database. Say I want to create a strict audit trail on all tables. I have one global audit table. So that when any table is altered, that alteration is then recorded, but for all tables. I would traditionally have a trigge...more >>