Error running report: procedure

Showing posts with label procedure. Show all posts

Thursday, March 29, 2012

Error when using SP in data flow

I want to execute a stored procedure in an OLE DB source in a Data Flow Task.

The stored procedure has a parameter.

When I put in an SQL command as :

EXEC sp_readcustomers 1

(thus passing 1 as the parameter value I can use the Preview button and I get a list of columns being returned.

The data flow task should run inside a ForEach Loop where I assign the value of an ADO resultset to a variable and it is this variable I want to pass to the SP :

EXEC sp_readcustomers ?

In the Parameter mapping I then name the parameter @.par_company_id (which is the exact same name as in the SP) and map it to the variable var_company_id.

@.par_company_id has been declared as int, var_company_id as Int16

When I now try to Parse or Preview the query from the OLE DB Source Edit window I get the following errors :

Parse : Attempted to read or write protected memory. This is often an indication that other memory is corrupt.

Preview : No value given for one or more required parameters (Microsoft SQL Native Client)

I have already installed SQL 2005 SP2 and VS2005 SP1.

I have tried everything I know, please help ?!?!

Have you tried putting the SQL Statment in a variable? You can parametrized queries using expressions in variables; then the source component will get the sql statemnt from the variable. There are a lot of example on this forum.|||

I have tried that.

I created a variable, set EvaluateAsExpression to True and entered the select statement as an expression.

Problem is that I can't select the variable, it is in the correct scope but it doesn't show up in the drop-down list of the OLE DB Source.

When you look at the documentation (Books online, etc. ...) I'm using the correct method to pass parameters to a stored procedure, is this a bug in VS/SQL ?

|||

Ronald Dirkx wrote:

I have tried that.

I created a variable, set EvaluateAsExpression to True and entered the select statement as an expression.

Problem is that I can't select the variable, it is in the correct scope but it doesn't show up in the drop-down list of the OLE DB Source.

When you look at the documentation (Books online, etc. ...) I'm using the correct method to pass parameters to a stored procedure, is this a bug in VS/SQL ?

To use the variable in an OLE DB Source, select the data access mode of "SQL command from variable." Then your variable will show up in the drop down.

Error when using a stored procedure in OLE DB source

I am trying to use a stored procedure in the OLE DB source, I am using the SQL Command for the Data Access mode. It returns values when using the preview but when I test the package I receive the error, "A rowset based on the SQL command was not returned by the OLE DB provider."how is the ole db source configured?|||For the the connection manager I just have a basic connection to the Sql server and then I am using the SQL command for the data access mode. The connection manager does work when I use other access modes like the table and view option.|||how is the connection manager configured? you may need to use a different configuration.|||ConnectionManagerType = OLEDB
Data Source = ABCSERVER;Initial Catalog=ABCDATABASE;Provider=SQLNCLI.1;OLE DB Services=-13;Integrated Security=SSPI;Auto Translate=True;
DataSourceID =
DelayValidation = False
Description =
RetainSameConnection = False
SupportsDTCTransactions = True|||

DustinT wrote:
ConnectionManagerType = OLEDB
Data Source = ABCSERVER;Initial Catalog=ABCDATABASE;Provider=SQLNCLI.1;OLE DB Services=-13;Integrated Security=SSPI;Auto Translate=True;
DataSourceID =
DelayValidation = False
Description =
RetainSameConnection = False
SupportsDTCTransactions = True

if i'm not mistaken, the sql native client provider (SQLNCLI.1) only works with sql server 2005.

if you are using a different version of sql server, then try using the microsoft ole db provider for sql server (SQLOLEDB.1)

|||See http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=366077&SiteID=1

Tuesday, March 27, 2012

Error when running sp_helpdb query

I get the following error when running command "sp_helpdb" in Query Analyzer.
Server: Msg 515, Level 16, State 2, Procedure sp_helpdb, Line 53 Cannot
insert the value NULL into column 'owner', table
'tempdb.dbo.#spdbdesc___________________________________________________________________________________________________________000100371F02';
column does not allow nulls. INSERT fails.
The statement has been terminated.Kush
use master
go
sp_helptext 'sp_helpdb'
I think it fails to find suser_sname of the current session
"Kush" <Kush@.discussions.microsoft.com> wrote in message
news:44DAF4E9-9DCA-47FC-85A3-79234F68EFF3@.microsoft.com...
>I get the following error when running command "sp_helpdb" in Query
>Analyzer.
> Server: Msg 515, Level 16, State 2, Procedure sp_helpdb, Line 53 Cannot
> insert the value NULL into column 'owner', table
> 'tempdb.dbo.#spdbdesc___________________________________________________________________________________________________________000100371F02';
> column does not allow nulls. INSERT fails.
> The statement has been terminated.|||Here's an URL that might explain it. Google is your friend :-)
http://groups.google.com/group/microsoft.public.sqlserver.server/browse_thread/thread/bc6f4659bb8dd60c/50f0170456dd13b5?q=sp_helpdb+insert+NULL&rnum=3#50f0170456dd13b5
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Kush" <Kush@.discussions.microsoft.com> wrote in message
news:44DAF4E9-9DCA-47FC-85A3-79234F68EFF3@.microsoft.com...
>I get the following error when running command "sp_helpdb" in Query Analyzer.
> Server: Msg 515, Level 16, State 2, Procedure sp_helpdb, Line 53 Cannot
> insert the value NULL into column 'owner', table
> 'tempdb.dbo.#spdbdesc___________________________________________________________________________________________________________000100371F02';
> column does not allow nulls. INSERT fails.
> The statement has been terminated.|||I will try that and let you know what happens!
"Tibor Karaszi" wrote:
> Here's an URL that might explain it. Google is your friend :-)
> http://groups.google.com/group/microsoft.public.sqlserver.server/browse_thread/thread/bc6f4659bb8dd60c/50f0170456dd13b5?q=sp_helpdb+insert+NULL&rnum=3#50f0170456dd13b5
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Kush" <Kush@.discussions.microsoft.com> wrote in message
> news:44DAF4E9-9DCA-47FC-85A3-79234F68EFF3@.microsoft.com...
> >I get the following error when running command "sp_helpdb" in Query Analyzer.
> >
> > Server: Msg 515, Level 16, State 2, Procedure sp_helpdb, Line 53 Cannot
> > insert the value NULL into column 'owner', table
> > 'tempdb.dbo.#spdbdesc___________________________________________________________________________________________________________000100371F02';
> > column does not allow nulls. INSERT fails.
> > The statement has been terminated.
>

Monday, March 26, 2012

Error when RowNumber is 0

Or is it when the dataset is null? Either way, my report is reporting a
Javasript error when my stored procedure returns no records. I isolated the
expression that is causing the problem. It is:
=iif( RowNumber("DataRecords") Mod 2, "WhiteSmoke", "White")
which I had in the table row Background property to alternate my background
color of the rows. It works fine, except when there are no records.
Can someone tell me how to fix it without removing the expression, which is
what I did to get around it for now. I would like to have the alternating row
colors back if I can. Basically I need to be able to determine that
RowNumber is 0 or that DataRecords, which is my dataset, is null.
BTW, the error did not show up while I was testing under Visual Studio 2005
Pro in Debug mode. Everything appeared fine until I deployed to the server.
If anyone knows how I can catch this type of problem BEFORE I deploy I would
like to know also.
TIA
URW
PS: I had accidentally posted this in the SQL Server Replication Group this
morning. I hope I have the right place (Reporting Services) this time. :-)I found the answer in another post, so the problem has been solved. I should
have searched first and I apologze for not doing that. I guess after I
realized I had posted to the wrong group earlier, I was a bit flustered
Thanks
"URW" wrote:
> Or is it when the dataset is null? Either way, my report is reporting a
> Javasript error when my stored procedure returns no records. I isolated the
> expression that is causing the problem. It is:
> =iif( RowNumber("DataRecords") Mod 2, "WhiteSmoke", "White")
> which I had in the table row Background property to alternate my background
> color of the rows. It works fine, except when there are no records.
> Can someone tell me how to fix it without removing the expression, which is
> what I did to get around it for now. I would like to have the alternating row
> colors back if I can. Basically I need to be able to determine that
> RowNumber is 0 or that DataRecords, which is my dataset, is null.
> BTW, the error did not show up while I was testing under Visual Studio 2005
> Pro in Debug mode. Everything appeared fine until I deployed to the server.
> If anyone knows how I can catch this type of problem BEFORE I deploy I would
> like to know also.
> TIA
> URW
> PS: I had accidentally posted this in the SQL Server Replication Group this
> morning. I hope I have the right place (Reporting Services) this time. :-)|||Could you tell us the solution ;-)?
"URW" <URW@.discussions.microsoft.com> escribió en el mensaje
news:B817A72E-446F-43BF-9B7E-C0A4D2553C15@.microsoft.com...
>I found the answer in another post, so the problem has been solved. I
>should
> have searched first and I apologze for not doing that. I guess after I
> realized I had posted to the wrong group earlier, I was a bit flustered
> Thanks
> "URW" wrote:
>> Or is it when the dataset is null? Either way, my report is reporting a
>> Javasript error when my stored procedure returns no records. I isolated
>> the
>> expression that is causing the problem. It is:
>> =iif( RowNumber("DataRecords") Mod 2, "WhiteSmoke", "White")
>> which I had in the table row Background property to alternate my
>> background
>> color of the rows. It works fine, except when there are no records.
>> Can someone tell me how to fix it without removing the expression, which
>> is
>> what I did to get around it for now. I would like to have the alternating
>> row
>> colors back if I can. Basically I need to be able to determine that
>> RowNumber is 0 or that DataRecords, which is my dataset, is null.
>> BTW, the error did not show up while I was testing under Visual Studio
>> 2005
>> Pro in Debug mode. Everything appeared fine until I deployed to the
>> server.
>> If anyone knows how I can catch this type of problem BEFORE I deploy I
>> would
>> like to know also.
>> TIA
>> URW
>> PS: I had accidentally posted this in the SQL Server Replication Group
>> this
>> morning. I hope I have the right place (Reporting Services) this time.
>> :-)|||Sure.
I just change the expression to
=iif( RowNumber(Nothing)Mod 2, "WhiteSmoke", "White")
I think by defaulting the scope of RowNumber, I am actually counting the
number of rows in the table, rather than the number of rows in the Dataset,
like I was before. Since the dataset can be null, I had problems when I tried
to get the row count for a null object. But the table is never null, and thus
the row count is always save to access and thus no crash. I am not sure if my
explanaition is correct, but that is how I could explain to myself why
getting the row number of a null object worked.
If you search for RowNumber in this group you will find several posts that
refer to this problem. They all show the same solution, but no explanaition
why it works. I hope mine helps but if not at least you have the answer for
my problem.
"MÃ³nica" wrote:
> Could you tell us the solution ;-)?
>
> "URW" <URW@.discussions.microsoft.com> escribiÃ³ en el mensaje
> news:B817A72E-446F-43BF-9B7E-C0A4D2553C15@.microsoft.com...
> >I found the answer in another post, so the problem has been solved. I
> >should
> > have searched first and I apologze for not doing that. I guess after I
> > realized I had posted to the wrong group earlier, I was a bit flustered
> >
> > Thanks
> >
> > "URW" wrote:
> >
> >> Or is it when the dataset is null? Either way, my report is reporting a
> >> Javasript error when my stored procedure returns no records. I isolated
> >> the
> >> expression that is causing the problem. It is:
> >>
> >> =iif( RowNumber("DataRecords") Mod 2, "WhiteSmoke", "White")
> >>
> >> which I had in the table row Background property to alternate my
> >> background
> >> color of the rows. It works fine, except when there are no records.
> >>
> >> Can someone tell me how to fix it without removing the expression, which
> >> is
> >> what I did to get around it for now. I would like to have the alternating
> >> row
> >> colors back if I can. Basically I need to be able to determine that
> >> RowNumber is 0 or that DataRecords, which is my dataset, is null.
> >>
> >> BTW, the error did not show up while I was testing under Visual Studio
> >> 2005
> >> Pro in Debug mode. Everything appeared fine until I deployed to the
> >> server.
> >> If anyone knows how I can catch this type of problem BEFORE I deploy I
> >> would
> >> like to know also.
> >>
> >> TIA
> >>
> >> URW
> >>
> >> PS: I had accidentally posted this in the SQL Server Replication Group
> >> this
> >> morning. I hope I have the right place (Reporting Services) this time.
> >> :-)
>
>sql

