sql overflow error: running

Showing posts with label running. Show all posts

Friday, March 30, 2012

Page break after X items

Is it possible to force a page break after a specified number of items in a table?
For instance, I have a name badge report but I'm running into a problem where some badges are sliced down the middle at page breaks when exporting to pdf or tiff. If I could force a page break after every 2nd or 3rd badge it would alleviate that problem.

Thanks in advance.Figured it out. I added a group on Code.GetCount(RowNumber(Nothing)) with a page break at the end and added this function:

public shared function GetCount(CurrentCount As Integer) As Integer
If CurrentCount Mod 2 = 0 Then
GetCount = CurrentCount-1
Else
GetCount = CurrentCount
End If
end function

|||

Hello Nblankton

that was a very good idea, I did try to use it but for some reason it only works with 2 records.

I tried to change it with Mod 4 (I thought that would break after 4 records but that is not the case, I page break after the 1,2 and 4th record).

I'm not sure if I'm missing something or it is a bug.

Any Idea?

Thank you

|||

You'd also need to also alter the CurrentCode-1 line to subtract the appropriate number so that for all four that returned number is the same.

Something like CurrentCode-(CurrentCode Mod 4)+1.

I worked out a different method later that I think is a little clearer, though. This groups by 3's:

public shared function GetCount(CurrentCount As Integer) As Integer
GetCount = (CurrentCount+1)/3
end function

|||

Hi Nblankton

thank you for the response, after I wrote the note I searched the forum and found this

"=Ceiling(RowNumber(Nothing)/4)" you can put any number (2,5,20) it works, without having to add any code.

Thank you for your help. adding a group to control the amount of record per page is a great idea.

|||hi, I hope someone can help me!
I don't understand where I must place "=Ceiling(RowNumber(Nothing)/4)"

I added a new group (I selected table, right-click Proprieties, groups and add)
and I've write "=Ceiling(RowNumber(Nothing)/4)" as expression, but when I tried to execute , an error rose!
A scope problem

did I wrong the place to write the expression?

Thanks in advance
sql

Page break after X items

Thanks in advance.Figured it out. I added a group on Code.GetCount(RowNumber(Nothing)) with a page break at the end and added this function:

public shared function GetCount(CurrentCount As Integer) As Integer
If CurrentCount Mod 2 = 0 Then
GetCount = CurrentCount-1
Else
GetCount = CurrentCount
End If
end function

|||

Hello Nblankton

that was a very good idea, I did try to use it but for some reason it only works with 2 records.

I tried to change it with Mod 4 (I thought that would break after 4 records but that is not the case, I page break after the 1,2 and 4th record).

I'm not sure if I'm missing something or it is a bug.

Any Idea?

Thank you

|||

You'd also need to also alter the CurrentCode-1 line to subtract the appropriate number so that for all four that returned number is the same.

Something like CurrentCode-(CurrentCode Mod 4)+1.

I worked out a different method later that I think is a little clearer, though. This groups by 3's:

public shared function GetCount(CurrentCount As Integer) As Integer
GetCount = (CurrentCount+1)/3
end function

|||

Hi Nblankton

thank you for the response, after I wrote the note I searched the forum and found this

"=Ceiling(RowNumber(Nothing)/4)" you can put any number (2,5,20) it works, without having to add any code.

Thank you for your help. adding a group to control the amount of record per page is a great idea.

|||hi, I hope someone can help me!
I don't understand where I must place "=Ceiling(RowNumber(Nothing)/4)"

did I wrong the place to write the expression?

Thanks in advance

Page break after X items

Thanks in advance.Figured it out. I added a group on Code.GetCount(RowNumber(Nothing)) with a page break at the end and added this function:

public shared function GetCount(CurrentCount As Integer) As Integer
If CurrentCount Mod 2 = 0 Then
GetCount = CurrentCount-1
Else
GetCount = CurrentCount
End If
end function

|||

Hello Nblankton

that was a very good idea, I did try to use it but for some reason it only works with 2 records.

I tried to change it with Mod 4 (I thought that would break after 4 records but that is not the case, I page break after the 1,2 and 4th record).

I'm not sure if I'm missing something or it is a bug.

Any Idea?

Thank you

|||

You'd also need to also alter the CurrentCode-1 line to subtract the appropriate number so that for all four that returned number is the same.

Something like CurrentCode-(CurrentCode Mod 4)+1.

I worked out a different method later that I think is a little clearer, though. This groups by 3's:

public shared function GetCount(CurrentCount As Integer) As Integer
GetCount = (CurrentCount+1)/3
end function

|||

Hi Nblankton

