Showing posts with label execute. Show all posts
Showing posts with label execute. Show all posts

Thursday, March 22, 2012

cursor + rounding?

Hi Guys,

I've created a cursor inside a function. When I break it down and execute the code piece by piece, no problem. However, try and parse it together and I get an error - 'Mixing old and new syntax is not allowed?' Something to do with the return statements? as I can alter the function to a procedure and it parses fine. Anyone come across this before?

Also, the values are being rounded when putting them into the cursor, even though I've declared the variables the cursor uses specifically as decimal? How can I get around this please?

Cheers,

Michelle

Michelle:

Can you describe what you are doing? This sounds rather vague.

|||

Blast, it didn't post. Umm, I've sorted the first problem, and I think I may have the answer to the second one. Will give it a go and let you know. Thanks for your help!

Cheers,

Michelle

|||

OK, both of those problems fixed. Should have been

DECLARE product_cursor CURSOR local SCROLL

NOT

DECLARE product_cursor SCROLL CURSOR

and Should have been:

decimal(8,2)

NOT

decimal.

Stupid mistakes! However, now I'm able to parse it fine, but am getting:

Msg 443, Level 16, State 15, Procedure CalculateFreight, Line 15

Invalid use of side-effecting or time-dependent operator in 'SELECT INTO' within a function.

Msg 443, Level 16, State 15, Procedure CalculateFreight, Line 25

Invalid use of side-effecting or time-dependent operator in 'UPDATE' within a function.

when I execute it!?

The SELECT INTO and UPDATE statements are fine when executed alone

Michelle

sql

Cursor @@rowcount returns 0 everytime

I everyone. I've got a very strange problem in my cursor. It's very strange because, if i execute my query without the cursor it returns one record, but if i use it in a cursor the @.@.rowcount returns 0.

Code Snippet

DECLARE CCursor CURSOR FAST_FORWARD

FOR

Select id_base, id_rc

From Terceiros_Tarifarios

Where codigo=@.terceiro

And data_fim>cast(cast(left(@.document_ini,4) as varchar)+'-'+Cast(right(@.documento_ini,2) as varchar)+'-01' as Datetime)

Open CCursor

set @.cnt_del = @.@.rowcount

if @.cnt_del=0

begin

print 'No records'

end

...

@.documento_ini (int) and @.terceiro (varchar(20)) are set before cursor declaration

Code Snippet

set @.documento_ini = 200607

set @.terceiro = '4170'

Please help my in this tip.

Thanks

@.@.Rowcount is not used for cursor row count. You have to use the @.@.FETCH_STATUS variable. The following logic can help you to achive your requirement..

Code Snippet

Declare CCursor Cursor FAST_FORWARD

For

Select * from Sysobjects;

Open CCursor

Declare @.flag as Bit

Set @.flag = 0 --Set the inital flag as OFF

Fetch next from CCursor Into .....

While @.@.FETCH_STATUS = 0

Begin

Set @.flag = 1 -- Reset the Flag

--do your regular action here..

Fetch next from CCursor Into .....

End

If @.flag = 0 --If no records on the Cursor the flag value is still ZERO

Begin

Print 'No records'

End

Close CCursor

Deallocate CCursor

|||

Ok, so i've changed @.@.rowcount for @.@.cursor_rows and the output is -1.

But how can i know how many rows are returned by cursor ?

I need to execute some code if cursor returns no records, and with @.@.FETCH_STATUS i can't confirm that.

edti: ok, i know u post a sample that uses @.@.FETCH_STATUS to know that, but i need to know it before a while @.@.fetch_status = 0

|||

@.@.CURSOR_ROWS is only used on the following STATIC & KEYSET cursors

Code Snippet

Declare @.cnt_del as int

Declare CCursor CURSOR STATIC –- or KEYSET

FOR

Select id_base, id_rc

From Terceiros_Tarifarios

Where codigo=@.terceiro

And data_fim>cast(cast(left(@.document_ini,4) as varchar)

+'-'+Cast(right(@.documento_ini,2) as varchar)+'-01' as Datetime)

Open CCursor

Set @.cnt_del = @.@.CURSOR_ROWS

If @.cnt_del=0

Begin

print 'No records'

End

Close CCursor

Deallocate CCursor

|||

Tiago Salgado,

Check function CURSOR_STATUS in BOL.

...

