Setting the Timeout for the WinRM - SQL Server DB Deploy

The parameters to the WinRM –SQL Sever DB Deploy task in VSTS can be used do a backup using the inline sql script.It is a good idea to set the additional arguments  to be -ConnectionTimeout 120 -QueryTimeout 120 (for two minutes of timeout). Set the number of seconds to a reasonable value for your system.

If you don't and your backup exceeds the default timeout (90 seconds I believe), then you will get an error like this:

##[error]Microsoft.PowerShell.Commands.WriteErrorException: Deployment on one or more machines failed.

System.Exception: The running command stopped because the preference variable "ErrorActionPreference"

or common parameter is set to Stop: Timeout expired. The timeout period elapsed prior to completion

of the operation or the server is not responding.

It is actually SQL Server complaining that the time has elapsed, but it is doing so based on wht the WinRM says is the timeout.

To set the timeout open your WinRM - SQL Sever DB Deploy task in VSTS and set the Additional Arguments to  -ConnectionTimeout 120 -QueryTimeout 120.

Remove alpha characters from string using SQL

If you have a string that has both numbers and alpha characters in it and want to remove all letters A-Z then this is a simple function that you can use on a column in SQL Server.


create function RemoveAlphas(@Text as nvarchar(255))
returns nvarchar(255)
as
BEGIN
Declare @Result as nvarchar(255)
Set @Result = @Text
Set @Result = Replace(@Result, 'A', '')
Set @Result = Replace(@Result, 'B', '')
Set @Result = Replace(@Result, 'C', '')
Set @Result = Replace(@Result, 'D', '')
Set @Result = Replace(@Result, 'E', '')
Set @Result = Replace(@Result, 'F', '')
Set @Result = Replace(@Result, 'G', '')
Set @Result = Replace(@Result, 'H', '')
Set @Result = Replace(@Result, 'I', '')
Set @Result = Replace(@Result, 'J', '')
Set @Result = Replace(@Result, 'K', '')
Set @Result = Replace(@Result, 'L', '')
Set @Result = Replace(@Result, 'M', '')
Set @Result = Replace(@Result, 'N', '')
Set @Result = Replace(@Result, 'O', '')
Set @Result = Replace(@Result, 'P', '')
Set @Result = Replace(@Result, 'Q', '')
Set @Result = Replace(@Result, 'R', '')
Set @Result = Replace(@Result, 'S', '')
Set @Result = Replace(@Result, 'T', '')
Set @Result = Replace(@Result, 'U', '')
Set @Result = Replace(@Result, 'V', '')
Set @Result = Replace(@Result, 'W', '')
Set @Result = Replace(@Result, 'X', '')
Set @Result = Replace(@Result, 'Y', '')
Set @Result = Replace(@Result, 'Z', '')
return @Result
END

Usage:

select dbo.RemoveAlphas('123abc456DEF')

Returns 123456

Using T-SQL to format date as yyyy-mm-dd

Surprisingly, MS SQL Server doesn't provide custom date formatting. Instead you need to use one of their existing formats or use C# to implement it, but the later seems a bit overkill for our purposes here. The formats are defined here. Below are some ways to get a datetime or date column to print out in the yyyy-mm-dd format.

Method 1
This is simple and straight forward. Interestingly, I don't see it defined here, but it works.

WARNING:
Since it is not documented it is up to you if you want to use it or not. It has been around for many years, but it is unknown if it will be there in the future. Use this option at your own risk.

In this example, the length of 20 is used to show it doesn't matter, but any size could be used since it actually gives us the format we are looking for.

SELECT CONVERT(NVARCHAR(20), GETDATE(), 23)

That will give you 2014-07-09.

NOTE: If you would like explore other undocumented formats, check this page out.

Method 2

A clever way is to use the 126 format which has hours, minutes, seconds, etc in it and just take the first 10 characters which is in the format yyyy-mm-dd. We could get a substring, but there really isn't a need since it will be implicitly truncated to 10 characters when we use char(10) or varchar(10) as our datatype we are converting to.

SELECT CONVERT(char(10), GetDate(),126)

That will give you 2014-07-09.


Method 2
If you have slightly different format requirement such as slashes or
select Replace(convert(nvarchar(10), GETDATE(), 102), '.', '-')

This works very simply because 102 is defined in the format yyyy.mm.dd and I am just replacing the periods with dashes. Pretty simple and effective.

Method 3
If you want it in the format yyyymmdd (no dashes) then you can use the 112 format as shown below.

select convert(nvarchar(10), GETDATE(), 112)

That will give you 20140709.

Get a list of tables in SQL Server that don't have primary keys defined

In SQL Server it is a best practice for all tables to have primary keys defined. A primary key is really a constraint. Below is a query to get a list of tables and the name of the primary key contstraint that is associated with that table. If the CONSTRAINT_NAME column is null then it doesn't have a primary key defined.


