I have some long running stored procedures that I invoke from ADO.NET (using Typed Datasets).
Inside the stored procedure, I like to keep track of the execution by using the PRINT command.
Is there a way of extracting and displaying this PRINT information in .NET during the stored procedure execution?
Hi, after some tooling round, googling, hair-pulling etc i have some prototypes of a simple C#.NET 1.1 console app which can now post a message to my yukon SB service i set up. It calls an internal activation stored proc to store the message in a simple table.
The problem i had was in the connection string "... Asynchronous Processing=true". The run-time throws an exception moaning about the Asynchronous Processing=true bit. So i just omitted it completely... Seems to work fine and as expected. I can post a message, my app exits, and the dummy stored proc (which i deliberately made enter a loop updating the table thousands of times to slow it down and lock the table) completed several seconds later proving the asychronousity of it (i issued a "select count(*) from X" query to wait for the write lock to be released, so i knew when the stored proc had finished and released its lock).
So, i guess my question is, firstly what is the "Asynchronous Processing=true" bit for in the connection string if we can just leave it out? And can anyone see any issues with what ive done?
Hi,
I have created a stored procedure for sending mail. While executing that i am getting the following error message:
SQL Server blocked access to procedure 'sys.sp_OACreate' of component 'Ole Automation Procedures' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ole Automation Procedures' by using sp_configure. For more information about enabling 'Ole Automation Procedures', see "Surface Area Configuration" in SQL Server Books Online.
Can anybody explain me how to resolve it?
Expecting the solution at the earliest
Hello
How can I send a list(s) of data into a stored procedure for processing?
For instance, I have a table: GroupContacts(groupname, userid, contactid).
At times I will be inserting X amount of records into it, X depends on how many contacts need to be added to a group by a user.
How can I send a list of (groupname, userid, contactid)'s into a stored procedure and then use some kind of for-loop to iterate through the list and insert the records?
Thanks
jenn
Dear All,
I need to create a functionality in SQL server 2000 that would send out data from a table to users within our company.
Is there some built in funcitonality wherby i can mail out the selected data from the table from a stored procedure? Then i could schedule the stored procedure to be run every morning at 7 am.
Never done this, can some one let me know if that would work and give me some pointers on what commands to use in the stored procedure? Thanks in advance!
I was wondering if there are any potential issues/drawbacks of a service sending a message to itself. Basically I have a situation where I only require one queue to hold messages. So say for instance I had a service named S1 associated with a queue named Q1 then I can do this:
Code Snippet
Begin conversation dialog @dialog_handle
from S1
to 'S1'
on contract MyContract
with encryption = off;
Send on conversation @dialog_handle
message type MyMessage
This works as expected. Any thoughts?
I have created a DTS package. I want to know how to call/run this package from a stored procedure.
Please let me know how to call this DTS from a Stored Procedure.
Thanks.
-Rajesh
In a VB.NET script, I am adding the dbnull value to a parameter that will populate a smalldatetime column:
cmd.Parameters.Add("@unitHdApprove", System.DBNull.Value.ToString)
The stored procedure then defines the input as smalldatetime:
@unitHdApprove smalldatetime,
However, the result is that the record is inserted with 1/1/1900 as the date value, instead of <NULL>.
I'm guessing that this occurs because the conversion of a '' to date will return 1/1/1900, and VB requries the parameter value to be a string (at least with this syntax), so System.DBNull.Value.ToString really equals ''.
I've rewritten the proc to accept the date as a string instead, and then for each date, cast it to a smalldatetime or set it to null as is appropriate. But this is a really bulky way to do things with more than a few dates! Is there any way to change what is in my VB code so that the procedure will insert the actual null value?
Thanks,
Sarah
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
Hi all..I'm trying to send multiple INT values to a Stored Procedure that willbe handled in an IN statement.ASP Code:strSQL = "SP_Get_Selections '29, 32'where 29 and 32 are 2 integer valuesNow, in my stored procedure... I would like to look these values up inan IN statement likeCREATE Procedure SY_GET_SELECTIONS@authorid varchar(20)SELECT * FROM Authors WHERE AuthorID IN (@authorid)But when I use this, I get [Microsoft][ODBC SQL Server Driver][SQLServer]Syntax error converting the varchar value '29, 32' to a columnof data type int.Any help would be great.Thanks
View 1 Replies View RelatedHello,
Yesterday I made the HelloWorld_CLR sample work and figured today I would create a simple windows service to immulate its functionality. When this was not working I tried the sample again and it stopped working. I have tried several things and nothing has worked. Has anyone seen this before or have any ideas on how I can get it working again? I even tried removing SQL Server and its samples then reinstalling to give me a clean slate but that did not work. I did not make any changes to the software nor did I make any changes to the database however the sample did so I need to make sure I can fix this incase it happens in development not to mention I need to continue development.
The Client Queue is normally empty but sometimes has a request to two in it. The Server Queue has alot in it that are both Request and an unspecified type or has a URL for a type. I would paste it in but it takes too much space. Please let me know if you have any ideas because I am not sure what else to try.
Bellow is a sample of the output from running this sample.
Connecting to SQL Server instance
Connected to SQL Server instance
Transaction 1 begun
Dialog begun from service (HelloWorldClient) to service (HelloWorldService)
Message sent of type 'Request'
Transaction 1 committed
Transaction 2 begun
Waiting for Response....
No message received - Ending dialog with Error
Transaction 2 committed
Connection closed - exiting
Press Enter to Exit
Thanks,
Scott Allison...
We're looking to send messages to an SSB Queue (our 'Notification' queue) that contains multiple values.. like Message #1: 20,1 to indicate Record #20, send Notification #1.. the reason for this is we have lots of different types of records in our database, and based on certain conditions, we would like to send different notices regarding these various record types.
What is the best format to send mesages like this? We thought about sending TABLE vars.. what other options are there?
if i send the string
2,3,4,5 to a stored procedure...
is there a way i could split those values for input into the database? no, right? i would need a seperate stored procedure that would take each value one at a time...correct?
Can anyone help,
I have a problem with calling my stored procedure in vb and passing in its parameters. the system is supposed to then use the store the procedures recordset and use this to pass into a crystal report.
I am getting the following message:
Procedure 'TestReport' expects parameters '@StartDate', which was not supplied.
This happens on the objCom.Execute line.
This is my code:
Dim CRXApplication As New CRAXDRT.Application
Dim CRXReport As CRAXDRT.Report
Dim CRXDatabase As CRAXDRT.Database
Set CRXReport = CRXApplication.OpenReport(APPConst.DatabasePath & stReport & ".rpt")
Dim objCom As adodb.Command
Set objCom = New adodb.Command
Dim prm_one As adodb.Parameter
Dim prm_two As adodb.Parameter
Dim prm_three As adodb.Parameter
Dim fdate, tdate As Date
Call CRXReport.Database.Tables(1).SetLogOnInfo("dell330", "Rota", "RotaAdmin", "dadcahadfu")
Set CRXDatabase = CRXReport.Database
fdate = cboFrom.Value
tdate = cboTo.Value
With objCom
.CommandText = "TestReport"
.CommandType = adCmdStoredProc
.ActiveConnection = cn.ConnectionString
Set prm_one = .CreateParameter("@StartDate", adDBTimeStamp, adParamInput, 8, fdate)
.Parameters.Append prm_one
Set prm_two = .CreateParameter("@EndDate", adDBTimeStamp, adParamInput, 8, tdate)
.Parameters.Append prm_two
Set prm_three = .CreateParameter("@Team", adInteger, adParamInput, 4, 2)
.Parameters.Append prm_three
.Parameters.Refresh
End With
Set SQLRecs = objCom.Execute
CRXReport.DiscardSavedData
CRXDatabase.SetDataSource SQLRecs, 3, 1
This is my Stored Procedure:
CREATE PROCEDURE [dbo].[TestReport] @StartDate datetime, @EndDate datetime, @Team int AS
SELECT dbo._Staff.UniqueName, 2 * DATEDIFF(day, @StartDate, dbo._StaffUnavailable.[Date]) + dbo._StaffUnavailable.TimeCode AS Expr1,
COUNT(dbo._StaffUnavailable.ID) AS CountOfID, dbo._StaffUnavailable.[Date], dbo._Team.Description AS Team, dbo._Staff.Team_ID
FROM dbo._Staff INNER JOIN
dbo._StaffUnavailable ON dbo._Staff.Staff_ID = dbo._StaffUnavailable.Staff_ID INNER JOIN
dbo._Team ON dbo._Staff.Team_ID = dbo._Team.ID
GROUP BY dbo._Staff.Staff_ID, dbo._Staff.UniqueName, 2 * DATEDIFF(day, @StartDate, dbo._StaffUnavailable.[Date]) + dbo._StaffUnavailable.TimeCode,
dbo._StaffUnavailable.[Date], dbo._Team.Description, dbo._Staff.Team_ID
Having Team_ID = @Team AND ([Date] >= @StartDate AND [Date] <= @EndDate)
GO
Thanks in advance
Ian.
hi there,
the subject line says it all. is there a way to send nicely formatted html email from within a stored procedure that returns a dataset?
thanks!
chris
I keep getting this error on one of my pages when trying to do an update to the table via a stored procedure:
*System.Data.SqlClient.SqlException: Procedure or function 'sp_u_contractor_references' expects parameter '@p_contractorrfid', which was not supplied
I have verified that I am adding this parameter and that the spelling is correct. I have also made sure that the value is not NULL.
I'm confused as to what else the problem could be. Any ideas?
Hello, I am getting many of these messages in my server's event log (approximately 13 every 5 seconds or so). I have tried clearing the queue with "END CONVERSATION @ConvHandle WITH CLEANUP;" but the event log keeps getting messages. I have attached an example below.
Type: Information
Event Source: MSSQLSERVER
Event Category: (2)
Event ID: 9724
Date: 7/10/2007
Time: 3:52:37 PM
Description:
The activated proc [dbo].[SqlQueryNotificationStoredProcedure-32e779eb-edcb-44d1-ba30-93f46ef9d9f8] running on queue HoudiniPlatform.dbo.SqlQueryNotificationService-32e779eb-edcb-44d1-ba30-93f46ef9d9f8 output the following: 'Could not find stored procedure 'dbo.SqlQueryNotificationStoredProcedure-32e779eb-edcb-44d1-ba30-93f46ef9d9f8'.'
Hi.
How can I get the messages sent by the server while I'm executing a stored procedure via the JDBC driver?
I need to get my own debug messages (done through the print() function)
and also standard server messages (such as "x row(s) affected" or
results from SET STATISTICS TIME ON). Is this possible?
Many thanks.
Carlos
I am unable to send null values through the Microsoft JDBC 1.1 driver to a stored procedure. Please look at the thread already started on the SQL Server Transact SQL Forum at http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1772215&SiteID=1
View 1 Replies View Related
Hello, I've been banging my head trying to figure this one out so I appreciate any help you guys can provide.
I've setup and configured SQL 2005's Database mail and have sent a few plain and simple text emails and I receive it fine at the destination.
I now want to send mime messages but I'm not sure what I'm doing wrong. I took the following source from an email that linked-in sent me which was a mime message:
"
Mime-Version: 1.0
Content-Type: multipart/alternative;
boundary='----=_Part_434343432'
------=_Part_434343432
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit
Plain Text Goes Here
------=_Part_434343432
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 7bit
<html>
<body>
<b>HTML Goes here</b>
</body>
</html>
------=_Part_434343432--
"
and put the above text in the @body parameter of the stored proc msdb.dbo.sp_send_dbmail and submitted it.
When I received the email, it didn't appear as html. Instead, I just got the mime message above as plain text in my email as if someone just typed it and sent it to me. What do I need to do for SQL Database Mail to send the above as a MIME message?
Thanks in advance for any help
Hello,
I'm using ASP.Net to update a table which include a lot of fields may be around 30 fields, I used stored procedure to update these fields. Unfortunatily I had to use a FormView to handle some TextBoxes and RadioButtonLists which are about 30 web controls.
I 've built and tested my stored procedure, and it worked successfully thru the SQL Builder.The problem I faced that I have to define the variable in the stored procedure and define it again the code behind againALTER PROCEDURE dbo.UpdateItems
(
@eName nvarchar, @ePRN nvarchar, @cID nvarchar, @eCC nvarchar,@sDate nvarchar,@eLOC nvarchar, @eTEL nvarchar, @ePhone nvarchar,
@eMobile nvarchar, @q1 bit, @inMDDmn nvarchar, @inMDDyr nvarchar, @inMDDRetIns nvarchar,
@outMDDmn nvarchar, @outMDDyr nvarchar, @outMDDRetIns nvarchar, @insNo nvarchar,@q2 bit, @qper2 nvarchar, @qplc2 nvarchar, @q3 bit, @qper3 nvarchar, @qplc3 nvarchar,
@q4 bit, @qper4 nvarchar, @pic1 nvarchar, @pic2 nvarchar, @pic3 nvarchar, @esigdt nvarchar, @CCHName nvarchar, @CCHTitle nvarchar, @CCHsigdt nvarchar, @username nvarchar,
@levent nvarchar, @eventdate nvarchar, @eventtime nvarchar
)
AS
UPDATE iTrnsSET eName = @eName, cID = @cID, eCC = @eCC, sDate = @sDate, eLOC = @eLOC, eTel = @eTEL, ePhone = @ePhone, eMobile = @eMobile,
q1 = @q1, inMDDmn = @inMDDmn, inMDDyr = @inMDDyr, inMDDRetIns = @inMDDRetIns, outMDDmn = @outMDDmn,
outMDDyr = @outMDDyr, outMDDRetIns = @outMDDRetIns, insNo = @insNo, q2 = @q2, qper2 = @qper2, qplc2 = @qplc2, q3 = @q3, qper3 = @qper3,
qplc3 = @qplc3, q4 = @q4, qper4 = @qper4, pic1 = @pic1, pic2 = @pic2, pic3 = @pic3, esigdt = @esigdt, CCHName = @CCHName,
CCHTitle = @CCHTitle, CCHsigdt = @CCHsigdt, username = @username, levent = @levent, eventdate = @eventdate, eventtime = @eventtime
WHERE (ePRN = @ePRN)
and the code behind which i have to write will be something like thiscmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@eName", ((TextBox)FormView1.FindControl("TextBox1")).Text);cmd.Parameters.AddWithValue("@ePRN", ((TextBox)FormView1.FindControl("TextBox2")).Text);
cmd.Parameters.AddWithValue("@cID", ((TextBox)FormView1.FindControl("TextBox3")).Text);cmd.Parameters.AddWithValue("@eCC", ((TextBox)FormView1.FindControl("TextBox4")).Text);
((TextBox)FormView1.FindControl("TextBox7")).Text = ((TextBox)FormView1.FindControl("TextBox7")).Text + ((TextBox)FormView1.FindControl("TextBox6")).Text + ((TextBox)FormView1.FindControl("TextBox5")).Text;cmd.Parameters.AddWithValue("@sDate", ((TextBox)FormView1.FindControl("TextBox7")).Text);
cmd.Parameters.AddWithValue("@eLOC", ((TextBox)FormView1.FindControl("TextBox8")).Text);cmd.Parameters.AddWithValue("@eTel", ((TextBox)FormView1.FindControl("TextBox9")).Text);
cmd.Parameters.AddWithValue("@ePhone", ((TextBox)FormView1.FindControl("TextBox10")).Text);
cmd.Parameters.AddWithValue("@eMobile", ((TextBox)FormView1.FindControl("TextBox11")).Text);
So is there any way to do it better than this way ??
Thank you
I have a process that is running on the os. This process is picking up FTP files every 5 min, it renames them so not to confuse them with the new files, copies all renamed files into one file to be used by bulk insert, runs the bulk insert to populate a table, and then runs the stored procedure that scrubbing the data and insert it into another table. For every transaction that I do in my stored procedure, I do the error checking as follows:
IF @@error <> 0
BEGIN
ROLLBACK TRANSACTION
RETURN
If my stored procedure encounters an error, return statement will stop it from running. If this happens, I need to stop the process that is running on the os as well.
Questions:
How can that be accomplished?
How to restart the stored procedure ones the error has been corrected?
Thank you for your help.
Hello, please help!!
I have spent days searching the web and forums for an answer to this simple question and cannot find an example.
I have built a service broker application on sql server 2005. The application puts some xml on an incoming queue which is basically a few parameters to be used in a query. This queue will then call a stored proc which does some business logic and puts the resulting results in another queue also in xml.
I have written a test harness in SQL to put messages on the inbound queue and then some sql to retrieve the returned code from the outbound queue.
What I want to do is be able to convert the SQL which does this into .net code to be used by an application. i.e. write in .net some code to put xml on a queue and then write some .net code to retrieve xml from another queue.
I wouldn't have thought this would be a difficult thing to do and would have been done hundreds of times, but unable to find anything to simply send and retrieve XML to service broker queues....
thanks for your help.. its really needed. I found some links, but they are really vague and often doing select statments in service broker or something like this. I don't want to call any sql, just send and recieve XML on the queues.
any example code that does this, would be really helpfull
kind regards,
David Weeden
Database Developer
I am building a stored procedure that changes based on the data that is available to the query. See below.
The query fails on line 24, I have the line highlighted like this.
Can anyone point out any problems with the sql?
------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------
This is the error...
Msg 8114, Level 16, State 5, Procedure sp_SearchCandidatesAdvanced, Line 24
Error converting data type varchar to numeric.
------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------
This is the exec point...
EXEC [dbo].[sp_SearchCandidatesAdvanced]
@LicenseType = 4,
@PositionType = 4,
@BeginAvailableDate = '10/10/2006',
@EndAvailableDate = '10/31/2007',
@EmployerLatitude = 29.346675,
@EmployerLongitude = -89.42251,
@Radius = 50
GO
------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------
This is the STORED PROCEDURE...
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[sp_SearchCandidatesAdvanced]
@LicenseType int = 0,
@PositionType int = 0,
@BeginAvailableDate DATETIME = NULL,
@EndAvailableDate DATETIME = NULL,
@EmployerLatitude DECIMAL(10, 6),
@EmployerLongitude DECIMAL(10, 6),
@Radius INT
AS
SET NOCOUNT ON
DECLARE @v_SQL NVARCHAR(2000)
DECLARE @v_RadiusMath NVARCHAR(1000)
DECLARE @earthRadius DECIMAL(10, 6)
SET @earthRadius = 3963.191
-- SET @EmployerLatitude = 29.346675
-- SET @EmployerLongitude = -89.42251
-- SET @radius = 50
SET @v_RadiusMath = 'ACOS((SIN(PI() * ' + @EmployerLatitude + ' / 180 ) * SIN(PI() * p.CurrentLatitude / 180)) + (COS(PI() * ' + @EmployerLatitude + ' / 180) * COS(PI() * p.CurrentLatitude / 180) * COS(PI()* p.CurrentLongitude / 180 - PI() * ' + @EmployerLongitude + ' / 180))) * ' + @earthRadius
SELECT @v_SQL = 'SELECT p.*, p.CurrentLatitude, p.CurrentLongitude, ' +
'Round(' + @v_RadiusMath + ', 0) AS Distance ' +
'FROM ProfileTable_1 p INNER JOIN CandidateSchedule c on p.UserId = c.UserId ' +
'WHERE ' + @v_RadiusMath + ' <= ' + @Radius
IF @LicenseType <> 0
BEGIN
SELECT @v_SQL = @v_SQL + ' AND LicenseTypeId = ' + @LicenseType
END
IF @PositionType <> 0
BEGIN
SELECT @v_SQL = @v_SQL + ' AND Position = ' + @PositionType
END
IF LEN(@BeginAvailableDate) > 0
BEGIN
SELECT @v_SQL = @v_SQL + ' AND Date BETWEEN ' + @BeginAvailableDate + ' AND ' + @EndAvailableDate
END
--SELECT @v_SQL = @v_SQL + 'ORDER BY CandidateSubscriptionEmployerId DESC, CandidateFavoritesEmployerId DESC, Distance'
PRINT(@v_SQL)
EXEC(@v_SQL)
-----------------------------------------------------------------------------------------------------------------
I have the following stored procedure in sql server 2000:
(@nsn varchar 15 int,
@item_nam varchar 255 ouput
)
AS
declare @stockquantity int
select @stockquantity = stockquantity
from inventory
where stockquantity = 0
select @item_nam = item_nam
from inventory where nsn = @nsn
delete from inventory
where nsn = @nsn
and stockquantity = 0
GO
what would the vb.net code be to display the item_nam if the query was successful in deleting a record, but would then retutrn a different mesage if the item was not deleted becasuse the stockquantity was not equal to zero. Right now it returns the item_nam even if the record wsa not deleted.
ANy help is much appreciated.
I don't know if this is possible. However, what i am attempting to do isusing C#'s window forms. I open up an excell sheet stored in my windowsform. The excel sheet stores names of the stored procedures in thatdatabase. I want to know if it's possible to click on that storedprocedure to open up a link to display the code of that stored procedure ofcourse in a read only mode.any suggestions....
View 2 Replies View Relatedhi all,i need to convert these simple PHP code into stored procedure :<?php$result=mssql_query( "SELECT whid, whcode FROM warehouse" );while( $wh = mssql_fetch_object( $result ) ){$result=mssql_query( "SELECT plid, nopl FROM packlist WHERE whid ='" . $wh->whid . "'";while( $pl = mssql_fetch_object( $result ) ){$result=mssql_query( "SELECT qty FROM packlistnmat WHERE plid ='" . $pl->plid . "'";while( $pln = mssql_fetch_object( $result ) ){echo "Stock from " . $wh->whcode . " AND Packing List number " .$pl->plid . " = " . $pln->qty;}}}?>my focus is in nested query, then i can call each field from the query(SELECT whid, whcode...) in sub query.thanks,aCe
View 4 Replies View Relatedhelp BUG in my sql code
i can not see this employee 111111 whan i run this code
SELECT 111111,1,'19/01/2008','29/01/2008' UNION ALL
TNX
Code Block
------------------------
-- create mod cycle shift pattern
------------------------
DECLARE
@shifts_pattern TABLE
(
[PatternId] [int] IDENTITY(1,1 ) NOT NULL,[patternShiftValue] [int]NOT NULL)
declare
@I int
set
@i=0
while
@i < 5
BEGIN
INSERT INTO @shifts_pattern ([patternShiftValue] )
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 6 UNION ALL
SELECT 7 UNION ALL
SELECT 8
set
@i=@i+1
end
-- select * from @shifts_pattern
--- end shift pattern
--------------------------------
declare
@empList
TABLE
( [empID] [numeric](18, 0) NOT NULL,[ShiftType] [int]NULL,[StartDate][datetime]NOT NULL,[EndDate] [datetime] NOT NULL) INSERT INTO
@empList ([empID], [ShiftType],[StartDate],[EndDate])
SELECT 111111,1,'19/01/2008','29/01/2008' UNION ALL
SELECT 222222,2,'29/01/2008','30/01/2008' UNION ALL
SELECT 333333,3 ,'27/01/2008','30/01/2008' UNION ALL
SELECT 444444,5 ,'26/01/2008','30/01/2008'
--------------------------------
-- create shifts table
declare
@empShifts
TABLE
( [empID] [numeric](18, 0) NOT NULL,[ShiftDate] [datetime]NOT NULL,[ShiftType] [int]NULL
,[StartDate] [datetime]NOT NULL,[EndDate] [datetime]NOT NULL)
DECLARE
@StartDate datetime
DECLARE
@EndDate datetime
Declare
@current datetime
DEclare
@last_shift_id int
Declare
@input_empID int
--SET
----@StartDate = StartDate
---- last day of next month
--SET
--@EndDate = EndDate
--@StartDate = DATEADD(m ,2,GETDATE()-DAY (GETDATE()) + 1 )
---- last day of next month
--SET
--@EndDate = DATEADD(m ,3,GETDATE()-DAY (GETDATE()) + 1)- 1
--set
--@current = @StartDate
----
----------------- open list table for emp with curser
DECLARE
List_of_emp CURSOR FOR
SELECT
emp.empId,emp.ShiftType,emp.StartDate,emp.EndDate FROM @empList emp
OPEN
List_of_emp
FETCH
List_of_emp INTO @input_empID , @last_shift_id ,@StartDate,@EndDate
-----------------
-- loop on all emp in the list
while
@@Fetch_Status = 0
begin
-- loop to insert info of emp shifts
while
@current<=@EndDate
begin
INSERT INTO @empShifts ([empID],[ShiftDate],[ShiftType],[StartDate] ,[EndDate])
select @input_empID ,@current,shift .patternShiftValue ,@StartDate,@EndDate
from @shifts_pattern as shift where PatternId=@last_shift_id+1
-- if it is Friday and we are on one of the first shift we don't move to next shift type .
if (DATENAME(dw ,@current) = 'Friday' ) and
EXISTS(select ShiftType from @empShifts where ShiftDate=@current and empID= @input_empID and ShiftType in ( 1,2,3))
-- do nothing
--set @last_shift_id=@last_shift_id
print ('friday first shift')
ELSE
set @last_shift_id=@last_shift_id+ 1
set @current=DATEADD( d,1, @current)
end
FETCH
List_of_emp INTO @input_empID ,@last_shift_id,@StartDate,@EndDate
-- init of start date for the next emp
set
@current = @StartDate
end
CLOSE
List_of_emp
DEALLOCATE
List_of_emp
select
empID,shiftDate,DATENAME (dw,shift.ShiftDate ), shiftType from @empShifts as shift
RETURN
I need to have my middle tier code get the T-SQL code for a stored procedure. So, I need to create a string something like:
string tsqlCode = @"CREATE PROCEDURE "NSInsertGenerator"
@GeneratorName NVARCHAR(255),
@SystemName NVARCHAR(255),
@ThreadPoolSize INT,
@ActivationState TINYINT
AS
INSERT INTO "NSGenerators"
( GeneratorName, SystemName, ThreadPoolSize, ActivationState)
VALUES(@GeneratorName, @SystemName, @ThreadPoolSize, @ActivationState)
GO"
This string will be analyzed by the middle tier code so that the list of parameters for this procedure will be built dynamically depending on the current version of this stored procedure.
We are using SQL Server 2000 SP4.
Could someone recommend me how I can get T-SQL code of a stored procedure, please?
Thanks a lot!
Pls check my code for the stored procedure which i created for the companydetails including companyid P.K. Not Null int(4),companyname Not Null varchar (20),address varchar(30) where companyid is the primary key and it should be autogenerate.I also want that it should check if the name exits or not.It should also check that the field is entered and not kept null.If it's null then should return the error message.I want to write the queries select,insert,update and delete in the single stored procedure.How can i differ all the query individually in a stored procedure.The select and insert query are done on the button click event whereas the update,delete queries are performed in the gridview link event. Pls help me and modify my code accordingly with ur suggestions who know
the stored procedure very well.First read what i want then give reply.waiting for the reply and with corrections.The coding is perfomed in sql server 2005 and asp.net with C# 2005, 1 ALTER PROCEDURE CompanyStoredProcedure2 @uspcompanyid int,3 @uspcompanyname varchar(20),4 @uspaddress1 varchar(30), 5 @frmErrorMessage as varchar(256) OUTPUT,6 @RETURNVALUE as int OUTPUT,7 @RETURNID as int OUTPUT8 AS9 declare
10 @companyid int,11 @companyname varchar(20),12 @address1 varchar(30) 13 14 BEGIN15 16 begin17 Select @RETURNVALUE = -918 RETURN -919 end20 21 begin22 Select @frmErrorMessage = 'The Operation Mode Has Not Been Specified'
23 return -924 end25 26 27 28 begin 29 --validation...
30 if (@uspcompanyname is Null or @uspcompanyname = '')31 begin32 Select @RETURNVALUE = -933 select @frmErrorMessage = 'Company Name is empty'
34 return -935 end 36
37 if exists (select companyid from companymaster 38 where upper(companyname) = upper(cast(@uspcompanyname as varchar(20))))39 begin40 select @companyid = companyid from companymaster 41 where upper(companyname)=upper(cast(@uspcompanyname as varchar(20) ) )42 end43 else 44 45 select @companyname= cast (@uspcompanynameas varchar(20))46 select @address1= cast(@uspaddress1 as varchar(30))47 select @companyid = isnull(max(companyid),0) + 1 from companymaster48 49 IF exists(SELECT * from companymaster where companyname=@companyname)50 begin51 Select @frmErrorMessage = 'Record With Company Name ' 52 + @companyname + ' is Already Exisiting For The Company Name ' 53 return -954 end 55 56 -- the following codes inserts
57 begin transaction58 INSERT INTO companymaster59 ( companyname, address1)60 VALUES (@companyname,@address1)61 commit transaction62 63 select @RETURNVALUE = 064 select @RETURNID = @companyid65 66 end67 68 69 -- the following codes edit/updates
70 begin71 UPDATE companymaster 72 SET companyname=@companyname,73 address1=@address1 74 WHERE companyid =cast(@uspcompanyid as int)75 76 select @RETURNVALUE = 077 select @RETURNID = cast(@uspcompanyid as int)78 end79 -- the following codes delete
80 begin81 DELETE companymaster WHERE (companyid = @companyid)82 end 83 84 END 85
Pls help me and modify my code accordingly with ur suggestions who know the stored procedure very well.First read what i want then give reply.
I have looked all around and I am having no luck trying to figure out how to alter a stored procedure within an asp.net application.Here is a short snippet of my code, but it keeps erroring out on me.Try myCommand.CommandText = "Using " & DatabaseName & vbNewLine & Me.txtStoredProcedures.Text myCommand.ExecuteNonQuery() myTran.Commit()Catch ex As Exception myTran.Rollback() Response.Write(ex.ToString())End Try The reason for this is because I have to propagate stored procedures across many databases and was hoping to write an application for it.Basically the database name is coming from a loop statement and I just want to keep on going through all the databases that I have chosen and have the stored procedure updated (altered) automatically So i thought the code above was close, but it keeps catching on me. Anybody's help would be greatly appreciated!!!
View 3 Replies View RelatedHi,
I am using vs2005, vc++, sql2005 and trying to write a complex extended stored procedure.
It seems I must use switch clrafe for my executable to deploy to sql.
But with clrafe, I can no longer use such c code tools such as typedef structures and pointer manipulation!
But I need to do these things to translate a contiuous block of binary data into a series of field/value pairs to be written to the sql server!!! Doing so externally would mean a severe drop in throughput: going back to the server for each field instead of all as one block!
Is there a technique, or pragma command to allow "unmanaged" c code to reside in a sql extended stored procedure?
Hi,
I'm not sure if this is really the right place for this but it is related to my earlier post. Please do say if you think I should move it.
I created a Stored procedure which I want to run from Visual basic (I am using 2008 Express with SQL Sever 2005 Express)
I have looked through many post and the explaination of the sqlConection class on the msdn site but I am now just confussed.
Here is my SP
ALTER PROCEDURE uspSelectBarItemID2
(
@BarTabID INT,
@DrinkID INT,
@ReturnBarItemID INT OUTPUT
)
AS
BEGIN
SELECT @ReturnBarItemID = barItemID
FROM [Bar Items]
WHERE (BarTabID = @BarTabID) AND (DrinkID = @DrinkID)
END
In VB I want to pass in the BarTabID and DrinkID varibles (Which Im grabbing from in as int variables) to find BarItemID in the same table and return it as an int.
What I dont understand is do I have to create a unique connection to my database because it is already liked with a dataset to my project with a number of BindingSources and TableAdapters.
Is there an easier way, could I dispense with SP and just use SQL with the VB code, I did think the SP would be neater.
Cheers.