Friday, March 23, 2012

Error when issuing BACKUP LOG mydb with truncate_only

Hi,

Following error appears in the SQL Server error log when I execute BACKUP Log db with truncate_only using a stored procedure. The stored proc I am using is as follows:

CREATE procedure spm_tranlog as

declare @.DBName as Varchar(120)

select @.DBName = DB_name()

dump transaction @.DBName with truncate_only

There are data import processes running in the night, before starting the processes we are executing spm_tranlog procedure to clear the transaction logs. The following error appears in log after executing the spm_tranlog:

“BACKUP failed to complete the command exec spm_tranlog”

This always happens after weekly server maintenance tasks. The scheduled maintenance tasks I am running are:Index rebuild, truncate log and shrinking database. There were no other processes running during that time when the spm_tranlog prodecure fail.

The Database size is 40GB, Log size is 80MB.

The database is in the simple recovery model.The database is in the simple recovery model.

I am running SQL Server 2000 (SP4) on windows server 2003.

Please help me to resolve this issue.

Thanks,

Roshan.

Hello Roshan,

Try this instead:

create procedure spm_tranlog

as
declare @.DBName asvarchar(120), @.sql asvarchar(200)

select @.DBName =DB_name()

select @.sql ='backup log '+ @.DBName +' with truncate_only'

