How To Execute A SQL-Script On SQL-Server 2005

Feb 15, 2006

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!

Regards,
Fabian

my favorit hoster is ASPnix : www.aspnix.com !

View 2 Replies


ADVERTISEMENT

Inport And Execute Sql Server 2000 Dts Packgages In Sql Server 2005

Jan 22, 2007

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,

View 1 Replies View Related

Execute Permissions Aspnet Sql Server 2005

Nov 29, 2006

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
 
EXEC sp_grantlogin [MachineNameASPNET]
EXEC sp_grantdbaccess [MachineNameASPNET], [ASPNET]
 
** Ive also done this by selecting the stored proc in sql server 2005 and setting permissions by right clicking and selecting properties.
 
Is there anything else I need to do ?
 
 

View 1 Replies View Related

How To Execute A Batch File Using SQL Server 2005?

Dec 23, 2005

Hi all,As the topic describes, I'd like to run a batch file using sql server.How do i do it?Thanks in advance

View 1 Replies View Related

Upgraded To SQL Server 2005. Updates No Longer Work. Error Could Not Find Server 'DEVDB' In Sysservers. Execute Sp_addlinkedse

Feb 28, 2006

Hello!

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)

Any help is greatly appreciated,

Lucio Gayosso

View 19 Replies View Related

Run Time Error While Execute Sql Server 2005 Integration Services Package From C#

Jun 6, 2006

Hi

I am trying to Execute an SSIS Package from C# Dot Net.

Below the code i am using.

try
{
Application app = new Application();
Package package = app.LoadPackage("DTSPackage/WinServeMonitoring.dtsx", null);
DTSExecResult result = package.Execute();
label1.Text = "Package Execution " + result.ToString();


}
catch (Exception exp)
{
label1.Text = exp.Message.ToString();
}

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

Can any one Help me !

Regards

Deepu M.I

View 9 Replies View Related

Saving The Full Resultset Of Execute Sql Task Directly Into Sql Server 2005 Table

May 8, 2007

Hi friends,

I couldn't find links for this issue.

1) How to write the contents of a dataset or a full resultset (from execute sql task) directly into a Sql Server 2005 table.

2) Since I have hundreds of Resulting columns, I want to create the Destination table based on the structure of the dataset.



How can we achieve this?



Thanks

Subhash Subramanyam









View 4 Replies View Related

ADO - Connection::Execute() Returning Zero Records Affected After Successful Insertion Of Data In SQL Server 2005

Apr 11, 2008

Hello,

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.

_variant_t recAffected;
connectionObject->Execute(SQL, &recAffected, adCmdText );

The SQL string is

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.

Thanks,
Kannan

View 1 Replies View Related

ADO - Connection::Execute() Returning Zero Records Affected After Successful Insertion Of Data In SQL Server 2005

Apr 5, 2008

Hello,

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.

_variant_t recAffected;
connectionObject->Execute(SQL, &recAffected, adCmdText );

The SQL string is

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.

Thanks,
Kannan

View 5 Replies View Related

Execute A Stored Procedure Which Calls SSIS Packages Using A Proxy Account For Non Sysadmins In Sql Server 2005

Sep 18, 2007



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.....

Thanks
Raja.V

View 2 Replies View Related

Execute Dts 2000 Packgage Under Sql 2005

Jan 22, 2007

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,

View 2 Replies View Related

Xp_cmdshell Execute Rights (2005)

Sep 11, 2006

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.)

View 3 Replies View Related

How To Execute The .rdl File In Asp Dotnet With C# 2005

Aug 7, 2007



hi friends,

i want to run the .rdl file(report file) in asp dotnet 2005 with c#

so please send me the coding

regards
koti

View 2 Replies View Related

Execute SQL2000 PAckage In 2005

Mar 12, 2007



HI 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.



Thanks

ISANAKA

View 1 Replies View Related

Execute 2000 DTS Pacakge SSIS In SQL 2005

May 8, 2008

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?


Thanks very much in advance!


Lisa

View 3 Replies View Related

Execute Stored Proc Using A Proxy Account In 2005

Sep 18, 2007

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.....

Thanks
Raja.V

View 1 Replies View Related

Execute SQL Task (SSIS 2005) SP2 And Resultset To A Parameter

Oct 8, 2007



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.

Any ideas?

Kind Regards
Thomas Ivarsson

View 5 Replies View Related

SQL 2005: Transactional Replication &> Executing Sp On Subscribershould Execute On Publisher

Jan 16, 2008

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

View 1 Replies View Related

Is It Possible To Execute The T-Sql Code Of Common Table Expressions In VB 2005 Express?

Jan 2, 2008

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?

Please help and respond.

Thanks in advance,
Scott Chang

View 1 Replies View Related

Execute Store Procedure In Informix From Sql Task Integration Services 2005

Dec 21, 2007



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??

View 2 Replies View Related

Help! The Transaction Log Is Full Error In SSIS Execute SQL Task When I Execute A DELETE SQL Query

Dec 6, 2006

Dear all:

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?

Many thanks,

Tomorrow

View 5 Replies View Related

Looking For A Way To Refer To A Package Variable Within Any Transact-SQL Code Included In Execute SQL Or Execute T-SQL Task

Apr 19, 2007

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.

View 5 Replies View Related

SSIS Execute Package With Execute Out Of Process = True Causes ProductLevelToLow Error

Mar 6, 2008



Hi.

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.

Any help would be greatly appreciated.

Thanks

Geoff.

View 7 Replies View Related

Error :Execute Trigger From Remote Server To Another Server By Linked Server

Jul 22, 2007

i did "Linked server" between To Servers , and it's Working.

---------------------------



For Example :

