Error running report: stored

Showing posts with label stored. 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

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

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.

Error when creating script to copy Stored Procedures

Our DB has around 30 SProcs - I need to move them into a script, so that it can be easily added to another server. The way I'm creating the script is to highlight all the SProcs, then copy - in my notepad screen, I paste, which gives me one script, which includes all the individual creation scripts for the Sprocs.

However, I'm getting an error when I create the script -

Cannot add rows to sysdepends for the current stored procedure because it depends on the missing object 'SP_MySproc'. The stored procedure will still be created.

So - what's a good, and/or easy way to structure the script, so that I can easily find WHERE to put Which SProc Script, in the list?

Hey,

You could follow those steps:

Right click Database in Sql Server Management Studio -> Tasks->Generate Scripts->choose databse ->...->check Stored Procedure->next...

|||

That still gives me the same error - I guess what I'm asking is:

how can I figure out what order the Stored Procedures need to be scripted, so this error doesn't happen?

|||

The generate script option only guarantees that the script can pass compile and the order is determined by design.

If you don't mind you could execute script twice and error should go away in the second time.

Hope this helps.

|||

Hi,

If you are sure that all procedures you need are in your script do not worry about the errors, procedures will be created and will work. The only problem will be if you call procedures from another table which are not scripted or if you script your procedures into another database (with different name) and you used call like exec [Sourcedatabase].[dbo].[Procedurename] so it is possible in this case that your procedures will not work if database [Sourcedatabase] is not visible on your server from inside your destination database so check your script for "long" calls.

If you would like to have procedures in correct order you can write TSQL script which will script procedures for you in correct order, I hear about application which can do it for you and you can do it by hands by ordering your procedures in the script the way that procedure is defined before is called by another procedure.

Thanks

JPazgier

Monday, March 12, 2012

error when changing published article at the subscriber

Using merge replication - the stored procs are published articles.

When trying to change the article at the subscriber (sql express) I get the following errors:

Msg 21531, Level 16, State 1, Procedure sp_MSmerge_alterschemaonly, Line 45
The DDL statement cannot be performed at the Subscriber or Republisher.

Msg 21530, Level 16, State 1, Procedure sp_MSmerge_ddldispatcher, Line 181
The DDL operation failed inside merge DDL replication manipulation.

Msg 3609, Level 16, State 2, Procedure usp_Anniversary_upd, Line 21
The transaction ended in the trigger. The batch has been aborted.

Does this mean it can only be changed at the publisher ?

Bruce

correct. If you want to make schema changes at the subscriber, you need to set @.replicate_ddl to false in sp_addmergepublication.|||

Schema changes cannot be made at the subscriber. They have to come from the Publisher.

Message: correct. If you want to make schema changes at the subscriber, you need to set @.replicate_ddl to false in sp_addmergepublication.

I believe Greg meant "publisher only" instead of "subscriber" in the above statement.

|||

Just to clarify this.....

1. You cannot change it at the subscriber, irrespective of whether @.replicate_ddl is set to 0 or 1 ?

2. If @.replicate_ddl is set to 1, then changing the proc on the publisher will result in the proc being changed at the subscriber next time it is synchronised (if a published article)

Is this correct ?

I ask, because @.replicate_ddl is set to 1, but I get that error mentioned earlier if I try and change it at the subscriber.....

Thanks
Bruce

|||

1. you're correct, i was wrong in my earlier post.

2. correct.

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

Error viewing reports

I have created a report, in SQL RS 2000, using My Windows Credentials.

ie under the datasource tab, I chose the "credentials stored securely in report server" OPTION. There I specified my user NT Credentials.

However, when I view the report, it gives me err

An error has occurred during report processing. (rsProcessingAborted) Get Online Help

Cannot create a connection to data source xxx. (rsErrorOpeningConnection) Get Online Help

Pl advise.

Thanks.

Hello Tarana,

You need to check the box 'Use as Windows credentials when connecting to the data source' in the data source tab. Try that and see if it works.

Jarret

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 trying to copy stored procedures from one db to another

Hello,

The export menu in DTS is a bit confusing for me. I have some stored procedures in one SQL Server and want to copy them to another. The username is the same for both SQL Servers. When I try copying the stored procedure with defaul settings, I get a progress bar (24 percent ccompleted, 62 percent completed...), then when its done it says "failed to copy objects from sql server to sql server". When I double click the error for details it says "User or role '[username]' already exists in the current database". Then I try again, unchecking "use default options" for my export. I go in to alter the options, uncheck "copy database users and database groups" (I've also tried unchecking "copy object-level permissions"), run it, get progress bar (like above), and then get another error that says "There is no such user or group '[username]'".

My user name is correct, I am able to access both Sql Servers with this username, and I should have all of the appropriate permissions.

I think maybe there is a checkbox I'm not unchecking or something stupid like that.

Thanks in advance for any feedback.Wild guess here: the user who owns the sp is not dbo ?|||Wild guess here: the user who owns the sp is not dbo ?

Sorry, your msg is a bit cryptic for me. What are "sp" and "dbo"? My guess on the latter is "database owner". I will check with the administrator to find out if I can get a username with more privileges. It is a shared Sql Server with a web hosting company and there are dozens of databases that I can see in there, but do not have access to, so users and permissions are a little hairy, i'm sure.

Thursday, March 29, 2012

Error when using SP in data flow

Error when using a stored procedure in OLE DB source

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

Monday, March 19, 2012

Error when creating Stored Procedure

Error when creating script to copy Stored Procedures

Monday, March 12, 2012

error when changing published article at the subscriber

Error when calling stored procedure

Incorrect syntax near 'InitProject'.

Friday, March 9, 2012

Error views

Error viewing reports

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 trying to copy stored procedures from one db to another

Error running report

Blog Archive

About Me