execute(@.sql)
go

Hope this helps.

Jarret

|||

Hi Jarret,

Thanks for your reply. I have changed the DUMP TRANSACTION to BACKUP LOG in my stored proc, but still shows same error message.

Usualy the dump transaction (or Backup Log) fails only when server is backing up databases or shrinking.

My problem is Backup Log satement fails even there are no database backups running. This is really strange.

Thanks,

Roshan.

|||

Can you run the backup log statement directly without getting an error?

backup log DB_Name with truncate_only

Jarret

|||

No. its not allowing me to run the backup log statement directly.

Roshan.

|||

Can you post the error message you are getting and the exact statement you are running?

Are you sure you have permissions to do this? You need to be a sysadmin on the server or in the db_owner role of the database.

Jarret

|||

Hi Jarret,

I am receiving following error in the SQL server log: "BACKUP failed to complete the command exec spm_tranlog"

and I am running following stored proc:

CREATE procedure spm_tranlog as

/* 29/03/2006 RXP: Modified to get the database name using system function
* DB_name(), to avoid hardcoding database name.
*
*/

declare @.DBName as Varchar(120)

select @.DBName = DB_name()
dump transaction @.DBName with truncate_only
GO

This was running perfecctly for a long time, suddenly start creating problems. The user running this proc has sysadmin rights.

Thanks,

Roshan.

|||

How are you running the stored procedure, just 'exec spm_tranlog' from a new query window?

Bypassing the stored procedure, can you try to run this:

declare @.DBName as Varchar(120)
select @.DBName = DB_name()
dump transaction @.DBName with truncate_only

And also...

declare @.DBName as Varchar(120)
select @.DBName = DB_name()
backup log @.DBName with truncate_only

If these still don't work, post the error message(s) you get from each.

Jarret

|||You cant truncate the Log with SIMPLE PLAN in a DB. Because it is doing by itself.

Error when issuing BACKUP LOG mydb with truncate_only

Hi,

Following error appears in the SQL Server error log when I execute BACKUP Log db with truncate_only using a stored procedure. The stored proc I am using is as follows:

CREATE procedure spm_tranlog as

declare @.DBName as Varchar(120)

select @.DBName = DB_name()

dump transaction @.DBName with truncate_only

“BACKUP failed to complete the command exec spm_tranlog”

The Database size is 40GB, Log size is 80MB.

The database is in the simple recovery model.The database is in the simple recovery model.

I am running SQL Server 2000 (SP4) on windows server 2003.

Please help me to resolve this issue.

Thanks,

Roshan.

Hello Roshan,

Try this instead:

create procedure spm_tranlog

as
declare @.DBName asvarchar(120), @.sql asvarchar(200)

select @.DBName =DB_name()

select @.sql ='backup log '+ @.DBName +' with truncate_only'