-- you are not explicitly declaring if your cursor is local or global, so

-- it will depend on your settings

open CCursor

if cursor_status('global', 'CCursor') = 0

print 'no records'

...

AMB

|||

cursor_status is not a valid function to verify for the record count, again if you use the dynamic cursor you always get the result as 1. (even there is no row).

Finally , if you use the dynamic cursor you can’t get the record count/status properly. (That’s what other providers like ADODB also says)

To achieve your desired output you have to use either STATIC or KEYSET cursor rather than DYNAMIC or FAST_FORWARD.

|||

Sorry, but for static and keyset cursors it is as valid as @.@.rowcount. My first sentence says "Check function cursor_status in BOL", so if he/she does, then will notice the "*" beside "cursor name" and "cursor variable" in the table describing "return types".

Anyway, thanks for your comment.

use northwind

go

declare c cursor local static

for

select *

from dbo.orders

where 1 = 2

open c

select cursor_status('local', 'c'), @.@.rowcount, @.@.cursor_rows

close c

deallocate c

go

AMB

|||Thanks to all your comments. I've solve my problem.

Monday, March 19, 2012

Currency Variables?

Starting in my control flow, I execute a data flow that populates a recordset via SQL 2005 Stored Proc. One of the columns in source table is a currency type.

Back to the control flow, I have a for each container that includes an execute sql task that updates or inserts records into another table. I get precision or data type issues since I can not assign the package variable to a currency data type. The only way I can get this to work is if I convert the currency column in my data flow to a string and then cast the variable in my update/insert sql task. Any suggestions?

Thanks....Gary

One suggestion would be to map the currency type in the recordset to a variable of type Object in the ForEach container's Variable mappings tab.

Then, when running the Execute SQL task in the ForEach container, use that Object variable in the parameter mapping tab with a DataType of CURRENCY.

No casting is needed.|||That works as described...Thanks!

Sunday, February 19, 2012

CTE Error: Incorrect syntax near the keyword 'with'. If this statement is a common table expre

I am having this error when using execute query for CTE

Help will be appriciated

Would be interesting to have the code you tried to execute, because this i ibviously a syntax error.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||? As Jens noted, you haven't showed us any code... But I'm betting you just need to use a semicolon before the "WITH": ;WITH myCTE AS ... -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <dba_sql@.discussions.microsoft.com> wrote in message news:35bbde4b-222b-4311-8087-09d80efaa94b@.discussions.microsoft.com... I am having this error when using execute query for CTE Help will be appriciated|||

I am getting this error when running the following code:

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE FUNCTION ClassificationsInTree(@.ClassificationTreeId int)

RETURNS @.ClassificationsInTree TABLE (ClassificationId int)

AS

BEGIN

WITH CLINTREE(ClassificationId) AS (

SELECT TopClassificationId FROM ClassificationTree

WHERE ClassificationTreeId = 81203717

UNION ALL

SELECT ClassificationId FROM Classification

INNER JOIN CLINTREE ON

CLINTREE.ClassificationId = Classification.ParentClassificationTree

WHERE Classification.ClassificationId <> CLINTREE.ClassificationId

)

--INSERT @.ClassificationsInTree

SELECT ClassificationId FROM CLINTREE

OPTION (MAXRECURSION 10);

RETURN

END

GO

The error messages:

Msg 156, Level 15, State 1, Procedure ClassificationsInTree, Line 7

Incorrect syntax near the keyword 'WITH'.

Msg 170, Level 15, State 1, Procedure ClassificationsInTree, Line 18

Line 18: Incorrect syntax near 'MAXRECURSION'.

Any thoughts? This is the exact syntax found in the help files, no?

|||? I was able to run that batch on my end with no errors once I uncommented the insert line... -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <JGilbertie@.discussions.microsoft.com> wrote in message news:0b38d9cc-f171-4ae6-8377-fc50393d8045@.discussions.microsoft.com... I am getting this error when running the following code: SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE FUNCTION ClassificationsInTree(@.ClassificationTreeId int) RETURNS @.ClassificationsInTree TABLE (ClassificationId int) AS BEGIN WITH CLINTREE(ClassificationId) AS ( SELECT TopClassificationId FROM ClassificationTree WHERE ClassificationTreeId = 81203717 UNION ALL SELECT ClassificationId FROM Classification INNER JOIN CLINTREE ON CLINTREE.ClassificationId = Classification.ParentClassificationTree WHERE Classification.ClassificationId <> CLINTREE.ClassificationId ) --INSERT @.ClassificationsInTree SELECT ClassificationId FROM CLINTREE OPTION (MAXRECURSION 10); RETURN END GO The error messages: Msg 156, Level 15, State 1, Procedure ClassificationsInTree, Line 7 Incorrect syntax near the keyword 'WITH'. Msg 170, Level 15, State 1, Procedure ClassificationsInTree, Line 18 Line 18: Incorrect syntax near 'MAXRECURSION'. Any thoughts? This is the exact syntax found in the help files, no?|||

