Granting ASP.NET web application access to run procedures in SQL

\ by

USE MyDatabase
GO

— Windows 2000 / XP
— Replace "{servername}" with your SQL Server name

EXEC sp_grantlogin [{servername}\ASPNET] 
EXEC sp_grantdbaccess [{servername}\ASPNET], [NETAPP]

— GRANT EXECUTE ON [ProcedureName] TO [NETAPP]
grant exec on [dbo].[usp_My_Procedure] TO NETAPP 
GO

— Windows Server 2003
— Info.

EXEC sp_grantlogin [NT AUTHORITY\NETWORK SERVICE]
EXEC sp_grantdbaccess [NT AUTHORITY\NETWORK SERVICE]

GRANT EXECUTE ON [ProcedureName] TO [NT AUTHORITY\NETWORK SERVICE]
GO

Posted in: SQL Server, WEB

SQL Transactions

\ by

A good article on implicit and explicit SQL Transactions:

Implicit transaction mode is not SQL Server’s default. You have to request implicit transaction mode either with a SET statement:

SET IMPLICIT_TRANSACTIONS ON;

That was from Did you Know? Nesting Transactions by Kalen Delaney.

-- Here's a full script to illustrate the behavior of @@trancount with implicit transactions:
SET IMPLICIT_TRANSACTIONS OFF;
GO
IF EXISTS ( SELECT *
 FROM sys.objects
 WHERE NAME = 'T1'
 AND type = 'U' )
 DROP TABLE T1;
GO
CREATE TABLE T1 (col1 INT);
GO
INSERT INTO T1
 SELECT 1;
GO

SET IMPLICIT_TRANSACTIONS ON;
GO
BEGIN TRAN;
 SELECT @@trancount;

UPDATE T1
 SET col1 = col1 + 1;
COMMIT TRAN;
SELECT @@trancount;

COMMIT TRAN;
SELECT @@trancount;
GO
SET IMPLICIT_TRANSACTIONS OFF;
GO
And more, On Transactions, errors and rollbacks from SQL in the Wild by Gail Shaw.
Posted in: SQL

Msg 1934, Sev 16: UPDATE STATISTICS failed because the following SET options have incorrect settings: ‘ARITHABORT’.

\ by

Got this when I ran my sp__updatestats procedure in a SQL Agent jobstep, but it worked in Query Analyzer. The reason? The default settings are probably different in QA rather than external connections similar to SQL Agent.

Msg 1934, Sev 16: UPDATE STATISTICS failed because the following SET options have incorrect settings: 'ARITHABORT'. [SQLSTATE 42000]

The solution was to recreate the procedure that ran UPDATE STATISTICS using a SET command within the procedure. 

CREATE PROCEDURE dbo.sp__UpdateStats
AS
SET ARITHABORT ON 
SET QUOTED_IDENTIFIER ON 
...

We started to get this problem after I added a computed column to a table. 

Posted in: ERRORs, SQL, SQLAgent

Use COALESCE to put a column of data into a delimited list variable

\ by

Here’s an example of how to create a delimited list, using a field of rows of data and COALESCE.

CREATE TABLE #tmp (ID INT IDENTITY, Value VARCHAR(10))

INSERT #tmp (Value) VALUES(@@IDENTITY)
INSERT #tmp (Value) VALUES(@@IDENTITY)
INSERT #tmp (Value) VALUES(@@IDENTITY)
INSERT #tmp (Value) VALUES(@@IDENTITY)
INSERT #tmp (Value) VALUES(@@IDENTITY)
INSERT #tmp (Value) VALUES(@@IDENTITY)
INSERT #tmp (Value) VALUES(@@IDENTITY)
INSERT #tmp (Value) VALUES(@@IDENTITY)
INSERT #tmp (Value) VALUES(@@IDENTITY)
INSERT #tmp (Value) VALUES(@@IDENTITY)

DECLARE @vcList VARCHAR(100)
SELECT @vcList = COALESCE(@vcList + ‘;’, ”)
+ ISNULL(Value, ‘0’)
FROM #tmp

-- ORDER BY might not work
PRINT @vcList
Resulting in:
10;1;2;3;4;5;6;7;8;9

