MS SQL Server :: Problem When Invoking Stored Procedure With Cursor From Inside .net

Invoking A Report From A Stored Procedure
I still need to know the best way to do this. We have a batch process that will run a stored procedure outside of SSRS. This batch process will populate a table with aggregated data. Because the batch is run overnight, what we want to do is have the user be able to look at the report after the batch process is done. Is there a way to have the stored procedure invoke the report and then store the report as a snapshot? Or is there a way to have the code in the program do this?

Thanks for the information.

View Replies !

Differentiate Between Whether Stored Procedure A Is Executed Inside Query Analyzer Or Executed Inside System Application Itself.
Just wonder whether is there any indicator or system parameters that can indicate whether stored procedure A is executed inside query analyzer or executed inside application itself so that if execution is done inside query analyzer then i can block it from being executed/retrieve sensitive data from it?

What i'm want to do is to block someone executing stored procedure using query analyzer and retrieve its sensitive results.
Stored procedure A has been granted execution for public user but inside application, it will prompt access denied message if particular user has no rights to use system although knew public user name and password. Because there is second layer of user validation inside system application.

However inside query analyzer, there is no way control execution of stored procedure A it as user knew the public user name and password.

Looking forward for replies from expert here. Thanks in advance.

Note: Hope my explaination here clearly describe my current problems.

View Replies !

Calling A Stored Procedure Inside Another Stored Procedure (or &"nested Stored Procedures&")
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?

View Replies !

How Can I Assign A Stored Procedure As Cursor's Data Source In AStored Procedure?
How can I create a Cursor into a Stored Procedure, with another Stored Procedure as data source?

Something like this:

CREATE PROCEDURE TestHardDisk
AS
BEGIN

DECLARE CURSOR HardDisk_Cursor
FOR Exec xp_FixedDrives
-- The cursor needs a SELECT Statement and no accepts an Stored Procedure as Data Source

OPEN CURSOR HardDisk_Cursor

FETCH NEXT FROM HardDisk_Cursor
INTO @Drive, @Space

WHILE @@FETCH_STATUS = 0
BEGIN

...
END
END

View Replies !

MS SQL Stored Procedures Inside Another Stored Procedure
Hi,
Do you know how to write stored procedures inside another stored procedure in MS SQL.

Create procedure spMyProc inputData varchar(50)
AS
----- some logical

procedure spMyProc inputInsideData varchar(10)
AS
--- some logical
--- go
-------

View Replies !

{fn CURDATE()} Inside Stored Procedure
Hi,I created the stored procedure in SQL Server 2000CREATE PROCEDURE RsaTestASSELECT {fn CURDATE()}and to my surprise found out that it worked..To my surprise, because all the documentationthat I find suggests that {fn CURDATE()} isan ODBC escape clause..I can understand why/how ODBC escape clauseswork in queries, but not how this would workinside a stored procedure.I created the sp by running the above code inqueryanalyzer. sp_helptext 'RsaTest' shows thatthe {fn CURDATE()} is still in there, so itdoesn't appear to get transformed in to SQLServer specific code by whatever means queryanalyzer uses to connect to the database..Can anyone explain how this works? Or whereto find more info on {fn } syntax inside storedprocedures?Cheers,Rsa

View Replies !

If Statement Inside A Stored Procedure!!!!
hi all,
i'm wondering if i can use one stored procedure in too cases, this is the senario:
i have stored procedure for admin and another one for user, they have the same select everything is the same except that in admin SP i have where @admin = admin and for user i have where @user = user
if there a way to use if and else to make this happen
this is what i did so far:

CREATE PROCEDURE [test] @admin INT, @user INT, @indexType INT as
if @indexType = 1

begin

SELECT * FROM table WHERE something IN (SELECT * FROM anothertable where admin = @admin)
end
else
begin
SELECT * FROM table WHERE user = @user
end
GO

any suggestion will be very helpful
thanks

View Replies !

How Can I Use A Stored Procedure Call Inside The WHERE?

Hi folks
I nead to call a stored procedure in a where statemene, but MSSQL dont like that.

My problem is that the StoredProcedure is calling itself recursive and therfore its impossible to add the code as a standard SELECT statement. Here is the code

ALTER PROCEDURE dbo.advsp_FilterRecordRights
@RequesterGUID Char(20),
@EntityGUID Char(20)
AS
BEGIN
if not Exists(Select *

from GUIDRightsH
where GUIDRightsH.EntityGUID = @EntityGUID and

GUIDRightsH.RequesterGUID = @RequesterGUID and
GUIDRightsH.RecProp <> 0)
Begin

Return 1
end

if not Exists(Select *

from UsergroupMembers
where UserGroupMembers.UsergroupGUID = @RequesterGUID and
dbo.advsp_FilterRecordRights(UserGroupMembers.UserGUID,@EntityGUID) = 1)
Begin

Return 1
end

Return 0

END

View Replies !

RaiseError Inside Stored Procedure
I want to execute some insert statements within a stored procedure and commit those changes regardless of any raiseerror that occurs later in the stored procedure. My difficulty is that I am forced to use raiseerror with severity 16 in order to send a message through the powerbuilder application interface (compiled vendor code). I have tried save points but can't get that to save my insert and still present an error to the user about something else that happens later.

here is an example

BEGIN procedure

Insert something and save it even if error is raised below

RaiseError('you made a mistake and need to do this.',16,-1)

END procedure

View Replies !