select t.TABLE_SCHEMA, t.TABLE_NAME, c.CONSTRAINT_NAME
from INFORMATION_SCHEMA.TABLES t 
left outer join INFORMATION_SCHEMA.TABLE_CONSTRAINTS c 
on (t.TABLE_SCHEMA = c.TABLE_SCHEMA and t.TABLE_NAME = c.TABLE_NAME and t.TABLE_TYPE = 'BASE TABLE' and c.CONSTRAINT_TYPE = 'PRIMARY KEY')
order by TABLE_NAME


You can add a where clause such as

where c.CONSTRAINT_TYPE is null 

to filter the results to just the tables that don't have a primary key.

Find blank rows in a SQL Server Database

After importing data into a SQL Server database there are sometimes blank rows that get created depending on what your data source looks like. Often when using Excel as a data source extra rows will be created with all blank values. Since the table doesn't by default have a primary key all columns can be null. Find what tables have blank rows and then deleting them can be time consuming. The script here will make this much easier.

Disclaimer

I have used this script successfully on my databases, but please, please, please make a backup of your database BEFORE you execute the following since it can affect all your tables. I am of course not responsible for any data loss caused by this script. 

Executing the script below does NOT actually do the deletes. You will still need to copy and paste the generated SQL into SSMS and execute it. I highly suggest you read the generated SQL to make sure it is doing what you want it to before you do the final execution of the generated sql.

create table #BlankRowCounts(TableName nvarchar(255), NumBlankRows int)

Declare @SQL as nvarchar(MAX)

select

    @SQL = ISNULL(@SQL + ' union ' , '')

    + 'select ' +

'''' + TABLE_NAME + ''' as TableName, ' +

'COUNT(1) as NumBlankRows' +

' from ' +

'[' + TABLE_NAME + ']' +

' where ' +

dbo.GetColumnList(TABLE_NAME, 1, ' is null and ') + ' is null'

from INFORMATION_SCHEMA.TABLES

where TABLE_TYPE = 'BASE TABLE'

ORDER BY TABLE_NAME

insert into #BlankRowCounts(TableName, NumBlankRows)

exec sp_executesql @SQL

select TableName, NumBlankRows,

'select ' +

'''' + TableName + ''' as TableName, ' +

dbo.GetColumnList(TableName, 1, ', ') + 

' from ' +

'[' + TableName + ']' +

' where ' +

dbo.GetColumnList(TableName, 1, ' is null and ') + ' is null' as SelectStmt,

'delete from ' +

'[' + TableName + ']' +

' where ' +

dbo.GetColumnList(TableName, 1, ' is null and ') + ' is null' as DeleteStmt

from

#BlankRowCounts

order by NumBlankRows desc

drop table #BlankRowCounts

IMPORTANT
You will also need to get the code for GetColumnList() function here.

The results of this SQL are simple. There are four columns:

• TableName - The table for which the statements will affect
• NumBlankRows - The number of rows in the table (see TableName) that have all blank columns
• SelectStmt - The select statement you can copy and paste into SSMS to actually see for yourself that the columns are null. You don't have to execute these, but they are here to convince yourself that the data is blank.
• DeleteStmt - The delete statement you can copy and paste into SSMS to actually do the deleting of the rows that have all the columns as null. I highly recommend reading this BEFORE you execute it. Also, consider backing up your data if the data is important to you.

Get Comma Separated List of Columns for a Table using T-SQL

Using T-SQL (Microsoft SQL Server) you can get a list of columns (delimited by commas or other delimiter of choice) for a given table using one of the function below. There are two ways to call it. Either one words, but it is up to your personal preferences and also how safe the column names are as to which function you use.

If ever in doubt, pass a 1 for @IncludeBrackets is the safest because it puts all the column names in square brackets. This allows column names to have spaces and other special characters that would not normally be allowed. This often happens when importing data from Excel and using the default names for the columns. This is because it uses the column headings in Excel which typically have spaces in them because they are meant to be human readable.

With that said, if I created the columns I always use Just alphanumeric characters and no spaces, etc so my column names are known to be safe. In this scenario, I personally feel it is easier to read the column names without the brackets so in this case I pass 0 for the @IncludeBracket parameter.

The first parameter is simply the table name.

Basic Usage

To use the function on a table called Person do the following.

select dbo.GetColumnList('Person', 1, ', ')
sample results: [FirstName], [LastName], [Phone]
or
select dbo.GetColumnList('Person', 0, ', ')

sample results: FirstName, LastName, Phone

Function Definition (Code)

Here is the code to create the SQL function



create function GetColumnList(@TableName as nvarchar(255), @IncludeBrackets as bit, @Delimiter as nvarchar(500))
returns nvarchar(max)
as
BEGIN
Declare @ColumnList as nvarchar(MAX)
Declare @BeginningBracket as nvarchar(1)
Declare @EndingBracket as nvarchar(1)

if @IncludeBrackets = 1
BEGIN
SET @BeginningBracket = '['
SET @EndingBracket = ']'
END
else
BEGIN
SET @BeginningBracket = ''
SET @EndingBracket = ''
END

select
@ColumnList = ISNULL(@ColumnList + @Delimiter, '')
+ @BeginningBracket + COLUMN_NAME + @EndingBracket
from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = @TableName
ORDER BY COLUMN_NAME

