executing OPENQUERY

Hi,

I'm trying to execute an openquery statement from SQL Server 2005 against a linked server AS2005 (both on same machine) from Management Studio.

If I use a one connection with full rights I have no problem. But if I use the specific connection for that application I got this error:

OLE DB provider "MSOLAP.3" for linked server "ASLOCAL2" returned message "The following system error occurred: A specified logon session does not exist. It may already have been terminated. .".

Msg 7303, Level 16, State 1, Line 5

Cannot initialize the data source object of OLE DB provider "MSOLAP.3" for linked server "ASLOCAL2".

"Ad hoc data-mining query" is checked.

MSOLAP Provider has "Allow inprocess" checked.

Code Snippet

SELECT * FROM OPENQUERY(ASLOCAL2,'SELECT non empty {[Measures].[Prices Avg] ,[Measures].[Prices Max] ,[Measures].[Prices Min] } ON COLUMNS,non empty [Tbl DW Dim Type].[Name].&[1] ON ROWS FROM [DW DEV] ')

Any ideas ?

Thnx.

Have you tried running a profiler trace against SSAS while trying to run the OPENQUERY() ?

I am wondering if it is a permissions issue for the account that the application is using for the connection.

|||The only difference between these tow accounts is that one account is Windows account and the other one is Sql server account.|||

Well that is probably your issue. SSAS only supports windows authentication. You would need to have the linked server setup to authenticate using the current login's security context, which works fine for windows accounts, but if you use a sql account, SQL Server will fall back to authenticating against the SSAS server using the account that the SQL Server is running under, which probably does not have permissions to query the cubes.

So your choices are to either only use windows accounts against the linked server or to make sure that the account that SQL Server is running under has access to the appropriate objects in SSAS.

|||

You're right.

I changed the way that the (web)application is connecting to the Sql server (until now I used sql account) and now I am using an windows account.

Executing DTS from Stored Procedure in Sql Server 2000

Hai .....,
I have created a DTS package in the Server machine (Server=machine7 and my machine is machine3). When i execute the DTS directly by selecting it, it completes successfully. But when I try to execute the same DTS thru my stored procedure, I 'm getting the following error message:(I Executed the procedure thru Query Analyzer window and got the following message)

DTSRun: Loading...
DTSRun: Executing...
DTSRun OnStart: DTSStep_DTSActiveScriptTask_1
DTSRun OnError: DTSStep_DTSActiveScriptTask_1, Error = -2147220482 (800403FE)
Error string: Error Code: 0
Error Source= msxml3.dll
Error Description: A connection with the server could not be established
Error on Line 10
Error source: Microsoft Data Transformation Services (DTS) Package
Help file: sqldts80.hlp
Help context: 4500
Error Detail Records:
Error: -2147220482 (800403FE); Provider Error: 0 (0)
Error string: Error Code: 0
Error Source= msxml3.dll
Error Description: A connection with the server could not be established

Error on Line 10
Error source: Microsoft Data Transformation Services (DTS) Package
Help file: sqldts80.hlp
Help context: 4500

Error: -2147467259 (80004005); Provider Error: 0 (0)
Error string: A connection with the server could not be established
Error source: msxml3.dll
Help file:
Help context: 0
DTSRun OnFinish: DTSStep_DTSActiveScriptTask_1
DTSRun: Package execution complete.
NULL
Can anyone help me solve this problem please

I am with the same occurrence, but in a server windows 2003 server. Does anybody can me to help?

Guilherme

guilhermer@.correios.com.br

|||

Can you post the code you are running?

This works:

xp_cmdshell'dtsrun /SmyServer /NmyPackage /E'

|||DTSRun /S "smg0062" /N "DBTNT - BAIXA SRO" /W "0" /Esql

Executing DTS from Stored Procedure in Sql Server 2000

Hai .....,
I have created a DTS package in the Server machine (Server=machine7 and my machine is machine3). When i execute the DTS directly by selecting it, it completes successfully. But when I try to execute the same DTS thru my stored procedure, I 'm getting the following error message:(I Executed the procedure thru Query Analyzer window and got the following message)

DTSRun: Loading...
DTSRun: Executing...
DTSRun OnStart: DTSStep_DTSActiveScriptTask_1
DTSRun OnError: DTSStep_DTSActiveScriptTask_1, Error = -2147220482 (800403FE)
Error string: Error Code: 0
Error Source= msxml3.dll
Error Description: A connection with the server could not be established
Error on Line 10
Error source: Microsoft Data Transformation Services (DTS) Package
Help file: sqldts80.hlp
Help context: 4500
Error Detail Records:
Error: -2147220482 (800403FE); Provider Error: 0 (0)
Error string: Error Code: 0
Error Source= msxml3.dll
Error Description: A connection with the server could not be established

Error on Line 10
Error source: Microsoft Data Transformation Services (DTS) Package
Help file: sqldts80.hlp
Help context: 4500

Error: -2147467259 (80004005); Provider Error: 0 (0)
Error string: A connection with the server could not be established
Error source: msxml3.dll
Help file:
Help context: 0
DTSRun OnFinish: DTSStep_DTSActiveScriptTask_1
DTSRun: Package execution complete.
NULL
Can anyone help me solve this problem please

I am with the same occurrence, but in a server windows 2003 server. Does anybody can me to help?

Guilherme

guilhermer@.correios.com.br

|||

Can you post the code you are running?

This works:

xp_cmdshell 'dtsrun /SmyServer /NmyPackage /E'

|||DTSRun /S "smg0062" /N "DBTNT - BAIXA SRO" /W "0" /E

Execute windows on client machine

Hello.

How to execute add users window, backup wizard and other windows from
Enterprice Manager on client machine? Does MDAC or sth is able to do this?

bye...

