Russ' Do It Yourself Home Workshop

Finding Fixes to Just About Anything and Everything

Archive for the ‘SQL Server’ Category

Getting SQL Express to Listen So You Can Connect Remotely

Posted by Russell Wright on April 22, 2014

Dang!  We had a situation where a new tool we were implementing in our back office, BillQuick Web Services 2014, would not use SQL Server since we purchased the "Pro" version and not the "Enterprise" version.  Unfortunately, it appears the trial version we installed has all the enterprise version capabilities and, after entering our license information, we were locked down to the pro version and it wouldn’t connect to the SQL Server anymore.

So, that meant I needed to install SQL Express to support the tool.  Okay, no biggie…except for the fact that SQL Express does not enabled Named Pipes or TCP/IP protocols, by default.  This took me a bit to figure out.

This is not a new problem, as posted by Peter van Ooijen in 2006.  Some things never change…

The easy way to fix this is to use SQL Server Configuration Manager.

image

Navigate to the Protocols section for your SQL Express instance and switch it on! 

image

And if that’s not enough, you need to change the TCP/IP properties to listen on the correct port.  I set the port to 1077 (one the SQL Server is NOT listening on).  It appears if you set it in the the IPAll section, the same port will be used for each IP address and you only have to enter it one time.

image

Oh, and then recycle your SQL server instance!

The connection string to use looks like this.

SQLServer\Instance,Port

image

 

Advertisements

Posted in Computer Software, SQL Server | Leave a Comment »

Setting SSIS Variables the SQL Way: Create a Directory or Folder using Dates

Posted by Russell Wright on January 16, 2014

Okay, I’m not an SSIS expert, but I do use it quite a bit.  So, when I had to do some “coding” in the script task recently I was able to get through it, but the next time I had to do something I wanted to determine an alternate, and possibly more expedient, way of accomplishing this simple task:  Set a variable based on dates or other easily generated data.

My task was pretty simple.  Create a folder structure during the execution of an SSIS package in which the files received during the B2B process can be placed.  It’s been done a million times, but there’s always a new and different way to accomplish this simple feat.  And, with SSIS, there are always intricacies of different data connection types and other obtuse settings that are not necessarily obvious to the uninitiated.

My inspiration came from The Data Queen and this blog post, “How to Set and Use Variables in SSIS Execute SQL Task.”  Basically what you are doing here is selecting a single row results set using the Execute SQL task and setting (initializing) your variable to one of the columns returned from the query. 

image

This gives you a pretty flexible approach where you can create a name/value pair table, such as this, and select a value from the table with which you initialize your SSIS variable.

CREATE TABLE [dbo].[TRAXSSISParms](
[Name] [varchar](255) NOT NULL,
[Value] [varchar](255) NOT NULL,
[Description] [varchar](255) NULL
) ON [PRIMARY]

But what I wanted to do wasn’t really about storing values to retrieve (well it was this too, but that’s not really what I’m talking about right now), but instead calculate values in order to create a dynamic folder name each time the package is executed.  And, I wanted to use regular SQL to do this, if possible.  Here’s what I found.

We will be using the Execute SQL Task and the File System Task.  I’ve named them Set Archive Folder Variable OLEDB and Create Archive Folder.  The reason I added OLEDB in the name is that, in this case, we are using the OLEDB data connection type, as opposed to the ADO.NET connection type.  More on that later.

image

User variables are created and scoped at the package level.  This process is not very intuitive if you’ve never done it before or, if you’re like me, you do it once-in-a-blue-moon.

You first have to make sure the Variables pane is enabled.  If you get a short menu, that means your focus is not on the SSIS designer canvas, as the menus change based on your current context (this is soooo OS/2-ish). 

image  image

You should now have a Variables tab along with a Toolbox tab.  The icons along the top are pretty self-explanatory if you hover over each one:  Create a variable, delete a variable, show system variables, show all variables, and add/remove columns to the menu display.  Here you can see my two variables, TRAXRootFolder and ArchiveFolder.  My package name is Test, so the scope is at the package level.  Both are String data types.

