DBA-Philiac

SET QUOTED_IDENTIFIER ON declare @yesterday varchar ( 20 ), @filename varchar ( 200 ), @bcpcommand varchar ( 500 ) select @yesterday = convert ( varchar , DateAdd ( "d" , - 1 , GETDATE ()), 112 ) set @filename   = 'E:\Temp\MYFILE_' + @yesterday + '.csv' print @filename set @bcpcommand = 'bcp "select * from databasename.dbo.table" queryout' + ' ' + @filename + ' -c -t, -T -SMYSERVER\MYINSTANCE' print @bcpcommand exec master .. xp_cmdshell @bcpcommand

From GUI if you find the above error, you can execute below command. RESTORE DATABASE DataBaseName FROM DISK = 'C:\BackupFileName.bak' WITH REPLACE

Parsing file using For Loop Suppose a file mytext.txt contains firstT 2ndT 3rdT 4thT ;1st2T 2nd2T 3rd2T 4th2T 1st3T 2nd3T 3rd3T If I want to parse the content of the file I will need to write to the below command for /F "eol=; tokens=1,2,3* delims= " %i in (myfile.txt) do @echo %i %j %k %l It means This command parses each line in myfile.txt, ignoring lines that begin with a semicolon and passing the first, second and third token from each line to the FOR body (tokens are delimited by spaces). The body of the FOR statement references %i to get the first token, %j to get the second token, %k to get the third one and %l to get all of the remaining tokens. Delimeter can be any thing and needs to be written after delims=. Often you could see set /A command in any batch script. The interpretation is like Set Displays, sets, or removes environment variables. Used without parameters, set displays the current environment settings. Syntax set [[/a [expression]] [...

A SPID in SQL Server is a Server Process ID. Every time an application connects to SQL Server, a new connection (or SPID) is created. This connection has a defined scope and memory space and cannot interact with other SPIDs. The term SPID is synonymous with Connection, or Session. Status of a Session: RUNNING: This status means session is running one or more batches. When Multiple Active Result Sets (MARS) is enabled, a session can run multiple batches. SUSPENDED : It means that the request currently is not active because it is waiting on a resource. The resource can be an I/O for reading a page, A WAIT can be communication on the network, or it is waiting for lock or a latch. It will become active once the task it is waiting for is completed. Once I/O is completed (Data for table tblStudents is available in the memory), query will move into RUNNABLE queue. RUNNABLE : The SPID is in the runnable queue of a scheduler and waiting for a quantum to run on the sche...

Hello All, Today I have just faced this problem when I was trying to log-in to a database. I can't even execute sp_who command to view who is doing wrong. Then I execute below command to see what was happening with my instance. SELECT     r . scheduler_id ,     r . cpu_time ,     r . session_id ,     r . command Command ,     t . text SQL_Statment ,     r . blocking_session_id Blocking_Session_ID ,     r . total_elapsed_time / 1000 Total_Elapsed_Time_Seconds ,     r . cpu_time CPU_Time ,     s . login_name Login_Name ,     s . [host_name] [Host_Name] ,     s . [program_name] [Program_name] ,     s . memory_usage Memory_Usage ,     r . status [Status] ,     db_name ( r . database_id ) Database_Name ,     r . wait_t...

Taking DB into SINGLE USER mode: For some maintenance situations, it is useful to obtain exclusive access to a SQL Server database. This can be achieved by setting the database's user access into single user mode, which permits only one connection to be made to the database at any time. Limitations and Restrictions If other users are connected to the database at the time that you set the database to single-user mode, their connections to the database will be closed without warning. The database remains in single-user mode even if the user that set the option logs off. At that point, a different user, but only one, can connect to the database. Prerequisites Before you set the database to SINGLE_USER, verify that the AUTO_UPDATE_STATISTICS_ASYNC option is set to OFF. When this option is set to ON, the background thread that is used to update statistics takes a connection against the database, and you will be unable to...

Apply Service Pack to SQL Server: First determine what is going on your server SELECT             SERVERPROPERTY ( 'ProductVersion' ) AS ProductVersion ,             SERVERPROPERTY ( 'ProductLevel' ) AS ProductLevel ,             SERVERPROPERTY ( 'Edition' ) AS Edition ,             SERVERPROPERTY ( 'ResourceLastUpdateDateTime' ) AS 'ResourceLastUpdateDateTime' ,             SERVERPROPERTY ( 'ResourceVersion' ) AS 'ResourceVersion' GO Follow this link to continue upgrade. SQL Server Edition Upgrade: This link has an overview of how to upgrade your existing edition. Compatibility Check: You can chec...

SQL Server Build Version http://sqlserverbuilds.blogspot.com.au/ How to determine which version I am running? http://support.microsoft.com/kb/321185 Changing the compatibility http://www.mssqltips.com/sqlservertip/1436/upgrading-sql-server-databases-and-changing-compatibility-levels/ How to Change the Collation Collations specify the rules for how strings of character data are sorted and compared, based on the norms of particular languages and locales. /* Find Collation of SQL Server Database */ SELECT DATABASEPROPERTYEX ( 'AdventureWorks' , 'Collation' ) GO /* Find Collation of SQL Server Database Table Column */ USE AdventureWorks GO SELECT name , collation_name FROM sys.columns WHERE OBJECT_ID IN ( SELECT OBJECT_ID FROM sys.objects WHERE type = 'U' AND name = 'Address' ) AND name = 'City' Changing the server collation is difficult. It will take to foll...