I have two similar stored procedures which I'm running. One runs and
one doesn't. I can run both with no problems in SQL Enterprise (7.0
standard) and have checked the permissions and am happy with them.
Whilst the statements in each sp are different, I'm calling them in
exactly the same way (using Delphi 5 Windows 2000). I don't get any
trappable errors.
I ran a trace on what was happening and I get two different set of
results. This is what I'm trying to get my head around.
Client Trace (runs)
1 09:09:44 Log started for: Swift Client Import Utility
2 09:09:48 SQL Prepare: MSSQL - :1 =
dbo.CBFAUpdateSwiftClient;1
3 09:09:48 SQL Misc: MSSQL - Set stored procedure on or off
4 09:09:48 SQL Data In: MSSQL - Param = 1, Name = Result, Type
= fldINT32, Precision = 0, Scale = 0, Data = NULL
5 09:09:48 SQL Misc: MSSQL - Set statement type
6 09:09:48 SQL Execute: MSSQL - :Result =
dbo.CBFAUpdateSwiftClient;1
7 09:09:48 SQL Stmt: MSSQL - Close
8 09:09:53 SQL Connect: MSSQL - Disconnect NEW
9 09:09:53 SQL Connect: MSSQL - Disconnect NEW
10 09:09:53 SQL Connect: MSSQL - Disconnect PASSTHRU
I would have thought that these would be nearly identical. They call
sp's on the same servers in the same way, so the call to do this
should (in my mind) be the same.
I can post the sp's if anyone thinks they are of relevance.
I can of course set up these stored procedures to run at a certain
time, but I'd like to try and understand this a little more.
I need to run stored procedures which are currently run on server A(located in another city),by connecting myself to it through my laptop running on SQL server 7.0(only client components installed)on NT workstaion4.0. Can anyone tell all the steps involved to run those stored procedures everyday.Even I need to monitor Server A from my laptop.Please advice...Urgent!!
Hi, Can someone help me with this problem. I have a stored procedure in SQL Server that updates a particular table. When I run it in SQL server Query Analyser, it works fine. But I want to invoke this stored procedure when I click a button on an MS Access Form. The code I'm using is:
Dim cn, cmd Set cn = CreateObject("ADODB.Connection") cn.Open "SQL" //Data Source Name Set cmd = CreateObject("ADODB.Command") Set cmd.ActiveConnection = cn cmd.CommandText = "LoadApplicants" //Stored Procedure Name cmd.CommandType = adCmdStoredProc cmd.Execute
for some reason only a few records are updated everytime I click on the button. Is there any reason why this is happening?
Hi there,I have a data manipulation process written in a Nested Stored procedurethat have four levels deeper. When I run these individual proceduresindividually they all seems to be fine. Where as when I run them alltogether as Nested proces (calling one in another as sub-procedures) Logfile is growing pretty bad like 25 to 30GB.. and finally getting kickedafter running disk space. This process is running around 3hrs on a SQLserever Standard Box having dual processer and 2gb ram.This procedures have bunch of bulk updates and at least one cursor ineacch procedure that gets looped through.I was wondering if anybody experienced this situation or have any clueas to why is this happening and how to resolve this?I am in a pretty bad shape to deliver this product and in need of urgenthelp.Any ideas would be greatly appreciated..Thanks in advance*** Sent via Developersdex http://www.developersdex.com ***
Hi allI am looking for the best method to automate a website's database management. Lets say I have a user registration database and the users register. This sends an automated email to the user with a link to activate the users registration. If the user does not register within 24 hours, his registration must be automatically deleted from the database using a stored procedure.I know how to do this using the global.aspx file, however there must be an alternative way of doing this, especially if the database is an SQL database. I do not know how much MSSQL server access is given to a developer by an as ISP who hosts the website.Can anyone tell me what would be the best method to use.ThxWarren
Developers have complained that some of their jobs are taking too long to run. I used Profiler to trace one really bad performer and could see that the code was making one pass through its loop in about 7 seconds. I found one select statement that was using the majority of the time, plugged it into query analyzer and looked at the execution plan. It was using an index but not very efficiently. I then ran it through index analysis and it recommended a new index. This was cool! The new index helped.
But, the job continued to run very, very slowly - over a week before it died! I ran another trace, and it was hanging for 10+ seconds in random locations. Sometimes it would be on a database call, sometimes it would be on a simple SET @variable = value statement. There was no pattern. According to the sys admin, the server itself did not look taxed. It does not appear to be a locking problem because of the random location of the stall. The application and server are only lightly used at this point, so I would hate to see how bad it could be if it was busy.
I am still fairly new to SQL, though I have years of DBA experience. I would appreciate any ideas - especially the obvious things that I am probably not considering. I have logged a call with Microsoft but they haven't gotten back with an answer. I sent them a trace file and a perfmon file, neither of which showed them anything.
I have a strange situation when I try to execute the same Stored Procedure on servers with different processors. Both servers are running the SQL SERVER 2008 R2 version with all updates.
All updates bios, disk controller, firmware, were applied on the new server.
New Server: (considerable difference in processing time) Stored Procedure without encryption, runs at about 02:16hs Stored Procedure with encryption, runs at about 08:00hs
Server Processor:
Intel (R) Xeon (R) CPU @ 2.60GHz E5-2697 v3
Old Server: (There Are No difference in processing time) Stored Procedure without encryption, runs at about 01:00hs Stored Procedure with encryption, runs at about 01:00hs
Server Processor: Intel (R) Xeon (R) CPU @ 2.7GHz E5-2697 V2
In terms of configuration, the server 2, have lower technology, lower bus, lower number of processors.
I believe that combination of Hardware/SO/Sql Server has a potential performance loss when running encrypted SP.
when i am running a Stored Procedures, system always returns me error message below and Stored Procedures stops. please help
Microsoft OLE DB Provider for ODBC Drivers error '80004005'
[Microsoft][ODBC SQL Server Driver] Received an unrecognized datatype 0 from TDS data stream
sometime it returns error messge like, TDS Buffer Length Too Large or Unknown token received from SQL Server
or Protocol error in TDS stream or Bad token from SQL Server: Datastream processing out of sync. or Invalid cursor state or TDS Buffer Link Too Large or Function sequence error
Stupid question but please be gentle and answer anyway please....
Background: We have SQL Server 2003 (32bit) running on our servers. Our .Net applications (from old release of VS) are still running on them and using the old databases. From what I understand there is no immediate plans to upgrade the servers. However the developers were just given this new upgrade (2005) SQL Server and VS (and fixing depreciated code etc in the .net apps).
Question: Can the applications and new stored procedures written via the 2005 environment be deployed successfully on the 2003 Servers? Same goes with Reporting Services?
I'm having some problems debugging SQL Server stored procedures on a SQL Server 2005 server. I have installed Visual Studio 2005 on a workstation running Windows XP, now I'm trying to debug a ASP.Net web application that has some code that executes the stored procedures on a Windows 2003 Server running SQL Server 2005.
I opened VS2005 ... created a connection to the SQL Server 2005 instance ... open the Stored procedure ... right click the stored procedure name and selected Step into Stored Procedure and the following message is displayed:
Unable to start T-SQL debugging.Could not attach to SQL Server process on 'ServerName'.
I want to know the differences between SQL Server 2000 storedprocedures and oracle stored procedures? Do they have differentsyntax? The concept should be the same that the stored proceduresexecute in the database server with better performance?Please advise good references for Oracle stored procedures also.thanks!!
This Might be a really simple thing, however we have just installed SQL server 2005 on a new server, and are having difficulties with the set up of the Store Procedures. Every time we try to modify an existing stored procedure it attempts to save it as an SQL file, unlike in 2000 where it saved it as part of the database itself.
One of my stored procs, taking one parameter, is running about 2+ minutes. But if I run the same script in the stored proc with the same parameter hardcoded, the query only runs in a couple of seconds. The execution plans are different as well. Any reason why this could happen? TIA.
Using SQL 2005, SP2. All of a sudden, whenever I create any stored procedures in the master database, they get created as system stored procedures. Doesn't matter what I name them, and what they do.
For example, even this simple little guy:
CREATE PROCEDURE BOB
AS
PRINT 'BOB'
GO
Gets created as a system stored procedure.
Any ideas what would cause that and/or how to fix it?
Hi there,I am trying to create a Sql Server 2005 Express database by running Sql Scripts. I have a script that has CREATE statements for tables and procedures. What I'd like it to do is create a new database with a name and a file location I specify.I searched high and low, tried lots of options with the SqlCmd tool but I keep getting authentication or other errors.Is this even possible? If so, does someone know of a good reference? I know I can create a new database and tables from within VWD but I need to create this database based on a .sql file....Thanks in advance....Imar
How do I search for and print all stored procedure names in a particular database? I can use the following query to search and print out all table names in a database. I just need to figure out how to modify the code below to search for stored procedure names. Can anyone help me out? SELECT TABLE_SCHEMA + '.' + TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'
Seems like I'm stealing all the threads here, : But I need to learn :) I have a StoredProcedure that needs to return values that other StoredProcedures return.Rather than have my DataAccess layer access the DB multiple times, I would like to call One stored Procedure, and have that stored procedure call the others to get the information I need. I think this way would be more efficient than accessing the DB multiple times. One of my SP is:SELECT I.ItemDetailID, I.ItemDetailStatusID, I.ItemDetailTypeID, I.Archived, I.Expired, I.ExpireDate, I.Deleted, S.Name AS 'StatusName', S.ItemDetailStatusID, S.InProgress as 'StatusInProgress', S.Color AS 'StatusColor',T.[Name] AS 'TypeName', T.Prefix, T.Name AS 'ItemDetailTypeName', T.ItemDetailTypeID FROM [Item].ItemDetails I INNER JOIN Item.ItemDetailStatus S ON I.ItemDetailStatusID = S.ItemDetailStatusID INNER JOIN [Item].ItemDetailTypes T ON I.ItemDetailTypeID = T.ItemDetailTypeID However, I already have StoredProcedures that return the exact same data from the ItemDetailStatus table and ItemDetailTypes table.Would it be better to do it above, and have more code to change when a new column/field is added, or more checks, or do something like:(This is not propper SQL) SELECT I.ItemDetailID, I.ItemDetailStatusID, I.ItemDetailTypeID, I.Archived, I.Expired, I.ExpireDate, I.Deleted, EXEC [Item].ItemDetailStatusInfo I.ItemDetailStatusID, EXEC [Item].ItemDetailTypeInfo I.ItemDetailTypeID FROM [Item].ItemDetails IOr something like that... Any thoughts?
I have MSSQL 2005. On earlier versions of MSSQL saving a stored procedure wasn't a confusing action. However, every time I try to save my completed stored procedure (parsed successfully ) I'm prompted to save it as a query on the hard drive.
How do I cause the 'Save' action to add the new stored procedure to my database's list of stored procedures?
We recently upgraded to SQL Server 2005. We had several stored procedures in the master database and, rather than completely rewriting a lot of code, we just recreated these stored procedures in the new master database.
For some reason, some of these stored procedures are getting stored as "System Stored Procedures" rather than just as "Stored Procedures". Queries to sys.Objects and sys.Procedures shows that these procs are being saved with the is_ms_shipped field set to 1, even though they obviously were not shipped with the product.
I can't update the sys.Objects or sys.Procedures views in 2005.
What effect will this flag (is_ms_shipped = 1) have on my stored procedures?
Can I move these out of "System Stored Procedures" and into "Stored Procedures"?
I am writing a set of store procedures (around 30), most of them require the same basic logic to get an ID, I was thinking to add this logic into an stored procedure.
The question is: Would calling an stored procedure from within an stored procedure affect performance? I mean, would it need to create a separate db connection? am I better off copying and pasting the logic into all the store procedures (in terms of performance)?
Hi all - I'm trying to optimized my stored procedures to be a bit easier to maintain, and am sure this is possible, not am very unclear on the syntax to doing this correctly. For example, I have a simple stored procedure that takes a string as a parameter, and returns its resolved index that corresponds to a record in my database. ie exec dbo.DeriveStatusID 'Created' returns an int value as 1 (performed by "SELECT statusID FROM statusList WHERE statusName= 'Created') but I also have a second stored procedure that needs to make reference to this procedure first, in order to resolve an id - ie: exec dbo.AddProduct_Insert 'widget1' which currently performs:SET @statusID = (SELECT statusID FROM statusList WHERE statusName='Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID) I want to simply the insert to perform (in one sproc): SET @statusID = EXEC deriveStatusID ('Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID) This works fine if I call this stored procedure in code first, then pass it to the second stored procedure, but NOT if it is reference in the second stored procedure directly (I end up with an empty value for @statusID in this example). My actual "Insert" stored procedures are far more complicated, but I am working towards lightening the business logic in my application ( it shouldn't have to pre-vet the data prior to executing a valid insert). Hopefully this makes some sense - it doesn't seem right to me that this is impossible, and am fairly sure I'm just missing some simple syntax - can anyone assist?
i have created the folowing function but keep geting an error.
Only functions and extended stored procedures can be executed from within a function.
Why am i getting this error!
Create Function myDateAdd (@buildd nvarchar(4), @avdate as nvarchar(25)) Returns nvarchar(25) as Begin declare @ret nvarchar(25) declare @sqlval as nvarchar(3000)
set @sqlval = 'select ''@ret'' = max(realday) from ( select top '+ @buildd +' realday from v_caltable where realday >= '''+ @avdate +''' and prod = 1 )a'
i need to use only one stored procedure and access many tablesso how write a stored procedure for that dohelp me looking forward for a reply to the earliest i am developing web page using asp.net using c# and sqlserver as backend
Hello I have two stored procedures @ID INT AS SELECT (CASE WHEN NUM >= 10 THEN CAST(PAID AS FLOAT) / CAST(NUM AS FLOAT) * 100 WHEN NUM < 10 THEN 0 END) AS PER FROM (SELECT (SELECT COUNT(*) AS Expr1 FROM Event_data AS D LEFT OUTER JOIN Events AS E ON E.id = D.Event_id WHERE (D.Transaction_type = 1) AND (D.Player_id = @ID)) AS NUM, (SELECT COUNT(*) AS Expr1 FROM Event_data AS D LEFT OUTER JOIN Events AS E ON E.id = D.Event_id WHERE (D.Transaction_type = 1) AND (D.Transaction_value > 0) AND (D.Player_id = @ID)) AS PAID) AS X and @ID INT AS SELECT P.*,'/' + DBO.GETCHIPFOLDER(@ID) + '/' + ISNULL(P.PHOTO,'BLANK.GIF') AS PIC,ISNULL( (SELECT SUM(TRANSACTION_VALUE) FROM EVENT_DATA WHERE PLAYER_ID=@ID AND TRANSACTION_TYPE=1 GROUP BY PLAYER_ID),0) AS WINNINGS FROM PLAYERS P undefined P
The first returns a percentage for player wins, the second gives me a photo and sums the player winnings I would like to combine the results so I can get the percentage and wininngs in one query, in another matter all together I would like create a procedure like the first but instead of returning only one player, I would like to return the percentage for each player Thanks in advance for any light you can shine on this.
I have a question about stored procedures, Is it better to use stored procedures even if I only use it once at my site? Or is it better to write the sql-part directly in the sqldatasource? And am I forced to create two different stored procedures even if they are exactly the same except the "Where-part"? Now I have around 40 stored procedures, and quite many of them looks the same except the where-part... (Iam just a beginner with SQL)
Hello every one, I m working in aspx 2.0 with sql server 2005, please tell me how can I create Stored Procedures for two or more tables not a single table(select,insert,update,delete please send me the queries which can help me in easy way I will very thankful to you Thank you
I am learning to make a ASP web site and feel that if i can do it the harder way using some stored procedures instead of using multiple datasources on each page requiring that it might be better. So i am wondering what are these used for:DECLARE vs just entering "@param1 varchar(30)"When i use "DECLARE @rc int" i get the error "Incorrect syntax near DECLARE"How to return values to ASP page in Visual Studio 2005 How to use @@rowcount - doesn't seem to work for me?i tried using DECLARE @rc intSET @rc = @@rowcountWhen to use GO, BEGIN etcIf i want to use the variable only in the procedure, and not needed to be inputed, do i need to put it in the CREATE PROCEDURE (section)?Should i use my own stored procedures or VS2005 created ones using datasources? not really procedures but SQL, in SQL can i do like IF ELSE? if i use my own i cant use the Optimistic Concurrency right? and whats that?
cREATE PROCEDURE emp @val varchar(50)AS declare @test varchar(50)declare @a varchar(50)set @a= @val + '_a'set @test = 'alter table dbo.rights_user add ' + @a + ' varchar(50) null' execute(@test) GO this is my procedure...anyth wrong here...i can able to execute procedure only with 3 char..egexec emp 'na'exec emp 'hr10'--->wen i try like this cannot..showing datataype mis match