return @ColumnList
END

Advances Usage

If you want to do the same thing, but for all tables in your database you can do something like the following. You can of course add a where clause, etc to select just the sames you want as well.


select 
dbo.GetColumnList(TABLE_NAME, 1, ', ')
from INFORMATION_SCHEMA.TABLES

FREE or Open Source or Inexpensive options to compare data (in tables) in SQL Server

Intro

Visual Studio 2012 (an some earlier editions) include Schema comparison for SQL Server. This is NOT what I am writing about here today. Assuming you have used this tool to make tables, etc the same you may have a need like I do when moving data from dev to production databases or the reverse. There are several ways to go about this.

FREE - SQL Server Data Tools 

Probably the best place to start is SQL Server Data Tools which is available from Microsoft. It includes among other things the ability to compare data in SQL Server tables.It is available for Visual Studio 2010 and newer. There was an option in some editions of Visual Studio 2010, but not in Visual Studio 2012. To get the functionality in Visual Studio 2012 you need the SQL Server Data Tools to be installed. Once you have it installed you will have functionality very similar to what was available in VS 2010 or the RedGate product. Here is a direct link for the download of the English ISO. One of the nice things about this option is that it is well integrated into Visual Studio 2012 and uses the same source and destination configurations as the SQL Schema Comparison that is built into VS 2012.You can also select what tables you want to compare, what columns in the tables, if you want source or destination records, etc. It will just to the update for you or you can have it generate the SQL Script that you can manually. It gives you a nice visual representation of the differences and let's you select the rows you want to change. It seems to be pretty fast. The generated SQL script even disables constraints as needed. It also seems to handle nulls properly. This is a very nice option for free!

It appears it can be called from the command line as well, but I have not tried it.

Here is the blog for the SSDT team.

FREE - tablediff.exe

IMHO, this may be the best choice for scripted options. Believe it or not tablediff.exe is a utility that comes with SQL Sever 2005 or greater. I believe this is the tool that SQL Server uses when replicating tables, though that is just what I read from someone else. It will tell you on a row by row and column by column basis what is different. It will even generate the SQL scripts needed to make the destination table look like the source table. As far as I know you cannot download it separately. However, it is installed when you install SQL Sever 2005 or newer and you choose SQL Server Replication feature. In SQL Server 2008 R2 it is included by default, but I'm not sure about the other versions. On my machine it was located at C:\Program Files\Microsoft SQL Server\100\COM\tablediff.exe. Once you find it you can type tablediff.exe -? for the options or refer here to the documentation. The parameters are pretty well documented and easy to follow to I won't go into all the options, but here is an example of how you would generate a change script (SQL) and see what the differences are.

C:\Program Files\Microsoft SQL Server\100\COM>tablediff.exe -sourceserver MySrcServer-sourcedatabase MyDevDB -sourceschema dbo -sourcetable Person -sourceuser User1 -sourcepassword User1Pwd -destinationserver MyDestServer -destinationdatabase MyProdDB -destinat

ionschema dbo -destinationtable Person -destinationuser User1 -destinationpassword User1Pwd -c -o c:\temp\diff.txt -f c:\temp\diffgen.sql

This will generate two files. diff.txt which will have the differences, and diffgen.sql which will be the SQL script you can execute to make the destination table the same as the source table.

WARNING:

Be careful, the SQL will also generate delete statements for your destination table. This may or may not be what you want so just be aware. I recommend backing up your destination table before doing this operation.

Also, it doesn't appear to generate correct scripts for null. It put null in single-quotes. This can be changed easily with a search and replace though.

Keep in mind this is per table. If you have lots of tables and you want them all to be updated it could be a done also, but it a bit tedious. However, the nice thing about this tool is since it is command line once you have it setup you can run it again and again with little to no effort.

If you want you can even download a GUI for the command line tool. NOTE: You still need the command line tool. I was not able to get it to work with SQL Server 2008 R2, but you can decompile it and fix it I'm sure. Does it work for anyone else? All it does is exactly what you do on the command line so I'm not sure I really see the benefit if you are going to want to run it more than once.

Inexpensive - SQLDBTools

If I have a little money to spend on the solution. SQLDBTools costs $60 at seems to do pretty much what the RedGate products do, but for a fraction of the cost. It does do schema and data comparisons which is nice to have it all in one tool. I does visually let you see the differences and it also generates the change script. I have not tried this product, but it looks like the best product for the money (if you are going to pay for a solution). Given that SQL Server Data Tools now exists I don't really see the advantage or reason to pay for this tool.

FREE - Linked Server

This is a less desirable option because it doesn't do anything automatically for you. After you have a linked server you can do queries to see the differences between tables. This does nothing for automatically generating the update scripts. This solution is good for analysis only.

Inexpensive - Beyond Compare

Beyond Compare does a nice job of visually showing the differences in two files. It doesn't know anything about SQL or databases, but as long as you use SSMS to export the data to CSV, Excel, or tabular data Beyond Compare will show you the differences in an Excel like manner. Again, this does nothing for generating the change script and could be slow for very large tables.This is best for comparing two adhoc queries in my opinion.