execute(@.sql)
go

Hope this helps.

Jarret

|||

Hi Jarret,

Thanks for your reply. I have changed the DUMP TRANSACTION to BACKUP LOG in my stored proc, but still shows same error message.

Usualy the dump transaction (or Backup Log) fails only when server is backing up databases or shrinking.

My problem is Backup Log satement fails even there are no database backups running. This is really strange.

Thanks,

Roshan.

|||

Can you run the backup log statement directly without getting an error?

backup log DB_Name with truncate_only

Jarret

|||

No. its not allowing me to run the backup log statement directly.

Roshan.

|||

Can you post the error message you are getting and the exact statement you are running?

Are you sure you have permissions to do this? You need to be a sysadmin on the server or in the db_owner role of the database.

Jarret

|||

Hi Jarret,

I am receiving following error in the SQL server log: "BACKUP failed to complete the command exec spm_tranlog"

and I am running following stored proc:

CREATE procedure spm_tranlog as

/* 29/03/2006 RXP: Modified to get the database name using system function
* DB_name(), to avoid hardcoding database name.
*
*/

declare @.DBName as Varchar(120)

select @.DBName = DB_name()
dump transaction @.DBName with truncate_only
GO

This was running perfecctly for a long time, suddenly start creating problems. The user running this proc has sysadmin rights.

Thanks,

Roshan.

|||

How are you running the stored procedure, just 'exec spm_tranlog' from a new query window?

Bypassing the stored procedure, can you try to run this:

declare @.DBName as Varchar(120)
select @.DBName = DB_name()
dump transaction @.DBName with truncate_only

And also...

declare @.DBName as Varchar(120)
select @.DBName = DB_name()
backup log @.DBName with truncate_only

If these still don't work, post the error message(s) you get from each.

Jarret

|||You cant truncate the Log with SIMPLE PLAN in a DB. Because it is doing by itself.

Error when issuing BACKUP LOG mydb with truncate_only

Hi,

Following error appears in the SQL Server error log when I execute BACKUP Log db with truncate_only using a stored procedure. The stored proc I am using is as follows:

CREATE procedure spm_tranlog as

declare @.DBName as Varchar(120)

select @.DBName = DB_name()

dump transaction @.DBName with truncate_only

“BACKUP failed to complete the command exec spm_tranlog”

The Database size is 40GB, Log size is 80MB.

The database is in the simple recovery model.The database is in the simple recovery model.

I am running SQL Server 2000 (SP4) on windows server 2003.

Please help me to resolve this issue.

Thanks,

Roshan.

Hello Roshan,

Try this instead:

create procedure spm_tranlog

as
declare @.DBName asvarchar(120), @.sql asvarchar(200)

select @.DBName =DB_name()

select @.sql ='backup log '+ @.DBName +' with truncate_only'

execute(@.sql)
go

Hope this helps.

Jarret

|||

Hi Jarret,

Thanks for your reply. I have changed the DUMP TRANSACTION to BACKUP LOG in my stored proc, but still shows same error message.

Usualy the dump transaction (or Backup Log) fails only when server is backing up databases or shrinking.

My problem is Backup Log satement fails even there are no database backups running. This is really strange.

Thanks,

Roshan.

|||

Can you run the backup log statement directly without getting an error?

backup log DB_Name with truncate_only

Jarret

|||

No. its not allowing me to run the backup log statement directly.

Roshan.

|||

Can you post the error message you are getting and the exact statement you are running?

Are you sure you have permissions to do this? You need to be a sysadmin on the server or in the db_owner role of the database.

Jarret

|||

Hi Jarret,

I am receiving following error in the SQL server log: "BACKUP failed to complete the command exec spm_tranlog"

and I am running following stored proc:

CREATE procedure spm_tranlog as

/* 29/03/2006 RXP: Modified to get the database name using system function
* DB_name(), to avoid hardcoding database name.
*
*/

declare @.DBName as Varchar(120)

select @.DBName = DB_name()
dump transaction @.DBName with truncate_only
GO

This was running perfecctly for a long time, suddenly start creating problems. The user running this proc has sysadmin rights.

Thanks,

Roshan.

|||

How are you running the stored procedure, just 'exec spm_tranlog' from a new query window?

Bypassing the stored procedure, can you try to run this:

declare @.DBName as Varchar(120)
select @.DBName = DB_name()
dump transaction @.DBName with truncate_only

And also...

declare @.DBName as Varchar(120)
select @.DBName = DB_name()
backup log @.DBName with truncate_only

If these still don't work, post the error message(s) you get from each.

Jarret

|||You cant truncate the Log with SIMPLE PLAN in a DB. Because it is doing by itself.sql

Error when issuing BACKUP LOG mydb with truncate_only

Hi,

Following error appears in the SQL Server error log when I execute BACKUP Log db with truncate_only using a stored procedure. The stored proc I am using is as follows:

CREATE procedure spm_tranlog as

declare @.DBName as Varchar(120)

select @.DBName = DB_name()

dump transaction @.DBName with truncate_only

“BACKUP failed to complete the command exec spm_tranlog”

This always happens after weekly server maintenance tasks. The scheduled maintenance tasks I am running are: Index rebuild, truncate log and shrinking database. There were no other processes running during that time when the spm_tranlog prodecure fail.

The Database size is 40GB, Log size is 80MB.

The database is in the simple recovery model.The database is in the simple recovery model.

I am running SQL Server 2000 (SP4) on windows server 2003.

Please help me to resolve this issue.

Thanks,

Roshan.

Hello Roshan,

Try this instead:

create procedure spm_tranlog
as
declare @.DBName as varchar(120), @.sql as varchar(200)
select @.DBName = DB_name()
select @.sql = 'backup log ' + @.DBName + ' with truncate_only'
execute (@.sql)
go

Hope this helps.

Jarret

|||

Hi Jarret,

Thanks for your reply. I have changed the DUMP TRANSACTION to BACKUP LOG in my stored proc, but still shows same error message.

Usualy the dump transaction (or Backup Log) fails only when server is backing up databases or shrinking.

My problem is Backup Log satement fails even there are no database backups running. This is really strange.

Thanks,

Roshan.

|||

Can you run the backup log statement directly without getting an error?

backup log DB_Name with truncate_only

Jarret

|||

No. its not allowing me to run the backup log statement directly.

Roshan.

|||

Can you post the error message you are getting and the exact statement you are running?

Are you sure you have permissions to do this? You need to be a sysadmin on the server or in the db_owner role of the database.

Jarret

|||

Hi Jarret,

I am receiving following error in the SQL server log: "BACKUP failed to complete the command exec spm_tranlog"

and I am running following stored proc:

CREATE procedure spm_tranlog as

/* 29/03/2006 RXP: Modified to get the database name using system function
* DB_name(), to avoid hardcoding database name.
*
*/

declare @.DBName as Varchar(120)

select @.DBName = DB_name()
dump transaction @.DBName with truncate_only
GO

This was running perfecctly for a long time, suddenly start creating problems. The user running this proc has sysadmin rights.

Thanks,

Roshan.

|||

How are you running the stored procedure, just 'exec spm_tranlog' from a new query window?

Bypassing the stored procedure, can you try to run this:

declare @.DBName as Varchar(120)
select @.DBName = DB_name()
dump transaction @.DBName with truncate_only

And also...

declare @.DBName as Varchar(120)
select @.DBName = DB_name()
backup log @.DBName with truncate_only

If these still don't work, post the error message(s) you get from each.

Jarret

|||You cant truncate the Log with SIMPLE PLAN in a DB. Because it is doing by itself.

Error when inserting datetime in german SQL Server 2000

Hello!
I have a program that are using a stored procedure to insert a row in
an SQL Server. During several years this program has worked fine using
an English version of SQL server. Now a customer wants to use a german
SQL server, but it doesn't work. The row is never inserted. I get the
following error:
[Microsoft][ODBC SQL Server Driver][SQL Server]Fehler beim
Konvertieren von Datentyp varchar in datetime.
Now, if I change the language setting to english in the "SQL Server
Login properties for 'sa'" it all works. If I restore the language
setting to german in the "SQL Server Login properties for 'sa'", it
all continues to work until I re-boot the PC. When I have re-booted, I
once gain end up with the error that I have described before.
It seems strange to me that SQL Server doesn't behave in a consistent
way. Sometimes it works with german language settings, sometimes it
doesn't.
Any explanations would be very appriciated.
BR / PatrikThis is because dateformat changes according to language settings.
date format considerations while insert and update:
You can SET DATEFORMAT and modify code as follows.
create table test(dt datetime)
go
declare @.dt varchar(32)
set @.dt ='26/07/2002'
set dateformat dmy --change dateformat
insert into test values(convert(datetime,@.dt)) ;
select * from test;
go
OR other method would be to use explicit coversion of the
date using CONVERT function.
create table test(dt datetime)
go
declare @.dt varchar(32)
set @.dt ='26/07/2002'
insert into test values(convert(datetime,@.dt, 103)) ;
select * from test;
go
--
-Vishal
"Patrik Johansson" <patrikmjohansson@.yahoo.se> wrote in message
news:8d5c526d.0307010514.b59427d@.posting.google.com...
> Hello!
> I have a program that are using a stored procedure to insert a row in
> an SQL Server. During several years this program has worked fine using
> an English version of SQL server. Now a customer wants to use a german
> SQL server, but it doesn't work. The row is never inserted. I get the
> following error:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Fehler beim
> Konvertieren von Datentyp varchar in datetime.
> Now, if I change the language setting to english in the "SQL Server
> Login properties for 'sa'" it all works. If I restore the language
> setting to german in the "SQL Server Login properties for 'sa'", it
> all continues to work until I re-boot the PC. When I have re-booted, I
> once gain end up with the error that I have described before.
> It seems strange to me that SQL Server doesn't behave in a consistent
> way. Sometimes it works with german language settings, sometimes it
> doesn't.
> Any explanations would be very appriciated.
> BR / Patrik|||Patrik,
Yes, language and localizations are a problem to deal with. You might find
this topic helpful:
Writing International Transact-SQL Statements in the MSDN at:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_qd_14_3unn.asp
You can also manage this by setting the language of the connection that is
transmitting data. For this use: SET LANGUAGE xxx for a connection or use
sp_defaultlanguage to set the default for a login.
Russell Fields
"Patrik Johansson" <patrikmjohansson@.yahoo.se> wrote in message
news:8d5c526d.0307010514.b59427d@.posting.google.com...
> Hello!
> I have a program that are using a stored procedure to insert a row in
> an SQL Server. During several years this program has worked fine using
> an English version of SQL server. Now a customer wants to use a german
> SQL server, but it doesn't work. The row is never inserted. I get the
> following error:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Fehler beim
> Konvertieren von Datentyp varchar in datetime.
> Now, if I change the language setting to english in the "SQL Server
> Login properties for 'sa'" it all works. If I restore the language
> setting to german in the "SQL Server Login properties for 'sa'", it
> all continues to work until I re-boot the PC. When I have re-booted, I
> once gain end up with the error that I have described before.
> It seems strange to me that SQL Server doesn't behave in a consistent
> way. Sometimes it works with german language settings, sometimes it
> doesn't.
> Any explanations would be very appriciated.
> BR / Patrik|||Pass the date in YYYYMMDD or YYYY-MM-DD format. Then there is no confusion
for people *or* the software.
"Patrik Johansson" <patrikmjohansson@.yahoo.se> wrote in message
news:8d5c526d.0307010514.b59427d@.posting.google.com...
> Hello!
> I have a program that are using a stored procedure to insert a row in
> an SQL Server. During several years this program has worked fine using
> an English version of SQL server. Now a customer wants to use a german
> SQL server, but it doesn't work. The row is never inserted. I get the
> following error:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Fehler beim
> Konvertieren von Datentyp varchar in datetime.
> Now, if I change the language setting to english in the "SQL Server
> Login properties for 'sa'" it all works. If I restore the language
> setting to german in the "SQL Server Login properties for 'sa'", it
> all continues to work until I re-boot the PC. When I have re-booted, I
> once gain end up with the error that I have described before.
> It seems strange to me that SQL Server doesn't behave in a consistent
> way. Sometimes it works with german language settings, sometimes it
> doesn't.
> Any explanations would be very appriciated.
> BR / Patrik|||Only YYYYMMDD without any separators works always:
SET DATEFORMAT dmy
SELECT ISDATE('2003-07-13'), ISDATE('20030713')
SET DATEFORMAT mdy
SELECT ISDATE('2003-07-13'), ISDATE('20030713')
I can see that you as an American are not that used to dealing with date
formats as I as a European am ;-)
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:uame7U9PDHA.2768@.tk2msftngp13.phx.gbl...
> Pass the date in YYYYMMDD or YYYY-MM-DD format. Then there is no
confusion
> for people *or* the software.
>
> "Patrik Johansson" <patrikmjohansson@.yahoo.se> wrote in message
> news:8d5c526d.0307010514.b59427d@.posting.google.com...
> > Hello!
> >
> > I have a program that are using a stored procedure to insert a row in
> > an SQL Server. During several years this program has worked fine using
> > an English version of SQL server. Now a customer wants to use a german
> > SQL server, but it doesn't work. The row is never inserted. I get the
> > following error:
> >
> > [Microsoft][ODBC SQL Server Driver][SQL Server]Fehler beim
> > Konvertieren von Datentyp varchar in datetime.
> >
> > Now, if I change the language setting to english in the "SQL Server
> > Login properties for 'sa'" it all works. If I restore the language
> > setting to german in the "SQL Server Login properties for 'sa'", it
> > all continues to work until I re-boot the PC. When I have re-booted, I
> > once gain end up with the error that I have described before.
> >
> > It seems strange to me that SQL Server doesn't behave in a consistent
> > way. Sometimes it works with german language settings, sometimes it
> > doesn't.
> >
> > Any explanations would be very appriciated.
> >
> > BR / Patrik
>|||> I can see that you as an American are not that used to dealing with date
> formats as I as a European am ;-)
Hey, don't call me an American... I'm a Canuck, just ask Tom...
:-Psql

