access the Sql server: 2012

Friday, March 30, 2012

How to auto start mssql service in command line?

Hi,
Could anyone tell, the way to automatically startup SQL
Server service and SQL Agent at mid-night in command line
in SQL Server 2000?
I tried to startup with command servr.exe in Binn
directory and seems like it stop the Launched startup
procedure 'sp_MScleanupmergepublisher'. How can I startup
SQL Server service without single user mode?
Thanks a lot.
-JennyMaybe:
net start mssqlserver
net start sqlserveragent
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Jenny" <jyu@.iseoptions.com> wrote in message
news:428001c47325$f6b44510$a601280a@.phx.gbl...
> Hi,
> Could anyone tell, the way to automatically startup SQL
> Server service and SQL Agent at mid-night in command line
> in SQL Server 2000?
> I tried to startup with command servr.exe in Binn
> directory and seems like it stop the Launched startup
> procedure 'sp_MScleanupmergepublisher'. How can I startup
> SQL Server service without single user mode?
> Thanks a lot.
> -Jenny
>|||Hi,
NET START MSSQLSERVER
NETSTART SQLSERVERAGENT
Put the above commands inside a batch file and schedule to fire at midnight
using OS scheduler. If the service is already running then it will
give an error and exits.
Thanks
Hari
MCDBA
"Jenny" <jyu@.iseoptions.com> wrote in message
news:428001c47325$f6b44510$a601280a@.phx.gbl...
> Hi,
> Could anyone tell, the way to automatically startup SQL
> Server service and SQL Agent at mid-night in command line
> in SQL Server 2000?
> I tried to startup with command servr.exe in Binn
> directory and seems like it stop the Launched startup
> procedure 'sp_MScleanupmergepublisher'. How can I startup
> SQL Server service without single user mode?
> Thanks a lot.
> -Jenny
>|||"Jenny" <jyu@.iseoptions.com> wrote in message
news:428001c47325$f6b44510$a601280a@.phx.gbl...
> Hi,
> Could anyone tell, the way to automatically startup SQL
> Server service and SQL Agent at mid-night in command line
> in SQL Server 2000?
> I tried to startup with command servr.exe in Binn
> directory and seems like it stop the Launched startup
> procedure 'sp_MScleanupmergepublisher'. How can I startup
> SQL Server service without single user mode?
> Thanks a lot.
> -Jenny
>
In SQL 7.0 it was: net stop mssqlserver and net stop mssqlagent
I'll assume it's the same in SQL 2000, and it should be in Books On Line.
hth.
steve.sql

how to auto restart the merge agent

I have a merge replication, the merge agent job failed with:
step 2 :
could not retrieve generation information at the 'Subscriber'. NOTE: The
step was retried the requested number of times (10) without succeeding. The
step failed.
step 3:
The merge agent job failed. The merge process could not retrieve generation
information at the 'Subscriber'. NOTE: The step was retried the requested
number of times (10) without succeeding. The step failed. [SQLSTATE 42000]
(Error 14151). The step failed.
I am not sure what had caused the merge agent to fail but after I restarted
the merge agent , it automatically caught up. I would like to know if there
is a way to automatically re-start the merge agent after the system restored.
Thanks in advance for any help.
Wen Chang
Wen,
I'm not too sure what you mean by setting it off when the system gets
restarted. If you mean have it keep trying, then you could loop the steps in
the merge agent's job. Alternatively you could have it run on a schedule and
not continuously - eg once each minute.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

How to auto increment with character + integer?

Hi,

Can anyone help me? I have created a table and with a primary key called "ID".
However, I want the "ID" be auto increment as well.

Here is my question, how can I do in the following format:

"PIC0001", "PIC0002", "PIC0003", ......... etc.

Anyone can help me?

Thanks.You can create a calculated field as part of a view, using the number from the IDENTITY column appended to the string "PIC".

SELECT 'PIC' + REPLACE(STR(ID,4),' ','0') AS AlphaID|||i'm a rookie...can u explain more detail for this one pls? SELECT 'PIC' + REPLACE(STR(ID,4),' ','0') AS AlphaID|||SELECT 'PIC' + REPLACE(STR(ID,4),' ','0') AS AlphaID

'PIC' is the literal you apparently want at the beginning of the string. ID would be a column of type INT, set to IDENTITY. REPLACE(STR(ID,4),' ','0') takes an int number, like 12, and changes it to '0012', thus for a row with an ID of 12, you would get a column called AlphaID with a value of 'PIC0012'.|||hello
thank you for the support

you mean that i have to make two fields one called ID of type int and the other called alphaid
of type nchar and put the default value of this field as you say?

or i have to put the statment SELECT 'PIC' + REPLACE(STR(ID,4),' ','0') AS AlphaID in the code of the application?

thank you again for the help|||If the prefix is ALWAYS 'PIC', then I would just create a VIEW that has as part of it that SELECT statement. If it varies, then I would create the table with the ID field and the Prefix field, and then combine them in a view.|||You could also implement this as a computed column within the tabl itself.

This approach might add more overhead though, so it depends on how many of your selects would actually be accessing this field directly.

One potential advantage to having the value as a computed column is that, assuming the computed column meets certain criteria, you can index the column. If you are planning on retrieving rows based on this field, this could work to your advantage.|||Actually, given triggers, you could certainly create a "real" field to hold the value and have it populated in the trigger.|||But would you gain anything by going the trigger route? Wouldn't that just add overhead to your inserts? Or would the overhead be comparable to the overhead assiociated with a computed column (at least when maintaining an index on that computed column).

