sql-server-2008 – Page 8

“The ‘Microsoft.ACE.OLEDB.12.0’ provider is not registered on the local machine” Error in importing process of xlsx to a sql server

December 12, 2022 by Tarik

Install the following to resolve your error. 2007 Office System Driver: Data Connectivity Components AccessDatabaseEngine.exe (25.3 MB) This download will install a set of components that facilitate the transfer of data between existing Microsoft Office files such as Microsoft Office Access 2007 (*.mdb and .accdb) files and Microsoft Office Excel 2007 (.xls, *.xlsx, and *.xlsb) … Read more

optional parameters in SQL Server stored proc?

November 27, 2022 by Tarik

You can declare like this CREATE PROCEDURE MyProcName @Parameter1 INT = 1, @Parameter2 VARCHAR (100) = ‘StringValue’, @Parameter3 VARCHAR (100) = NULL AS /* check for the NULL / default value (indicating nothing was passed */ if (@Parameter3 IS NULL) BEGIN /* whatever code you desire for a missing parameter*/ INSERT INTO …….. END /* … Read more

SQL Server Management Studio SSMS tries to “save to file” instead of execute on F5

November 18, 2022 by Tarik

It sounds like you have accidentally changed from the default query option of “Results to Grid” to “Results to File”. If that is the case it is easy to fix: From the Query Menu -> Results To -> Results To Grid. OR Press Ctrl + D Once you have tried one of the steps above, … Read more

How to convert float to varchar in SQL Server

November 18, 2022 by Tarik

Try using the STR() function. SELECT STR(float_field, 25, 5) STR() Function Another note: this pads on the left with spaces. If this is a problem combine with LTRIM: SELECT LTRIM(STR(float_field, 25, 5))

How to check SQL Server version

November 17, 2022 by Tarik

Following are possible ways to see the version: Method 1: Connect to the instance of SQL Server, and then run the following query: Select @@version An example of the output of this query is as follows: Microsoft SQL Server 2008 (SP1) – 10.0.2531.0 (X64) Mar 29 2009 10:11:52 Copyright (c) 1988-2008 Microsoft Corporation Express Edition … Read more

Export query result to .csv file in SQL Server 2008

November 13, 2022 by Tarik

Open SQL Server Management Studio Go to Tools > Options > Query Results > SQL Server > Results To Text On the far right, there is a drop down box called Output Format Choose Comma Delimited and click OK Here’s a full screen version of that image, below This will show your query results as … Read more

Cannot open backup device. Operating System error 5

October 26, 2022 by Tarik

Yeah I just scored this one. Look in Windows Services. Start > Administration > Services Find the Service in the list called: SQL Server (MSSQLSERVER) look for the “Log On As” column (need to add it if it doesn’t exist in the list). This is the account you need to give permissions to the directory, … Read more

Why is SQL Server 2008 Management Studio Intellisense not working?

October 23, 2022 by Tarik

I understand this post is old but if anybody is still searching and has not found a solution to the intellisense issue even after re-installing, applying the cumulative updates, or other methods, then I hope I may be of assistance. I have Applied SQL 2008 R2 Service Pack 1 which you can download here http://www.microsoft.com/download/en/details.aspx?id=26727 … Read more

A transport-level error has occurred when receiving results from the server [closed]

October 22, 2022 by Tarik

The database connection is closed by the database server. The connection remains valid in the connection pool of your app; as a result, when you pickup the shared connection string and try to execute it’s not able to reach the database. If you are developing Visual Studio, simply close the temporary web server on your … Read more

Enable ‘xp_cmdshell’ SQL Server

October 21, 2022 by Tarik

You need to enable it. Check out the Permission section of the xp_cmdshell MSDN docs: http://msdn.microsoft.com/en-us/library/ms190693.aspx: — To allow advanced options to be changed. EXEC sp_configure ‘show advanced options’, 1 GO — To update the currently configured value for advanced options. RECONFIGURE GO — To enable the feature. EXEC sp_configure ‘xp_cmdshell’, 1 GO — To … Read more