My first SQL server script - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

My first SQL server script

Author

Topic

alexz22
Starting Member

7 Posts

Posted - 2008-01-19 : 13:39:09

I wrote an application in clasic ASP which ties to an Access DB.
Now I;m migrating it to SQL server. I installed the SQL 2005 express edition on my PC and was able to migrate the Access via SSMA for access. I changed my connection string to point to the server

connStr = "server=server IP address, 1030; Integrated Security=True; Initial Catalog=HR"

Seems to connect.
The error I get is on the first query
SQL = "SELECT * FROM Table1"
RS.Open SQL, connStr, 3, 3 <----------------
The error point ot this line,
What should I change it to, in order to execute the query.
Thank you all.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2008-01-19 : 15:26:18

[code]
Dim cmd
Dim rs

Set cmd = Server.CreateObject("ADODB.Command")
Set rs = Server.CreateObject("ADODB.Recordset")

With cmd
.ActiveConnection = connStr
.CommandType = 1
.commandText = "SELECT * FROM Table1"

rs.Open .Execute
End With

If Not rs.Eof Then
' Do something with rs
rs.Close
End If

Set rs = Nothing
Set cmd = Nothing
[/code]

by the way, what is error message?

subrata4allfriends
Starting Member

24 Posts

Posted - 2008-01-19 : 15:27:14

I think ur query is perfect....please check the database connection steps.

Thanks & Regards,
4allfriends.

"Life is not a bed of roses."

alexz22
Starting Member

7 Posts

Posted - 2008-01-19 : 16:08:26

quote:
Originally posted by russell

by the way, what is error message?

Getting somewhere

This is the error before
# Error Type:
Microsoft OLE DB Service Components (0x80040E21)
Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.
C:\...\../config.inc, line 10

This is after i followed you suggestion.

Error Type:
Microsoft OLE DB Service Components (0x80040E21)
Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done..

.ActiveConnection = connStr<---------

BTW the DB is empty

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2008-01-19 : 18:41:10

try getting that , 1030 out of yer conn string

alexz22
Starting Member

7 Posts

Posted - 2008-01-20 : 10:03:14

quote:
Originally posted by russell

try getting that , 1030 out of yer conn string

I tried
connStr = "server=IP address, Integrated Security=True; Initial Catalog=HR"
doesn't work either.
Is there a sampleDB and script I can down load and test somewhere?

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2008-01-20 : 12:13:54

i just gave u one that works. the error isnt the script.

does your windows account have permission on the sql server and the database hr?

try making your connection string look like this
"Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=hr;Data Source=IPAddressHere"

alexz22
Starting Member

7 Posts

Posted - 2008-01-20 : 13:22:21

quote:
Originally posted by russell

i just gave u one that works. the error isnt the script.

does your windows account have permission on the sql server and the database hr?

try making your connection string look like this
"Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=hr;Data Source=IPAddressHere"

I'm running it on my windows XP PC and local IIS console.
I don't think the problem is in the script you gave either.
How do I check for the permissions? sorry for the need to be baby sat through this process.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2008-01-20 : 13:34:17

Ok, for IIS:

go to adminitrative tool, internet information services. right click default web site (assuming that is the one) click properties. go to directory security tab. click Edit button in the "anonymous access and authenmtication control panel"

tick anonymous access then change the account used to YOUR machine login.

Try the conn string i just gave u in last post. if that doesnt work, then u amy not have configured sql server yet. let us know and we can walk u thru that...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources