What is the proper way of handling apostrophes in parameter strings for stored procedures? -- Regards, Fred Chateau fchateauAtComcastDotNet ...more >>

In a web based application I have a dropdown that gets populated based on what state the user selects. I am trying to rewrite the procedure so that it will select from one table if the state = VA and another table if it is not VA This is what I am working on, but cannot seem to get the syntax c...more >>

Is there some tool that I can run to give me a report of all my function and proc calls for certain top level routines? I know things like that exist for other languages, but not sure about Sql Server 2005, particularly since all the code is in the database, not in files. I'm getting los...more >>

Hi, I need to insert a new record, which is a copy of a record. I chose to create a dynamic s.p. to create the insert. -- 1. build dynamic insert EXEC sproc_GetFields 'mytable, 'id' ,@strFieldList OUTPUT SET @strDynamicInsert = 'INSERT INTO EVENT SELECT ' + @strFieldList + ' FROM mytable...more >>

Hi, I have 2 tables Table A id prod qty Table B id prod qty I need to copy all data from table B into table A where the id = x and prod = y and they do not exist in table A. At the same time I need to update the qty in table A with qty of table B where the id = x and prod = y i...more >>

Is there a better way to writ this stored proc: ALTER PROCEDURE dbo.GetUsers @UserId int AS BEGIN DECLARE @IsRole int SELECT @IsRole = RoleId FROM [User] WHERE [UserId] = @UserId If @IsRole=1 SELECT u.[UserId], u.[FirstName], u.[LastName], u.[UserName], u....more >>

Hello, all I am preparing a CLR stored procedure in C# that talks back to an Oracle database via the OracleClient. This procedure connects to an Oracle server, performs a query, and returns a converted set of rows back to the calling SqlServer across the context pipe. When the assembly is c...more >>

I have a situation where an application calls a proc (ProcA) and within ProcA a second proc is called (ProcB). In ProcB, an error is raised like so: RAISERROR('This is my error', 16, 1) When that error occurs, I need to know this in ProcA. I realize I can figure it out like so: IF (@@E...more >>

This query will return MealId MealQuantity only when there is a value for MealQuantity. is there a way to return all MealId for the condition in Where clause even when Quantity for that MealId is 0. SELECT MealId, MealQuantity FROM RegistrationMeal WHERE [MealId] IN (SELECT...more >>

Hi, --Table Schema with some records Create table rptTest ( State nvarchar(2), CCode nvarchar(30), TTCode nvarchar(14), Dt varchar(30), TotalCount int null, RCode nchar(3), Reason nvarchar(255) ) Go Insert into rptTest Values ('CA', 'CReport','XYZ','2007.06.01',2,'000','test') In...more >>

I would like to write an SP that would delete all sql server agent jobs that have 'Last Run Outcome' = Succeeded and next run date is 'not scheduled'. Finally I will schedule this SP to run daily. Any help will be greatly appreciated. Thanks ...more >>

When I run the following query it gives me the following error Column 'UserID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. SELECT uar.UserID, dbo.udf_strAppUserRolenames(1, u.UserID) as RoleName FROM t_UserAppRoles u...more >>

Hi, i am trying to call a stored procedure from VBA code but it is returning 0 rows. When i run from the frontend (Access 2007) its fine, i dont understand what i am doing wrong. The inline function is below: ALTER FUNCTION dbo.GetPayrollDetails (@Start smalldatetime, @End smalldatetime) RET...more >>

Is it possible to create an index based on an expression? Thanks ...more >>

I am new to SQL and need to Update a table. Can someone check my work? UPDATE dbo.TABLE 1 SET LoanSTUFF = '(LOANPROGRAM <> BRIDGE)' WHERE dbo.clsProperty '[PROPTYPE]' NOT IN (2010, 2020, 2030, 2040, 2050) MasterCheckListID = 6 MAIN TABLE IS TO UPDATE IS TABLE 1 AND IF THE CODE IN T...more >>

I have a calendar table but need the logic to determine if it is the 5th business of the month. I want to set @FifthBusinessDay to either Y or N. Can someone help? DECLARE @CurrentDate datetime DECLARE @FifthBusinessDay char(1) CREATE TABLE #Calendar ( CalDate datetime NOT NULL , Bu...more >>

I have inherited a billing system that needs a lot of help. The first thing I usually do to improve purformance is eliminate all cursors, but I may need to keep this one unless one of you can provide a creative solution. The last time this cursor ran it took 39 hours!! There has to be a better...more >>

I have inherited a billing system that needs a lot of help. The first thing I usually do to improve purformance is eliminate all cursors, but I may need to keep this one unless one of you can provide a creative solution. The last time this cursor ran it took 39 hours!! There has to be a better...more >>

ACCESS Front End SQl Server 2k Back End The application is a perfectly straightforward MS Access MDB file that is linked to a SQL Server database on a LAN. The application has been stable for six years. However, earlier this month the SQL Server box crashed, owing to another database growi...more >>

if exists (select * from tblziprequest where zipProcessed is null) begin select 1/0 end else begin select 1/1 end What is the purpose of the "select 1/0" or the "select 1/1"? Thanks -- Dan D....more >>

What's the minimum rights I should give to user that can do nothing at all but only execute sp. Thanks...more >>

Is there a way to specify in SP that if I am doing select * from abc where param = 'something'. I want to make sure it will only return one row. I know I can do this by doing top 1. But I want to specify in sp to output or return only one row. Thanks...more >>

I know in SQL you can write a query like - Where price > 123 Is it also possible to write a query like Where name Begins > B Which would produce a list of all names that begin with A for instance??? Thanks a lot...more >>

I've got an odd-shaped CSV file as follows: 0,, 1, So it has three columns, but some rows don't have the delimiter. How do I import this? Thanks...more >>

Hi, I've got a curious problem. I've got 4 SQL-Servers (SQL 2000/Win2003). There is a network share on every server, Full-Access vor Everybody. I can see this shares in Explorer, I can copy/delete Files (using for example DOS-Box). I can fire a "xp_cmdshell" stored procedure form server04 "xp...more >>

Hi, i tried to estimate a index, based on the help http://technet.microsoft.com/en-us/library/ms178085.aspx But this doesnt work. There seems to be a error in Step1 of this site. my Example: num_Rows = 2685791 num_Key_Cols = 6 fixed_Key_Size ...more >>

Hi, I have a very simple query like this one: SELECT a FROM rates WHERE a IS NOT NULL 'a' is a column with int values. I have also a 'b', 'c' and 'd' column with int values. When looking to each record, then only two columns are used with int values (the other two have a null value). ...more >>

Hi all, I have just finished reading an article about performance tuning SQL Server 2000. The author mentioned that a good technique is converting hash-join queries to inner loop queries. I don't understand what that means. Can someone point me to an article or book chapter that explains t...more >>

Hello I have a large database, and I have created a user that only should execute created stored procedures. How I set that up so that user only have the right to execute stored procedures (or selected stored procedures) using the sql 2k5 management studio? TIA /Lasse ...more >>