Error accessing report: executing

Showing posts with label executing. Show all posts

Thursday, March 29, 2012

Error Executing, SSIS Packages Coppied from another server

Hello All,

I am working on an application used to move packages (SSIS Packages) from one server to another.The packages are saved under MSDB folder.

Case: When the application is running on my system, i try to copy packages created from another server to my Server.I am using package protection level as "Server Storage". When i try to execute the coppied package from my system, it is giving me error.
An oledb error has occured, Error Code 0x80040E4D, An Oledb record is available, Source : Microsoft OLedb Provider for Sql Server", Description : "Login failed for sa".

Case : When the Package Coppier application is running on my system, i try to copy packages created on my server to my another Server.I use the same package protection level as "Server Storage". When i try to execute the copied package on destination server it is working fine without errors.

Please guide me on this issue, as soon as possible

Thanks And Regards
Subin

Make sure all the permissions are properly set on your server.

DId you read this (http://www.microsoft.com/technet/prodtechnol/sql/2005/mgngssis.mspx)

ServerStorage. No encryption is added to the package. Instead the package’s contents are secured based on the database’s object access security. If the ServerStorage value is used, the package must be saved to SQL Server in the sysdtspackages90 table in the msdb database. It cannot be saved to the file system.

The SSIS package security model is also extensible. There is a Sensitive attribute in the XML schema of an SSIS package that controls access to the package’s properties. For example, if the Sensitive attribute is set to 1, when the user opens the package all sensitive data will be removed. Developers can incorporate this Sensitive attribute to get the same type of protection for their SSIS custom components.

If you are using the ServerStorage ProtectionLevel property, the method of controlling access to the packages saved in the database is by using SQL Server Database roles. By default, SQL Server 2005 provides the following roles for SSIS package management . You can find these by opening SQL Server Management Studio, then expanding the msdb database, Roles, Database Roles node:

db_dtsadmin. SSIS package administrator rights.

db_dtsltduser. Rights to execute only the SSIS packages the user has been given permission for.

db_dtsoperator. Operation rights to SSIS packages including the ability to run as well as backup and restore packages.

You can also create your own custom database roles for SSIS Package management. Add the appropriate users to those roles and then assign those roles to your own SSIS packages.

You can enable package roles using the SQL Server Management Studio by right-clicking a saved package and then selecting the Package Roles option on the shortcut menu as is shown in Figure 11

BTW, is there any reason for not using SSMS to import/export the packages (http://www.microsoft.com/technet/prodtechnol/sql/2005/mgngssis.mspx)

Error executing xp_cmdshell from within a SQL task in a DTS package...

Hello

I am trying to execute xp_cmdshell' from within a DTS package that
was created by another person. When I try to execute that SQL Task'
selectively from within the package, I get the following error
message:

Error Title: Package Error
Error Details:
Error Source: Microsoft OLE DB Provider for SQL Server
Error Description: xpsql.cpp: Error 87 from GetProxyAccount on line
604

Why is this error message popping up? When I create a new package
(myself) and create an exact same SQL task as above and run it, the
SQL task runs fine.

Appreciate any help / feedback.

Thanks in Advance
Jagannathan SanthanamWhen a sysadmin role member executes xp_cmdshell, it runs under the
security context of the SQL Server service account. However, when a
non-sysadmin user executes the proc, it runs under the SQL Agent proxy
account.

It looks like there is an issue with your configuration of the proxy
account and/or SQL Server service account security. Enterprise Manager
automatically assigns the necessary rights to these accounts when these
are specified or changed via the GUI. Alternatively, you can manually
assign the following Windows rights to the accounts.

<Excerpt
href="http://support.microsoft.com/default.aspx?scid=kb;en-us;264155"
MSSQLServer and SQLServerAgent Services
- Act as part of the Operating System.
- Increase Quotas.
- Replace a process level token.
- Log on as a batch job.
SQLAgentCmdExec Account
- Log on as a batch job.

NOTE: You must restart the entire server, not just the SQL Services, in
order for any changes made to user rights permissions to take effect.

</Excerpt
--
Hope this helps.

Dan Guzman
SQL Server MVP

--------
SQL FAQ links (courtesy Neil Pike):

http://www.ntfaq.com/Articles/Index...epartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--------

"Jagannathan Santhanam" <jags_32@.yahoo.com> wrote in message
news:605df08e.0311170307.39d9b1f1@.posting.google.c om...
> Hello
> I am trying to execute 'xp_cmdshell' from within a DTS package that
> was created by another person. When I try to execute that 'SQL Task'
> selectively from within the package, I get the following error
> message:
> Error Title: Package Error
> Error Details:
> Error Source: Microsoft OLE DB Provider for SQL Server
> Error Description: xpsql.cpp: Error 87 from GetProxyAccount on line
> 604
>
> Why is this error message popping up? When I create a new package
> (myself) and create an exact same SQL task as above and run it, the
> SQL task runs fine.
> Appreciate any help / feedback.
> Thanks in Advance
> Jagannathan Santhanam

ERROR EXECUTING XMLA WHILE IMPLEMENTING PROJECT REAL BI

Hi all,

I didnot find any other forum to post this problem. I am facing an problem while trying to implement the PROJECT REAL BI.

I was trying to implement the PROJECT REAL but facing an error. After attaching the mdf : REAL_Source_Sample_V6 and REAL_Warehouse_Sample_V6 when I try to execute the XMLA Script “REAL Warehouse Sample V6 MT.xmla” i am getting an error "Object Reference not set to an instance of object ( ConnectionDlg)". Can you tell me why this error comes. I am using SQL SERVER 2005 SP 2. You can ping me at girija45701@.yahoo.com...

Thanks in advance...

In Sql Server Management Studio

01. Select tools --> Options

02. Select File Extension under text editor on the left side panel.

03. In the right side panel Add XMLA in the Extension box and select XMLA Query Editor from the drop down list.

04. Click on the 'Add' button.

05. Close and restart Sql Server Management Studio.

By mapping the extension to an editor, the SSMS will know which Engine/parser to use.

regards.

Error executing User Defined function on another server

Hi All,
Facing a problem while executing a user defined function (UDF) which resides
on another sql server ( we have access on this sql server to execute the
UDF). This UDF accepts a varchar parameter with max length 8. When we
execute the function as
Select * from
SERVERNAME.DBName.dbo. fn_GetConsulteesDataForReport('00118179'
) it throws an
error as given below.
Server: Msg 170, Level 15, State 31, Line 1
Line 2: Incorrect syntax near '('.
Please let me know what is the issue in my SQL statement and how we can
resolve this.
Thanks in advance.
SuhasThere's a workaround for this hm... feature. Use the OPENQUERY function:
select *
from openquery(SERVERNAME, '
select *
from
DBName.dbo. fn_GetConsulteesDataForReport('00118179'
)
')
ML
http://milambda.blogspot.com/|||Hi ML,
Thank you for giving this suggestion. But please let me know whether open
query does accept variable as an input to the function ? I mean, can I
execute the same as given below.
declare @.empid varchar(8)
select @.empid = '00118179'
select *
from openquery(SERVERNAME, '
select *
from
DBName.dbo.fn_GetConsulteesDataForReport(@.empid)
')
Becuase we need to dynamically change this employee ID.
Thank you.
Suhas
"ML" wrote:

> There's a workaround for this hm... feature. Use the OPENQUERY function:
> select *
> from openquery(SERVERNAME, '
> select *
> from
> DBName.dbo. fn_GetConsulteesDataForReport('00118179'
)
> ')
>
> ML
> --
> http://milambda.blogspot.com/|||For actual production use I'd suggest designing a local UDF, that references
tables in the database on the linked server. This way you can call the
function directly, and still access the data remotely.
ML
http://milambda.blogspot.com/|||Sorry, I forgot to mention, that OPENQUERY does not accept parameters or
variables. Read more here:
http://msdn.microsoft.com/library/d...br />
5xix.asp
ML
http://milambda.blogspot.com/

Error executing SSIS package

Hello,
I'm running SQL Server 2005 Developer edition on Vista Ultimate. I've
created several SSIS packages and run them under proxy account. At first
they worked just fine, but then all of them started to fail with the error
messages like this one:
Error: 2007-03-20 22:59:15.56 Code: 0xC002F304 Source:
NonTransactableSql Execute SQL Task Description: An error occurred with
the following error message: "Could not find file 'I:\Users\Peter
Afonin\AppData\Local\Temp\tmp458F.tmp'.". End Error DTExec: The package
execution returned DTSER_FAILURE (1). Started: 10:59:14 PM Finished:
10:59:15 PM Elapsed: 1.014 seconds. The package execution failed. The
step failed.
If the cause is really in this tmp file, it doesn't make any sense. Yes, I'm
deleting my tmp files periodically, so I don't have these files. It's hard
to believe that SQL Server relies on tmp files that are often deleted. Plus,
I was running the same packages on Windows XP for over a year and never had
problems like this.
What else could it be and how to fix it? Is it a security issue?
I would appreciate your suggestions very much.
Thank you,
--
Peter AfoninHi Peter
"Peter Afonin" wrote:
> Hello,
> I'm running SQL Server 2005 Developer edition on Vista Ultimate. I've
> created several SSIS packages and run them under proxy account. At first
> they worked just fine, but then all of them started to fail with the error
> messages like this one:
> Error: 2007-03-20 22:59:15.56 Code: 0xC002F304 Source:
> NonTransactableSql Execute SQL Task Description: An error occurred with
> the following error message: "Could not find file 'I:\Users\Peter
> Afonin\AppData\Local\Temp\tmp458F.tmp'.". End Error DTExec: The package
> execution returned DTSER_FAILURE (1). Started: 10:59:14 PM Finished:
> 10:59:15 PM Elapsed: 1.014 seconds. The package execution failed. The
> step failed.
> If the cause is really in this tmp file, it doesn't make any sense. Yes, I'm
> deleting my tmp files periodically, so I don't have these files. It's hard
> to believe that SQL Server relies on tmp files that are often deleted. Plus,
> I was running the same packages on Windows XP for over a year and never had
> problems like this.
> What else could it be and how to fix it? Is it a security issue?
> I would appreciate your suggestions very much.
> Thank you,
> --
> Peter Afonin
>
I would suspect that the issue is probably with 'I:\Users\Peter
Afonin\AppData\Local\Temp' not existing where is your temp directory set to?
John|||Hi John,
No, this directory is real. This is a default directory in Vista (I have it
on partition I).
Thank you,
Peter
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:6899EAEE-6306-4D47-913B-BF503C445D66@.microsoft.com...
> Hi Peter
> "Peter Afonin" wrote:
>> Hello,
>> I'm running SQL Server 2005 Developer edition on Vista Ultimate. I've
>> created several SSIS packages and run them under proxy account. At first
>> they worked just fine, but then all of them started to fail with the
>> error
>> messages like this one:
>> Error: 2007-03-20 22:59:15.56 Code: 0xC002F304 Source:
>> NonTransactableSql Execute SQL Task Description: An error occurred
>> with
>> the following error message: "Could not find file 'I:\Users\Peter
>> Afonin\AppData\Local\Temp\tmp458F.tmp'.". End Error DTExec: The package
>> execution returned DTSER_FAILURE (1). Started: 10:59:14 PM Finished:
>> 10:59:15 PM Elapsed: 1.014 seconds. The package execution failed. The
>> step failed.
>> If the cause is really in this tmp file, it doesn't make any sense. Yes,
>> I'm
>> deleting my tmp files periodically, so I don't have these files. It's
>> hard
>> to believe that SQL Server relies on tmp files that are often deleted.
>> Plus,
>> I was running the same packages on Windows XP for over a year and never
>> had
>> problems like this.
>> What else could it be and how to fix it? Is it a security issue?
>> I would appreciate your suggestions very much.
>> Thank you,
>> --
>> Peter Afonin
> I would suspect that the issue is probably with 'I:\Users\Peter
> Afonin\AppData\Local\Temp' not existing where is your temp directory set
> to?
> John|||John, I can confirm that SQL Server is indeed looking for these tmp files.
1. I've recreated my packages, they worked fine.
2. I removed all files from I:\Users\Peter Afonin\AppData\Local\Temp folder.
The packages stopped working, giving me the error I mentioned before.
3. I put these tmp files back to this folder, the packages work fine again.
This is something that I've never heard before and that doesn't make any
sense. Why this never happened on Windows XP, only Vista? What should I do -
it looks like I cannot cleanup my computer anymore?
As a last resort - can I change the folder for tmp files that are needed for
SSIS packages execution, so I could exclude it somehow from the disk
cleanup?
Any advice would be greatly appreciated.
Thank you,
Peter
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:6899EAEE-6306-4D47-913B-BF503C445D66@.microsoft.com...
> Hi Peter
> "Peter Afonin" wrote:
>> Hello,
>> I'm running SQL Server 2005 Developer edition on Vista Ultimate. I've
>> created several SSIS packages and run them under proxy account. At first
>> they worked just fine, but then all of them started to fail with the
>> error
>> messages like this one:
>> Error: 2007-03-20 22:59:15.56 Code: 0xC002F304 Source:
>> NonTransactableSql Execute SQL Task Description: An error occurred
>> with
>> the following error message: "Could not find file 'I:\Users\Peter
>> Afonin\AppData\Local\Temp\tmp458F.tmp'.". End Error DTExec: The package
>> execution returned DTSER_FAILURE (1). Started: 10:59:14 PM Finished:
>> 10:59:15 PM Elapsed: 1.014 seconds. The package execution failed. The
>> step failed.
>> If the cause is really in this tmp file, it doesn't make any sense. Yes,
>> I'm
>> deleting my tmp files periodically, so I don't have these files. It's
>> hard
>> to believe that SQL Server relies on tmp files that are often deleted.
>> Plus,
>> I was running the same packages on Windows XP for over a year and never
>> had
>> problems like this.
>> What else could it be and how to fix it? Is it a security issue?
>> I would appreciate your suggestions very much.
>> Thank you,
>> --
>> Peter Afonin
> I would suspect that the issue is probably with 'I:\Users\Peter
> Afonin\AppData\Local\Temp' not existing where is your temp directory set
> to?
> John|||Hi Peter
"Peter Afonin" wrote:
> John, I can confirm that SQL Server is indeed looking for these tmp files.
> 1. I've recreated my packages, they worked fine.
> 2. I removed all files from I:\Users\Peter Afonin\AppData\Local\Temp folder.
> The packages stopped working, giving me the error I mentioned before.
> 3. I put these tmp files back to this folder, the packages work fine again.
> This is something that I've never heard before and that doesn't make any
> sense. Why this never happened on Windows XP, only Vista? What should I do -
> it looks like I cannot cleanup my computer anymore?
> As a last resort - can I change the folder for tmp files that are needed for
> SSIS packages execution, so I could exclude it somehow from the disk
> cleanup?
> Any advice would be greatly appreciated.
> Thank you,
> Peter
>
SSIS may use files in a users temporary directory, if they have been
automatically generated or converted. You will need to check the datasources
and the package steps to see what they are. If necessary they can be moved to
a "common" directory that has the correct permissions for all users that will
run the package.
You may have found that removing the equivalent files in windows XP would
have had a similar result.
John|||Thank you, John, I'll edit my packages and see what's going on.
Peter
On Mar 22, 1:44 am, John Bell <jbellnewspo...@.hotmail.com> wrote:
> HiPeter
>
>
> "PeterAfonin" wrote:
> > John, I can confirm that SQL Server is indeed looking for these tmp files.
> > 1. I've recreated my packages, they worked fine.
> > 2. I removed all files from I:\Users\PeterAfonin\AppData\Local\Temp folder.
> > The packages stopped working, giving me the error I mentioned before.
> > 3. I put these tmp files back to this folder, the packages work fine again.
> > This is something that I've never heard before and that doesn't make any
> > sense. Why this never happened on Windows XP, only Vista? What should I do -
> > it looks like I cannot cleanup my computer anymore?
> > As a last resort - can I change the folder for tmp files that are needed for
> > SSIS packages execution, so I could exclude it somehow from the disk
> > cleanup?
> > Any advice would be greatly appreciated.
> > Thank you,
> >Peter
> SSIS may use files in a users temporary directory, if they have been
> automatically generated or converted. You will need to check the datasources
> and the package steps to see what they are. If necessary they can be moved to
> a "common" directory that has the correct permissions for all users that will
> run the package.
> You may have found that removing the equivalent files in windows XP would
> have had a similar result.
> John- Hide quoted text -
> - Show quoted text -

Error executing SQL Script from VB:NET

Hi.

I have a complete script from a Database.

But when I try to execute this script (script.txt) from VB.NET shows me many errors. Basically, the special caracters (vbreturn, vbtab, etc.)

How execute this script or format the text to generate the database without problems?

Thanks 4all

IF it is a SQL script, you need a SQL interpreter to run the script.

You would use either OSQL.exe or SQLCmd.exe. (Refer to Books Online for complete syntax details.)

OSQL -S servername -U username -P password -i Scriptfile

Similar command line parameters for SQLCmd.exe

|||You could also tell us what the errors are, what VB object you are trying to use, etc. There are lots of different reasons why this won't work, but I am sure that it could be made to work. Using sqlcmd, osql, or isql would be the easiest way to go, (it changes from one version of SQL Server to the next for the past few versions

but SQLCMD is just a .NET program running your batches, so we can make it happen if you will describe more about your issues.

error executing sprocedure

Hello,
I've created one stored procedure that has one input=20
parameter(number of days), if i don=B4t pass any value when=20
executing this procedure sql reports one error.=20
I would like to execute this procedure and don't pass the=20
input paramenter to the stored procedure but if the number=20
of days is 0(zero)
Is this possible?
Thanks.
Best regards
You can define a default value for the parameter, in which case you don't
have to pass it:
CREATE PROCEDURE proc_name
@.number_of_days INT = 0
AS
....
Jacco Schalkwijk
SQL Server MVP
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:8edb01c47878$887871e0$a501280a@.phx.gbl...
Hello,
I've created one stored procedure that has one input
parameter(number of days), if i dont pass any value when
executing this procedure sql reports one error.
I would like to execute this procedure and don't pass the
input paramenter to the stored procedure but if the number
of days is 0(zero)
Is this possible?
Thanks.
Best regards
|||For more details please refer to the BOL
CREATE PROC myp
@.var INT =0
AS
DECLARE @.id INT
SET @.id=(SELECT * FROM Table WHERE col=@.var)
IF @.id =0
RAISERROR ("Error",16,1)
RETURN 99
or
CREATE PROC myp
@.var INT =0
AS
IF@.var >0
SELECT * FROM Table WHERE col=@.var
ELSE
Raiserror ("Error,"16,1)
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:8edb01c47878$887871e0$a501280a@.phx.gbl...
Hello,
I've created one stored procedure that has one input
parameter(number of days), if i dont pass any value when
executing this procedure sql reports one error.
I would like to execute this procedure and don't pass the
input paramenter to the stored procedure but if the number
of days is 0(zero)
Is this possible?
Thanks.
Best regards
|||Thanks Jacco.
Best regards
>--Original Message--
>You can define a default value for the parameter, in=20
which case you don't=20
>have to pass it:
>CREATE PROCEDURE proc_name
>@.number_of_days INT =3D 0
>AS
>....
>--=20
>Jacco Schalkwijk
>SQL Server MVP
>
>"CC&JM" <anonymous@.discussions.microsoft.com> wrote in=20
message=20
>news:8edb01c47878$887871e0$a501280a@.phx.gbl...
>Hello,
>I've created one stored procedure that has one input
>parameter(number of days), if i don=B4t pass any value when
>executing this procedure sql reports one error.
>I would like to execute this procedure and don't pass the
>input paramenter to the stored procedure but if the number
>of days is 0(zero)
>Is this possible?
>Thanks.
>Best regards=20
>
>.
>
|||In addition to Jacco's comments, what he and others showed you is an
optional parameter... To make a parameter optional you must give it a
default value. Then when no value is supplied by the caller the default
value is used.
You may use default values with OUTPUT parameters. And you may have some,
all or none of your parameters with default values.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:8edb01c47878$887871e0$a501280a@.phx.gbl...
Hello,
I've created one stored procedure that has one input
parameter(number of days), if i dont pass any value when
executing this procedure sql reports one error.
I would like to execute this procedure and don't pass the
input paramenter to the stored procedure but if the number
of days is 0(zero)
Is this possible?
Thanks.
Best regards

error executing sprocedure

Hello,
I've created one stored procedure that has one input parameter(number of days), if i don=B4t pass any value when executing this procedure sql reports one error. I would like to execute this procedure and don't pass the input paramenter to the stored procedure but if the number of days is 0(zero)
Is this possible?
Thanks.
Best regardsYou can define a default value for the parameter, in which case you don't
have to pass it:
CREATE PROCEDURE proc_name
@.number_of_days INT = 0
AS
...
--
Jacco Schalkwijk
SQL Server MVP
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:8edb01c47878$887871e0$a501280a@.phx.gbl...
Hello,
I've created one stored procedure that has one input
parameter(number of days), if i don´t pass any value when
executing this procedure sql reports one error.
I would like to execute this procedure and don't pass the
input paramenter to the stored procedure but if the number
of days is 0(zero)
Is this possible?
Thanks.
Best regards|||For more details please refer to the BOL
CREATE PROC myp
@.var INT =0
AS
DECLARE @.id INT
SET @.id=(SELECT * FROM Table WHERE col=@.var)
IF @.id =0
RAISERROR ("Error",16,1)
RETURN 99
--
or
CREATE PROC myp
@.var INT =0
AS
IF@.var >0
SELECT * FROM Table WHERE col=@.var
ELSE
Raiserror ("Error,"16,1)
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:8edb01c47878$887871e0$a501280a@.phx.gbl...
Hello,
I've created one stored procedure that has one input
parameter(number of days), if i don´t pass any value when
executing this procedure sql reports one error.
I would like to execute this procedure and don't pass the
input paramenter to the stored procedure but if the number
of days is 0(zero)
Is this possible?
Thanks.
Best regards|||Thanks Jacco.
Best regards
>--Original Message--
>You can define a default value for the parameter, in which case you don't >have to pass it:
>CREATE PROCEDURE proc_name
>@.number_of_days INT =3D 0
>AS
>....
>-- >Jacco Schalkwijk
>SQL Server MVP
>
>"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message >news:8edb01c47878$887871e0$a501280a@.phx.gbl...
>Hello,
>I've created one stored procedure that has one input
>parameter(number of days), if i don=B4t pass any value when
>executing this procedure sql reports one error.
>I would like to execute this procedure and don't pass the
>input paramenter to the stored procedure but if the number
>of days is 0(zero)
>Is this possible?
>Thanks.
>Best regards >
>.
>|||In addition to Jacco's comments, what he and others showed you is an
optional parameter... To make a parameter optional you must give it a
default value. Then when no value is supplied by the caller the default
value is used.
You may use default values with OUTPUT parameters. And you may have some,
all or none of your parameters with default values.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:8edb01c47878$887871e0$a501280a@.phx.gbl...
Hello,
I've created one stored procedure that has one input
parameter(number of days), if i don´t pass any value when
executing this procedure sql reports one error.
I would like to execute this procedure and don't pass the
input paramenter to the stored procedure but if the number
of days is 0(zero)
Is this possible?
Thanks.
Best regardssql

error executing sprocedure

Hello,
I've created one stored procedure that has one input=20
parameter(number of days), if i don=B4t pass any value when=20
executing this procedure sql reports one error.=20
I would like to execute this procedure and don't pass the=20
input paramenter to the stored procedure but if the number=20
of days is 0(zero)
Is this possible?
Thanks.
Best regardsYou can define a default value for the parameter, in which case you don't
have to pass it:
CREATE PROCEDURE proc_name
@.number_of_days INT = 0
AS
...
Jacco Schalkwijk
SQL Server MVP
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:8edb01c47878$887871e0$a501280a@.phx.gbl...
Hello,
I've created one stored procedure that has one input
parameter(number of days), if i dont pass any value when
executing this procedure sql reports one error.
I would like to execute this procedure and don't pass the
input paramenter to the stored procedure but if the number
of days is 0(zero)
Is this possible?
Thanks.
Best regards|||For more details please refer to the BOL
CREATE PROC myp
@.var INT =0
AS
DECLARE @.id INT
SET @.id=(SELECT * FROM Table WHERE col=@.var)
IF @.id =0
RAISERROR ("Error",16,1)
RETURN 99
or
CREATE PROC myp
@.var INT =0
AS
IF@.var >0
SELECT * FROM Table WHERE col=@.var
ELSE
Raiserror ("Error,"16,1)
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:8edb01c47878$887871e0$a501280a@.phx.gbl...
Hello,
I've created one stored procedure that has one input
parameter(number of days), if i dont pass any value when
executing this procedure sql reports one error.
I would like to execute this procedure and don't pass the
input paramenter to the stored procedure but if the number
of days is 0(zero)
Is this possible?
Thanks.
Best regards|||Thanks Jacco.
Best regards
>--Original Message--
>You can define a default value for the parameter, in=20
which case you don't=20
>have to pass it:
>CREATE PROCEDURE proc_name
>@.number_of_days INT =3D 0
>AS
>....
>--=20
>Jacco Schalkwijk
>SQL Server MVP
>
>"CC&JM" <anonymous@.discussions.microsoft.com> wrote in=20
message=20
>news:8edb01c47878$887871e0$a501280a@.phx.gbl...
>Hello,
>I've created one stored procedure that has one input
>parameter(number of days), if i don=B4t pass any value when
>executing this procedure sql reports one error.
>I would like to execute this procedure and don't pass the
>input paramenter to the stored procedure but if the number
>of days is 0(zero)
>Is this possible?
>Thanks.
>Best regards=20
>
>.
>|||In addition to Jacco's comments, what he and others showed you is an
optional parameter... To make a parameter optional you must give it a
default value. Then when no value is supplied by the caller the default
value is used.
You may use default values with OUTPUT parameters. And you may have some,
all or none of your parameters with default values.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:8edb01c47878$887871e0$a501280a@.phx.gbl...
Hello,
I've created one stored procedure that has one input
parameter(number of days), if i dont pass any value when
executing this procedure sql reports one error.
I would like to execute this procedure and don't pass the
input paramenter to the stored procedure but if the number
of days is 0(zero)
Is this possible?
Thanks.
Best regards

Error executing sp_change_users_login

Hello -
Hello, i'm trying to run
Exec sp_change_Users_login 'report'
From a few databases on my servers. When I try to execute,=20
i'm receiving the following error message:
=AB Server: Msg 15289, Level 16, State 1, Procedure=20
sp_change_users_login, Line 27
Terminating this procedure. Cannot have an open=20
transaction when this is
run.=BB
Here's the code i'm trying to execute:
=AB=20
if exists (select * from dbo.sysobjects where id =3D=20
object_id(N'[dbo].[OutputOrphanUsers]') and OBJECTPROPERTY
(id, N'IsUserTable') =3D 1)
drop table [dbo].[OutputOrphanUsers]
go
create table OutputOrphanUsers=20
(uname varchar(30),
usid varbinary(85))
go
insert OutputOrphanUsers EXEC=20
sp_change_users_login 'Report'=BB
I hope that you can help me, i cant found any workarround=20
to this situation.
Best regerdsI'm not sure there is a workaround... I tried hiding the query in openrowset
and that didn't work either... ie
insert OutputOrphanUsers select a.* FROM
OPENROWSET('SQLOLEDB','servername';'logi
n';'password',
'exec sp_change_users_login report') AS a
Actually what you could do is get the source... in master sp_helptest
sp_change_users_login and change the code that requires no transaction (but
only if the parameter is report. Then re-add it as a different name. The
common naming convention would be sp__change_users_login (2 underscores
after sp).
That will work.
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:153ed01c4157e$e99d76d0$a101280a@.phx
.gbl...
Hello -
Hello, i'm trying to run
Exec sp_change_Users_login 'report'
From a few databases on my servers. When I try to execute,
i'm receiving the following error message:
Server: Msg 15289, Level 16, State 1, Procedure
sp_change_users_login, Line 27
Terminating this procedure. Cannot have an open
transaction when this is
run.
Here's the code i'm trying to execute:
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[OutputOrphanUsers]') and OBJECTPROPERTY
(id, N'IsUserTable') = 1)
drop table [dbo].[OutputOrphanUsers]
go
create table OutputOrphanUsers
(uname varchar(30),
usid varbinary(85))
go
insert OutputOrphanUsers EXEC
sp_change_users_login 'Report'
I hope that you can help me, i cant found any workarround
to this situation.
Best regerds

error executing query

I have a database running on MSDE it was working fine. Recently when I try t
o select from the database I am getting the following error after a few reco
rds have been returned. All of the tables are giving this problem. Not sure
what to do.
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionCheckForD
ata (CheckforData()).
[Microsoft][ODBC SQL Server Driver][DBNETLIB]General network err
or. Check your network documentation.
Connection Broken"Warren" <anonymous@.discussions.microsoft.com> wrote in message
news:E77B88A4-C231-4C30-B2D2-C4282A43A32F@.microsoft.com...
> I have a database running on MSDE it was working fine. Recently when I try
to select from the database I am getting the following error after a few
records have been returned. All of the tables are giving this problem. Not
sure what to do.
> [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionCheckForData[/c
olor]
(CheckforData()).
> [Microsoft][ODBC SQL Server Driver][DBNETLIB]General network error. Ch
eck
your network documentation.
> Connection Broken
>
It could be several things... first a question: were any changes made or
different software installed on the computer running MSDE?
If not, databases like MSDE and SQL Server can expose network issues such as
packet drops or interruptions. Also, rule out name resolution problems like
DNS or WINS.
Another angle, consider installing the latest version of MDAC on the client
computer and enabling both SQL Server utilities TCP/IP and Named Pipes.
Steve

error executing query

I have a database running on MSDE it was working fine. Recently when I try to select from the database I am getting the following error after a few records have been returned. All of the tables are giving this problem. Not sure what to do.
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionCheckForData (CheckforData()).
[Microsoft][ODBC SQL Server Driver][DBNETLIB]General network error. Check your network documentation.
Connection Broken
"Warren" <anonymous@.discussions.microsoft.com> wrote in message
news:E77B88A4-C231-4C30-B2D2-C4282A43A32F@.microsoft.com...
> I have a database running on MSDE it was working fine. Recently when I try
to select from the database I am getting the following error after a few
records have been returned. All of the tables are giving this problem. Not
sure what to do.
> [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionCheckForData
(CheckforData()).
> [Microsoft][ODBC SQL Server Driver][DBNETLIB]General network error. Check
your network documentation.
> Connection Broken
>
It could be several things... first a question: were any changes made or
different software installed on the computer running MSDE?
If not, databases like MSDE and SQL Server can expose network issues such as
packet drops or interruptions. Also, rule out name resolution problems like
DNS or WINS.
Another angle, consider installing the latest version of MDAC on the client
computer and enabling both SQL Server utilities TCP/IP and Named Pipes.
Steve
sql

Tuesday, March 27, 2012

Error Executing Parallel SQL Tasks

I have three SQL tasks executing in parallel in an Integration Services package.

+-B-+
A-+-C-+-E
+-D-+

It starts with task A; then B, C, and D all execute in parallel; and finally task E runs after BCD are done.

B, C, and D are all Execute SQL tasks, all with the same connection manager. Here is their code:

B) SELECT CASE WHEN COUNT(*) = 0 THEN 0 ELSE 1 END AS Process
FROM temp_B

C) SELECT CASE WHEN COUNT(*) = 0 THEN 0 ELSE 1 END AS Process
FROM temp_C

D) SELECT CASE WHEN COUNT(*) = 0 THEN 0 ELSE 1 END AS Process
FROM temp_D

Each one is setting a binary value to a package variable (using Result Set settings) based on the count of records from different tables.

This works with no problems when I run it against one server (development). But when I switch to the production server, task B and D both fail. I'v checked to make sure all of the temp tables exist in the database for that connection manager and that all three have the same connection manager - all is okay.

Here's the trickier part. When I'm still pointing to the production server and I run these tasks individually, they are all successful. It is only when they are attempting to run in parallel that they fail.

Here is the Output error:
Error: 0xC002F210 at Process Med?, Execute SQL Task: Executing the query "SELECT CASE WHEN COUNT(*) = 0 THEN 0 ELSE 1 END AS Process FROM temp_B" failed with the following error: "Invalid object name 'temp_B'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

What could be causing this? I'm at a loss.

Why do they fail? Do you have an error message?

-Jamie

|||Sorry Jamie - I neglected to put in the error message at first. It's now in the original post.|||

perhaps the error message is a red herring. Can you use Profiler to check that everything is OK when the tasks get fired.

That's just an idea for diagnosis. I'm at a a loss as to what the problem might be!

-Jamie

|||

Problem solved.

Sorry about the errant post - it was actually not a parallel issue.

One of the previous steps changed the conenction manager's database setting and was doing it incorrectly. The tasks were hitting a false database. Not sure why task C didn't fail - but it's moot.

Thanks for your help.

Error executing Packaga from job

Hello, I finally could upload the package, and from the management studio interface I ran the package and it worked perfectly.

When I created a job, with one step only to execute that package, the job fails.

When I go to history it doesnt give me any details of what failed on the package or in the job

Date 24/01/2007 12:30:28
Log Job History (Carga datos ACH)

Step ID 1
Server ATLANTE\SQL2005
Job Name Carga datos ACH
Step Name Carga de datos de ach
Duration 00:00:02
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0

Message
Executed as user: ATLANTE\SYSTEM. The package execution failed. The step failed.

Maybe is the user that it tried to execute the package as?

How can I change it?

please delete this post. sorry for repeating|||I can't, but can someone merge some of Luis' threads that all revolve around the same issue?

Thanks,
Phil|||

this article describe solution to most comon issues when executing the package:

http://support.microsoft.com/kb/918760

|||

Hello, when I try to change the package protection level to server storage I got this

Failed to apply package protection with error 0xC0014061 "The protection level, ServerStorage, cannot be used when saving to this destination. The system could not verify that the destination supports secure storage capability.". This error occurs when saving to Xml.

|||Are you storing the packages as XML files or inside the DB? if you are using xmls files you can use DonSaveSensitive and the use package configurations to set the conection strings at run time as described in method 4 in that article.

Error executing non query: Timeout expired

I was having some errors from the webpage accessing the OLlinks table in the database.

Error executing non query: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

StackTrace: at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader()
at admin_admOLEditLinks.selectData(String strID) in e:\wwwroot\home\admin\admOLEditLinks.aspx.cs:line 101
DateTime:5/23/2007 1:14:10 PM
Source:http://www.myDomain.comiAdmin/admOLEditLinks.aspx?ID=3
ErrorMessage:Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

I kept getting the above error so then I try to access the table directly inside of MS SQL Server Management Studio and then I recieved the following error:

SQL Execution Error.

Executed SQL statement: SELECT lnkID, linkFromID, linkToID FROM OLlinks

Error Source: .Net SqlClient Data Provider

Error Message: Timeout expired. The timeout period elapsed prior to completion of the opration or the server is not responding.

Open any other table works fine except this table only. Any help is much appreciated.

Looks like you are returning too many rows to the application? If you run the query in Query Analyzer how many rows does your SELECT statement retrieve?

|||

Okay, here's what I did. In MS SQL Server Management Studio, I click on the New Query icon and pasted in the "SELECT lnkID, linkFromID, linkToID FROM OLlinks" query and the query run fine with only 5 records showing. But if I right click on the OLlinks table in the Tables folder and choose Open Table then I do not see any records showing...and then for a long time I will receive the above error.

This problem does not occur everytime. It happens randomly.

|||

From the description you provided it looks that there is a lock which is there on the table.Might be you are running something in transaction which uses this table. Check in the Activity monitor and see the Lock by process.

|||

I don't think that is the case. I was able to open other tables fine. In addition, this problem only occurs randomly or appears to occur randomly.

And by the way, where is the Activity monitor?

|||

In the sql management studio... under the Management Node... It is.. I was talking about just a possibility and it would be nice to check if it is the case. Big Smile

|||

There is nothing in the Management Node.

|||

Hi,

If it only occurs randomly, I assume that some other operation might be locking the table you're querying.

You can try to increase the timeout value of the command execution. To check if someone else is locking the table, you can use SQL Profiler.

HTH. If this does not answer your question, please feel free to mark the post as Not Answered and reply. Thank you!

Error executing extended stored procedure: Invalid Parameter

When I try to connect to sql server instance I received this error:
TITLE: Microsoft SQL Server Management Studio
--
An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)
--
ADDITIONAL INFORMATION:
Error executing extended stored procedure: Invalid Parameter
Error executing extended stored procedure: Invalid Parameter (Microsoft SQL
Server, Error: 22001)
when i click refresh button i am able to see my schema, but i can't see the
properties of my base and any table properties. I receive error:
TITLE: Microsoft SQL Server Management Studio
--
Cannot show requested dialog.
--
ADDITIONAL INFORMATION:
Cannot show requested dialog. (SqlMgmt)
--
An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)
--
Error executing extended stored procedure: Invalid Parameter
Error executing extended stored procedure: Invalid Parameter (Microsoft SQL
Server, Error: 22001)
--
BUTTONS:
OK
--
also the registry key
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names is
locked and can not be opened.
After a reboot the key is now readable and the 2005 instance can be
connected to without these messages.
But after a while it does it again.
does anyone have any idea what is going wrong?Hi
My guess is that you have some group policy or Anti-virus/ISD system that is
locking down this registry key.
John
"nikolakg" wrote:
> When I try to connect to sql server instance I received this error:
> TITLE: Microsoft SQL Server Management Studio
> --
> An exception occurred while executing a Transact-SQL statement or batch.
> (Microsoft.SqlServer.ConnectionInfo)
> --
> ADDITIONAL INFORMATION:
> Error executing extended stored procedure: Invalid Parameter
> Error executing extended stored procedure: Invalid Parameter (Microsoft SQL
> Server, Error: 22001)
>
> when i click refresh button i am able to see my schema, but i can't see the
> properties of my base and any table properties. I receive error:
> TITLE: Microsoft SQL Server Management Studio
> --
> Cannot show requested dialog.
> --
> ADDITIONAL INFORMATION:
> Cannot show requested dialog. (SqlMgmt)
> --
> An exception occurred while executing a Transact-SQL statement or batch.
> (Microsoft.SqlServer.ConnectionInfo)
> --
> Error executing extended stored procedure: Invalid Parameter
> Error executing extended stored procedure: Invalid Parameter (Microsoft SQL
> Server, Error: 22001)
>
> --
> BUTTONS:
> OK
> --
>
> also the registry key
> HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names is
> locked and can not be opened.
> After a reboot the key is now readable and the 2005 instance can be
> connected to without these messages.
> But after a while it does it again.
> does anyone have any idea what is going wrong?

ERROR EXECUTING EXTENDED STORED PROCEDURE Specified user can not login

Hello All,
I'm wrote a program that uses the extended stored procedure XP_CMDSHELL .
The user is not an admin and I'm not sure how to set up a Proxy account.
I'm getting the following error when I try to add the user into the
Non-SysAdmin job step proxy account.
ERROR EXECUTING EXTENDED STORED PROCEDURE Specified user can not login
I gave the users domain rights to the master database on the server and then
gave permission to use the XP_CMDSHELL procedure.
My knowledge on permissions in SQL SERVER 2000 is hazy and I would like to
know what steps I have to take to add a non-admin user to be able to use the
XP_CMDSHELL procedure. Do I have to set anything outside of SQL SERVER
2000?...
Any help will be greatly appreciated.
Thanks,
Matt
If the user invoking xp_cmdshell is not a member of the sysadmin role, the
proxy account will be used.
The proxy account is setup using the dialog on the SQL Agent property page
in SQL Enterprise Manager (Job System tab) or using the extend stored
procedure xp_sqlagent_proxy_account, see
http://msdn.microsoft.com/library/de...aa-sz_8sdm.asp
See http://www.databasejournal.com/featu...le.php/3372131 for
more background.
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2005 All rights reserved.
"Matthew Beirn" <mbeirn@.ssimed.com> wrote in message
news:u7EruuMTFHA.3336@.TK2MSFTNGP10.phx.gbl...
> Hello All,
> I'm wrote a program that uses the extended stored procedure XP_CMDSHELL .
> The user is not an admin and I'm not sure how to set up a Proxy account.
> I'm getting the following error when I try to add the user into the
> Non-SysAdmin job step proxy account.
> ERROR EXECUTING EXTENDED STORED PROCEDURE Specified user can not login
> I gave the users domain rights to the master database on the server and
> then gave permission to use the XP_CMDSHELL procedure.
> My knowledge on permissions in SQL SERVER 2000 is hazy and I would like to
> know what steps I have to take to add a non-admin user to be able to use
> the XP_CMDSHELL procedure. Do I have to set anything outside of SQL
> SERVER 2000?...
> Any help will be greatly appreciated.
> Thanks,
> Matt
>

Thursday, March 29, 2012

Error Executing, SSIS Packages Coppied from another server

Error executing xp_cmdshell from within a SQL task in a DTS package...

ERROR EXECUTING XMLA WHILE IMPLEMENTING PROJECT REAL BI

Error executing User Defined function on another server

Error executing SSIS package

Error executing SSIS package

Error executing SSIS package

Error executing SQL Script from VB:NET

error executing sprocedure

error executing sprocedure

error executing sprocedure

Error executing sp_change_users_login

error executing query

error executing query

error executing query

Tuesday, March 27, 2012

Error Executing Parallel SQL Tasks

Error executing Packaga from job

Error executing non query: Timeout expired

Error executing extended stored procedure: Invalid Parameter

ERROR EXECUTING EXTENDED STORED PROCEDURE Specified user can not login

Error accessing report

Blog Archive

About Me