Wednesday, March 21, 2012

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

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

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.

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

Monday, March 19, 2012

Error when creating Stored Procedure

I am trying to create a new stored procedure:
****************************************
***************
CREATE PROCEDURE XXX_GetInfo AS
select cast(info.id as varchar) refno, info.begindt, enddt, info.company,
info.type, info.weight
from info info
union
select cast(test.id as varchar) refno, test.begindt, enddt, test.company,
test.type, info.weight
from link1.test.dbo.test test
order by begindt,enddt
****************************************
***************
link1 is a linked server
When I check syntax, sql comes back and says that syntax is correct.
When I try to create the stored procedure, I get an:
[Microsoft SQL-DMO (ODBC SQLState: 42000)]
Error 7405: Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS
options to be set for the connection. This ensures consistent query
semantics. Enable these options and then reissue your query.
I have checked that ANSI_NULLS, ANSI_WARNINGS, and ANSI Padding is
configured as on for both servers.
When Setting up the linked server :
General Tab
Linked server - link1
Other data source - Provider Name - Microsoft OLE DB Provider for
SQL Server
Product name -
Data source - ip address of the server
Provider string -
catalog - test
Security Tab
Be made using this security context - sa of the remote machine
Any help or insight as to why it will not allow me to create the stored
procedure will be greatly appreciated.
Thanks in Advance
VinceServer settings are overridden by tool settings which are overriden by
explicit SET settings. So just issue the appropriate SET statements in the
same query window where you are executing the create proc statement.
Cindy Gross, MCDBA, MCSE
http://cindygross.tripod.com
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Vince,
Thank you for using MSDN Newsgroup! It's my pleasure to assist you with this
issue.
I think Cindy has pointed out the root cause that the server settings are ov
eridden. To explicit
that settings in either Enterprise Manager or Query Analyzer, you should iss
ue the SET within
the CREATE PROCEDURE so that these setting will not be affected by SQL state
ments in
anohter Client Tools . Otherwise, the life span of these settings ends with
the batch finishes
and they may be OFF before you create your procedure.
Please look through the following script and see if it works on your side. I
f there is anything
more we can do to assist you, please feel free to post it in the group.
---
CREATE PROCEDURE XXX_GetInfo
AS
SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
GO
select cast(info.id as varchar) refno, info.begindt, enddt, info.company,
info.type, info.weight
from info info
union
select cast(test.id as varchar) refno, test.begindt, enddt, test.company,
test.type, info.weight
from link1.test.dbo.test test
order by begindt,enddt
GO
---
Best regards,
Billy Yao
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.

