?
Solved

MSDE MSSQL Authentication

Posted on 2003-03-18
101
Medium Priority
?
1,240 Views
Last Modified: 2012-05-04
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
0
Comment
Question by:hanneman
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 40
  • 30
  • 19
  • +4
101 Comments
 
LVL 23

Expert Comment

by:adathelad
ID: 8158634
> strDatabase = "c:\MSSQL\DATA\mydata.mdf"

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
0
 
LVL 4

Author Comment

by:hanneman
ID: 8158645
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'.


0
 
LVL 43

Expert Comment

by:Eugene Z
ID: 8158651
"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.mdf"
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 23

Expert Comment

by:adathelad
ID: 8158661
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!

0
 
LVL 23

Expert Comment

by:adathelad
ID: 8158691
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;"
0
 
LVL 4

Author Comment

by:hanneman
ID: 8158695
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;"
0
 
LVL 23

Expert Comment

by:adathelad
ID: 8158704
ok, now try my previous post (use SQL Server account="sa" password=""). See if that works.
0
 
LVL 4

Author Comment

by:hanneman
ID: 8158735
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.
0
 
LVL 4

Author Comment

by:hanneman
ID: 8158744
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;"
0
 
LVL 43

Expert Comment

by:Eugene Z
ID: 8158777
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....
0
 
LVL 4

Author Comment

by:hanneman
ID: 8158779
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'  
0
 
LVL 34

Expert Comment

by:arbert
ID: 8158818
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
0
 
LVL 4

Author Comment

by:hanneman
ID: 8158831
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.
0
 
LVL 43

Expert Comment

by:Eugene Z
ID: 8158851
use 'osql' from DOS
0
 
LVL 4

Author Comment

by:hanneman
ID: 8158877
osql:
runs, prompts for password, wont accept my password, or blank password.
0
 
LVL 43

Expert Comment

by:Eugene Z
ID: 8158878
or free "MSDE Query" GUI from http://www.msde.biz/
0
 
LVL 43

Expert Comment

by:Eugene Z
ID: 8158893
osql:
what about Trusted connection '-E' if you have Windows and SQL authentication..
0
 
LVL 4

Author Comment

by:hanneman
ID: 8158909
I have downloaded MDSE Query twice, both times i get error:
'application failed to initialise'
0
 
LVL 4

Author Comment

by:hanneman
ID: 8158918
osql:
'-E' login fails.
0
 
LVL 43

Expert Comment

by:Eugene Z
ID: 8158982
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
0
 
LVL 23

Expert Comment

by:adathelad
ID: 8158992
Are you sure, you actually have a database called "mydata" on your SQL Server?
0
 
LVL 4

Author Comment

by:hanneman
ID: 8159022
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.

0
 
LVL 23

Expert Comment

by:adathelad
ID: 8159064
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".
0
 
LVL 43

Expert Comment

by:Eugene Z
ID: 8159071
run
osql -SyourSERVER -E -Q"master..sp_helpdb"
To see if "mydata" db on your MSDE
0
 
LVL 13

Expert Comment

by:ispaleny
ID: 8159159
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?


0
 
LVL 13

Expert Comment

by:ispaleny
ID: 8159193
Run REGEDIT and look at node
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer

LoginMode REG_DWORD 0x00000002(2) = mixed mode auth

Good luck!

0
 
LVL 4

Author Comment

by:hanneman
ID: 8159317
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
0
 
LVL 23

Expert Comment

by:adathelad
ID: 8159369
Ok, if you can no .LDF file you need:

Dim oConnection
Set oConnection = Server.CreateObject("ADODB.COnnection")
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
0
 
LVL 4

Author Comment

by:hanneman
ID: 8159425
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.
0
 
LVL 4

Author Comment

by:hanneman
ID: 8159462
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
0
 
LVL 23

Expert Comment

by:adathelad
ID: 8159477

Ok, try this as the SQL statement to execute:

strSQL = "CREATE DATABASE mydata
ON PRIMARY (FILENAME = 'c:\MSSQL\DATA\mydata.mdf')
FOR ATTACH"

0
 
LVL 23

Expert Comment

by:adathelad
ID: 8159498
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.
0
 
LVL 13

Expert Comment

by:ispaleny
ID: 8159547
Try
exec sp_attach_single_file_db @dbname='mydata',@physname='c:\MSSQL\DATA\mydata.mdf'

0
 
LVL 23

Expert Comment

by:adathelad
ID: 8159596
ispaleny: we tried that and it returned an error. See 4-5 posts back :)
0
 
LVL 13

Expert Comment

by:ispaleny
ID: 8159606
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).
0
 
LVL 4

Author Comment

by:hanneman
ID: 8159639
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.
0
 
LVL 13

Expert Comment

by:ispaleny
ID: 8159659
0
 
LVL 23

Expert Comment

by:adathelad
ID: 8159704
once you've opened the connection, try setting the ConnectionTimeout property as below:

oConnection.ConnectionTimeout = 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.
0
 
LVL 13

Expert Comment

by:ispaleny
ID: 8159711
Query SELECT SERVERPROPERTY('PRODUCTLEVEL') should return "SP3".
0
 
LVL 4

Author Comment

by:hanneman
ID: 8159726
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...
0
 
LVL 13

Expert Comment

by:ispaleny
ID: 8159744
It is not MSSQL 2000!!!!!!

See 7.00.623
0
 
LVL 13

Expert Comment

by:ispaleny
ID: 8159752
You have an incorrect MSDE installed!!!!!!!!!!!
0
 
LVL 23

Expert Comment

by:adathelad
ID: 8159764
yes, that could definately be a problem! :D

hannemane - you'd need SQL 2000 on your machine
0
 
LVL 13

Expert Comment

by:ispaleny
ID: 8159767
0
 
LVL 4

Author Comment

by:hanneman
ID: 8159831
will try again when download complete.
thanks everyone! I'll let you know how it goes...
0
 
LVL 23

Expert Comment

by:adathelad
ID: 8159905
good luck with the download :)
I once downloaded a 89MB file via my 56k modem and so I appreciate how painstaking it is!!
0
 
LVL 34

Expert Comment

by:arbert
ID: 8159954
That link is only Service Pack 3 for EXISTING MSDE 2000 installations.....It won't upgrade your 7.0 installation....

Brett
0
 
LVL 13

Expert Comment

by:ispaleny
ID: 8160026
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.
0
 
LVL 4

Author Comment

by:hanneman
ID: 8160030
which link? I dont want to be downloading for 3 hours only to get the wrong file....
0
 
LVL 4

Author Comment

by:hanneman
ID: 8160045
ok, will download the 70MB one. probly more like 4 hours.
0
 
LVL 13

Expert Comment

by:ispaleny
ID: 8160079
0. See you after 4 hours (maybe)
1. Download
2. Unpack
3. Commandline:
 C:\sql2ksp3\MSDE\setup.exe /upgradesp SQLRUN INSTANCENAME=MSSQLSERVER BLANKSAPWD=1
0
 
LVL 3

Expert Comment

by:QJohnson
ID: 8163079
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.
0
 
LVL 4

Author Comment

by:hanneman
ID: 8164898
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....


0
 
LVL 13

Expert Comment

by:ispaleny
ID: 8165212
Did you lose any data?
0
 
LVL 4

Author Comment

by:hanneman
ID: 8165261
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.
0
 
LVL 13

Expert Comment

by:ispaleny
ID: 8165263
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\SOFTWARE\Microsoft\Microsoft SQL Server"
"HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer"

2. Run setup again.
0
 
LVL 4

Author Comment

by:hanneman
ID: 8165323
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server
found.
delete contents of folder, or just delete all subfolders?

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer
not found

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServ80\MSSQLServer
found.
delete contents of folder, or just delete all subfolders?

points up!

0
 