Additional Info

I found this site that has a pretty exhaustive list of tools for SQL Server that may be useful.

Removing duplicate rows in SQL Server

If you are using SQL Server 2005 or greater (we need the CTE (Common Table Expression) functionality) you can in one statement delete duplicate rows from a table. You can look at all columns in the table or you can look at a subset of the columns. The example below uses just one column (EmailAddress), but you can replace that one column with a comma separated list of the columns you want to consider.

Before we go off and start running delete scripts on our data, it is always a good idea to make sure you have a backup of the table so you can check you work after or restore if something goes wrong.

Let's first look at the data

-- see what rows have duplicates and which ones don't
SELECT  EmailAddress
      , row_number() OVER ( PARTITION BY EmailAddress ORDER BY ID) AS NumInstances
FROM    Person

-- show only the rows that are duplicates
with myTable as (
SELECT  EmailAddress
      , row_number() OVER ( PARTITION BY EmailAddress ORDER BY ID) AS NumInstances
FROM    Person
)
select * FROM myTable
    WHERE   NumInstances > 1

The actual delete statement

-- do the actual deleting of the duplicate rows
with myTable as (
SELECT  EmailAddress
      , row_number() OVER ( PARTITION BY EmailAddress ORDER BY ID) AS NumInstances
FROM    Person
)
DELETE  FROM myTable
    WHERE   NumInstances > 1

To modify the code to fit your situation you will typically just need to replace the items in red with the comma separated list of columns you want to consider. Next change the table name in blue to the table you are trying to remove the duplicates from. Finally, change the column in orange to be one or more columns (separated by comma). In this case, I have a primary key so, I used it. You could use all the columns you are considering as well.

For more info on how it works and pre-SQL 2005 solutions see here.

MySql error message: Parameter '@A' must be defined using .NET connector

I upgraded my MySql .NET connector (MySql.Data.dll) from 1.0.7.30072 to a much newer 6.5.4.0 version. I was expecting complete backward compatibility, but I was completely wrong in my assumption. Instead I started getting the following error.

Fatal error encountered during command execution.  

  at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)

The problem is that “Fatal error encountered during command execution” was not particularly useful. As it turned out we had accidentally deployed the new MySql.Data.dll to QA and production without noticing that some queries failed with the above message. Then I tried to reproduce the problem on my laptop and could not. My project in Visual Studio 2010 had a reference to v1 of the MySql.Data.dll. Once I figured out there was a different version that snuck into our development environment and made it to production I figured that was the issue. So, I changed the reference in VS2010, but it seemed to be using the older version still. I’m not sure exactly how and I didn’t take the time to figure out why, and only made everything more confusing. In the end, I created a command line app, and added the new version of the MySql.Data.dll to the project and added just the snippet of code that was breaking. Thank goodness I could now reproduce the error on my laptop. Now I looked at the inner exception, and I see what the real error message is:

Parameter '@A' must be defined.

Okay, now we are getting somewhere. After a bit of searching I figured out that I had to add “Allow User Variables=True” to my connection string (not the SQL, but the connection string in the config file).

The solution: Just add

Allow User Variables=True

to your config file and you can now use user defined variables in your sql statements that you pass to the MySql .NET connector.

Finding Currently Running Query using T-SQL

To find out what queries are currently running on your SQL Server try the following query.

SELECT sqltext.TEXT,
req.session_id,
req.status,
req.command,
req.cpu_time,
req.total_elapsed_time
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext

Keep in mind that the results of this query will include this query itself, so the result will always be at least one row returned.

If you decide you want to kill one of the queries you can use the kill sql statement and the session id (see the second column).

The system is simple:

KILL <session id here>

I owe the basis for this post to the following post: http://blog.sqlauthority.com/2009/01/07/sql-server-find-currently-running-query-t-sql/

Get a list of all user defined stored procedures, functions, etc

If you are using SQL Server 2005 and newer you can use the queries below to get a list of any user defined object (including, but not limited to stored procedures, scalar-valued functions, table-valued functions, aggregate functions, and views) for a given database on a SQL Server 2005 installation. For a complete list of objects that you can list click here. The queries below will give you the same results you get when you look in Object Explorer in SSMS (Microsoft SQL Server Managements Studio).

All User Defined Objects

This will give you all the objects listed here.

-- all user defined objects
SELECT  sys.schemas.name + '.' + sys.objects.name, type, type_desc
FROM    sys.objects
INNER JOIN sys.schemas ON sys.objects.schema_id = sys.schemas.schema_id
order by 3,1

Common User Defined Objects

This will give you a list of the following user defined objects: stored procedures, views, table-values functions, scalar-valued functions, aggregate functions.

-- common user defined objects
SELECT  sys.schemas.name + '.' + sys.objects.name, type, type_desc
FROM    sys.objects
INNER JOIN sys.schemas ON sys.objects.schema_id = sys.schemas.schema_id
where type in
(
'p', 'pc', -- stored procs
'v', -- views
'tf', 'if', 'ft', -- table-valued functions
'fn', 'fs', -- scalar-valued functions
'af' -- aggregate functions
)
order by 3,1

User Defined Stored Procedures

-- user defined stored procs
SELECT  sys.schemas.name + '.' + sys.objects.name
FROM    sys.objects
INNER JOIN sys.schemas ON sys.objects.schema_id = sys.schemas.schema_id
where type in ('p', 'pc')
order by 1

User Defined Views

-- user defined views
SELECT  sys.schemas.name + '.' + sys.objects.name
FROM    sys.objects
INNER JOIN sys.schemas ON sys.objects.schema_id = sys.schemas.schema_id
where type='v'
order by 1

User Defined Table-Valued Functions

-- user defined table-valued functions
SELECT  sys.schemas.name + '.' + sys.objects.name
FROM    sys.objects
INNER JOIN sys.schemas ON sys.objects.schema_id = sys.schemas.schema_id
where type in ('tf', 'if', 'ft')
order by 1

User Defined Scalar-Valued Functions

-- user defined scalar-valued functions
SELECT  sys.schemas.name + '.' + sys.objects.name
FROM    sys.objects
INNER JOIN sys.schemas ON sys.objects.schema_id = sys.schemas.schema_id
where type in ('fn', 'fs')
order by 1

User Defined Aggregate-Valued Functions

-- user defined aggregate-valued functions
SELECT  sys.schemas.name + '.' + sys.objects.name
FROM    sys.objects
INNER JOIN sys.schemas ON sys.objects.schema_id = sys.schemas.schema_id
where type = 'af'
order by 1

Stored procedure for page results and sort by different columns

 

Here is a stored procedure for MS SQL Server that can be used to page the results and allow it to be sorted by different columns. It is fast and easy to use. Enjoy.

--GetPersonPage2005 5, 20, 'NAME DESC'
--GetPersonPage2005 null, 20, 'NAME DESC'
--GetPersonPage2005 5, 20, 'SEARCH_CODE ASC'

alter proc GetPersonPage2005
@StartRowIndex decimal(18,0),
@MaximumRows int,
@OrderBy varchar(50)
as

SELECT [NAME], SEARCH_CODE, CREATED
FROM
     (SELECT [NAME], SEARCH_CODE, CREATED,
               ROW_NUMBER() OVER
            (
            ORDER BY -- add columns to sort by here
                CASE @OrderBy WHEN 'NAME DESC' THEN  [NAME] END DESC,
                CASE @OrderBy WHEN 'NAME ASC' THEN  [NAME] END ASC,
                CASE @OrderBy WHEN 'SEARCH_CODE DESC' THEN  SEARCH_CODE END DESC,
                CASE @OrderBy WHEN 'SEARCH_CODE ASC' THEN  SEARCH_CODE END ASC,
                CASE @OrderBy WHEN 'CREATED DESC' THEN  CREATED END DESC,
                CASE @OrderBy WHEN 'CREATED ASC' THEN  CREATED END ASC
            ) as RowNum
      FROM ALL_PERSON e
     ) as Tbl
WHERE RowNum BETWEEN @StartRowIndex AND (@MaximumRows + @StartRowIndex - 1)   
Order by RowNum ASC
go

How to copy Oracle data to MS SQL Server

 

This tutorial gives you the bare minimum information to copy data from an Oracle database to a MS SQL Server database using SSIS (Integration Services).

Edit tnsnames.ora

Create an entry in tnsnames.ora file. This is a file that Oracle uses to abstract the server, port, etc and make it easy to reference. This file resides on the machine that SSIS is running from. The changes will also need to be done on the server were SSIS runs. If you don’t have Oracle installed on that server, you need to do that also.

Here is an example that we will use in this example:

TEMPTEST =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = serverNameHere)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = ORACLETEST)

)

)

You can make sure you have this configured properly by opening up a command prompt and typing

sqlplus username/password@TEMPTEST

If you get a SQL> prompt then you have configured the tnsnames.org entry ok.

Create a project

In Business Intelligence Studio or Visual Studio if you have it, create a new Business Intelligence project.

Add an Oracle Connection

There are two types. One that is from Microsoft and the other that is from Oracle. Either one will technically work.

MS OLE DB Provider for Oracle

This requires an entry in the tnsnames.ora file that matches the name of the Server you enter in the configuration dialog for the OLE DB datasource.

Click the Test Connection to verify it works.

Oracle Provider for OLE DB

This requires an entry in tnsnames.ora file that matches the Server you enter in the configuration dialog for the OLE DB datasource.

Click the Test Connection to verify it works.

Add MS SQL Server Connection

There are like Oracle several options for connecting to a MS SQL Server database. In this example, I am going to use OLE DB. Right-Click the Connection Managers area and choose New ADO.NET connection. In this case we are connecting to a named instance called SQL2005. If you are using the default instance of SQL Server, then just remove the \SQL2005 from this example. The name of the database is called TestSSISDB. The user is also called TestSSISDB.

Add Data Flow Task

