db datatypes: procedure

Showing posts with label procedure. Show all posts

Friday, March 30, 2012

problem with Log

Hello,

Could anybody let me know why is this happening while executing a stored procedure through asp.net

Log Entry string is too long. A string written to the event log cannot exceed 32766 characters.

general

The message sounds like you tried to write a very large string to you system event log.
Possibly as a result of throwing an exception when trying to call the stored proc.

Do you have an Exception logging mechanism in place that is configured to put exception messages into your system Event Log?

Can you step into the code to see any possible exception when you make the stored proc call, or turn off exception handling/logging for this call and let the exception go to the page.

sql

Problem with locking in Selects

I have a Stored Procedure that performs a simple SELECT. The Select
have no locking hints or other hints and the database is set up in a
standard configuration.

The problem is that the SELECT runs for some time and while it is
running I can see (in the profiler) that other SPs with simple SELECTs
are held waiting until "my" SP has finished. The other SPs may be
other instances of the same SP as the one I'm running. All SPs
contains simple SELECTs and should only hold shared locks.

I have also checked if there are any locks holding the other SPs back
- there isn't any.

So my question is: What resouce can hold out other simple SELECTs in
this situation? Where should I look to identify the resource?

Regards

Bjrn(bjornsuneandersen@.gmail.com) writes:

Quote:

Originally Posted by

I have a Stored Procedure that performs a simple SELECT. The Select
have no locking hints or other hints and the database is set up in a
standard configuration.
>
The problem is that the SELECT runs for some time and while it is
running I can see (in the profiler) that other SPs with simple SELECTs
are held waiting until "my" SP has finished. The other SPs may be
other instances of the same SP as the one I'm running. All SPs
contains simple SELECTs and should only hold shared locks.
>
I have also checked if there are any locks holding the other SPs back
- there isn't any.
>
So my question is: What resouce can hold out other simple SELECTs in
this situation? Where should I look to identify the resource?

I have written a stored procedure aba_lockinfo which is useful for
this sort of things. You find it at
http://www.sommarskog.se/sqlutil/aba_lockinfo.html
What you should look for is the value WAIT in the lstatus column. That is
what the blocked processes are waiting for.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Wednesday, March 28, 2012

Problem with LIKE in stored procedure