LVL 13

Expert Comment

by:ispaleny
ID: 8165416
These registry things are comming from THE HELL.
I have HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer 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\SOFTWARE\Microsoft\MSSQLServ80
with all children, grandchildren and other relatives!
0
 
LVL 4

Author Comment

by:hanneman
ID: 8165432
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.

0
 
LVL 4

Author Comment

by:hanneman
ID: 8165450
c:\programfiles\microsoft sql server folder is there, but no msde
0
 
LVL 4

Author Comment

by:hanneman
ID: 8165459
how about I try delete all keys again, try and get back to where I was before (old version running) then perform the update...?
0
 
LVL 13

Expert Comment

by:ispaleny
ID: 8165491
Can you run "c:\Program Files\Microsoft SQL Server\mssql\Binn\sqlservr.exe" ?
0
 
LVL 4

Author Comment

by:hanneman
ID: 8165513
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?
0
 
LVL 4

Author Comment

by:hanneman
ID: 8165545
BTW, sa login no longer works in odbc...
0
 
LVL 13

Expert Comment

by:ispaleny
ID: 8165553
Run "c:\Program Files\Microsoft SQL Server\80\Tools\Binn\sqlmangr.exe"
0
 
LVL 13

Expert Comment

by:ispaleny
ID: 8165556
?HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer80\MSSQLServer\LoginMode
0
 
LVL 23

Expert Comment

by:adathelad
ID: 8165559
login "sa"
password ""

should still work. Have you tried connecting to the master database using these login details?
0
 
LVL 13

Expert Comment

by:ispaleny
ID: 8165586
MSDE SP3 is by default in WinAuth.
0
 
LVL 4

Author Comment

by:hanneman
ID: 8165599
sqlmangr.exe runs, but the service will not start.

-----------

you told me:
"Delete
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServ80
with all children, grandchildren and other relatives!"

however
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\login 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."
0
 
LVL 13

Expert Comment

by:ispaleny
ID: 8165620
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\login mode = 1

1. Change to 2
2. Restart SQL Server
3. Login as SA
0
 
LVL 4

Author Comment

by:hanneman
ID: 8165681
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 ?
0
 
LVL 13

Expert Comment

by:ispaleny
ID: 8165728
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'"

0
 
LVL 13

Expert Comment

by:ispaleny
ID: 8165737
Add apostrophe to RESTORE!!!
RESTORE DATABASE mydata FROM DISK='c:\MSSQL2000\DATA\mydata.bak'
0
 
LVL 23

Expert Comment

by:adathelad
ID: 8165743
yes hanneman, try the methods i posted before that you tried last time....fingers crossed, touch wood....
0
 
LVL 4

Author Comment

by:hanneman
ID: 8165804
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"

0
 
LVL 13

Expert Comment

by:ispaleny
ID: 8165827
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
0
 
LVL 4

Author Comment

by:hanneman
ID: 8165874
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...
0
 
LVL 4

Author Comment

by:hanneman
ID: 8165881
more points!!!
0
 
LVL 23

Expert Comment

by:adathelad
ID: 8165900
try using the owner prefix in the select statement for the database owned ones.

e.g.
SELECT * FROM mydata.tblUser

0
 
LVL 13

Expert Comment

by:ispaleny
ID: 8165902
Run
select * from mydata.tblanswers
0
 
LVL 4

Author Comment

by:hanneman
ID: 8165907
Anyone reccommend a good SQL Language syntax website?
or a book?
0
 
LVL 23

Expert Comment

by:adathelad
ID: 8165914
0
 
LVL 4

Author Comment

by:hanneman
ID: 8165918
Using the owner prefix works, but I cannot change every SQL statement in my application...
how to change the owner?
0
 
LVL 13

Expert Comment

by:ispaleny
ID: 8165928
A, Create user
1. exec sp_change_users_login @Action ='Auto_Fix',@UserNamePattern ='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='TblNotificationReq', @newowner='dbo'
exec sp_changeobjectowner @objname='tblsearch', @newowner='dbo'
exec sp_changeobjectowner @objname='TblUser', @newowner='dbo'
exec sp_changeobjectowner @objname='object', @newowner='dbo'

0
 
LVL 4

Author Comment

by:hanneman
ID: 8165980
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...

0
 
LVL 23

Expert Comment

by:adathelad
ID: 8165997
Try:

exec sp_changeobjectowner @objname='mydata.tblanswers', @newowner='dbo'
0
 
LVL 13

Accepted Solution

by:
ispaleny earned 1000 total points
ID: 8166008
Once again, maybe better.

A, Create user
1. exec sp_addlogin 'mydata'
2. exec sp_change_users_login @Action ='Update_One',@UserNamePattern='mydata',@LoginName='mydata'
3. Login as mydata (not sa)

B, OR Run
exec sp_changeobjectowner @objname='mydata.tblanswers', @newowner='dbo'
exec sp_changeobjectowner @objname='mydata.TblAnswers2', @newowner='dbo'
exec sp_changeobjectowner @objname='mydata.TblConference', @newowner='dbo'
exec sp_changeobjectowner @objname='mydata.TblNotificationReq', @newowner='dbo'
exec sp_changeobjectowner @objname='mydata.tblsearch', @newowner='dbo'
exec sp_changeobjectowner @objname='mydata.TblUser', @newowner='dbo'
0
 
LVL 13

Expert Comment

by:ispaleny
ID: 8166016
I think A is the better choice. It will work exactly the same way as on the ISP server.
0
 
LVL 4

Author Comment

by:hanneman
ID: 8166064
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!!!
0
 
LVL 4

Author Comment

by:hanneman
ID: 8166070
ok, I try A
0
 
LVL 4

Author Comment

by:hanneman
ID: 8166096
I changed the ownership of sometables back to mydata, and the created the user. Works fine. 100% working either way!
0
 
LVL 23

Expert Comment

by:adathelad
ID: 8166100
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 :)
0
 
LVL 13

Expert Comment

by:ispaleny
ID: 8166176
adathelad,
I did some calculations.


set dateformat mdy
go
select uid,EP,SubRate,Rate,convert(varchar,HitDate,111)
--uid,EP,SubRate,Rate,HitDate
from (
select uid,EP,SubRate=Rate-Rate_ispa,Rate
,HitDate=case when Rate<Rate_ispa
         then convert(datetime,convert(float,getdate())+(EP-EP_ispa)/(Rate_ispa-Rate))
         end
from
(
select x.uid,x.EP,y.EP as EP_ispa
,Rate=convert(float,x.EP)/(convert(float,getdate()-x.Created))
,Rate_ispa=convert(float,y.EP)/(convert(float,getdate()-y.Created))
from
(
          select 'angelIII' as uid,convert(datetime,'03/09/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/02',0) ,EP= 20046
) x
cross join (select uid='ispaleny',Created =convert(datetime,'12/25/02',0) ,EP= 20046 ) y
) xx) xxx
order by isnull(HitDate,'9999') DESC, case when HitDate is null then -SubRate end
--order by Rate DESC

0
 
LVL 13

Expert Comment

by:ispaleny
ID: 8166227
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!
0
 
LVL 13

Expert Comment

by:ispaleny
ID: 8166482
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)
0
 
LVL 4

Author Comment

by:hanneman
ID: 8166852
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>sqlservr.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....


0
 
LVL 4

Author Comment

by:hanneman
ID: 8166906
it was running already. machine is being disobedient and not showing service manager. everything ok.
0
 
LVL 6

Expert Comment

by:Mindphaser
ID: 8169408
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 **
0
 
LVL 4

Author Comment

by:hanneman
ID: 8169844
Thanks heaps to ispaleny, adathelad and EE. I widh I could give A+!!
0
 
LVL 13

Expert Comment

by:ispaleny
ID: 8170142
Thank you.
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question