hanneman
asked on
MSDE MSSQL Authentication
I am running XP Pro.
I have installed MSDE from the Office XP CD, this includes SQL Server for development, but not the full version.
I have setup user and system DSN's in ODBC for SQL Server, and tested the connections in ODBC, which are successful.
Trying to connect from an asp page (IIS 5.1) using either connection string or dsn, I get a logon failed message from SQL. Using either the 'Windows NT Authentication' or 'SQL Server Authentication', the problem is the same.
My question is:
How (where) can I specify login name? Is there a default login for SQL Server?
here is my connection string:
strSQLServer = "localserver"
strDatabase = "c:\MSSQL\DATA\mydata.mdf"
strUserID = "windowsuser"
strPassword = "windowspassword"
strConn = "Provider=SQLOLEDB;" & _
"Data Source=" & strSQLServer & ";" & _
"Initial Catalog=" & strDatabase & ";" & _
"User ID=" & strUserID & ";" & _
"Password=" & strPassword & ";" & _
"Persist Security Info=False;"
However, I am fairly sure that the connection string itself is fine,
I just need the correct username/password, or a way to set SQL Server to accept a username/password combination.
Thanks,
-h
I have installed MSDE from the Office XP CD, this includes SQL Server for development, but not the full version.
I have setup user and system DSN's in ODBC for SQL Server, and tested the connections in ODBC, which are successful.
Trying to connect from an asp page (IIS 5.1) using either connection string or dsn, I get a logon failed message from SQL. Using either the 'Windows NT Authentication' or 'SQL Server Authentication', the problem is the same.
My question is:
How (where) can I specify login name? Is there a default login for SQL Server?
here is my connection string:
strSQLServer = "localserver"
strDatabase = "c:\MSSQL\DATA\mydata.mdf"
strUserID = "windowsuser"
strPassword = "windowspassword"
strConn = "Provider=SQLOLEDB;" & _
"Data Source=" & strSQLServer & ";" & _
"Initial Catalog=" & strDatabase & ";" & _
"User ID=" & strUserID & ";" & _
"Password=" & strPassword & ";" & _
"Persist Security Info=False;"
However, I am fairly sure that the connection string itself is fine,
I just need the correct username/password, or a way to set SQL Server to accept a username/password combination.
Thanks,
-h
ASKER
oops. was typing in example values, .mdf isnt there in my actual connection string.
here is the error message:
Microsoft OLE DB Provider for SQL Server (0x80040E4D)
Login failed for user 'Admin'.
here is the error message:
Microsoft OLE DB Provider for SQL Server (0x80040E4D)
Login failed for user 'Admin'.
"c:\MSSQL\DATA\mydata.mdf" is database data file
must be -> Initial Catalog=<databasename>;
e.g. 'Initial Catalog=PUBS'
(not c:\MSSQL\DATA\pubs_data.md f"
must be -> Initial Catalog=<databasename>;
e.g. 'Initial Catalog=PUBS'
(not c:\MSSQL\DATA\pubs_data.md
Ok...sorry to confirm again, but from what you've said it sounds like you are passing in:
strDatabase = "c:\MSSQL\DATA\mydata"
when there should be no path info in there. I like to clear any confusion!
strDatabase = "c:\MSSQL\DATA\mydata"
when there should be no path info in there. I like to clear any confusion!
Try using an SQL server account.
By default, try account "sa", password ""
strSQLServer = "localserver"
strDatabase = "databasename"
strUserID = "sa"
strPassword = ""
strConn = "Provider=SQLOLEDB;" & _
"Data Source=" & strSQLServer & ";" & _
"Initial Catalog=" & strDatabase & ";" & _
"User ID=" & strUserID & ";" & _
"Password=" & strPassword & ";" & _
"Persist Security Info=False;"
By default, try account "sa", password ""
strSQLServer = "localserver"
strDatabase = "databasename"
strUserID = "sa"
strPassword = ""
strConn = "Provider=SQLOLEDB;" & _
"Data Source=" & strSQLServer & ";" & _
"Initial Catalog=" & strDatabase & ";" & _
"User ID=" & strUserID & ";" & _
"Password=" & strPassword & ";" & _
"Persist Security Info=False;"
ASKER
ok, I have taken out the path data, and database filename extension thanks!
but I still get the login failed error.
strSQLServer = "localserver"
strDatabase = "mydata"
strUserID = "windowsuser"
strPassword = "windowspassword"
strConn = "Provider=SQLOLEDB;" & _
"Data Source=" & strSQLServer & ";" & _
"Initial Catalog=" & strDatabase & ";" & _
"User ID=" & strUserID & ";" & _
"Password=" & strPassword & ";" & _
"Persist Security Info=False;"
but I still get the login failed error.
strSQLServer = "localserver"
strDatabase = "mydata"
strUserID = "windowsuser"
strPassword = "windowspassword"
strConn = "Provider=SQLOLEDB;" & _
"Data Source=" & strSQLServer & ";" & _
"Initial Catalog=" & strDatabase & ";" & _
"User ID=" & strUserID & ";" & _
"Password=" & strPassword & ";" & _
"Persist Security Info=False;"
ok, now try my previous post (use SQL Server account="sa" password=""). See if that works.
ASKER
ok, SQL Server lets me use 'sa' with a blank password in the ODBC, (now set up to use 'SQL Authentication'). However, I am still getting a login error:
Microsoft OLE DB Provider for SQL Server (0x80004005)
Cannot open database requested in login 'mydata'. Login fails.
Microsoft OLE DB Provider for SQL Server (0x80004005)
Cannot open database requested in login 'mydata'. Login fails.
ASKER
connection string:
strSQLServer = "localserver"
strDatabase = "mydata"
strUserID = "sa"
strPassword = ""
strConn = "Provider=SQLOLEDB;" & _
"Data Source=" & strSQLServer & ";" & _
"User ID=" & strUserID & ";" & _
"Password=" & strPassword & ";" & _
"Initial Catalog=" & strDatabase & ";" & _
"Persist Security Info=False;"
strSQLServer = "localserver"
strDatabase = "mydata"
strUserID = "sa"
strPassword = ""
strConn = "Provider=SQLOLEDB;" & _
"Data Source=" & strSQLServer & ";" & _
"User ID=" & strUserID & ";" & _
"Password=" & strPassword & ";" & _
"Initial Catalog=" & strDatabase & ";" & _
"Persist Security Info=False;"
are you sure 'sa' pwd is blank.
do you have EM? - can you register your MSDE and see what login is active or change sa's password or any UID....
do you have EM? - can you register your MSDE and see what login is active or change sa's password or any UID....
ASKER
I think the problem is now in the ODBC setup, if I use one of the databases that comes with SQL server, then it works.
e.g.
strSQLServer = "localserver"
strDatabase = "master"
strUserID = "sa"
strPassword = ""
strConn = "Provider=SQLOLEDB;" & _
"Data Source=" & strSQLServer & ";" & _
"User ID=" & strUserID & ";" & _
"Password=" & strPassword & ";" & _
"Initial Catalog=" & strDatabase & ";" & _
"Persist Security Info=False;"
but when I try to 'attach database filename' using: 'mydata', I get error: 'the database entered is not valid'
e.g.
strSQLServer = "localserver"
strDatabase = "master"
strUserID = "sa"
strPassword = ""
strConn = "Provider=SQLOLEDB;" & _
"Data Source=" & strSQLServer & ";" & _
"User ID=" & strUserID & ";" & _
"Password=" & strPassword & ";" & _
"Initial Catalog=" & strDatabase & ";" & _
"Persist Security Info=False;"
but when I try to 'attach database filename' using: 'mydata', I get error: 'the database entered is not valid'
You cannot use Windows Authentication to connect to MSDE from IIS unless MSDE is running on the same box as IIS--is this how you're configured or is MSDE on a different box?
If you have Enterprise manager, do you actually see a database called MyData?
Brett
If you have Enterprise manager, do you actually see a database called MyData?
Brett
ASKER
MSDE and IIS are running on the same box.
I dont have EM, I am told there is a free download of it on msdn, but i havent found it yet.
I dont have EM, I am told there is a free download of it on msdn, but i havent found it yet.
use 'osql' from DOS
ASKER
osql:
runs, prompts for password, wont accept my password, or blank password.
runs, prompts for password, wont accept my password, or blank password.
or free "MSDE Query" GUI from http://www.msde.biz/
osql:
what about Trusted connection '-E' if you have Windows and SQL authentication..
what about Trusted connection '-E' if you have Windows and SQL authentication..
ASKER
I have downloaded MDSE Query twice, both times i get error:
'application failed to initialise'
'application failed to initialise'
ASKER
osql:
'-E' login fails.
'-E' login fails.
be sure you are in admin group on this box before '-E'
MSDE Query:
What is required to run MSDE Query?
The free Microsoft runtime .NET framework is required.
http://msdn.microsoft.com/library/default.asp?url=/downloads/list/netdevframework.asp
MSDE Query:
What is required to run MSDE Query?
The free Microsoft runtime .NET framework is required.
http://msdn.microsoft.com/library/default.asp?url=/downloads/list/netdevframework.asp
Are you sure, you actually have a database called "mydata" on your SQL Server?
ASKER
osql -E now goes. what should I do from there?
I have MSDE, with the MSSQL Server Running.
I have no idea how put put the database on the server, it is currently in the folder with the default databases.
I have MSDE, with the MSSQL Server Running.
I have no idea how put put the database on the server, it is currently in the folder with the default databases.
That is the reason why you can't connect to it and can connect to the system databases. If you have the .MDF and .LDF files try connecting to the "master" database as you've been able to do, and execute the SQL statement given in the example below.
Just make sure you check the paths i've given below and make sure they match up with your .MDF and .LDF files.
Connection.Open <Your connection string to master database>
Dim strSQL
strSQL = "EXEC sp_attach_db @dbname = N'mydata',
@filename1 = N'c:\MSSQL\DATA\mydata.mdf ',
@filename2 = N'c:\MSSQL\DATA\mydata_log .ldf'"
Connection.Execute strSQL
Once this is done, try connecting to database "mydata".
Just make sure you check the paths i've given below and make sure they match up with your .MDF and .LDF files.
Connection.Open <Your connection string to master database>
Dim strSQL
strSQL = "EXEC sp_attach_db @dbname = N'mydata',
@filename1 = N'c:\MSSQL\DATA\mydata.mdf
@filename2 = N'c:\MSSQL\DATA\mydata_log
Connection.Execute strSQL
Once this is done, try connecting to database "mydata".
run
osql -SyourSERVER -E -Q"master..sp_helpdb"
To see if "mydata" db on your MSDE
osql -SyourSERVER -E -Q"master..sp_helpdb"
To see if "mydata" db on your MSDE
1.
osql -SServerName -Usa -dmaster
or
osql -SServerName -E -dmaster
Replace ServerName and type password on prompt.
Now, if you are connected write folowing query and press <Enter>
use mydata
Any errors occured?
osql -SServerName -Usa -dmaster
or
osql -SServerName -E -dmaster
Replace ServerName and type password on prompt.
Now, if you are connected write folowing query and press <Enter>
use mydata
Any errors occured?
Run REGEDIT and look at node
HKEY_LOCAL_MACHINE\SOFTWAR E\Microsof t\MSSQLSer ver\MSSQLS erver
LoginMode REG_DWORD 0x00000002(2) = mixed mode auth
Good luck!
HKEY_LOCAL_MACHINE\SOFTWAR
LoginMode REG_DWORD 0x00000002(2) = mixed mode auth
Good luck!
ASKER
adathelad:
I dont have the .ldf file.
Tried to run the query (minus the '@filename2='), but got:
ADODB.Connection (0x800A0E78)
Operation is not allowed when the object is closed.
I have stopped and restarted, but same again.
ispaleny:
osql -SServerName -E -dmaster
runs, then I get a prompt:
1>
so i typed in:
1>EXEC sp_attach_db @dbname = N'mydata',
@filename1 = N'c:\MSSQL\DATA\mydata.mdf '
but it just goes to the second line:
2>
I dont think it is the permissions now, I think adathelad got me past that with 'sa', but now I am stuck with how to put the database onto the server.
I will up points....
Thanks everyone so far!
-h
I dont have the .ldf file.
Tried to run the query (minus the '@filename2='), but got:
ADODB.Connection (0x800A0E78)
Operation is not allowed when the object is closed.
I have stopped and restarted, but same again.
ispaleny:
osql -SServerName -E -dmaster
runs, then I get a prompt:
1>
so i typed in:
1>EXEC sp_attach_db @dbname = N'mydata',
@filename1 = N'c:\MSSQL\DATA\mydata.mdf
but it just goes to the second line:
2>
I dont think it is the permissions now, I think adathelad got me past that with 'sa', but now I am stuck with how to put the database onto the server.
I will up points....
Thanks everyone so far!
-h
Ok, if you can no .LDF file you need:
Dim oConnection
Set oConnection = Server.CreateObject("ADODB .COnnectio n")
oConnection.open <Your connection string to master database>
Dim strSQL
strSQL = "EXEC sp_attach_single_file_db @dbname = 'mydata',
@physname = 'c:\MSSQL\DATA\mydata.mdf' "
oConnection.Execute strSQL
Dim oConnection
Set oConnection = Server.CreateObject("ADODB
oConnection.open <Your connection string to master database>
Dim strSQL
strSQL = "EXEC sp_attach_single_file_db @dbname = 'mydata',
@physname = 'c:\MSSQL\DATA\mydata.mdf'
oConnection.Execute strSQL
ASKER
we are getting there, but I think there is a problem with the .mdf file itself.
I ran:
"EXEC sp_attach_single_file_db @dbname = 'mydata',
@physname = 'c:\MSSQL\DATA\mydata.mdf' "
heres the new error:
Microsoft OLE DB Provider for SQL Server (0x80040E14)
The header for file 'c:\MSSQL\DATA\mydata.mdf' is not a valid database file header. The PageAudit property is incorrect.
I ran:
"EXEC sp_attach_single_file_db @dbname = 'mydata',
@physname = 'c:\MSSQL\DATA\mydata.mdf'
heres the new error:
Microsoft OLE DB Provider for SQL Server (0x80040E14)
The header for file 'c:\MSSQL\DATA\mydata.mdf'
ASKER
The .mdf is a file that I requested from my webhost. I was sick of developing on Access and deploying on SQL. Its not a service they usually provide and im loath to ask for another file. the only other file I can get my hands on is the mydata.bak. dont suppose thats any use?
more points adathelad? :-p
more points adathelad? :-p
Ok, try this as the SQL statement to execute:
strSQL = "CREATE DATABASE mydata
ON PRIMARY (FILENAME = 'c:\MSSQL\DATA\mydata.mdf'
FOR ATTACH"
If that doesn't work, we can try restoring the "mydata.bak" file:
strSQL = "RESTORE DATABASE mydata
FROM DISK = 'c:\MSSQL\DATA\mydata.bak' "
I've assumed the .bak file is in the same location.
Let me know how it goes.
strSQL = "RESTORE DATABASE mydata
FROM DISK = 'c:\MSSQL\DATA\mydata.bak'
I've assumed the .bak file is in the same location.
Let me know how it goes.
Try
exec sp_attach_single_file_db @dbname='mydata',@physname ='c:\MSSQL \DATA\myda ta.mdf'
exec sp_attach_single_file_db @dbname='mydata',@physname
ispaleny: we tried that and it returned an error. See 4-5 posts back :)
It can be a version conflict. You can upgrade database file, but you cannot downgrade it.
Run: SELECT @@VERSION
If it returns a value under 8.00.760, download and run a service pack (SP3).
Run: SELECT @@VERSION
If it returns a value under 8.00.760, download and run a service pack (SP3).
ASKER
how it went:
strSQL = "CREATE DATABASE mydata
ON PRIMARY (FILENAME = 'c:\MSSQL\DATA\mydata.mdf' )
FOR ATTACH"
result:
The header for file 'c:\MSSQL7\DATA\mydata.mdf ' is not a valid database file header. The PageAudit property is incorrect.
strSQL = "RESTORE DATABASE mydata FROM DISK = 'c:\MSSQL7\DATA\mydata.bak '
result:
Timeout Expired.
Now that I think about it, being able to use the restore option would be the best, as I can easily download the daily .bak of the database....
so close! btw, its a 10Mb DB.
strSQL = "CREATE DATABASE mydata
ON PRIMARY (FILENAME = 'c:\MSSQL\DATA\mydata.mdf'
FOR ATTACH"
result:
The header for file 'c:\MSSQL7\DATA\mydata.mdf
strSQL = "RESTORE DATABASE mydata FROM DISK = 'c:\MSSQL7\DATA\mydata.bak
result:
Timeout Expired.
Now that I think about it, being able to use the restore option would be the best, as I can easily download the daily .bak of the database....
so close! btw, its a 10Mb DB.
link to SP3 - English 56 MB
http://download.microsoft.com/download/e/9/4/e943e32d-1e1c-4700-abd9-4b3df9c9c495/sql2ksp3.exe
http://download.microsoft.com/download/e/9/4/e943e32d-1e1c-4700-abd9-4b3df9c9c495/sql2ksp3.exe
once you've opened the connection, try setting the ConnectionTimeout property as below:
oConnection.ConnectionTime out = 60
This will mean it will only time out after 60 seconds, as opposed to the default 15. See if that helps. It shouldn't take too long though.
oConnection.ConnectionTime
This will mean it will only time out after 60 seconds, as opposed to the default 15. See if that helps. It shouldn't take too long though.
Query SELECT SERVERPROPERTY('PRODUCTLEV EL') should return "SP3".
ASKER
hmmm.
version on my machine:
Microsoft SQL Server 7.00 - 7.00.623 (Intel X86) Nov 27 1998 22:20:07 Copyright (c) 1988-1998 Microsoft Corporation MSDE on Windows NT 5.1 (Build 2600: )
version of online SQL Server:
Microsoft SQL Server 2000 - 8.00.665 (Intel X86) Jul 29 2002 15:02:32 Copyright (c) 1988-2000 Microsoft Corporation Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 3)
56MB. hmmm. I hate 56k sometimes... but its free...
version on my machine:
Microsoft SQL Server 7.00 - 7.00.623 (Intel X86) Nov 27 1998 22:20:07 Copyright (c) 1988-1998 Microsoft Corporation MSDE on Windows NT 5.1 (Build 2600: )
version of online SQL Server:
Microsoft SQL Server 2000 - 8.00.665 (Intel X86) Jul 29 2002 15:02:32 Copyright (c) 1988-2000 Microsoft Corporation Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 3)
56MB. hmmm. I hate 56k sometimes... but its free...
It is not MSSQL 2000!!!!!!
See 7.00.623
See 7.00.623
You have an incorrect MSDE installed!!!!!!!!!!!
yes, that could definately be a problem! :D
hannemane - you'd need SQL 2000 on your machine
hannemane - you'd need SQL 2000 on your machine
Download this
MSDE 2000 + SP3 = 70 MB
http://download.microsoft.com/download/e/9/4/e943e32d-1e1c-4700-abd9-4b3df9c9c495/SQL2KDeskSP3.exe
MSDE 2000 + SP3 = 70 MB
http://download.microsoft.com/download/e/9/4/e943e32d-1e1c-4700-abd9-4b3df9c9c495/SQL2KDeskSP3.exe
ASKER
will try again when download complete.
thanks everyone! I'll let you know how it goes...
thanks everyone! I'll let you know how it goes...
good luck with the download :)
I once downloaded a 89MB file via my 56k modem and so I appreciate how painstaking it is!!
I once downloaded a 89MB file via my 56k modem and so I appreciate how painstaking it is!!
That link is only Service Pack 3 for EXISTING MSDE 2000 installations.....It won't upgrade your 7.0 installation....
Brett
Brett
SQL2kdesksp3.exe includes all of the files that are required to install a new instance of Desktop Engine (.msi files), to upgrade all existing instances of Desktop Engine (.msp files), as well as to consume merge modules (.msm files) into applications.
ASKER
which link? I dont want to be downloading for 3 hours only to get the wrong file....
ASKER
ok, will download the 70MB one. probly more like 4 hours.
0. See you after 4 hours (maybe)
1. Download
2. Unpack
3. Commandline:
C:\sql2ksp3\MSDE\setup.exe /upgradesp SQLRUN INSTANCENAME=MSSQLSERVER BLANKSAPWD=1
1. Download
2. Unpack
3. Commandline:
C:\sql2ksp3\MSDE\setup.exe
You can do an awful lot of poking around on your own with MSDE with the SQL-DMO library. It gives you access to all the functionality available in Enterprise Manager. In a clean MSDE install, all you really need is the defaul user name (sa) and the blank password to get the DMO running and it will provide you a list of "register servers" that will include your MSDE engine. So you can get the Server name from there if you forget it.
Once you have a server name, you can very easily write code to inspect, create, and edit logins. You can really do anything you could do with EM. So you can build some great little utilities for your development environment.
Once you have a server name, you can very easily write code to inspect, create, and edit logins. You can really do anything you could do with EM. So you can build some great little utilities for your development environment.
ASKER
downloaded.
unpacked.
clicked on setup.exe, "Incorrect Instance Name"
uninstalled msde.
clicked on setup.exe, "Incorrect Instance Name"
run command line:
C:\sql2ksp3\MSDE\setup.exe SQLRUN INSTANCENAME=MSSQLSERVER BLANKSAPWD=1
"Incorrect Commandline Parameters"
run commandline:
setup.exe INSTANCENAME=MSSQLSERVER BLANKSAPWD=1
Installed, except, That I could not find any folders, nor could I find a way of starting the SQL Service Manager.
Could not see any way of uninstalling sp3.
Installed previous version of MSDE, errors during install:
-vswitch.
Now I dont seem to have either installed properly nor do I know how to uninstall either of them, as I cant find them in add/remove, nor canI find an uninstall file.
sorry people, I seem to have managed to mess this one up nicely....
unpacked.
clicked on setup.exe, "Incorrect Instance Name"
uninstalled msde.
clicked on setup.exe, "Incorrect Instance Name"
run command line:
C:\sql2ksp3\MSDE\setup.exe
"Incorrect Commandline Parameters"
run commandline:
setup.exe INSTANCENAME=MSSQLSERVER BLANKSAPWD=1
Installed, except, That I could not find any folders, nor could I find a way of starting the SQL Service Manager.
Could not see any way of uninstalling sp3.
Installed previous version of MSDE, errors during install:
-vswitch.
Now I dont seem to have either installed properly nor do I know how to uninstall either of them, as I cant find them in add/remove, nor canI find an uninstall file.
sorry people, I seem to have managed to mess this one up nicely....
Did you lose any data?
ASKER
umm, not sure what you mean. I still have all the original .mdf and .bak files (I was working with copies...) So the database files should be okay.
And I still have all the sp3 files.
And I still have all the sp3 files.
Upgrade from MSDE 7.0 worked better than a simple MSDE 2K installation. Anyway, you want install MSDE 2K.
Problem can be fixed in registry.
1. Use regedit to delete all keys in
"HKEY_LOCAL_MACHINE\SOFTWA RE\Microso ft\Microso ft SQL Server"
"HKEY_LOCAL_MACHINE\SOFTWA RE\Microso ft\MSSQLSe rver"
2. Run setup again.
Problem can be fixed in registry.
1. Use regedit to delete all keys in
"HKEY_LOCAL_MACHINE\SOFTWA
"HKEY_LOCAL_MACHINE\SOFTWA
2. Run setup again.
ASKER
HKEY_LOCAL_MACHINE\SOFTWAR E\Microsof t\Microsof t SQL Server
found.
delete contents of folder, or just delete all subfolders?
HKEY_LOCAL_MACHINE\SOFTWAR E\Microsof t\MSSQLSer ver
not found
HKEY_LOCAL_MACHINE\SOFTWAR E\Microsof t\MSSQLSer v80\MSSQLS erver
found.
delete contents of folder, or just delete all subfolders?
points up!
found.
delete contents of folder, or just delete all subfolders?
HKEY_LOCAL_MACHINE\SOFTWAR
not found
HKEY_LOCAL_MACHINE\SOFTWAR
found.
delete contents of folder, or just delete all subfolders?
points up!
These registry things are comming from THE HELL.
I have HKEY_LOCAL_MACHINE\SOFTWAR E\Microsof t\MSSQLSer ver everywhere, in every installation. But when I come to this forum ALL USERS have it elsewhere. One in Cars, another in MSSQLServ80.
Delete
HKEY_LOCAL_MACHINE\SOFTWAR E\Microsof t\MSSQLSer v80
with all children, grandchildren and other relatives!
I have HKEY_LOCAL_MACHINE\SOFTWAR
Delete
HKEY_LOCAL_MACHINE\SOFTWAR
with all children, grandchildren and other relatives!
ASKER
deleted all keys.
ran:
setup.exe INSTANCENAME=MSSQLSERVER BLANKSAPWD=1
Installed, except, That I can not find any folders, nor can I find a way of starting the SQL Service Manager.
ran:
setup.exe INSTANCENAME=MSSQLSERVER BLANKSAPWD=1
Installed, except, That I can not find any folders, nor can I find a way of starting the SQL Service Manager.
ASKER
c:\programfiles\microsoft sql server folder is there, but no msde
ASKER
how about I try delete all keys again, try and get back to where I was before (old version running) then perform the update...?
Can you run "c:\Program Files\Microsoft SQL Server\mssql\Binn\sqlservr .exe" ?
ASKER
double clicked it, got a dos pop up, with lots of good messages in it.
2003-03-19 10:20:00.82 server Microsoft SQL Server 2000 - 8.00.760 (Intel X8
6)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Desktop Engine on Windows NT 5.1 (Build 2600: )
2003-03-19 10:20:00.82 server Copyright (C) 1988-2002 Microsoft Corporation.
2003-03-19 10:20:00.82 server All rights reserved.
2003-03-19 10:20:00.82 server Server Process ID is 3432.
2003-03-19 10:20:00.83 server Logging SQL Server messages in file 'C:\Program
Files\Microsoft SQL Server\MSSQL\LOG\ERRORLOG' .
2003-03-19 10:20:00.84 server SQL Server is starting at priority class 'norma
l'(1 CPU detected).
2003-03-19 10:20:00.87 server SQL Server configured for thread mode processin
g.
2003-03-19 10:20:00.88 server Using dynamic lock allocation. [500] Lock Block
s, [1000] Lock Owner Blocks.
2003-03-19 10:20:00.90 spid3 Starting up database 'master'.
2003-03-19 10:20:01.23 server Using 'SSNETLIB.DLL' version '8.0.760'.
2003-03-19 10:20:01.26 spid5 Starting up database 'model'.
2003-03-19 10:20:01.64 server SQL server listening on 172.180.7.189: 1433.
2003-03-19 10:20:01.65 server SQL server listening on 127.0.0.1: 1433.
2003-03-19 10:20:01.67 server SQL server listening on TCP, Shared Memory, Nam
ed Pipes.
2003-03-19 10:20:01.68 server SQL Server is ready for client connections
2003-03-19 10:20:01.68 spid3 Server name is 'THEMIDNIGHT'.
2003-03-19 10:20:01.70 spid3 Skipping startup of clean database id 4
2003-03-19 10:20:02.07 spid5 Clearing tempdb database.
2003-03-19 10:20:03.00 spid5 Starting up database 'tempdb'.
2003-03-19 10:20:03.18 spid3 Recovery complete.
2003-03-19 10:20:03.19 spid3 SQL global counter collection task is created.
and now?
2003-03-19 10:20:00.82 server Microsoft SQL Server 2000 - 8.00.760 (Intel X8
6)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Desktop Engine on Windows NT 5.1 (Build 2600: )
2003-03-19 10:20:00.82 server Copyright (C) 1988-2002 Microsoft Corporation.
2003-03-19 10:20:00.82 server All rights reserved.
2003-03-19 10:20:00.82 server Server Process ID is 3432.
2003-03-19 10:20:00.83 server Logging SQL Server messages in file 'C:\Program
Files\Microsoft SQL Server\MSSQL\LOG\ERRORLOG'
2003-03-19 10:20:00.84 server SQL Server is starting at priority class 'norma
l'(1 CPU detected).
2003-03-19 10:20:00.87 server SQL Server configured for thread mode processin
g.
2003-03-19 10:20:00.88 server Using dynamic lock allocation. [500] Lock Block
s, [1000] Lock Owner Blocks.
2003-03-19 10:20:00.90 spid3 Starting up database 'master'.
2003-03-19 10:20:01.23 server Using 'SSNETLIB.DLL' version '8.0.760'.
2003-03-19 10:20:01.26 spid5 Starting up database 'model'.
2003-03-19 10:20:01.64 server SQL server listening on 172.180.7.189: 1433.
2003-03-19 10:20:01.65 server SQL server listening on 127.0.0.1: 1433.
2003-03-19 10:20:01.67 server SQL server listening on TCP, Shared Memory, Nam
ed Pipes.
2003-03-19 10:20:01.68 server SQL Server is ready for client connections
2003-03-19 10:20:01.68 spid3 Server name is 'THEMIDNIGHT'.
2003-03-19 10:20:01.70 spid3 Skipping startup of clean database id 4
2003-03-19 10:20:02.07 spid5 Clearing tempdb database.
2003-03-19 10:20:03.00 spid5 Starting up database 'tempdb'.
2003-03-19 10:20:03.18 spid3 Recovery complete.
2003-03-19 10:20:03.19 spid3 SQL global counter collection task is created.
and now?
ASKER
BTW, sa login no longer works in odbc...
Run "c:\Program Files\Microsoft SQL Server\80\Tools\Binn\sqlma ngr.exe"
?HKEY_LOCAL_MACHINE\SOFTWA RE\Microso ft\MSSQLSe rver80\MSS QLServer\L oginMode
login "sa"
password ""
should still work. Have you tried connecting to the master database using these login details?
password ""
should still work. Have you tried connecting to the master database using these login details?
MSDE SP3 is by default in WinAuth.
ASKER
sqlmangr.exe runs, but the service will not start.
-----------
you told me:
"Delete
HKEY_LOCAL_MACHINE\SOFTWAR E\Microsof t\MSSQLSer v80
with all children, grandchildren and other relatives!"
however
HKEY_LOCAL_MACHINE\SOFTWAR E\Microsof t\MSSQLSer ver\MSSQLS erver\logi n mode = 1
---------------------
login "sa"
password ""
does not work. not through asp connection string or odbc:
"Login failed for user 'sa'. Reason: Not associated with a trusted SQL Server connection."
-----------
you told me:
"Delete
HKEY_LOCAL_MACHINE\SOFTWAR
with all children, grandchildren and other relatives!"
however
HKEY_LOCAL_MACHINE\SOFTWAR
---------------------
login "sa"
password ""
does not work. not through asp connection string or odbc:
"Login failed for user 'sa'. Reason: Not associated with a trusted SQL Server connection."
HKEY_LOCAL_MACHINE\SOFTWAR E\Microsof t\MSSQLSer ver\MSSQLS erver\logi n mode = 1
1. Change to 2
2. Restart SQL Server
3. Login as SA
1. Change to 2
2. Restart SQL Server
3. Login as SA
ASKER
1. Changed to 2
2. Restarted
3. Login successful
should I setup files and folders and run:
RESTORE DATABASE mydata FROM DISK = 'c:\MSSQL2000\DATA\mydata. bak ?
2. Restarted
3. Login successful
should I setup files and folders and run:
RESTORE DATABASE mydata FROM DISK = 'c:\MSSQL2000\DATA\mydata.
1. bak
CREATE DATABASE mydata
RESTORE DATABASE mydata FROM DISK = 'c:\MSSQL2000\DATA\mydata. bak
2. OR mdf
EXEC sp_attach_single_file_db @dbname = 'mydata',@physname = 'c:\MSSQL\DATA\mydata.mdf' "
CREATE DATABASE mydata
RESTORE DATABASE mydata FROM DISK = 'c:\MSSQL2000\DATA\mydata.
2. OR mdf
EXEC sp_attach_single_file_db @dbname = 'mydata',@physname = 'c:\MSSQL\DATA\mydata.mdf'
Add apostrophe to RESTORE!!!
RESTORE DATABASE mydata FROM DISK='c:\MSSQL2000\DATA\my data.bak'
RESTORE DATABASE mydata FROM DISK='c:\MSSQL2000\DATA\my
yes hanneman, try the methods i posted before that you tried last time....fingers crossed, touch wood....
ASKER
connect to master.
run:
strSQL = "CREATE DATABASE mydata"
oconn.execute(strSQL)
database created. :-)
run:
strSQL = "RESTORE DATABASE ...."
oconn.execute(strSQL)
error:
device activation... physical path
assume:
physical path from ISP is stored in the DB.
recreate physical path on local machine.
run.
DROP...
CREATE...
RESTORE
no errors, but:
some tables working, some get "invalid object"
run:
strSQL = "CREATE DATABASE mydata"
oconn.execute(strSQL)
database created. :-)
run:
strSQL = "RESTORE DATABASE ...."
oconn.execute(strSQL)
error:
device activation... physical path
assume:
physical path from ISP is stored in the DB.
recreate physical path on local machine.
run.
DROP...
CREATE...
RESTORE
no errors, but:
some tables working, some get "invalid object"
Run query:
select u.name as OwnerName,o.name as TableName
from sysobjects o
left join sysusers u on o.uid=u.uid
where xtype='U'
order by u.name,o.name
select u.name as OwnerName,o.name as TableName
from sysobjects o
left join sysusers u on o.uid=u.uid
where xtype='U'
order by u.name,o.name
ASKER
result:
owner, tablename
dbo,TblCountry
dbo,TblDiscipline
dbo,TblNotified
dbo,TblRegion
dbo,TblTransaction
mydata,tblanswers
mydata,TblAnswers2
mydata,TblConference
mydata,TblNotificationReq
mydata,tblsearch
mydata,TblUser
dbo owned tables are working....
table owned by the database are not...
owner, tablename
dbo,TblCountry
dbo,TblDiscipline
dbo,TblNotified
dbo,TblRegion
dbo,TblTransaction
mydata,tblanswers
mydata,TblAnswers2
mydata,TblConference
mydata,TblNotificationReq
mydata,tblsearch
mydata,TblUser
dbo owned tables are working....
table owned by the database are not...
ASKER
more points!!!
try using the owner prefix in the select statement for the database owned ones.
e.g.
SELECT * FROM mydata.tblUser
e.g.
SELECT * FROM mydata.tblUser
Run
select * from mydata.tblanswers
select * from mydata.tblanswers
ASKER
Anyone reccommend a good SQL Language syntax website?
or a book?
or a book?
Try these :)
http://www.w3schools.com/sql/default.asp
www.sql101.com
www.sqlmag.com
msdn.microsoft.com
http://www.w3schools.com/sql/default.asp
www.sql101.com
www.sqlmag.com
msdn.microsoft.com
ASKER
Using the owner prefix works, but I cannot change every SQL statement in my application...
how to change the owner?
how to change the owner?
A, Create user
1. exec sp_change_users_login @Action ='Auto_Fix',@UserNamePatte rn ='mydata'
2. Login as mydata (not sa)
B, OR Run
exec sp_changeobjectowner @objname='tblanswers', @newowner='dbo'
exec sp_changeobjectowner @objname='TblAnswers2', @newowner='dbo'
exec sp_changeobjectowner @objname='TblConference', @newowner='dbo'
exec sp_changeobjectowner @objname='TblNotificationR eq', @newowner='dbo'
exec sp_changeobjectowner @objname='tblsearch', @newowner='dbo'
exec sp_changeobjectowner @objname='TblUser', @newowner='dbo'
exec sp_changeobjectowner @objname='object', @newowner='dbo'
1. exec sp_change_users_login @Action ='Auto_Fix',@UserNamePatte
2. Login as mydata (not sa)
B, OR Run
exec sp_changeobjectowner @objname='tblanswers', @newowner='dbo'
exec sp_changeobjectowner @objname='TblAnswers2', @newowner='dbo'
exec sp_changeobjectowner @objname='TblConference', @newowner='dbo'
exec sp_changeobjectowner @objname='TblNotificationR
exec sp_changeobjectowner @objname='tblsearch', @newowner='dbo'
exec sp_changeobjectowner @objname='TblUser', @newowner='dbo'
exec sp_changeobjectowner @objname='object', @newowner='dbo'
ASKER
Using mydata:
A1:
Microsoft OLE DB Provider for SQL Server (0x80040E14)
Terminating this procedure. The Action 'Auto_Fix' is incompatible with the other parameter values ('mydata', '(null)').
B:
Microsoft OLE DB Provider for SQL Server (0x80040E14)
Object 'tblanswers' does not exist or is not a valid object for this operation.
Using Master:
A1:
Success.
A2:
Login to mydata failed for used mydata
so close...
A1:
Microsoft OLE DB Provider for SQL Server (0x80040E14)
Terminating this procedure. The Action 'Auto_Fix' is incompatible with the other parameter values ('mydata', '(null)').
B:
Microsoft OLE DB Provider for SQL Server (0x80040E14)
Object 'tblanswers' does not exist or is not a valid object for this operation.
Using Master:
A1:
Success.
A2:
Login to mydata failed for used mydata
so close...
Try:
exec sp_changeobjectowner @objname='mydata.tblanswer s', @newowner='dbo'
exec sp_changeobjectowner @objname='mydata.tblanswer
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I think A is the better choice. It will work exactly the same way as on the ISP server.
ASKER
I chose B.
result: 100% working!!!
Firstly:
A great deal of thanks is owed to both adathelad and ispaleny! this is one long PAQ!
Secondly:
How do you think the points should be split?
I am thinking:
ispaleny: 200
adathelad: 150
would that be fair? adathelad already has 50,000!!!
result: 100% working!!!
Firstly:
A great deal of thanks is owed to both adathelad and ispaleny! this is one long PAQ!
Secondly:
How do you think the points should be split?
I am thinking:
ispaleny: 200
adathelad: 150
would that be fair? adathelad already has 50,000!!!
ASKER
ok, I try A
ASKER
I changed the ownership of sometables back to mydata, and the created the user. Works fine. 100% working either way!
Was quite a long post really wasnt it! At least we got there in the end.
Split the points however you feel is right, I don't want to influence you. I won't complain :)
Split the points however you feel is right, I don't want to influence you. I won't complain :)
adathelad,
I did some calculations.
set dateformat mdy
go
select uid,EP,SubRate,Rate,conver t(varchar, HitDate,11 1)
--uid,EP,SubRate,Rate,HitD ate
from (
select uid,EP,SubRate=Rate-Rate_i spa,Rate
,HitDate=case when Rate<Rate_ispa
then convert(datetime,convert(f loat,getda te())+(EP- EP_ispa)/( Rate_ispa- Rate))
end
from
(
select x.uid,x.EP,y.EP as EP_ispa
,Rate=convert(float,x.EP)/ (convert(f loat,getda te()-x.Cre ated))
,Rate_ispa=convert(float,y .EP)/(conv ert(float, getdate()- y.Created) )
from
(
select 'angelIII' as uid,convert(datetime,'03/0 9/00',0) as Created,497075 as EP
union all select 'nigelrivett' ,'09/04/00' ,243957
union all select 'ScottPletcher' ,'09/10/01' ,172421
union all select 'bhess1' ,'11/17/98' ,159295
union all select 'chigrik' ,'11/13/98' ,131519
union all select 'tchalkov' ,'04/10/98' ,111901
union all select 'simonsabin' ,'03/03/99' ,103532
union all select 'acperkins@devx','05/29/01 ' ,87370
union all select 'miron@devx' ,'08/24/01' ,85623
union all select 'spiridonov' ,'08/13/97' ,75921
union all select 'gpbuenrostro' ,'07/29/99' ,50601
union all select 'adathelad' ,'10/11/02' ,47951
union all select 'spcmnspff' ,'09/17/01' ,46730
union all select 'emoreau' ,'08/02/98' ,41595
union all select 'curtis591' ,'02/16/00' ,33098
union all select uid='ispaleny',Created =convert(datetime,'12/25/0 2',0) ,EP= 20046
) x
cross join (select uid='ispaleny',Created =convert(datetime,'12/25/0 2',0) ,EP= 20046 ) y
) xx) xxx
order by isnull(HitDate,'9999') DESC, case when HitDate is null then -SubRate end
--order by Rate DESC
I did some calculations.
set dateformat mdy
go
select uid,EP,SubRate,Rate,conver
--uid,EP,SubRate,Rate,HitD
from (
select uid,EP,SubRate=Rate-Rate_i
,HitDate=case when Rate<Rate_ispa
then convert(datetime,convert(f
end
from
(
select x.uid,x.EP,y.EP as EP_ispa
,Rate=convert(float,x.EP)/
,Rate_ispa=convert(float,y
from
(
select 'angelIII' as uid,convert(datetime,'03/0
union all select 'nigelrivett' ,'09/04/00' ,243957
union all select 'ScottPletcher' ,'09/10/01' ,172421
union all select 'bhess1' ,'11/17/98' ,159295
union all select 'chigrik' ,'11/13/98' ,131519
union all select 'tchalkov' ,'04/10/98' ,111901
union all select 'simonsabin' ,'03/03/99' ,103532
union all select 'acperkins@devx','05/29/01
union all select 'miron@devx' ,'08/24/01' ,85623
union all select 'spiridonov' ,'08/13/97' ,75921
union all select 'gpbuenrostro' ,'07/29/99' ,50601
union all select 'adathelad' ,'10/11/02' ,47951
union all select 'spcmnspff' ,'09/17/01' ,46730
union all select 'emoreau' ,'08/02/98' ,41595
union all select 'curtis591' ,'02/16/00' ,33098
union all select uid='ispaleny',Created =convert(datetime,'12/25/0
) x
cross join (select uid='ispaleny',Created =convert(datetime,'12/25/0
) xx) xxx
order by isnull(HitDate,'9999') DESC, case when HitDate is null then -SubRate end
--order by Rate DESC
Replace getdate() by @getdate and add
declare @getdate datetime set @getdate='03/14/03'
if you want to get correct results.
You are going ahead.
Good luck!
declare @getdate datetime set @getdate='03/14/03'
if you want to get correct results.
You are going ahead.
Good luck!
hanneman,
21 pages, it is really long. I wonder it resulted in a running system. I do not care about points, although it is a nice game. Split the points of your own free choice.
Never spend more for an acquisition than you have to.
(Ferengi - Rules of Acquisition)
21 pages, it is really long. I wonder it resulted in a running system. I do not care about points, although it is a nice game. Split the points of your own free choice.
Never spend more for an acquisition than you have to.
(Ferengi - Rules of Acquisition)
ASKER
You coulda told me that before i kept upping the points!!!
Oh well, easy come easy go.
BTW, SQLserver will not start now:
C:\PROGRA~1\MICROS~4\MSSQL \BINN>sqls ervr.exe
2003-03-19 14:28:03.54 server initerrlog: Could not open error log file 'C:\Program Files\Microsoft SQL Server\MSSQL\LOG\ERRORLOG' . Operating system error =
32(error not found).
hahahahaha! well, I think I have enough here for me to re-install anyway....
Oh well, easy come easy go.
BTW, SQLserver will not start now:
C:\PROGRA~1\MICROS~4\MSSQL
2003-03-19 14:28:03.54 server initerrlog: Could not open error log file 'C:\Program Files\Microsoft SQL Server\MSSQL\LOG\ERRORLOG'
32(error not found).
hahahahaha! well, I think I have enough here for me to re-install anyway....
ASKER
it was running already. machine is being disobedient and not showing service manager. everything ok.
hanneman
You asked to split points between ispaleny (250) and adathelad (100)
I have reduced the points on this question from 350 to 250 as indicated by your request at Community Support. Please copy the URL and create a new question in this topic area for the other Experts to whom you wish to award points. The title of the question should read "Points for", followed by the Expert's name. In the question itself, you should paste the link to the original question and perhaps a comment stating that the points are for their help with that question. Once you have created the new questions, you can go back to the original, and accept the comment from the Expert for whom you did not create a new question. The Experts will comment in your new "Points for" question(s), which you then accept and grade to close.
If you have any questions, please don't hesitate to ask.
Thank you.
** Mindphaser - Community Support Moderator **
I left instructions on how to proceed in the original question.
** Mindphaser - Community Support Moderator **
You asked to split points between ispaleny (250) and adathelad (100)
I have reduced the points on this question from 350 to 250 as indicated by your request at Community Support. Please copy the URL and create a new question in this topic area for the other Experts to whom you wish to award points. The title of the question should read "Points for", followed by the Expert's name. In the question itself, you should paste the link to the original question and perhaps a comment stating that the points are for their help with that question. Once you have created the new questions, you can go back to the original, and accept the comment from the Expert for whom you did not create a new question. The Experts will comment in your new "Points for" question(s), which you then accept and grade to close.
If you have any questions, please don't hesitate to ask.
Thank you.
** Mindphaser - Community Support Moderator **
I left instructions on how to proceed in the original question.
** Mindphaser - Community Support Moderator **
ASKER
Thanks heaps to ispaleny, adathelad and EE. I widh I could give A+!!
Thank you.
When you try to connect to an SQL database, unlike MS Access, you don't specify a database file. You must specify the actual name of the database.
e.g.
strDatabase = "YourDatabaseName"
Cheers