Server 1 =S1.

Server = S2.

i create table in S1 : name = TblS1

i create same table in S2 : name TblS2



and i create trigger(name tr_cpD) From S1 in TblS1 For send data To TblS2 in S2

/****************** trigger Code ***************

CREATE TRIGGER dbo.tr_cpD

ON dbo.TblS1

AFTER INSERT

AS


BEGIN





SET NOCOUNT ON;


insert into [S2].[dbname].[dbo].[TblS2] Select ID,Name from insertedEND

**************************************************



result is :

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.





how i can execute this trigger



View 5 Replies View Related

T-SQL And Visual Basic 2005 Codes That Execute A User-Defined Stored Procedure In Management Studio:How To Declare EXEC &&amp; Sp?

Jan 17, 2008

Hi all,

In my SQL Server Management Studio Express (SSMSE), I executed the following sql code suuccessfully:
--insertNewRocord.sql--

USE shcDB

GO

CREATE PROC sp_insertNewRecord @procPersonID int,

@procFirstName nvarchar(20),

@procLastName nvarchar(20),

@procAddress nvarchar(50),

@procCity nvarchar(20),

@procState nvarchar(20),

@procZipCode nvarchar(20),

@procEmail nvarchar(50)

AS INSERT INTO MyFriends

VALUES (@procPersonID, @procFirstName, @procLastName, @procAddress,

@procCity, @procState, @procZipCode, @procEmail)

GO

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)

command.CommandType = CommandType.StoredProcedure


EXEC sp_insertNewRecord 6, 'Craig', 'Utley', '5577 Baltimore Ave',

'Ellicott City', 'MD', '21045', 'CraigUtley@yahoo.com'


Console.WriteLine("Row inserted: " + _

command.ExecuteNonQuery().ToString)

Catch ex As Exception

Console.WriteLine(ex.Message)

Throw

Finally

connection.Close()

End Try

End Sub

End Class

===========================================================
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.

Thanks in advance,
Scott Chang

View 22 Replies View Related

T-SQL And Visual Basic 2005 Codes That Execute A User-Defined Stored Procedure In Management Studio Express (Part 2)

Jan 23, 2008

Hi Jonathan Kehayias, Thanks for your valuable response.

I had a hard time to sumbit my reply in that original thread yesterday. So I created this new thread.

Here is my response to the last code/instruction you gave me:

I corrected a small mistake (on Integrated Security-SSPI and executed the last code you gave me.

I got the following debug error message:

1) A Box appeared and said: String or binary data would be truncated.

The statement has been terminated.

|OK|

2) After I clicked on the |OK| button, the following message appeared:

This "SqlException was unhandled

String or binary data would be truncated.

The statement has been terminated."

is pointing to the "Throw" code statement in the middle of

.......................................

Catch ex As Exception

MessageBox.Show(ex.Message)

Throw

Finally

..........

Please help and advise how to correct this problem in my project that is executed in my VB 2005 Express-SQL Server Management Studio Express PC.



Thanks,
Scott Chang

The code of my Form1.vb is listed below:

Imports System.Data

Imports System.Data.SqlClient

Imports System.Data.SqlTypes

Public Class Form1

Public Sub InsertNewFriend()

Dim connectionString As String = "Data Source=.SQLEXPRESS;Initial Catalog=shcDB;Integrated Security=SSPI;"

Dim connection As SqlConnection = New SqlConnection(connectionString)

Try

connection.Open()

Dim command As SqlCommand = New SqlCommand("sp_insertNewRecord", connection)

command.CommandType = CommandType.StoredProcedure

command.Parameters.Add("@procPersonID", SqlDbType.Int).Value = 7

command.Parameters.Add("@procFirstName", SqlDbType.NVarChar).Value = "Craig"

command.Parameters.Add("@procLastName", SqlDbType.NVarChar).Value = "Utley"

command.Parameters.Add("@procAddress", SqlDbType.NVarChar).Value = "5577 Baltimore Ave"

command.Parameters.Add("@procCity", SqlDbType.NVarChar).Value = "Ellicott City"

command.Parameters.Add("@procState", SqlDbType.NVarChar).Value = "MD"

command.Parameters.Add("@procZipCode", SqlDbType.NVarChar).Value = "21045"

command.Parameters.Add("@procEmail", SqlDbType.NVarChar).Value = "CraigUtley@yahoo.com"

Dim resulting As String = command.ExecuteNonQuery

MessageBox.Show("Row inserted: " + resulting)

Catch ex As Exception

MessageBox.Show(ex.Message)

Throw

Finally

connection.Close()

End Try

End Sub

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

InsertNewFriend()

End Sub

End Class

View 6 Replies View Related

Conditional Execute By Execute SQL Task Return Value?

Jun 25, 2007

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?

View 1 Replies View Related

Create The Package On The Server, And Execute Through Sql Server Agent

Oct 24, 2006

Hi,

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 ;)

Thanks in advance!

Greenies

View 11 Replies View Related

Execute Script To Linked Oracle Server From Sql Server

Feb 26, 2008

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.

PS. posted in another section too

View 1 Replies View Related

SQL Server Execute Sp Permission

May 23, 2008

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?

View 2 Replies View Related

Setup Sql Server Job To Execute VB Exe

Jan 15, 2001

I'm trying to setup SQL Server 7.0 to run a job that will execute a Visual Basic 6.0 exe project. Can someone tell me how to code
this?

View 2 Replies View Related

How Can I Execute The .SQL File In SQL Server

Jun 13, 2002

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.


Thanks in Advance..


Madhavi

View 1 Replies View Related

Execute SP On Linked Server?

Jul 24, 2006

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.

Please help....

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved