I would like to know how to execute an existing sql-script (from a file) on a sql-server 2005 Express. I use SQL Manager 2005, but I do not want to use this tool - because parts of the script might not be executed in a transaction, so that I may not use SQL Manager 2005.
But anyway I would like to be able to execute this on commmand line. The problem is that I even do not know 1) how to start command line 2) how to execute the existing sql-file from there.
I would be very happy if anybody could help me out with this!
i read some kb on msde that explain how to do the task in the title, but in sql 2005 client tool i can't see where to execute dts packgage in 2000 mode,
Ive created a DAL called Artist.xsd. Ive used stored procedures to access the data. The wizard created a stored procedure called 'dbo.ArtistSelectCommand' Ive granted the ASPNET account execute permissions on this stored procedure When I run the application and try to execute the stored proc, I get this error EXECUTE permission denied on object 'ArtistSelectCommand', database 'EBSNet', owner 'dbo'.
as far as im aware ive givne the ASPNET account the correct permissions
We just upgraded to SQL Server 2005 from SQL Server 2000. The DB was backed up using Enterprise Manager and restored with SQL Server Management Studio Express CTP. Everything went as expected (no errors, warnings, or any other indicator of problems).
The DB resides in a DB Server (Server1) and the application we are running is a Client/Server system where the AppServer resides on Server2.
During the application's operation all read, create, and delete transactions work fine but no update works. When viewing details in Trace Log I see this message after attempting any update:
Could not find server 'Server1' in sysservers. Execute sp_addlinkedserver to add the server to sysservers. (7202)
I have added reffrence for DTS Run time (using Microsoft.SqlServer.Dts.Runtime;)
But I am getting an Error like this
Error 1 'Application' is an ambiguous reference between 'System.Windows.Forms.Application' and 'Microsoft.SqlServer.Dts.Runtime.Application' C:Documents and Settingsvahqmd10My DocumentsVisual Studio 2005ProjectsWindowsApplication1WindowsApplication1Form1.cs 23 17 WindowsApplication1
Error 2 'Application' is an ambiguous reference between 'System.Windows.Forms.Application' and 'Microsoft.SqlServer.Dts.Runtime.Application' C:Documents and Settingsvahqmd10My DocumentsVisual Studio 2005ProjectsWindowsApplication1WindowsApplication1Form1.cs 23 39 WindowsApplication1
Error 3 The type 'System.Windows.Forms.Application' has no constructors defined C:Documents and Settingsvahqmd10My DocumentsVisual Studio 2005ProjectsWindowsApplication1WindowsApplication1Form1.cs 23 35 WindowsApplication1
For the following ADO Connection::Execute() function the "recAffected" is zero, after a successful insertion of data, in SQL Server 2005. In SQL Server Express I am getting the the number of records affected value correctly.
BEGIN DELETE FROM MyTable WHERE Column_1 IN ('abc', 'def' ) END BEGIN INSERT INTO MyTable (Column_1, Column_2, Cloumn_3 ) SELECT 'abc', 'data11', 'data12' UNION ALL SELECT 'def', 'data21', 'data22' END
But see this, for SQL Server 2005 "recAffected" has the correct value of 2 when I have just the insert statement.
INSERT INTO MyTable (Column_1, Column_2, Cloumn_3 ) SELECT 'abc', 'data11', 'data12' UNION ALL SELECT 'def', 'data21', 'data22'
For SQL Server 2005 in both cases the table got successfully inserted two rows and the HRESULT of Execute() function returns S_OK.
Does the Execute function has any problem with a statement like the first one (with delete and insert) on a SQL Server 2005 DB?
Why the "recAffected" has a value zero for the first SQL (with delete and insert) on a SQL Server 2005 DB? Do I need to pass any options to Execute() function for a SQL Server 2005 DB?
When connecting to SQL Server Express the "recAffected" has the correct values for any type of SQL statements.
Thank you for your time. Any help greatly appreciated.
For the following ADO Connection::Execute() function the "recAffected" is zero, after a successful insertion of data, in SQL Server 2005. In SQL Server Express I am getting the the number of records affected value correctly.
BEGIN DELETE FROM MyTable WHERE Column_1 IN ('abc', 'def' ) END BEGIN INSERT INTO MyTable (Column_1, Column_2, Cloumn_3 ) SELECT 'abc', 'data11', 'data12' UNION ALL SELECT 'def', 'data21', 'data22' END
But see this, for SQL Server 2005 "recAffected" has the correct value of 2 when I have just the insert statement.
INSERT INTO MyTable (Column_1, Column_2, Cloumn_3 ) SELECT 'abc', 'data11', 'data12' UNION ALL SELECT 'def', 'data21', 'data22'
For SQL Server 2005 in both cases the table got successfully inserted two rows and the HRESULT of Execute() function returns S_OK.
Does the Execute function has any problem with a statement like the first one (with delete and insert) on a SQL Server 2005 DB?
Why the "recAffected" has a value zero for the first SQL (with delete and insert) on a SQL Server 2005 DB? Do I need to pass any options to Execute() function for a SQL Server 2005 DB?
When connecting to SQL Server Express the "recAffected" has the correct values for any type of SQL statements.
Thank you for your time. Any help greatly appreciated.
Hi all, I have a problem while i create a proxy account.The situation is like this...There is a user who has an login in to the server.He has a stored procedure which calls some on the SSIS packages and XP_cmdshell...so this stored procedure basically load some data in to the tables .So for the login in order to execute the stored procedure as he is not a Sys admin I have created a proxy account in my account as Iam an SA and then in the proxies and in principals I selected his login name and this way I have created a credential and a proxy account.
Now the problem is if he logins with his id and password and try to execute the stored procedure it gives an error message
Server: Msg 15153, Level 16, State 1, Procedure xp_cmdshell, Line 1 The xp_cmdshell proxy account information cannot be retrieved or is invalid. Verify that the '##xp_cmdshell_proxy_account##' credential exists and contains valid information.
....so this mean the login is not able to see the proxy account.So what I did is I created a job and then in the job owner tab I have selected his login and then created a step with the type operating system (CmdExec) as I need to just execute the stored procedure and used the proxy account that I have created.
so I gave the command -- exec <stored procedure> --. But this job fails and gives the error message as [298] SQLServer Error: 536, Invalid length parameter passed to the SUBSTRING function. [SQLSTATE 42000]....
So now ....first My question is am I doing in a right way....if its right then why Iam not able to execute the stored procedure.
If there is any other way through which I can execute the stored procedure using a proxy account for the logins who are not sys admins....please do let me know.....
hello world, i read some kb on msde that explain how to do the task in the title, but in sql 2005 client tool i can't see where to execute dts packgage in 2000 mode,
I was trying to grant access for an application user for executing xp_cmdshell, but I got some error message saying that either doesn't the user exist, or I don't have the permissions to grant this. Does the user need to be a user in Master ? Or, don't I when logged in as "sa" have the sufficient permissions to grant execute on a SP in master?
I solved it by checking "Control server" under "Properties" > "Securables" for the login, but I don't actually want this login to have full control.
(And yes, I've read that allowing xp_cmdshell usage isn't recommended at all.)
Could you please help me on this issue.It is URgent .I am new to SSIS.
I am using DTS package in SSIS. I Have an SQLTask and DTS Package. I want to fail the SSIS PAckage when DTS 2000 Package Fails. I want to know How to populate the failure of DTS PAcage to SSIS PAcage.When i execute the SSIS Package the DTSPAckage task Failed Internally but it styays yellow for ever.it is not showing red Color in SSIS Package.Any help is greatyly appreciated.
I have a legncy DTS package when upgrading to SQL 2005, I used "SQL Business Intelligence Development Studio" to create a project, the control flow is "Execute 2000 DTS Package", by right click "Execute Task" of the control flow, the color changed from yellow to red, the message said "package Execution completed". When I try to schedule this package in SQL Server Agent, the job failed. When I run dtexec, got following error message.
C:>dtexec /file "C:Documents and SettingslyangMy DocumentsVisual Studio 200 5ProjectsTraingDTSTraingDTSDTSTraining.dtsx"
Microsoft (R) SQL Server Execute Package Utility Version 9.00.1399.06 for 32-bit Source: Execute DTS 2000 Package Task Execute DTS 2000 Package Task is initiated: 0% complete
End Progress
Error: 2008-05-08 09:13:35.74 Code: 0x00000000 Source: Execute DTS 2000 Package Task Description: System.Runtime.InteropServices.COMException (0x80040427): Execut ion was canceled by user. at DTS.PackageClass.Execute() at Microsoft.SqlServer.Dts.Tasks.Exec80PackageTask.Exec80PackageTask.ExecuteT hread() End Error
Progress: 2008-05-08 09:13:35.74 Source: Execute DTS 2000 Package Task Execute DTS 2000 Package Task is completed: 100% complete End Progress Warning: 2008-05-08 09:13:35.74 Code: 0x80019002 Source: DTSTraining Description: The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the M aximumErrorCount or fix the errors. End Warning
DTExec: The package execution returned DTSER_FAILURE (1).
Can anyone tell me what does the error message mean and how to solve them?
Hi all, I have a problem while i create a proxy account in SQL Sever 2005.The situation is like this...There is a user who has an login in to the server.He has a stored procedure which calls some on the SSIS packages and XP_cmdshell...so this stored procedure basically load some data in to the tables .So for the login in order to execute the stored procedure as he is not a Sys admin I have created a proxy account in my account as Iam an SA and then in the proxies and in principals I selected his login name and this way I have created a credential and a proxy account.
Now the problem is if he logins with his id and password and try to execute the stored procedure it gives an error message
Server: Msg 15153, Level 16, State 1, Procedure xp_cmdshell, Line 1 The xp_cmdshell proxy account information cannot be retrieved or is invalid. Verify that the '##xp_cmdshell_proxy_account##' credential exists and contains valid information.
....so this mean the login is not able to see the proxy account.So what I did is I created a job and then in the job owner tab I have selected his login and then created a step with the type operating system (CmdExec) as I need to just execute the stored procedure and used the proxy account that I have created.
so I gave the command -- exec <stored procedure> --. But this job fails and gives the error message as [298] SQLServer Error: 536, Invalid length parameter passed to the SUBSTRING function. [SQLSTATE 42000]....
So now ....first My question is am I doing in a right way....if its right then why Iam not able to execute the stored procedure.
If there is any other way through which I can execute the stored procedure using a proxy account for the logins who are not sys admins....please do let me know.....
Hello! I would like to write a value from a column to a parameter in SSIS with the Execute SQL task. The problem is that I will never get a value for the parameter.
You can recreate the problem with the AdventureWorksDW sample database.
1. Drop an execute SQL task in the control flow
2. Set the connection to the AdventureWorksDw database
3. Write this in the SQL Statement box Select Max(FullDateAlterNateKey) as LastDate
From DimTime 4. Set the resultset to single Row 5. Under result set assign LastDate as the Result Name and create a parameter with a default date. 6. Execute the task, that will finish succesfully but the value of the parameter in 5 have not changed.
I have tried to change the scope to both the package level and the task level without any success. The value of the variable is still the default value. I have also tried a string variable without sucess.
Hi,I am trying to set up a Updatable Subscriptions for TransactionalReplication with SQL 2005 (queued updating).I have a stored procedure "SYS_spShellTest" which is replicated to thesubscriber viathe replication option "Execution of the stored procedure":-------------------------------------------------------------ALTER procedure [dbo].[SYS_spShellTest] @cmd varchar(1000)WITH EXECUTE AS ownerasBEGINEXEC @result = master..xp_cmdshell @cmd, NO_OUTPUTReturn (@result)END-------------------------------------------------------------1) If I call "exec SYS_spShellTest" on the publisher the call is alsoexecuted on the subscriber.2) If I call "exec SYS_spShellTest" on the subscriber the call is NOTexecuted on the publisher.How can I make 2) work so that it is also executed on the publisherplease?Thanks a lot,Patrick
Hi all, I have the following T-SQL code of Common Table Express (CTE) that works in the SQL Server Management Studio Express (SSMSE):
--CTE.sql--
USE ChemAveRpd
GO
WITH PivotedLabTests AS
(
SELECT LT.AnalyteName, LT.Unit,
Prim = MIN(CASE S.SampleType WHEN 'Primary' THEN LT.Result END),
Dupl = MIN(CASE S.SampleType WHEN 'Duplicate' THEN LT.Result END),
QA = MIN(CASE S.SampleType WHEN 'QA' THEN LT.Result END)
FROM LabTests LT
JOIN Samples S ON LT.SampleID = S.SampleID
GROUP BY LT.AnalyteName, LT.Unit
)
SELECT AnalyteName, Unit, avg1 = (abs(Prim + Dupl)) / 2,
avg2 = (abs(Prim + QA)) / 2,
avg3 = (abs(Dupl + QA)) / 2,
RPD1 = (abs(Prim - Dupl) / abs(Prim + Dupl)) * 2,
RPD2 = (abs(Prim - QA) / abs(Prim + QA)) * 2,
RPD3 = (abs(Dupl - QA) / abs(Dupl + QA)) * 2
FROM PivotedLabTests
GO
=========================================== How can I execute this set of the CTE.sql code in the VB 2005 Express via the Stored Procedure programming?
hi, i need execute a store procedure of Informix from sql server integration services. i use driver IBM informix provider 3.2 and have linked server Informix and use sql task for execute the procedure it's posible??
I had got the below error when I execute a DELETE SQL query in SSIS Execute SQL Task :
Error: 0xC002F210 at DelAFKO, Execute SQL Task: Executing the query "DELETE FROM [CQMS_SAP].[dbo].[AFKO]" failed with the following error: "The transaction log for database 'CQMS_SAP' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
But my disk has large as more than 6 GB space, and I query the log_reuse_wait_desc column in sys.databases which return value as "NOTHING".
So this confused me, any one has any experience on this?
I'm looking for a way to refer to a package variable within any Transact-SQL code included in either an Execute SQL or Execute T-SQL task. If this can be done, I need to know the technique to use - whether it's something similar to a parameter placeholder question mark or something else.
FYI - I've been able to successfully execute Transact-SQL statements within the Execute SQL task, so I don't think the Execute T-SQL task is even necessary for this purpose.
I have a master package, which executes child packages that are located on a SQL Server. The Child packages execute other child packages which are also located on the SQL server.
Everything works fine when I execute in process. But when I set the parameter in the mater package ExecutePackageTask to ExecuteOutOfProcess = True, I get the following error
Error: 0xC00470FE at DFT Load Data, DTS.Pipeline: SSIS Error Code DTS_E_PRODUCTLEVELTOLOW. The product level is insufficient for component "Row Count" (5349).
Error: 0xC00470FE at DFT Load Data, DTS.Pipeline: SSIS Error Code DTS_E_PRODUCTLEVELTOLOW. The product level is insufficient for component "SCR Custom Split" (6399).
Error: 0xC00470FE at DFT Load Data, DTS.Pipeline: SSIS Error Code DTS_E_PRODUCTLEVELTOLOW. The product level is insufficient for component "SCR Data Source" (5100).
Error: 0xC00470FE at DFT Load Data, DTS.Pipeline: SSIS Error Code DTS_E_PRODUCTLEVELTOLOW. The product level is insufficient for component "DST_SCR Load Data" (6149).
The child packages all run fine when executed directly, and the master package runs fine if Execute Out of Process is False.
Msg 7399, Level 16, State 1, Procedure tr_cpD, Line 14
The OLE DB provider "SQLNCLI" for linked server "S2" reported an error. The provider did not give any information about the error.
Msg 7312, Level 16, State 1, Procedure tr_cpD, Line 14
Invalid use of schema or catalog for OLE DB provider "SQLNCLI" for linked server "S2". A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog or schema.
EXEC sp_insertNewRecord 7, 'Peter', 'Wang', '678 Old St', 'Detroit',
'Michigon', '67899', 'PeterWang@yahoo.com'
GO
======================================================================= Now, I want to insert a new record into the dbo.Friends table of my shcDB by executing the following T-SQL and Visual Basic 2005 codes that are programmed in a VB2005 Express project "CallshcDBspWithAdoNet": --Form1.vb--
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Public Class Form1
Public Sub InsertNewFriend()
Dim connectionString As String = "Integrated Security-SSPI;Persist Security Info=False;" + _
"Initial Catalog=shcDB;Data Source=.SQLEXPRESS"
Dim connection As SqlConnection = New SqlConnection(connectionString)
connection.Open()
Try
Dim command As SqlCommand = New SqlCommand("sp_InsertNewRecord", connection)
=========================================================== I ran the above project in VB 2005 Express and I got the following 5 errors: 1. Name 'EXEC' is not declared (in Line 16 of Form1.vb) 2. Method arguments must be enclosed in parentheses (in Line 16 of Form1.vb) 3. Name 'sd-insertNewRecord' is not declared. (in Line 16 of Form1.vb) 4.Comma, ')', or a valid expression continuation expected (in Line 16 of Form1.vb) 5. Expression expected (in Line 16 of Form1.vb) ============================================================ I know that "EXEC sp_insertNewRecord 6, 'Craig', 'Utley', '5577 Baltimore Ave',
'Ellicott City', 'MD', '21045', 'CraigUtley@yahoo.com' "in Line 16 of Form1.vb is grossly in error. But I am new in doing the programming of T-SQL in VB 2005 Express and I am not able to change it.
Please help and advise me how to correct these problems.
I have a SSIS package contains an "Execute SQL Task". The SQL will raise error or succeed. However, it sounds the package won't pick up the raised error?
Or is it possible to conditional run other control flow items according the the status of SQL task execution?
Some of you guys seem to be gurus with the new Integration Services technology, so I hope someone can lend me some advice, as I haven't worked much with integration before.
Ok, here we go. What I want to do is select some data from my database and export the result to a flat file (really a .csv file with values delimited by semicolons). Sounds like a simple thing to do? Well, I'm sure it is when you know how to do it :) I know I could manage the same thing by writing a C# class that creates that .csv file, but the decision has been made to use Integration Services for these kind of operations.
I created an SSIS project in Business Intelligence Development Studio, and created a package (I defined the task flow etc.). By choosing "Execute package" from the IDE I managed to create the flat file, and everything seemed sweet. However, When trying to execute the package (package.Execute();) from C# code, it only results in a failure. I have read on several sites that this has to do with my program lacking the rights to run the package from the client side. OK, fair enough. I need to create the package on the server, and use an SQL Server Agent to execute the package through the agent.
Can anyone tell me how I need to do this? How can I ensure that the package is created on the sql server instead of locally on my development computer? When I create a new SSIS project the package is already made, and it is created locally on my PC.
I hope someone can give me some help. Even a little nudge would be appreciated ;)
Ok, so these are the facts: My company wants to implement a BI, the warehousing company we used sold us panorama on sql server. Our database is on oracle. They were proposing this method: a) Link oracle server, copy oracle tables to sql database and then build cubes from sql database. The operation will be made only once a day at night. I don't like it so make it like this: b) Build cubes directly from linked oracle server. As it is not good to build directly from tables and views (because they could change between processing dimensions), I used materialized views (or snapshots) and some views on slow modifying tables. Problem: Oracle snapshots must be refreshed and that is done in oracle with a procedure. My question is: How can I run an oracle procedure directly from sql server? I want to create a job that will refresh the oracle snapshots and process analysis database after that. I know I can use OPENQUERY to return queries from oracle but I want to execute a script. Solutions?
Another way was by doing it in a command line. I asume there is an special job feature that can run programs.
I have a custom made database role that has permissions to execute a stored procedure. The store procedure then selects data from some tables. But the role does not have select permissions on the tables. Do I have to allow select on the tables, or can I do this another way?
hi i have some couple of .sql files. each file contains some tables creation and some stored procedures creation, i don't want to go to Query Analyzer open each SQL file and Execute, i want to execute all .SQL files at a time, how can i do this one.
I've created a stored procedure on several servers. All with the same name and all in a database with same names. Now I’m trying to execute the stored procedures by executing a script.... I don’t get it to work! I can execute it successfully by starting it manually: exec [CIMPDB01CIMT1].zz_am.dbo.usp_SpaceMon exec [CIMPDB01CIMT2].zz_am.dbo.usp_SpaceMon and so on..... no problem
But when I try to execute it by using a script (and substitute the instance names with values taken from a table) it won’t:
declare @x int declare @dbname varchar(500) declare @SQL nvarchar(600) set @x = 1
create table #databases (ID int IDENTITY,name varchar(500))
insert #databases select instancelongname from instances where actief='J'
while @x <= (select max(id) from #databases) begin select @dbname = name from #databases where id = @x print @dbname select @SQL='exec ' + @dbname + '.zz_am.dbo.usp_SpaceMon' print @SQL execute @SQL
set @x = @x + 1 end
drop table #databases
Msg 203, Level 16, State 2, Line 17 The name 'exec [CIMPDB01CIM].zz_am.dbo.usp_SpaceMon' is not a valid identifier.
