How to pass a null variable to a SQL Stored Procedure from code

Im calling a SQL stored procedure from a piece of code: SqlHelper.ExecuteDataset(sqlConnection, CommandType.StoredProcedure, STORED_PROC_NAME, sqlParameters); where the sqlParameters variable is defined as: SqlParameter[] sqlParameters = new SqlParameter[SQL_NUMBER_PARAMETERS]; Log.Logger.Debug(string.Format(“Running proc: {0} “, STORED_PROC_NAME)); SqlParameters[0] = new SqlParameter(“fieldID”, SqlDbType.BigInt ); SqlParameters[0].Value = fieldID; SqlParameters[0].Direction = ParameterDirection.Input; I need to now pass in another two parameters to […]

MS Excel – join external (SQL) data with local table (sheet)

I’m trying to get data from both external source and another sheet of my excel file into one table. What I have in SQL Server is a table of my customers: ╔════╦══════╦═════════╗ ║ ID ║ Name ║ Country ║ ╠════╬══════╬═════════╣ ║ 1 ║ Joe ║ Spain ║ ║ 2 ║ Bob ║ Frence ║ ║ […]

How do I find the data directory for a SQL Server instance?

We have a few huge databases (20GB+) which mostly contain static lookup data. Because our application executes joins against tables in these databases, they have to be part of each developers local SQL Server (i.e. they can’t be hosted on a central, shared database server). We plan on copying a canonical set of the actual […]

“CASE” statement within “WHERE” clause in SQL Server 2008

I am working with a query which contains “CASE” statement within “WHERE” clause. But SQL Server 2008 is giving some errors while executing it. Can anyone please help me with the correct query? Here is the query: SELECT tl.storenum ‘Store #’, co.ccnum ‘FuelFirst Card #’, co.dtentered ‘Date Entered’, CASE st.reasonid WHEN 1 THEN ‘Active’ WHEN […]

Implementing transactions over multiple databases

I am performing data changes on multiple databases, and I want to implement a transaction that will cover all of the changes. This is what I currently have: try { db[1].begintransaction(); db[1].ExecuteNonQuery(); db[2].begintransaction(); db[2].ExecuteNonQuery(); … db[N].begintransaction(); db[N].ExecuteNonQuery(); // will execute only if no exception raised during the process for (int a = 0; a < […]

Equivalent of MySQL ON DUPLICATE KEY UPDATE in Sql Server

I am trying to find an equivalent of the following MySql query in Sql Server (2012)? INSERT INTO mytable (COL_A, COL_B, COL_C, COL_D) VALUES ( ‘VAL_A’,’VAL_B’, ‘VAL_C’, ‘VAL_D’) ON DUPLICATE KEY UPDATE COL_D= VALUES(COL_D); Can anyone help? PS. I have read that MERGE query has similar function, but I find the syntax of that very […]

Generate table relationship diagram from existing schema (SQL Server)

Is there a way to produce a diagram showing existing tables and their relationships given a connection to a database? This is for SQL Server 2008 Express Edition.

What's the simplest way to access mssql with python or ironpython?

I’ve got mssql 2005 running on my personal computer with a database I’d like to run some python scripts on. I’m looking for a way to do some really simple access on the data. I’d like to run some select statements, process the data and maybe have python save a text file with the results. […]

Function-based indexes in SQL Server

I’m trying to understand if function based indexes similar to the one’s in Oracle or PostgreSQL exist in SQL Server In PostgreSQL, I can create a function based index using by using the following syntax: CREATE INDEX sample ON “TestDB” ((“expression1” || ‘ ‘ || “expression2”)); I found a article where I found something called […]

Debugging Stored Procedure in SQL Server 2008

Is there any way to debug a stored procedure on SQL Server 2008? I have access to use SQL Server Management Studio 2008 and Visual Studio 2008 (not sure whether either provides this functionality). Generally I use the SQL profiler to find the parameters passed to the stored proc, however would like to be able […]

MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.