To my mind, it is a LOT easier to configure a computed column for this solution than it is to program a trigger. But, then again, I rarely use triggers, so I might not be the best resource here. All things being equal though (and I'm NOT saying they are; you might have a better idea than me on that one), I'd go with the solution that's easier to implement.|||Presuming the ID number part of the computed column never changes, you would need only an INSERT trigger. In most applications, the number of INSERTs vs. the number of SELECTs is very small, and so the overhead of doing the insert trigger is really small.

I certainly agree that often easier is better. I have not compared performance of these two solutions (though I would likely do a column fed by a trigger).|||I might suggest you use IDENTITY as PK and add 'PIC' and '000..' to the layer(s) that require it.

This is based on the presumption that PIC000n... is a (surrogate) PK with no "real" business meaning and using only int will make the key more efficient.|||I agree, to an extent. If multiple systems, or layers, might be touching this data, then it CAN add some consistency to be able to define this formatting in one central location.

While you could easily add this computed "key" as a property of a class, for example, you might then need to write numerous report which use this key; some might be Crystal reports, some SQL Server reporting services. Some might be generated ad hoc by the end user. IF this is a business-level way of identifying these records, I don't necessarily see a problem with allowing that identifying characteristic to be stored with the record itself.|||hello
thank you for the support
but can you give me more details about how can i do that?

thank you again for th help anf for the time|||Hi,
I have similar question, I have created a table and with a primary key called "ID".
However, I want the "ID" be auto increment as well.
Here is my question, how can I do in the following format:
"1", "2", "3", ......... etc.
Anyone can help me?
Thanks.|||If you just need numbers to autoincrement, set the column to be an IDENTITY column.

How to auto increment an alphanumeric Primary Key in SQL? :(

How to auto increment an alphanumeric Primary Key in SQL? :( Because I want to add something like this in the Primary Key, for example i'll add a new data with an alphanumeric value of ABC-0001, then I'll add another 1, and it auto increments to ABC-0002 and so on.. How can I do it? And if I'll add a new alpha character in the Primary Key field, for example DEF-0001, then I'll add another and it auto increments to 002, and so on, and it will go back to 0001 if i'll use another combination of alpha characters. for example, i'll add a new alpha character AAA, will it go back to 0001 or it will continue?

T___T I hope u get my point.. I want my table to look like this if i have added the data

ABC-0001
ABC-0002
DEF-0001
DEF-0002
AAA-0001

then if il add a new 1, for example ABC

it will auto increment to 0003 in the same field, it will look like this after adding

ABC-0001
ABC-0002
ABC-0003
DEF-0001
DEF-0002
AAA-0001

Will it be possible? :(

hi,

i got wat you wanted to say.but i think auotincrement is possible only with numeric fields,i cant think of any possible algorithm that would solve your problem for autoincrementing alphanumeric field.But one possible solution would be to generate your own primary key.For that to happen first you must split your Alpha-numeric field into two..ie first part will take ABC and second would take ur numeric field 0001 etc...

make your primary key as the combination of both the fields...this is the solution i could think of...

hope you could find,wat you actually wanted..all the best :)

|||

Hey ranjit that's what im thinking off also... bat what if there's a new alpha combination, for example DEF, it will continue to autoincrement, and i think it will not return to 0001.. so what will happen is, if i have added ABC-0001 and ABC-0002, the next one would be
DEF-0003 not DEF-0001

will it be possible to put it back to 0001 if il add a new alpha combination? or very impossible? :(

T___T

|||

to get the primary key... you have to findmax for that perticular string with Query sequence like...

SELECT MAX(CAST(SUBSTRING(id,CHARINDEX('-',id)+1,50) AS INT)) + 1 FROM TBL_Table WHEREID LIKE 'DEF%' [this can be like ..."id like 'ABC%' to get highest value in ABC Sequence].. hope it helps you..

|||

wow thanks for this kaushal.. il try it out :)

How To Auto Increment A Primary ID Field?

I was just wondering on a very simple database table with lets say a primary key set to columb ID and another columb lets say products, can you make the primary key automaticly increment its self whenever a new entry has been put in?

For instance say I have this table set up with ID Being the primary KEY, Columb 1 = ID( INT ), Columb 2 = Products ( VarChar(50) ), and have the fields ID = 1, and products = my product....and if a user inserts a new record say from a gridview or some sort of data entry the second ID Feild will automaticly be 2 and the products gets updated per user input......I'm very sorry but I'm having a hard time putting this into words for some reason..

umm basicly user adds something into the products feild and the ID field automaticly increments one number higher from the last one?

Thanks

Adam.

On the ID column set identity=Yes, and the value will be assigned automatically!

HTH

|||OH MAN your joking right? LOL I must have overlooked something.....because at first I thought that was how you do it but I was not sure and I think I tried setting it to yes in the past but I must have overlooked the values that changed or something....maybe I was having an off day.....but thats perfect thank you so much...|||Actually sorry to bother you again by any change would you know if you can set a primary key and a foreign key inside the same table?|||

This is why I needed my foregn key and primary key things to work...well at least my search's that I do point me in that direction.

I have a couple of listboxs where a user clicks on an option in parent listbox and the results come out on the child list box

Pritty much my problem is that when I use one table and several columbs the first ListBox (ListBox1) which only contained 6 Items had about 30 more Blank (Or Database NULL) fields after the 6 Selections....This was due because some fields were longer then others......The only way I've got around this is to create a different sql datasource for all selections which gives me 7 total sqldatasource on one particular page.....not a very smart coding practise lol...

How to authorize a user only through an application

Hello,
we use Windows Authentification with SQL Server 2005 but we want our users
to access to the database only with an application. We don't want our users
to access to the database with an other application.
What are the possibilities ? Does SQL Server recognize an application with a
signature or something like that ?
Thanks
Best regardsCheck in Books On Line about the use of an 'Application Role'.
An 'Application Role' is a special type of login that may work for your
situation.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Jojonews" <Jojonews@.discussions.microsoft.com> wrote in message
news:46345D65-7404-40EE-BECE-BED51AEC45B1@.microsoft.com...
> Hello,
> we use Windows Authentification with SQL Server 2005 but we want our users
> to access to the database only with an application. We don't want our
> users
> to access to the database with an other application.
> What are the possibilities ? Does SQL Server recognize an application with
> a
> signature or something like that ?
> Thanks
> Best regardssql

How to author reports for Reporting Services?

I have installed and am using Reporting Services.

I can create RDL files using Visual Studio.NET, but like to offload this task to a minion who does not have VS.NET.

Is there a stripped down something-or-other to make reports that and end user or business analyst could use and then upload the reports?

Thanks.You might consider Soft Artisans'OfficeWriter. I personally don't have any experience using Reporting Services with OfficeWriter; I have only used their ExcelWriter product and was very pleased with both it and their customer service.

Terri

How to authenticate users using user login and password stored in SQL database?

Hi. I have a DetailsView with Bound Fields "Login" and "Password". This informations are stored in SQL database. How to solve such authorization? How to compare password stored in database against passowrd typed by user? Is this a good idea to use CustomValidator control to write some checking procedure?. Regards. Pawel.

When you click the login button. Perform a select query to your database that gets the password for the given loginname.

Then compare the two passwords.

Good luck!

How to autenticate to local SQL Express server

I have VWD 2005 Express and SQL 2005 Express installed and I would like to convert my access database to SQL. I tried running the upsizing wizard in access and could not connect to the local sql express server. I downloaded and installed SQL Server Migration assistant for Access and same thing, I can not connect to the server. What credentials do I use, I've tried creating an account on the server using the server management utility and adding the user to every role available but still can not connect to the server.What's the error message? You can test connection of the DSN used in Upsizing Wizard in odbcad32.exe (ODBC Data Source Administrator) to see whether it will succeed.

How to Audit Login, Log off, and Login failure attempts

Hi,
Can anyone provide a pointer on how to enable login, log out, and=
login failure attempts without using the SQL Profiler? I want=
to enable this type of auditing at all times and store the=
results to a table. SQL Profiler does it however I would have=
to keep this tool open all the time in order to get the audit=
results.
Any advice is greatly appreciated. Thanks!
-Lorinda
User submitted from AEWNET (http://www.aewnet.com/)Take a look at sp_trace_create and sp_trace_status in Books Online. You can
create a SQL Agent job to start these on startup or create your own procedur
e
that preps and calls these and mark that proc to start up when SQL Server
does. It basically does the same thing as profiler but is script based
instead of the interactive GUI.
Hope this helps.
Sincerely,
Anthony Thomas
"Guest" wrote:

> Hi,
> Can anyone provide a pointer on how to enable login, log out, and login failure at
tempts without using the SQL Profiler? I want to enable this type of auditing at al
l times and store the results to a table. SQL Profiler does it however I would have
to
keep this tool open all the time in order to get the audit results.
> Any advice is greatly appreciated. Thanks!
> -Lorinda
> User submitted from AEWNET (http://www.aewnet.com/)
>|||Hi,
See this article by Vyas.
http://vyaskn.tripod.com/server_sid..._sql_server.htm
Thanks
Hari
SQL Server MVP
"AnthonyThomas" <AnthonyThomas@.discussions.microsoft.com> wrote in message
news:7938CF87-6D42-43FD-8A54-7F7288BEE53A@.microsoft.com...[vbcol=seagreen]
> Take a look at sp_trace_create and sp_trace_status in Books Online. You
> can
> create a SQL Agent job to start these on startup or create your own
> procedure
> that preps and calls these and mark that proc to start up when SQL Server
> does. It basically does the same thing as profiler but is script based
> instead of the interactive GUI.
> Hope this helps.
> Sincerely,
>
> Anthony Thomas
>
> "Guest" wrote:
>|||Also in SQL Enterprise Manager, right click your server and go to properties
( I think the security tab) ... IN the middle you can set some login
auditing parameters..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Guest" <Guest@.aew_nospam.com> wrote in message
news:%23prHz3gwEHA.1400@.TK2MSFTNGP11.phx.gbl...
Hi,
Can anyone provide a pointer on how to enable login, log out, and login
failure attempts without using the SQL Profiler? I want to enable this type
of auditing at all times and store the results to a table. SQL Profiler
does it however I would have to keep this tool open all the time in order to
get the audit results.
Any advice is greatly appreciated. Thanks!
-Lorinda
User submitted from AEWNET (http://www.aewnet.com/)|||What you require is also what C2 auditing in SQL Server offers (and much
more). This might also help:
http://www.databasejournal.com/feat...cle.php/3399241
Sasan Saidi, MSc in CS
Senior DBA
Brascan Business Services
"I saw it work in a cartoon once so I am pretty sure I can do it."
"Guest" wrote:

> Hi,
> Can anyone provide a pointer on how to enable login, log out, and login failure at
tempts without using the SQL Profiler? I want to enable this type of auditing at al
l times and store the results to a table. SQL Profiler does it however I would have
to
keep this tool open all the time in order to get the audit results.
> Any advice is greatly appreciated. Thanks!
> -Lorinda
> User submitted from AEWNET (http://www.aewnet.com/)
>|||Server side tracing is exactly what I need. Thank you for all your response
s!
-Lorinda
User submitted from AEWNET (http://www.aewnet.com/)

How to Audit Login, Log off, and Login failure attempts

Hi,
Can anyone provide a pointer on how to enable login, log out, and= login failure attempts without using the SQL Profiler? I want= to enable this type of auditing at all times and store the= results to a table. SQL Profiler does it however I would have= to keep this tool open all the time in order to get the audit= results.
Any advice is greatly appreciated. Thanks!
-Lorinda
User submitted from AEWNET (http://www.aewnet.com/)Take a look at sp_trace_create and sp_trace_status in Books Online. You can
create a SQL Agent job to start these on startup or create your own procedure
that preps and calls these and mark that proc to start up when SQL Server
does. It basically does the same thing as profiler but is script based
instead of the interactive GUI.
Hope this helps.
Sincerely,
Anthony Thomas
"Guest" wrote:
> Hi,
> Can anyone provide a pointer on how to enable login, log out, and login failure attempts without using the SQL Profiler? I want to enable this type of auditing at all times and store the results to a table. SQL Profiler does it however I would have to keep this tool open all the time in order to get the audit results.
> Any advice is greatly appreciated. Thanks!
> -Lorinda
> User submitted from AEWNET (http://www.aewnet.com/)
>|||Hi,
See this article by Vyas.
http://vyaskn.tripod.com/server_side_tracing_in_sql_server.htm
Thanks
Hari
SQL Server MVP
"AnthonyThomas" <AnthonyThomas@.discussions.microsoft.com> wrote in message
news:7938CF87-6D42-43FD-8A54-7F7288BEE53A@.microsoft.com...
> Take a look at sp_trace_create and sp_trace_status in Books Online. You
> can
> create a SQL Agent job to start these on startup or create your own
> procedure
> that preps and calls these and mark that proc to start up when SQL Server
> does. It basically does the same thing as profiler but is script based
> instead of the interactive GUI.
> Hope this helps.
> Sincerely,
>
> Anthony Thomas
>
> "Guest" wrote:
>> Hi,
>> Can anyone provide a pointer on how to enable login, log out, and login
>> failure attempts without using the SQL Profiler? I want to enable this
>> type of auditing at all times and store the results to a table. SQL
>> Profiler does it however I would have to keep this tool open all the time
>> in order to get the audit results.
>> Any advice is greatly appreciated. Thanks!
>> -Lorinda
>> User submitted from AEWNET (http://www.aewnet.com/)|||Also in SQL Enterprise Manager, right click your server and go to properties
( I think the security tab) ... IN the middle you can set some login
auditing parameters..
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Guest" <Guest@.aew_nospam.com> wrote in message
news:%23prHz3gwEHA.1400@.TK2MSFTNGP11.phx.gbl...
Hi,
Can anyone provide a pointer on how to enable login, log out, and login
failure attempts without using the SQL Profiler? I want to enable this type
of auditing at all times and store the results to a table. SQL Profiler
does it however I would have to keep this tool open all the time in order to
get the audit results.
Any advice is greatly appreciated. Thanks!
-Lorinda
User submitted from AEWNET (http://www.aewnet.com/)|||What you require is also what C2 auditing in SQL Server offers (and much
more). This might also help:
http://www.databasejournal.com/features/mssql/article.php/3399241
--
Sasan Saidi, MSc in CS
Senior DBA
Brascan Business Services
"I saw it work in a cartoon once so I am pretty sure I can do it."
"Guest" wrote:
> Hi,
> Can anyone provide a pointer on how to enable login, log out, and login failure attempts without using the SQL Profiler? I want to enable this type of auditing at all times and store the results to a table. SQL Profiler does it however I would have to keep this tool open all the time in order to get the audit results.
> Any advice is greatly appreciated. Thanks!
> -Lorinda
> User submitted from AEWNET (http://www.aewnet.com/)
>|||Server side tracing is exactly what I need. Thank you for all your responses!
-Lorinda
User submitted from AEWNET (http://www.aewnet.com/)sql

How to Audit Login, Log off, and Login failure attempts

Hi,
Can anyone provide a pointer on how to enable login, log out, and=
login failure attempts without using the SQL Profiler? I want=
to enable this type of auditing at all times and store the=
results to a table. SQL Profiler does it however I would have=
to keep this tool open all the time in order to get the audit=
results.
Any advice is greatly appreciated. Thanks!
-Lorinda
User submitted from AEWNET (http://www.aewnet.com/)
Take a look at sp_trace_create and sp_trace_status in Books Online. You can
create a SQL Agent job to start these on startup or create your own procedure
that preps and calls these and mark that proc to start up when SQL Server
does. It basically does the same thing as profiler but is script based
instead of the interactive GUI.
Hope this helps.
Sincerely,
Anthony Thomas
"Guest" wrote:

> Hi,
> Can anyone provide a pointer on how to enable login, log out, and login failure attempts without using the SQL Profiler? I want to enable this type of auditing at all times and store the results to a table. SQL Profiler does it however I would have to
keep this tool open all the time in order to get the audit results.
> Any advice is greatly appreciated. Thanks!
> -Lorinda
> User submitted from AEWNET (http://www.aewnet.com/)
>
|||Hi,
See this article by Vyas.
http://vyaskn.tripod.com/server_side...sql_server.htm
Thanks
Hari
SQL Server MVP
"AnthonyThomas" <AnthonyThomas@.discussions.microsoft.com> wrote in message
news:7938CF87-6D42-43FD-8A54-7F7288BEE53A@.microsoft.com...[vbcol=seagreen]
> Take a look at sp_trace_create and sp_trace_status in Books Online. You
> can
> create a SQL Agent job to start these on startup or create your own
> procedure
> that preps and calls these and mark that proc to start up when SQL Server
> does. It basically does the same thing as profiler but is script based
> instead of the interactive GUI.
> Hope this helps.
> Sincerely,
>
> Anthony Thomas
>
> "Guest" wrote:
|||Also in SQL Enterprise Manager, right click your server and go to properties
( I think the security tab) ... IN the middle you can set some login
auditing parameters..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Guest" <Guest@.aew_nospam.com> wrote in message
news:%23prHz3gwEHA.1400@.TK2MSFTNGP11.phx.gbl...
Hi,
Can anyone provide a pointer on how to enable login, log out, and login
failure attempts without using the SQL Profiler? I want to enable this type
of auditing at all times and store the results to a table. SQL Profiler
does it however I would have to keep this tool open all the time in order to
get the audit results.
Any advice is greatly appreciated. Thanks!
-Lorinda
User submitted from AEWNET (http://www.aewnet.com/)
|||What you require is also what C2 auditing in SQL Server offers (and much
more). This might also help:
http://www.databasejournal.com/featu...le.php/3399241
Sasan Saidi, MSc in CS
Senior DBA
Brascan Business Services
"I saw it work in a cartoon once so I am pretty sure I can do it."
"Guest" wrote:

> Hi,
> Can anyone provide a pointer on how to enable login, log out, and login failure attempts without using the SQL Profiler? I want to enable this type of auditing at all times and store the results to a table. SQL Profiler does it however I would have to
keep this tool open all the time in order to get the audit results.
> Any advice is greatly appreciated. Thanks!
> -Lorinda
> User submitted from AEWNET (http://www.aewnet.com/)
>
|||Server side tracing is exactly what I need. Thank you for all your responses!
-Lorinda
User submitted from AEWNET (http://www.aewnet.com/)

how to audit local access to sql server

hello
I am looking for a way to audit only local access to sql server 2000.
that is, I don't care about networked clients logging in to database, I
want to know everything that a user does who logs in at the local
console access.
is there a way to do this without buying an agent? can c2 auditing be
specific and ignore all remote access and only log the local sql server
2k interactions? I don't want to get swamped in a deluge of *all*
activity being logged, my application logs all client access to my
satisfaction. I want to now make sure no one can access database
locally and leave me with no log of activity...direct and local db
access...
thx,
rpf
You can set up a rolling serverside trace that filters on the hostname of
the local server
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"RPF" <richard_p_franklin@.yahoo.com> wrote in message
news:1129408525.096989.140790@.f14g2000cwb.googlegr oups.com...
> hello
> I am looking for a way to audit only local access to sql server 2000.
> that is, I don't care about networked clients logging in to database, I
> want to know everything that a user does who logs in at the local
> console access.
> is there a way to do this without buying an agent? can c2 auditing be
> specific and ignore all remote access and only log the local sql server
> 2k interactions? I don't want to get swamped in a deluge of *all*
> activity being logged, my application logs all client access to my
> satisfaction. I want to now make sure no one can access database
> locally and leave me with no log of activity...direct and local db
> access...
> thx,
> rpf
>
|||I'm interested in this also, but can you explain how exactly to do
this? I admin our IIS server, the SQL server was setup by a consultant
that went out of business. I'm not real good with the SQL server and
don't want to break anything.
Also, will this have any impact on the performance of the SQL server?
|||The easiest way to generate the commands for a serverside trace is to use
the Profiler GUI (Start>Run>Profiler.exe). Select File>New>Trace, put in you
server name and then select the events you are interested in and set the
appropriate filters (click on help on the dialog to get details of what the
tabs do). Once you're happy with your selection click on Run and check that
the required events are being captured. If happy then stop the trace and
goto File>Script Trace>For SQL 2000. This will prompt you to save a sql
file. Open this using Query Analyzer and you will have the template for your
trace. In order to set this up on a rolling basis you will need to wrap the
template in a stored procedure in which you generate the filename (usually
based on the date). In order to start it automatically when sql starts you
can use sp_procoption (see BOL for details). I will try and post an
article/code on my site tonight. There can be a performance impact but it
depends on what you trace. As long as you don't trace statement level events
then the performance impact is generally negligible.
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
<brk100@.gmail.com> wrote in message
news:1129473547.688786.109990@.g14g2000cwa.googlegr oups.com...
> I'm interested in this also, but can you explain how exactly to do
> this? I admin our IIS server, the SQL server was setup by a consultant
> that went out of business. I'm not real good with the SQL server and
> don't want to break anything.
> Also, will this have any impact on the performance of the SQL server?
>

how to audit local access to sql server

hello
I am looking for a way to audit only local access to sql server 2000.
that is, I don't care about networked clients logging in to database, I
want to know everything that a user does who logs in at the local
console access.
is there a way to do this without buying an agent? can c2 auditing be
specific and ignore all remote access and only log the local sql server
2k interactions? I don't want to get swamped in a deluge of *all*
activity being logged, my application logs all client access to my
satisfaction. I want to now make sure no one can access database
locally and leave me with no log of activity...direct and local db
access...
thx,
rpfYou can set up a rolling serverside trace that filters on the hostname of
the local server
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"RPF" <richard_p_franklin@.yahoo.com> wrote in message
news:1129408525.096989.140790@.f14g2000cwb.googlegroups.com...
> hello
> I am looking for a way to audit only local access to sql server 2000.
> that is, I don't care about networked clients logging in to database, I
> want to know everything that a user does who logs in at the local
> console access.
> is there a way to do this without buying an agent? can c2 auditing be
> specific and ignore all remote access and only log the local sql server
> 2k interactions? I don't want to get swamped in a deluge of *all*
> activity being logged, my application logs all client access to my
> satisfaction. I want to now make sure no one can access database
> locally and leave me with no log of activity...direct and local db
> access...
> thx,
> rpf
>|||I'm interested in this also, but can you explain how exactly to do
this? I admin our IIS server, the SQL server was setup by a consultant
that went out of business. I'm not real good with the SQL server and
don't want to break anything.
Also, will this have any impact on the performance of the SQL server?|||The easiest way to generate the commands for a serverside trace is to use
the Profiler GUI (Start>Run>Profiler.exe). Select File>New>Trace, put in you
server name and then select the events you are interested in and set the
appropriate filters (click on help on the dialog to get details of what the
tabs do). Once you're happy with your selection click on Run and check that
the required events are being captured. If happy then stop the trace and
goto File>Script Trace>For SQL 2000. This will prompt you to save a sql
file. Open this using Query Analyzer and you will have the template for your
trace. In order to set this up on a rolling basis you will need to wrap the
template in a stored procedure in which you generate the filename (usually
based on the date). In order to start it automatically when sql starts you
can use sp_procoption (see BOL for details). I will try and post an
article/code on my site tonight. There can be a performance impact but it
depends on what you trace. As long as you don't trace statement level events
then the performance impact is generally negligible.
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
<brk100@.gmail.com> wrote in message
news:1129473547.688786.109990@.g14g2000cwa.googlegroups.com...
> I'm interested in this also, but can you explain how exactly to do
> this? I admin our IIS server, the SQL server was setup by a consultant
> that went out of business. I'm not real good with the SQL server and
> don't want to break anything.
> Also, will this have any impact on the performance of the SQL server?
>

how to audit local access to sql server

hello
I am looking for a way to audit only local access to sql server 2000.
that is, I don't care about networked clients logging in to database, I
want to know everything that a user does who logs in at the local
console access.
is there a way to do this without buying an agent? can c2 auditing be
specific and ignore all remote access and only log the local sql server
2k interactions? I don't want to get swamped in a deluge of *all*
activity being logged, my application logs all client access to my
satisfaction. I want to now make sure no one can access database
locally and leave me with no log of activity...direct and local db
access...
thx,
rpfYou can set up a rolling serverside trace that filters on the hostname of
the local server
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"RPF" <richard_p_franklin@.yahoo.com> wrote in message
news:1129408525.096989.140790@.f14g2000cwb.googlegroups.com...
> hello
> I am looking for a way to audit only local access to sql server 2000.
> that is, I don't care about networked clients logging in to database, I
> want to know everything that a user does who logs in at the local
> console access.
> is there a way to do this without buying an agent? can c2 auditing be
> specific and ignore all remote access and only log the local sql server
> 2k interactions? I don't want to get swamped in a deluge of *all*
> activity being logged, my application logs all client access to my
> satisfaction. I want to now make sure no one can access database
> locally and leave me with no log of activity...direct and local db
> access...
> thx,
> rpf
>|||I'm interested in this also, but can you explain how exactly to do
this? I admin our IIS server, the SQL server was setup by a consultant
that went out of business. I'm not real good with the SQL server and
don't want to break anything.
Also, will this have any impact on the performance of the SQL server?|||The easiest way to generate the commands for a serverside trace is to use
the Profiler GUI (Start>Run>Profiler.exe). Select File>New>Trace, put in you
server name and then select the events you are interested in and set the
appropriate filters (click on help on the dialog to get details of what the
tabs do). Once you're happy with your selection click on Run and check that
the required events are being captured. If happy then stop the trace and
goto File>Script Trace>For SQL 2000. This will prompt you to save a sql
file. Open this using Query Analyzer and you will have the template for your
trace. In order to set this up on a rolling basis you will need to wrap the
template in a stored procedure in which you generate the filename (usually
based on the date). In order to start it automatically when sql starts you
can use sp_procoption (see BOL for details). I will try and post an
article/code on my site tonight. There can be a performance impact but it
depends on what you trace. As long as you don't trace statement level events
then the performance impact is generally negligible.
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
<brk100@.gmail.com> wrote in message
news:1129473547.688786.109990@.g14g2000cwa.googlegroups.com...
> I'm interested in this also, but can you explain how exactly to do
> this? I admin our IIS server, the SQL server was setup by a consultant
> that went out of business. I'm not real good with the SQL server and
> don't want to break anything.
> Also, will this have any impact on the performance of the SQL server?
>

How to Audit DMLs in SQL Server 2005

Hi,

I am looking for solutions of auditing DMLs using SQL Server 2005.
The tasks are:

1) Audit DML statements, including SELECT, INSERT, UPDATE, and DELETE. Please note, not only UPDATE, DELETE and INSERT, but also SELECT
2) Protect the audit trails from insider threats, i.e. protect the audit trails from DBAs.

The questions are:
What would be the solutions recommended by MS?
What are the options, either good or bad, to accomplish auditing DML and protecting audit trails.

Thanks a lot

The most 'robust' and secure method is utilzing one of the third party products designed for that purpose. Any method utilizing server based triggers or stored procedures 'could' be vunerable to DBA mis-use.

Here are links to most of them -there could be additional not on my list.

Audit Tools
ApexSQL Audit http://www.apexsql.com/sql_tools_audit.asp
AuditDatabase (Free Web based trigger generation) http://www.auditdatabase.com/
Lumigent Adit DB http://www.lumigent.com/products/auditdb.html
OmniAudit http://www.krell-software.com/omniaudit/index.asp
SQLLog http://www.rlpsoftware.com/mainframe.asp?contents=SQLLog.asp&mainmenu=SQLLog&submenu=Info
Upscene SQL Log Manager http://www.upscene.com/index.htm?./products/audit/mssqllm_main.htm
DB Audit Expert http://www.softtreetech.com/dbaudit/

|||Hi, Arnie,

Thanks a lot for your prompt response.

I checked out these products and found they can audit INSERT, UPDATE, and DELETE, most via triggers, but they can not audit SELECT.

Please forgive me for a few more questions in that direction:

1) Is there any built-in features of SQL Server 2005 for DML auditing, except database triggers?
2) Can SQL Profiler be used for auditing SELECT, INSERT, UPDATE and DELETE statements?
3) Is there any way to audit SELECT using SQL Server 2005?

Thanks a lot again :-)

|||

I don't think that is exactly correct. Lunigent's Audit tool reads the Transaction log -does not rely on database Triggers, AND can easily track SELECT activity.

You should check it out a bit more. Not inexpensive, but it will be less than the time cost to attempt to create anything that does the job, and whatever is custom created is guaranteed to be less 'robust'.

http://lumigent.com/products/auditdb.html

Your questions...

1. No

2. Yes, but with a measurable performance hit. And would be easily accessible by a DBA since it requires the same level of access to the server as a DBA.

3. No, not directly. SELECT statement 'could' be done through stored procedures, and INSERTs make to a logging table, but again, suseptible to DBA interference.

If you want to be able to log priviledged users (DBA, DBO, etc.) AND data reads, I don't know of any 'secure' method other than the third party products.

|||That is great to know that SQL Profiler can audit SELECT, INSERT,UPDATE and DELETE.

Assuming we ignore the requirement about protecting auditing trails for this moment:

1) Which event(s) should I audit SELECT, UPDATE, DELETE and INSERT against a table using SQL Profiler?
2) Can we audit the (before and after) values of UPDATE, DELETE and INSERT on a table in a trace log?
3) Also I am a little confused: while saying SQL Server 2005 doesn't have built-in auditing DML feature (except triggers), however, SQL Profiler does audit SELECT, UPDATE, DELETE and INSERT. Is there any thing wrong in auditing DML in SQL Profiler, except performance issue? I am not sure if we could say or use SQL Profiler to audit DML.

Yes, I double checked Lunigent's Audit tool, it does audit all DMLs.
Thanks a lot for that information.

I am very new to SQL Server, please forgive me for asking so much questions

|||

Profiler can capture the command and parameters/values that were sent to SQL Server. It does not, however, provide the results (or 'after) state due to data modification -UNLESS there is another specific data read.

Events: TSQL: StmtStarting, TSQL: StmtCompleting

Profiler wasn't designed as a 'Auditing' tool.

Profiler is a bit 'ham handed' compared to the tools specifically designed for auditing.

Profiler requires the same level of permissions as a DBA would have.

Profiler can be easily 'thwarted' by a DBA.

AS you stated your specifications, I highly recommend NOT going further with Profiler for the Auditing task.

You will be investing a lot of time and effort, and it still will not meet your requirements.

|||Thank you very much, Arnie, your comments are very helpful...
sql

How To Audit DML Table Changes w/o Triggers?

Whenever any DML activity occurs in a database I need to audit the following:

1. The table that was changed (INS, UPD, DEL)
2. The data value of the primary key of the changed row

For example, if someone executes:

UPDATE payroll SET Salary = 50000 WHERE empid = 123

I need: "payroll" and "123"

For various business reasons I can't use triggers. It is just one of those things...

I've looked at various options on SQL Profiler and while it looks like I can tell there was an action on the "payroll" table it doesn't look likely that I'll be able to figure out that it was on primary key 123. This seems to especially be the case on the execution of a stored proc where data values are passed as @.parameters.

I know there are third-party products that analyze transaction logs with a GUI and let you export these results to a CSV file or Excel. The concern here is that I need something which is an on-going process, so the GUI and human interaction necessary to generate the file doesn't quite cut it. If I'm wrong here and there's a suggestion I'd certainly look at it.

Thanks so much!

Doug

The options are pretty much what you described. Or you could do this in your code by logging the parameters to the SP that does the modification for example. Allowing ad-hoc insert/update/delete to tables directly is not a good thing to do. In SQL Server 2005, you can use event notifications to do this easily.|||Thanks for the feedback...but I'm not sure I understand you ... I cannot use a trigger ... and the log reading tools don't seem to offer a constant flow. So the items I mention in my post won't work.

Is there any other approach?

Thanks again...I appreciate any suggestions.

Doug|||My point was that your options are limited in SQL Server 2000. I am not aware of the fulll capabilities of various 3rd party tools that work with log files directly. Did you check web site of companies like Lumigent? Another idea I was thinking about was to use replication. You could configure log reader agent to output verbose information which will include commands that are being replicated. Maybe this will help. It is hard to tell. And it almost seems like you have to modify your application if existing tools or methodologies do not meet your requirements.|||OK, this gives me a couple of leads to follow. Thank you very much for thinking this over with me, I appreciate your feedback.

All the best!

Doug

How to audit data flow runs

What I would like is to audit each run of each data flow.
Logging looks almost good enough, except that it will only record the supported system variables, and I want to record some additional info -- at least a user package variable that (I know) specifies what the affected time period is (of the data being flowed).
I could make a custom component (a), which writes an audit record to an audit table, and records the identity key generated, and then attach that identity key as a new output column, so that I could run the data flow through this data flow component, to attach the audit id to every row.
I don't know how to get access from within the custom data flow component to interesting system variables (eg, computer, date the data flow started, execution id...) ?
Also, I'd like to do an update at the end of the data flow, to store the completion time (and whether success or error) back to the same table where I recorded the audit event -- for which I could use that key I got when I added the audit row -- maybe I want to record it into a package variable for later use from the component (a) which created the row.
Does anyone know how I can hook the data flow completion (optimally, hook both success and failure) -- eg, the "OnPostExecute Event" -- I don't know how to write code or script and get it to run at OnPostExecute time, which sounds like the desirable time for me?
Or anyone have better ideas of how I could accomplish my goal here (which is to record data about each run, data consisting of not only the system variables available to the logging, but also at least one user component variable -- and preferably to record this data to an audit table, and then supplement my dataflow with the id of this audit record, so that all stored records point back to it)?
Perry,
You're on exactly the right lines in using the OnPostExecute event handler to achieve what you wnt to achieve here.
I've written a small demo of using event handlers in exactly this situation. You can pick it up from here.

There's a downloadable demo in there plus explanations of exactly what's going on. I hope its useful to you and if its not, please let me know.

-Jamie|||Mark has done something we could use to achieve this. Have a look. It's cool. It works on the similar principal as RowCount, but you can set event hanlder to do the auditing.

http://markiehillmsis.blogspot.com/

Thanks
Sutha

How to audit data flow runs

There's a downloadable demo in there plus explanations of exactly what's going on. I hope its useful to you and if its not, please let me know.

-Jamie|||Mark has done something we could use to achieve this. Have a look. It's cool. It works on the similar principal as RowCount, but you can set event hanlder to do the auditing.

http://markiehillmsis.blogspot.com/

Thanks
Sutha

How to audit all SQL Queries in a database?

How can you audit all queries within a database?

Hi Joshua,

there is builtin functionality for this. Youcan either implement your own logic at your frontend for doing so, using an additional layer between the database on the data components or only use stored procedure to retrieve the data with logging the actions inside the stored procedure.

If you want to use a third party logreader like lumigent you can explore the transaction logs for statements.

-Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Hi Joshua,

Look for SQL Trace or Profiler in Books Online.

|||Audit sounds to me (could be to my non-native language english) as you want to audit or trace SQL queries for a long time. profiler is very costy and should only be started as a debugging tool, not for running it all the time. I just wanted to make sure that you keep that in mind. Otherwise, using the Profiler for a short amount of time to find out problems and queries fired against the database is a usual and daily business for developers and administrators.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de
|||

If your interest this in the control of changes in the registries of a table, you can use triggers to before know the state the data of the registry and despues of being updated.

Respuesta en idioma original:

Si tu interes esta en el control de cambios en los registros de una tabla, puedes utilizar disparadores para conocer el estado de los datos del registro antes y despues de ser actualizado.

|||Profiler is very costly, but you can set up a job to run a trace, just for certain types of events, like Batch Complete and RPC Complete, which will capture all of the queries run. If you script this in a job, then direct the results to a trace file on the hard drive of your server, the cost is minimal. You can then use fn_get_traceinfo to review the trace files, and even load them into a database table for analysis.|||Thanks Allen, this was helpful. I'll give it a try.

how to AttachDbFilename in application's directory

How can I use AttachDbFilename in DbNameDataSet.xsd to access the database in the same directory as the exe file (i.e. the application using it)?You need to specificy AttachDBFilename=True in the connection string of the SQL Connectionsql

How to attach/deattach a Express database to a fullblown SQL Server 2005

Hi. I would like to if its possible to take a Express ".mdf" file and attach it to a full blown SQL Server 2005?, to "attach" it to a real SQL Server 2005?. And how do i do this?. or do i have to do it since there is a "DataDirectory" directive that manage this for me in the connectionstring?, im a little newbie about this :)

You have two options both are covered in the thread below, if you have more questions post again. Hope this helps

http://forums.asp.net/1194348/ShowPost.aspx

How to attach some extra PDF document into my report

I neet to attach ( embed ) some extra documents end of my quote form
conditionaly by Quote Products
please help :-)On Apr 18, 11:00 am, ilker tiryaki
<ilkertiry...@.discussions.microsoft.com> wrote:
> I neet to attach ( embed ) some extra documents end of my quote form
> conditionaly by Quote Products
> please help :-)
As far as I know, the only way to do this would be via an image
control; however, this would add a lot of size to the report, etc. The
best way to handle this is to access the PDF, etc from a custom web
application that also has access to the Report Server. Sorry I could
not be of further assistance.
Regards,
Enrique Martinez
Sr. Software Consultant

How to Attach MS Access Database to MSDE

I have a program installation setup that comes with a default MS Access Database. How do I attach this default database into MSDE on my server?
Message posted via http://www.sqlmonster.com
You cannot attach Access databse file (*.mdb/*.mde) to SQL Server/MSDE, they
are completely different thing , except for being called "database" and
being from MS. But you can use any available tools you know to transfer the
Access database (structure and/or data) to a database on MSDE. If you have
Access2K or later, open the Access *.mdb file, click menu "Tools->Database
Utilities->Upsizing Wizard...". It will lead you to "Upsize" your Access
database to SQL Server/MSDE database. After running the wizard, you may
still need to do some manual changes on the newly created database on MSDE
to make sure PK/Index, data types are OK, and since Access queries may not
may not be transferred into the database on MSDE, you definitely have
re-write some or all of queries you used in the Access file with views or
stored procedures in MSDE.
"Cedric Boo via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:aa84e44efc594341b542ca374dd4a3df@.SQLMonster.c om...
> I have a program installation setup that comes with a default MS Access
Database. How do I attach this default database into MSDE on my server?
> --
> Message posted via http://www.sqlmonster.com
|||Hello,
the upsizing tool crashed for me with a overflow error. I use MSDE2000a, that doesn't include the dtsrunui tool ,( so any help how i get a access db to msde2000a ? I check dtsrun , but im very confused how to use it. How i get the data from the access db,
which is a file not a server like dtsrun expected.
Message posted via http://www.sqlmonster.com
|||hi Alexander,
Alexander Baumgart via SQLMonster.com wrote:
> Hello,
> the upsizing tool crashed for me with a overflow error. I use
> MSDE2000a, that doesn't include the dtsrunui tool ,( so any help how
> i get a access db to msde2000a ? I check dtsrun , but im very
> confused how to use it. How i get the data from the access db, which
> is a file not a server like dtsrun expected.
regarding Access Upswize Wizard problems, please have a look at
http://support.microsoft.com/default...;EN-US;Q237980
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

How to attach mdf files which are residing on other machine

SQL Server 2005 and the related databases are on one server. Due to

space constraints I have to move one of the database(mdf & ldf) to

second server and access the same from the 1st server.

The following are the steps I ned to follow on the 1st server:

Detached the database

Moved the mdf & ldf files to 2nd machine/server

Attach the database pointing to the 2nd server.

The 1st and 2nd steps went fine, the problem is with the 3rd step.

In the Attach Databases window, I clicked on ADD, it opened LOCATE

DATABASE FILE, the folder structure of the 1st server. Is there any way

I can change the SELECTED PATH and poin to the 2nd server?

I did even mapped network drive to the 2nd server but the mapped drive

is not getting displayed in the LOCATE DATABASE FILE folder structure.

Can we attach mdf/ldf files which reside on other machine?

Thanks in advance.

No - you can't use mdf, ldf or ndf files on devices which are not directly attached to SQL Server. It can't resolve shares, drive mappings, or redirects at this low a level.

Buck Woody

|||Network databases are normally not recommended. Unless your network storage meets strict I/O requirements, it's _not_ supported.

That's said, you can mount a network database if you enable trace flag 1807.

http://support.microsoft.com/kb/304261
http://msdn2.microsoft.com/en-us/library/ms176061.aspx
http://www.microsoft.com/sql/alwayson/default.mspx
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx

How to attach mdf files which are residing on other machine

SQL Server 2005 and the related databases are on one server. Due to space
constraints I have to move one of the database(mdf & ldf) to second server
and access the same from the 1st server.
The following are the steps I ned to follow on the 1st server:
1. Detached the database
2. Moved the mdf & ldf files to 2nd machine/server
3. Attach the database pointing to the 2nd server.
The 1st and 2nd steps went fine, the problem is with the 3rd step.
In the Attach Databases window, I clicked on ADD, it opened LOCATE DATABASE
FILE, the folder structure of the 1st server. Is there any way I can change
the SELECTED PATH and poin to the 2nd server?
I did even mapped network drive to the 2nd server but the mapped drive is
not getting displayed in the LOCATE DATABASE FILE folder structure.
Can we attach mdf/ldf files which reside on other machine?
Thanks in advance.> Can we attach mdf/ldf files which reside on other machine?
Short answer: No.
Long answer: Yes, with the proper trace flag, but you don't want to do that (trust me). See
http://support.microsoft.com/?id=304261
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"mallykarjun" <mallykarjuna@.mallykarjuna.com> wrote in message
news:5e451cd9d0a042e2b8571d9eee45d779@.ureader.com...
> SQL Server 2005 and the related databases are on one server. Due to space
> constraints I have to move one of the database(mdf & ldf) to second server
> and access the same from the 1st server.
> The following are the steps I ned to follow on the 1st server:
> 1. Detached the database
> 2. Moved the mdf & ldf files to 2nd machine/server
> 3. Attach the database pointing to the 2nd server.
> The 1st and 2nd steps went fine, the problem is with the 3rd step.
> In the Attach Databases window, I clicked on ADD, it opened LOCATE DATABASE
> FILE, the folder structure of the 1st server. Is there any way I can change
> the SELECTED PATH and poin to the 2nd server?
> I did even mapped network drive to the 2nd server but the mapped drive is
> not getting displayed in the LOCATE DATABASE FILE folder structure.
> Can we attach mdf/ldf files which reside on other machine?
> Thanks in advance.

How to attach mdf files which are residing on other machine

SQL Server 2005 and the related databases are on one server. Due to space
constraints I have to move one of the database(mdf & ldf) to second server
and access the same from the 1st server.
The following are the steps I ned to follow on the 1st server:
1. Detached the database
2. Moved the mdf & ldf files to 2nd machine/server
3. Attach the database pointing to the 2nd server.
The 1st and 2nd steps went fine, the problem is with the 3rd step.
In the Attach Databases window, I clicked on ADD, it opened LOCATE DATABASE
FILE, the folder structure of the 1st server. Is there any way I can change
the SELECTED PATH and poin to the 2nd server?
I did even mapped network drive to the 2nd server but the mapped drive is
not getting displayed in the LOCATE DATABASE FILE folder structure.
Can we attach mdf/ldf files which reside on other machine?
Thanks in advance.> Can we attach mdf/ldf files which reside on other machine?
Short answer: No.
Long answer: Yes, with the proper trace flag, but you don't want to do that
(trust me). See
http://support.microsoft.com/?id=304261
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"mallykarjun" <mallykarjuna@.mallykarjuna.com> wrote in message
news:5e451cd9d0a042e2b8571d9eee45d779@.ur
eader.com...
> SQL Server 2005 and the related databases are on one server. Due to space
> constraints I have to move one of the database(mdf & ldf) to second server
> and access the same from the 1st server.
> The following are the steps I ned to follow on the 1st server:
> 1. Detached the database
> 2. Moved the mdf & ldf files to 2nd machine/server
> 3. Attach the database pointing to the 2nd server.
> The 1st and 2nd steps went fine, the problem is with the 3rd step.
> In the Attach Databases window, I clicked on ADD, it opened LOCATE DATABAS
E
> FILE, the folder structure of the 1st server. Is there any way I can chang
e
> the SELECTED PATH and poin to the 2nd server?
> I did even mapped network drive to the 2nd server but the mapped drive is
> not getting displayed in the LOCATE DATABASE FILE folder structure.
> Can we attach mdf/ldf files which reside on other machine?
> Thanks in advance.

How to attach db with missing log file?

I jad detached a larg db with multiple empty log files.
While moving the db to another server, a drive array onthe
first one went bad, taking one of my log files with it.
How can I re-attach the db and have it ignore that its
missing a log file?
Thank you in advance!Hi
You may be able to use sp_attach_single_file_db, although this would not be
guaranteed.
John
"Merlin" <anonymous@.discussions.microsoft.com> wrote in message
news:bc7201c40dfc$f3419e20$a001280a@.phx.gbl...
> I jad detached a larg db with multiple empty log files.
> While moving the db to another server, a drive array onthe
> first one went bad, taking one of my log files with it.
> How can I re-attach the db and have it ignore that its
> missing a log file?
> Thank you in advance!|||Thank you for your help. sp_attach_single_file_db is not
allowing me to get past the missing log file, either. is
there any other way?

>--Original Message--
>Hi
>You may be able to use sp_attach_single_file_db, although
this would not be
>guaranteed.
>John
>"Merlin" <anonymous@.discussions.microsoft.com> wrote in
message
>news:bc7201c40dfc$f3419e20$a001280a@.phx.gbl...
onthe
>
>.
>|||Hi
Not that I know off. You may want to call Microsoft PSS if going back to the
last backup is not a viable option.
John
"Merlin" <anonymous@.discussions.microsoft.com> wrote in message
news:d87301c40e01$69052ec0$a601280a@.phx.gbl...
> Thank you for your help. sp_attach_single_file_db is not
> allowing me to get past the missing log file, either. is
> there any other way?
>
>
> this would not be
> message
> onthe|||Not supported etc and make sure you have a copy of the mdf before starting
this. Also replace the relavent database,drive letters and filenames with
your particulars as this answer was for a specific case.
1) Make sure you have a copy of PowerDVD301_2_Data.MDF
2) Create a new database called fake (default file locations)
3) Stop SQL Service
4) Delete the fake_Data.MDF and copy PowerDVD301_2_Data.MDF
to where fake_Data.MDF used to be and rename the file to fake_Data.MDF
5) Start SQL Service
6) Database fake will appear as suspect in EM
7) Open Query Analyser and in master database run the following :
sp_configure 'allow updates',1
go
reconfigure with override
go
update sysdatabases set
status=-32768 where dbid=DB_ID('fake')
go
sp_configure 'allow updates',0
go
reconfigure with override
go
This will put the database in emergency recovery mode
8) Stop SQL Service
9) Delete the fake_Log.LDF file
10) Restart SQL Service
11) In QA run the following (with correct path for log)
dbcc rebuild_log('fake','h:\fake_log.ldf')
go
dbcc checkdb('fake') -- to check for errors
go
12) Now we need to rename the files, run the following (make sure
there are no connections to it) in Query Analyser
(At this stage you can actually access the database so you could use
DTS or bcp to move the data to another database .)
use master
go
sp_helpdb 'fake'
go
/* Make a note of the names of the files , you will need them
in the next bit of the script to replace datafilename and
logfilename - it might be that they have the right names */
sp_renamedb 'fake','PowerDVD301'
go
alter database PowerDVD301
MODIFY FILE(NAME='datafilename', NEWNAME = 'PowerDVD301_Data')
go
alter database PowerDVD301
MODIFY FILE(NAME='logfilename', NEWNAME = 'PowerDVD301_Log')
go
dbcc checkdb('PowerDVD301')
go
sp_dboption 'PowerDVD301','dbo use only','false'
go
use PowerDVD301
go
sp_updatestats
go
13) You should now have a working database. However the log file
will be small so it will be worth increasing its size
Unfortunately your files will be called fake_Data.MDF and
fake_Log.LDF but you can get round this by detaching the
database properly and then renaming the files and reattaching
it
14) Run the following in QA
sp_detach_db PowerDVD301
--now rename the files then reattach
sp_attach_db 'PowerDVD301','h:\dvd.mdf','h:\DVD.ldf'
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Merlin" <anonymous@.discussions.microsoft.com> wrote in message
news:d87301c40e01$69052ec0$a601280a@.phx.gbl...
> Thank you for your help. sp_attach_single_file_db is not
> allowing me to get past the missing log file, either. is
> there any other way?
>
>
> this would not be
> message
> onthe|||I fixed this by running dbcc rebuild_log and then running
dbcc checkdb. all is good now. sp_attach_single_file_db
didnt work for me, it was still looking for the missing
log file before letting me attach the db. Thank you for
the help, I appreciated it!!!

>--Original Message--
>Hi
>You may be able to use sp_attach_single_file_db, although
this would not be
>guaranteed.
>John
>"Merlin" <anonymous@.discussions.microsoft.com> wrote in
message
>news:bc7201c40dfc$f3419e20$a001280a@.phx.gbl...
onthe
>
>.
>sql

How to attach an .MDF file to another server?

Hi all

I am having trouble moving an .mdf file from a dev machine to a production machine.

On the dev machine(windows xp sp2 login <MyName>) I have an SSE instance - part of VisualStudio installation.
on the production machine I have only one full version SS instance called "PROD"(windows server 2k3, login Administrator) I do have SSMS installed too.

I create a website in VS on my dev machine. C:\Websites\Website1
I add to App_Data a new file: DB1.mdf
I add a couple of tables to DB1.mdf and maybe other objects etc. - all dbo.
Then I detach DB1.mdf and physically copy the file (without the .ldf as suggested in books online) via tha LAN.

from: C:\Websites\Website1\App_Data\DB1.mdf(dev machine)
to: C:\DB1.mdf (production machine)

I then try to attach C:\DB1.mdf to PROD and I get an error. The server is looking for a db by the name of the original path on the dev machine C:\Websites\Website1\App_Data\DB1.mdf There seems to be no way to rename the db.-not in VS before detaching it, not on PROD in the GUI to attach.

Just for kicks, I created the same folders on the production machine, so the file would have the same physical path as its name(which I still cannot change). C:\Websites\Website1\App_Data\DB1.mdf I even pasted the original .ldf file. This time it worked. I was able to attach it.

What is up with this? Is an .mdf file for ever stuck with the name of the path where it was first created?

This seems to be a most common scenario, even though I am not deploying a whole website.

Are we always supposed to execute our own script?

How are you reattaching the file ?

You should be able to use the command:

CREATE DATABASE database_name
ON
(name = logical_file_name,
filename = 'new file location')
FOR ATTACH

You'll need to know the logical_file_name that the mdf file uses. Find that out by running this command in the database before detaching:

select name from sysfiles where filename like '%.mdf'
|||

If you right click on the databases folder in Object Explorer in SSMS and select Attach... you get a UI to locate the .mdf file. The UI looks for the other files in database (.ndf or .ldf) in the locations specified in the .mdf file. If it doesn't find them there, it displays an error message for the files it can't find.

You can change the location where it is looking for the other files by editing the "Current File Path" in the lower grid. You can also remove the .ldf files from the grid (so it won't try to attach them) by selecting them in the grid and clicking on the Remove button.

Hope this helps,

Steve

|||

Thanks to both of you.

Both ways work now. I can't even duplicate the issue, and I remember I struggled for a while prior to posting a few days ago. It just didn't like that file name. I swear there is a voodoo ghost on the network!

Steven, I also played with some test UDFs and TVFs written in c#. I right-click deployed the assembly in visual studio. The test was to see what all I needed to do when I reattach the .mdf file. Well the functions work on the new machine just like that. So where is the .net assembly? Is the .dll embedded in the .mdf file? Because that is all I transferred.

Carl

how to attach Access database to MSDE

Hello,
what is the procedure to attach a .MDB databse to MSDE?
I don't care using forms or report from the Access database, I just want to
have tables and queries on MSDE then I will analyze data and arrange comments
to them.
Thanks
Rocco
Hi,
You can not attach a Access database to SQL Server MSDE. This is due to
changes in architecture.Methods to transfer:-
1. SQL Server DTS to copy structure and data
2. Upsizing wizard (
http://www.mssqlcity.com/FAQ/General/MigFromAcc.htm )
Thanks
Hari
SQL Server MVP
"rocco" <rocco@.discussions.microsoft.com> wrote in message
news:0D42E485-DCCC-45E8-A2D4-5AE3A298E71A@.microsoft.com...
> Hello,
> what is the procedure to attach a .MDB databse to MSDE?
> I don't care using forms or report from the Access database, I just want
> to
> have tables and queries on MSDE then I will analyze data and arrange
> comments
> to them.
> Thanks
> Rocco
|||OK. ANy tutorial for the DTS? I have opened and since anything is labelled in
that sort of *MicrosoftCreeepingLanguage* I'm not able to use it.
Maybe is not the MicrosoftCreeping thing, but only that I'm not to good at
that!
"Hari Prasad" wrote:

> Hi,
> You can not attach a Access database to SQL Server MSDE. This is due to
> changes in architecture.Methods to transfer:-
> 1. SQL Server DTS to copy structure and data
> 2. Upsizing wizard (
> http://www.mssqlcity.com/FAQ/General/MigFromAcc.htm )
>
> Thanks
> Hari
> SQL Server MVP
>
> "rocco" <rocco@.discussions.microsoft.com> wrote in message
> news:0D42E485-DCCC-45E8-A2D4-5AE3A298E71A@.microsoft.com...
>
>
|||Hi,
This migration is very simple. Just open the Export and Import utility
wizard from SQL Server program group.
Select the source as Access and destination as SQL Server MSDE and select
dbname.
Now select the tables you need to copy. CLick next.
This wizard will automatically drags you till end and will copy the data to
SQL Server. So easy.
Thanks
Hari
SQL Server MVP
"rocco" <rocco@.discussions.microsoft.com> wrote in message
news:4093596D-D547-460D-B761-3F1373169432@.microsoft.com...[vbcol=seagreen]
> OK. ANy tutorial for the DTS? I have opened and since anything is labelled
> in
> that sort of *MicrosoftCreeepingLanguage* I'm not able to use it.
> Maybe is not the MicrosoftCreeping thing, but only that I'm not to good at
> that!
> "Hari Prasad" wrote:

How to attach a MS SQL 2005 database (.mdf) file to MS SQL 2000

I have 2 files from MS SQL 2005, .mdf and .ldf . How am I going to attach it to the MS SQL 2000? Does it need converter? Please guide.
Thanks.

Quote:

Originally Posted by kentwong

I have 2 files from MS SQL 2005, .mdf and .ldf . How am I going to attach it to the MS SQL 2000? Does it need converter? Please guide.
Thanks.

with enterprise tools u can attach the datbase|||You have posted this question in the Articles section. I am moving it to the SQL Server forum.

ADMIN

How to attach a db_file with problems?

A database goes "SUSPECT" the client deatach de DB. later it tries to
reatach it but he gets an error. I tried to copy the file to other disk but
I get an error from windows saying that a cyclic error in the disk prevent
the copy. Is there any way to attach a file with problems? just in order to
save at least some of the data?You can create a new database with the same name, using the same data file
and log file names. Then shut down SQL Server and copy your "bad" files
over the top of these new files. When SQL Server starts the database will
once again be suspect. You can then put the database in emergency mode
(status 32768) and recycle SQL Server. At this point you should be able to
at least salvage some of the data, may be all depending on the rason it
went suspect.
Rand
This posting is provided "as is" with no warranties and confers no rights.|||Hi,
To add on, I had a identical instance where I followed the same procedure as
Rand suggested , after that,
1. The Datbase was able to open in Emergency mode (32768)
2. I was not able to take a backup.
What I did,
1. Create a new database
2. Script out all objects in seperate files
3. Executed the user defined types(UDT) and Table creation script in new
database
4. Run DTS to transfer all data from old to new database ( U can also use
BCP OUT /IN)
5. Executed the Indexe creation script
6. Executed all the other object creation script
After these steps I did a comparison of old and new database and FYI, I lost
few pieces of data which I asked the users to re enter.
Thanks
Hari
MCDBA
"Rand Boyd [MSFT]" <rboyd@.onlinemicrosoft.com> wrote in message
news:M3yLU1E6DHA.2768@.cpmsftngxa07.phx.gbl...

quote:

> You can create a new database with the same name, using the same data file
> and log file names. Then shut down SQL Server and copy your "bad" files
> over the top of these new files. When SQL Server starts the database will
> once again be suspect. You can then put the database in emergency mode
> (status 32768) and recycle SQL Server. At this point you should be able to
> at least salvage some of the data, may be all depending on the rason it
> went suspect.
> Rand
> This posting is provided "as is" with no warranties and confers no rights.
>

How to attach a db_file with problems?

How to attach a DB that has not been deatached

My SQL is 2KSP3a (8.00.760)
I 've both MSDB datafiles with no backup that comes from a Destroyed Server
and I would like to recover DTS from them
This datafiles have not been detached previosly. Are recovered from original
disks when SQL its stopped.
I've installed a new SQL Server on a new machine
I've put the necessary flags, started SQL detached actual MSDB and...when i
try to do 'sp_attach_db' on a new server with the old MSDB I obtain the KB...
Knowledge Base
FIX: Transaction Log Restore Fails with Message 3456
PSS ID Number: 329487
Article Last Modified on 9/25/2003
How can i obtain the fix or resolve the DTS recovery?
Thansk a lot !!
Germa Guerra
Not sure if this applies to MSDB but it 's probably worth a look:
http://www.sqlservercentral.com/scri...p?scriptid=599
Restoring a .mdf
Andrew J. Kelly SQL MVP
"Germa Guerra" <GermaGuerra@.discussions.microsoft.com> wrote in message
news:A5D3DE5A-4D04-41B5-944E-2E8302621F31@.microsoft.com...
> My SQL is 2KSP3a (8.00.760)
> I 've both MSDB datafiles with no backup that comes from a Destroyed
> Server
> and I would like to recover DTS from them
> This datafiles have not been detached previosly. Are recovered from
> original
> disks when SQL its stopped.
> I've installed a new SQL Server on a new machine
> I've put the necessary flags, started SQL detached actual MSDB and...when
> i
> try to do 'sp_attach_db' on a new server with the old MSDB I obtain the
> KB...
> Knowledge Base
> FIX: Transaction Log Restore Fails with Message 3456
> PSS ID Number: 329487
> Article Last Modified on 9/25/2003
> --
> How can i obtain the fix or resolve the DTS recovery?
> Thansk a lot !!
> --
> Germa Guerra

how to attach a DB

I have database DB1 on Box1 and I have the full backup of that database and also have data file and log file.If I want to restore the database on a diff box How can do that.
1.How can I restore the Database?
2.How ca I attach the data file to the database on new Box?
Thanks.Check out the WITH REPLACE arguement of the RESTORE command.

or if you have a mdf and a ldf look at sp_attach_db.

How to attach a database using SQL Server 2005

Hello
In the full version of SQL Server I can use enterprise manager to attach a
database. I have an SQL Server 2005 database from another machine which I
want to attach to a new computer with SQL Server 2005 Express. Is there a
tool I can use or do I need to run a command?
Angushave you try the sp_attach stored procedure?
"Angus" <nospam@.gmail.com> wrote in message
news:#iTQZL8kHHA.4936@.TK2MSFTNGP03.phx.gbl...
> Hello
> In the full version of SQL Server I can use enterprise manager to attach a
> database. I have an SQL Server 2005 database from another machine which I
> want to attach to a new computer with SQL Server 2005 Express. Is there a
> tool I can use or do I need to run a command?
> Angus
>|||Yes I have found the sp_attach syntax - but how do I run these commands?
Can I run them from the command line? But if I navigate to prog
files\microsoft SQL Server\MSSQL.1\MSSQL\Binn\ - I cannot run eg sp_attach?
Is this not the right path?
Or have I maybe not installed something?
There is a Start menu item, Microsoft SQL Server 2005... Configuration
Tools... SQL Server Configuration Manager - but can't see where I could type
commands in there?
Angus
"Jeje" <willgart@.hotmail.com> wrote in message
news:uGaU5N8kHHA.2272@.TK2MSFTNGP02.phx.gbl...
> have you try the sp_attach stored procedure?
> "Angus" <nospam@.gmail.com> wrote in message
> news:#iTQZL8kHHA.4936@.TK2MSFTNGP03.phx.gbl...
> > Hello
> >
> > In the full version of SQL Server I can use enterprise manager to attach
a
> > database. I have an SQL Server 2005 database from another machine which
I
> > want to attach to a new computer with SQL Server 2005 Express. Is there
a
> > tool I can use or do I need to run a command?
> >
> > Angus
> >
> >|||"Angus" <nospam@.gmail.com> wrote in
news:ecHo7S8kHHA.4188@.TK2MSFTNGP02.phx.gbl:
> Yes I have found the sp_attach syntax - but how do I run these
> commands? Can I run them from the command line? But if I navigate to
> prog files\microsoft SQL Server\MSSQL.1\MSSQL\Binn\ - I cannot run eg
> sp_attach? Is this not the right path?
> Or have I maybe not installed something?
> There is a Start menu item, Microsoft SQL Server 2005... Configuration
> Tools... SQL Server Configuration Manager - but can't see where I
> could type commands in there?
You can use the OSQL command-line utility to run stored procedures. See
BOL for how to use it.
> Angus
> "Jeje" <willgart@.hotmail.com> wrote in message
> news:uGaU5N8kHHA.2272@.TK2MSFTNGP02.phx.gbl...
>> have you try the sp_attach stored procedure?
>> "Angus" <nospam@.gmail.com> wrote in message
>> news:#iTQZL8kHHA.4936@.TK2MSFTNGP03.phx.gbl...
>> > Hello
>> >
>> > In the full version of SQL Server I can use enterprise manager to
>> > attach
> a
>> > database. I have an SQL Server 2005 database from another machine
>> > which
> I
>> > want to attach to a new computer with SQL Server 2005 Express. Is
>> > there
> a
>> > tool I can use or do I need to run a command?
>> >
>> > Angus
>> >
>> >
>|||On Fri, 11 May 2007 12:52:21 +0100, "Angus" <nospam@.gmail.com> wrote:
>Hello
>In the full version of SQL Server I can use enterprise manager to attach a
>database. I have an SQL Server 2005 database from another machine which I
>want to attach to a new computer with SQL Server 2005 Express. Is there a
>tool I can use or do I need to run a command?
>Angus
SQL Server Management Studio Express has an attach command.
Click on Databases, right context mouse click, attach..
Another way to move a database from one machine to another is to make
a backup and then restore the backup to the new machine.|||Hi
CREATE DATABASE ...command has FOR ATTACH option ,please take a look at
BOL.
"Angus" <nospam@.gmail.com> wrote in message
news:%23iTQZL8kHHA.4936@.TK2MSFTNGP03.phx.gbl...
> Hello
> In the full version of SQL Server I can use enterprise manager to attach a
> database. I have an SQL Server 2005 database from another machine which I
> want to attach to a new computer with SQL Server 2005 Express. Is there a
> tool I can use or do I need to run a command?
> Angus
>

How to attach a database using SQL Server 2005

have you try the sp_attach stored procedure?
"Angus" <nospam@.gmail.com> wrote in message
news:#iTQZL8kHHA.4936@.TK2MSFTNGP03.phx.gbl...
> Hello
> In the full version of SQL Server I can use enterprise manager to attach a
> database. I have an SQL Server 2005 database from another machine which I
> want to attach to a new computer with SQL Server 2005 Express. Is there a
> tool I can use or do I need to run a command?
> Angus
>
"Angus" <nospam@.gmail.com> wrote in
news:ecHo7S8kHHA.4188@.TK2MSFTNGP02.phx.gbl:

> Yes I have found the sp_attach syntax - but how do I run these
> commands? Can I run them from the command line? But if I navigate to
> prog files\microsoft SQL Server\MSSQL.1\MSSQL\Binn\ - I cannot run eg
> sp_attach? Is this not the right path?
> Or have I maybe not installed something?
> There is a Start menu item, Microsoft SQL Server 2005... Configuration
> Tools... SQL Server Configuration Manager - but can't see where I
> could type commands in there?
You can use the OSQL command-line utility to run stored procedures. See
BOL for how to use it.

> Angus
> "Jeje" <willgart@.hotmail.com> wrote in message
> news:uGaU5N8kHHA.2272@.TK2MSFTNGP02.phx.gbl...
> a
> I
> a
>

How to attach a database using SQL Server 2005

Hello
In the full version of SQL Server I can use enterprise manager to attach a
database. I have an SQL Server 2005 database from another machine which I
want to attach to a new computer with SQL Server 2005 Express. Is there a
tool I can use or do I need to run a command?
Angushave you try the sp_attach stored procedure?
"Angus" <nospam@.gmail.com> wrote in message
news:#iTQZL8kHHA.4936@.TK2MSFTNGP03.phx.gbl...
> Hello
> In the full version of SQL Server I can use enterprise manager to attach a
> database. I have an SQL Server 2005 database from another machine which I
> want to attach to a new computer with SQL Server 2005 Express. Is there a
> tool I can use or do I need to run a command?
> Angus
>|||Yes I have found the sp_attach syntax - but how do I run these commands?
Can I run them from the command line? But if I navigate to prog
files\microsoft SQL Server\MSSQL.1\MSSQL\Binn\ - I cannot run eg sp_attach?
Is this not the right path?
Or have I maybe not installed something?
There is a Start menu item, Microsoft SQL Server 2005... Configuration
Tools... SQL Server Configuration Manager - but can't see where I could type
commands in there?
Angus
"Jeje" <willgart@.hotmail.com> wrote in message
news:uGaU5N8kHHA.2272@.TK2MSFTNGP02.phx.gbl...[vbcol=seagreen]
> have you try the sp_attach stored procedure?
> "Angus" <nospam@.gmail.com> wrote in message
> news:#iTQZL8kHHA.4936@.TK2MSFTNGP03.phx.gbl...
a[vbcol=seagreen]
I[vbcol=seagreen]
a[vbcol=seagreen]|||"Angus" <nospam@.gmail.com> wrote in
news:ecHo7S8kHHA.4188@.TK2MSFTNGP02.phx.gbl:

> Yes I have found the sp_attach syntax - but how do I run these
> commands? Can I run them from the command line? But if I navigate to
> prog files\microsoft SQL Server\MSSQL.1\MSSQL\Binn\ - I cannot run eg
> sp_attach? Is this not the right path?
> Or have I maybe not installed something?
> There is a Start menu item, Microsoft SQL Server 2005... Configuration
> Tools... SQL Server Configuration Manager - but can't see where I
> could type commands in there?
You can use the OSQL command-line utility to run stored procedures. See
BOL for how to use it.

> Angus
> "Jeje" <willgart@.hotmail.com> wrote in message
> news:uGaU5N8kHHA.2272@.TK2MSFTNGP02.phx.gbl...
> a
> I
> a
>|||On Fri, 11 May 2007 12:52:21 +0100, "Angus" <nospam@.gmail.com> wrote:

>Hello
>In the full version of SQL Server I can use enterprise manager to attach a
>database. I have an SQL Server 2005 database from another machine which I
>want to attach to a new computer with SQL Server 2005 Express. Is there a
>tool I can use or do I need to run a command?
>Angus
SQL Server Management Studio Express has an attach command.
Click on Databases, right context mouse click, attach..
Another way to move a database from one machine to another is to make
a backup and then restore the backup to the new machine.|||Hi
CREATE DATABASE ...command has FOR ATTACH option ,please take a look at
BOL.
"Angus" <nospam@.gmail.com> wrote in message
news:%23iTQZL8kHHA.4936@.TK2MSFTNGP03.phx.gbl...
> Hello
> In the full version of SQL Server I can use enterprise manager to attach a
> database. I have an SQL Server 2005 database from another machine which I
> want to attach to a new computer with SQL Server 2005 Express. Is there a
> tool I can use or do I need to run a command?
> Angus
>sql

How to Attach a database to SQLServer7

OK, I'm an idiot - hopefully, someone will help me out anyway...
How do I "attach" a database to SQLServer 7 ?
I created a database and developed a VB program to use it. I wanted to
demo it to a co-worker of mine. So, I COPIED the database (the .MDF
file and it associated .LDF file) (that's: copied the files with
Windows Explorer - NOT backed-them-up with Enterprise Manager) to a CD
and sent it to him (he's 2000 miles away). He copied it on to his
server (running SQLServer 7) but we can't get SQLServer to see it.
I've read a couple of things about "attaching" a copied database
(mainly Q224071) but I can't figure out how to do that. It tells me to
run a "stored procedure". How? Where is this sp_attach_db stored
procedure? And how do I use it? Can someone give me a clue as to how
to do this?
BTW, this is NOT in a production environment. No one but me uses this
database or even the server itself. So, I'm pretty much free to do
whatever needs to be done.
Thanks for any guidance.INF: Moving SQL Server Databases to a New Location with Detach/Attach
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q224071
> run a "stored procedure". How? Where is this sp_attach_db stored
> procedure? And how do I use it? Can someone give me a clue as to how
> to do this?
Well, sp_attach_db is present in the master database and this is similar to
using any conventional stored procedure. The article contains good
information on how to move a database to different locations.
HTH,
Vinod Kumar
MCSE, DBA, MCAD
www.extremeexperts.com
"Martin" <martinvalley@.comcast.net> wrote in message
news:2f7okvsjllmh0fp4nsrkp4paem59vavms4@.4ax.com...
> OK, I'm an idiot - hopefully, someone will help me out anyway...
> How do I "attach" a database to SQLServer 7 ?
> I created a database and developed a VB program to use it. I wanted to
> demo it to a co-worker of mine. So, I COPIED the database (the .MDF
> file and it associated .LDF file) (that's: copied the files with
> Windows Explorer - NOT backed-them-up with Enterprise Manager) to a CD
> and sent it to him (he's 2000 miles away). He copied it on to his
> server (running SQLServer 7) but we can't get SQLServer to see it.
> I've read a couple of things about "attaching" a copied database
> (mainly Q224071) but I can't figure out how to do that. It tells me to
> run a "stored procedure". How? Where is this sp_attach_db stored
> procedure? And how do I use it? Can someone give me a clue as to how
> to do this?
> BTW, this is NOT in a production environment. No one but me uses this
> database or even the server itself. So, I'm pretty much free to do
> whatever needs to be done.
> Thanks for any guidance.
>|||Thanks, I got it working.
What I didn't understand was HOW to execute the stored procedure. If
some other clueless newbie reads this, run "Query Analyzer" and
execute the query from there.
On Tue, 26 Aug 2003 19:59:25 -0700, Martin <martinvalley@.comcast.net>
wrote:
>OK, I'm an idiot - hopefully, someone will help me out anyway...
>How do I "attach" a database to SQLServer 7 ?
>I created a database and developed a VB program to use it. I wanted to
>demo it to a co-worker of mine. So, I COPIED the database (the .MDF
>file and it associated .LDF file) (that's: copied the files with
>Windows Explorer - NOT backed-them-up with Enterprise Manager) to a CD
>and sent it to him (he's 2000 miles away). He copied it on to his
>server (running SQLServer 7) but we can't get SQLServer to see it.
>I've read a couple of things about "attaching" a copied database
>(mainly Q224071) but I can't figure out how to do that. It tells me to
>run a "stored procedure". How? Where is this sp_attach_db stored
>procedure? And how do I use it? Can someone give me a clue as to how
>to do this?
>BTW, this is NOT in a production environment. No one but me uses this
>database or even the server itself. So, I'm pretty much free to do
>whatever needs to be done.
>Thanks for any guidance.
>|||Martin,
FYI, on SQL Server 2000 from the Enterprise Manager. Connect to the
server. Right click on Databases. Left click on All Tasks. Left click
on Attach Database... Fill in the blanks. This will not work on SQL
7.0, use the Query Analyzer. Also you do not need the ldf for an
attach, it will create a new one, although it will not hurt.
Norman
Martin <martinvalley@.comcast.net> wrote in message news:<kvfpkv4tovnk1id8td6s5i9krtlidtlscf@.4ax.com>...
> Thanks, I got it working.
> What I didn't understand was HOW to execute the stored procedure. If
> some other clueless newbie reads this, run "Query Analyzer" and
> execute the query from there.
>
> On Tue, 26 Aug 2003 19:59:25 -0700, Martin <martinvalley@.comcast.net>
> wrote:
> >OK, I'm an idiot - hopefully, someone will help me out anyway...
> >
> >How do I "attach" a database to SQLServer 7 ?
> >
> >I created a database and developed a VB program to use it. I wanted to
> >demo it to a co-worker of mine. So, I COPIED the database (the .MDF
> >file and it associated .LDF file) (that's: copied the files with
> >Windows Explorer - NOT backed-them-up with Enterprise Manager) to a CD
> >and sent it to him (he's 2000 miles away). He copied it on to his
> >server (running SQLServer 7) but we can't get SQLServer to see it.
> >
> >I've read a couple of things about "attaching" a copied database
> >(mainly Q224071) but I can't figure out how to do that. It tells me to
> >run a "stored procedure". How? Where is this sp_attach_db stored
> >procedure? And how do I use it? Can someone give me a clue as to how
> >to do this?
> >
> >BTW, this is NOT in a production environment. No one but me uses this
> >database or even the server itself. So, I'm pretty much free to do
> >whatever needs to be done.
> >
> >Thanks for any guidance.
> >

Friday, March 30, 2012

Blog Archive

About Me