Thanks for the reply.

I get those same two errors whether that line is commented or not...

Is there some kind of configuration I need to do to enable the WITH statement? shot in the dark, but I can't see any difference from examples I've found for using WITH.

|||? None that I know of. The only one I could think of was compatability level -- but I just tested with a database set to compatability level 80 (SQL Server 2000) and was still not able to replicate the error. Regardless, you should probably make sure yours is set correctly. Right-click on your database in SSMS, click Properties, then Options. Make sure Compatability Level is set to SQL Server 2005... Aside from that, though, I'm not sure what's going on. Can you use CTEs at all (outside of UDFs?) And did you try adding a semicolon before the WITH, as I suggested before? It appears to be unnecessary on my end, but it's always a good idea anyway... -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <JGilbertie@.discussions.microsoft.com> wrote in message news:1cbe0923-6981-44eb-b4cd-8bdee48ad315@.discussions.microsoft.com... Thanks for the reply. I get those same two errors whether that line is commented or not... Is there some kind of configuration I need to do to enable the WITH statement? shot in the dark, but I can't see any difference from examples I've found for using WITH.|||

So your suggestion to check the compatability level led me to the answer. The database server I was trying to run the query against is a SQL2000 server. We have many instances of SQL Server running for development purposes, and I didn't realize I was working against a SQL2000 instance. That database has the data I need in it, so I will have to move it to another machine.

A silly mistake, but I wouldn't have realized it, Thanks for your help!

|||I got the same error in Crystal Reports XI. I used Toad to write the SQL, then copied it into the command editor in Crystal. It worked fine, until I opened the same saved report on CRXI from a Citrix client. Still works fine using Toad, same datasource, credentials, etc. After reading this thread, I tried putting the semicolon in front of WITH and it worked. Strange to me, but it works,

csvde from Execute Process Task not returning groupType

When I run csvde from within an SSIS package's Execute Process Task, it does not return the groupType attribute, but when I run it directly from a command prompt, it does return that attribute. The csvde swiches are set the same in both cases:

-t 3268 -u -f allGroups.csv -d "DC=corp,DC=microsoft,DC=com" -r "(objectClass=group)" -l "groupType,mail,member"

The header line in the output file produced by the package is:

DN,member,mail,member;range=0-1499

while the header line in the file produced when csvde is run directly is:

DN,member,groupType,mail,member;range=0-1499

Has anyone else encountered this behavior?

Thanks,

Ron Rice

How do you have the Execute Process task setup?

Also, you may want to specify a full path to the -f flag.|||

Phil,

Unfortunately, making the -f switch a full path did not change the results.

I also tried changing the order of the attributes in the -l switch to "mail,groupType,member". At first I thought this had worked, because it did return the groupType attribute, but then I noticed that the "mail" attribute was not returned after making this change!

So for whatever reason, when I execute csvde from a package, the first attribute in the -l switch is not returned.

Thanks,

Ron

|||

Drop the double quotes around the -l flag parameters.

-l mail,groupType,member

|||

Phil,

Removing the double quotes from around the -l switch list of attributes did fix the problem. Thanks!

I wonder why csvde would behave differently when run in an Execute Process task versus being run directly from a command prompt. Also, I tried running it using T-SQL and xp_cmdshell, and that had the same problem. And I seem to remember running into issues concerning double quotes when I ran the bcp command from an Execute Process task, as well.

Whatever the ultimate cause of the problem, now that I know the work-around I am a happy camper!

Ron

|||

Rice31416 wrote:

Whatever the ultimate cause of the problem, now that I know the work-around I am a happy camper!

Ron

It's not really a work around. According to the csvde page, double quotes are not required. (See the examples at the bottom.)

