I donot know how to use SQL NOCOUNT . I have written store procs for select and insert , Below are the store procs ,Can anyone tell me how to use SQL NOCOUNT in this StoreProcs???
Select:
if exists (select * from dbo.sysobjects where id = object_id('[dbo].[GetAllLinks]') )drop Procedure [dbo].[GetAllLinks]
GO
CREATE Procedure [dbo].[GetAllLinks]
AS
Select * from UsefulLink
Go
Insert Storeproc:
if exists (select * from dbo.sysobjects where id = object_id('[dbo].[InsertLink]') )drop Procedure [dbo].[InsertLink]
I am not so in database administration and I wold like to get some glue how to do what I am looking for. I have a set of procedure and view in my Database HISTORY, then I define Grant the Execute rights to all those procedure for user NTAuthorityNETWORKSERVICE.
Then I have a second database named CONFIG where table might be accessed by store procedure of the database HISTORY. So first of all, in addition to set the EXECUTE grant access to my strore proces of database HISTORY, I need to set the SELECT grant access to all tables from database CONFIG on which are access by my storeprocs of database HISTORY.
First of all is it correct to set rights like this ?
Second, is there a way to set the access right of all my desire procedure in one shot ( script or something else ) becasue I have doen it manually and its painfull ? If yes how to do that
We are in the midst of cleaning up database01. There are objects in database01 that are widely used in store procedures that reside in database02.
Problem is: I need to know which tables and view from database01 are in use by store procedures in database02. This is important because if I accidently decommission tables that are currently in use by production class s-procedures, it will not be pretty. A sample output could look like:
I am not a programmer and none of our programmers here claim that there is any solution to this problem without spending thousands on s/w licenses. Any simple solution + code snippet that will help me resolve this problem by myself would be incredibly valuable.
If I understood correctly from some reading SET NOCOUNT ON is use so that my sp does not return the number of row affected and this should improve greatly the performance of my sp. I should use if for all insert, delete adn update sp. Is this correct? Is there any situation I need to have it set to OFF? What about if I only do a SELECT * FROM Table, is there any point to turn this ON? Thanks.
Hi, i have the next problem... I work with: - Sqlserver 2000 - Web server, Tomcat. - JDBC. - Java Aplication.
I notice that when I call a store procedure from the aplication java, I usually set no count on in my sp because of some exception errors in my aplication. But I have one special version of a jdbc that it doesnt work with this parameter. I wish it could be a parameter of the database that is set by the jdbc, but i´m not sure.
i useset nocount on inside the stored procedure at the beginning however theNumber of Rows Counted/Affected shows up when I execute the storedprocedure in query analyzer using execute sprocName. I also tried toadd theSET NOCOUNT ON at the beginning of the procedure and it still shows thenumber of rows affected.if i setset nocount onexec sprocName then the result set does not show the number of rowsaffected.Any idea why this happens? I know that NOCOUNT is set on runtime notparse time.Thanks!
What I am Trying To Achieve I am trying to get the ID of the row I am inserting - using code that works fine on: SQL Server 2000 with an ODBC connection and SQL Server 2005 with an ODBC connection
Problem When the code is executed it inserts the row 4 times.
My Settings I have the following OLE DB connection string:- Provider=SQLOLEDB.1;User ID=myuserid;Initial Catalog=dbname;Data Source=dbserveraddress;User Id=myuserid;PASSWORD=mypassword;
and I am executing the following SQL:- session("sessionID") = dbExecuteRS("SET NOCOUNT ON;INSERT INTO myTable (ipAddress) VALUES('"& request.serverVariables("REMOTE_HOST") &"');SELECT SCOPE_IDENTITY();SET NOCOUNT OFF;")(0)
dbExecuteRS = a function that executes a query
I know my asp page is only executing this sql once but the SQL Profiler says it is running multiple times.
What I've Tried So Far 1) A normal insert works fine - only inserts one row 2) The same SQL with an OLE DB connection string on SQL Server 2000 encounters the same problem - the insert sql with the set nocount on inserts 4 rows 3) I've tried using this bit of SQL/ASP which causes the same problem:- set tmpRS = dbExecuteRS("SET NOCOUNT ON;INSERT INTO mytable (ipAddress) VALUES('"& request.serverVariables("REMOTE_HOST") &"');SELECT newID = SCOPE_IDENTITY();") if not tmpRS.eof then response.write("tmpRS('newID'): " & tmpRS("newID") & "<BR>") end if set tmpRS = nothing
Conclusion From all of my tests I can conclude that this problem ONLY happens when we are connecting to the database via the OLE DB connection string and using an INSERT statement with SET NOCOUNT ON
I have a SP that has a cursor, and loops. Each loop executes SQL, and inserts values into a temp table on each loop. I want to just have the values in the temp table to show up in QA not the loop's sql results.
I thought:
SET NOCOUNT ON at the begining of my proc would suppress the results, and then
SET NOCOUNT OFF prior to my select on my temp table would do this, but the resultset of the loop still shows up in QA. It does suppress the count values on the message tab of QA, but that is it.
How to configure replication so that replication triggers issue a SET NOCOUNT ON?
Also: are there any negative consequences of adding a SET NOCOUNT ON to replication triggers?
I'm not a DBA so my experience with replication is pretty much non-existent. Hopefully, there's a simple configuration option that I can apply when setting up replication for a database. If not, is it going to be feasible for me to write a script that runs over the replication triggers/procs to add this SET statement (any pointers on inbuilt procs that would help here would be real useful)?
Is there a way to namespace groups of stored procs to reduce confusion when using them?
For C# you can have ProjectName.ProjectSection.Classname when naming a class. I am just wondering if there is a way to do the same with SQL stored procs. I know Oracle has packages and the name of the package provides a namespace for all of the stored procs inside it.
CREATE PROCEDURE PROC1 AS BEGIN SELECT A.INTCUSTOMERID,A.CHREMAIL,B.INTPREFERENCEID,C.CHR PREFERENCEDESC FROM CUSTOMER A INNER JOIN CUSTOMERPREFERENCE B ON A.INTCUSTOMERID = B.INTCUSTOMERID INNER JOIN TMPREFERENCE C ON B.INTPREFERENCEID = C.INTPREFERENCEID WHERE B.INTPREFERENCEID IN (6,7,2,3,12,10) ORDER BY B.INTCUSTOMERID
END
IF I AM USING THIS PROC AS I/P TO ANOTHER PROC THEN IT GIVES NO PROBLEM AS I CAN USE ?
BUT IF , I USE ANOTHER PROC SIMILAR TO THE FIRST ONE WHICH GIVES SLIGHTLY DIFFERENT RESULTS AND GIVES TWO SETS OF RESULTS,THEN WE HAVE A PROBLEM,HO TO SOLVE THIS :-
CREATE PROCEDURE MY_PROC AS BEGIN SELECT A.INTCUSTOMERID,A.CHREMAIL,B.INTPREFERENCEID,C.CHR PREFERENCEDESC FROM CUSTOMER A INNER JOIN CUSTOMERPREFERENCE B ON A.INTCUSTOMERID = B.INTCUSTOMERID INNER JOIN TMPREFERENCE C ON B.INTPREFERENCEID = C.INTPREFERENCEID WHERE B.INTPREFERENCEID IN (23,12,10) ORDER BY B.INTCUSTOMERID
END
SELECT A.INTCUSTOMERID,MAX(case when A.intpreferenceid = 23 then '1' else '0' end) + MAX(case when A.intpreferenceid = 12 then '1' else '0' end) + MAX(case when A.intpreferenceid = 10 then '1' else '0' end) AS PREFER FROM CUSTOMER GROUP BY A.INTCUSTOMERID ORDER BY A.INTCUSTOMERID END
WHICH NOW GIVES ME TWO SETS OF DATA , BOTH REQUIRED THEN HOW TO USE ONE SET OF RESULTS AS I/P TO ANOTHER PROC AND OTHER SET OF RESULTS AS I/P TO YET ANOTHER PROC .
I am wondering if it is possible to execute an .exe from a stored proc. I have an application that calls a stored proc and I am wanting for that stored proc to call up an app to show certain results. I have no way of doing this on the application itself as I dont have the source code. So, since I do have access to the SQL I am wondering if it could be done there.
Can somebody provide me an argument on how nicely formatted and indented procs perform better. I am currently formatting a 1000 line proc just to understand what it does ... Maybe your argument will help convince my development team to write neat procs :)
First off, this is a cross post which is also located here: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=34073
Now, with the following stored procs and UDF, the Result is NULL and I cannot figure out why.
What I am doing is calling a stored procedure pass in an OUTPUT variable of type float. This stored proceudre then calls a different stored procedure passing the same OUTPUT variable of type float. The second stored procedure then calls a UDF passing in two variables to be multiplied ans this should set the OUTPUT variable to the result.
The UDF does return the correct result to the interior stored procedure, but the interior stored procedure does not pass the value back to the original stored procudure resultint in a Result value = NULL.
The code is below, just copy / past, execute and you will see exactly what I mean.
Any thoughts?
USE NORTHWIND GO
CREATE FUNCTION RECTANGULAR_XSECTION (@fWidth float, @fHeight float)
I am trying to improve my SQL and reduce the number of connections my website is making to the server.
Currently I have a stored procedure that gets all the games (and their details) for a single user which works fine which uses a WHERE userid = @userid which is an Int value.
I now need to create a new procedure which brings back the to all their "friends" (again by their userid). How is it best to do this? I originally tried to do a WHERE userID IN (@userid) but was unable to work out what variable type to use for @userid.
Is it best to do a single query in this way or is there a way to use the existing SP, loop through the collection of userids and join the result together into a single set to return? If an IN is the best route, what is the correct datatype for the variable?
What is the procedure to create a extended stored procedure using C#.
Actually we have created a dll file using c# and it was added to the bin folder of mssql server. Also dll file is added to the sql server using "xp_extendedproc"
But while executing the extended proc we got this error "ODBC: Msg 0, Level 16, State 1 Cannot load the DLL xp_TestSql.dll, or one of the DLLs it references. Reason: 126(The specified module could not be found.)"
can anyone help me out.... i need to compare a date that was retrieved from the database and the system date then it must be coded in the stored procs of the database.. help!!!!
I'm relatively new to stored procs (not to SQL or SQL Server) and I am trying to get transactions to work within a stored proc. Here is the code:
( --define the parameters that are needed @userID char(32), @userName varchar(50), @status char(10), @type char(10), @password varchar(50), @firstName varchar(100), @lastName varchar(100), @email varchar(200), @domain varchar(50), @pwdExpiry int, @badLogin int, @lastLogin datetime, @full varchar(8000), @read varchar(8000), @noaccess varchar(8000) ) AS BEGIN TRAN tmp1 --First, insert the user into the system INSERT INTO ptsUsers(UserID, UserName, Status, Type, Password, FirstName, LastName, Email, DomainName, PasswordExpiry, BadLoginAttempts, LastLoginDate) values(@userID, @userName, @status, @type, @password, @firstName, @lastName, @email, @domain, @pwdExpiry, @badLogin, @lastLogin);
--Now, we need to add the function access edges for the user declare @arrayValue char(32) declare @rightID char(32) declare @sepPos int while patindex('%,%',@full)<>0 BEGIN select @sepPos = patindex('%,%' , @full) select @arrayValue = left(@full, @sepPos - 1) -- replace the value with an empty string select @full = stuff(@full, 1, @sepPos, '')
--create and parse the new id declare @strID char(32),@tmpStr varchar(40) set @tmpStr = newid(); set @strID = REPLACE(@tmpStr,'-','')
--get the access right id for full control select @rightID = (Select AccessRightID from ptsAccessRights where Name = 'Write') if(@rightID IS NOT NULL) --insert the records that are full access INSERT INTO ptsFunctionAccessEdges(FunctionAccessEdgeID, FunctionID, AccessRightID, UserID) VALUES(@strID, @arrayValue, @rightID, @userID) else RAISERROR(50100,15,1) END COMMIT TRAN tmp1 IF @@TRANCOUNT > 0 RAISERROR(50101,15,1) ROLLBACK TRAN tmp1
The transaction does not rollback when any errors occur at all. Any advice/help?
I am not an expert in Stored Procs. I would like to build one for a product list that would return a default value without using output parameters, if possible. Ultimately, I wouldn't be opposed to it.
It currently looks like this:
CREATE PROCEDURE ProductsByVendorNo ( @VendorNum nvarchar(24) ) AS
SELECT P.ProductID AS ProductID, P.ProductShortName AS ProductName, P.ProductDesc AS ProductDesc, U.UnitDesc AS Unit, P.VendorProductNumber AS VendorNumber, V.VendorName AS VendorName, P.Price AS Price, P.ImageThumb AS ImageThumb
FROM tblProducts AS P INNER JOIN tblUnitCodes AS U ON P.UnitCode=U.UnitCode INNER JOIN tblVendors AS V ON P.VendorID=V.VendorID
WHERE V.VendorsVendorNo = @VendorNum AND P.Inactive = 0
ORDER BY ProductName, VendorNumber
I would like for it to return a default, constant value for the URL in the ImageThumb field, if this one is empty. I could not find good documentation of how to use IF statements for this case, i.e. to alter the return of just one field.
I want to "deny" create, update,and delete access on the dbo stored procs that are in the database, but do not want take away dbo owner access. is this possible?
can i create a role and deny access on a particular table in msdb? or a system table in the user table. Thus preventing the developers on the box access to update any of the dbo owned sp's and have them create their own user-owned stored procs?
I am trying to set up a DTS package that selects data from one table on server A into another table on server B. I want to do a select statement that will do the following:
select store_name from store (server A) where date_created >= (select max(date_created) from store (server B)
I use EM 7.0 to manage all of my extracts, however, the data is moving from a Syabase (adaptive Server)onto another Syabase(adaptive server) machine. Unfortunately, there is no functionality for a linked server connection.
I tried the following, (which doesn't error out), but is not displaying the source columns in the destination tab of the DTS package when setting up the transformations.
declare @maxdate datetime exec serverB.dbo.sp_max_date_from_store @maxdate select store_name from store --(server A) where date_created >= @store
Any help or suggestions would be greatly appreciated! trevorb
DECLARE @object int DECLARE @hr int DECLARE @property varchar(255) DECLARE @return varchar(255) DECLARE @output varchar(255) DECLARE @source varchar(255) DECLARE @description varchar(255) --Instantiate a Network Object EXEC @hr = sp_OACreate 'WScript.Network', @object OUT IF @hr <> 0 BEGIN EXEC sp_displayoaerrorinfo @object, @hr RETURN END -- Get a property using an output parameter. --EXEC @hr = sp_OAMethod @object, 'userDomain' --,@property OUT EXEC @hr = sp_OAGetProperty @object, 'userDomain'--, @property OUT IF @hr <> 0 BEGIN EXEC sp_displayoaerrorinfo @object, @hr RETURN END --PRINT @property
-- Destroy the object. EXEC @hr = sp_OADestroy @object IF @hr <> 0 BEGIN EXEC sp_displayoaerrorinfo @object, @hr RETURN END
I get the same behavior stated in microsoft's support site related to BUG 55840 for Sql Server 7.0. I get an error message when trying to retrieve with @property OUT parameter. And I get an Empty record set when NO Output param is supplied.
I will be taking over a database that has almost no pk's or relations(this is not my choice, but a vendors) Management is looking at stored procs to improve performance, but I am wondering if the db is in this state will there really be a gain. I am pushing for normalization first, but if anybody has any ideas or opinions I would appreciate
I have a stored proc that is run periodically which I execute with a DTS package. The problem is that I have to update a field for each record selected by this stored proc with a specific date. I need to prompt the operator for this date so I can use this value.
Ok, I've read somewhere(which I'm looking for again : ) that said that there are errors like DeadLock that kills the execution of a stored proc and there are other errors that do not necessarily kill the rest of the execution of the stored proc. Is that true? If so does anyone have any links I can read. What I'm seeing is a bad id in the foreign key and I think what is happening is that there was a unique constraint error on the first insert but the stored proc continued executing and used the bad id later on in the stored proc.
I do know I can use the @@error and will start using it but I need more proof to agree or not agree with my theory.
Thanks ahead of time for any information you can give me either way.