thank you for the response, after I wrote the note I searched the forum and found this

"=Ceiling(RowNumber(Nothing)/4)" you can put any number (2,5,20) it works, without having to add any code.

Thank you for your help. adding a group to control the amount of record per page is a great idea.

|||hi, I hope someone can help me!
I don't understand where I must place "=Ceiling(RowNumber(Nothing)/4)"

did I wrong the place to write the expression?

Thanks in advance

PAE & AWE on x64 Windows & SQL

We've just setup some new Microsoft SQL 2005 servers running windows 2003 R2
x64 with 16GB of RAM. Since we are running a 64 bit version of windows and a
64 bit version of SQL, is it any longer neccesary to add the /3GB /PAE
option in the boot.ini and enable AWE in SQL?
Thanks!
Brad"Brad Baker" <brad@.nospam.nospam> wrote in message
news:OF%23WZo3xGHA.2384@.TK2MSFTNGP05.phx.gbl...
> We've just setup some new Microsoft SQL 2005 servers running windows 2003
> R2 x64 with 16GB of RAM. Since we are running a 64 bit version of windows
> and a 64 bit version of SQL, is it any longer neccesary to add the /3GB
> /PAE option in the boot.ini and enable AWE in SQL?
>
Slava says no /3GB, no /PAE, no AWE option. Just grant the SQL account the
"Lock Pages in Memory" privilege.
Q and A: Using Lock Pages In memory on 64 bit platform
Q: Hello Slava, I would like to confirm my understanding that on SQL 2005
64 bit edition it is recommended to grant Lock Pages in Memory right to the
SQL account and then turn on the AWE setting. Thanks
A: Yes, we do recommend to turn on Lock pages in memory so that OS doesn't
page SQL Server out. However on 64 bit you only need to grant the right
"Lock Pages in Memory" to the SQL account for SQL Server to utilize this
feature. You do need to to change any of AWE settings through sp_configure.
http://blogs.msdn.com/slavao/archive/2005/08/31/458545.aspx
Slava explains:
http://blogs.msdn.com/slavao/archive/2005/04/29/413425.aspx
David

PAE & AWE on x64 Windows & SQL

We've just setup some new Microsoft SQL 2005 servers running windows 2003 R2
x64 with 16GB of RAM. Since we are running a 64 bit version of windows and a
64 bit version of SQL, is it any longer neccesary to add the /3GB /PAE
option in the boot.ini and enable AWE in SQL?
Thanks!
Brad"Brad Baker" <brad@.nospam.nospam> wrote in message
news:OF%23WZo3xGHA.2384@.TK2MSFTNGP05.phx.gbl...
> We've just setup some new Microsoft SQL 2005 servers running windows 2003
> R2 x64 with 16GB of RAM. Since we are running a 64 bit version of windows
> and a 64 bit version of SQL, is it any longer neccesary to add the /3GB
> /PAE option in the boot.ini and enable AWE in SQL?
>
Slava says no /3GB, no /PAE, no AWE option. Just grant the SQL account the
"Lock Pages in Memory" privilege.
Q and A: Using Lock Pages In memory on 64 bit platform
Q: Hello Slava, I would like to confirm my understanding that on SQL 2005
64 bit edition it is recommended to grant Lock Pages in Memory right to the
SQL account and then turn on the AWE setting. Thanks
A: Yes, we do recommend to turn on Lock pages in memory so that OS doesn't
page SQL Server out. However on 64 bit you only need to grant the right
"Lock Pages in Memory" to the SQL account for SQL Server to utilize this
feature. You do need to to change any of AWE settings through sp_configure.
http://blogs.msdn.com/slavao/archiv.../31/458545.aspx
Slava explains:
http://blogs.msdn.com/slavao/archiv.../29/413425.aspx
David

Friday, March 23, 2012

Package Time Outs

Okay, we have are running our Master Package (and therefore all related Child packages) through a .bat file. The .bat file is scripted using the following logic for an entire month of daily runs:

Code Snippet

DTExec /FILE E:\ETL\FinancialDataMart\Master.dtsx /DECRYPT masterpwd /SET \Package.Variables[ReportingDate].Value;"2/01/2007" > E:\ETL\ErrorLogs\Processing\etl_20070201log.txt
IF NOT %ERRORLEVEL%==0 GOTO ERROR%ERRORLEVEL%
mkdir E:\ETL\ErrorLogs\Archive\20070201
move E:\ETL\ErrorLogs\Processing\*.txt E:\ETL\ErrorLogs\Archive\20070201