image

Let’s look at the settings of the Execute SQL Task.  I’ve highlighted the important areas and will discuss each one.

image

The SQLStatement will show you very quickly what I am attempting to do.  I am using SQL to generate a result set without an underlying table query. 

image

Declare @ArchiveFolder varchar(255) — Create SQL variable in which to hold results
Select @ArchiveFolder = ? + CONVERT(VARCHAR, GETDATE(), 112) — Concatenates the root folder variable passed in (F:\TRAXData\B2B\) with a generated date
Select @ArchiveFolder AS Value — Selects the value in the SQL variable and returns it in the result set with a column name of “Value”

 

The ResultSet property should be set to Single row.  If you set it to something other than Single row you’ll get an error.

The ConnectionType is very important.  If you’ve dealt with SSIS, you know that passing parameters depends upon the type of connection you have.  This TechNet article http://technet.microsoft.com/en-us/library/ms140355(v=sql.105).aspx provides the following references to parameter markers and parameters names.  In this case, using OLEDB, we use the question mark (?) as the parameter marker or placeholder and the parameter names are ordinals with a zero base (0, 1, 2, 3…).  I’ll show you what this looks like if you use the ADO.NET connection at the end of this blog.

image

image

Parameter Mapping

image

Result Set

image

 

 

 

 

 

 

 

 

 

 

 

 

Posted in SQL Server | Leave a Comment »

Argument "SMTP" for option "connection" is not valid. The command line parameters are invalid.

Posted by Russell Wright on November 18, 2013

When scheduling an SSIS package deployed to SQL Server the job failed.  The job history indicates my SMTP connection is invalid for some reason.  So, I checked the command line created by the job scheduler.

Argument “SMTP” for option “connection” is not valid.  The command line parameters are invalid.  The step failed.

What you will see is the /CONNECTION parameter for the SMTP connection manager is enclosed in “\ and \” characters.  Not sure why, but you can see this is also the case for the SQL Server connection manager, but it doesn’t suffer from the same problem.

Before:

/CONNECTION “SMTP Connection Manager”; “\”SmtpServer=smtp.mail.mycompany.com;UseWindowsAuthentication=False;EnableSsl=False;\””

image

After:

/CONNECTION “SMTP Connection Manager”; “SmtpServer=smtp.mail.mycompany.com;UseWindowsAuthentication=False;EnableSsl=False;”

image

Making this simple change seems to have corrected the issue.

Posted in SQL Server | Leave a Comment »

How to Get a CSV File Generated from a SQL Server Query (SQL 2008)

Posted by Russell Wright on August 21, 2012

In SSMS (SQL Server Management Studio) you are running a query and want to get a CSV file as the results.  This is accomplished by setting the Output format in the Query Options to Comma delimited.  You can also determine whether to include the column headers in the results.

image

By right-clicking on the query design surface you can choose to send the results to a file.

image

You should be prompted to specify a file to save to when the query is executed.

Posted in SQL Server | Leave a Comment »

Oracle OLE DB Error 0x80040E07 in SSIS

Posted by Russell Wright on August 17, 2012

If you find yourself dealing with running an OLE DB query against an Oracle database in SSIS and you get an error similar to this, make sure you are comparing dates using the Oracle TO_DATE function.

e.g.  DOC_DATE  >= TO_DATE( ’01-01-2012′, ‘MM-DD-YYYY’)

or

TO_DATE(TO_CHAR(SYSDATE – 7,’MM-DD-YYYY’),’MM-DD-YYYY’)

Code: 0xC0202009
   Source: Import Level tables Level1 Source [1261]
   Description: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80040E07.
An OLE DB record is available.  Source: "Microsoft OLE DB Provider for Oracle"  Hresult: 0x80040E07  Description: "ORA-01861: literal does not match format string".

 

Search strings:

ORA-01861: Literal does not match format string

Microsoft OLE DB Provider for Oracle 0x80040E07

Posted in SQL Server | Leave a Comment »