How to connect to SQL server from Management Studio using Windows Authentication as a different user

How to connect to SQL server from Management Studio using Windows Authentication  as a different user?

You can connect SQL server  from Management studio as a different windows user using the run as command. To accomplish the same do the below.

Press Windows Key + R or Open Command Prompt

Type the below command to Open “SQL Server Management Studio” with a different user

runas /user: " Ssms.exe location in your machine"

Example :

runas /user:MYDOMAIN\Rajesh "C:\Program Files (x86)\Microsoft SQL Server\120\Tools\Binn\ManagementStudio\Ssms.exe"

To get your  SQL Server Management Studio Location . Right click on “SQL Server Management Studio” from start menu and in context menu click “Open File Location”. This will take you to the Ssms.exe location.

Deploy/Use assemblies which require Unsafe/External Access with CLR and T-SQL

I believe, I don’t have to write anything more, instead redirect you to the below fantastic blog which explains the same in detail

.

http://www.manjuke.com/2011/11/deployuse-assemblies-which-require.html

The timestamp syntax is deprecated, use rowversion instead

The timestamp syntax is deprecated. This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.


Have a look here https://msdn.microsoft.com/en-us/library/ms182776.aspx

How to force a query to recompile every time in SQL server.

If you want to clear execution plan and cache, you could use the below commands

DBCC FreeProcCache
DBCC DropCleanbuffers

More details here

https://msdn.microsoft.com/en-us/library/ms174283.aspx

If you want to force query compilation each time, just add the below to the end of the query

OPTION (RECOMPILE)

Why SQL server some times silently truncate the values you passed to a column and some time errors

Scenario

There are scenarios when SQL server silently truncate the values you passed to a column and sometime error as below. Actually what is happening behind the seen here?

Msg 8152, Level 16, State 14, Line 2

String or binary data would be truncated.

The statement has been terminated.

Let’s explore…….

The magic here is because of ANSI_WARNINGS begin set to OFF

In normal circumstances SQL server will truncate the data passed with respect to the underlying column if ANSI_WARNINGS is set to OFF. In case of ANSI_WARNINGS set to ON will result in the above error.

But this is overridden in case you are passing data through a variable. For example assume you have an underlying column of varchar(50) and you declare a variable varchar(50). If you try to assign the variable with a string having length greater than 50 then SQL server will automatically truncate the same to 50 with without popping any error irrespective of ANSI_WARNINGS OFF or ON.

But the above can result in an error if you are declaring the variable as varchar (40) for underling varchar (50) column.

Note: This truncation can also happen in .net before hitting to SQL server if you have explicitly declared the size of SQL parameter

For example:

comm.Parameters.Add("@Name", System.Data.SqlDbType.NVarChar, 50);

comm.Parameters["@Name"].Value = Name;

How to check SQL server collation?

For checking database collation you can sue the below command

select serverproperty('collation')

select databasepropertyex('master','collation')

Ho to remove / release locks from a SQL server table?

No need for me to explain this in details as this guy  has done it @

http://exacthelp.blogspot.in/2012/08/how-to-release-or-remove-lock-on-table.html

Storing Multiple values in a SQL column using bit(&) operator.

There may be situation where we want to store multiple values against a customer, user etc.  Assume for a customer we need to store whether he  owns a House, Car, Bike, Flat, Laptop…….etc.
In normal scenario, what we do is to create separate column for these in the table and save them as Boolean values as below.

 By using the & bit operator in SQL we could eliminate create multiple columns for storing these type of values. Let us see first how we can store this data by using only a single column.

I believe all we know about binary representation like

Decimal  Binary equivalent
2               000010
4               000100
8               001000
16             010000
32             100000

The logic here to first represent the below values for each

Has House 2

Has Car  4
Has Bike 8
Has Flat 16
Has Laptop 32

(You may extent this to store n number using respective decimal/binary representation like 64,128,256……)

So if  we need to store data for a customer who has house and a flat, what we do is add the numbers against “Has House” and ”Has Flat” which will give 18 (2(Has House)+16(Has Flat)=18).  Store these values against the customer in a single column as shown below.

We could compare whether the customer has a house or a flat using the query below,

 

SELECT 'Has House'  FROM dbo.New_Cuatomer_Data WHERE Owns & 2 = 2

SELECT 'Has Car'    FROM dbo.New_Cuatomer_Data WHERE Owns & 4 = 4
SELECT 'Has Bike'   FROM dbo.New_Cuatomer_Data WHERE Owns & 8 = 8
SELECT 'Has Flat'   FROM dbo.New_Cuatomer_Data WHERE Owns & 16 = 16 
SELECT 'Has Laptop' FROM dbo.New_Cuatomer_Data WHERE Owns & 32 = 32 

How does it work?

The logic works here is that, the & operator checks whether the bit positions specified after the & operator in the query is on or not(1 or 0) .For example in the case of 18, it will binary represented as below.

Hence for

SELECT Owns & 2  FROM dbo.New_Cuatomer_Data
 It will return 2 if 2^nd position is on (i.e. if 1)

Binary Bit Positions	32	16	8	4	2	1
Decimal equivalent 18	0	1	0	0	1	0

How to find the port no of SQL server instance?

The default port of SQL server instance will be normally 1433, for more information please refer the below link


http://support.microsoft.com/kb/287932

But It is not necessary that the SQL server instance will operate in the default port in all client places .You can find the port of SQL server 2008 instance by going to .Start->programs->Microsoft SQL Server 2008 R2->Configuration Tools->SQL Server Configuration Manager.

In SQL Server Configuration Manager go to, SQL Server Network Configuration->protocols for MSQLSERVER->double click TC/IP.

Please refer the below link for more options

http://decipherinfosys.wordpress.com/2008/01/02/finding-the-port-number-for-a-particular-sql-server-instance/
Please see the screen shot below for more details

How to rename the logical name of a database in SQL 2008?

How to rename the logical name of a database in SQL 2008?

The blow TSQL will rename the logical name of your  databases

ALTER DATABASE [yourdatabses]

MODIFY FILE (NAME = 'oldlogicalname', NEWNAME = 'newloggicalname')

In the above example [yourdatabses] can be replaced with your databases name,’oldlogicalname' can be replaced with the existing logical name of your database and 'newloggicalname' name can be replaced with the new logical name for your databases.

Reference
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=54300

How to see the list of files in a SQL server backup file?

How to see the list of files in a SQL server backup file?

Today I was coding one application which will help us to restore and take back of our application’s databases.While coding the application I came across a situation, where I need to now the logical file name of the mdf and ldf files in the backup file.I googled a loot and finally I found a solution which I thought of sharing here for other who needs the same.

The TSQL command below will show the show the back up type and other details of the file.

RESTORE HEADERONLY FROM DISK='E:\xxxxxxxxxxxxx'

 

To retrieve the logical files name and other details use the below TSQL

RESTORE FILELISTONLY FROM DISK = 'E:\xxxxxxxxxxxxx' with file=1

In the baove example 'E:\xxxxxxxxxxxxx' can be replaced with your backup files and location

Reference

http://www.mssqltips.com/sqlservertutorial/109/sql-server-restore-filelistonly/

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=54300

http://blog.sqlauthority.com/2007/02/25/sql-server-restore-database-backup-using-sql-script-t-sql/
http://beyondrelational.com/blogs/madhivanan/archive/2011/04/18/identifying-the-backup-type-of-backup-file.aspx

An internal error occurred on the report server. See the error log for more details. (rsInternalError) Invalid column name 'AwaitingFirstExecution'.

Today when I resorted our reporstserver databases of SSRS to the newly formatted server I came across the below error as


An internal error occurred on the report server. See the error log for more details. (rsInternalError) Invalid column name 'AwaitingFirstExecution'.

After a lot of googling I found that the above error occurs when there is a difference between version of the reporting server and the report server databases .The solution for this error is as below

1) Stop the reporting server

2) Restore the Reporstserver and ReportServerTempDB databses.

3) Restart reportingsverever.

How to get the current user and Machine from which a query is executed in SQL server?

You can use select host_name() for getting the name of the machine from which the querry has been excecuted and for getting the windows user which excecute the querry tou can use the caommand select SYSTEM_USER

Warning: Null value is eliminated by an aggregate or other SET operation

Today when I was executing one of the crystal reports in my application. I came across an error message as “Warning: Null value is eliminated by an aggregate or other SET operation”. I then googled a lot and found that this error is coming when a field is having null values for which an aggregate function like sum,avd etc is executed in the SQL statement.


This error can be suppressed by adding the command as below prior to executing the SQL statement

SET ANSI_WARNINGS OFF

How to covert an xml values to a table in SQL server 2005?

The below code will read xml data from a variable and will covert it to a temporary table.


