The Temple of Fu

code, systems and games

Archive for the ‘Database’ Category

Procedure or function X expects parameter Y, which was not supplied.

with 3 comments

I have been tracking down the source of this error for about an hour and a half.

“Procedure or function ‘procedure name’ expects parameter ‘@parameter_name’, which was not supplied.”

This is happening when I call a stored procedure with parameters through .Net’s data connection to SQL (System.data.SqlClient) and also through a WCF client proxy. The only way I was even able to see that error was via SQL Profiler and by executing the code it captured but that’s because we do not have good logging setup on our WCF endpoints yet. After much pain and debugging I found that it was because I forgot to add (or accidentally removed) a critical piece when setting this up, so yes it all comes down to user ID10T error but isn’t it always.

m_cmd.CommandType = CommandType.StoredProcedure

Hope this helps someone, off to refill my coffee cup!

Written by lordfu

August 30, 2012 at 9:52 am

SQL Server 2005 – No Management Studio

leave a comment »

This happens to me all the time. The order usually goes along the lines of.

1. Install Windows
2. Get updates including .Net framework
3. Install Visual Studio
4. Install SQL Server
x. Check for SSMS and it is not there.

What happens is at some point SQL Server Express or some_other_installation causes SQL Server 2005 to not install the Client Components. Even re-running the installation and selecting Client Tools will not get them installed.

There are two ways that I know of to get them installed.
1. Install SQL Server 2005 before all updates and installing Visual Studio.

2. Run it in maintenance mode. To do this go to “Add or Remove Programs”, find the entry for “SQL Server 2005”, select it and hit the Change button. Under the section for “SQL Server 2005 Common Components” you’ll see an entry for “Workstation Components”, select it and hit the next button. Keep following the dialogs until you come to one called “Change or Remove Instance”, be sure to select “Change Installed Components”. The Feature Selection dialog will appear. Expand the “Client Components” node, select “Management Tools” and click next. Follow the remaining dialogs. After setup runs you should find “SQL Server Management Studio” in the Start menu.

Written by lordfu

July 4, 2012 at 1:57 pm

Posted in Database, MSSQL, Windows

SSIS – File mapping object ‘Global\DTSQLIMPORT’ could not be opened. Operating system error code 2(The system cannot find the file specified.). Make sure you are accessing a local server via Windows security.

with 2 comments

Man one of these days Microsoft will make these error messages easier to understand. I am one of the ones that came from DTS land where after a few years things became comfortable; but in an effort to make things easier for us Microsoft gave us SQL Server Integration Services (SSIS) to overcome some of the DTS package shortcomings. Well they made my life a living hell in trying to learn this, well at least this particular task /rant.

The latest in my struggles is trying to deduce from the garbage that are SSIS messages what exactly I need to do to fix my error. The most recent being:
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Could not bulk load because SSIS file mapping object 'Global\DTSQLIMPORT ' could not be opened. Operating system error code 2(The system cannot find the file specified.). Make sure you are accessing a local server via Windows security.".

What was I trying to do I will represent in this complicated flowchart.
Excel document –> SQL Server table

Confused yet? Me neither, If I were to do this with a DTS package it would have taken an hour tops but currently I am going on 4 hours! Not only am I battling Excel and it’s ‘here let me make this easier for you and guess what types or lengths your columns can hold’ productivity enhancements. I am also needing to deduce these bs error messages that are given at freaking runtime from within BIDS designer!
/rant #2

OK so the actual fix now is; instead of using a SQL Destination (even though you are inserting into SQL wtf) use an OLE DB Destination. The reason being to use a SQL destination you need to run the package on the machine that hosts SQL Server (destination). If you want to use a remote SQL Server, use OLEDB destination.

Hope that helps.

Written by lordfu

November 5, 2010 at 4:33 pm

Posted in Development, MSSQL, SSIS

If temp table exists – Drop it!

leave a comment »

You’ve created a temp table in a stored procedure and now you are testing it which involves dropping a temp table manually if you are not executing the stored procedure in it’s entirety (common if you are testing chunks of it).

So here’s the easy solution. We need to check if the temp table exists within the TempDB database and if it does, we need to drop it. Place the following at the top of your procedure, obviously before creating the temp table =] Now everytime it runs it will drop the table and then recreate it, no more manually dropping the temp table!

IF OBJECT_ID('tempdb..#Temp') IS NOT NULL
DROP TABLE #Temp

Replace #Temp with the name of your temp table.

Hope this helps!

Written by lordfu

July 29, 2010 at 1:50 pm

Posted in Database, MSSQL

Truncate and DBCC

leave a comment »

So while developing some applications I have the need to clean a table and reset identity fields. Let it be known that if any of the fields are foreign keys in other tables those tables need truncated as well, working from last linked table forward to the Primary key table.

In the below example make sure to put in place of myDatabase and myTable your database that contains the table to be truncated. =]


use myDatabase
truncate table myTable


use myDatabase
DBCC CHECKIDENT ('myTable', RESEED, 0)

Written by lordfu

June 25, 2010 at 2:25 pm

Posted in Database, MSSQL

Server is not configured for RPC(Remote Procedure Calls)

with 4 comments

I was setting up a new database on a new SQL Server 2005 installation that has a few linked servers configured (which happen to be SQL Server 2000), while testing execution of a remote stored procedure it failed with the error:

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

The problem was that RPC was not configured for the linked server. this is not set by default, you can see what settings are configured with
exec sp_helpserver

If ‘rpc and rpc out’ are not in the results, then the the linked server isn’t configured for RPC. To do so:

exec sp_serveroption @server='myserver', @optname='rpc', @optvalue='true'
exec sp_serveroption @server='myserver', @optname='rpc out', @optvalue='true'

Or alternatively you can right click on your linked server bring up it’s properties and select True for the value from the RPC and RPC Out rows.

Set True for values RPC and RPC Out

Hope this helps!

Written by lordfu

April 23, 2010 at 10:51 am

Posted in Database, Development, MSSQL

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 0 – No connection could be made because the target machine actively refused it.)

leave a comment »

Ok I am not a SQL god and make no claims of even being a SQL guru so this will be short and sweet.

I received this error on a new SQL Server 2000 install (hey some have legacy systems to support still!) while trying to connect from a web application.

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 0 – No connection could be made because the target machine actively refused it.)

After a day or so of trouble shooting and testing different configurations, the standard tests Named Instance vs TCP/IP, firewalls, user name password, connection strings, tel-netting etc, it boiled down to SQL server SP4 not being installed on the SQL server. After we installed SP4 all was good.

Hope this helps someone!

Written by lordfu

April 16, 2010 at 3:34 pm