Hello,
I am using Sql Server 2000 and am having problems after I tried restoring my
db today.
I had got the database backup file from another user and I was able to
restore my db successfully. But after doing so I was not able to query any
of my tables.
Any query I give like,
select * from tablename yielded Invalid object name 'tablename'
Earlier the owner of this db was not "sa" but rather "celia".
After restoring the db, I am not able to login to SQL Server Query Analyser
using celia/password.
I logged in as "sa" then and had to execute my query like
select * from celia.tablename and it worked this time.
Any idea on what went wrong? How do I get this back to working?
Thanks.
CeliaHi,
You can syncronze the login and users first. This will allow you to login
using celia user. Use the below procedure to sync the Login and user
sp_change_users_login 'update_one','celia','celia' -- (See books online for
more information)
Any idea on what went wrong? How do I get this back to working?
Since the object owner (table) is CELIA, even if you login as SA, you need
to mention table owner name (CELIA.Table_name).
This prblem will be solved once you sync. the login and user.
Incase if you want to use SA or other users to access the table with out
table owner qualifier than chnage the object owner to DBO.
This can be done using the system procedure 'sp_changeobjectowner' (See
books online for more info)
Thanks
Hari
SQL Server MVP
"msnews" <jukliukiuk@.celia.com> wrote in message
news:%23Fez80wzFHA.1968@.TK2MSFTNGP10.phx.gbl...
> Hello,
> I am using Sql Server 2000 and am having problems after I tried restoring
> my
> db today.
> I had got the database backup file from another user and I was able to
> restore my db successfully. But after doing so I was not able to query any
> of my tables.
> Any query I give like,
> select * from tablename yielded Invalid object name 'tablename'
> Earlier the owner of this db was not "sa" but rather "celia".
> After restoring the db, I am not able to login to SQL Server Query
> Analyser
> using celia/password.
> I logged in as "sa" then and had to execute my query like
> select * from celia.tablename and it worked this time.
> Any idea on what went wrong? How do I get this back to working?
> Thanks.
> Celia
>
>
Showing posts with label file. Show all posts
Showing posts with label file. Show all posts
Monday, March 19, 2012
Login failed Error
Hello,
I am using Sql Server 2000 and am having problems after I tried restoring my
db today.
I had got the database backup file from another user and I was able to
restore my db successfully. But after doing so I was not able to query any
of my tables.
Any query I give like,
select * from tablename yielded Invalid object name 'tablename'
Earlier the owner of this db was not "sa" but rather "celia".
After restoring the db, I am not able to login to SQL Server Query Analyser
using celia/password.
I logged in as "sa" then and had to execute my query like
select * from celia.tablename and it worked this time.
Any idea on what went wrong? How do I get this back to working?
Thanks.
CeliaHi,
You can syncronze the login and users first. This will allow you to login
using celia user. Use the below procedure to sync the Login and user
sp_change_users_login 'update_one','celia','celia' -- (See books online for
more information)
Any idea on what went wrong? How do I get this back to working?
Since the object owner (table) is CELIA, even if you login as SA, you need
to mention table owner name (CELIA.Table_name).
This prblem will be solved once you sync. the login and user.
Incase if you want to use SA or other users to access the table with out
table owner qualifier than chnage the object owner to DBO.
This can be done using the system procedure 'sp_changeobjectowner' (See
books online for more info)
Thanks
Hari
SQL Server MVP
"msnews" <jukliukiuk@.celia.com> wrote in message
news:%23Fez80wzFHA.1968@.TK2MSFTNGP10.phx.gbl...
> Hello,
> I am using Sql Server 2000 and am having problems after I tried restoring
> my
> db today.
> I had got the database backup file from another user and I was able to
> restore my db successfully. But after doing so I was not able to query any
> of my tables.
> Any query I give like,
> select * from tablename yielded Invalid object name 'tablename'
> Earlier the owner of this db was not "sa" but rather "celia".
> After restoring the db, I am not able to login to SQL Server Query
> Analyser
> using celia/password.
> I logged in as "sa" then and had to execute my query like
> select * from celia.tablename and it worked this time.
> Any idea on what went wrong? How do I get this back to working?
> Thanks.
> Celia
>
>
I am using Sql Server 2000 and am having problems after I tried restoring my
db today.
I had got the database backup file from another user and I was able to
restore my db successfully. But after doing so I was not able to query any
of my tables.
Any query I give like,
select * from tablename yielded Invalid object name 'tablename'
Earlier the owner of this db was not "sa" but rather "celia".
After restoring the db, I am not able to login to SQL Server Query Analyser
using celia/password.
I logged in as "sa" then and had to execute my query like
select * from celia.tablename and it worked this time.
Any idea on what went wrong? How do I get this back to working?
Thanks.
CeliaHi,
You can syncronze the login and users first. This will allow you to login
using celia user. Use the below procedure to sync the Login and user
sp_change_users_login 'update_one','celia','celia' -- (See books online for
more information)
Any idea on what went wrong? How do I get this back to working?
Since the object owner (table) is CELIA, even if you login as SA, you need
to mention table owner name (CELIA.Table_name).
This prblem will be solved once you sync. the login and user.
Incase if you want to use SA or other users to access the table with out
table owner qualifier than chnage the object owner to DBO.
This can be done using the system procedure 'sp_changeobjectowner' (See
books online for more info)
Thanks
Hari
SQL Server MVP
"msnews" <jukliukiuk@.celia.com> wrote in message
news:%23Fez80wzFHA.1968@.TK2MSFTNGP10.phx.gbl...
> Hello,
> I am using Sql Server 2000 and am having problems after I tried restoring
> my
> db today.
> I had got the database backup file from another user and I was able to
> restore my db successfully. But after doing so I was not able to query any
> of my tables.
> Any query I give like,
> select * from tablename yielded Invalid object name 'tablename'
> Earlier the owner of this db was not "sa" but rather "celia".
> After restoring the db, I am not able to login to SQL Server Query
> Analyser
> using celia/password.
> I logged in as "sa" then and had to execute my query like
> select * from celia.tablename and it worked this time.
> Any idea on what went wrong? How do I get this back to working?
> Thanks.
> Celia
>
>
Login failed Error
Hello,
I am using Sql Server 2000 and am having problems after I tried restoring my
db today.
I had got the database backup file from another user and I was able to
restore my db successfully. But after doing so I was not able to query any
of my tables.
Any query I give like,
select * from tablename yielded Invalid object name 'tablename'
Earlier the owner of this db was not "sa" but rather "celia".
After restoring the db, I am not able to login to SQL Server Query Analyser
using celia/password.
I logged in as "sa" then and had to execute my query like
select * from celia.tablename and it worked this time.
Any idea on what went wrong? How do I get this back to working?
Thanks.
Celia
Hi,
You can syncronze the login and users first. This will allow you to login
using celia user. Use the below procedure to sync the Login and user
sp_change_users_login 'update_one','celia','celia' -- (See books online for
more information)
Any idea on what went wrong? How do I get this back to working?
Since the object owner (table) is CELIA, even if you login as SA, you need
to mention table owner name (CELIA.Table_name).
This prblem will be solved once you sync. the login and user.
Incase if you want to use SA or other users to access the table with out
table owner qualifier than chnage the object owner to DBO.
This can be done using the system procedure 'sp_changeobjectowner' (See
books online for more info)
Thanks
Hari
SQL Server MVP
"msnews" <jukliukiuk@.celia.com> wrote in message
news:%23Fez80wzFHA.1968@.TK2MSFTNGP10.phx.gbl...
> Hello,
> I am using Sql Server 2000 and am having problems after I tried restoring
> my
> db today.
> I had got the database backup file from another user and I was able to
> restore my db successfully. But after doing so I was not able to query any
> of my tables.
> Any query I give like,
> select * from tablename yielded Invalid object name 'tablename'
> Earlier the owner of this db was not "sa" but rather "celia".
> After restoring the db, I am not able to login to SQL Server Query
> Analyser
> using celia/password.
> I logged in as "sa" then and had to execute my query like
> select * from celia.tablename and it worked this time.
> Any idea on what went wrong? How do I get this back to working?
> Thanks.
> Celia
>
>
I am using Sql Server 2000 and am having problems after I tried restoring my
db today.
I had got the database backup file from another user and I was able to
restore my db successfully. But after doing so I was not able to query any
of my tables.
Any query I give like,
select * from tablename yielded Invalid object name 'tablename'
Earlier the owner of this db was not "sa" but rather "celia".
After restoring the db, I am not able to login to SQL Server Query Analyser
using celia/password.
I logged in as "sa" then and had to execute my query like
select * from celia.tablename and it worked this time.
Any idea on what went wrong? How do I get this back to working?
Thanks.
Celia
Hi,
You can syncronze the login and users first. This will allow you to login
using celia user. Use the below procedure to sync the Login and user
sp_change_users_login 'update_one','celia','celia' -- (See books online for
more information)
Any idea on what went wrong? How do I get this back to working?
Since the object owner (table) is CELIA, even if you login as SA, you need
to mention table owner name (CELIA.Table_name).
This prblem will be solved once you sync. the login and user.
Incase if you want to use SA or other users to access the table with out
table owner qualifier than chnage the object owner to DBO.
This can be done using the system procedure 'sp_changeobjectowner' (See
books online for more info)
Thanks
Hari
SQL Server MVP
"msnews" <jukliukiuk@.celia.com> wrote in message
news:%23Fez80wzFHA.1968@.TK2MSFTNGP10.phx.gbl...
> Hello,
> I am using Sql Server 2000 and am having problems after I tried restoring
> my
> db today.
> I had got the database backup file from another user and I was able to
> restore my db successfully. But after doing so I was not able to query any
> of my tables.
> Any query I give like,
> select * from tablename yielded Invalid object name 'tablename'
> Earlier the owner of this db was not "sa" but rather "celia".
> After restoring the db, I am not able to login to SQL Server Query
> Analyser
> using celia/password.
> I logged in as "sa" then and had to execute my query like
> select * from celia.tablename and it worked this time.
> Any idea on what went wrong? How do I get this back to working?
> Thanks.
> Celia
>
>
Monday, March 12, 2012
Login Audit
I'd like to keep track of the number of logins to a SQL server over a
day which are then logged in a simple text file, for licensing
reasons. What do you think is the best way to do this? I was thinking
of using a trace but fear this may generate too much info. Could I
schedule a stored procedure to run every hour which records the number
and exports to a text file?
Thanks for your help in advance!
JohnTurn of the Login success and failure auditing functionality of SQL Server.
This will create an entry for each event in the NT Application Eventlog,
then you use WMI to filter them out and send them to a file for example.
This will be the lowest overhead solution, using SQL Trace/Profiler has much
more impact on your server performance.
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2004 All rights reserved.
"John McGinty" <jpmcginty@.talk21.com> wrote in message
news:87642ea9.0401280404.40536dde@.posting.google.com...
Rand
This posting is provided "as is" with no warranties and confers no rights.
day which are then logged in a simple text file, for licensing
reasons. What do you think is the best way to do this? I was thinking
of using a trace but fear this may generate too much info. Could I
schedule a stored procedure to run every hour which records the number
and exports to a text file?
Thanks for your help in advance!
JohnTurn of the Login success and failure auditing functionality of SQL Server.
This will create an entry for each event in the NT Application Eventlog,
then you use WMI to filter them out and send them to a file for example.
This will be the lowest overhead solution, using SQL Trace/Profiler has much
more impact on your server performance.
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2004 All rights reserved.
"John McGinty" <jpmcginty@.talk21.com> wrote in message
news:87642ea9.0401280404.40536dde@.posting.google.com...
quote:|||The logins also get sent to the SQL Server errorlog as well.
> I'd like to keep track of the number of logins to a SQL server over a
> day which are then logged in a simple text file, for licensing
> reasons. What do you think is the best way to do this? I was thinking
> of using a trace but fear this may generate too much info. Could I
> schedule a stored procedure to run every hour which records the number
> and exports to a text file?
> Thanks for your help in advance!
> John
Rand
This posting is provided "as is" with no warranties and confers no rights.
Wednesday, March 7, 2012
Logical File Rename
Dear All
How to rename the logical file name of the database?
thanks
Manoj kumarUse the ALTER DATABASE command. (See Books Online for syntax.)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Manoj" <Manoj@.discussions.microsoft.com> wrote in message
news:4AE35774-3B5F-4C82-A7FF-A22E1EF27964@.microsoft.com...
> Dear All
> How to rename the logical file name of the database?
> thanks
> Manoj kumar|||Thanks Your very much
Manoj kumar
"Tibor Karaszi" wrote:
> Use the ALTER DATABASE command. (See Books Online for syntax.)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Manoj" <Manoj@.discussions.microsoft.com> wrote in message
> news:4AE35774-3B5F-4C82-A7FF-A22E1EF27964@.microsoft.com...
>
>
How to rename the logical file name of the database?
thanks
Manoj kumarUse the ALTER DATABASE command. (See Books Online for syntax.)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Manoj" <Manoj@.discussions.microsoft.com> wrote in message
news:4AE35774-3B5F-4C82-A7FF-A22E1EF27964@.microsoft.com...
> Dear All
> How to rename the logical file name of the database?
> thanks
> Manoj kumar|||Thanks Your very much
Manoj kumar
"Tibor Karaszi" wrote:
> Use the ALTER DATABASE command. (See Books Online for syntax.)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Manoj" <Manoj@.discussions.microsoft.com> wrote in message
> news:4AE35774-3B5F-4C82-A7FF-A22E1EF27964@.microsoft.com...
>
>
Logical File Name Change
Is there a way that we can change logical file names?In SQL2000 you can use
ALTER DATABASE
MODIFY FILE
(NAME = logical_file_name,
NEWNAME = new_logical_name)
See BOL for details
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"JI" <anonymous@.discussions.microsoft.com> wrote in message
news:935C21AE-5DD0-4FF8-9C1E-02A673930B8E@.microsoft.com...
> Is there a way that we can change logical file names?
>
ALTER DATABASE
MODIFY FILE
(NAME = logical_file_name,
NEWNAME = new_logical_name)
See BOL for details
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"JI" <anonymous@.discussions.microsoft.com> wrote in message
news:935C21AE-5DD0-4FF8-9C1E-02A673930B8E@.microsoft.com...
> Is there a way that we can change logical file names?
>
logical file name
After backing up a database I restored it to a different database. The
physical file names are different but the logical file name is the same for
the two databases. Does this pose any problems?
IMO, it shouldn't cause any problem.
Thanks
Yogish
|||That is common and is not a problem.
Andrew J. Kelly SQL MVP
"Tom Reis" <reistom@.cdnet.cod.edu> wrote in message
news:u2PVrWb%23EHA.3376@.TK2MSFTNGP12.phx.gbl...
> After backing up a database I restored it to a different database. The
> physical file names are different but the logical file name is the same
> for
> the two databases. Does this pose any problems?
>
|||As the others have stated, this won't cause any problems. If you are using
SQL 2000 and want to be tidy, you can change the logical file name with
ALTER DATABASE. For example:
ALTER DATABASE MyDatabase
MODIFY FILE(NAME='OldDatabase', NEWNAME='MyDatabase')
ALTER DATABASE MyDatabase
MODIFY FILE(NAME='OldDatabase_Log', NEWNAME='MyDatabase_Log')
Hope this helps.
Dan Guzman
SQL Server MVP
"Tom Reis" <reistom@.cdnet.cod.edu> wrote in message
news:u2PVrWb%23EHA.3376@.TK2MSFTNGP12.phx.gbl...
> After backing up a database I restored it to a different database. The
> physical file names are different but the logical file name is the same
> for
> the two databases. Does this pose any problems?
>
physical file names are different but the logical file name is the same for
the two databases. Does this pose any problems?
IMO, it shouldn't cause any problem.
Thanks
Yogish
|||That is common and is not a problem.
Andrew J. Kelly SQL MVP
"Tom Reis" <reistom@.cdnet.cod.edu> wrote in message
news:u2PVrWb%23EHA.3376@.TK2MSFTNGP12.phx.gbl...
> After backing up a database I restored it to a different database. The
> physical file names are different but the logical file name is the same
> for
> the two databases. Does this pose any problems?
>
|||As the others have stated, this won't cause any problems. If you are using
SQL 2000 and want to be tidy, you can change the logical file name with
ALTER DATABASE. For example:
ALTER DATABASE MyDatabase
MODIFY FILE(NAME='OldDatabase', NEWNAME='MyDatabase')
ALTER DATABASE MyDatabase
MODIFY FILE(NAME='OldDatabase_Log', NEWNAME='MyDatabase_Log')
Hope this helps.
Dan Guzman
SQL Server MVP
"Tom Reis" <reistom@.cdnet.cod.edu> wrote in message
news:u2PVrWb%23EHA.3376@.TK2MSFTNGP12.phx.gbl...
> After backing up a database I restored it to a different database. The
> physical file names are different but the logical file name is the same
> for
> the two databases. Does this pose any problems?
>
logical file name
After backing up a database I restored it to a different database. The
physical file names are different but the logical file name is the same for
the two databases. Does this pose any problems?IMO, it shouldn't cause any problem.
Thanks
Yogish|||That is common and is not a problem.
Andrew J. Kelly SQL MVP
"Tom Reis" <reistom@.cdnet.cod.edu> wrote in message
news:u2PVrWb%23EHA.3376@.TK2MSFTNGP12.phx.gbl...
> After backing up a database I restored it to a different database. The
> physical file names are different but the logical file name is the same
> for
> the two databases. Does this pose any problems?
>|||As the others have stated, this won't cause any problems. If you are using
SQL 2000 and want to be tidy, you can change the logical file name with
ALTER DATABASE. For example:
ALTER DATABASE MyDatabase
MODIFY FILE(NAME='OldDatabase', NEWNAME='MyDatabase')
ALTER DATABASE MyDatabase
MODIFY FILE(NAME='OldDatabase_Log', NEWNAME='MyDatabase_Log')
Hope this helps.
Dan Guzman
SQL Server MVP
"Tom Reis" <reistom@.cdnet.cod.edu> wrote in message
news:u2PVrWb%23EHA.3376@.TK2MSFTNGP12.phx.gbl...
> After backing up a database I restored it to a different database. The
> physical file names are different but the logical file name is the same
> for
> the two databases. Does this pose any problems?
>
physical file names are different but the logical file name is the same for
the two databases. Does this pose any problems?IMO, it shouldn't cause any problem.
Thanks
Yogish|||That is common and is not a problem.
Andrew J. Kelly SQL MVP
"Tom Reis" <reistom@.cdnet.cod.edu> wrote in message
news:u2PVrWb%23EHA.3376@.TK2MSFTNGP12.phx.gbl...
> After backing up a database I restored it to a different database. The
> physical file names are different but the logical file name is the same
> for
> the two databases. Does this pose any problems?
>|||As the others have stated, this won't cause any problems. If you are using
SQL 2000 and want to be tidy, you can change the logical file name with
ALTER DATABASE. For example:
ALTER DATABASE MyDatabase
MODIFY FILE(NAME='OldDatabase', NEWNAME='MyDatabase')
ALTER DATABASE MyDatabase
MODIFY FILE(NAME='OldDatabase_Log', NEWNAME='MyDatabase_Log')
Hope this helps.
Dan Guzman
SQL Server MVP
"Tom Reis" <reistom@.cdnet.cod.edu> wrote in message
news:u2PVrWb%23EHA.3376@.TK2MSFTNGP12.phx.gbl...
> After backing up a database I restored it to a different database. The
> physical file names are different but the logical file name is the same
> for
> the two databases. Does this pose any problems?
>
logical file name
After backing up a database I restored it to a different database. The
physical file names are different but the logical file name is the same for
the two databases. Does this pose any problems?IMO, it shouldn't cause any problem.
--
Thanks
Yogish|||That is common and is not a problem.
--
Andrew J. Kelly SQL MVP
"Tom Reis" <reistom@.cdnet.cod.edu> wrote in message
news:u2PVrWb%23EHA.3376@.TK2MSFTNGP12.phx.gbl...
> After backing up a database I restored it to a different database. The
> physical file names are different but the logical file name is the same
> for
> the two databases. Does this pose any problems?
>|||As the others have stated, this won't cause any problems. If you are using
SQL 2000 and want to be tidy, you can change the logical file name with
ALTER DATABASE. For example:
ALTER DATABASE MyDatabase
MODIFY FILE(NAME='OldDatabase', NEWNAME='MyDatabase')
ALTER DATABASE MyDatabase
MODIFY FILE(NAME='OldDatabase_Log', NEWNAME='MyDatabase_Log')
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Tom Reis" <reistom@.cdnet.cod.edu> wrote in message
news:u2PVrWb%23EHA.3376@.TK2MSFTNGP12.phx.gbl...
> After backing up a database I restored it to a different database. The
> physical file names are different but the logical file name is the same
> for
> the two databases. Does this pose any problems?
>
physical file names are different but the logical file name is the same for
the two databases. Does this pose any problems?IMO, it shouldn't cause any problem.
--
Thanks
Yogish|||That is common and is not a problem.
--
Andrew J. Kelly SQL MVP
"Tom Reis" <reistom@.cdnet.cod.edu> wrote in message
news:u2PVrWb%23EHA.3376@.TK2MSFTNGP12.phx.gbl...
> After backing up a database I restored it to a different database. The
> physical file names are different but the logical file name is the same
> for
> the two databases. Does this pose any problems?
>|||As the others have stated, this won't cause any problems. If you are using
SQL 2000 and want to be tidy, you can change the logical file name with
ALTER DATABASE. For example:
ALTER DATABASE MyDatabase
MODIFY FILE(NAME='OldDatabase', NEWNAME='MyDatabase')
ALTER DATABASE MyDatabase
MODIFY FILE(NAME='OldDatabase_Log', NEWNAME='MyDatabase_Log')
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Tom Reis" <reistom@.cdnet.cod.edu> wrote in message
news:u2PVrWb%23EHA.3376@.TK2MSFTNGP12.phx.gbl...
> After backing up a database I restored it to a different database. The
> physical file names are different but the logical file name is the same
> for
> the two databases. Does this pose any problems?
>
logical device already exists?
I want to back the master db to the disk file e:\data\MSSQL\BACKUP\master.BAK.
When I tried to create a new backup device called 'master', I got the error:
Error 15026: Logical device 'master' already exists.
But I've checked several times and don't see master.BAK already exist in e:\data\MSSQL\BACKUP. I had no problem creating the backup devices for other system default databases, like msdb, model, etc.
Anybody know what might be the problem?
Bing
When you create a backup device you are associating a logical name to a
physical file name. In SQL Server there is more than one type of device.
If you look in BOL under sysdevices you can find out about all the different
devices. Anyway "master" already is a device that is associated with the
actual master database. If you want to create a logical backup device to
refer to your master database backup, call it "master_bak", or something.
Hope this helps you understand what a device is. If you really want to see
all the devices you already have assigned you can run the following command
from QA:
select * from master.dbo.sysdevices
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"bing" <bing@.discussions.microsoft.com> wrote in message
news:9AC7B842-64A8-4790-8C39-87957075EE39@.microsoft.com...
> I want to back the master db to the disk file
e:\data\MSSQL\BACKUP\master.BAK.
> When I tried to create a new backup device called 'master', I got the
error:
> Error 15026: Logical device 'master' already exists.
> But I've checked several times and don't see master.BAK already exist in
e:\data\MSSQL\BACKUP. I had no problem creating the backup devices for
other system default databases, like msdb, model, etc.
> Anybody know what might be the problem?
> Bing
|||Hi,
It seems there is an entry in master..sysdevices table.
Execute the below procedure from Query ANalyzer and ensure that you do not
have the same file.
sp_helpdevice
To double check the same by querying :-
select * from master..sysdevices
If you have an entry then you can drop the device by using the below command
(Execute the comand if you need)
sp_dropdevice 'device_name'
Thanks
Hari
MCDBA
Thanks
Hari
MCDBA
"bing" <bing@.discussions.microsoft.com> wrote in message
news:9AC7B842-64A8-4790-8C39-87957075EE39@.microsoft.com...
> I want to back the master db to the disk file
e:\data\MSSQL\BACKUP\master.BAK.
> When I tried to create a new backup device called 'master', I got the
error:
> Error 15026: Logical device 'master' already exists.
> But I've checked several times and don't see master.BAK already exist in
e:\data\MSSQL\BACKUP. I had no problem creating the backup devices for
other system default databases, like msdb, model, etc.
> Anybody know what might be the problem?
> Bing
|||Thanks all for the information. Very helpful.
Bing
"Hari Prasad" wrote:
> Hi,
> It seems there is an entry in master..sysdevices table.
> Execute the below procedure from Query ANalyzer and ensure that you do not
> have the same file.
> sp_helpdevice
> To double check the same by querying :-
> select * from master..sysdevices
> If you have an entry then you can drop the device by using the below command
> (Execute the comand if you need)
> sp_dropdevice 'device_name'
> Thanks
> Hari
> MCDBA
>
> Thanks
> Hari
> MCDBA
>
> "bing" <bing@.discussions.microsoft.com> wrote in message
> news:9AC7B842-64A8-4790-8C39-87957075EE39@.microsoft.com...
> e:\data\MSSQL\BACKUP\master.BAK.
> error:
> e:\data\MSSQL\BACKUP. I had no problem creating the backup devices for
> other system default databases, like msdb, model, etc.
>
>
When I tried to create a new backup device called 'master', I got the error:
Error 15026: Logical device 'master' already exists.
But I've checked several times and don't see master.BAK already exist in e:\data\MSSQL\BACKUP. I had no problem creating the backup devices for other system default databases, like msdb, model, etc.
Anybody know what might be the problem?
Bing
When you create a backup device you are associating a logical name to a
physical file name. In SQL Server there is more than one type of device.
If you look in BOL under sysdevices you can find out about all the different
devices. Anyway "master" already is a device that is associated with the
actual master database. If you want to create a logical backup device to
refer to your master database backup, call it "master_bak", or something.
Hope this helps you understand what a device is. If you really want to see
all the devices you already have assigned you can run the following command
from QA:
select * from master.dbo.sysdevices
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"bing" <bing@.discussions.microsoft.com> wrote in message
news:9AC7B842-64A8-4790-8C39-87957075EE39@.microsoft.com...
> I want to back the master db to the disk file
e:\data\MSSQL\BACKUP\master.BAK.
> When I tried to create a new backup device called 'master', I got the
error:
> Error 15026: Logical device 'master' already exists.
> But I've checked several times and don't see master.BAK already exist in
e:\data\MSSQL\BACKUP. I had no problem creating the backup devices for
other system default databases, like msdb, model, etc.
> Anybody know what might be the problem?
> Bing
|||Hi,
It seems there is an entry in master..sysdevices table.
Execute the below procedure from Query ANalyzer and ensure that you do not
have the same file.
sp_helpdevice
To double check the same by querying :-
select * from master..sysdevices
If you have an entry then you can drop the device by using the below command
(Execute the comand if you need)
sp_dropdevice 'device_name'
Thanks
Hari
MCDBA
Thanks
Hari
MCDBA
"bing" <bing@.discussions.microsoft.com> wrote in message
news:9AC7B842-64A8-4790-8C39-87957075EE39@.microsoft.com...
> I want to back the master db to the disk file
e:\data\MSSQL\BACKUP\master.BAK.
> When I tried to create a new backup device called 'master', I got the
error:
> Error 15026: Logical device 'master' already exists.
> But I've checked several times and don't see master.BAK already exist in
e:\data\MSSQL\BACKUP. I had no problem creating the backup devices for
other system default databases, like msdb, model, etc.
> Anybody know what might be the problem?
> Bing
|||Thanks all for the information. Very helpful.
Bing
"Hari Prasad" wrote:
> Hi,
> It seems there is an entry in master..sysdevices table.
> Execute the below procedure from Query ANalyzer and ensure that you do not
> have the same file.
> sp_helpdevice
> To double check the same by querying :-
> select * from master..sysdevices
> If you have an entry then you can drop the device by using the below command
> (Execute the comand if you need)
> sp_dropdevice 'device_name'
> Thanks
> Hari
> MCDBA
>
> Thanks
> Hari
> MCDBA
>
> "bing" <bing@.discussions.microsoft.com> wrote in message
> news:9AC7B842-64A8-4790-8C39-87957075EE39@.microsoft.com...
> e:\data\MSSQL\BACKUP\master.BAK.
> error:
> e:\data\MSSQL\BACKUP. I had no problem creating the backup devices for
> other system default databases, like msdb, model, etc.
>
>
logical device already exists?
I want to back the master db to the disk file e:\data\MSSQL\BACKUP\master.BA
K.
When I tried to create a new backup device called 'master', I got the error:
Error 15026: Logical device 'master' already exists.
But I've checked several times and don't see master.BAK already exist in e:\
data\MSSQL\BACKUP. I had no problem creating the backup devices for other
system default databases, like msdb, model, etc.
Anybody know what might be the problem?
BingWhen you create a backup device you are associating a logical name to a
physical file name. In SQL Server there is more than one type of device.
If you look in BOL under sysdevices you can find out about all the different
devices. Anyway "master" already is a device that is associated with the
actual master database. If you want to create a logical backup device to
refer to your master database backup, call it "master_bak", or something.
Hope this helps you understand what a device is. If you really want to see
all the devices you already have assigned you can run the following command
from QA:
select * from master.dbo.sysdevices
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"bing" <bing@.discussions.microsoft.com> wrote in message
news:9AC7B842-64A8-4790-8C39-87957075EE39@.microsoft.com...
> I want to back the master db to the disk file
e:\data\MSSQL\BACKUP\master.BAK.
> When I tried to create a new backup device called 'master', I got the
error:
> Error 15026: Logical device 'master' already exists.
> But I've checked several times and don't see master.BAK already exist in
e:\data\MSSQL\BACKUP. I had no problem creating the backup devices for
other system default databases, like msdb, model, etc.
> Anybody know what might be the problem?
> Bing|||Hi,
It seems there is an entry in master..sysdevices table.
Execute the below procedure from Query ANalyzer and ensure that you do not
have the same file.
sp_helpdevice
To double check the same by querying :-
select * from master..sysdevices
If you have an entry then you can drop the device by using the below command
(Execute the comand if you need)
sp_dropdevice 'device_name'
Thanks
Hari
MCDBA
Thanks
Hari
MCDBA
"bing" <bing@.discussions.microsoft.com> wrote in message
news:9AC7B842-64A8-4790-8C39-87957075EE39@.microsoft.com...
> I want to back the master db to the disk file
e:\data\MSSQL\BACKUP\master.BAK.
> When I tried to create a new backup device called 'master', I got the
error:
> Error 15026: Logical device 'master' already exists.
> But I've checked several times and don't see master.BAK already exist in
e:\data\MSSQL\BACKUP. I had no problem creating the backup devices for
other system default databases, like msdb, model, etc.
> Anybody know what might be the problem?
> Bing|||Thanks all for the information. Very helpful.
Bing
"Hari Prasad" wrote:
> Hi,
> It seems there is an entry in master..sysdevices table.
> Execute the below procedure from Query ANalyzer and ensure that you do not
> have the same file.
> sp_helpdevice
> To double check the same by querying :-
> select * from master..sysdevices
> If you have an entry then you can drop the device by using the below comma
nd
> (Execute the comand if you need)
> sp_dropdevice 'device_name'
> Thanks
> Hari
> MCDBA
>
> Thanks
> Hari
> MCDBA
>
> "bing" <bing@.discussions.microsoft.com> wrote in message
> news:9AC7B842-64A8-4790-8C39-87957075EE39@.microsoft.com...
> e:\data\MSSQL\BACKUP\master.BAK.
> error:
> e:\data\MSSQL\BACKUP. I had no problem creating the backup devices for
> other system default databases, like msdb, model, etc.
>
>
K.
When I tried to create a new backup device called 'master', I got the error:
Error 15026: Logical device 'master' already exists.
But I've checked several times and don't see master.BAK already exist in e:\
data\MSSQL\BACKUP. I had no problem creating the backup devices for other
system default databases, like msdb, model, etc.
Anybody know what might be the problem?
BingWhen you create a backup device you are associating a logical name to a
physical file name. In SQL Server there is more than one type of device.
If you look in BOL under sysdevices you can find out about all the different
devices. Anyway "master" already is a device that is associated with the
actual master database. If you want to create a logical backup device to
refer to your master database backup, call it "master_bak", or something.
Hope this helps you understand what a device is. If you really want to see
all the devices you already have assigned you can run the following command
from QA:
select * from master.dbo.sysdevices
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"bing" <bing@.discussions.microsoft.com> wrote in message
news:9AC7B842-64A8-4790-8C39-87957075EE39@.microsoft.com...
> I want to back the master db to the disk file
e:\data\MSSQL\BACKUP\master.BAK.
> When I tried to create a new backup device called 'master', I got the
error:
> Error 15026: Logical device 'master' already exists.
> But I've checked several times and don't see master.BAK already exist in
e:\data\MSSQL\BACKUP. I had no problem creating the backup devices for
other system default databases, like msdb, model, etc.
> Anybody know what might be the problem?
> Bing|||Hi,
It seems there is an entry in master..sysdevices table.
Execute the below procedure from Query ANalyzer and ensure that you do not
have the same file.
sp_helpdevice
To double check the same by querying :-
select * from master..sysdevices
If you have an entry then you can drop the device by using the below command
(Execute the comand if you need)
sp_dropdevice 'device_name'
Thanks
Hari
MCDBA
Thanks
Hari
MCDBA
"bing" <bing@.discussions.microsoft.com> wrote in message
news:9AC7B842-64A8-4790-8C39-87957075EE39@.microsoft.com...
> I want to back the master db to the disk file
e:\data\MSSQL\BACKUP\master.BAK.
> When I tried to create a new backup device called 'master', I got the
error:
> Error 15026: Logical device 'master' already exists.
> But I've checked several times and don't see master.BAK already exist in
e:\data\MSSQL\BACKUP. I had no problem creating the backup devices for
other system default databases, like msdb, model, etc.
> Anybody know what might be the problem?
> Bing|||Thanks all for the information. Very helpful.
Bing
"Hari Prasad" wrote:
> Hi,
> It seems there is an entry in master..sysdevices table.
> Execute the below procedure from Query ANalyzer and ensure that you do not
> have the same file.
> sp_helpdevice
> To double check the same by querying :-
> select * from master..sysdevices
> If you have an entry then you can drop the device by using the below comma
nd
> (Execute the comand if you need)
> sp_dropdevice 'device_name'
> Thanks
> Hari
> MCDBA
>
> Thanks
> Hari
> MCDBA
>
> "bing" <bing@.discussions.microsoft.com> wrote in message
> news:9AC7B842-64A8-4790-8C39-87957075EE39@.microsoft.com...
> e:\data\MSSQL\BACKUP\master.BAK.
> error:
> e:\data\MSSQL\BACKUP. I had no problem creating the backup devices for
> other system default databases, like msdb, model, etc.
>
>
Friday, February 24, 2012
Logging using OnError Event
Hi
We are generating log file in our SSIS package by enabling the built-in feature of SSIS tool. We are generating log for the "OnError" event. This also recorded the error/failed task messages in the text file "log.txt". That error information is too complex with more unwanted information like below
-OnError,,,pkgExtract,,,8/30/2006 11:50:04 AM,8/30/2006 11:50:04 AM,-1071636471,0x,An OLE DB error has occurred. Error code: 0x80040E21.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".
OnError,,,pkgExtract,,,8/30/2006 11:50:04 AM,8/30/2006 11:50:04 AM,-1071607780,0x,There was an error with input column "create_user_id" (116) on input "OLE DB Destination Input" (103). The column status returned was: "The value violated the integrity constraints for the column.".
OnError,,,pkgExtract,,,8/30/2006 11:50:04 AM,8/30/2006 11:50:04 AM,-1071607767,0x,The "input "OLE DB Destination Input" (103)" failed because error code 0xC020907D occurred, and the error row disposition on "input "OLE DB Destination Input" (103)" specifies failure on error. An error occurred on the specified object of the specified component.
This is infact not in a better readable format. We also don't want to do our error logging in database.
Is there any way of defining our error log and create error error log with customization of our messages . Can we do it using OnError event handler.
Please help us with some good solution to avoid giving this confused error log messages.
Thanks
Kumaran
Sure, you can do that. Read this: http://blogs.conchango.com/jamiethomson/archive/2005/06/11/1593.aspx
-Jamie
Logging Usage (logins)?
Hi everyone!
Can anyone tell me if it's possible to set up some kind of a trigger that will write out to a file or a table and log details of all database connections?
ie: user, datetime of connection, database connected to, etc.
Any help appreciated.
Cheers,
MeganHi Meagan ,
Use SQL Profiler and run a trace you will then see which user connected to which database and at which time they did so ...and more.
Regards
Burner
Can anyone tell me if it's possible to set up some kind of a trigger that will write out to a file or a table and log details of all database connections?
ie: user, datetime of connection, database connected to, etc.
Any help appreciated.
Cheers,
MeganHi Meagan ,
Use SQL Profiler and run a trace you will then see which user connected to which database and at which time they did so ...and more.
Regards
Burner
Monday, February 20, 2012
logging the DDL stmts
Hi :
I am using .cmd files to execute .sql files.
sqlcmd is used in .cmd files to execute the .sql stmts.
In .cmd file the sqlcmd line of code is as follows:
sqlcmd -i .\..\..\sql\tables\create_employee_table.sql
In .sql file the ddl stmt is as follows:
CREATE TABLE [Employee](
[EmployeeID] [int] IDENTITY(1,1) NOT NULL,
[NationalIDNumber] [nvarchar](15) COLLATE Latin1_General_CS_AI NOT NULL,
[ContactID] [int] NOT NULL,
[LoginID] [nvarchar](256) COLLATE Latin1_General_CS_AI NOT NULL,
[ManagerID] [int] NULL,
DF_Employee_rowguid] DEFAULT (newid()),
[ModifiedDate] [datetime] NOT NULL CONSTRAINT CONSTRAINT [PK_Employee_EmployeeID] PRIMARY KEY CLUSTERED
(
[EmployeeID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
DDL used are create and drop of tables and indexes.
How do i log all the ddl stmts executed into some .log file (xyz.log)?
The log file should read something like,
employee table created sucessfully
employee table dropped sucessfully.
...................
sqlcmd -o c:\log\xyz.log , gives me only the output for dml stmts (like select * from emp).. anything other than this will be very helpful.
Any solutions will be of great help.
This is a broader SQL question than just SQL Express so I'm moving it to the Database Engine forum; I think you'll find a better answer there.
In looking around a bit, I found information about DDL Triggers which would seem to do what you suggest. The folks in the other forum can validate my guess.
Regards,
Mike Wachal
SQL Express team
-
Check out my tips for getting your answer faster and how to ask a good question: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=307712&SiteID=1
From the sounds of it you just want output in a text file to know whether or not your create table statement succeeded or not. DDL triggers can do this on the server, however if you have malformed sql (as in your post) or just want to log out to a text file directly from sqlcmd have a look at the -r parameter
sqlcmd -icreate_employee_table.sql -S. -E -o create_employee_table.log -r1
The file "create_employee_table.log" will contain output like this:
Msg 102, Level 15, State 1, Server name, Line 7
Incorrect syntax near ']'.
Msg 319, Level 15, State 1, Server name, Line 11
Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.
To print out success messages you'll need to actually check the value of the @.@.error in the script and print out an appropriate message.
CREATE TABLE [Employee](
[EmployeeID] int IDENTITY(1,1) NOT NULL,
[NationalIDNumber] nvarchar(15) COLLATE Latin1_General_CS_AI NOT NULL,
[ContactID] int NOT NULL,
[LoginID] nvarchar(256) COLLATE Latin1_General_CS_AI NOT NULL,
[ManagerID] int NULL,
[DF_Employee_rowguid] UNIQUEIDENTIFIER DEFAULT (newid()),
[ModifiedDate] datetime NOT NULL CONSTRAINT [PK_Employee_EmployeeID] PRIMARY KEY CLUSTERED
(
[EmployeeID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
if @.@.error = 0
print 'employee table created sucessfully'
Logging stored Procedure changes into file
Hello all,
I have a big stored procedure which is going to alter many tables,insert data, basically lot of changes.
So, i want to have a text file (or) any log file which will display, what all the changes does the stored procedure has done ( They dont want profiler output )
Can anybody know how to log the results of execution of stored procedure to a text file.
Thanks.
Is your stored just going to run once, or many times?
If it is just a one-time batch job type thing, you could create one or more logging tables, and then have your SP do inserts into the logging table that record what was done (like before and after values, etc.)
|||
Unless you use your own logging logic within the procedure or use the profiler or the trace procedure (like the profiler) you won′t be able to do such a tracing.
Jens K. Suessmeyer
http://www.sqlserver2005.de
Logging Sql queries
In SQLServer 2000, I am sure there was a way that you configure the
database to log to a file each query that gets run against it.
However, I cannot seen to find where to set this up in Enterprise
Manager."Mystery Man" <PromisedOyster@.hotmail.com> wrote in message
news:87c81238.0405130309.7c0013fb@.posting.google.c om...
> In SQLServer 2000, I am sure there was a way that you configure the
> database to log to a file each query that gets run against it.
> However, I cannot seen to find where to set this up in Enterprise
> Manager.
database to log to a file each query that gets run against it.
However, I cannot seen to find where to set this up in Enterprise
Manager."Mystery Man" <PromisedOyster@.hotmail.com> wrote in message
news:87c81238.0405130309.7c0013fb@.posting.google.c om...
> In SQLServer 2000, I am sure there was a way that you configure the
> database to log to a file each query that gets run against it.
> However, I cannot seen to find where to set this up in Enterprise
> Manager.
I don't know any way to do exactly what you want, but there are options:
If you're going through ODBC, you can enable ODBC logging.
You can also enable profiler to capture all the traffic.
Another option is to invest in
http://www.lumigent.com/products/le_sql/le_sql.htm|||Mystery Man (PromisedOyster@.hotmail.com) writes:
> In SQLServer 2000, I am sure there was a way that you configure the
> database to log to a file each query that gets run against it.
> However, I cannot seen to find where to set this up in Enterprise
> Manager.
The Profiler is the tool you should use. Or at least where you should
start looking. If you are seriously into log everything which happens
on the server, you should set up a server-side trace with help of
the sp_trace procedures.
Furthermore, if you cannot accept anything to be unlogged because the
trace file fills up the disk, set the C2-autiding configuration option,
which throttles the server in this situation.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
Logging output in a transaction
We have a publish job that we are trying to automate, the problem is getting the output back to the app. or a file. Originally we had print statements, this worked great when we manually ran the proc in QA and could capture the output, now that we are automating it from an application I am not sure how to capture these Print statements - ideally I would like to find this out.
The App. is doing a Try-Catch block so using something like isql.exe will not do the trick otherwise that is the route we would go.
I tried logging everyting to a table but those inserts get rolled back with XACT_ABORT. What about the xp proc that logs it to the event log? Thought of that but that would make a real mess of the event log with all of our status messages.
Now we are considering using xp_cmdshell that calls a batch file to output our status text, is this my best option? I would prefer to capture all of the print statements so if anyone knows how to do this that would be preferable!
Thanks!:bump:
wrong forum? Should I try in an App Dev forum, perhaps?
The App. is doing a Try-Catch block so using something like isql.exe will not do the trick otherwise that is the route we would go.
I tried logging everyting to a table but those inserts get rolled back with XACT_ABORT. What about the xp proc that logs it to the event log? Thought of that but that would make a real mess of the event log with all of our status messages.
Now we are considering using xp_cmdshell that calls a batch file to output our status text, is this my best option? I would prefer to capture all of the print statements so if anyone knows how to do this that would be preferable!
Thanks!:bump:
wrong forum? Should I try in an App Dev forum, perhaps?
Subscribe to:
Posts (Atom)