DTExec /FILE E:\ETL\FinancialDataMart\Master.dtsx /DECRYPT masterpwd /SU /SET \Package.Variables[ReportingDate].Value;"2/02/2007" > E:\ETL\ErrorLogs\Processing\etl_20070202log.txt
IF NOT %ERRORLEVEL%==0 GOTO ERROR%ERRORLEVEL%
mkdir E:\ETL\ErrorLogs\Archive\20070202
move E:\ETL\ErrorLogs\Processing\*.txt E:\ETL\ErrorLogs\Archive\20070202

etc...

Generally it takes about 40-45 minutes to run one days worth of data. However, we have found unpredictable instances where the job will take 3 hours or even 6 hours and appear to hang....

The weirdness sets in when we kill the job and rerun it. In all instances of a rerun, the job will execute in the normal 40-45 minute time frame. Obviously, we would like to institute some sort of logging, monitoring and error handling....including if need be a method to timeout a process and restart it.

I am reviewing the WMI (Windows Management Instrumentation) Task but I'm not entirely convinced that it's the right tool for the job.

Questions:

Code Snippet

Thanks in advance!

Sounds like you're encountering some sort of deadlock. It's not clear to me whether or not you've enabled logging in all the packages; have you?

Have you installed SP2? We added some more logging in SP2 around calls to external databases.

You shouldn't need to kill the packages, but if you really, really want to go that way, you could use a Execute Process tasks (calling DTExec) instead of the Execute Package tasks, and set the TimeOut TerminateProcessAfterTimeout properties.

Package Runs but as soon as it is scheduled in SQL Server it hangs

I'm trying to get a cute FTP script running from a package that connects to a FTPS web site.

Regardless of what method I use to execute the script it runs sucessfully in Debug mode, if I import the package into Integration Services from SQL Server Management Studio it runs, however as soon as create a SQL Server job using the stored package it hangs.

I have tried an Active X, VB.Net and an Execute Process item and get the same result everytime.

I'm also getting the same problem now with a java script I'm running from an 'Execute Process' item. Runs fine until I create a job..

Has anyone experienced the same problem? I haven't a clue what is going on and the logs aren't giving me much information.

Thanks for your help

one thing to take into account is that the script will most likely run under a different security context when it is running as a job. When you debug the script it's running with your security context, when running as a job it will run under either a proxy account context or the context of the sql agent service.

This change in security context may be causing a dialog box to pop, or you may be experiencing some kind of hidden failure.

I'd suggest having a look at the script and evaluating what security context is needed for each call. After that insert some logging into the script and evaluate what statement is not returning.

Package running other packages

Hi everybody,

I have to create a package that executes other packages I've already created... Every one of these packages run with the same Configuration File, but if I try to execute the main one, including the path of the file, I get errors from the other packages because they can't find it... How can I manage to pass this file and its content to the other packages?

Here a little explanation of te process:

Main Package needs configuration file X.dtsConfig, and calls: package1, which needs configuration file X.dtsConfig; package2, .......

I hope everything is clear...

I have completely replaced all these packages using config files and calling other packages.

It it is supposed to work with SP1 though. I did not try sp1 yet.

In the meantime, I have consolidated all these "sub-packages" functionality into the main packages.

Philippe

|||

teone wrote:

Hi everybody,

I have to create a package that executes other packages I've already created... Every one of these packages run with the same Configuration File, but if I try to execute the main one, including the path of the file, I get errors from the other packages because they can't find it... How can I manage to pass this file and its content to the other packages?

Here a little explanation of te process:

Main Package needs configuration file X.dtsConfig, and calls: package1, which needs configuration file X.dtsConfig; package2, .......

I hope everything is clear...

You can pass values from the parent package through to child packages using parent package configurations.

-Jamie

|||but what I need is to pass the whole configuration file, not only some variable of the package.... how can I do that?|||

Why not just reference the same config file in each package?

This is made alot easier by the use of indirect configurations: http://blogs.conchango.com/jamiethomson/archive/2005/11/02/2342.aspx

-Jamie

|||

I am stupid, and I need further explanations....

I created a new environment variable, and I set its value with the path of my configuration file... is this the right thing to do?

After this I have enabled package configuration on the package, and set the source to that variable, but it doesn't work... Why?

Thanks

|||Restart the machine so the sys variable is recognized.|||

teone wrote:

I am stupid, and I need further explanations....

I created a new environment variable, and I set its value with the path of my configuration file... is this the right thing to do?

Yes.

teone wrote:

After this I have enabled package configuration on the package, and set the source to that variable, but it doesn't work... Why?

Thanks

Without being there its a bit hard to say. What "doesn't work"? What behaviour are you expecting? What happens instead?

-Jamie

