Dynamic Code Activated By Sp_executesql Can Anyone Help Me With That One?
Apr 2, 2008
Hi everyone
I try to run Dynamic sql wherby sp_executesql as follows:
Code Snippet
DECLARE @params NVARCHAR(4000)
DECLARE @portion INT
SET @portion=6
DECLARE @mydynamic NVARCHAR(4000)
SELECT @mydynamic = ' SELECT TOP @portion * FROM server.database.dbo.mytable'
SELECT @params = N'@portion INT '
EXEC sp_executesql @mydynamic,@params, @portion
I get the following error message:
Code Snippet
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '@portion'.
Any idea what is wrong with that code?Thanks
View 6 Replies
ADVERTISEMENT
Jul 16, 2005
I have a stored procedure into which a bit type of parameter called '@activated' is being passed. This stored procedure gets all the users matching the condition :
Activated = isnull(@activated, -1)
In users table, one of the columns is 'Activated' of bit type.
The strange thing happening here is that if @activated is null, then the above condition is true for all rows in users table, even though we know that the column Activation is either 1 or 0 only and never -1.Can someone explain why this strange thing is happening?
View 2 Replies
View Related
Apr 2, 2008
Hi everyone,
The following code to run dynamic sql:
DECLARE @params NVARCHAR(4000)
DECLARE @portion INT
SET @portion=6
DECLARE @mydynamic NVARCHAR(4000)
SELECT @mydynamic = ' SELECT TOP @portion * FROM server.databse.dbo.table'
SELECT @params = N'@portion INT '
EXEC sp_executesql @mydynamic,@params, @portion
yields the following error message:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '@portion'.
anyone has any idea why ? How it should be corrected?
Thanks
View 11 Replies
View Related
Sep 4, 2007
Here is the query..
@ENTITY, @ FIELD, @KEYID, @VALUE comes dynamically using cursor. Here in this example I have took one sample and assigned it a value to do sanity check.
DECLARE @ENTITY nvarchar (100)
SET @ENTITY ='AccidentDimension'
DECLARE @FIELD nvarchar (100)
SET @FIELD = 'UHReceivedDate'
DECLARE @KEYID nvarchar (100)
SET @KEYID = '1074958'
DECLARE @VALUE varchar (100)
SET @VALUE = '10JAN2020'
DECLARE @FLAG NVARCHAR(50);
SET @FLAG = (SELECT LEFT(@ENTITY, (SELECT CHARINDEX( 'DIM', @ENTITY) -1)) )+ 'LastUpdateFlag';
DECLARE @KeyName NVARCHAR(50);
SET @KeyName = (SELECT LEFT(@ENTITY, (SELECT CHARINDEX( 'DIM', @ENTITY) -1)) )+ 'Key'
DECLARE @KeyValue INT
DECLARE @SQL1 NVARCHAR (1000)
SET @SQL1 = ' SET @KeyValueOUT = Select '+ @KeyName + ' FROM ClaimManagementFact WHERE ClaimKey = ' + @KEYID +
' GROUP BY ' + @KeyName + ' HAVING SUM(TotalClaimCount) > 0 OR SUM(IncidentOnlyClaimCount) > 0 )'
EXECUTE sp_executesql @SQL1, @KeyValueOUT INT OUTPUT;
@KeyValue= @KeyValueOUT OUTPUT;
Select @KeyValue
A) What i want to do is store the value resulting from select statemenet by executing @SQL1 which is INT to @KeyValue. In previous thread I tried various thing but resulting in errors.
Thanks in advance for help
View 6 Replies
View Related
Sep 20, 2004
Hi there,
I am trying to build a proc that uses a loop to import data into several tables. The data is copied into the appropriate table according to the contents of the variable @PracticeCode. I am also trying to add a date value to each record as it is added to the table. I thought that the best way to do this would be t use the sp_executesql stored proc. but I am having difficulty getting it to work. Here's what I have done so far:
-- insert data into proper tables with extract date added
SET @SQLString ='INSERT INTO GMS_48hrAccess.dbo.tbl_Surgery'+@PracticeCode+' SELECT
SurgeryKey,'+
@extractDate+',
ClinicianCode,
StartTime,
SessionGroup,
[Description],
SurgeryName,
Deleted,
PremisesKey
FROM GMS_48hrAccess.dbo.tbl_SurgeryIn'
EXEC master..sp_executesql @SQLString
And here's the error message that I get:
Server: Msg 241, Level 16, State 1, Line 90
Syntax error converting datetime from character string.
I understand why I am getting this error I just can't seem to fix it. I've consulted BOl and have tried various Parameter combinations but to no avail.
Can anyone help?
Thanks
View 1 Replies
View Related
Jan 11, 2008
For the example stored procedure below, lets say I want to use the "sp_executesql" stored procedure instead of "EXECUTE".
CREATE PROCEDURE [dbo].[spGetEmployees]
@managerId int,
@employeeIdList nvarchar( 200 )
AS
EXECUTE
(
'SELECT *
FROM [dbo].[hrEmployees]
WHERE [ManagerID] = ' + CAST( @managerId AS nvarchar ) + '
AND [EmployeeID] IN (' + @employeeIdList + ')'
)
I want to rewrite it something like this. Please see MSDN documentation ( http://msdn2.microsoft.com/en-us/library/ms188001.aspx ) for sp_executesql stored procedure usage.DECLARE @selectStatement nvarchar(500)
SET @selectStatement = 'SELECT * FROM [dbo].[hrEmployees] WHERE [ManagerID] = @paramManagerID AND [EmployeeID] IN (' + @employeeIdList + ')'
DECLARE @paramList nvarchar(500)
SET @paramList = '@paramManagerID int'
EXECUTE sp_executesql @selectStatement, @paramList, @paramManagerID = @managerId
Reason for using "sp_executesql" is the performance gain.However, as you can see, the @employeeIdList cannot be included as part of the Parameter List ( @paramList )like the @managerId since it **has** to be passed in as a varchar ( example: @employeeIdList = '1,2,3,4' ).
My Question Is there a way to include it as a parameter instead of it being part of the embedded dynamic SQL syntax?
View 1 Replies
View Related
Apr 18, 2006
hi
I am trying to store the output of sp-executesql into a variable to implement it as a user defined function later
The function is
ALTER function [dbo].[UnitsAvailable] (@id int)
returns int
as
begin
declare @sql nvarchar(100)
declare @params nvarchar(500)
declare @count nvarchar(10)
set @sql = N'Select count(*) from units where projectid=' + convert(varchar,@id) + 'and sold=0 and displayunit=1'
set @params = N'@countOUT nvarchar(10) OUTPUT';
exec sp_executesql @sql, @params, @countOUT=@count OUTPUT;
return @count
end
The result is that I am able to parameterize the sql end execute with the right result. The only problem is that the value is not stored in the variable @count. I could get to the same result using managed code in sql 2005 but still I am curious to find out where the problem is ....
Can you please help?
Thanks Alex
View 6 Replies
View Related
Aug 12, 2006
Okay, so I came across an odd performance issue that I'm wondering if some guru can help me out with.
I have a query that uses a paging algorithm that uses a paging algorithm and a table variable, then gets a page of data based on a join to that table variable. Here's a simplified query using the algoritm:
--declare table variable... not shown for brevity
--make sure we only store the least amount of records possible
SET ROWCOUNT ( @pageNumber + 1 ) * @pageSize
--insert into table variable
INSERT INTO @TableVariable( Key )
SELECT key FROM table
WHERE whatever = @p1
--we only want one page of data
SET ROWCOUNT @pageSize
--now get the page of data from the table
SELECT key FROM table
WHERE whatever = @p1
AND [TableVar Identity Column] > @pageNumber * @pageSize
The algorithm works great for our needs, BUT, I noticed something a little odd about its behavior during performance testing.
In particular, when I run the query using Sql Server Management Studio, where I manually DECLARE all the variables it ends up needing only 156 reads to complete the job. When I call it from the app using ADO.NET, however, I noticed it needs 310 reads! Huh?
I looked for differences, and the only one I could determine was that ADO.NET passes the query and uses sp_executesql and passes the parameters vs. declaring and setting them statically before executing the query. I confirmed that this was the issue by manually running sp_execute SQL and seeing that it took roughly the same number of reads (274) to process the query.
Naturally, I don't want the time it takes to perfrom my query to double, but and frankly I don't understand why there would be a difference in performance. Can anyone help me track down what is going on and suggest to me how to fix the problem.
I assume that SQL Server Management Studio optimizes the execution path somehow, but I'm not sure how to gain the same benefit for my passed query. Can I enable something with hints? Is there something else going on that I should know about?
View 10 Replies
View Related
Jul 23, 2005
This is a odd problem where a bad plan was chosen again and again, butthen not.Using the profiler, I identified an application-issued statement thatperformed poorly. It took this form:exec sp_executesql N'SELECT col1, col2 FROM t1 WHERE (t2= @Parm1)',N'@Parm1 int', @Parm1 = 8609t2 is a foreign key column, and is indexed.I took the statement into query analyzer and executed it there. Thequery plan showed that it was doing a scan of the primary key index,which is clustered. That's a bad choice.I then fiddled with it to see what would result in a good plan.1) I changed it to hard code the query value (but with the parmdefinition still in place. )It performed well, using the correct index.Here's how it looked.exec sp_executesql N'SELECT cbord.cbo1013p_AZItemElement.AZEl_Intid AS[Oid], cbord.cbo1013p_AZItemElement.incomplete_flag AS [IsIncomplete],cbord.cbo1013p_AZItemElement.traceflag AS [IsTraceAmount],cbord.cbo1013p_AZItemElement.standardqty AS [StandardAmount],cbord.cbo1013p_AZItemElement.Uitem_intid AS [NutritionItemOid],cbord.cbo1013p_AZItemElement.AZeldef_intid AS [AnalysisElementOid] FROMcbord.cbo1013p_AZItemElement WHERE (Uitem_intid= 8609)', N'@Parm1 int',@Parm1 = 8609After doing this, re-executing the original form still gave badresults.2) I restored the use of the parm, but removed the 'exec' from thestart.It performed well.After that (surprise!) it also performed well in the original form.What's going on here?
View 3 Replies
View Related
Oct 18, 2007
Hi all--Given a table with the following structure on SQL Server 2005 SP2:
CREATE TABLE [dbo].[Contact_Data](
[COMPANY] [nvarchar](255) NULL,
[CONTACT] [nvarchar](255) NULL,
[LASTNAME] [nvarchar](255) NULL,
[DEPARTMENT] [nvarchar](255) NULL,
[TITLE] [nvarchar](255) NULL,
[PHONE1] [nvarchar](255) NULL,
[PHONE2] [nvarchar](255) NULL,
[PHONE3] [nvarchar](255) NULL,
[FAX] [nvarchar](255) NULL,
[EXT1] [nvarchar](255) NULL,
[EXT2] [nvarchar](255) NULL,
[ADDRESS1] [nvarchar](255) NULL,
[ADDRESS2] [nvarchar](255) NULL,
[CITY] [nvarchar](255) NULL,
[STATE] [nvarchar](255) NULL,
[ZIP] [float] NULL,
[KEY1] [nvarchar](255) NULL,
[KEY2] [float] NULL,
[KEY3] [nvarchar](255) NULL,
[KEY4] [nvarchar](255) NULL,
[U_COMPANY] [nvarchar](255) NULL,
[U_CONTACT] [nvarchar](255) NULL,
[U_LASTNAME] [nvarchar](255) NULL,
[U_CITY] [nvarchar](255) NULL,
[U_STATE] [nvarchar](255) NULL,
[U_KEY1] [nvarchar](255) NULL,
[U_KEY2] [nvarchar](255) NULL,
[U_KEY3] [nvarchar](255) NULL,
[U_KEY4] [nvarchar](255) NULL,
[U_KEY5] [nvarchar](255) NULL,
[Corporate] [nvarchar](255) NULL,
[Account_Role] [nvarchar](255) NULL,
[Premise_Role] [nvarchar](255) NULL
)
I'm trying to write some dynamic SQL that cycles through all the column names in a table, checks through all the rows in that table for any blank rows of data and assign the rows count result to a variable called 'blankrow'. Here's my code so far:
declare @blankrow int,
@sqlstr nvarchar(2048),
@sqlstr2 nvarchar(2048)
set @sqlstr2 = N'select name from sys.all_columns
where object_id = 1831117714'
set @sqlstr = N'SELECT count(*)
FROM [Contact_Data]
where (' + @sqlstr2 + ') IS NOT null'
exec sp_executesql @sqlstr
set @blankrow=convert(nvarchar, @sqlstr)
print @blankrow
set @sqlstr='The number of blank rows found= ' + CAST(@@ROWCOUNT as Varchar(5)) + ' rows.'
go
Here are the errors I get:
1. For this code:
set @sqlstr2 = N'select name from sys.all_columns
where object_id = 1831117714'
set @sqlstr = N'SELECT count(*)
FROM [Contact_Data]
where (' + @sqlstr2 + ') IS NOT null'
exec sp_executesql @sqlstr
Error:
Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
2. For this code:
set @blankrow=convert(nvarchar, @sqlstr)
print @blankrow
set @sqlstr='The number of blank rows found= ' + CAST(@@ROWCOUNT as Varchar(5)) + ' rows.'
I get this error:
Conversion failed when converting the nvarchar value 'SELECT count(*)
FROM [Contact' to data type int.
For the latter error, I know I have to change type from int to nvarchar, but don't know how to do this yet in this context. I suspect I have to do something like this:
execute sp_executsql @sqlstr, @blankrow int, @blankrow=CAST(@sqlstr as nVarchar(5))
Ideas?
Thanks,
Jon
View 8 Replies
View Related
Mar 3, 2006
Hey all,
So here's my deal, I've got a bunch of tables set up like so.
CaseName_Class
CaseName_Address
CaseName_Mailing
The casename obviously changes based on the name of the case, but all table share the "_Class", "_Address" etc.
Now, I have built apps for these to do various things, handling mail, logging claims in, whatever. And at this point in time each case has a new directory with the files inside that directory that all do the same thing. Reason why this sucks is that if I change an app in one place, I should be hanging it in about 100 other places which causes...
So, what I am attempting to do is created a stored proc in SQL to handle one of the applications, pass in a few variables and make all of the cases run off of that one proc...yay! go me.
Now...due to the fact I have to pull the case prefix name everytime the proc runs, I am kind of shooting myself in the foot being that the proc has to recompile everytime to get that casename.
The code that is currently there and works, looks like this
Code:
SET @dynSQLBox = N'SELECT @BoxCount1 = COUNT(MailingID) ' + char(10) +
'FROM ' + @Client + '_Mailing' + char(10) +
'WHERE MailEventID = ' + CAST( @MailEventID as varchar(20) ) + char(10) +
'AND MailTypeID = ' + CAST( @MailTypeID as varchar(20) ) + char(10) +
'AND box = ' + @Box
Just as an example that is looking for the max box count inside of a box inside of the specific case.
I am wondering if there is a better way to go about getting data that has to be dynamic inside of a stored proc.
I hope this makes sense, and look forward to talking about it.
Thanks!
Caden
View 1 Replies
View Related
Jan 23, 2008
Hi,
I have a requirement to create #Temp table in database and insert values to it.
I use following code:
DbCommand dbCreateTable;
dbCreateTable = provider.CreateCommand();
dbCreateTable.Connection = conn;
dbCreateTable.CommandText ="Create table #MyTemp (Id varchar(10))";
dbCreateTable.ExecuteNonQuery();
string[] insertValues = {"Insert into #Mytemp values ('TestString1')",
"Insert into #Mytemp values ('TestString2')"};
DbCommand dbInsertData = provider.CreateCommand();
dbInsertData.Connection = conn;
foreach (String insertStr in insertValues)
{
dbInsertData.CommandText = insertStr;
dbInsertData.ExecuteNonQuery();
}
Code creates the Temp table but when it comes to insert statement, it throws error saying "Temp table not found".
Reason can be Create and Insert statement gets executed as 2 different sessions.
How to get the above requirement work fine?
Thank you.
HV
View 4 Replies
View Related
Mar 13, 2007
Hi There
I just had the following scenario.
I checked sys.service_queues to confirm the my queue has an activated proc assigned to it and is activation_enabled.
I send messages to the service, however i see that the messages just sit in the queue. I run profiler with all SB and Error events there is nothing, i check the sql server log, there is also nothing.
So i have no idea why the activated proc is not firing, all i see in the profiler trace is that the messages are acknowledged but the activated proc does not execute.
For a while i was stumped. i then tried to execute the proc myself and i got a syntax error.
Basically what happened was that i altered a table that the proc used and now the proc cannot execute cos there are more columns that must be inserted so i altered the proc.
However the activated proc still did not fire.
I had to disable and re-enable activation for it to work.
What i am wondering is why was there no indication in profiler or the sql og as the the fact that something was wrong with the activated proc ? Surely the activated proc should have fired and given the same error i got either to the sql log or profiler ?
And why did i have to re-enable activation ? Does sql cache the proc when you activate it and not know that the proc has changed when you alter it ?
Thanx
View 4 Replies
View Related
Sep 10, 2006
Hi there
I have multiple queues with the same activated stored procedure (for various reasons we are trying this scenario).
My biggest obsticle is i cannot figure out a way to determine with the activated sp which queue caused it to activate.
Basically i need to make the sp dynamic, so that no matter which queue activated the sp the sp can determine the queue name and use that dynamically to do the receive command from the right queue.
I am sure it is possible since sys.dm_broker_activated_tasks shows how many sp's are activated by each queue, however the sp name is the same for all queues so that does not help me.
How do i determine within an activated sp which queue caused it to activate?
Thanx
View 1 Replies
View Related
Nov 23, 2006
Hi guys!
Is it possible for a stored procedure to be activated more than once even if a message is sent only once? After sending the message, I check the sys.dm_broker_activated_tasks and see that it activates more than once. Is there a way for me to prevent this from happening?
Thanks a lot! :)
View 4 Replies
View Related
Nov 27, 2013
I have 3 tables, please find the table structure and sample data below
Table 1 : tblUserInfo
UserID | FirstName | Email | JoinedDate
1 Testuser01 User01@User.com 10-10-2013
2 Testuser10 User10@User.com 11-10-2013
3 Testuser20 User10@User.com 11-10-2013
Table 2 : tblOffers
OfferID | OfferName | ExpiryDate
1 OfferSample1 15-10-2014
2 OfferSample2 15-9-2014
3 OfferSample3 10-07-2014
Table 3 :tblOfferActivated
ActivationID | UserID | OfferID | ActivationCode | ActivatedDate
1 2 3 ABC 11-11-2013
2 2 1 CEG 13-11-2013
3 3 1 JHG 18-11-2013
4 3 2 KIU 20-11-2013
Expected Output
I want to list out the users with the first activated offer details.The OfferName Should be based on the first activated date
UserID | FirstName | Email | JoinedDate | OfferID | OfferName |ActivatedDate | ActivationCode
1 Testuser01 User01@User.com 10-10-2013 Null Null Null Null
2 Testuser10 User10@User.com 11-10-2013 3 OfferSample2 11-11-2013 ABC
3 Testuser20 User10@User.com 11-10-2013 4 offerSample3 18-11-2013 JHG
View 6 Replies
View Related
Aug 24, 2006
Hi There
I was wondering, i have experienced alot of stored procedure blocking where you have a stored procedure that get executed very frequently and is complex, when the sp has to recompile it is locked and cannot be executed by other processes.
How will this work with an activated stored procedure, our activated stored procedure will probably not be too complex but it may exec complex ones base on the message type.
Now lets say that a message is recieved by the activated sp he in turn executes a complex sp to process the message this causes the complex sp to recompile, during this time another activated sp is spawned by the queue with the same message type he execs the same complex sp that is busy recompiling, i presume that the second activated sp will be blocked until the sp has completed recompiling.
Is this correct, because on a very busy queue with very complex sp's being called by the activated stored procedure, the benefit of having multiple queue readers is negated by the blocking caused by the recompiling sp's called by the activated sp.
Basically no matter how many queue readers kick in to handle the messages they all wait for the sp to recompile from the first message.
I am hoping multiple spawned activated stored procedures somehow over come this? Or work differently somehow.
Does anyone know ?
Thanx
View 3 Replies
View Related
Jan 11, 2007
Hi
I'm having a problem debugging (with VS2005,) a CLR Stored Procedure which is activated on a queue. I know it is working because it is consuming and processing messages when one appears in the queue.
I can debug it 'manually' by either right-clicking on the SP within VS2005 and selecting 'Step into Stored Procedure' or by attaching to the SQL Server Management Studio process, setting a breakpoint and executing the stored procedure from a Management Studio query window.
However, if I send a message to my queue within Management Studio, my breakpoints are NOT being hit within VS2005, but the message IS reaching my queue and it IS being processed by the SP.
Can anyone help?
Thanks
View 3 Replies
View Related
Sep 4, 2007
Hi I have a couple of questions to understand better Service Broker..
Every time I alter the activation Stored Procedure on the receiving queue I also execute the:
alter database set new_broker rollback
1) can somebody explain me in a detailed way why I have to do that? what happen inside SQL Server when there is a Stored Procedure activated on a queue??
Also link to white papers, resouces on so on are appreciated.
My Activation Stored Procedure calls other stored procedure.
2) If ONLY one of the nested stored procedures changes I have to execute the command:
alter database set new_broker rollback ???
I think that the answer to the first question will answer the second one..
Thank you for any helps!
Marina B.
View 3 Replies
View Related
Apr 26, 2006
I've read Remus' article on Fire and Forget tactics when it comes to ending conversations, and I have to admit I am guilty of sending a message to a service and immediately ending the conversation. I've set up a stored procedure to end the conversation on the initiator queue, but I'm guessing it's never being fired because I am not ending the conversation in the target activated stored proc, which is a CLR stored proc.
Can you tell me how to end the conversation from a CLR stored proc?
Now that I think of it none of the code I use in my TSQL activated stored procs to handle different message types and error-checking is done in the CLR stored proc.
Thanks
View 5 Replies
View Related
Aug 2, 2006
Hi There
2 Questions :
1. Almost in every SB example you will see this sql :
BEGIN TRANSACTION
WAITFOR (
RECEIVE TOP (1)
@MessageType = message_type_name,
@Message = message_body
FROM [Queue1]
WHERE conversation_handle = @ConversationHandle
), timeout 5000;
If this sql in an activated sp do you really have to have the waitfor ? Since the sp will only be fired if there is a message on the queue ?
2. It is reccomended that for high volume SB apps you do not do a top(1) receive but process batches. Exactly what is the best practice to do this. Receive a batch into a table variable and then what ? Process through it with a cursor ? That is not very efficient either, i would just like some insight into batch queue processing as everywhere i have seen uses top (1) from the queue ?
Thanx
View 3 Replies
View Related
Oct 19, 2006
Hi everyone! Good day!
I'm not really sure if my question should fall on Service Broker or T-SQL, but I hope someone helps me with this... After activating the stored procedure assigned to the queue, is there any way for me to find out if the stored procedure is already finished executing?
I have successfully sent messages to my queue but I have no way to know if all the processing is already done.
Thanks so much!
View 4 Replies
View Related
Jun 29, 2015
I've data like below:
Now, I've to get active data for a particular date range. Let me explain the active data definition as below:
StartDate : 01-Jul-2015
EndDate : 31-Dec-2015
It should return all the data which was active for that date range even if it was only for one day.If no data found for that date range, check the last record before start date and and if its active then it should be returned else not.
I though of creating a function and pass primary key with date range and return the final status but that doesn't seems like an optimized query.
View 4 Replies
View Related
Oct 23, 2006
I have an activated SP that is issuing PRINT statements.
These are raised as ErrorLog and EventLog events when using SQLProfiler
However if you attach an event notification for ERRORLOG and EVENTLOG, nothing appears in the queue. Any other ERRORLOG or EVENTLOG event caught by profiler is caught by the event notification as expected.
View 2 Replies
View Related
Mar 7, 2008
Hi,
I am getting deadlock on activated procedure which I am using to receive message from the Service Broker Queue.
Deadlock details:
Two threads are tring to do delete on internal table queue_messages_122847900 ends up in a dead lock.
Activated procedure code
RECEIVE TOP(1) @xmlMessage = message_body,
@handle = conversation_handle,
@message_type = message_type_name
FROM TransactionQueue;
IF (@message_type = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog')
BEGIN
END CONVERSATION @handle;
RETURN 0
END
.........................
.........................
After this I do process the message and some other processing
And then
END CONVERSATION @handle;
Note I do have single conversation group
Is their a problem in the way I am receiving and processing messages. Is it possible because of the delay between RECEIVE and END CONVERSATION same message is read by two different threads.
Thanks
View 1 Replies
View Related
Dec 1, 2006
I have a initiator and a target service broker peer.
Both are controlled by a C# unit test. The initiator uses the Microsoft.Samples.SqlServer class. The target service uses stored procedure activation.
Sending a message from the initiator to the target, saves the content of the message, along with its conversation handle in the target's database specific table.
The unit test needs - at a later time - to instruct the target to send a message back on the same conversation handle to the initiator service.
For this the C# unit test creates a Conversation off of the saved conversation handle:
Service client = new Service("cleintservicename", conn, tran);
Conversation dialog = null;
dialog = new Conversation(client, convHandle);
Sending the message on this dialog generates an error "Message body: <Error xmlns="http://schemas.microsoft.com/SQL/ServiceBroker/Error"><Code>-8495</Code><Description>The conversation has already been acknowledged by another instance of this service.</Description></Error>".
Is the error due to the fact that a service - using the activated stored procedure already picked up the conversation, so that a new reference to the service can not be created through the Service class in CLR?
If so, I might need then to skip the activated stored procedure in favor or a CLR service, alltogether?
Any help - greatly appreciated.
View 7 Replies
View Related
Jul 31, 2006
I have been trying to get my dynamic query to work with sp_executesql and I cant seem to figure out this one issue.DECLARE @SQL NVARCHAR(1000)SET @SQL = N'WITH Data AS(SELECT Id, Username, FirstName, LastName, Email, LastLogin, ROW_NUMBER() OVER(ORDER BY @SortExpression) AS RowNumber FROM Users) SELECT * FROM Data WHERE RowNumber BETWEEN @Between1 AND @Between2'EXECUTE sp_executesql @SQL, N'@SortExpression VARCHAR(50), @Between1 INT, @Between2 INT', @SortExpression = 'Email', @Between1 = 1, @Between2 = 10As you can see, the data should get sorted by the value of @SortExpression. However thats not the case. The Data does not get sorted at all no matter that i pass in as the value of @SortExpression.I can't seem to figure out why its not working.
View 2 Replies
View Related
Aug 8, 2005
What is wrong in this query..how can I make it to work
DECLARE @strSQL nVarchar(4000)
DECLARE @Name VArchar(100)
--SET @Name = '''sysdatabase'',''sysindexes'''
SET @Name = ''sysdatabase''
SET @strSQL = 'SELECT * FROM dbo.sysobjects WITH (NOLOCK) WHERE Name IN (@prmName)'
EXECUTE dbo.sp_executesql @strSQL,
N'@prmName varchar(100)',
@prmName= @Name
Note : I do not want to replace the query as
SET @strSQL = 'SELECT * FROM dbo.sysobjects WITH (NOLOCK)
WHERE Name IN ' + @Name + ')' , because my queryplan changes if I do this.
Any work around or anything you guys suggest ..
Thanks.
View 2 Replies
View Related
Feb 27, 2008
I'm having trouble working out why the sp_executesql procedure is not replacing my place holders with the value assigned to it.
Some quick info: I'm running the routine from the commandline through OSQL on a box that has MSSQL2000 enterprise installed. The code is sent to a MSSQL2005 box.
I've noticed one dumb thing I've done and that is making the nvarchar variable @db_name a different size to the one declared in the sp_executesql command. But I'm not sure if that is the problem. It throws a @db_name is not a database error etc.
Snippet that is not working:
declare @db_name varchar(80)
declare @sql_command nvarchar(1500)-- for our dynamic sql command within the cursor loop.
fetch
next
from
settings_cursor
into
@db_name
while
@@fetch_status = 0
begin
print 'CHECKING DBOPTIONS FOR ' + @db_name + ' - ( CHECKSUM, CREATE & UPDATE STATS, FULLRECOVERY)'
set @sql_command ='select'
set @sql_command = @sql_command + 'count(*)'
set @sql_command = @sql_command + 'from'
set @sql_command = @sql_command + 'sys.databases'
set @sql_command = @sql_command + 'where'
set @sql_command = @sql_command + 'name = ''@db_name'''
set @sql_command = @sql_command + 'and'
set @sql_command = @sql_command + 'page_verify_option_desc = ''checksum'''
set @sql_command = @sql_command + 'and'
set @sql_command = @sql_command + 'is_auto_create_stats_on = 1'
set @sql_command = @sql_command + 'and'
set @sql_command = @sql_command + 'is_auto_update_stats_on =1'
set @sql_command = @sql_command + 'and'
-- select recovery model based upon database name.
if @db_name = 'DBAdmin'
or @db_name = 'Master'
or @db_name = 'Model'
or @db_name = 'msdb'
begin
set @sql_command = @sql_command + 'recovery_model_desc = ''simple'''
end
else
begin
set @sql_command = @sql_command + 'recovery_model_desc = ''full'''
end
-- include db chaining for Master database
if @db_name = 'Master'
begin
set @sql_command = @sql_command + 'and'
set @sql_command = @sql_command + 'is_db_chaining_on = 1'
end
-- execute sql command.
--print @sql_command
declare @count int
execute @count = sp_executesql @sql_command, N'@db_name nvarchar(20)',@db_name=@db_name
if @count = 0-- no records were returned as the settings were wrong.
begin
select 'Issue with settings. altering now'
if @db_name = 'DBAdmin'
or @db_name = 'Master'
or @db_name = 'Model'
or @db_name = 'msdb'
begin
alter database [@db_name] set recovery simple
alter database [@db_name] set page_verify checksum
end
else
begin
alter database [@db_name] set recovery full
alter database [@db_name] set page_verify checksum
end
if @db_name = 'msdb'
begin
alter database [@db_name] set db_chaining on
end
-- all databases get these switched on
alter database [@db_name] set auto_create_statistics on
alter database [@db_name] set auto_update_statistics on
end
else
begin
select 'all settings for ' + @db_name + ' are good'
end
fetch next from settings_cursor into @db_name
end
-- clean up
close settings_cursor
deallocate settings_cursor
View 3 Replies
View Related
Jan 18, 2002
Hi
I am trying to execute sp_executesql dynamically. What I am trying to do is read all the user tables using a cursor build sql statement and using
EXEC sp_execute sqlstmt.
Here is piece of code.
DECLARE C1 CURSOR FOR SELECT NAME FROM SYSOBJECTS WHERE XTYPE='U'
OPEN c1
FETCH NEXT FROM C1 INTO @v_TableName
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @v_SQL= 'DROP TABLE ' + @v_TableName
--EXEC @v_SQL
PRINT @v_SQL
IF @v_Error<>0
BEGIN
SELECT @ErrorCount=@ErrorCount+1
PRINT 'ERROR OCCURED WHILE DROPING TABLE ' + @v_TableName
--GOTO ErrorHandler
END
FETCH NEXT FROM C1 INTO @v_TableName
END
CLOSE c1
DEALLOCATE C1
Please let me know where I am doing wrong.
Thanks,
Rau
View 1 Replies
View Related
Sep 3, 2004
hi everybody
How can we execute a string of sql statements in Oracle ,similar to sp_executesql in sql server.
ie a string can contain insert into a table statement,delete a row from a table statement, update etc.
Thanks all of You
View 2 Replies
View Related
Jan 7, 2004
Is there anything that will allow you to execute a line of sql code if it is longer than 4000 Unicode characters? The line of code is stored in a NVARCHAR Variable.
I'm using sp_ExecuteSQL and have hit the 4000 character wall
View 1 Replies
View Related
Apr 10, 2008
I have a string which I want to pass into an IF statement. Here's what I have:
declare @sumclause varchar(4000)
set @sumclause = '(select count(*) from Participants where Weeks_Left<=8 or CDE_ACTV=''24'''
IF cast(@sumclause as int)>0
BEGIN
...
END
I'm first putting it in a string because I'm passing another string into it which can't be seen above.
As you can see, I tried casting it since it had a problem with computing a string as an int, but it still doesn't work. Help?
View 9 Replies
View Related