--
__ __
|__\\ | || |_// / \\ \_// FreeBSD: The Power To Serve
|__// |__|| | \\ \__// / \\ +----------+
+[ http://bukox.prv.pl ][ http://total.bukox.dmkproject.pl ]+Hi

It is not clear exactly what you are wishing, but at a guess you have EM on
the client machine but your server is not registered using an account that
is sufficiently privileged.

You may want to write your own interface that calls the appropriate stored
procedures.

John

"[BuKoX]" <bukox.wytnij@.tlen.pl> wrote in message
news:186574153.20041223193834@.tlen.pl...
> Hello.
> How to execute add users window, backup wizard and other windows from
> Enterprice Manager on client machine? Does MDAC or sth is able to do this?
> bye...
> --
> __ __
> |__\\ | || |_// / \\ \_// FreeBSD: The Power To Serve
> |__// |__|| | \\ \__// / \\ +----------+
> +[ http://bukox.prv.pl ][ http://total.bukox.dmkproject.pl ]+

execute SSIS package stored in remote SQL server from a client machine.

I have written a VB component to execute a SSIS package stored in SQL server.

I am using the Microsoft.sqlserver.dts.dtsclient, dtsconnection/dtscommand

and executereader and everything works perfect on the local machine. This is descibed in a msdn site .

But my customer have a remote SQL server and want no extra BI clients/SQL server clients etc on the client machine, only an ordinary windows client and network connection to the SQL server.

My question is: Can you still use dtsclient or do you have to do in some other way?

rose-marie

It depends where your VB component is going to be running. You will need SSIS installed on the same machine.

-Jamie

|||

The whole idea was to run the VB component in a client machine without any extra i.e without SSIS.

I understand that this is not possible, is it possible to achieve this in another way?

rose-marie

|||

Can you not run the component somewhere else?

|||

No, in this case there is a demand on a stand-alone windows application

without any dependencies other than oledb connections to SQL server.

Otherwise I would have liked a web service in the server machine or something like that, but...

rose-marie

Execute permission lost for nonadmin user after db migration with attach

I have moved a SQL2000 database from one machine to another by detaching and
attaching the database from enterprise manager.
All went well except that I have lost execute permissions on a bunch of
stored procedures for non admin users. I have not lost the user.
Has anyone else has experienced this problem?
Is this normal? How can I avoid this happening?
Is there a way I can compare permissions with the old db without going and
manually check each and every sp?
The first machine was a Win 2000 machine and the target was Windows 2003.
I'm not sure that it matters
Thanks,
Dimitrie
dimitrie wrote:
> I have moved a SQL2000 database from one machine to another by
> detaching and attaching the database from enterprise manager.
> All went well except that I have lost execute permissions on a bunch
> of stored procedures for non admin users. I have not lost the user.
> Has anyone else has experienced this problem?
> Is this normal? How can I avoid this happening?
> Is there a way I can compare permissions with the old db without
> going and manually check each and every sp?
> The first machine was a Win 2000 machine and the target was Windows
> 2003. I'm not sure that it matters
>
> Thanks,
> Dimitrie
You may be able to do what you are asking using one of the change
manager products in the marketplace. Red Gate and Imceda both have them,
as do some other players. If both databases are accessible, the change
manager should be able to compare and create a script to get the new
database in compliance with the old.
David Gugick
Imceda Software
www.imceda.com
|||If the users were NT logins, simply add the NT loging to the server.
However if the users were mapped to standard SQL logins, read up on
sp_change_users_login in books on line.THat will fix up the users for you.
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
"dimitrie" <dagafitei@.yahoo.com> wrote in message
news:%23cr6j5$rEHA.1644@.tk2msftngp13.phx.gbl...
> I have moved a SQL2000 database from one machine to another by detaching
and
> attaching the database from enterprise manager.
> All went well except that I have lost execute permissions on a bunch of
> stored procedures for non admin users. I have not lost the user.
> Has anyone else has experienced this problem?
> Is this normal? How can I avoid this happening?
> Is there a way I can compare permissions with the old db without going and
> manually check each and every sp?
> The first machine was a Win 2000 machine and the target was Windows 2003.
> I'm not sure that it matters
>
> Thanks,
> Dimitrie
>

Execute permission lost for nonadmin user after db migration with attach

I have moved a SQL2000 database from one machine to another by detaching and
attaching the database from enterprise manager.
All went well except that I have lost execute permissions on a bunch of
stored procedures for non admin users. I have not lost the user.
Has anyone else has experienced this problem?
Is this normal? How can I avoid this happening?
Is there a way I can compare permissions with the old db without going and
manually check each and every sp?
The first machine was a Win 2000 machine and the target was Windows 2003.
I'm not sure that it matters
Thanks,
Dimitriedimitrie wrote:
> I have moved a SQL2000 database from one machine to another by
> detaching and attaching the database from enterprise manager.
> All went well except that I have lost execute permissions on a bunch
> of stored procedures for non admin users. I have not lost the user.
> Has anyone else has experienced this problem?
> Is this normal? How can I avoid this happening?
> Is there a way I can compare permissions with the old db without
> going and manually check each and every sp?
> The first machine was a Win 2000 machine and the target was Windows
> 2003. I'm not sure that it matters
>
> Thanks,
> Dimitrie
You may be able to do what you are asking using one of the change
manager products in the marketplace. Red Gate and Imceda both have them,
as do some other players. If both databases are accessible, the change
manager should be able to compare and create a script to get the new
database in compliance with the old.
--
David Gugick
Imceda Software
www.imceda.com|||Hi
Your problem is that users are stored in master. You moved your DB from one
server to another one that does not have the same users in it's master.
Generally, before you detach and attach, script out the users and
permissions, remove the permissions, detach the DB, attach the DB and then
re-apply the users and permissions from the script.
The user ID between syslogings and sysusers in the master and user DB must
match exactly, the name is not a good enough match.
You can also copy users between servers by creating a new DTS task using the
'Transfer Logins Task' and running it.
Regards
Mike
"dimitrie" wrote:
> I have moved a SQL2000 database from one machine to another by detaching and
> attaching the database from enterprise manager.
> All went well except that I have lost execute permissions on a bunch of
> stored procedures for non admin users. I have not lost the user.
> Has anyone else has experienced this problem?
> Is this normal? How can I avoid this happening?
> Is there a way I can compare permissions with the old db without going and
> manually check each and every sp?
> The first machine was a Win 2000 machine and the target was Windows 2003.
> I'm not sure that it matters
>
> Thanks,
> Dimitrie
>
>|||If the users were NT logins, simply add the NT loging to the server.
However if the users were mapped to standard SQL logins, read up on
sp_change_users_login in books on line.THat will fix up the users for you.
--
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
"dimitrie" <dagafitei@.yahoo.com> wrote in message
news:%23cr6j5$rEHA.1644@.tk2msftngp13.phx.gbl...
> I have moved a SQL2000 database from one machine to another by detaching
and
> attaching the database from enterprise manager.
> All went well except that I have lost execute permissions on a bunch of
> stored procedures for non admin users. I have not lost the user.
> Has anyone else has experienced this problem?
> Is this normal? How can I avoid this happening?
> Is there a way I can compare permissions with the old db without going and
> manually check each and every sp?
> The first machine was a Win 2000 machine and the target was Windows 2003.
> I'm not sure that it matters
>
> Thanks,
> Dimitrie
>

Execute permission lost for nonadmin user after db migration with attach

I have moved a SQL2000 database from one machine to another by detaching and
attaching the database from enterprise manager.
All went well except that I have lost execute permissions on a bunch of
stored procedures for non admin users. I have not lost the user.
Has anyone else has experienced this problem?
Is this normal? How can I avoid this happening?
Is there a way I can compare permissions with the old db without going and
manually check each and every sp?
The first machine was a Win 2000 machine and the target was Windows 2003.
I'm not sure that it matters
Thanks,
Dimitriedimitrie wrote:
> I have moved a SQL2000 database from one machine to another by
> detaching and attaching the database from enterprise manager.
> All went well except that I have lost execute permissions on a bunch
> of stored procedures for non admin users. I have not lost the user.
> Has anyone else has experienced this problem?
> Is this normal? How can I avoid this happening?
> Is there a way I can compare permissions with the old db without
> going and manually check each and every sp?
> The first machine was a Win 2000 machine and the target was Windows
> 2003. I'm not sure that it matters
>
> Thanks,
> Dimitrie
You may be able to do what you are asking using one of the change
manager products in the marketplace. Red Gate and Imceda both have them,
as do some other players. If both databases are accessible, the change
manager should be able to compare and create a script to get the new
database in compliance with the old.
David Gugick
Imceda Software
www.imceda.com|||If the users were NT logins, simply add the NT loging to the server.
However if the users were mapped to standard SQL logins, read up on
sp_change_users_login in books on line.THat will fix up the users for you.
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
"dimitrie" <dagafitei@.yahoo.com> wrote in message
news:%23cr6j5$rEHA.1644@.tk2msftngp13.phx.gbl...
> I have moved a SQL2000 database from one machine to another by detaching
and
> attaching the database from enterprise manager.
> All went well except that I have lost execute permissions on a bunch of
> stored procedures for non admin users. I have not lost the user.
> Has anyone else has experienced this problem?
> Is this normal? How can I avoid this happening?
> Is there a way I can compare permissions with the old db without going and
> manually check each and every sp?
> The first machine was a Win 2000 machine and the target was Windows 2003.
> I'm not sure that it matters
>
> Thanks,
> Dimitrie
>

Execute permission lost after database migration with attach

I have moved a SQL2000 database from one machine to another by detaching and
attaching the database from enterprise manager.
All went well except that I have lost execute permissions on a bunch of
stored procedures for non admin users. I have not lost the user but rather
it's permissions.
Has anyone else has experienced this problem?
Is this normal? How can I avoid this happening?
The first machine was a Win 2000 machine and the target was Windows 2003.
I'm not sure that it matters
Thanks,
DimitrieHi,
Looks like the mapping between the logins and users are lost after the
restore. You could the system procedure sp_change_users_login to recreate
the mapping. See the details of the procedure in books online.
Thanks
Hari
SQL Server MVP
"dimitrie" <dagafitei@.yahoo.com> wrote in message
news:%23N7B8y$rEHA.2000@.tk2msftngp13.phx.gbl...
>I have moved a SQL2000 database from one machine to another by detaching
>and attaching the database from enterprise manager.
> All went well except that I have lost execute permissions on a bunch of
> stored procedures for non admin users. I have not lost the user but rather
> it's permissions.
> Has anyone else has experienced this problem?
> Is this normal? How can I avoid this happening?
> The first machine was a Win 2000 machine and the target was Windows 2003.
> I'm not sure that it matters
>
> Thanks,
> Dimitrie
>|||> Looks like the mapping between the logins and users are lost after the
> restore. You could the system procedure sp_change_users_login to recreate
> the mapping. See the details of the procedure in books online.
Agree with Hari. Just an addition: sp_change_userslogin works for SQL logins
only. For Win logins, you have to download additioal procedures from MS site
(file mapsids.exe). Check the article at
http://support.microsoft.com/kb/240872/EN-US/.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com

Execute Package Task Failed when deployed to server..

Hi,

I have a package that contains an Execute Package Task... it works fine when on my local machine and on our test-server.
After deploying the SSIS solution to our customers server, I open the project in Visual Studio and run the mainPackage in debug mode and the Task fails when trying to execute the package.. if I remove everything in the childpackage it still fails... the path to the package is correct and I know that I have rigths to execut it. I do not use any package security/encryption..
If i create a new package in the project and create a new fileconnection to it the Execute Package Task executes correctly, with the exact same content as the original package...

So my guess is that something in the FileConnetion object is set wrong, but i cannot understand what it is. The errormessage i get is something like this. "Execute Package Failed. Could not execute package".

Anyone had the same problem as I? I′m grateful for any help!

/erik

Hi Erik,

This could be the package ProtectionLevel property. You can check if this is the problem by setting it to EncryptSensitiveWithPassword and then supplying a password. You will need to pass the password in using the /Decrypt switch in DTExec or by setting it in an SSIS job step definition.

Hope this helps,
Andy

|||Sticky|||I still do not know what caused the problem. I "solved" it by creating a new package with the exact same content. I tried to do as Andy suggested but it dit not help...|||

Hi Erik,

Thanks for the feedback - glad you got it working.

Andy

Execute Command Process - remote batch file?

Good afternoon-
Can a batch file that resides on another server be executed from a different machine? I have a batch file that resides on a server that I would like to run using SQL 2005 Integration Services. Is there anything I can do that would allow me to remotely execute this batch file and have it run in that environment.
BATCH FILE:

cd C:\Trandev\
otrun -at OTRecogn.att -DINPUT_FILE=%1 -tl 1 -cs dv -lg mylog -I
C:\Trandev represents the remote environment

I have tried mapping the remote machine to a network drive on my local machine and using that drive to execute the batch file in an Execute Process Task, but it does not work.

SSIS:

I have a FOR EACH loop grabbing files and writing fileName to a variable that is passed to the Process Task as an argument through an expression(%1 in the batch file above). The Working Directory is a mapped network drive. The Executable is also a network drive plus batch file name.

Any help would be appreciated.
My computer is a HP Compaq dc7100, 512mb RAM, WindowsXP

An error message (if there is one) is more use than just saying "it doesn't work".

-Jamie

|||

[Execute Process Task] Error: In Executing "V:\z_811Test.bat" "edi_txt_test.in383" at "V:\", The process exit code was "1" while the expected was "0".

V:\ is a mapped network drive representing the environment where I wish to run the batch file.

James