I Can't seem to get LIKE to work in a stored procedure. For instance this
sql works in a query...
SELECT username, approle, billingcustomer, emailredundant, groupadmin,
branch, nakey, companyadmin, bcstring, dateapproved
FROM users
WHERE (branch LIKE '%%') AND (bcstring LIKE '%%') AND (dateapproved IS
NULL)
ORDER BY username
But this stored procedure returns no records when %% is supplied in the two
argumants...
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GetUnapprovedUsers]
(
@.branch nchar(4),
@.bcstring nvarchar(20)
)
AS
SET NOCOUNT ON;
SELECT username, approle, billingcustomer, emailredundant, groupadmin,
branch, nakey, companyadmin, bcstring, dateapproved
FROM users
WHERE (branch LIKE @.branch) AND (bcstring LIKE @.bcstring) AND
(dateapproved IS NULL)
ORDER BY username
Anyone know why?
Thanks,
Gary> @.branch nchar(4),
Note that nchar is fixed length so:
GetUnapprovedUsers
@.branch = N'%%'
@.bcstring = N'%%'
Is equivalent to:
GetUnapprovedUsers
@.branch = N'%% '
@.bcstring = N'%%'
You won't get any matches unless you have branches with spaces. I'm not
sure I understand why you specify 2 wildcard characters.
--
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
"GaryDean" <gdeanblakely@.newsgroup.nospam> wrote in message
news:%23lvBQm9gIHA.3352@.TK2MSFTNGP04.phx.gbl...
>I Can't seem to get LIKE to work in a stored procedure. For instance this
>sql works in a query...
> SELECT username, approle, billingcustomer, emailredundant, groupadmin,
> branch, nakey, companyadmin, bcstring, dateapproved
> FROM users
> WHERE (branch LIKE '%%') AND (bcstring LIKE '%%') AND (dateapproved IS
> NULL)
> ORDER BY username
> But this stored procedure returns no records when %% is supplied in the
> two argumants...
> set ANSI_NULLS ON
> set QUOTED_IDENTIFIER ON
> GO
> ALTER PROCEDURE [dbo].[GetUnapprovedUsers]
> (
> @.branch nchar(4),
> @.bcstring nvarchar(20)
> )
> AS
> SET NOCOUNT ON;
> SELECT username, approle, billingcustomer, emailredundant, groupadmin,
> branch, nakey, companyadmin, bcstring, dateapproved
> FROM users
> WHERE (branch LIKE @.branch) AND (bcstring LIKE @.bcstring) AND
> (dateapproved IS NULL)
> ORDER BY username
> Anyone know why?
> Thanks,
> Gary
>|||Dan,
you are not making sense to me. % means "any string of zero or more
characters". WHERE branch LIKE '%%' is equivilent to not having the WHERE
clause at all. % is not a "wild card character." My example stored
procedure and plain sql are totally equivilent - one works the other does
not.
Gary
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:DD64A043-B281-4ED9-8770-101A9EDC93C2@.microsoft.com...
>> @.branch nchar(4),
> Note that nchar is fixed length so:
> GetUnapprovedUsers
> @.branch = N'%%'
> @.bcstring = N'%%'
> Is equivalent to:
> GetUnapprovedUsers
> @.branch = N'%% '
> @.bcstring = N'%%'
> You won't get any matches unless you have branches with spaces. I'm not
> sure I understand why you specify 2 wildcard characters.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> http://weblogs.sqlteam.com/dang/
> "GaryDean" <gdeanblakely@.newsgroup.nospam> wrote in message
> news:%23lvBQm9gIHA.3352@.TK2MSFTNGP04.phx.gbl...
>>I Can't seem to get LIKE to work in a stored procedure. For instance this
>>sql works in a query...
>> SELECT username, approle, billingcustomer, emailredundant,
>> groupadmin, branch, nakey, companyadmin, bcstring, dateapproved
>> FROM users
>> WHERE (branch LIKE '%%') AND (bcstring LIKE '%%') AND (dateapproved
>> IS NULL)
>> ORDER BY username
>> But this stored procedure returns no records when %% is supplied in the
>> two argumants...
>> set ANSI_NULLS ON
>> set QUOTED_IDENTIFIER ON
>> GO
>> ALTER PROCEDURE [dbo].[GetUnapprovedUsers]
>> (
>> @.branch nchar(4),
>> @.bcstring nvarchar(20)
>> )
>> AS
>> SET NOCOUNT ON;
>> SELECT username, approle, billingcustomer, emailredundant,
>> groupadmin, branch, nakey, companyadmin, bcstring, dateapproved
>> FROM users
>> WHERE (branch LIKE @.branch) AND (bcstring LIKE @.bcstring) AND
>> (dateapproved IS NULL)
>> ORDER BY username
>> Anyone know why?
>> Thanks,
>> Gary
>|||> My example stored procedure and plain sql are totally equivilent - one
> works the other does not.
These are not equivalent and that is why you get different results. Let me
try to explain another way.
The select statement works because you are passing only wildcards (exactly 2
percent signs). This is almost the same as no WHERE clause except that NULL
values will be excluded.
The stored procedure is different because the @.branch parameter is declared
as fixed length of 4. When you pass 2 percent signs, the actual value used
in the LIKE expression is the 2 percent signs plus 2 blanks ('%% '). This
means that you will only find branches that end in 2 blanks. I think you
will get results you expect if you declare the parameter as nvarchar(4)
instead of nchar(4).
As I mentioned earlier, although 2 leading percent signs will work, the
second is superfluous.
--
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
"GaryDean" <gdeanblakely@.newsgroup.nospam> wrote in message
news:uXs7YfFhIHA.1184@.TK2MSFTNGP04.phx.gbl...
> Dan,
> you are not making sense to me. % means "any string of zero or more
> characters". WHERE branch LIKE '%%' is equivilent to not having the WHERE
> clause at all. % is not a "wild card character." My example stored
> procedure and plain sql are totally equivilent - one works the other does
> not.
> Gary
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:DD64A043-B281-4ED9-8770-101A9EDC93C2@.microsoft.com...
>> @.branch nchar(4),
>> Note that nchar is fixed length so:
>> GetUnapprovedUsers
>> @.branch = N'%%'
>> @.bcstring = N'%%'
>> Is equivalent to:
>> GetUnapprovedUsers
>> @.branch = N'%% '
>> @.bcstring = N'%%'
>> You won't get any matches unless you have branches with spaces. I'm not
>> sure I understand why you specify 2 wildcard characters.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> http://weblogs.sqlteam.com/dang/
>> "GaryDean" <gdeanblakely@.newsgroup.nospam> wrote in message
>> news:%23lvBQm9gIHA.3352@.TK2MSFTNGP04.phx.gbl...
>>I Can't seem to get LIKE to work in a stored procedure. For instance
>>this sql works in a query...
>> SELECT username, approle, billingcustomer, emailredundant,
>> groupadmin, branch, nakey, companyadmin, bcstring, dateapproved
>> FROM users
>> WHERE (branch LIKE '%%') AND (bcstring LIKE '%%') AND (dateapproved
>> IS NULL)
>> ORDER BY username
>> But this stored procedure returns no records when %% is supplied in the
>> two argumants...
>> set ANSI_NULLS ON
>> set QUOTED_IDENTIFIER ON
>> GO
>> ALTER PROCEDURE [dbo].[GetUnapprovedUsers]
>> (
>> @.branch nchar(4),
>> @.bcstring nvarchar(20)
>> )
>> AS
>> SET NOCOUNT ON;
>> SELECT username, approle, billingcustomer, emailredundant,
>> groupadmin, branch, nakey, companyadmin, bcstring, dateapproved
>> FROM users
>> WHERE (branch LIKE @.branch) AND (bcstring LIKE @.bcstring) AND
>> (dateapproved IS NULL)
>> ORDER BY username
>> Anyone know why?
>> Thanks,
>> Gary
>>
>|||Yes, that was the problem. Thanks for the help.
Gary
www.deanblakely.com
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:4D2F5517-B34F-4742-8763-AC46A34CE0FD@.microsoft.com...
>> My example stored procedure and plain sql are totally equivilent - one
>> works the other does not.
> These are not equivalent and that is why you get different results. Let
> me try to explain another way.
> The select statement works because you are passing only wildcards (exactly
> 2 percent signs). This is almost the same as no WHERE clause except that
> NULL values will be excluded.
> The stored procedure is different because the @.branch parameter is
> declared as fixed length of 4. When you pass 2 percent signs, the actual
> value used in the LIKE expression is the 2 percent signs plus 2 blanks
> ('%% '). This means that you will only find branches that end in 2
> blanks. I think you will get results you expect if you declare the
> parameter as nvarchar(4) instead of nchar(4).
> As I mentioned earlier, although 2 leading percent signs will work, the
> second is superfluous.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> http://weblogs.sqlteam.com/dang/
> "GaryDean" <gdeanblakely@.newsgroup.nospam> wrote in message
> news:uXs7YfFhIHA.1184@.TK2MSFTNGP04.phx.gbl...
>> Dan,
>> you are not making sense to me. % means "any string of zero or more
>> characters". WHERE branch LIKE '%%' is equivilent to not having the
>> WHERE clause at all. % is not a "wild card character." My example
>> stored procedure and plain sql are totally equivilent - one works the
>> other does not.
>> Gary
>> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
>> news:DD64A043-B281-4ED9-8770-101A9EDC93C2@.microsoft.com...
>> @.branch nchar(4),
>> Note that nchar is fixed length so:
>> GetUnapprovedUsers
>> @.branch = N'%%'
>> @.bcstring = N'%%'
>> Is equivalent to:
>> GetUnapprovedUsers
>> @.branch = N'%% '
>> @.bcstring = N'%%'
>> You won't get any matches unless you have branches with spaces. I'm not
>> sure I understand why you specify 2 wildcard characters.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> http://weblogs.sqlteam.com/dang/
>> "GaryDean" <gdeanblakely@.newsgroup.nospam> wrote in message
>> news:%23lvBQm9gIHA.3352@.TK2MSFTNGP04.phx.gbl...
>>I Can't seem to get LIKE to work in a stored procedure. For instance
>>this sql works in a query...
>> SELECT username, approle, billingcustomer, emailredundant,
>> groupadmin, branch, nakey, companyadmin, bcstring, dateapproved
>> FROM users
>> WHERE (branch LIKE '%%') AND (bcstring LIKE '%%') AND (dateapproved
>> IS NULL)
>> ORDER BY username
>> But this stored procedure returns no records when %% is supplied in the
>> two argumants...
>> set ANSI_NULLS ON
>> set QUOTED_IDENTIFIER ON
>> GO
>> ALTER PROCEDURE [dbo].[GetUnapprovedUsers]
>> (
>> @.branch nchar(4),
>> @.bcstring nvarchar(20)
>> )
>> AS
>> SET NOCOUNT ON;
>> SELECT username, approle, billingcustomer, emailredundant,
>> groupadmin, branch, nakey, companyadmin, bcstring, dateapproved
>> FROM users
>> WHERE (branch LIKE @.branch) AND (bcstring LIKE @.bcstring) AND
>> (dateapproved IS NULL)
>> ORDER BY username
>> Anyone know why?
>> Thanks,
>> Gary
>>
>>
>

Problem with LIKE %

I have a stored procedure:
CREATE PROCEDURE SearchHistoryClient
@.TextContain nvarchar(1000),
@.Email nvarchar(512),
@.Operator_URI nvarchar(512),
@.DateFrom DateTime,
@.DateTo DateTime,
@.Company_ID int
AS
DECLARE @.SQL nvarchar(4000)
SELECT @.SQL = '
SELECT
tblTemp.ChatTime,
tblTemp.Client_ID,
tblTemp.Client_SIPURI,
tblOperator.Operator_ID,
tblOperator.Operator_SIPURI,
tblOperator.Operator_Alias,
tblTemp.ChatTranscript
FROM
(SELECT * FROM tblSupportClient INNER JOIN tblClient ON
(tblSupportClient.ClientID = tblClient.Client_ID))
AS tblTemp INNER JOIN tblOperator ON tblTemp.Operator_ID =
tblOperator.Operator_ID
WHERE
(tblOperator.Company_ID = ''' + cast(@.Company_ID as nvarchar) + ''' ) AND
(tblTemp.ChatTime BETWEEN ''' + cast(@.DateFrom as nvarchar) + ''' AND
''' + cast(@.DateTo as nvarchar) + ''')
'
SELECT @.TextContain = '%'+ @.TextContain + '%';
-- Check if keyword is specified
IF (@.TextContain <> "")
BEGIN
SELECT @.SQL = @.SQL + ' AND (tblTemp.ChatTranscript LIKE ''' +
cast(@.TextContain as nvarchar) + ''') '
END
...
EXEC(@.SQL)
It always return 0 rows if @.TextContain contains more than a word.
e.g. if you call the procedure and the input string for @.TextContain is
"Hello"
then it returns some rows if they contains "Hello" string. But when you
specify "Hello John" it won't work correctly, though in your database the
string Hello John... exists.
Can anyone help me?Can you do a PRINT of @.SQL before executing, and see how the string is
formed? Btw, why are you using dynamic SQL?
--
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"John C#" <John C#@.discussions.microsoft.com> wrote in message
news:66468268-7264-41A7-AD86-F6162639EA6F@.microsoft.com...
> I have a stored procedure:
> CREATE PROCEDURE SearchHistoryClient
> @.TextContain nvarchar(1000),
> @.Email nvarchar(512),
> @.Operator_URI nvarchar(512),
> @.DateFrom DateTime,
> @.DateTo DateTime,
> @.Company_ID int
> AS
> DECLARE @.SQL nvarchar(4000)
> SELECT @.SQL = '
> SELECT
> tblTemp.ChatTime,
> tblTemp.Client_ID,
> tblTemp.Client_SIPURI,
> tblOperator.Operator_ID,
> tblOperator.Operator_SIPURI,
> tblOperator.Operator_Alias,
> tblTemp.ChatTranscript
> FROM
> (SELECT * FROM tblSupportClient INNER JOIN tblClient ON
> (tblSupportClient.ClientID = tblClient.Client_ID))
> AS tblTemp INNER JOIN tblOperator ON tblTemp.Operator_ID =
> tblOperator.Operator_ID
> WHERE
> (tblOperator.Company_ID = ''' + cast(@.Company_ID as nvarchar) + ''' ) AND
> (tblTemp.ChatTime BETWEEN ''' + cast(@.DateFrom as nvarchar) + ''' AND
> ''' + cast(@.DateTo as nvarchar) + ''')
> '
> SELECT @.TextContain = '%'+ @.TextContain + '%';
> -- Check if keyword is specified
> IF (@.TextContain <> "")
> BEGIN
> SELECT @.SQL = @.SQL + ' AND (tblTemp.ChatTranscript LIKE ''' +
> cast(@.TextContain as nvarchar) + ''') '
> END
> ...
> EXEC(@.SQL)
> --
> It always return 0 rows if @.TextContain contains more than a word.
> e.g. if you call the procedure and the input string for @.TextContain is
> "Hello"
> then it returns some rows if they contains "Hello" string. But when you
> specify "Hello John" it won't work correctly, though in your database the
> string Hello John... exists.
> Can anyone help me?|||Hi,
I tried to create a new table with a field of ntext type and then I use SQL
command to select, it works. However, it still does not work with fields
marked <long text>. Is it about type error? What is the problem here?
"Narayana Vyas Kondreddi" wrote:

> Can you do a PRINT of @.SQL before executing, and see how the string is
> formed? Btw, why are you using dynamic SQL?
> --
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "John C#" <John C#@.discussions.microsoft.com> wrote in message
> news:66468268-7264-41A7-AD86-F6162639EA6F@.microsoft.com...
>
>

Friday, March 23, 2012

problem with IN (@intParameter) in a stored procedure

I want to pass a list of integer values as a parameter for a stored procedure where the criteria column is an integer

I understand what the problem is but I cannot seem to find a solution, I can't pass it as a varchar because the column is an int and I can't pass it as an in because of the commas in the list

SELECTAppId, ApplicationName, NextPage, AppData, GrpLogo, DeptLogin
FROMdbo.TblApplication
WHERE(AppIdIN(@.intParameter))
ORDER BYSortOrder, ApplicationName

any help please

This question has been answered a few times, with sample code in the solution. Please search in these forums. I am sure you will find it.|||Search for the Split function.|||

thanks for that I found304221 Terri Morton's bit did the trick

|||

I did as you suggested and found 304221 from Terri Morton's did just what I wanted

Many Thanks

Problem with IN

I want to create a store procedure like this

CREATE PROCEDURE test
@.values VARCHAR(8000)
AS

SELECT *
FROM MyTable
WHERE MyTable.ID IN(@.values)
GO

the parameter @.values = '''1'',''3'',''5'',''6'',''7'''

How can I do that??

Thanks :p

Franky
franky@.boucheros.comTry writing the SELECT sentence an a string and then EXECUTE this string.

Originally posted by Franky
I want to create a store procedure like this

CREATE PROCEDURE test
@.values VARCHAR(8000)
AS

SELECT *
FROM MyTable
WHERE MyTable.ID IN(@.values)
GO

the parameter @.values = '''1'',''3'',''5'',''6'',''7'''

How can I do that??

Thanks :p

Franky
franky@.boucheros.com|||CREATE PROCEDURE test (@.values VARCHAR(8000))
as

Declare @.Query nVarchar(1000)

SET @.Query=N'SELECT * FROM Table ' +
'WHERE Table.ID IN( ' + @.values + ')'

EXECUTE sp_executesql @.Query, N'@.level tinyint', @.level = 35

====================================

Where @.values must be

@.values = '''1''' + ','+ '''3'''+ ',' + '''5''' + ','+'''6'''+','+'''7'''|||Lots of ways to skin this cat. A non-dynamic solution:

SELECT *
FROM MyTable
WHERE @.values like '''%' + cast(MyTable.ID as varchar(4)) + '%'''

Another method would be to create a user-defined funtion that returns a table of values from your string.

blindman|||Originally posted by blindman
Lots of ways to skin this cat. A non-dynamic solution:

SELECT *
FROM MyTable
WHERE @.values like '''%' + cast(MyTable.ID as varchar(4)) + '%'''

Another method would be to create a user-defined funtion that returns a table of values from your string.

blindman

Or this way

SELECT *
FROM MyTable
WHERE CHARINDEX(@.values,cast(MyTable.ID as varchar))>0|||Snail, I think you will need to put quotes around your value so that a value such as 1 doesn't match up with a string like ("8", "9", "10", "11").

blindman|||Originally posted by blindman
Snail, I think you will need to put quotes around your value so that a value such as 1 doesn't match up with a string like ("8", "9", "10", "11").

blindman

blindman - it was my fault but another one - it needs to change order of arguments in charindex function. Nothing is wrong with quotes. Check this one:

create table #test(id int identity,code varchar(10))
insert #test(code) values('a')
insert #test(code) values('b')
insert #test(code) values('c')

declare @.list varchar(80)
set @.list='''1'',''3'',''5'',''6'',''7'''
select * from #test
where CHARINDEX(cast(id as varchar),@.list)>0

Problem with IF

It is a procedure that does the paging on the friends table, the variable @.PageWay is the ordering that the return table have to appear, ASC or DESC.

Why it's accusing error on if clause? "if(@.PageWay= 1)"

createProcedure teste2

@.user_idint,

@.PageIndexint,

@.PageSizeint,

@.PageOrdervarchar(10),

@.PageWaybit

Begin

Declare @.FirstRowint,

@.LastRowint,

@.Recordsint,

@.Paginasfloat,

@.Pagesint

Select @.FirstRow=( @.PageIndex- 1)* @.PageSize+ 1,

@.LastRow= @.PageSize+(@.PageIndex- 1)* @.PageSize;

With invitationas

(

Select*,

Row_Number()over(orderby friend_idasc)as RowNumber

from friendswhere [user_id]=(@.user_id)and invited=(1)

)

if(@.PageWay= 1)

Begin

Select*from invitationwhere RowNumberbetween @.FirstRowand @.LastRoworderby

casewhen @.PageOrder='creation'then creationendasc,

casewhen @.PageOrder='e_mail'then e_mailendasc

End

else

Begin

Select*from invitationwhere RowNumberbetween @.FirstRowand @.LastRoworderby

casewhen @.PageOrder='creation'then creationenddesc,

casewhen @.PageOrder='e_mail'then e_mailenddesc

End

Set @.Records=(SelectCount(*)as'amigos'From friendswhere [user_id]=(@.user_id)and invited=(1))

Set @.Paginas=(Convert(Float,@.Records)/Convert(Float,@.PageSize))

Set @.Pages=Ceiling(@.Paginas)

return @.Pages

End

Thank you very much.

I think as you must put a semicolon before you start WITH statement, you must put something after. Hmm

|||

I discovered the error. After you define CTE you must use it in the next statement, otherwise you will got a message error. So I just put the following query before the If statement "Select Count(*) from invitation", and worked just fine.

There is the message when you put a Select query that not use the CTE,

Msg 422, Level 16, State 4, Procedure teste2, Line 30

Common table expression defined but not used.

Thank you very much, for had seemed my post.

Wednesday, March 21, 2012

Problem with handling dates

hi all
I have a stored procedure that gets date from a particular field using the DATEPART function. However, somewhere in between it has to do an update which is why it adds hours, days or months to the function. Here is the code
SELECT @.var_NewNextRunDate=(CAST(DATEPART(YYYY,@.DT) AS CHAR(4)) + '/'
+ RIGHT(CAST(100+DATEPART(MM,@.DT) AS CHAR(3)),2) + '/'
+ RIGHT(CAST(100+DATEPART(DD,@.DT) AS CHAR(3)),2) + ' '
+ RIGHT(CAST(100+DATEPART(HH,@.DT) + @.var_Frequency AS CHAR(3)),2) + ':'
+ RIGHT(CAST(DATEPART(MI,@.DT) AS CHAR(2)),2) + ':'
+ RIGHT(CAST(DATEPART(SS,@.DT) AS CHAR(2)),2)+ ':'
+ RIGHT(CAST(DATEPART(MS,@.DT) AS CHAR(3)),3) )
Now the problem is that when there a change in day the hours get stuck because it simply adds the hours so if it is 23:40 and it adds 3 it becomes 26:40 which makes no sense. Can someone please help me find a solution to this? I have the same situation with days, and months as well.
Thank you.you should do all data/time manipulations on a date/time datatypes not chars.
if you are extracting a portion of an existing date/time which results in a char datatype, you should cast it back to date/time before you start manipulating it.|||Although this is an old thread I just thought I'd add what I did. I just used the dateadd function to resolve the issue. It worked just perfect.

Tuesday, March 20, 2012

Problem with GetUTCDate function in stored procedure

Why does this not work? I get the following error when trying to save the stored procedure. "Incorrect syntax near ')'."

SQL2005 SP1 developing with VS 2005 SP1

UPDATE mytable
SET modifyDate = getutcdate()
WHERE rowID = 1

I have also tried several variations all of which don't work.

DECLARE @.modifyDate datetime
Set @.modifyDate = getutcdate()

SELECT @.modifyDate getutcdate()

However if I use the getdate() instead any of the above work. ?

Larry

For some unknown reason the getutcdate() must be enclosed in () or the procedure ignores the WHERE clause portion of the procedure.

UPDATE mytable
SET modifyDate = (getutcdate())
WHERE rowID = 1

In my example in the first post the getutcdate() is not enclosed in () and the WHERE clause is not part of the query at least that is what VS 2005 determines.

As for the syntax error, that happened to be unrelated altogether except as a coding error. This error started popping up during a code review/update because of the law forcing businesses to save all email and IM correspondence. We determined that henceforth we must standardize our timebase and the logical time is UTC. So all our stored procedures that use a "modifyDate" field are being updated to now use UTC times. The syntax error appeared in just a few procedures that have "Administrative" functionallity that can modify the modifyDate from external code. We are now removing that capability from our system. It just so happens that in a few isolated cases the declaration of the modifyDate appeared last in the parameters section of the procedure. Thus when the line was commented out there was a trailing "," on the previous line that caused the syntax error.

Nevertheless VS2005 improperly detects the endpoint of the procedure when the getutcdate() is not enclosed in () itself.

Larry

|||UPDATE myTable
SET modifyDate = getutcdate()
WHERE id = 1

This one looks fine and works on my machine.
And this sp works too.
CREATE PROCEDURE [dbo].[tDate_sp]
AS
UPDATE myTable
SET modifyDate = getutcdate()
WHERE id = 1|||

I think that the issues may be with Visual Studio 2005 SP1.

If I create the stored procedure as shown above, what actually happens is the editor imporperly places the blue border that deliniates the SQL statement is such a way that the WHERE clause in this case is excluded. However SQL 2005 does accept the entire statement.

So in appearance the border outline in Visural Studio would have the following enclosed inside the statement:

UPDATE myTable
SET modifyDate = getutcdate()

This would NOT be inside the statement:

WHERE id = 1

The net effect is that it does work it is just imporperly indicated in the editor.

Larry

Problem with GETDATE in SQL Stored Procedure

hi all,

i am using a stored procedure where i am using GETDATE to give default value to a field ( @.effectivedate as Datetime = GETDATE)

i am making the SP call in my code .

Dim cmd As System.Data.Common.DbCommand = db.GetStoredProcCommand("sel_TemplateData")

db.AddInParameter(cmd, "@.TemplateID", DbType.Int32, Convert.ToInt32(_templateId))

db.AddInParameter(cmd, "@.State", DbType.String, mrmParams("State").ToString())

db.AddInParameter(cmd, "@.SectionCode", DbType.String, mrmParams("SectionCode").ToString())

Dim ds As DataSet = db.ExecuteDataSet(cmd)

should i need to pass this as a parameter along with other parameter as below ? will it be defaultly taken.

when i try to add this parameter an error is thrown " cannot convert string to datetime .

is the syntax for GETDATE correct.

thanks in Advance

Since Getdate() is the non-deterministic function you can’t assign this function as your default value of the SP parameter.

Try to use the following approach to pick up the current date when there is no explicit value passed for datetime valued parameters.

Code Snippet

raete Procedure TestDateParam

(

@.Date as datetime= '1900-01-01'

)

Begin

Set @.Date = Case When @.Date = '1900-01-01' Then Getdate() Else @.Date End;

Select @.Date Date

End

Exec TestDateParam --it will use the current date

Exec TestDateParam '1/1/2007' --it will use the passed date value

|||

Could you instead use NULL as your default value? If that is possible then you would not need the set statement but could use the ISNULL or COALESCE function -- something like:

ISNULL(@.Date, getdate())

COALESCE(@.Date, getdate())

within the body of your stored procedure

|||

If you are wanting to make @.EffectiveDate an optional parameter for the procedure, I suggest using Kent's suggestion of setting the optional value equal to NULL. It is cleaner than presuming a redefined date means none supplied...

|||In addition to Kent and Arnie you could consider using the syntax:

Create procedure someproc
(
@.SomeDate DATETIME = NULL
)
AS

SELECT
(...Something)
WHERE YourColumn = @.SomeDate OR @.SomeDate IS NULL

But this highly depends on your needs in the logic of the stored procedure.

Jens K. Suessmeyer.

http://www.sqlserver2005.de

Problem with formatting with CR and LF in a string

Hi,

I'm trying to use carriage return (CR) and line feed (LF) to format a string for use with the msdb.dbo.sp_send_dbmail stored procedure. My goal is to have several lines of text delineated with CR LF. However, it appears that SQL Server 2005 is replacing the CR LF with 2 spaces. The snipped below demonstrates this.

declare @.msg nVarChar(100)
set @.msg = 'this is before the cr ' + char(10) + char(11) + char(12) + char(13) + char(14) + char(10) + 'and this is after the cr'
select @.msg

If I copy the text returned by the select into a hex editor what I see for the portion of the string "cr ' + char(10) + char(11) + char(12) + char(13) + char(14) + char(10) + 'a" is:

63 72 20 20 0B 0C 20 0E 0F 61

I was expecting:

63 72 20 0A 0B 0C 0D 0E 20 61

This is what leads me to believe the CR and LF are being replaced with spaces as it shows hex 20 (SPACE) instead of hex 0A (LF) and hex 0D (CR).

Can someone explain to me how to make this do what I want it to?

Thanks
John

Given this simple test:

CREATE TABLE dbo.CRLF(id int,crlf nvarchar(100))

DECLARE @.msg nVarChar(100)

SET @.msg = 'this is before the cr ' + char(10)+ char(13) +'and this is after the cr'

INSERT INTO dbo.CRLF(id,crlf)VALUES(1, @.msg )

SELECT * FROM dbo.CRLF

I get this if I copy the results from Query Editor:

idcrlf

1this is before the crand this is after the cr

And this ouput put if I Open Table in Object Explorer:

1this is before the cr

and this is after the cr

|||I'm unable to duplicate your results. I get a single line when opening the table in the Object Explorer.

If you add a DECLARE @.LongMsg and set @.LongMsg = @.Msg + @.Msg + @.Msg and then use @.LongMsg as the @.body parameter of the msdb.dbo.sp_send_dbmail your email will be a single long line with no CR LF showing in the text. This is basically what I'm trying to do.

Perhaps there is a server setting somewhere that needs to be tweaked on our server.

John|||

Ya it true but you see 2 box kind of symbol when you opened

the table trough Object explore, select the option “Result to Text” in the

Query panel and select the row then output shows like this.

(1 row(s)

affected)

idcrlf

1This is 1 msg

and this is

2 msg

this is 3

msg

(1 row(s)

affected)

|||Ok, after changing the option I can see the CR in the results. Thanks for showing me something I didn't know.

However, I'm still having problems with the CR in the email text. Here's another snippit from the stored procedure:

Declare @.Message varchar(200)
Declare @.MessageList varChar(max)

SET @.MessageList = 'Daily Summary Report' + @.CR
SET @.MessageList = @.MessageList+'New Items'+ @.CR
SET @.MessageList = @.MessageList+'--'+ @.CR
DECLARE c2 CURSOR FOR
select MSG from Email.Messages
where Event_ID = @.EventID
OPEN c2
FETCH NEXT FROM c2
INTO @.Message
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.MessageList = @.MessageList + @.Message + @.CR
FETCH NEXT FROM c2
INTO @.Message
END
CLOSE c2
DEALLOCATE c2

This builds the body text of the email in the variable @.MessageList. It always puts a CR at the end of the line for the SET @.MessageList lines that are outside of the loop.

Inside the loop, if the message type has messages like "Added UPC 123456123456" it works fine with a CR at the end of every line.

If the message type has messages like "FAILED Client1 20050122 Source TYPE 2: 12 COLUMNS SEMI COLON DELIMITED production" it does not put a CR at the end of each line. If I put CR twice at the end of the line I do get two CR's in the email.

I'm stumped. At this point I have put in an if statement to put 2 CR's at the end of the event types that are not gettting the single CR and that seems to work, but I'm one of those types that like to understand why things work or don't work.

Any more thoughts? And thanks for your help.

John|||

Hmmm, I tried deleting a post I just made in this thread, because it seemed no longer relevant after John had made a simultaneous post, and it appears to have deleted John's new post, too.

I think that is a bug in the FORUM software, since I chose only my post when I selected "Delete".

Sorry!

Dan

Monday, March 12, 2012

Problem with FETCH LAST

Hello everyone, hope someone can help me with this.

I have a SQL stored procedure that inserts a record into a table,
creates a cursor to fetch the last record that was added to get the
unique key that was created and then writes that and other info to a
separate table. This procedure was working fine at our ISP under NT 4
and SQL 7.

We recently moved to another ISP on servers that are windows 2000 and
SQL 2000. Now this code is going kerplooey. It actually worked fine
in the staging area but now that it was moved into production, it is
not working. also wanted to mention that the production database was
restored from a backup. below is the code.

the first time this is run it is ok, for example the transaction
number is 1. the next time it is run, a new record is created in the
sweep results with a transaction number of 2. but for some reason,
when i declare the cursor to fetch the last record, it goes back to
the transaction number 1 record. so the counts from transaction 1
don't match counts from transaction 2 and the next step has an error
condition and doesn't work.

thanks in advance for any help you can provide

Ann Williams

-- update the sweep results table
INSERT tbl_sweepresults (del_wrkfeedback_count,
updnull_feedback_count, swp_feedback_count,
swp_count_error, del_error, updnull_error, swp_error, init_error,
sweep_date)
VALUES (@.var_del_wrkfeedback_count, @.var_updnull_feedback_count,
@.var_swp_feedback_count,
@.var_swp_count_error, @.var_del_error, @.var_updnull_error,
@.var_swp_error, @.var_init_error, GETDATE())
-- create cursor
DECLARE tbl_sweepresults_cursor SCROLL CURSOR FOR
SELECT transaction_no, sweep_date, init_error, updnull_feedback_count
FROM tbl_sweepresults
OPEN tbl_sweepresults_cursor
-- get transaction number, sweep date, init error, feedback sweep
count and pass to tbl_currentTrans for OPAL comparison
FETCH LAST FROM tbl_sweepresults_cursor INTO @.var_transaction_no,
@.var_sweep_date, @.var_init_error, @.var_swp_countzero
DELETE tbl_currentTrans
INSERT tbl_currentTrans (current_transaction_no, current_sweep_date,
current_init_error, current_swp_countzero)
VALUES (@.var_transaction_no, @.var_sweep_date, @.var_init_error,
@.var_swp_countzero)
-- close the cursor
CLOSE tbl_sweepresults_cursor
DEALLOCATE tbl_sweepresults_cursorIs the value you are trying to retrieve an IDENTITY column? If so,
SCOPE_IDENTITY() is what you need. It returns the last inserted identity
value.

Since your cursor declaration doesn't include an ORDER BY clause you've been
lucky that it ever gave a meaningful result. FETCH LAST will just return an
indeterminate row from the table. Moving to another system (perhaps one with
more read-ahead cacheing) has shown up this defficiency which relied on the
engine always returning the last-inserted row.

Q. Why insert the row you've just added into another table
(tbl_currentTrans)? After all you already have it in a table and you know
the primary key.

--
David Portas
----
Please reply only to the newsgroup
--

Problem with Extended Stored Procedure calling an WebService...

Hi all!
I have a problem with a Extended Stored Procedure that calls a
WebService...I'm using SOAP to call the WebService and I'm using an
TokenManager that I have written myself...I have WSE 2.0 installed...
It works fine when I'm calling it from my PC, but when I call it from the
SQL test server which is a Windows 2003 server with SQL Server 2000
installed.
The message we get when run the Extended Stored Procedure is.
ODBC: Msg 0, Level 16, State 1
Cannot load the DLL XpMQSQL.DLL, or one of the DLLs it references. Reason:
126(The specified module could not be found.).
Does anyone know what I'm missing? We tried to install WSE 2.0 runtime
version but that didn't help...
Regards,
Tommy Selggren
Mandator Sverige AB
http://www.mandator.comUse DEPENDS.EXE to figure out which file(s) is/are missing. Download
DEPENS.EXE from http://www.dependencywalker.com/ and point it at you DLL
which hosts the XP
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2005 All rights reserved.
"Tommy Selggren" <tommy.selggren@.telia.com> wrote in message
news:e$pRK3kGFHA.2136@.TK2MSFTNGP14.phx.gbl...
> Hi all!
> I have a problem with a Extended Stored Procedure that calls a
> WebService...I'm using SOAP to call the WebService and I'm using an
> TokenManager that I have written myself...I have WSE 2.0 installed...
> It works fine when I'm calling it from my PC, but when I call it from the
> SQL test server which is a Windows 2003 server with SQL Server 2000
> installed.
> The message we get when run the Extended Stored Procedure is.
> ODBC: Msg 0, Level 16, State 1
> Cannot load the DLL XpMQSQL.DLL, or one of the DLLs it references. Reason:
> 126(The specified module could not be found.).
> Does anyone know what I'm missing? We tried to install WSE 2.0 runtime
> version but that didn't help...
> Regards,
> Tommy Selggren
> Mandator Sverige AB
> http://www.mandator.com
>
>|||Many thanks!
"Gert E.R. Drapers" <GertD@.SQLDevNet> wrote in message
news:eT2QgflGFHA.3092@.tk2msftngp13.phx.gbl...
> Use DEPENDS.EXE to figure out which file(s) is/are missing. Download
> DEPENS.EXE from http://www.dependencywalker.com/ and point it at you DLL
> which hosts the XP
> GertD@.SQLDev.Net
> Please reply only to the newsgroups.
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> You assume all risk for your use.
> Copyright SQLDev.Net 1991-2005 All rights reserved.
> "Tommy Selggren" <tommy.selggren@.telia.com> wrote in message
> news:e$pRK3kGFHA.2136@.TK2MSFTNGP14.phx.gbl...
the
Reason:
>

Problem with extended procedure

Hi all,
i have MS SQL Server 2000 Standard with my own extended procedure. I run
extended procedurec, and this procedure make sepparate session and run stored
procedure (asynchronous). But the extended procedure failed with error:
Server user 'domain\xxx' is not a valid user in database 'db'.
Database 'db' was restore from backup from another server, but user's SID is
the same as login, there is no orphanes , or somethink like that. (to be on
the safe said)I drop the user and the login and create new login and user .
But no change.
User domain\xxx is the local admin, and MS SQL Server and ServerAgent start
with this account.
Any suggestion?
Thanks Honza
Run the query below to verify the login is a sysadmin role member. Also,
verify that you are connecting to the correct SQL Server instance from your
extended proc. A Profiler trace may help.
SELECT IS_SRVROLEMEMBER ('sysadmin' , 'domain\xxx')
Hope this helps.
Dan Guzman
SQL Server MVP
"Jan Schustr" <Jan Schustr@.discussions.microsoft.com> wrote in message
news:10A5175F-50C8-4C0E-B503-47CD5B08D4C5@.microsoft.com...
> Hi all,
> i have MS SQL Server 2000 Standard with my own extended procedure. I run
> extended procedurec, and this procedure make sepparate session and run
> stored
> procedure (asynchronous). But the extended procedure failed with error:
> Server user 'domain\xxx' is not a valid user in database 'db'.
> Database 'db' was restore from backup from another server, but user's SID
> is
> the same as login, there is no orphanes , or somethink like that. (to be
> on
> the safe said)I drop the user and the login and create new login and user
> .
> But no change.
> User domain\xxx is the local admin, and MS SQL Server and ServerAgent
> start
> with this account.
> Any suggestion?
> Thanks Honza

Problem with extended procedure

Hi all,
i have MS SQL Server 2000 Standard with my own extended procedure. I run
extended procedurec, and this procedure make sepparate session and run stored
procedure (asynchronous). But the extended procedure failed with error:
Server user 'domain\xxx' is not a valid user in database 'db'.
Database 'db' was restore from backup from another server, but user's SID is
the same as login, there is no orphanes , or somethink like that. (to be on
the safe said)I drop the user and the login and create new login and user .
But no change.
User domain\xxx is the local admin, and MS SQL Server and ServerAgent start
with this account.
Any suggestion?
Thanks HonzaRun the query below to verify the login is a sysadmin role member. Also,
verify that you are connecting to the correct SQL Server instance from your
extended proc. A Profiler trace may help.
SELECT IS_SRVROLEMEMBER ('sysadmin' , 'domain\xxx')
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Jan Schustr" <Jan Schustr@.discussions.microsoft.com> wrote in message
news:10A5175F-50C8-4C0E-B503-47CD5B08D4C5@.microsoft.com...
> Hi all,
> i have MS SQL Server 2000 Standard with my own extended procedure. I run
> extended procedurec, and this procedure make sepparate session and run
> stored
> procedure (asynchronous). But the extended procedure failed with error:
> Server user 'domain\xxx' is not a valid user in database 'db'.
> Database 'db' was restore from backup from another server, but user's SID
> is
> the same as login, there is no orphanes , or somethink like that. (to be
> on
> the safe said)I drop the user and the login and create new login and user
> .
> But no change.
> User domain\xxx is the local admin, and MS SQL Server and ServerAgent
> start
> with this account.
> Any suggestion?
> Thanks Honza

Problem with extended procedure

Hi all,
i have MS SQL Server 2000 Standard with my own extended procedure. I run
extended procedurec, and this procedure make sepparate session and run store
d
procedure (asynchronous). But the extended procedure failed with error:
Server user 'domain\xxx' is not a valid user in database 'db'.
Database 'db' was restore from backup from another server, but user's SID is
the same as login, there is no orphanes , or somethink like that. (to be on
the safe said)I drop the user and the login and create new login and user .
But no change.
User domain\xxx is the local admin, and MS SQL Server and ServerAgent start
with this account.
Any suggestion?
Thanks HonzaRun the query below to verify the login is a sysadmin role member. Also,
verify that you are connecting to the correct SQL Server instance from your
extended proc. A Profiler trace may help.
SELECT IS_SRVROLEMEMBER ('sysadmin' , 'domain\xxx')
Hope this helps.
Dan Guzman
SQL Server MVP
"Jan Schustr" <Jan Schustr@.discussions.microsoft.com> wrote in message
news:10A5175F-50C8-4C0E-B503-47CD5B08D4C5@.microsoft.com...
> Hi all,
> i have MS SQL Server 2000 Standard with my own extended procedure. I run
> extended procedurec, and this procedure make sepparate session and run
> stored
> procedure (asynchronous). But the extended procedure failed with error:
> Server user 'domain\xxx' is not a valid user in database 'db'.
> Database 'db' was restore from backup from another server, but user's SID
> is
> the same as login, there is no orphanes , or somethink like that. (to be
> on
> the safe said)I drop the user and the login and create new login and user
> .
> But no change.
> User domain\xxx is the local admin, and MS SQL Server and ServerAgent
> start
> with this account.
> Any suggestion?
> Thanks Honza

problem with executing sp_addrolemember...

When I login localy (computer with sql server) I can call procedure sp_addrolemember but when I am loged in remotely (from client computer)and try to call the same procedure I get this message: 'User does not have permission to performe this action'.

executing the procedure requires membership to db_owner or db_securityadmin database roles so that has nothing to do with workstation but with principals and thus logins...

verify the login you are connecting with from the remote client is mapped to a database user with enought permissions..

regards

Problem with ExecuteNonQuery

I have created a stored procedure that takes several parameters and ultimately does an INSERT on two tables. The sp returns with an integer indicating which is positive if one or more rows were added.

If I execute the SP by hand using the SQL Server Management Studio Express I get the proper results, the records are added to both tables and the return values are proper. One is an output parameter indicating the Identity value of the main record, the return value simply >0 if OK.

However, when I use C#, build my connection, command and its associated parameters making sure they match the SP then I get a malfunction.

The problem is that when I call ExecuteNonQuery the integer value it returns is -1 even though calling it from Mgmt. Studio gives a >0 result. Even though it returns -1 I can confirm that the records were added to BOTH tables and that the output parameter (The identity) given to me is also correct. However the return value is always -1.

I have no idea what is going wrong, Since I have SQL Express 2005 I do cannot do profiling :(. I really don't see why this goes wrong and I think using ExecuteScalar is not the best choice for this type of action.

ExecuteScalar is used for database calls that return one and only one value. This sounds like what you're doing. Why don't you think that ExecuteScalar is appropriate for this?

|||

The return Value from ExecuteNonQuery retuns number of rows effected. It is better to use ExecuteScalar but not must. For execute nonquery commnd, 0th [zero] command parameter is your return value. try to access that. If you dont get then create another parameter to ur sp @.newID OUTPUT, and set that in u r sp @.newID = @.@.IDENTITY. and access that in your CommandObject after executing ExecuteNonQuery();

|||

Are you checking the return value from the ExecuteNonQuery function, or the parameter with type ReturnValue?

result=cmd.ExecuteNonQuery()

cmd.Parameters.Add("@.RETURN_VALUE",sqldbtype.Int).ParameterDirection=ReturnValue

cmd.executeNonQuery()

result=cmd.Parameters("@.RETURN_VALUE").Value

Problem with Execute SQL Task

I am having problems creating an "Execute SQL Task" which calls a stored procedure.

I have tested the procedure successfully using parameters that I have hardcoded on the command line (i.e., EXEC procedure_name 1, 2). This works fine, but I'm having problems using variables(i.e., EXEC procedure_name @.VAR1, @.VAR2). I'm using a ConnectionType of OLE DB.

When I parse the Query I get an error message that says "The query failed to parse. Must declare the variable '@.VAR'".

However, I have this variable declared and assigned a value. I have played around the Parameter Mapping pane but I'm not getting anywhere.

Can anyone shed some light on this particular problem and how I go about fixing this?

thanks

John

John,

Place 'EXEC procedure_name ?, ?' as the SQL Statement. Then, under Parameter Mapping, add a variable select User::Var1 (or whatever the name of Var1 is) as the variable name and enter '0' (zero) as the Parameter Name. Add another variable, select User::Var2 as the variable name and enter '1' as the Parameter Name. That should do it.

Also, I've never been able to parse a query with parameters in it.

Eric

|||I could not reproduce your problem. However, I could use "Execute SQL task" to execute a stored procedure by passing a variable as the input parameter.

I had sp_GetDetails in my db that took one input (varchar type). I created a variable called "inputVal" and assigned a value to it. In my "Execute SQL task", I had "Execute sp_GetDetails @.inputValParam" as my SQLStatement. I also created a mapping between inputVal and inputValParam using "Parameter Mapping" in my task. This task works if the connection type is ADO.Net. If I change that to OLE DB, it does not work.|||Thank you so much. This has been very frustrating at best. I don't think some of these transform tasks are that intuitive at all...|||How would you pass a mix of variables and hardcoded values (i.e., @.var1, @.var2, null, null, 2, "test") without using the parameter mapping?|||

Simply use "EXEC procedure_name ?, ?, 1, NULL, 'Yes', ?, ?". The question marks serve as placeholders for the parameters which you are going to map on the 'Parameter Mapping' page. The parameter list is a 0-based array. So your parameter names will be 0, 1, 2, 3, ..., n respectively, and they're placed into the SQL in the order they're named, so the first '?' corresponds to parameter 0, the second '?' corresponds to parameter 1, etc.

So, if I have User::var1 with a value of 'A' mapped to 0, User::var2 = 'B' -> 1, User::var3 = 'Jim' -> 2, User::var4 = 'Bob' -> 3 the SQL sent via the SQL task would be "EXEC procedure_name 'A', 'B', 1, NULL, 'Yes', 'Jim', 'Bob'".

Hope that doesn't confuse things more.

Eric

|||

Kaarthik,

Kirk has a useful post which may help you here: http://sqljunkies.com/WebLog/knight_reign/archive/2005/10/05/17016.aspx

-Jamie

|||Thanks for the great info. I'll see if I can get this to work for me. Great help again ...

Friday, March 30, 2012

problem with Log

Problem with locking in Selects

Wednesday, March 28, 2012

Problem with LIKE in stored procedure

Problem with LIKE in stored procedure

Problem with LIKE %

Friday, March 23, 2012

problem with IN (@intParameter) in a stored procedure

Problem with IN

Problem with IF

Wednesday, March 21, 2012

Problem with handling dates

Tuesday, March 20, 2012

Problem with GetUTCDate function in stored procedure

Problem with GETDATE in SQL Stored Procedure

Problem with formatting with CR and LF in a string

Monday, March 12, 2012

Problem with FETCH LAST

Problem with Extended Stored Procedure calling an WebService...

Problem with extended procedure

Problem with extended procedure

Problem with extended procedure

problem with executing sp_addrolemember...

Problem with ExecuteNonQuery

Problem with Execute SQL Task

db datatypes

Blog Archive

About Me