Quotation marks in an exported csv file from sql

As probably known, we can export data to a csv file from the export wizard.

But, if the text columns have the “,” – that it is the column separate sign – all the csv file will be not arranged.

One possible solution is to declare Quotation marks to all columns, and it will help to separate the columns.

Update string add spaces to the input text

Can it be that we will try to update a string and SQL add spaces to it while updating the column?

Answer: Yes!

Char and nchar data types fill the string to the defined text.

An example: 

If a column declared as char 3, and we will run

UPDATE MyTable SET CharColumn = 'AA' WHERE id = 1

and the value there will be set to 'AA ' !

Include columns headers in csv files via grid results

There are few ways to save query results to files.

One of them is direct from the grid results:

But, when you will do it, you will find the csv file without the columns names:

Not so nice…

The solution is quite simple: declare in the options to get also the headers:

Done. Try again – but again – no columns header!!!

Now what?

The solution for that is the most common solution in the IT world: restart – restart SSMS.

Next time – you will get the headers.

Output list - Lookup columns

In order to see the columns we get from the lookup operation:

Right click on the lookup icon in the execution plan --> Output list

How to write Special Characters?

Question:

Try to type the sign '©', or maybe '¾'.

problem: no button for that.... :(

Solotion:

Press tab + numbers:

Alt 0169 = '©'

Alt 0190 = '¾'

Note: thias is not only SSMS trick... you can try it on notepad also :)

See all of the Special Characters Alt Keyboard Sequences here:

http://tools.oratory.com/altcodes.html

Find all permissions in all levels on a database

Here is a script that find all permissions in all levels on a database for a specific user (@FromUser) and make scripts of the to another one (@ToUser).

Notes:

1. If you want to get the scripts to the current user, put the same name in both parameters @FromUser and @ToUser.

2. Those queris without the user filter will return all the permissions to all users in the database.

SET NOCOUNT ON

DECLARE       @FromUser sysname, @ToUser sysname

SELECT @FromUser = 'FromUserName', @ToUser = 'ToUserName'

SELECT 'USE' + SPACE(1) + QUOTENAME(DB_NAME()) AS '--Database Context'

--Role Memberships:

SELECT 'EXEC sp_addrolemember @rolename ='

              + SPACE(1) + QUOTENAME(USER_NAME(rm.role_principal_id), '''') + ', @membername =' + SPACE(1)

              + QUOTENAME(@ToUser, '''') AS '--Role Memberships'

FROM   sys.database_role_members AS rm

WHERE  USER_NAME(rm.member_principal_id) = @FromUser

ORDER BY rm.role_principal_id ASC

--Object Level Permissions:

SELECT CASE WHEN perm.state <> 'W' THEN perm.state_desc ELSE 'GRANT' END

              + SPACE(1) + perm.permission_name + SPACE(1) + 'ON ' + QUOTENAME(SCHEMA_NAME(obj.schema_id)) + '.' +QUOTENAME(obj.name)

              + CASE WHEN cl.column_id IS NULL THEN SPACE(0) ELSE '(' + QUOTENAME(cl.name) + ')' END

              + SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(@ToUser) COLLATE database_default

              + CASE WHEN perm.state <> 'W' THEN SPACE(0) ELSE SPACE(1) + 'WITH GRANT OPTION' END AS '--Object Level Permissions'

FROM          sys.database_permissions perm

JOIN          sys.objects obj                          ON perm.major_id = obj.[object_id]

JOIN          sys.database_principals usr ON perm.grantee_principal_id = usr.principal_id

LEFT JOIN     sys.columns cl                           ON cl.column_id = perm.minor_id AND cl.[object_id] =perm.major_id

WHERE  usr.name = @FromUser

ORDER BY perm.permission_name ASC, perm.state_desc ASC

--Database Level Permissions

--D = Deny, R = Revoke, G = Grant, W = Grant With Grant Option

SELECT CASE WHEN perm.state <> 'W' THEN perm.state_desc ELSE 'GRANT' END

       + SPACE(1) + perm.permission_name + SPACE(1)

       + SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(@ToUser) COLLATE database_default

       + CASE WHEN perm.state <> 'W' THEN SPACE(0) ELSE SPACE(1) + 'WITH GRANT OPTION' END AS '--Database Level Permissions'

FROM   sys.database_permissions AS perm

       INNER JOIN

       sys.database_principals AS usr

       ON perm.grantee_principal_id = usr.principal_id

WHERE  usr.name = @FromUser

AND    perm.major_id = 0

ORDER BY perm.permission_name ASC, perm.state_desc ASC

-- Schema Level Permission

SELECT CASE

                     WHEN perm.state <> 'W' THEN perm.state_desc --W=Grant With Grant Option

                     ELSE 'GRANT'

              END

                           + SPACE(1) + perm.permission_name --CONNECT, etc

                           + SPACE(1) + 'ON' + SPACE(1) + class_desc + '::' COLLATE database_default --TO <user name>

                           + QUOTENAME(SCHEMA_NAME(major_id))

                           + SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(@ToUser) COLLATE database_default

                           + CASE

                                  WHEN perm.state <> 'W' THEN SPACE(0)

                                  ELSE SPACE(1) + 'WITH GRANT OPTION'

                             END

                     AS '-- Schema Level Permission --'

from sys.database_permissions AS perm

       inner join sys.schemas s

              on perm.major_id = s.schema_id

       inner join sys.database_principals dbprin

              on perm.grantee_principal_id = dbprin.principal_id

WHERE class = 3 --class 3 = schema

and dbprin.name = @FromUser

ORDER BY perm.permission_name ASC, perm.state_desc ASC

--Server Level Permissions

SELECT  server_permissions.state_desc COLLATE SQL_Latin1_General_CP1_CI_AS + ' ' + server_permissions.permission_nameCOLLATE SQL_Latin1_General_CP1_CI_AS

        + ' TO [' + server_principals.name COLLATE SQL_Latin1_General_CP1_CI_AS + ']' AS '--Server Level Permissions'

FROM    sys.server_permissions AS server_permissions WITH (NOLOCK)

        INNER JOIN sys.server_principals AS server_principals WITH (NOLOCK) ON server_permissions.grantee_principal_id =server_principals.principal_id

WHERE   server_principals.type IN ('S', 'U', 'G')

AND           server_principals.name = @FromUser

ORDER BY server_principals.name ,

        server_permissions.state_desc ,

        server_permissions.permission_name;

ISNUMERIC issue: Conversion failed when converting the nvarchar value to data type int

SELECT ... ,CONVERT(int, CAST(MyColumn AS float))   [Points] 

FROM ...

WHERE ISNUMERIC(MyColumn) = 1

Error:

cast failed: Conversion failed when converting the nvarchar value to data type int

But..... we check that ISNUMERIC(MyColumn) = 1, so why it is failed?

Cause:

Some charactes that are not numbers are return from ISNUMERIC as numeric, for example:

select

       ISNUMERIC('-'),

       ISNUMERIC('+'),

       ISNUMERIC('$'),

       ISNUMERIC('.'),

       ISNUMERIC(','),

       ISNUMERIC('\')

will return 1 to all!

Solution: TRY_PARSE

in the conditions, check WHERE TRY_PARSE (MyColumn AS float) IS  NOT NULL

SELECT ... ,CONVERT(int, CAST(MyColumn AS float))   [Points] 

FROM ...

WHERE TRY_PARSE (MyColumn AS float) IS  NOT NULL

TRY_PARSE tries to parse:

• if it can parse - it return the pared value,
• if not - it return NULL, and not fail the query!!!

so, if we check thet TRY_PARSE IS  NOT NULL, we can do the casting in the select.

Fake error in tSql code

just add a Raiseerror command:

RAISERROR ('fake message', 16, 1)

Return the data from XML as a table

declare @ColumnXML xml =

'<XmlValues>

  <XmlValue SomeId="1">aaaa</XmlValue>

  <XmlValue SomeId="2">bbbb</XmlValue>

  <XmlValue SomeId="3">cccc</XmlValue>

</XmlValues>

'

-- get all XML records

SELECT T.c.query('.') AS result 

FROM   @ColumnXML.nodes('/XmlValues/XmlValue') T(c) 

-- return the data from the xml as a table

SELECT T.c.value('@SomeId','nvarchar(100)') as SomeId,

              T.c.value('.', 'varchar(50)') as SomeValue

FROM   @ColumnXML.nodes('/XmlValues/XmlValue') T(c) 

-- also you can do filters:

where T.c.value('@SomeId','nvarchar(100)') = 2

DISTRIBUTED TRANSACTION - errors and fixes

Open DISTRIBUTED TRANSACTION between servers

http://copypastenet.blogspot.co.il/2016/06/transaction-between-servers-distributed.html

OLE DB provider "SQLNCLI11" for linked server "" returned message "Cannot start more transactions on this session."

http://copypastenet.blogspot.co.il/2016/06/ole-db-provider-sqlncli11-for-linked.html

The Microsoft Distributed Transaction Coordinator (MS DTC) has cancelled the distributed transaction.

http://copypastenet.blogspot.co.il/2016/06/the-microsoft-distributed-transaction.html

The Microsoft Distributed Transaction Coordinator (MS DTC) has cancelled the distributed transaction

Try to do:

DISTRIBUTED TRANSACTION in try-catch block.

Error message:

The Microsoft Distributed Transaction Coordinator (MS DTC) has cancelled the distributed transaction.

Solution:

remove the try-catch block and see the exact error. then fix it - it probably some 'standard' SQL error.

but - if you want/need to see the errors:

run dynamic sql from the linked server to the current one:

http://copypastenet.blogspot.co.il/2016/06/run-dynamic-sql-from-linked-server-to.html

OLE DB provider "SQLNCLI11" for linked server "" returned message "Cannot start more transactions on this session."

Try to do:

DISTRIBUTED TRANSACTION

Error message:

OLE DB provider "SQLNCLI11" for linked server "" returned message "Cannot start more transactions on this session."

Solution:

In order to enable the distributed transactions set:

SET XACT_ABORT ON;

Run dynamic sql from the linked server to the current one

SET XACT_ABORT ON; -- In order to enable the distributed transactions

BEGIN DISTRIBUTED TRANSACTION

DECLARE @SQL nvarchar(max)

BEGIN TRY

       set @SQL = 'REVERT; EXECUTE AS LOGIN = ''SA''

              EXECUTE(''

       UPDATE / INSERT / .....

       FROM [LinkedServerDataBaseName].[LinkedServerSchemaName].[LinkedServerTableName] b

       JOIN [MyServerNameAsLinkedServerName].[DataBaseName].[SchemaName].[TableName] ns  ON b.BRN_ID = ns.BRN_ID

       WHERE .......

              '') AT [LinkedServerName]';

       exec sp_executesql @SQL

       COMMIT TRANSACTION

END TRY

BEGIN CATCH

       -- ROLLBACK TRANSACTION

       -- do something....

END CATCH

SET XACT_ABORT OFF;

LinkedServerDataBaseName - database name in the linked server

MyServerNameAsLinkedServerName - my server has to be declared as linked server in the other server, this is it's linked server name there.

LinkedServerName - the linked server name in my server