May 2008 | SansSQL

Friday, May 9, 2008

Easy way to copy table structure

Hey friends what you people do you do to copy the structure of your table.I have an simple idea to do that.

1.Go to your Enterprise Manager
2.Go to the database where the required table is located
3.Select the table of your choice
4.Simply press Ctrl+C
5.Then go to Query Analyser and do Ctrl+V
6.Then Press F5
You are done...

Saturday, May 3, 2008

Emergency mode

sp_configure 'allow update', 1
Reconfigure with override

update sysdatabases set status=32768 where name=

sp_configure 'allow update', 1
Reconfigure with override

Use this query to take the database to Emergency Mode whenever it is marked as suspect.
When the database is in Emergency mode, you can query the database and export the data into a new database.

Backup without affecting LSN

/*In SQL server 2005 you can take backup without affecting logshipping . You can use WITH COPY_ONLY option in BACKUP command, this command will take backup without affecting LSN.*/
--Don't forget to change the dbname and backup path before using the above command.

Error in replication::subscription(s) have been marked inactive and must be reinitialized

Whenever there is an error as mentioned above then, you can try to update the status column in the MSsubscriptions table in the distribution database. The status column for the expired subscription indicated a value of 0 meaning inactive. The value of 2 in the status column means Active.

Try the following steps:
1. Select * from MSsubscriptions to locate the expired subscription.
2. Use the query below to reset the status in MSsubscriptions table. Fill in the values for the publisher_id, publisher_db, publication_id, subscriber_id and subscriber_db in the query below with the values from the expired subscription in the MSsubscriptions table.

update distribution..MSsubscriptions set status=2 where publisher_id='x' andpublisher_db='x' and publication_id='x' and subscriber_id='x' and subscriber_db='x'

Status of the subscription:
0 = Inactive
1 = Subscribed
2 = Active

Virtual LOG info

It is used to get the number of virtual log file for a database.

How to get current database name

Use the below Query

SELECT db_name()

How to Change the Owner of a DTS package?

sp_reassign_dtspackageowner [@name =] 'name', [@id =] 'id', [@newloginname =] 'newloginname'

sp_reassign_dtspackageowner is an undocumented stored procedure which is present in MSDB database.

Get the List of Failed Jobs

use msdb
SELECT name FROM msdb.dbo.sysjobs A, msdb.dbo.sysjobservers B WHERE A.job_id = B.job_id AND B.last_run_outcome = 0

How to get the version of SQL server

Simply execute the below query to get the SQL server version you are running.

SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

How to find the SQL Server Version?

Simply execute the below query to get the version of SQL Server you are running.

SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

How to change the server wide date format

Many times, when you install windows and sql server 2000 or 2005 in the fresh machine. generally application which is pointing to your database fails to insert the dd/mm/yyyy
date to your database. even you put the write syntex in your application program and the insert query. Why ?

It is due to server level date settings. As a default your server settings would be in mm/dd/yyyy format, which needs to be changed in the server.

How to do this?
Follow these simple steps...

1. Start -> Control Pannel -> Reginal settings ->Customize
after that go the date format and set the short date = dd/mm/yyyy
and long date format = dd mmmm, yyyy

After doing this, Your application will be able to insert the exact date to your database.As there is no sql server level settings to change the date format. But apart from thatyou can use SET option while firing the insert command to your database
Like this...

SET dd/mm/yyyy
Insert into table (date1) value ('29/04/2008')