http://technet2.microsoft.com/WindowsServer/en/Library/1050686f-3464-41af-b7e4-016ab0c4db261033.mspx

Tuesday, February 14, 2012

cscript execution problems after windows updates/ie7 install

after windows updates/ie7 install on windows 2003 SP1 machine with SQL2000
SP4 buil 8.0.2039 we can no longer execute cscript from sql example:
***********************
declare @.script varchar(8000)
set @.script = 'cscript.exe'
create table #output (
output varchar(8000)
)
insert into #output
exec master.dbo.xp_cmdshell @.script
select * from #output
drop table #output
***********************
returns NULL
Any ideas?
my error was slightly different and I tried all sorts of things from checking
the nessessary permissions for sql service accounts to proxy accounts etc. at
the end I ended up manually uninstalling one by one all microsoft patches I
installed that caused this and it fixed it!
check that the service account sql is running under has permission for the
following:
NTFS for C:\Program Files\Microsoft SQL Server\MSSQL
Read and write for registry keys at and under:
HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQLServer
HKEY_LOCAL_MACHINE\System\CurrentControlset\Servic es\MSSQLServer
HKEY_LOCAL_MACHINE\Software\Microsoft\Windows NT\CurrentVersion\Perflib
HKEY_LOCAL_MACHINE\Software\Clients\Mail
User Rights:
Act as Part of the Operating System = SeTcbPrivilege
Bypass Traverse Checking = SeChangeNotify
Lock Pages In Memory = SeLockMemory
Log on as a Batch Job = SeBatchLogonRight
Log on as a Service = SeServiceLogonRight
Replace a Process Level Token = SeAssignPrimaryTokenPrivilege
good luck
"Laurence Neville" wrote:

> George - did you figure the answer out? I am having a very similar problem -
> I used to run a VBS file that lives on a remote server with cscript through
> XP_CMDSHELL, and suddenly I am getting "Access is Denied". See the thread in
> this group:
> Access is Denied when accessing files on remote server thru XP_CMDSHELL (was
> working last week)
>
>
> "George" <George@.discussions.microsoft.com> wrote in message
> news:6893962E-3AB1-4154-9E15-E0562233283E@.microsoft.com...
>
>

cscript execution problems after windows updates/ie7 install

after windows updates/ie7 install on windows 2003 SP1 machine with SQL2000
SP4 buil 8.0.2039 we can no longer execute cscript from sql example:
***********************
declare @.script varchar(8000)
set @.script = 'cscript.exe'
create table #output (
output varchar(8000)
)
insert into #output
exec master.dbo.xp_cmdshell @.script
select * from #output
drop table #output
***********************
returns NULL
Any ideas?George - did you figure the answer out? I am having a very similar problem -
I used to run a VBS file that lives on a remote server with cscript through
XP_CMDSHELL, and suddenly I am getting "Access is Denied". See the thread in
this group:
Access is Denied when accessing files on remote server thru XP_CMDSHELL (was
working last week)
"George" <George@.discussions.microsoft.com> wrote in message
news:6893962E-3AB1-4154-9E15-E0562233283E@.microsoft.com...
> after windows updates/ie7 install on windows 2003 SP1 machine with SQL2000
> SP4 buil 8.0.2039 we can no longer execute cscript from sql example:
> ***********************
> declare @.script varchar(8000)
> set @.script = 'cscript.exe'
> create table #output (
> output varchar(8000)
> )
> insert into #output
> exec master.dbo.xp_cmdshell @.script
> select * from #output
> drop table #output
> ***********************
> returns NULL
> Any ideas?|||my error was slightly different and I tried all sorts of things from checking
the nessessary permissions for sql service accounts to proxy accounts etc. at
the end I ended up manually uninstalling one by one all microsoft patches I
installed that caused this and it fixed it!
check that the service account sql is running under has permission for the
following:
NTFS for C:\Program Files\Microsoft SQL Server\MSSQL
Read and write for registry keys at and under:
HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQLServer
HKEY_LOCAL_MACHINE\System\CurrentControlset\Services\MSSQLServer
HKEY_LOCAL_MACHINE\Software\Microsoft\Windows NT\CurrentVersion\Perflib
HKEY_LOCAL_MACHINE\Software\Clients\Mail
User Rights:
Act as Part of the Operating System = SeTcbPrivilege
Bypass Traverse Checking = SeChangeNotify
Lock Pages In Memory = SeLockMemory
Log on as a Batch Job = SeBatchLogonRight
Log on as a Service = SeServiceLogonRight
Replace a Process Level Token = SeAssignPrimaryTokenPrivilege
good luck
"Laurence Neville" wrote:
> George - did you figure the answer out? I am having a very similar problem -
> I used to run a VBS file that lives on a remote server with cscript through
> XP_CMDSHELL, and suddenly I am getting "Access is Denied". See the thread in
> this group:
> Access is Denied when accessing files on remote server thru XP_CMDSHELL (was
> working last week)
>
>
> "George" <George@.discussions.microsoft.com> wrote in message
> news:6893962E-3AB1-4154-9E15-E0562233283E@.microsoft.com...
> > after windows updates/ie7 install on windows 2003 SP1 machine with SQL2000
> > SP4 buil 8.0.2039 we can no longer execute cscript from sql example:
> > ***********************
> > declare @.script varchar(8000)
> > set @.script = 'cscript.exe'
> > create table #output (
> > output varchar(8000)
> > )
> >
> > insert into #output
> > exec master.dbo.xp_cmdshell @.script
> >
> > select * from #output
> >
> > drop table #output
> > ***********************
> > returns NULL
> >
> > Any ideas?
>
>