NOTE:
Don’t be surprised when ORDER BY doesn’t work.

‘SQL Server query processor builds an different execution plan when expressions are applied to columns in the query’s ORDER BY clause, than when those same expressions are applied to columns in the query’s SELECT list. The decision made by the query processor is based on the cost of possible execution plans.’

Posted in: SQL

Can we use the SQL 2005 engine for queries against a SQL 2000 linked server?

\ by

You bet!

  1. On your SQL 2005/8 instance create a linked SQL 2000 server
  2. Run your SQL 2005 query using something like PIVOT or UNPIVOT against the SQL 2000 linked server table.
SELECT  [ND],
        [SD],
        [TN],
        [OR],
        [VA]
FROM    (
         SELECT State,
                SalesAmt
         FROM   sqltest.northwind.dbo.Sales
        ) p PIVOT ( SUM(SalesAmt) FOR State IN ([ND], [SD], [TN], [OR], [VA]) ) AS pvt
 
Here's a link to an article example about PIVOT, UNPIVOT.
 
Posted in: SQL

[ODBC Driver Manager] Driver does not support this function … ‘MSDASQL’ IDBInitialize::Initialize

\ by
On a new SQL Server I was trying to run an OPENROWSET query that opened a Foxpro free table and was getting this error. It turned out the latest Foxpro drivers were not yet installed. An easy way to find the drivers and their dates is to open the ODBC Data Source Administrator and click on drivers. You could also test by trying to create an ODBC connection to Foxpro.
 
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error.  
[OLE/DB provider returned message: [Microsoft][ODBC Driver Manager] Driver does not support this function]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize returned 0x80004005:   ].
 
e.g. 

SELECT *   
FROM  OPENROWSET (
  'MSDASQL','Driver=Microsoft Visual FoxPro Driver;SourceDB=E:\DATA;SourceType=DBF;',
  'SELECT  * FROM MyFreeTable;'
)

Once the correct drivers were installed we could run the query, above, and create ODBC connections. Then, we were getting this error, but only when not running directly on the server itself.

OLE DB provider "MSDASQL" for linked server "(null)" returned message "[Microsoft][ODBC Visual FoxPro Driver]File 'hprofile.dbf' does not exist.".
Msg 7350, Level 16, State 2, Line 1
Cannot get the column information from OLE DB provider "MSDASQL" for linked server "(null)".

So, our OPENROWSET queries and the linked server queries could be run locally to the server. There must still be some proxy permissions or security something we still need to set. 

Still, another error we're getting is this: 
Cannot get the schema rowset 'DBSCHEMA_CATALOGS' for OLE DB provider "SQL Server" for linked server "(null)" — huh?

Posted in: SQL, SQL Server

Import EXCEL data using OPENDATASOURCE

\ by

Don't forget that now that it is in the tempdb table you can script out a field descriptor or a CREATE table statement. Use that for a permanent import table elsewhere. 

USE tempdb
GO
SELECT *
INTO MyEXCELSheet
FROM OPENDATASOURCE(
'Microsoft.Jet.OLEDB.4.0',
'Data Source="E:\atest.xls";Extended properties=Excel 5.0'
)...Sheet1$
--
-- Now use Query Analyzer to script out the table in tempdb. Here's a sample script.
--
CREATE TABLE [dbo].[MyEXCELSheet] (
[CustomerID] [nvarchar] (255) NULL ,
[EmployeeID] [float] NULL ,
[Freight] [float] NULL ,
[OrderDate] [datetime] NULL ,
[OrderID] [float] NULL ,
[RequiredDate] [datetime] NULL ,
[ShipAddress] [nvarchar] (255) NULL ,
[ShipCity] [nvarchar] (255) NULL ,
[ShipCountry] [nvarchar] (255) NULL ,
[ShipName] [nvarchar] (255) NULL ,
[ShippedDate] [datetime] NULL ,
[ShipPostalCode] [float] NULL ,
[ShipRegion] [nvarchar] (255) NULL ,
[ShipVia] [float] NULL
) ON [PRIMARY]
END
GO
Posted in: Excel, SQL