Monday, March 12, 2012

Error when calling stored procedure

Tongue Tied 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 Smile don't need.

|||

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.

Friday, March 9, 2012

Error views

Dear Team,

Can any body tell in which table the stored procedure error
details are stored.

When any stored procedure is compiled the
compilation errors/results are stored in which table.

i.e equivalent to user_errors as in Oracle.

Any help will be greatly appreciated.

Regards,
SridharTo the best of my knowledge there isn't an exact equivalent to what you are looking for in SQL. Serious errors are (above State 17?) are thrown into SQL's error log (in EM under Management).

You can start a trace file using SQL Profiler or using a stored procedure to log selected events (including errors) to a table or a file (or in the case of SQL Profiler, to the screen).

Regards,

hmscott|||Dear hmscott,

Thanks for the info. When i execute any query or procedure with any syntax error, then the QueryAnalyser gives the error details from "sysmessages" table along with the procedure name, line etc. I wonder how Query Analyser is able to corelate the error messages and executed procedure. This made me curious to know, more about the storage of compilation results.

Regards,
Sridhar

Originally posted by hmscott
To the best of my knowledge there isn't an exact equivalent to what you are looking for in SQL. Serious errors are (above State 17?) are thrown into SQL's error log (in EM under Management).

You can start a trace file using SQL Profiler or using a stored procedure to log selected events (including errors) to a table or a file (or in the case of SQL Profiler, to the screen).

Regards,

hmscott

Wednesday, March 7, 2012

Error using 'Validation Subscription' with SPs replcations

I was runnung 'Validation Subscription' on our replication server and it try
to validate a replicated 'stored --procedure' with the row count and returned
with an error :
exec dbo.sp_table_validation @.table = 'usp_SP1', @.expected_rowcount = 0,
@.rowcount_only = 1, @.full_or_fast = 2, @.shutdown_agent = 0
Transaction sequence number and command ID of last execution batch are
0x00015D47000055A7000400000000 and 2.
Invalid object name 'usp_SP1'.
Invalid object name 'usp_SP1'.
(Source: SQLServer1\DBServer1 (Data source); Error number: 208)
Anyone may have any experience using 'Validation Subscription'? What
should be the proper step in re-syn if it returns descrapancy. I tried to run
the 'Reinitialize subscriptions having data validation failue' and it did
nothing.
Is there a system SP that can validate one Article(table) at a time? and
what whould be the proper procedure is descripancy is found?
Thanks for your help in advance.
You can't validate stored procedures - what this process does is a row count
and optionally a checksum. Stored procedures don't have rows - not even in
SQL 2005

For some people data consistency is very important and they do the row count
followed by a checksum when the row count's don't agree and if there is a
discrepancy, the agent stops so you can manually fix the problems. For
others this isn't as important and they don't have the agents stop if there
are problems. Note that if the tables are very large some people prefer the
fast rowcount option.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Fas" <Fas@.discussions.microsoft.com> wrote in message
news:05E781A8-0706-4D8A-8911-34997B97D0E3@.microsoft.com...
>I was runnung 'Validation Subscription' on our replication server and it
>try
> to validate a replicated 'stored --procedure' with the row count and
> returned
> with an error :
> --
> exec dbo.sp_table_validation @.table = 'usp_SP1', @.expected_rowcount = 0,
> @.rowcount_only = 1, @.full_or_fast = 2, @.shutdown_agent = 0
> Transaction sequence number and command ID of last execution batch are
> 0x00015D47000055A7000400000000 and 2.
> Invalid object name 'usp_SP1'.
> Invalid object name 'usp_SP1'.
> (Source: SQLServer1\DBServer1 (Data source); Error number: 208)
> --
> Anyone may have any experience using 'Validation Subscription'? What
> should be the proper step in re-syn if it returns descrapancy. I tried to
> run
> the 'Reinitialize subscriptions having data validation failue' and it did
> nothing.
> Is there a system SP that can validate one Article(table) at a time? and
> what whould be the proper procedure is descripancy is found?
> Thanks for your help in advance.
>
|||Hilary,
Sorry for the confusion, I was not expected to validate SPs. I just select
Validate subscription from REplication Monitor. And it started the
validation, apparently including the SPs, which I was defined as a
replication article in the publisher. Looks like treated the SP same as
table when validating. Is there ways that I can specify just 1 table to be
validated?
Thanks for your quick response!

Error using scalar valued function in Stored Procedure

Hi,

I am getting the following error msg while executing a stored procedure (getAllCollections) which uses a scalar valued function.

error msg:

Could not find server 'Select dbo' in sysservers. Execute sp_addlinkedserver to add the server to sysservers.

Stored procedure:

ALTER PROCEDURE [dbo].[getAllCollections]

(

@.pCids varchar(7500)

)

declare @.cmd varchar(8000)

set @.cmd='Select dbo.getCollectionList(c.collection_id) as cList from CollectionT c WHERE c.collection_id in (' +@.pCids + ')'

print @.cmd

exec @.cmd

Where as the 'dbo.getCollectionList' is the scalar valued function. If i execute the query separately as below it works fine:

Select dbo.getCollectionList(c.collection_id) as cList from CollectionT c WHERE c.collection_id in (1234)

I believe i am doing some silly mistake somewhere. Any help will be much appreciated.

Thanks.

Saran:

I think all you need to do is change this:

exec @.cmd

to this:

exec ( @.cmd )

Dave

|||