Drag the Data Flow Task onto your Control Flow tab / page. Rename the task something like “Copy Data”. Double-click the task to open the Data Flow tab.

Add OLE DB Source

Drag the OLE DB Source onto the Data Flow tab.

Rename it something like My Oracle Data Source.

Double-click the OLE DB Source to open the Editor.

Enter information like the following:

Use the Preview button to make sure your query works.

Create the Destination Table

Create a table in TestSSISDB using MS SQL Server Management Studio or like tool.

The table should have a ID (identity column), object_id, first_name, last_name.

Use the following to create it if you prefer:

BEGIN TRANSACTION

GO

CREATE TABLE dbo.LocalPerson

(

ID int NOT NULL IDENTITY (1, 1),

Object_ID varchar(100) NULL,

First_Name varchar(500) NULL,

Last_Name varchar(500) NULL

) ON [PRIMARY]

GO

ALTER TABLE dbo.Table_1 ADD CONSTRAINT

PK_Table_1 PRIMARY KEY CLUSTERED

(

ID

) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO

COMMIT

Add Copy Column Transformation

Drag the Copy Column Data Flow Transformation to the Data Flow tab.

Connect the arrows to and from this transformation to the data source and destination.

Double-Click transformation and click the columns you want to copy.

Add OLE DB Destination

Drag the OLE DB Destination onto the Data Flow tab.

Rename it something like TestSSISDB SQL Destination.

Double-click the OLE DB Source to open the Editor.

Enter information like the following:

Be sure to select the right connection manager.

Review the Mappings, and Advanced Options.

Configuring Encryption of Sensitive Data

It is important that you configure how encryption will be handled. To do so, make sure the Properties panel is visible. Click the page / background on one of the tabs (i.e. Control Flow or Data Flow). You will see a lot of properties in the panel. At the top of the panel it should say Package <package name>. Enter a password in the PackagePassword field, and change the Protection level to EncryptSensitiveWithPassword. Note that the password will not show in the connection strings or in the PackagePassword field. It should look something like this.

Build package

Testing Package

F5 to run.

Deploying Package

Locate the tnsnames.ora file and make the same change as we did to the development machine. Be sure to test the configuration as we did for the development machine also.

Copy Package.dtsx file to server.

Open up MS SQL Server Management Studio and connect to the Integration Services found on the server where it will run.

Under Stored Packages | MSDB right click MSDB and choose Import Package…

Import package as shown below:

Be sure to select “Encrypt sensitive data with password” from the Protection Level field. If you don’t, the package will run by itself, but it will NOT run as a job.

It should prompt you for the password you entered in the PackagePassword field in Visual Studio property panel for package. If it doesn’t prompt you for the password, the package in Visual Studio is not configured correctly. Be sure you built the package after configuring the password.

Test Deployment

To test your deployment, right click the package you imported and choose Run Package. If it has issues, go back to Visual Studio and add logging to a text file and consume appropriate events to help debug the package.

More information

Here is a good tutorial to get you start or give you more details than this tutorial does.

http://msdn2.microsoft.com/en-us/library/ms167031.aspx

Truncating a table from a Linked Server

Let’s assume you are using SQL Server and you want to truncate a table on a Linked Server but are getting errors about permissions.

Msg 4701, Level 16, State 1, Line 1

Cannot find the object "MyTable" because it does not exist or you do not have permissions.

The first thing to check is that you have the proper permissions. MSDN says

“The minimum permission required is ALTER on table_name. TRUNCATE TABLE permissions default to the table owner, members of the sysadmin fixed server role, and the db_owner and db_ddladmin fixed database roles, and are not transferable. However, you can incorporate the TRUNCATE TABLE statement within a module, such as a stored procedure, and grant appropriate permissions to the module using the EXECUTE AS clause.”

Assuming you do actually have permission the problem is probably in the syntax.

My guess is that you tried the same thing I did to start with, which is the following.

truncate table MyLinkedServer.MyDB.dbo.MyTable

You can use truncate table statement on a Linked Server, but not with that syntax and you have to know a trick. Here is the same statement, but using a different syntax.

EXEC MyLinkedServer.MyDB.sys.sp_executesql N'truncate table dbo.MyTable'

It seems a bit convoluted to me, but it works.

NOTE: If you get a message about … is not configured for RPC then click here to read how to fix that issue.

How to resolve: Msg 7411, Level 16, State 1, Line 1 Server 'MyServer' is not configured for RPC.

 

If you get the following error when trying to execute a stored procedure on a Linked Server in MS SQL Server you need to enable RPC on the server that has the linked server defined (I called my linked serverMyServer in this example)

Msg 7411, Level 16, State 1, Line 1

Server 'MyServer' is not configured for RPC.

The solution is simple, just execute the following SQL on the server where you have defined the Linked Server. You will need to change MyServer to the name of your linked server.

EXEC master.dbo.sp_serveroption @server=N'MyServer', @optname=N'rpc', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'MyServer', @optname=N'rpc out', @optvalue=N'true'