cscript execution problems after windows updates/ie7 install

after windows updates/ie7 install on windows 2003 SP1 machine with SQL2000
SP4 buil 8.0.2039 we can no longer execute cscript from sql example:
***********************
declare @.script varchar(8000)
set @.script = 'cscript.exe'
create table #output (
output varchar(8000)
)
insert into #output
exec master.dbo.xp_cmdshell @.script
select * from #output
drop table #output
***********************
returns NULL
Any ideas?George - did you figure the answer out? I am having a very similar problem -
I used to run a VBS file that lives on a remote server with cscript through
XP_CMDSHELL, and suddenly I am getting "Access is Denied". See the thread in
this group:
Access is Denied when accessing files on remote server thru XP_CMDSHELL (was
working last week)
"George" <George@.discussions.microsoft.com> wrote in message
news:6893962E-3AB1-4154-9E15-E0562233283E@.microsoft.com...
> after windows updates/ie7 install on windows 2003 SP1 machine with SQL2000
> SP4 buil 8.0.2039 we can no longer execute cscript from sql example:
> ***********************
> declare @.script varchar(8000)
> set @.script = 'cscript.exe'
> create table #output (
> output varchar(8000)
> )
> insert into #output
> exec master.dbo.xp_cmdshell @.script
> select * from #output
> drop table #output
> ***********************
> returns NULL
> Any ideas?|||my error was slightly different and I tried all sorts of things from checkin
g
the nessessary permissions for sql service accounts to proxy accounts etc. a
t
the end I ended up manually uninstalling one by one all microsoft patches I
installed that caused this and it fixed it!
check that the service account sql is running under has permission for the
following:
NTFS for C:\Program Files\Microsoft SQL Server\MSSQL
Read and write for registry keys at and under:
HKEY_LOCAL_MACHINE\Software\Microsoft\MS
SQLServer
HKEY_LOCAL_MACHINE\System\CurrentControl
set\Services\MSSQLServer
HKEY_LOCAL_MACHINE\Software\Microsoft\Wi
ndows NT\CurrentVersion\Perflib
HKEY_LOCAL_MACHINE\Software\Clients\Mail
User Rights:
Act as Part of the Operating System = SeTcbPrivilege
Bypass Traverse Checking = SeChangeNotify
Lock Pages In Memory = SeLockMemory
Log on as a Batch Job = SeBatchLogonRight
Log on as a Service = SeServiceLogonRight
Replace a Process Level Token = SeAssignPrimaryTokenPrivilege
good luck
"Laurence Neville" wrote:

> George - did you figure the answer out? I am having a very similar problem
-
> I used to run a VBS file that lives on a remote server with cscript throug
h
> XP_CMDSHELL, and suddenly I am getting "Access is Denied". See the thread
in
> this group:
> Access is Denied when accessing files on remote server thru XP_CMDSHELL (w
as
> working last week)
>
>
> "George" <George@.discussions.microsoft.com> wrote in message
> news:6893962E-3AB1-4154-9E15-E0562233283E@.microsoft.com...
>
>