When you do exec @.cmd it takes the contents of @.cmd as a stored procedure name. Since you had 3 periods in your string, it was thinking 'SELECT dbo' was a server name, and 'collection_id) as cList from CollectionT c WHERE c' as a database.

Like Dave said, use exec (@.cmd)

|||

You should replace the dynamic SQL with a safer solution. You can use a TSQL table-valued function to split the list of IDs and use it like:

Select dbo.getCollectionList(c.collection_id) as cList from CollectionT c

WHERE exists(

select * from split_str as s

where s.value = c.collection_id

)

Please see the link below for some solutions that show how to split a string into a table or list of values.

http://www.sommarskog.se/arrays-in-sql.html

|||

Thanks a lot. It solved the issue. Silly me. :)

- Saran.

Error using more than one dataset

Hi,

I have a problem using more than one dataset to create my report. I wish to use data from one stored procedure to create the values for a combobox, that I use as input to another stored procedure.

When I create more than one dataset and set up my report parameters to use one of them to create comboboxes, I get the following error, when I want to see the preview:

An error occured during local report processing.

The definition of the report '/StockInTrade' is invalid.

The data set name is missing in the data region 'DataSetName'

The report worked just fine when I used hardcodede values for my combobox. The error occurs as soon as I define more than one dataset (meaning it fails even before I try to use the second dataset as report parameter values).

Does anyone know what I am doing wrong?

-- Heidi :-)

The error occured because I had a table without a dataset attached!

Friday, February 24, 2012

Error trying to Insert into Table -

Hello...
I have a stored procedure that is failing to insert into a table. When I
manually run the INSERT statement in SQL QA, I receive the following error
message:
"Server: Msg 208, Level 16, State 1, Procedure
ins_C5257449520A4EBDBDF3BBA9F8C9F40E, Line 29
Invalid object name 'tsvw_C5257449520A4EBDBDF3BBA9F8C9F40E'."
I ran a query against the SysObjects table and there is not a record of the
"tsvw.." object..but there is one for the "ins_C525744..." referenced in the
first part of the error message.
This seems to be related to our replication setup...but I am unsure how to
resolve or actually find the problem.
I looked at the Stored Procedures list in the database and I can't find one
named ins_C5257449520A4EBDBDF3BBA9F8C9F40E.
If anyone has any advice or suggestions I'd appreciate it.
thanks
- dw
Is this table published for merge replication or a subscriber to a merge
publication?
If so, your replication deployment has not been successful. I would advise
you to drop your subscription and recreate it.
If not, run this script to scrub your subscription database:
http://groups-beta.google.com/group/...a?dmode=source
"dw" <dw@.discussions.microsoft.com> wrote in message
news:81190FB9-1651-4EAE-AFF8-0243837A1D63@.microsoft.com...
> Hello...
> I have a stored procedure that is failing to insert into a table. When I
> manually run the INSERT statement in SQL QA, I receive the following error
> message:
> "Server: Msg 208, Level 16, State 1, Procedure
> ins_C5257449520A4EBDBDF3BBA9F8C9F40E, Line 29
> Invalid object name 'tsvw_C5257449520A4EBDBDF3BBA9F8C9F40E'."
> I ran a query against the SysObjects table and there is not a record of
the
> "tsvw.." object..but there is one for the "ins_C525744..." referenced in
the
> first part of the error message.
> This seems to be related to our replication setup...but I am unsure how to
> resolve or actually find the problem.
> I looked at the Stored Procedures list in the database and I can't find
one
> named ins_C5257449520A4EBDBDF3BBA9F8C9F40E.
> If anyone has any advice or suggestions I'd appreciate it.
> thanks
> - dw
|||Actually, it isn't a table...as it turns out...it is a reference to an
Insert Trigger on a table that is part of a Merge Replication.
I discovered this last night...there were two Insert Triggers on this table
(which is part of a merge publication). I deleted the one that was causing
the error and now everything works (even my replication/subscriptions) again.
- dw
"Hilary Cotter" wrote:

> Is this table published for merge replication or a subscriber to a merge
> publication?
> If so, your replication deployment has not been successful. I would advise
> you to drop your subscription and recreate it.
> If not, run this script to scrub your subscription database:
> http://groups-beta.google.com/group/...a?dmode=source
> "dw" <dw@.discussions.microsoft.com> wrote in message
> news:81190FB9-1651-4EAE-AFF8-0243837A1D63@.microsoft.com...
> the
> the
> one
>
>

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.

Thursday, March 29, 2012

Error when using SP in data flow

Error when using a stored procedure in OLE DB source

Tuesday, March 27, 2012

Error when running sp_helpdb query

Monday, March 26, 2012

Error when RowNumber is 0

Error when RowNumber is 0

Friday, March 23, 2012

Error when issuing BACKUP LOG mydb with truncate_only

Error when issuing BACKUP LOG mydb with truncate_only

Error when issuing BACKUP LOG mydb with truncate_only

Error when issuing BACKUP LOG mydb with truncate_only

Error when inserting datetime in german SQL Server 2000

Wednesday, March 21, 2012

Error when file is saved to PDF

Error when file is saved to PDF

Monday, March 19, 2012

Error when creating Stored Procedure

Monday, March 12, 2012

Error when calling stored procedure

Incorrect syntax near 'InitProject'.

Friday, March 9, 2012

Error views

Wednesday, March 7, 2012

Error using 'Validation Subscription' with SPs replcations

Error using scalar valued function in Stored Procedure

Error using more than one dataset

Friday, February 24, 2012

Error trying to Insert into Table -

Error traping in DTS

Error running report

Blog Archive

About Me