Error when schedule a job in SQL 2005

Hi,

I have a SSIS package that contains an Execute DTS 2000 process. This DTS 2000 is calling a legacy package, which resides on the same server. The legacy package is just to import a flat file into the database. When i right click and execute this package, it runs perfectly without any errors. However, if i try to schedule this package as a job, it keeps failing.

I checked the flat file source for the permission, it does have the account of the executor of the package. In the view history, i didn't see any more information about why it is failing. It keeps saying "The job failed. The Job was invoked by Schedule 10...."

Does anyone have any ideas?

Did you try to turn on logging so you could get better error details?|||

Thanks, I turned on the log and it wrote to the application log events saying. Is there another log event in SQL 2005? I only saw it said "View History" in the scheduled job.

Status: Failed - Invoked on: 2006-11-06 13:56:00 - Message: The job failed. The Job was invoked by Schedule 24 (Test Schedule). The last step to run was step 1 (RUN ImportData)

|||See this KB about turning on logging and general troubleshooting of SSIS packages under Agent:
http://support.microsoft.com/kb/918760

error when rebooting

Hi,
I developp a C/S soft for a client with MSDE. I install it on its computer (Win2k) and it can not reboot.
list of errors :
error when calling Dllregisterserver in c:\ProgramFiles\CommonFiles\system\ADO\...
msado15.dll hresult 80070005
msador15.dll hresult 8002801C
msadrh15.dll hresult 80004005
msadomd.dll hresult 80020009
msadox.dll hresult 8002801C
msjro.dll hresult 80020009
(sorry it's french translation ;o))
There are no pb with my computer(and with all I test !!!) and I expect a pb of MDAC version (mine is 2.7 SP1 refresh).
Has somebody the same pb ?
JC
It does look like a problem with mdac. I suggest you update it.
http://microsoft.com/data
-oj
http://www.rac4sql.net
"jc" <jean-christophe.watelet@.hardis.fr> wrote in message
news:A1E4439A-A868-45D4-B665-A2ECBFAFF3A0@.microsoft.com...
> Hi,
> I developp a C/S soft for a client with MSDE. I install it on its computer
(Win2k) and it can not reboot.
> list of errors :
> error when calling Dllregisterserver in
c:\ProgramFiles\CommonFiles\system\ADO\...
> msado15.dll hresult 80070005
> msador15.dll hresult 8002801C
> msadrh15.dll hresult 80004005
> msadomd.dll hresult 80020009
> msadox.dll hresult 8002801C
> msjro.dll hresult 80020009
> (sorry it's french translation ;o))
> There are no pb with my computer(and with all I test !!!) and I expect a pb of
MDAC version (mine is 2.7 SP1 refresh).
> Has somebody the same pb ?
> JC
|||thanks...but...
I just have a phonecall with the informatic system manager.
It seems not to be a pb of MDAC version but a conflict with a Pocket PC and (for me) due to the install of MSDE, especially with the evolution of the MDAC. The local user settings are corrupt...not the local machine settings.
I suppose the MDAC 2.7 SP1 Refresh is not compatible with activesync 3.5...and I will look for known bugs on the web
Tch?
JC
|||Bonjour JC,
Like you I have just installed MSDE into Win2k and I get exactly the same error report as yourself at startup but with a different set of DLLs. msado15, msadox, mscidce, msadds, msdapfst, msdarem, msdfmap. I can get through the problem and login using my normal profile but problems like Windows Explorer not navigating correctly and locking up occur. Unlike you, I am no expert in this area, the machine is simply my own home setup.
Since you posted this problem, have you managed to learn anything more about a solution which may point me in the right direction?
Merci beaucoup,
AB

Error when file is saved to PDF

I am calling a procedure that will save a file to PDF. It runs successfully. When I go to look at the PDF, I get a message, "Acrobat reader could not open 'Doc.pdf' because it is either not a support file or because the file has been damaged."

This is how I'm calling the procedure:

SaveFile(Dts.Variables("varSSRS_URL").Value.ToString() + "?/TestReports/MyReport&rs:Command=Render&rs:Format=PDF", "C:\" + "MyReport.pdf")

Ifaber,

Is this a SSIS ralated issue? If so, please provide more details about the error, which task, etc...

|||I'm with Rafael. I cannot find a SaveFile function when searching in an SSIS script component.|||

Yes, this is a SSIS issue. I put this script in a Script Task.

See http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1173906&SiteID=1&PageID=1 for reference. I have jumped through a lot of errors, and this is my last problem.

|||

ifaber wrote:

Yes, this is a SSIS issue. I put this script in a Script Task.

See http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1173906&SiteID=1&PageID=1 for reference. I have jumped through a lot of errors, and this is my last problem.

In that case, you're going to have to provide your script for us to look at. The link you provided doesn't serve as a valid foundation for your question.

My question though, is where is the PDF data stream? You can't create a PDF file by merely adding a .pdf extension. The data going into SaveFile needs to be a PDF binary stream, I would think.|||

I don't know anything about a PDF data stream. I read somewhere else on the net that 'rs:Command=Render&rs:Format=PDF' would work.

This is the sample that I got and used:

//Sample call
SaveFile(Dts.Variables("varSSRS_URL").Value.ToString() + "?%2fYOUR_SSRS_FOLDER%2fYOUR_REPORT_NAME&rs:Command=Render&rs:Format=PDF", outpath + "FILENAME.pdf")

//Note that you can replace "EXCEL" by "CSV" or "PDF" or any other supported export format

//The get & save file method
Protected Sub SaveFile(ByVal url As String, ByVal localpath As String)
Dim loRequest As System.Net.HttpWebRequest
Dim loResponse As System.Net.HttpWebResponse
Dim loResponseStream As System.IO.Stream
Dim loFileStream As New System.IO.FileStream(localpath, System.IO.FileMode.Create, System.IO.FileAccess.Write)
Dim laBytes(256) As Byte
Dim liCount As Integer = 1

Try
loRequest = CType(System.Net.WebRequest.Create(url), System.Net.HttpWebRequest)
loRequest.Credentials = New System.Net.NetworkCredential(Dts.Variables("varSSRS_LOGIN").Value.ToString(), Dts.Variables("varSSRS_PASSWORD").Value.ToString(), Dts.Variables("varSSRS_DOMAIN").Value.ToString())
loRequest.Timeout = 1000 * 60 * 15 'timeout 15 minutes
loRequest.Method = "GET"
loResponse = CType(loRequest.GetResponse, System.Net.HttpWebResponse)
loResponseStream = loResponse.GetResponseStream
Do While liCount > 0
liCount = loResponseStream.Read(laBytes, 0, 256)
loFileStream.Write(laBytes, 0, liCount)
Loop
loFileStream.Flush()
loFileStream.Close()
Catch ex As Exception

End Try

End Sub

|||Unless you're having issues with the variables, this looks like it's an SSRS issue....

I think the thread already going in the SSRS forum would be more beneficial.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1199641&SiteID=1|||I had issues with the variables because I had a typo. Now it just won't create the PDF.|||

ifaber wrote:

I had issues with the variables because I had a typo. Now it just won't create the PDF.

Yep, then we might want to look at moving this back to the SSRS forum for starters. The code you are building isn't specific to SSIS (though you might be using SSIS variables) so we are likely not going to be able to help you any further.|||Ok...thanks|||

ifaber wrote:

Ok...thanks

Moved to the SSRS Forum to see if anyone over here has any ideas for this custom code.

ifaber is trying to build a PDF file using a reporting services call. The user is trying to perform this in an SSIS task, but the code the user has built isn't SSIS specific.

Thanks,
Phil Brammer
SSIS Moderator

Error when file is saved to PDF

I am calling a procedure that will save a file to PDF. It runs successfully. When I go to look at the PDF, I get a message, "Acrobat reader could not open 'Doc.pdf' because it is either not a support file or because the file has been damaged."

This is how I'm calling the procedure:

SaveFile(Dts.Variables("varSSRS_URL").Value.ToString() + "?/TestReports/MyReport&rs:Command=Render&rs:Format=PDF", "C:\" + "MyReport.pdf")

Ifaber,

Is this a SSIS ralated issue? If so, please provide more details about the error, which task, etc...

|||I'm with Rafael. I cannot find a SaveFile function when searching in an SSIS script component.|||

Yes, this is a SSIS issue. I put this script in a Script Task.

See http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1173906&SiteID=1&PageID=1 for reference. I have jumped through a lot of errors, and this is my last problem.

|||

ifaber wrote:

Yes, this is a SSIS issue. I put this script in a Script Task.

See http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1173906&SiteID=1&PageID=1 for reference. I have jumped through a lot of errors, and this is my last problem.

In that case, you're going to have to provide your script for us to look at. The link you provided doesn't serve as a valid foundation for your question.

My question though, is where is the PDF data stream? You can't create a PDF file by merely adding a .pdf extension. The data going into SaveFile needs to be a PDF binary stream, I would think.|||

I don't know anything about a PDF data stream. I read somewhere else on the net that 'rs:Command=Render&rs:Format=PDF' would work.

This is the sample that I got and used:

//Sample call
SaveFile(Dts.Variables("varSSRS_URL").Value.ToString() + "?%2fYOUR_SSRS_FOLDER%2fYOUR_REPORT_NAME&rs:Command=Render&rs:Format=PDF", outpath + "FILENAME.pdf")

//Note that you can replace "EXCEL" by "CSV" or "PDF" or any other supported export format

//The get & save file method
Protected Sub SaveFile(ByVal url As String, ByVal localpath As String)
Dim loRequest As System.Net.HttpWebRequest
Dim loResponse As System.Net.HttpWebResponse
Dim loResponseStream As System.IO.Stream
Dim loFileStream As New System.IO.FileStream(localpath, System.IO.FileMode.Create, System.IO.FileAccess.Write)
Dim laBytes(256) As Byte
Dim liCount As Integer = 1

Try
loRequest = CType(System.Net.WebRequest.Create(url), System.Net.HttpWebRequest)
loRequest.Credentials = New System.Net.NetworkCredential(Dts.Variables("varSSRS_LOGIN").Value.ToString(), Dts.Variables("varSSRS_PASSWORD").Value.ToString(), Dts.Variables("varSSRS_DOMAIN").Value.ToString())
loRequest.Timeout = 1000 * 60 * 15 'timeout 15 minutes
loRequest.Method = "GET"
loResponse = CType(loRequest.GetResponse, System.Net.HttpWebResponse)
loResponseStream = loResponse.GetResponseStream
Do While liCount > 0
liCount = loResponseStream.Read(laBytes, 0, 256)
loFileStream.Write(laBytes, 0, liCount)
Loop
loFileStream.Flush()
loFileStream.Close()
Catch ex As Exception

End Try

End Sub

|||Unless you're having issues with the variables, this looks like it's an SSRS issue....

I think the thread already going in the SSRS forum would be more beneficial.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1199641&SiteID=1|||I had issues with the variables because I had a typo. Now it just won't create the PDF.|||

ifaber wrote:

I had issues with the variables because I had a typo. Now it just won't create the PDF.

Yep, then we might want to look at moving this back to the SSRS forum for starters. The code you are building isn't specific to SSIS (though you might be using SSIS variables) so we are likely not going to be able to help you any further.|||Ok...thanks|||

ifaber wrote:

Ok...thanks

Moved to the SSRS Forum to see if anyone over here has any ideas for this custom code.

ifaber is trying to build a PDF file using a reporting services call. The user is trying to perform this in an SSIS task, but the code the user has built isn't SSIS specific.

Thanks,
Phil Brammer
SSIS Moderator

Error when calling stored procedure

I am trying to execute a store procedure from ASP/VB but it fails with the message:

Incorrect syntax near 'InitProject'.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.Data.SqlClient.SqlException: Incorrect syntax near 'InitProject'.

Source Error:

Line 24: cmd.Parameters("@.ProjectId").Value = 3Line 25: cn.Open()Line 26: cmd.ExecuteNonQuery()Line 27: cn.Close()Line 28: End Sub

Here is my code:

'Execute the InitProject stored procedure
'Create the connection from the string in the web.config file
Dim cn As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("SMARTConnectionString").ConnectionString)
'I want to execute InitProject stored procedure
Dim cmd As SqlCommand = New SqlCommand("InitProject", cn)
'With the parameter @.ProjectId = 3
cmd.Parameters.Add(New SqlParameter("@.ProjectId", Data.SqlDbType.Int))
cmd.Parameters("@.ProjectId").Direction = Data.ParameterDirection.Input
cmd.Parameters("@.ProjectId").Value = 3
cn.Open()
'But this fails
cmd.ExecuteNonQuery()
cn.Close()

And my stored procedure is defined as:

[dbo].[InitProject] @.ProjectId int
-- Add the parameters for the stored procedure here
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
insert into MATERIAL ( PROJECT_ID, SECTION_ID, CATEGORY_ID, ROOM_ID, ITEM_ID )
select @.ProjectId, SECTION_ID, CATEGORY_ID, ROOM_ID, ITEM_ID
from MATERIAL_TEMPLATE
END

The store procedure works fine when I do

exec InitProject 3

in sql query.

I have been away from .NET but try putting commandtype = storedproc. I dont remember the syntax but you could figure it out. It would go before adding the parameters.|||

Like this:

Dim cmd As SqlCommand = New SqlCommand("InitProject", cn)

cmd.CommandType = CommandType.StoredProcedure

.....

|||

Thank you very much for your help. It works like this:

'I want to execute InitProject stored procedure
Dim cmd As SqlCommand = New SqlCommand("InitProject", cn)
cmd.CommandType = Data.CommandType.StoredProcedure
'With the parameter @.ProjectId = 3
cmd.Parameters.Add(New SqlParameter("@.ProjectId", Data.SqlDbType.Int))
cmd.Parameters("@.ProjectId").Direction = Data.ParameterDirection.Input
cmd.Parameters("@.ProjectId").Value = 3
cn.Open()
'Now it works
cmd.ExecuteNonQuery()
cn.Close()

I don't understand why I need to prefix withData. when others don't need.

|||

Hi

Hierarchy : System.Data.CommandType.StoredProcedure

You just import namespace System so you need to prefix with Data

If you import namespace System.Data you don't need that.

Hope it helps.

Error when calling a Sub report (Jump to report)

I have created a two reports with a parameterized query - one master and one sub report.
When I want to call the sub report from the master report I would like to be able to pass the values for the parameters in to the sub report.

And I also don′t want the passed parameters to be prompted in the sub report (so I blanked out the 'Prompt:' field).

But I got following error:
The report parameter 'parameter name' is read-only and cannot be modified.

What to do?

Running Report Server: 8.00.743.00

/Christofer

Instead of blanking the Prompt field, check the 'Hidden' checkbox.|||

Well...
Should it be in the Report Parameters dialog box where I define parameters?
If so, I can′t find this checkbox "Hidden" in my version, or where should I find it?

Regards,
Christofer

Error traping in DTS

I am using a DTS for my purpose and I am calling that DTS execution in a stored procedure(using master..xp_cmdshell dtsrun/F).I like to store the error meassage or error description in the Stored Procedure if it fails due to any reason.How can I do that?call the dtsrun also with /L<log file path >|||Will you kindly give me a brief of that please?|||dtsrun gets some arguments the /L <file path > switch "tells" dtsrun where to log it executed steps results
for more info search books online for "dtsrun Utility" there you can find all info about dtsrun.