You need RPC enabled on the Linked Server so that you can called stored procedures. In most cases you only need to do the second one that allows out, but if you have problems you can do the first one also. Most people recommend doing both even though both are not needed in many cases.

Getting the status (including In Progress) of SQL Job in SQL Server 2005+

I find it amazing how many posts there on forums, etc where people want to know what the status of SQL Server Jobs are and how difficult it was. The SysJobHistory table is good if you don’t care about jobs that are In Progress, but if you do even though it has a status of In Progress it will never show that status because the SysJobHistory record is not created until the job is Completed.

Here is the code if you don’t care about In Progress and want to use the SysJobHistory table. This would basically mean you want the last status of a job that completed.

select * from msdb.dbo.sysJobHistory h, msdb.dbo.sysJobs j
where j.job_id = h.job_id and h.run_date =
(select max(hi.run_date) from msdb.dbo.sysJobHistory hi where h.job_id = hi.job_id)

The SysJobHistory table is pretty well documented at here.

Since SQL Server 2005, if you want to get the current execution status of a job you can use the following:

exec msdb.dbo.sp_help_job

This is simple and easy. The problem some people seem to have with it is that it is a stored procedure and can’t use it like a table. It does already allow for a bunch of parameters that are very much like using a where clause. For example if you want to get all In Progress jobs it is simple:

exec msdb.dbo.sp_help_job @execution_status = 1

Still, you are limited to what they provide as parameters. Luckily as with any stored procedure you can work around this.

This site has lots of examples of how to take results from stored procs and select from them.

One example of this (if OPENROWSET is available on your installation of SQL Server) is:

SELECT *  FROM OPENROWSET ( 'SQLOLEDB','SERVER=.;Trusted_Connection=yes','EXECUTE msdb.dbo.sp_help_job');

If you don’t have OPENROWSET as an option then you will have to work a bit harder.

My first thought was do to something like

insert into Results exec msdb.dbo.sp_help_job --@execution_status = 1

That gets the data, but also generates an error which I really don’t think is a good thing.

Msg 8164, Level 16, State 1, Procedure sp_get_composite_job_info, Line 72
An INSERT EXEC statement cannot be nested.

So, I had to look to another solution. Unfortunately it is undocumented, but it works.

declare @Results table(
    job_id uniqueidentifier not null,
    last_run_date int not null,
    last_run_time int not null,
    next_run_date int not null,
    next_run_time int not null,
    next_run_schedule_id int not null,
    requested_to_run int not null, -- bool
    request_source int not null,
    request_source_id sysname collate database_default null,
    running int not null, -- bool
    current_step int not null,
    current_retry_attempt int not null,
    job_state int not null )

insert @Results exec master.dbo.xp_sqlagent_enum_jobs @is_sysadmin = 1, @job_owner = ''

select * from @Results

This works well UNLESS you want to connect to another server via a linked server since this is really calling a DLL underneath the stored procedure. If you try that you will get the error:

Msg 7411, Level 16, State 1, Line 1
Server 'myserver' is not configured for RPC.

I am pretty sure I can get by that, but I think that is opening up a security hole.

Conclusion:

I find this topic so frustrating. I really want it to be simpler and supported, but the best I can do is use the unsupported option since my SQL Server configuration cannot be changed. I wished all SQL Server stored procedures worked more like Table-valued functions. In the end, I created another table that each of the jobs from the different servers log when the start and stop. I can then do some queries to figure out the status (including In Progress) of the jobs. Seems like there should be a better way than opening up rpc, using openquery, etc. If I can implement it, Microsoft certainly could. Please Microsoft, please. What am I missing?

Why doesn’t my SQL Server Job that uses CmdExec not report a failure correctly

Here’s the scenario. I am running SQL Server and I have a created a Job that has among other steps a CmdExec step. This CmdExec step calls a C# console application that I wrote. My C# console application writes its errors to the Windows Event Log instead of just throwing an exception. True, I could just throw the exception and the exception would get logged to the Windows Event Log, but there would be 2 or 3 entries and they would be ugly and in general just confusing. I want to have better control over what gets written to the Windows Event Log so I have a try-catch at the highest level of my console application so that nearly no exception will be just thrown.

The problem

The problem is that when I catch the exception my program no longer returns a non-zero return code (error code) like it would if an uncaught exception was thrown. I want my program to have a return of 0 only if it was successful, and non-zero (let’s choose 1) if there is an error I caught and wrote to the Windows Event Log. The major reason I need to make sure the return code is correct is because SQL Server’s CmdExec step looks at the return code to determine if the step succeeded or not which it then affects the overall flow of the later steps and also what the status of the Job is.

Thank goodness there is an easy way to solve this problem if you are writing a C# console application.

Here is a very simple but illustrative example of how to change the return code. No need to set it for success since it will do that for us. We just have to handle the case where we catch the exception and want to have the return code to 1 (indicating an error).

static void Main(string[] args)
{
try
{
// some code here
throw new Exception(“Oh no an error”);
}
catch (Exception ex)
{
// write error to Windows Event log here using your favorite code
…

//Change the return code to 1 since there was an error
System.Environment.ExitCode = 1;
}
}

