Archive for January, 2015

Start up server in master only recovery mode, allowing only access to sqlcmd

January 28, 2015

on a command line
net start MSSQL$instancename /f /T3608 /m”SQLCMD”

then logon using sqlcmd

Advertisements

Access Denied error when attempting to access Oracle data via a linked server

January 27, 2015

Problem: The OLE DB provider “OraOLEDB.Oracle” for linked server “X” reported an error. Access Denied
Cannot get the column information from OLE DB provider “OraOLEDB.Oracle” for linked server “X”

Solution: Go to the OraOLEDB.Oracle provider, check Allow in process. Then restart the server service.

Can be scripted using EXEC master.dbo.sp_MSset_oledb_prop N’OraOLEDB.Oracle’, N’AllowInProcess’, 1

administrative share

January 20, 2015

\\servername\c$

this is an administrative share that only administrators can access. If your account is not an admin then you will not have access. Instead set up sharing via the security tab within windows explorer.

Resource semaphore waits/queries not running when there is seemingly enough memory for them to run

January 5, 2015

http://blogs.msdn.com/b/sqlqueryprocessing/archive/2010/02/16/understanding-sql-server-memory-grant.aspx

•Resource Semaphore checks for waiting queries in its queue.
•If it finds a waiting query, it puts the new query into a wait queue for fairness.
•The wait queue is designed on first-come-first-served basis with small weight to favour small queries.

Small queries are classed as queries requesting than 5MB

Example: if there is 10GB memory free on the server then the following will happen

•A query requesting 11GB is executed it will wait for memory to become available with a resource_semaphore wait
•A query requesting 1GB is then executed, it will see that the above query is already waiting and so go into the queue. This query is not small (not less than 5MB) and there is already something in the queue ahead of it so the query will also go into resource_semaphore wait status.

The query goes into a wait status despite there being enough memory available for the query to run because 1. queue is first come first served 2. Query is not classed as small

email csv attachment using sp_send_dbmail formatted for Excel

January 5, 2015

Sometimes you want to email the result of a query as an attachment and you would like it to open nicely in excel. By default the attachment will look horrific in excel but you can force it to look nice (1 field from result set mapping to 1 column in excel) by adding the following line to the top of your attachment

“sep=,”

To automatically do this use the following as a template when using sp_send_dbmail . Substitute in your column names and table name where appropiate

SET @Column1Name = ‘[sep=,’ + CHAR(13) + CHAR(10) + ‘Column1]’
SET @Query = ‘SELECT Column1 AS ‘ + @Column1Name + ‘, Column2, Column3 FROM myTable’

Youll also want to make sure that you use the following parameters modified to your needs

,@query=@Query
,@attach_query_result_as_file=1
,@query_attachment_filename=’QueryResults.csv’
,@query_result_separator=’,’ –enforce csv
,@query_result_no_padding=1 –trim
,@query_result_width=32767 –stop wordwrap

SOURCE: http://www.purplefrogsystems.com/blog/2014/04/excel-doesnt-open-csv-files-correctly-from-sp_send_dbmail/

Calculation of Resource Pool Target Memory

January 5, 2015

We specify a minimum and a maximum percentage when we configure the resource pool. Using these figures we can derive an effective max percentage for each group.

effective max percentage = min (max % , 100 – sum(all other pool minimum percentages))

The Target Memory = effective max percentage