Iterate Result Set Inside Stored Procedure
Hello, I have a situation that I query a table and return multiple rows (email addresses). I want to iterate through the rows and concatenate all email addresses into one string (will be passing this to another stored procedure to send mail). How can I process result rows inside a stored procedure? This is what I have so far: CREATE PROCEDURE [dbo].[lm_emailComment_OnInsert] @serviceDetailID int,@comment varchar(500),@commentDate DateTime,@commentAuthor varchar(100)ASBEGINDECLARE @serviceID intDECLARE @p_recipients varchar(8000)DECLARE @p_message varchar(8000)DECLARE @p_subject varchar(100)/* Grab the Service_id from underlying Service_Detail_id*/SELECT @serviceID = Service_id FROM lm_Service_Detail WHERE Service_Detail_id = @serviceDetailID/* Get email addresses of Service Responsible Parties */
SELECT DISTINCT dbo.lm_Responsible_Party.EmailFROM dbo.lm_Service_Detail INNER JOIN
dbo.lm_Service_Filing_Type ON dbo.lm_Service_Detail.Service_id = dbo.lm_Service_Filing_Type.Service_id INNER JOIN
dbo.lm_Responsible_Party_Filing_Type ON dbo.lm_Service_Filing_Type.Filing_Type_id = dbo.lm_Responsible_Party_Filing_Type.Filing_Type_id INNER JOIN
dbo.lm_Responsible_Party ON dbo.lm_Responsible_Party_Filing_Type.Party_id = dbo.lm_Responsible_Party.Party_idWHERE (dbo.lm_Service_Detail.Service_Detail_id = @serviceDetailID)/* Build message */
SET @p_subject = "KLM - Service ID: " + CAST(@serviceID AS varchar(4))SET @p_recipients = "" /*need string of addresses*/

SET @p_message = @p_message + "Service Detail ID: " + CAST(@serviceDetailID AS varchar(4)) + char(13)SET @p_message = @p_message + "Comment Date: " + CAST(@commentDate As varchar(25)) + char(13)SET @p_message = @p_message + "Comment Author: " + @commentAuthor + char(13)SET @p_message = @p_message + "Comment: " + @comment + char(13)PRINT "subject: " + @p_subject + char(13)PRINT "recip: " + @p_recipients + char(13)PRINT "msg: " + @p_message + char(13)/*Send the email*/
Execute master..xp_sendmail @recipients = @p_recipients, @message = @p_message, @subject = @p_subject END
GO

View Replies !

Using Joins Inside Stored Procedure With Row_Number
This example is working:
Declare @startRowIndex INT; set @startRowIndex = (@PagerIndex * @ShowMembers); With BattleMembers as ( SELECT TOP 20 ROW_NUMBER() OVER (ORDER BY LastActivityDate DESC) AS Row, UserId, UserName FROM aspnet_Users) SELECT UserId, UserName FROM BattleMembers WHERE Row between @startRowIndex and @startRowIndex+@ShowMembersEND
and this one doesn't work:USE [xx]GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[battle_Paging]@PagerIndex INT,@ShowMembers INT ASBEGINDeclare @startRowIndex INT; set @startRowIndex = (@PagerIndex * @ShowMembers); With BattleMembers as ( SELECT TOP 20 ROW_NUMBER() OVER (ORDER BY aspnet_Users.LastActivityDate DESC) AS Row, aspnet_Users.UserId, aspnet_Users.UserName, mb_personal.Avatar FROM aspnet_Users LEFT JOIN mb_personal ON (mb_personal.UserID=aspnet_Users.UserId) SELECT UserId, UserName, Avatar FROM BattleMembers WHERE Row between @startRowIndex and @startRowIndex+@ShowMembersEND
Error:Msg 156, Level 15, State 1, Procedure battle_Paging, Line 18Incorrect syntax near the keyword 'SELECT'.
I try to join in the table mb_personal here. Some help would be very appreciated! Thanks.

View Replies !

Comparing Two Strings Inside A Stored Procedure
Basically I have two strings. Both strings will contain similar data because the 2nd string is the first string after an update of the first string takes place. Both strings are returned in my Stored Procedure
For example:String1 = "Here is some data. lets type some more data"String2 = "Here's some data. Lets type some data here"I would want to change string2 (inside my Stored Procedure) to show the changed/added text highlighted and the deleted text with a strike though.
So I would want string2 to look like thisstring2 = "Here's <strike>is</strike> some data. Lets type some <strike>more</strike> data here"
Is there an way to accomplish this inside a stored procedure?

View Replies !

Executing A Variable Inside A Stored Procedure
Hello :) I need to do something like this:

CREATE PROCEDURE SelectCostumers @name varchar(100)
Declare @SQL = "SELECT Id, Name FROM Costumers"
AS
IF (@name IS NULL)
@SQL
ELSE
@SQL += "WHERE Name LIKE @name"

See, what I need is a string variable that I can concatenate with whatever I want depending on the parameter I get.

Thank you

View Replies !

How To Dynamically Create SQL Inside A Stored Procedure?
I am having problem with 'TOP @pageSize'. It doesn't work, but if I replace it by 'TOP 5' or 'TOP 6' etc., then the stored procedure runs without errors.
Can someone please tell me how I could use @pageSize here so that it dynamically determines the 'n' of 'TOP n' ?

ALTER PROCEDURE dbo.spGetNextPageRecords

(
@pageSize int,
@previousMaxId int

)

AS
/* SET NOCOUNT ON */
SELECT Top @pageSize ProductId, ProductName
FROM Products
WHERE (ProductID > @previousMaxId) order by ProductId
RETURN

View Replies !

How To Query Two MS SQL DB's On The Same Server Inside A Stored Procedure
Okay, so I have a problem and I would be REALLY grateful for anyassistance anyone can offer because I have found little or no help onthe web anywhere.I want to access and do joins between tables in two different SQL db'son the same server. Heres what Im dealing with.In one database resides all of my security features for our clients,where it decides who can login, etc etc....In another database, I need to cross reference with a few fields in mysecurity db.See the issue Im running into here is that because the way the peoplehave their databases set up for different products, I would normallyhave to put these tables with security features in every database...which is horrible, because every time I do an update I would have todo it in 12 different places. Thats not efficient at all.So I thought if I had one central DB, where all security features arecontrolled from, that would be perfect... now the issue is crossreferencing and doing joins with other tables that ARENT in the samedb....have I lost you yet?I appreciate all of your help!THANKS!!