Troubleshooting ‘The client connection security context could not be impersonated. Attaching files require an integrated client login’

I am calling msdb.dbo.sp_send_dbmail from my C# console application and is connecting via a SQL Server Database user. When I call stored proc from C# I get the message below.

The client connection security context could not be impersonated. Attaching files require an integrated client login

there are some things you can try.

• If you want to use a SQL Server Database user you still can, but you will need to give that user sysadmin rights. You can add those permissions using the following command:
  
sp_addsrvrolemember '<Login>', 'sysadmin' 

• Try connecting as a Windows users instead of a SQL Server Database user. In other words, try a domain user.
  
• Make sure the file is not too large. The default size is 1,000,000 bytes (nearly 1MB). You can change the max attachment size using the following command:
  

  USE msdb
  EXEC sysmail_configure_sp @parameter_name='MaxFileSize', @parameter_value=N'1572864'
  GO
  
  In the example above the new max attachment is 1.5MB
  

Troubleshooting ‘profile name is not valid’ error

I got this message when I tried to call the msdb.dbo.sp_send_dbmail stored procedure. I had setup my profile (let’s call it Profile1). I even gave my user (let’s call it User1) the permissions to send mail as described here. I then called it from some C# code and got the following message.

profile name is not valid

Here are the steps I used to figure out what my problem was.

1. Double check that the name of the profile is correct and is being passed correctly to the stored proc using the @profile_name parameter.
   
2. The next step is to figure out if it is my code or SQL server side. The easiest way to do that is to open SQL Management Studio and connect to your database server using the exact user/login you were using when you got the error. Then try to send the mail message using the following (tweak as necessary):
   

   msdb.dbo.sp_send_dbmail
       @profile_name = 'Profile1',
       @recipients = 'someone@somewhere.com',
       @subject = 'Test subject',
       @body = '<p>Test Body</p>',
       @body_format = 'HTML'
   
   If you still get the error, it is something to do with SQL Server, otherwise, it is somewhere in your code that is called the stored proc. If it is in your code, you are on your own. If it is on the server (still getting the error) then proceed to the next step.

3. In SQL Management Studio, open another connection, but this time connect as a user that has more rights (probably yourself). Now execute the following stored proc.
   
   msdb.dbo.sysmail_help_principalprofile_sp @principal_name = 'User1', @profile_name = 'Profile1'
   
   If you get no results back then that means you have a permission problem. This likely means that your profile is NOT public or the user (User1) don’t have access to it. You an change the permission by going to the Database Mail Configuration Wizard | Manage profile security radio button | Next button. Here you can click the profile to make it public. You can alternatively go to the Private Profiles tab and select the user and then the profile. Either way should work.
   
   If you run the stored procedure again, it should show that you now have access to the profile now. You can also re-run the sp_send_dbmail and it should work this time.

Getting the Description Column from SQL Server

If you are storing valuable information such as comments, etc in the Description for a given column in your table you may want to export it to excel or in general just get to it via t-sql. It is actually pretty simple to get it. Below is a query you can run from the database you want to report on. I have exluded views, non-user tables, default tables, and system diagram tables, but you can change that by commenting out the appropriate statements in the where clause.

SELECT
[Schema] = ColumnInfo.TABLE_SCHEMA,
[Table Name] = ColumnInfo.TABLE_NAME,
[Column Name] = ColumnInfo.COLUMN_NAME,
[Position In Table] = ColumnInfo.ORDINAL_POSITION,
[Date Type] = ColumnInfo.[Data_Type],
[Description] = Properties.value
FROM
INFORMATION_SCHEMA.COLUMNS ColumnInfo
LEFT OUTER JOIN sys.extended_properties Properties
ON
Properties.major_id = OBJECT_ID(ColumnInfo.TABLE_SCHEMA+'.'+ColumnInfo.TABLE_NAME)
AND Properties.minor_id = ColumnInfo.ORDINAL_POSITION
AND Properties.name = 'MS_Description'
WHERE
-- exclude ones created when SQL Server was installed
OBJECTPROPERTY(OBJECT_ID(ColumnInfo.TABLE_SCHEMA+'.'+ColumnInfo.TABLE_NAME), 'IsMSShipped')=0
 
-- only get tables (no views)
and OBJECTPROPERTY(OBJECT_ID(ColumnInfo.TABLE_SCHEMA+'.'+ColumnInfo.TABLE_NAME), 'IsTable')=1
 
-- exclude ones created when SQL Server was installed
and OBJECTPROPERTY(OBJECT_ID(ColumnInfo.TABLE_SCHEMA+'.'+ColumnInfo.TABLE_NAME), 'IsUserTable')=1
 
-- exclude tables used for system diagrams
and ColumnInfo.TABLE_SCHEMA+'.'+ColumnInfo.TABLE_NAME <> 'dbo.sysdiagrams'

--AND ColumnInfo.TABLE_NAME = 'table_name'
ORDER BY
ColumnInfo.TABLE_NAME, ColumnInfo.ORDINAL_POSITION

I got the basics for this from this post.