From Access how to create stored procedure in SQL server

V

Vinod

Hi All,

Can any one help me out in creation of stored procedure in SQL Server 2005
from Access 2003?

My connection string is working and I'm using pass through query to create a
stored procedure in SQL Server 2005. While executing pass through query I'm
getting following error.

Runtime error: '-2147217900 (80040e14)':
Invalid SQL Statement; expected 'Delete', 'Insert', 'Procedure', 'Select' or
'Update'.

Advanced Thanks
Vinod
 
T

Tom van Stiphout

On Sun, 22 Feb 2009 08:08:01 -0800, Vinod

There are several ways, but this may be the simplest:
Dim conn As ADODB.Connection
Dim sql As String

Set conn = New ADODB.Connection
conn.ConnectionString =
"Provider=SQLNCLI10;Server=myServer;Database=myDatabase;Uid=myUser;
Pwd=myPassword;"
conn.Open
sql = "create procedure dbo.test1 as select * from dbo.Customers"
conn.Execute sql
Set conn = Nothing

(of course you replace myObjectNames with yours)

-Tom.
Microsoft Access MVP
 
V

Vinod

Thanks Tom for your response.

I'm trying like this.
Dim conn As ADODB.Connection
Dim sql As String

Set conn = New ADODB.Connection
conn.ConnectionString = "ODBC;DRIVER={SQL
Server};SERVERSERVERPERSONAL-4B2728\SQLEXPRESS;DATABASE=master;Trusted_Connection"
conn.Open
sql ="Create Procedure dbo.test As Begin Delete * from EMP; Delete * From
temp; End"
conn.Execute sql
Set conn = Nothing

I've been getting following error while executing 'conn.open' as follows:
Run-time error '-2147467259(80004005)':
[Microsoft][ODBC Driver Manager] Data source name not found and no default
driver specified.

Please help me out in resolving above issue which will be appreciated.

Advanced Thanks
Vinod
 
T

Tom van Stiphout

On Sun, 22 Feb 2009 11:20:02 -0800, Vinod

That means your connection string is incorrect. I always use
connectionstrings.com to look them up.

-Tom.
Microsoft Access MVP

Thanks Tom for your response.

I'm trying like this.
Dim conn As ADODB.Connection
Dim sql As String

Set conn = New ADODB.Connection
conn.ConnectionString = "ODBC;DRIVER={SQL
Server};SERVERSERVERPERSONAL-4B2728\SQLEXPRESS;DATABASE=master;Trusted_Connection"
conn.Open
sql ="Create Procedure dbo.test As Begin Delete * from EMP; Delete * From
temp; End"
conn.Execute sql
Set conn = Nothing

I've been getting following error while executing 'conn.open' as follows:
Run-time error '-2147467259(80004005)':
[Microsoft][ODBC Driver Manager] Data source name not found and no default
driver specified.

Please help me out in resolving above issue which will be appreciated.

Advanced Thanks
Vinod


Tom van Stiphout said:
On Sun, 22 Feb 2009 08:08:01 -0800, Vinod

There are several ways, but this may be the simplest:
Dim conn As ADODB.Connection
Dim sql As String

Set conn = New ADODB.Connection
conn.ConnectionString =
"Provider=SQLNCLI10;Server=myServer;Database=myDatabase;Uid=myUser;
Pwd=myPassword;"
conn.Open
sql = "create procedure dbo.test1 as select * from dbo.Customers"
conn.Execute sql
Set conn = Nothing

(of course you replace myObjectNames with yours)

-Tom.
Microsoft Access MVP
Click to expand...
Click to expand...
 
V

Vinod

Thanks Tom,

Connection string it has been working with pass through query with some 'SQL
Server' statements.

For Stored procedure it has not been working.

Please help me out.

Advanced Thanks
Vinod

Tom van Stiphout said:
On Sun, 22 Feb 2009 11:20:02 -0800, Vinod

That means your connection string is incorrect. I always use
connectionstrings.com to look them up.

-Tom.
Microsoft Access MVP

Thanks Tom for your response.

I'm trying like this.
Dim conn As ADODB.Connection
Dim sql As String

Set conn = New ADODB.Connection
conn.ConnectionString = "ODBC;DRIVER={SQL
Server};SERVERSERVERPERSONAL-4B2728\SQLEXPRESS;DATABASE=master;Trusted_Connection"
conn.Open
sql ="Create Procedure dbo.test As Begin Delete * from EMP; Delete * From
temp; End"
conn.Execute sql
Set conn = Nothing

I've been getting following error while executing 'conn.open' as follows:
Run-time error '-2147467259(80004005)':
[Microsoft][ODBC Driver Manager] Data source name not found and no default
driver specified.

Please help me out in resolving above issue which will be appreciated.

Advanced Thanks
Vinod


Tom van Stiphout said:
On Sun, 22 Feb 2009 08:08:01 -0800, Vinod

There are several ways, but this may be the simplest:
Dim conn As ADODB.Connection
Dim sql As String

Set conn = New ADODB.Connection
conn.ConnectionString =
"Provider=SQLNCLI10;Server=myServer;Database=myDatabase;Uid=myUser;
Pwd=myPassword;"
conn.Open
sql = "create procedure dbo.test1 as select * from dbo.Customers"
conn.Execute sql
Set conn = Nothing

(of course you replace myObjectNames with yours)

-Tom.
Microsoft Access MVP


Hi All,

Can any one help me out in creation of stored procedure in SQL Server 2005
from Access 2003?

My connection string is working and I'm using pass through query to create a
stored procedure in SQL Server 2005. While executing pass through query I'm
getting following error.

Runtime error: '-2147217900 (80040e14)':
Invalid SQL Statement; expected 'Delete', 'Insert', 'Procedure', 'Select' or
'Update'.

Advanced Thanks
Vinod
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Create Stored Procedure in SQL server from Access VBA? 4
How to execute SQL stored procedure from Access 8
How to execute SQL strored procedure from Access 6
Connection failed error number 3151 3
Keeping Access Running Between SQL Server Reboots 3
Stored Procedure vs. View for Access 2
Create Stored Procedure from Access VBA? 14
How to call a stored procedure with paramters in SQL Server Express 2008, using code 3

Top