View Replies !

Creating A Table Inside A Stored Procedure
I am trying to creating a table inside a stored procedure using SQL that works fine in Query Analyzer. However, when I check the syntax I get the following message:

Error 208: Invalid object name '##OPTIONSEX'

I am using the following SQL script:

CREATE PROCEDURE [dbo].[Test2] AS

CREATE TABLE ##OPTIONSEX
(
OPTION_PLAN VARCHAR(50),
TOT_OPTIONS_EXCHANGED FLOAT NULL
)

GO

INSERT ##OPTIONSEX

SELECT
B.COMPONENT,
TOT_OPTIONS_EXCHANGED = SUM(A.UNITS)
FROM TBLEXERCISEOPTIONS A, TBLCOMPONENT B
WHERE B.COMPONENTID = A.COMPONENTID
GROUP BY B.COMPONENT

GO

Any help getting this to run correctly would be appreciated.

View Replies !

Query From Parameters Inside A Stored Procedure
Hi,

I have to write a stored procedure what repair a table.
It have to delete lost rows before make relation to itself (PK column is 'Kw_KeywordID', FK column is 'Kw_ParentID').
I do not found the way to write this procedure to can create statement from parameter of the procedure.
I would like to pass the table name, but I receive error if the query like 'Select ... Form @Table ....'.
If the parameter is in the 'WHERE', nothing problem.

My procedure is:
CREATE PROCEDURE sp_Repair_IS_KW_AbtKz176
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

declare @OrphanRowCount int
select @OrphanRowCount = 1

while(@OrphanRowCount > 0)
begin
  DECLARE KWCursor CURSOR FOR
  SELECT count(*) AS cRowCount FROM IS_KW_AbtKz176 WHERE Kw_ParentID IS NOT NULL AND Kw_ParentID NOT IN (SELECT Kw_KeywordID FROM IS_KW_AbtKz176);

  OPEN KWCursor;
  FETCH NEXT FROM KWCursor INTO @OrphanRowCount
  CLOSE KWCursor
  DEALLOCATE KWCursor

  IF (@@FETCH_STATUS = 0) AND (@OrphanRowCount > 0)
     BEGIN
   exec('DELETE FROM IS_KW_AbtKz176 WHERE Kw_ParentID IS NOT NULL AND Kw_ParentID NOT IN (SELECT Kw_KeywordID FROM IS_KW_AbtKz176)')
     END
end
END
GO

How can I run fully parameterized queries from an SP.
I can make it only like exec('DELETE FROM' + @TableName + ...)

Thank you for any idea,
Imre

View Replies !

Debugging Stored Procedure Inside SSMSE !!!
Hi for all

i just get the news that the Dec. CTP of SQL Server 2005 & tools is released !

but my dream is to see debugging stored procedure support inside the SSMSE as debugging applications inside Visual Studio 2005

Many many many thanks to Microsoft and for the great efforts on the cool products

Mohamed

View Replies !

Commit Not Commiting Inside Stored Procedure
Hi Folks!

I have a cursor inside a stored procedure that should identify rows based on certain criteria and update the columns on those records.

While I try to do so, the cursor goes into an infinite loop although I have only 1 record that matches my criteria. I think that the transaction is not commited and the cursor is picking up the same record for update.

Here is my sample record in table :tblMsg
SenderRef <space> MsgStatusId <space> MsgType <space>Groupid
1281341<space> 1 <space>KBC-NON-ETC-MATCHED-BLIM <space>191902

1281341 <space>18 <space>KBC-RCVD-ADM <space>191902<space>

and here is my code.

CREATE PROCEDURE msg_ResolveADMBeforeBlim
AS

DECLARE @blimGroupId AS INT
DECLARE @admSenderRef AS VARCHAR(50)

DECLARE admCursor CURSOR FOR
SELECT senderref
FROMtblMsg
WHERE msgstatusid = 18
AND msgtype = 'KBC-RCVD-ADM'
FOR UPDATE

OPEN admCursor

FETCH NEXT FROM admCursor INTO @admSenderRef

WHILE @@FETCH_STATUS = 0
BEGIN
--FIND CORRESPONDING BLIM RECORD AND GRAB ITS GROUPID TO UPDATE THE ADM
SELECT @blimGroupId = GroupID
FROM tblMsg
WHERE msgType = 'KBC-NON-ETC-MATCHED-BLIM'
AND SenderRef = @admSenderRef
PRINT 'AFTER SELECT INSIDE WHILE: Senderref- >' + @admSenderRef;
--UPDATE THE ADM RECORD WITH THE GROUPID AND MSGSTATUS = 3
BEGIN
UPDATE tblMsg
SET MsgStatusId = 3, GroupId = @blimGroupId
WHERE CURRENT OF admCursor
END
PRINT 'AFTER UPDATE INSIDE WHILE';
END

CLOSE admCursor
DEALLOCATEadmCursor

The update statement was included beteween a BEGIN TRAN and END TRAN. But no joy.

Any ideas why this would happen?

Thanks for your help.
Arun

View Replies !

Invoking Webservice Using SQL CLR Procedure
Hi,

I have written a SQL CLR procedure, which will be invoking the webservice..I developed the application locally and it works fine, I am able to invoke the webservice using the SQL CLR procedure present in my database. But when i hosted the webservice in App server and executed the SQL CLR procedure in DB Server.
From DB Server, I am not able to invoke the webservice present in the app server. But i am able to browse the webservice from my db server.
I am getting the foolowing error message

A .NET Framework error occurred during execution of user defined routine or aggregate 'usp_LoadView':
System.Net.WebException: Unable to connect to the remote server ---> System.Net.Sockets.SocketException: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond
System.Net.Sockets.SocketException:
   at System.Net.Sockets.Socket.DoConnect(EndPoint endPointSnapshot, SocketAddress socketAddress)
   at System.Net.Sockets.Socket.InternalConnect(EndPoint remoteEP)
   at System.Net.ServicePoint.ConnectSocketInternal(Boolean connectFailure, Socket s4, Socket s6, Socket& socket, IPAddress& address, ConnectSocketState state, IAsyncResult asyncResult, Int32 timeout, Exception& exception)
System.Net.WebException:
   at StoredProcedures.usp_LoadView(String ConnectionString, String WebserviceUrl, String ColumnMappingsXml, SqlXml AddressXml, SqlXml& ExceptionSqlXml, Int32& ErrorStatus)

I goggled and tried out various options, like increasing €œwebservice timeout€? and increasing €œexecutiontimeout€? for HttpRuntime, but none seems to be working. Please provide me your suggestions to how to fix this..

View Replies !

How To Pass The Value Of A Text Box Inside A FORMVIEW To A SQL Stored Procedure
Hi,
I'm new to ASP.NET 2.0. I have a sqldatasource and a formview controls on a web page and inside the formview control I have two textboxes. When I click the UPDATE button in the formview, I'd like to be able to retrieve the values of the textboxes inside the formview control and pass these values to a 'Update' SQL stored procedure defined in the Sqldatasource.
Does anyone know how I can do this? Hope my question is clear.
Thanks in advance.
hakl

View Replies !

To Pass Data To An Stored Procedure Inside Sql Server
I want to use a stored procedure inside Sql Server from my aspx page so that the data entered in the form goes to the database after submit.

My stored procedure (inside the SQL Server) inserts several fields in a table and it returns two variables.
What code I need to write in order to pass the data from the form to the stored procedure inside the Sql Server? And to store the two returned values?

Thanks

View Replies !

SELECT Query Stmt Inside Stored Procedure
Friends,

What are the possible usuages of a SELECT query stmt inside a stored procedure ??