|||The people I work for decided I don't need to do this package anymore.... so I quit thinking about it.
Thanks anyway!
|||

Bonus!!

I wish my superiors would say that to me sometime! :)

package path referenced an object that cannot be found

I am running Final Relase of 2005 version 9.00.1399. I built an Integration Services package saved it closed up, came in the next day opened the project and I get 46 Warnings and the message on all of them is similar:

"Warning loading Package.dtsx: The package path referenced an object that cannot be found: "\Package\Truncate Temp Table.Properties[Connection]". This occurs when an attempt is made to resolve a package path to an object that cannot be found."

The problem is that I had an Execute SQL Task that I named Truncate Temp Table for a while, but subsequently changed the name before I saved it at the end of the day. So the task called Truncate Temp Table doesn't exist anymore and it is still trying to find its properties. I have tried running Clean, Build, and Rebuild.

How do I get the package path to refresh with the current package design?

Thanks!

Hi,

If you got this solved I'd love to know how you did it! I am having the same problem!

Thanks,

PMR

|||

Hi..

Try regenerate the package id of your SSIS package, and save it...

ref : http://support.microsoft.com/?kbid=906564

|||

Hi KamiNoChikara,

I don't know if this will help or not, but version 9.00.1399 is not the latest version. SP1 addressed a host of SSIS issues, and is available at http://www.microsoft.com/downloads/details.aspx?FamilyID=cb6c71ea-d649-47ff-9176-e7cac58fd4bc&DisplayLang=en.

Hope this helps,
Andy

|||

KamiNoChikara wrote:
I am running Final Relase of 2005 version 9.00.1399. I built an Integration Services package saved it closed up, came in the next day opened the project and I get 46 Warnings and the message on all of them is similar:
"Warning loading Package.dtsx: The package path referenced an object that cannot be found: "\Package\Truncate Temp Table.Properties[Connection]". This occurs when an attempt is made to resolve a package path to an object that cannot be found."
The problem is that I had an Execute SQL Task that I named Truncate Temp Table for a while, but subsequently changed the name before I saved it at the end of the day. So the task called Truncate Temp Table doesn't exist anymore and it is still trying to find its properties. I have tried running Clean, Build, and Rebuild.
How do I get the package path to refresh with the current package design?
Thanks!

My guess is that you have something external to the package that is referencing this path. Are you using configurations by any chance?

-Jamie

package path referenced an object that cannot be found

How do I get the package path to refresh with the current package design?

Thanks!

Hi,

If you got this solved I'd love to know how you did it! I am having the same problem!

Thanks,

PMR

|||

Hi..

Try regenerate the package id of your SSIS package, and save it...

ref : http://support.microsoft.com/?kbid=906564

|||

Hi KamiNoChikara,

Hope this helps,
Andy

|||

KamiNoChikara wrote:
I am running Final Relase of 2005 version 9.00.1399. I built an Integration Services package saved it closed up, came in the next day opened the project and I get 46 Warnings and the message on all of them is similar:
"Warning loading Package.dtsx: The package path referenced an object that cannot be found: "\Package\Truncate Temp Table.Properties[Connection]". This occurs when an attempt is made to resolve a package path to an object that cannot be found."
The problem is that I had an Execute SQL Task that I named Truncate Temp Table for a while, but subsequently changed the name before I saved it at the end of the day. So the task called Truncate Temp Table doesn't exist anymore and it is still trying to find its properties. I have tried running Clean, Build, and Rebuild.
How do I get the package path to refresh with the current package design?
Thanks!

My guess is that you have something external to the package that is referencing this path. Are you using configurations by any chance?

-Jamie

Wednesday, March 21, 2012

Package fails when ran as a scheduled job

I have a large number of SSIS packages, which I have developed over the last few months.

Having written and tested them locally, running in VS05 etc - I have moved them to a server, stored in the MSDB database.

I am having real troubles with packages that move tables from one database to another.

I am working on a migration project, so I have several packages that move tables from the source database, into my staging database.

One package which will not run at all, basically just moves 15 tables from database A on my server to database B.

The package is essentially a few SQL tasks to create tables, then a data flow.

The data flow contains the table movements as an OLE DB source to an OLE DB destination. No intermediate processing.

In an attempt to get some meaningful logs of the reasons for failre I ran the package from the commandline with the output piped to a text file. That text file contained the following error:

Error: 2006-11-20 11:48:47.78
Code: 0xC0202009
Source: Data Flow Task Source - tblParking [1641]
Description: An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Protocol error in TDS stream".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Communication link failure".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Shared Memory Provider: No process is on the other end of the pipe.
".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Communication link failure".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Shared Memory Provider: No process is on the other end of the pipe.
".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Communication link failure".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Shared Memory Provider: No process is on the other end of the pipe.
".
End Error
Error: 2006-11-20 11:48:47.78
Code: 0xC0047038
Source: Data Flow Task DTS.Pipeline
Description: The PrimeOutput method on component "Source - tblParking" (1641) returned error code 0xC0202009. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.
End Error
Error: 2006-11-20 11:48:47.84
Code: 0xC0047021
Source: Data Flow Task DTS.Pipeline
Description: Thread "SourceThread1" has exited with error code 0xC0047038.
End Error

I've googled for most of the error messages there, and tried applying some of the things I found.

I've set the commit size on the OLE DB destination to 10k rows.

I've set the number of engine threads to 2.

The SQL Agent service is started by a domain user that has sufficient server and database roles to do what it needs - other packages run fine.

I'm really stumped here now - this package will run perfectly in visual studio, but fails when ran
as a scheduled task, and I'd really appreciate any advice or pointers

Matthew McNally wrote:
I have a large number of SSIS packages, which I have developed over the last few months.
Having written and tested them locally, running in VS05 etc - I have moved them to a server, stored in the MSDB database.
I am having real troubles with packages that move tables from one database to another.
I am working on a migration project, so I have several packages that move tables from the source database, into my staging database.
One package which will not run at all, basically just moves 15 tables from database A on my server to database B.
The package is essentially a few SQL tasks to create tables, then a data flow.
The data flow contains the table movements as an OLE DB source to an OLE DB destination. No intermediate processing.
In an attempt to get some meaningful logs of the reasons for failre I ran the package from the commandline with the output piped to a text file. That text file contained the following error:
Error: 2006-11-20 11:48:47.78
Code: 0xC0202009
Source: Data Flow Task Source - tblParking [1641]
Description: An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Protocol error in TDS stream".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Communication link failure".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Shared Memory Provider: No process is on the other end of the pipe.
".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Communication link failure".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Shared Memory Provider: No process is on the other end of the pipe.
".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Communication link failure".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Shared Memory Provider: No process is on the other end of the pipe.
".
End Error
Error: 2006-11-20 11:48:47.78
Code: 0xC0047038
Source: Data Flow Task DTS.Pipeline
Description: The PrimeOutput method on component "Source - tblParking" (1641) returned error code 0xC0202009. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.
End Error
Error: 2006-11-20 11:48:47.84
Code: 0xC0047021
Source: Data Flow Task DTS.Pipeline
Description: Thread "SourceThread1" has exited with error code 0xC0047038.
End Error
I've googled for most of the error messages there, and tried applying some of the things I found.
I've set the commit size on the OLE DB destination to 10k rows.
I've set the number of engine threads to 2.
The SQL Agent service is started by a domain user that has sufficient server and database roles to do what it needs - other packages run fine.
I'm really stumped here now - this package will run perfectly in visual studio, but fails when ran
as a scheduled task, and I'd really appreciate any advice or pointers

Every time I have received this error:

"Communication link failure".

It has been due to network connectivity isuues during the execution of my package; nothing to do with the package itself...not sure if that is always the case.

|||I dont think its a network problem Rafael

Firstly, as there should be no network traffic, as the source and target database are on the same server.

Secondly, the package will consistently fail as a scheduled task. I could run it now manually - and it would be fine. Schedule it to run 2 minutes later - and it will fail.

Actually - I only assume there is no network traffic. The server is called Llama, and my package has two data-sources, both of which are mapped to databases on Llama.

Surely this does not mean that these packages are sending data across the network to talk to the server they reside on?|||

Verify your boot.ini file on that server and make sure that there is no memory limit like using /3GB flag. That will reduce available memory to outside applications. We faced similar issues on "Communication link failure". After removing /3gb limit flag it was running fine.

Best of luck.

Veera Maganti

|||Thanks for your response Veera.

the boot.ini on this server does not have the /3gb switch

The whole boot.ini is:

[boot loader]
timeout=30
default=multi(0)disk(0)rdisk(0)partition(2)\WINDOWS
[operating systems]
multi(0)disk(0)rdisk(0)partition(2)\WINDOWS="Windows Server 2003, Standard" /fastdetect /NoExecute=OptOut
|||

Well there's your problem right there. Your server is called "Llama". if you upgrade to the "Cheetah" it will run just fine.

just joking... did you try entering a password into the SSIS to encryptsensativedatawithpassword? then schedule it and enter the password in there?

|||

Hi Matthew,

I have the same error, I want to know if you can solve this problem.

Thank you for your help.

Antonio