|
Hi,
I need to move several databases to another computer, is there a utility for this on SQL 2005 server? I hope I don't have to recreate the database and tables. I'm new to this, so thanks for the help.
Lou
|
|
|
|
|
I would think the easiest would be to detach them, copy them over, and reattach them. Alternatively, you can back them up and restore them. Both methods are pretty simple.
Scott
|
|
|
|
|
First a given, backup the database. Then you could use the backup to "restore" to the new computer. This works well because it should copy the permissions of the database too.
here is some additional reading from microsoft's site, Hope this helps...
http://support.microsoft.com/kb/314546/en-us
There are other ways as well but this may be the easiest for someone new to SQL server.
|
|
|
|
|
At least it worked, though badly.
For months I've been playing with SQL Server Express 2008 on my server, building databases and connecting to them just to see what happens when I try different things. To be honest, I haven't learned much, since my time is so limited, but it's been fun. Last night I created a new DB with the intent of banging out a tool to use at work for tracking some measurements I have to record. I can add functionality later, but I thought I'd at least get the measurements saved for now.
Building the DB in the management console was a breeze, though tedious, but when I switched back to the client computer, created a skeleton app in C#, and tried to add the DB connection, it all fell apart. It's telling me that the database doesn't exist, or I don't have permission to open it. That's all - no other clues. Yet from the same app and computer, if I try to connect to one of the other DBs I have on the same instance of SQL Server 2008, I get no errors at all. That tells me that SQL ServerX is configured correctly, ie, Remote Connections enabled, TCP/IP enabled and using port 1433, else I'd get the same error from all the other DBs installed. That also implies that the permissions are correct, as I built them all using the same user account, in the same way.
What the heck am I missing here? Nothing on Google or MSDN has helped, not even a clue. Was there a checkbox I missed when creating this new DB that I missed, something that says "Publish this database in the Browser Service?" I don't recall seeing anything like that.
The only change that I've noted is that, since I created the other DBs on the server, several times the Windows Update service has run automatically. Did anything from Microsoft recently change the way SQL Server works? If so, there's no documentation I can find...
Any ideas? Should I just delete the DB and start over? I hate it when I'm all fired up with an idea, motivation at top levels, ready to build something useful, then some crap like this lets all the wind out of my sails.  I've wasted two precious nights on this nonsense, and might not have that luxury again for weeks!
"A Journey of a Thousand Rest Stops Begins with a Single Movement"
|
|
|
|
|
I'd be inclined to think i i your connection, you don't mention testing a connection from your new app to another database.
Next check the credentials you are using have the right permissions, I give my app credentials dbo rights to the database. It's embarrassing the number of times I have to stand back and scratch the head when setting up a new app, it got so bad I set up a shell application with all the needed bits in place and just rename the name space and database when I want to start a new project.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Actually, I tested the connection to all the other databases on the server; they all worked fine. And I do have dbo rights on all of them, including the new one. I'm wondering if something non-obvious went wrong when I created the database on the server. There were no error messages, but 'stuff' happens. Perhaps the best bet is to delete it and recreate it. It's a little one, with just two tables. Frustrating... 
"A Journey of a Thousand Rest Stops Begins with a Single Movement"
|
|
|
|
|
Check the DB security, I don't have a SQL Server on hand right now to check where, but in the management console go to the database, then security and see who has access to it, also at server level check the DB assignments to each security group.
The problem is not related to your app, is in the database security for sure
I want to die like my grandfather- asleep, not like the passengers in his car, screaming!
|
|
|
|
|
I'll do that. It makes sense, in a twisty, Microsoftian way. Since all the DBs were created with the same tool, using the same settings, they should behave identically. But prior to this last one there was an automatic Windows Update. That may have changed some of the default settings in SQL Server. If not, I'm bolluxed; nothing else has changed.
"A Journey of a Thousand Rest Stops Begins with a Single Movement"
|
|
|
|
|
At least a partial solution...
When creating the database in Management Studio, I used the user name RAWRIGHT\Administrator as the owner. But while reviewing the error logs I found many entries corresponding to my attempts to connect locally reporting a logon failure for RAWRIGHT\rawright. Apparently it considered me connecting from the client PC to be a different user than the local one, which makes sense. What doesn't make sense is that the other DBs on the server don't care what login I use. I still haven't solved that mystery.
But for now, I added the RAWRIGHT\rawright user name to the list of LOGIN objects on the server and I can now connect using the Add Database wizard in Visual Studio. I'm still mystified about why the other DBs work fine, while this one has been a nightmare, but at least I can get on with the project.
If anyone has an explanation I'd appreciate hearing it. MSDN and Microsoft Support have apparently never heard that there's a problem, based on the results from Bing. 
"A Journey of a Thousand Rest Stops Begins with a Single Movement"
|
|
|
|
|
Roger Wright wrote: MSDN and Microsoft Support have apparently never heard that there's a problem, based on the results from Bing.
Did you ever consider the possibility of Bing being a bit subjective when looking for MS problems?
|
|
|
|
|
That thought had crossed my mind. I also considered the possibility that Bing is just as useless as the old MSDN Search, but of course discarded that train of thought as unworthy. 
And I posted too soon. Having spent an hour recreating the database, all the tables, and a diagram with a username that works, I returned to Visual Studio and added the connection successfully. Unfortunately, the wizard connects nicely, but reports that the database contains no tables (nor anything else). Thanks a bunch Microsoft, for a week of fun-filled long evenings... 
"A Journey of a Thousand Rest Stops Begins with a Single Movement"
|
|
|
|
|
Then it is time to modify your subject line again.
|
|
|
|
|
It gets better. I had to go back to the server and click on Properties for each table, then individually select every Permission I want to have associated with my username. Talk about tedious! There's about a dozen permissions associated with each table. Surely there must be a simpler way!
But now I can at least see the tables and their columns in Visual Studio; I have yet to discover whether I can actually access them in code. I've never had much luck making the drag and drop method of creating data bindings before - I'm obviously missing something there, since no one else seems to bitch about it as much as I do. But that task is better left for another day...
Progress, however miniscule, is not to be scoffed at, nor pushed too far. It's late, and time for a nap before I really mess something up.
"A Journey of a Thousand Rest Stops Begins with a Single Movement"
|
|
|
|
|
Go back to the server in SSMS, create another SQL ID in the server security, don't assign anything to the id.
Go the the database security and add the user, in the users permissions (where is has public checked) check dbo.
Now test the id by making another connection in SSMS using the new ids creds, make sure you can sees all the tables in the DB etc
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I'm only 300 miles away... 
|
|
|
|
|
I used Temp Table in my procedure, If I like to get values from temp table
in a procedure it shows error.
|
|
|
|
|
I believe no one can help without seeing the error message or the stored procedure you have. Would you mind posting that?
|
|
|
|
|
HI Guys need ur advise here
I am facing this issue that when i use the sp_executesql to execute my statement, the query time is way longer then normal.
Previously i am using this methed in stored procedure
simpely coding the statement
SELECT * FROM Bla Bla
but when i change to using
DECLARE @SQL AS NVARCHAR(MAX)
SET @SQL = 'SELECT * FROM bla bla Table'
EXECUTE sp_executesql @SQL
the query time slow down greatly
i change to using sp_executesql because i need to create the STATEMENT base on some condition checking
will be gald if u guys have any advise
Thanks a million
KaKaShi HaTaKe
|
|
|
|
|
This might be because SQL is not choosing the best execution plan. Check the execution plan which is followed to execute that dynamic query. Although, I am not aware that there is much you can do about this (except for getting rid of dynamic queries if possible), you can check out parameter sniffing if it can help. Here[^] is an article that tells about it.
|
|
|
|
|
Everyone keeps telling me that there is little or no difference between a stored proc and dynamic SQL, then I come across a post like this one and I think they are full of bullshit.
Change your var from nvarchar(max) to varchar(8000), it may help and cannot hurt.
REALLY make sure that you must use dynamic sql, there is often a way to get around using it if you construct your query correctly. Using case, conditional where and even if clauses to avoid dynamic sql.
As danish suggested, take a look at the execution plan of the dynamic and a normal version of the proc and see where you are paying the penalty.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Mycroft Holmes wrote: Everyone keeps telling me that there is little or no difference between a stored proc and dynamic SQL, then I come across a post like this one and I think they are full of bullsh*t.
They obviously are. Just ask a simple question:
What takes more time - cooking and eating or just eating?
They should understand.
|
|
|
|
|
I could connect the database to different server thro' network, but last few days it fails to connect. It populates the Error message Failed to retrieve data for this request (Microsoft.SqlServer.Management.Sdk.Sfc.EnumeratorCore)
To my suprises I could connect the SQL Profiler. If there is network problem it wouldn't connect profiler. I think there is no problem in Network. Could anyone help me out.
Regards,
John.L.Ponratnam
|
|
|
|
|
Quick Google found this: http://forums.asp.net/t/1381599.aspx[^
me, me, me
"The dinosaurs became extinct because they didn't have a space program. And if we become extinct because we don't have a space program, it'll serve us right!"
Larry Niven
|
|
|
|
|
Hi all,
I have a legacy database system which was developed with the old MS Basic PDS ISAM. Does anyone know of a way to import the data into Access?
Thanks
|
|
|
|
|
I don't, but it sounds interesting (at least mildly).
The first step would be to find documentation of the file structure.
Or maybe the Jet Engine can read them? Did you search online?
Edit: Or an ODBC driver?
modified on Wednesday, March 31, 2010 9:44 PM
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