How can we process the results of the SELECT query other than for documentation/Reporting purposes(Correct me if i'm wrong in this) ??

can any one throw some lite on this ..

Thanks,
SqlPgmr

View Replies !

Stored Procedure Output Parameter Inside Select...
Does anyone know how can I (or can I) use a stored procedure output parameter(s) inside Select statement. For example

Select abc, cde, 'xyz' = Case
When 'aaa' then {output parameter of my stored procedure with 'aaa' as input parameter}
When 'bbb' then {output parameter of my stored procedure with 'bbb' as input parameter}
end
from MyTable

Thanks
Arcady

View Replies !

Help-convert Date Inside Stored Procedure Problem
problem convet date

Code Snippet
DECLARE
@y_Date [varchar](4),@M_Date [varchar](2),@d_Date [varchar](2)
DECLARE @mydate [datetime]
set @y_Date='2008'
set @M_Date ='8'
SET @d_Date='05'
set @mydate = CAST(CAST(@M_Date + '-'+ @d_Date + '-' + @Y_Date AS nvarchar) AS DATETIME)
select @mydate

2008-05-08 00:00:00.000
and it look ok

but whan i do this

Code SnippetEXECUTE [dbo].[testpro] @mydate

the date not pass properly !!

Code Snippet
DECLARE
DECLARE @mydate [datetime]
set @mydate = getdate()

2008-05-08 01:20:35.870
like this it work ok

what is the problem with tis line ?
set @mydate = CAST(CAST(@M_Date + '-'+ @d_Date + '-' + @Y_Date AS nvarchar) AS

tnx

View Replies !

How To Create A Second Independent Transaction Inside A CLR Stored Procedure?
I use the context connection for the "normal" work in the CLR procedure.

View Replies !

How Do I Find Out If A SQL Statement Found Records Inside A Stored Procedure
I need to run a sequel statement in a stored procedure and get if it found any records because the next statements depend on if records were found or not. How do I do this?

View Replies !

Insertion And Deletion Of Records Inside A Single Stored Procedure
Hi,
I have two tables A and B. In table A i have three columns called empid, empname and empsalary where empid is an identity column. Table A has some records filled in it. Table B has the same schema except the fact that the empid is not an identity column in table B. Table B does not contain any rows initially. All other aspects remain the same as that of table A. Now i am going to delete some records in table A based on the empid. When i delete the records in table A based on empid the deleted records should be inserted into table B with the same empid. I need to accomplish these two tasks in a single stored procedure. How to do it? I need the entire code for the stored procedure. Please help me. I am trying for the past 4 days.
Thanx in Advance

View Replies !

How To Use A Stored Procedure Inside Select Query (sql Server Version 8)

Hi,
 Please help me in this problem...
 i am new to sql server..
 i am using sql server version 8...(doesnot support function with retun values..)
 so i have created a procedure...
 -----------procedure------------------(to find next monday after 6 months)-------------------
[code]
create proc next_Monday ( @myDate DATETIME )
as
BEGIN
set @myDate = dateadd(mm, 6, @myDate)
while datepart(dw,@myDate) <> 2
begin
 set @myDate = dateadd(dd, 1, @myDate)
end
 select @myDate
end
go
[/code]
 --------------------------------------------------------
 i can able to execute this procedure separately.... working well...
 but don't know how to call it inside another query....
the following throws error....
select smaster.sname, smaster.Datex, 'xxx'=(execute next_monday smaster.Datex) from smaster
 please help me... how to fix this problem...



View Replies !

Using Multiple Distinct Queries Inside Single Stored Procedure
Hello,

I was wondering if anyone can explain the positives and negatives of using a single stored procedure that contains one or more distinct queries. I know there are problems with dynamic SQL but I am not proficient enough to know whether this falls under that umbrella.

For clarification, what I am referring to is this: In a single stored procedure, I have a parameter called Query_ID that is used to identify which query in the sproc that I want to execute. Then from my ASP page, I simply pass the appropriate value for Query_ID. So:

IF @QUERY_ID = 1
    BEGIN
        SELECT [whatever]
        FROM [tbl1]
        WHERE [conditions]
        GROUP BY [something]
        ORDER BY [somethingelse]
    END
ELSE
IF @QUERY_ID = 2
    BEGIN
        SELECT [whatever]
        FROM [tbl2]
        WHERE [conditions]
        GROUP BY [something]
        ORDER BY [somethingelse]
    END
END

I hope that makes sense. Thanks in advance.

View Replies !

Cursor With Stored Procedure
I have a stored procedure that basically recieves the where clause of a select statement and executes the new sql statement... ie:

CREATE PROCEDURE [dbo].[bsa_GetImportIDs]
(@FilterText varchar(1000))
AS

DECLARE @MySQL varchar(1000)

SET @MySQL = "SELECT Import_ID FROM tblImport WHERE " + @FilterText

EXEC (@MySQL)
GO

Now, in another stored procedure, I need to use the stored procedure above in a cursor so that I can execute an insert statement for each occurance of the Import_ID that appears in that dataset... ie:

CREATE PROCEDURE [dbo].[bsa_PutLargeCase]
AS

DECLARE @CaseID uniqueidentifier
SET @CaseID = NewID()
Declare @ImportID uniqueidentifier

Declare curClient Cursor FAST_FORWARD for
SELECT Import_ID FROM dbo.bsa_GetImportIDs (@FilterText) <---- this does not work!!!

Open curClient
FETCH NEXT FROM curClient INTO @ImportID
WHILE @@FETCH_STATUS = 0
BEGIN

EXEC dbo.bsa_PutCaseDetail @CaseID, @ImportID

FETCH NEXT FROM curClient INTO @ImportID
END

CLOSE curClient
DEALLOCATE curClient

GO

How can I utilize my first stored procedure in the cursor of the second? ... or
Are there any other approaches that may be a better solution to what I am trying to accomplish?

Thanks in advance for any input.

View Replies !

Stored Procedure Into A Cursor
Hi guys!!I am trying to fill a cursor with the results of a StoredProcedured, but SQL give me an syntax error message , does any one cangive me some helpI am using SQL Server, this is the first lines of my codeDECLARE FRates_Cursor CURSOR FORexec GET_FJRs_Eng 'all'OPEN FRates_Cursorif I run just the exec GET_FJRs_Eng 'all' line it give me the dataresults I am trying to put into the cursor, what that means is thestored is working fineThanks in advance

View Replies !

Help In Stored Procedure With Cursor
I am trying to create the procedure from two tables to create the flat file
from the two tables and as they are processed to the file then want to change the
status from 'U' to 'P'.
I am trying to declare the cursor from the union of two tables so that I can
store the data into one table.
And then put the data into the flat file and then marked the processed data as 'P' from 'U'
please help in doing that.
thanks,
Harish

CREATE PROCEDURE SP_P8

AS

declare c123 cursor for

select 'CA'+'|'+COMPANY+'|'+COMPANYNAME+'|'+CUSTOMERNUMBE R+'|'+COMPANYPHONE
+'|'+COMPANYFAX+'|'+FEDTAXID+'|'+DUNSNUMBER+'|'+S1 099+'|'+DUNSSUFFIX+'|'+convert(char,ADDRESSSEQ)
+'|'+COUNTRY+'|'+ADDRESSTYPE+'|'+ADDRESS1+'|'+ADDR ESS2+'|'+ADDRESS3+'|'+ADDRESS4
+'|'+CITY+'|'+STATE+'|'+ZIP+'|'+FAXNUMBER hello1,status
from v1

UNION

select 'CT'+'|'+COMPANY+'|'+CONTACTNAME+'|'+CONTACTTYPE+' |'+CONVERT(CHAR(10),CONTACTADDRESS)
+'|'+PHONE+'|'+FAX+'|'+TITLE hello,status
from v3

declare @status char(1)
declare @wholerecord text

open c123
fetch next from c123 into @wholerecord,@status
while @@fetch_status=0

begin

update v1 set status='P' where current of c123
update v3 set status='P' where current of c123
fetch next from c123 into @wholerecord,@status

end
close c123
deallocate c123

select * into temp23 from v2 where status='P' and

declare @v datetime
declare @filename varchar(32)
select @v=getdate()
select @filename = 'XCUST'+CONVERT(varchar, @v, 112)+ SUBSTRING(CONVERT(varchar, @v, 108), 1, 2)
+ SUBSTRING(CONVERT(varchar, @v, 108), 4, 2)+ SUBSTRING(CONVERT(varchar, @v, 108), 7, 2) + '.TXT'
declare @cmdstring varchar(255)
Select @cmdstring = 'bcp conversion..temp23 out d:est' + @filename + ' -c -t"|" -r -S -Usa -Ppassword'

exec master..xp_cmdshell @cmdstring
drop table temp23

View Replies !

Help In Stored Procedure With Cursor
Please help in creating the stored procedure for
the following code.

Thanks in Advance.
=============================
Create procedure extractdata

@companynumber varchar(3),
@firstdateofmonth int,
@lastdateofmonth int

as
truncate table xt

truncate table xtt

insert into xt(account_code,balance_date,net_change,current_ba lance,oldlob)
select account_code, balance_date,net_change,current_balance,@companynu mber
from ccc@companynumber..glbal where balance_date<=@firstdateofmonth

declare @v1 varchar(32)
declare @v2 int
declare @v3 float

declare c1 cursor for
select account_code, max(balance_date) from xt
where oldlob=@companynumber
group by account_code

open c1

fetch next from c1 into @v1,@v2
WHILE @@FETCH_STATUS = 0

begin
update xt set status='max' where account_code=@v1 and balance_date=@v2
and oldlob=@companynumber
fetch next from c1 into @v1,@v2

end

close c1
deallocate c1

insert into xtt(account_code,balance_date,net_change,current_b alance,oldlob)
select account_code,balance_date,net_change,current_balan ce,oldlob from xt
where status='max' and oldlob=@companynumber
order by account_code

insert into test1(account_code,current_balance,net_change,bala nce_date,oldlob)
select account_code,round(current_balance,2),round(net_ch ange,2),balance_date,@companynumber
from xtt where oldlob=@companynumber
and account_code in (select account_code from xxxmay where oldlob=@companynumber)

update test1 set net_change=0 where balance_date<@lastdateofmonth
and oldlob=@companynumber

select sum(current_balance),sum(net_change) from test1
where oldlob=@companynumber

View Replies !

Out A Cursor From A Stored Procedure
hello!

any of you have an idea how i can declare an output parameter for my cursor which is inside a stored procedure. i would lik to see the output using the exec command but i don't know how to get the out from my cursor.
please help!

honey

View Replies !

Invoking A Web Service Using MSSOAPLib From A SQL Server CLR Procedure
Hi,

I need to invoke a web service from a CLR procedure without adding a web reference. I'm using MSSOAPLib for doing this.

I am able to do this from a desktop application, but it doesnt seem to work in a SQL Procedure. The execution just seems to hang at the code 'SoapClient soapClient = new SoapClient();'. There is no exception either, as the error message doesn't get logged in the catch block.

I'm also unable to debug the procedure using the remote debugger provided with vs2005. It gives me a warning saying 'The breakpoint will not current be hit. No symbols have been loaded for this document'.

I have registered the assembly as UNSAFE.

The code snippet is given below

HttpWebRequest req =

(HttpWebRequest)WebRequest.Create(url);

req.Method = "GET";

req.KeepAlive = false;

if (userName != null && password != null && userName != "" && password != "")

req.Credentials = new NetworkCredential(userName, password);

HttpWebResponse resp = (HttpWebResponse)req.GetResponse();

StreamReader sr = new StreamReader(resp.GetResponseStream());

wsdl = sr.ReadToEnd();

sr.Close();

//Load the wsdl in a XML Document and get the details of the

//service, port name, and the number of parameter to the

//web method

XmlDocument xmlDoc = new XmlDocument();

xmlDoc.LoadXml(wsdl);

//Get the service name and the port name

XmlNode serviceNode = xmlDoc.GetElementsByTagName("service").Item(0);

string serviceName = serviceNode.Attributes["name"].Value;

XmlNode portNode = serviceNode.FirstChild;

string portName = portNode.Attributes["name"].Value;

try

{

SoapClient soapClient = new SoapClient();

soapClient.mssoapinit(url, serviceName, portName, "");

Type type = soapClient.GetType();

object[] args = new object[1]; //specified service accepts one argument of type string

args[0] = messageBody;

object ox = type.InvokeMember(webMethod,

BindingFlags.InvokeMethod, null, soapClient, args); //Dynamic Invocation, No Proxy class, no web reference

if (ox != null)

{

updateDataCMD = connection.CreateCommand();

updateDataCMD.CommandText = "INSERT INTO LogTable VALUES('" + ox.ToString() + "')";

updateDataCMD.ExecuteNonQuery();

}

}

catch (Exception exp)

{

updateDataCMD = connection.CreateCommand();

updateDataCMD.CommandText = "INSERT INTO LogTable VALUES('" + exp.ToString() + "')";

updateDataCMD.ExecuteNonQuery();

}

Would be great if I can get some pointers as to why it is not working and also how can I debug the procedure.

Thanks and Regards,

Rakesh

View Replies !

Using MatLab Built-in Statistical Functions Inside SQL Server Stored Procedure
Hi everybody,

I would like to use MatLab built-in statistical functions (beta, gamma, normal, etc.) from inside a SQLServer stored proceudre. Does anyone know if possible? (Of course, If so, where can I get documentation for doing this?)

Thanks in advance!

JorgeHG.

View Replies !

Using Cursor As OUT Parameter In Stored Procedure
Hi guys,
I have a serious problem.
I need to use my cursor as an out parameter, but the problem is, HOW CAN I CLOSE THE CURSOR??????If I dont close the cursor, my server is getting really slow because of the open cursors, cause I have more than 100 stored procedures, which have a cursor as an out-parameter.Here's one of my stored procedures :

create or replace PACKAGE pkgResIS TYPE resType IS REF CURSOR RETURN res%ROWTYPE;END pkgRes;
create or replace procedure res_sel_val(p_id in number,cs out pkgRes.resType)asBEGIN open cs for select * from res where res_id = p_id; --close cs;EXCEPTION when others then raise_application_error(-20970, 'record kan niet geselecteerd worden');END res_sel_val;
How can I close my cursor? If I write the "close cursor" (which is in red at the code above), it returns an empty cursor, which is not my intention.Please help me with thisThanks in advance Morph 'n Nike

View Replies !

Creating Cursor From Stored Procedure
Hi guys!i want to create one cursor in the t-sql. the problem is i want to usestored procedure instead of select command in cursor.can anyone tell me how can i use stored procedure's o/p to createcursor?i'm using sql 2000 and .net 2.0thanks,Lucky

View Replies !

Error From Stored Procedure With A Cursor
I receive the following intermittent error when executing a stored procedure:

Msg 16942, Sev 16: Could not generate asynchronous keyset. The cursor has been deallocated. [SQLSTATE 42000]
Msg 3624, Sev 16: Location: lckclass.cpp:111 Expression: m_status == ACTIVE || m_status == ORPHANED SPID: 17 Process ID: 203 [SQLSTATE 01000]

The process uses a cursor to update a SQL7 table from another. This is not a consistent failure. Sometimes the procedure runs fine to completion. Has anyone else had the same problem??? We had a suggestion that it might be a tempdb problem??? Any ideas?

View Replies !

Dynamic Cursor In Stored Procedure
when i try to compile the following sp, i get an error Line 11:Incorrect syntax near;. Can someone please tell me what i am doing wrong. thanks a lot.

CREATE PROCEDURE test_dump (@p_query nvarchar(4000)) AS
declare

@cmdtxt as varchar(4000),
@SQLString NVARCHAR(4000),
@SQLString1 NVARCHAR(4000),
@pid varchar(22),
@lname varchar(60)
begin

EXEC SQL BEGIN DECLARE SECTION;

char prep[] = @p_query;

EXEC SQL END DECLARE SECTION;

EXEC SQL PREPARE prep_stat FROM :prep;

EXEC SQL DECLARE contact_crsr CURSOR FOR prep_stat;

OPEN contact_crsr
FETCH NEXT FROM contact_crsr
INTO @pid, @lname

-- Check @@FETCH_STATUS to see if there are any more rows to fetch.

WHILE @@FETCH_STATUS = 0

BEGIN

SET @SQLString1 = 'HELLO ' + @pid + ' ' + @lname
select @cmdtxt = "echo " + @SQLString1 + " >> c:empmyfile.txt"
exec master..xp_cmdshell @cmdtxt

FETCH NEXT FROM contact_crsr

INTO @pid, @lname

END

CLOSE contact_crsr

DEALLOCATE contact_crsr

end

View Replies !

How To Declare Cursor In Stored Procedure?
I am trying to decalare the cursor in the below stored procedure. Can any one please help me to correct the cursor declaration?? Basically, i am testing how to declare the cursor in stored procedure.

CREATE PROCEDURE STP_EMPSAL
@empno int,
@Employee_Cursor CURSOR VARYING OUTPUT
FOR SELECT empno FROM AdventureworksDW.dbo.emp
AS
OPEN Employee_Cursor;
FETCH NEXT FROM Employee_Cursor into @empno;
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN TRAN
UPDATE emp set sal= sal+ 2000 where
empno = @empno and comm is null
mgr='Scott';
FETCH NEXT FROM Employee_Cursor into @empno;
COMMIT;
END;
CLOSE Employee_Cursor;
DEALLOCATE Employee_Cursor;

View Replies !

Stored Procedure Using A Declared Cursor
I need to write a stored procedure using T-SQL to declare a cursor for containing id(staff_no), names and specialism of all doctors that have specialism, The contents of the cursor then are to be displayed using a loop and print statement to give a formatted display of the output of each record within the cursor.

The doctors table has the following columns with specialism allowing NULL values

doctor
(
staff_no CHAR(3),
doctor_name CHAR(12),
position CHAR(15),
specialism CHAR(15),
PRIMARY KEY(staff_no)
)

Any help would be greatly appreciated.

View Replies !

Looping Through Stored Procedure Inside Another Stored Procedure And Displaying The Category And Then Displaying 1 Item In Each Category
I used to do this with classic asp but I'm not sure how to do it with .net.Basically I would take a table of Categories, Then I would loop through those. Within each loop I would call another stored procedure to get each item in that Category. I'll try to explain, Lets say category 2 has a player Reggie Bush and a player Drew Brees, and category 5 has Michael Vick, but the other categories have no items.Just for an example.. Category Table: ID Category1 Saints2 Falcons3 Bucaneers4 Chargers5 FalconsPlayer Table:ID CategoryID Player News Player Last Updated1 1 Reggie Bush Poetry in motion 9/21/20062 1 Drew Brees What shoulder injury? 9/18/20063 5 Michael Vick Break a leg, seriously. 9/20/2006 Basically I would need to display on a page:SaintsReggie BushPoetry in MotionFalconsMichael VickBreak a leg, seriously.So that the Drew Brees update doesnt display, only the Reggie Bush one, which is the latest.I have my stored procedures put together to do this. I just don't know how to loop through and display it on a page. Right now I have two datareaders in the code behind but ideally something like this, I would think the code would go on the page itself, around the html.

View Replies !

Sql Stored Procedure - With In Cursor Get @@identity Value For Insert And Use That Again
I have stored procedure which contains follwing part of it. it says syntax when i worte line to get @@identity valuewhen delete that line command succesful. but i need to get @@identity from the insert statement and assign it to a variable and use it after
any body pls tell me how to get this within a stored prosedure or what is the error of the following code bit. (#tblSalesOrders is a temporary table which containsset of records from orginal table )DECLARE @soNo1 INT
DECLARE @CursorOrders CURSOR
SET @CursorOrders = CURSOR FAST_FORWARD FOR
select fldSoNo from #tblSalesOrders
declare @newSONO1 int OPEN @CursorOrders
FETCH NEXT FROM @CursorOrders INTO @soNo1
WHILE @@FETCH_STATUS = 0
BEGIN
----for each salesorder insert to salesorderline
insert into tblSalesOrders (fldWebAccountNo,fldDescription,fldSoDate,fldGenStatus) select (fldWebAccountNo,fldDescription,fldSoDate,fldGenStatus) from #tblSalesOrders where fldSoNo =@soNo1;

set @newSONO1=SCOPE_IDENTITY;
-------in this section again create another cursor for another set of records and insert to a table passing identity value return from the above insert --------------------------
SELECT @intErrorCode = @@ERRORIF (@intErrorCode <> 0) GOTO PROBLEM
FETCH NEXT FROM @CursorOrders INTO @soNo1
END CLOSE @CursorOrders
DEALLOCATE @CursorOrders

View Replies !

Receiving And Sending A Cursor With(in) A Stored Procedure
Can someone post some code that shows a Stored Procedure receiving a cursor that it can process - lets say a group of order detail records are received that must be saved along with the single Order header record.

And, in another example, a SP returns a result set to the calling program. - For example, a particular sale receipt is pulled up on the screen and the order detail is needed.

Thanks for help on this,

Peter

View Replies !

Unique Cursor Name In Recursive Stored Procedure
How can I solve this probleM?

procedure XX
DECLARE cc CURSOR FOR SELECT....
OPEN cc
...
WHILE @@FETCH_STATUS = 0
BEGIN

View Replies !

Unique Cursor Name In Recursive Stored Procedure
How can I solve this probleM?
I thing that I need to create cursor with unique cursor name

procedure XX param
DECLARE cc CURSOR FOR SELECT....param --ERROR bcause it's already exists
OPEN cc
...
WHILE @@FETCH_STATUS = 0
BEGIN
XX param
FETCH NEXT ...
END
CLOSE cc
....

View Replies !

Stored Procedure Cursor Problem URGENT
Hi,

I have created the following stored procedure to get the text from one table and compare with them with another table and the one's that match will assign the corresponding ID. But the problem is that it only assigns the last id in the table from the main table which new_cur2 holds. So the problem is that its not updating with the correct ID its just updating with the last ID the cursor holds. Does any one know what it could be.....I think it may just be a little coding error....thanks

CREATE PROCEDURE [MYSP] AS

Declare @pdesc nvarchar(30)
Declare @ssc int
Declare @myid int
Declare @name nvarchar(30)

Declare new_cur CURSOR DYNAMIC FOR
SELECT ProductDescription, SubSubCatID
FROM C2000HPB
FOR UPDATE

Open new_cur
FETCH FROM new_cur INTO @pdesc, @ssc
While @@FETCH_STATUS = 0

BEGIN
Declare new_cur2 CURSOR DYNAMIC FOR
SELECT SubSubCatID, SubSubCategory FROM SSC
FOR READ ONLY

Open new_cur2
FETCH FROM new_cur2 INTO @myid, @name
While @@FETCH_STATUS = 0

BEGIN
IF PATINDEX ('@name%',@pdesc) = 0
Set @ssc = @myid
UPDATE C2000HPB
SET SubSubCatID = @ssc
FETCH NEXT FROM new_cur2 INTO @myid, @name

END

Close new_cur2
DEALLOCATE new_Cur2
FETCH NEXT FROM new_cur INTO @pdesc,@ssc
END
Close new_cur
DEALLOCATE new_Cur

View Replies !

Make A Dynamic Cursor In A Stored Procedure
I need im my aplication to meke a "Cursor" in a execution of a stored procedure.

For sample:

In a table with a report definition I have the "Fileds, From, Group, Order " clausulas and I need make a cursor with a contents of this fileds.

How can I do ???

My code:

Declare @idRelat int, @cmd_FROM nvarchar(1024), @cmd_Det nvarchar(50)
SELECT @idRelat = idRelat, @cmd_Det = cmd_DET
FROM RelatÃ³rios WHERE Nome = @p_Relat

Declare @Tot_Col smallint, @Tot_Lin smallint, @Campos smallint,
@Aux_Select nvarchar(1024), @Aux_Group nvarchar(1024), @Aux_Order nvarchar(1024)

Select @Tot_Col = 0
Select @Tot_Lin = 0
Select @Campos = 0
Select @Aux_Select = "SELECT " + @cmd_DET + "AS Soma"
Select @Aux_Group = "GROUP BY "
Select @Aux_Order = "ORDER BY "
Declare @a_Local char(1), @a_Linha smallint, @a_Campo nvarchar(50)
Declare cur_Aux insensitive cursor for
SELECT Local, Linha, Campo
From RelatÃ³rios_Margens
WHERE (idRelat = @idRelat)
ORDER BY Local, Linha
Open cur_Aux
Fetch cur_Aux into @a_Local, @a_Linha, @a_Campo
While @@FETCH_status = 0 begin
If @a_Local = "C"
 Select @Tot_Col = @Tot_Col + 1
Else
 Select @Tot_Lin = @Tot_Lin + 1
Select @Campos = @Campos + 1
If @Aux_Group <> "GROUP BY " begin
 Select @Aux_Group = @Aux_Group + ", "
 If @Aux_Order <> "ORDER BY " begin
 Select @Aux_Order = @Aux_Order + ", "
Select @Aux_Select = sSelect + ", " + @a_Campo + " AS Campo" + @Campos
Select @Aux_Group = @Aux_Group + @a_Campo
Select @Aux_Order = @Aux_Order + @a_Campo
Fetch cur_Aux into @a_Local, @a_Linha, @a_Campo
End
Select @Aux_Select = @Aux_Select
-- <<<< MONTA COMANDO SQL
Select @Aux_Select = @Aux_Select + " " + @cmd_FROM + " " + @p_Filtro + " " + @Aux_Group + " " + @Aux_Order
Declare @Cursor_Aux cursor
Set @Cursor_Aux = cursor for @Aux_Select
Open @Cursor_Aux

Not working !!!!

View Replies !