declare @InputTable xml set @InputTable ='<emaillist CustomerId="1" DepartmentID="1" DepartmentName="Chandelier Male" InvocieId="12009 " InvAmount="6985.00" SettledAmt="0.00" BalanceAmt="6985.00" Age="304" Due=" > 90 " Processed="0" CurrencyName="Rufiyaa" Symbol="MRF" /> <emaillist CustomerId="1" DepartmentID="1" DepartmentName="Chandelier Male" InvocieId="12425 " InvAmount="36.00" SettledAmt="0.00" BalanceAmt="36.00" Age="297" Due=" > 90 " Processed="0" CurrencyName="Rufiyaa" Symbol="MRF" /> <emaillist CustomerId="1" DepartmentID="1" DepartmentName="Chandelier Male" InvocieId="14723 " InvAmount="669.00" SettledAmt="0.00" BalanceAmt="669.00" Age="261" Due=" > 90 " Processed="0" CurrencyName="Rufiyaa" Symbol="MRF" /> <emaillist CustomerId="1" DepartmentID="1" DepartmentName="Chandelier Male" InvocieId="15353 " InvAmount="140.00" SettledAmt="0.00" BalanceAmt="140.00" Age="249" Due=" > 90 " Processed="0" CurrencyName="Rufiyaa" Symbol="MRF" />' Declare @TempTable table ( CustomerId int, DepartmentID int, DepartmentName varchar(50), InvocieId nchar(20), InvAmount numeric(18,2), SettledAmt numeric(18,2), BalanceAmt numeric(18,2), Age int, Due char(10), Processed bit, CurrencyName nvarchar(40), Symbol nvarchar(40), Invoicedate datetime, Invoiceno varchar(50), Referenceno varchar(100), remarks varchar(1000) ) insert into @TempTable SELECT Tbl.Col.value('@CustomerId', 'INT'), Tbl.Col.value('@DepartmentID', 'INT'), Tbl.Col.value('@DepartmentName', 'varchar(50)'), Tbl.Col.value('@InvocieId', 'nchar(20)'), Tbl.Col.value('@InvAmount', 'numeric(18,2)'), Tbl.Col.value('@SettledAmt', 'numeric(18,2)'), Tbl.Col.value('@BalanceAmt', 'numeric(18,2)'), Tbl.Col.value('@Age', 'INT'), Tbl.Col.value('@Due', 'char(10)'), Tbl.Col.value('@Processed', 'bit'), Tbl.Col.value('@CurrencyName', 'nvarchar(40)'), Tbl.Col.value('@Symbol', 'nvarchar(40)'), Tbl.Col.value('@Invoicedate', 'datetime'), Tbl.Col.value('@Invoiceno', 'varchar(50)'), Tbl.Col.value('@Referenceno', 'varchar(100)'), Tbl.Col.value('@remarks', 'varchar(1000)') FROM @InputTable.nodes('//EmailList') Tbl(Col) order by Tbl.Col.value('@Age','INT') select * from @TempTable Regards Rajesh Kamalakshan

How to attach a mdf file to a database in SQL server 2005?

You can use the below command to attach a mdf file to a database in SQL Server 2005.

sp_attach_single_file_db @dbname= 'adminv2', @physname= C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\adminv2.mdf'

How to know the number of rows in all the tables in a SQL server 2005 database?

The blow query will show the number of rows in all the tables in a SQL server 2005 database


select distinct sysobjects.name,sysindexes.rows from sysobjects
inner join sysindexes on sysindexes.id=sysobjects.id
where sysobjects.xtype='u'
order by sysindexes.rows desc

or you can use the below querry to the same details

SELECT OBJECT_NAME(OBJECT_ID) TableName, st.row_count

FROM sys.dm_db_partition_stats st
WHERE index_id < 2
ORDER BY st.row_count DESC

when running job for sedning mails from SQL 2005 iam getting error like ,Executed as user: admin. INSERT failed because the following SET options have incorrect settings: 'ARITHABORT'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or query notifications and/or xml data type methods. [SQLSTATE 42000] (Error 1934) Mail queued. [SQLSTATE 01000] (Error 0). The step failed.,00:00:01,16,0,,,,0

This issues can be solved by putting the command SET ARITHABORT ON before you call the SP for sending mail in the scheduler.

SET ARITHABORT ON

How to Create stored procedure or View in SQL 2005 with encryption?

You can encrypt a view or an SP in SQL server 2005 by using the key work "with encryption" while creating the SP or View.


Please look into the example below.

create procedure MySp with encryption
as
begin
select * from EmailList
end

Once you created an sp or view with the above option added then it cannot be viewed by any users.if the suer tryes to view the above sp orview then it SQL server will show a message like . The text for object 'MySp' is encrypted.

One note of caution. Save the original text of the stored procedure before encrypting it, as there is no straightforward way to decode the encrypted text. One hack is to attach a debugger to the server process and retrieve the decrypted procedure from memory at runtime


Please look to the below link for how to decrypt the encrypted Sp or view.

http://www.sql-shield.com/decrypt-stored-procedure.html

How check a constraint with if exixts in sql server 2005?

By using below script you will be able to check the contraint with if exist.

if not exists(SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_NAME='CrmUserHirarchey' AND CONSTRAINT_NAME='FK_CrmUserHirarchey_aUsergroups' ) begin ALTER TABLE [dbo].[CrmUserHirarchey] WITH CHECK ADD CONSTRAINT [FK_CrmUserHirarchey_aUsergroups] FOREIGN KEY([UserGroupID], [CompanyId]) REFERENCES [dbo].[aUsergroups] ([UserGroupID], [CompanyID]) ALTER TABLE [dbo].[CrmUserHirarchey] CHECK CONSTRAINT [FK_CrmUserHirarchey_aUsergroups] end