I hope to add more posts in the coming days as I'm back to being just a DBA (mainly.)  This will be more up-to-date also.  I hope to have more posts on SQL 2012, 2012 R2, 2014 but mainly 2008 R2 at the moment.  AlwaysON will be a common topic in the near term.  Let me know what you all are working on Data Shephards.

Anyone going to PASS this year?  I put in a request to go this year.  I haven't been since 2011 which was also my first time.

I'll have to check out the SQL Server Club to see how it is going too!

New found love of set based programming

Hey All

Its been quite a while huh? Here's something i've been working on. In trying to get a way from procedural/cursor based TSQL, I've found how to get done what i need using set based programming! In using SQL the way it was meant to be, I can now execute commands faster using set based coding. By declaring a temp table as a variable, I can still use memory-based processing without the overhead of DiskIO. Dynamic SQL is built with a SELECT statement and inserted into the temp table. The reason I used a temp table was the need for a unique identity column to be used as a counter. I have linked servers reaching out to all my database servers so I can remotely run commands or access data. With the example below, I can run a command returning file information for each database on each server! THIS will be extremely handy for building configuration dashboards.

Happy Hunting Data Shepherds!!!



DECLARE
@COUNTER TINYINT = 1,
@COMMAND NVARCHAR(500)

DECLARE
@VIEWCOMMAND TABLE
(COUNTER TINYINT IDENTITY(1,1) NOT NULL,
COMMAND NVARCHAR(100))

INSERT INTO @VIEWCOMMAND
SELECT
'SELECT * FROM [' + S.name + '].[' + D.name + '].SYS.DATABASE_FILES' AS 'COMMAND'
FROM
sys.servers S,
sys.databases D

WHILE @COUNTER IS NOT NULL

BEGIN

SELECT @COMMAND =
(SELECT
COMMAND
FROM
@VIEWCOMMAND
WHERE
COUNTER = @COUNTER)

EXEC(@COMMAND)

SET @COUNTER =
(SELECT
MIN(COUNTER)
FROM
@VIEWCOMMAND
WHERE
COUNTER > @COUNTER)

END

Server 'myserver' is not configured for RPC

Here's an interesting issue. I wrote a stored proc that kills processes on any server for any database with optional loginame or datetime sequence. On 2 of the production servers i got the below error. all other servers, it works fine including other prod servers. the below procs alleviate the issue by configuring the linkedserver for RPC. i'll need to add these for a new proc for creating linkedservers!!!

Problem: Server 'myserver' is not configured for RPC

Solution: Problem is most likely that RPC is not configured for your linked server. That is not a default option, after all. You can see what settings are configured with exec sp_helpserver.

If 'rpc,rpc out' is not in your 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'

Dropping logins fail - login granted perms to other logins/users

This drove me insane yesterday!!! Simple task of dropping logins from one domain after adding accounts from another domain became a chore when 2 accounts failed to dropped because:

Login 'XXXX\XXXXXXX.xxxxxxx' has granted one or more
permission(s). Revoke the permission(s) before dropping the login.


I tried so many things like revoking granting ability from the acccount after searching who the accounts granted rights to (me!?!) Revokes rights from my account and on and on with no luck. I got frustrated and went home in disgust!!! SO I read that if all fails, drop the account (MINE!!!) that perms were granted to and voila the accounts dropped!!! Buzha!!!

ACCESS and BIGINTs

SQL Server introduced the BIGINT datatype in 2005 to keep up with Oracle. This is a cool thing especially for data warehousing. Unfortunately, ACCESS has the Integer and Long Integer datatypes. The Long Integer is equivalent to SQLs INT but no equivalent to SQLs BIGINT. This causes issues with ACCESS. If you link to a SQL table where the primary key is a BIGINT, the linked table will display #DELETED! in each cell. NO ITS NOT A SECURITY ISSUE!!! So if you have customers that need to view SQL data thru Access linked tables, there are work arounds BUT the BIGINTs will need to be changed to INT. Create an interface NOT in Access for your customers to view data if need be MOSS 07 is a quick solution if you're using it, else try VS.NET or JAVA web apps. yes its time consuming but you can't give all users Admin rights and SQL Server to play with!!!

Here's a link to your work arounds:

EXTRACTING A DATABASE USER'S LOGIN NAME THATS NOT STORED IN THE SYSLOGIN TABLE

I'M WRITING SCRIPTS FOR HANDLING A DOMAIN ACCOUNT CHANGE OVER. DID YOU KNOW WHEN YOU ENTER A NEW DATABASE USER AND GIVE IT A DOMAIN ACCOUNT LOGIN NAME, THAT DOES NOT EXIST IN THE INSTANCE\SECURITY\USERS LIST, THAT IT DOES NOT REGISTER IT IN THE SYSLOGIN TABLE? SO IF YOU NEED TO PULL THAT LOGIN NAME, YOU CAN USE SUSER_SNAME() BY
PASSING THE .SYS.SYSUSER.SID VALUE FOR THE DATABASE USER ACCOUNT IN QUESTION. I ORIGINALLY THOUGHT I WAS GOING TO HAVE TO ACCESS ACTIVE DIRECTORY DATA THROUGH A SMALL VB APP. THE AD ADMIN GROUP WILL SURELY NOT GIVE THAT ACCESS TO ANYBODY AND CAN FORESEE LOTS OF POLITICS AND PAPERWORK INVOLVED IN EVEN A FAIR SIZED ORG OF SAY A FEW HUNDRED PEOPLE! HOPE THIS SAVES YOU ALL SOME TIME!

How to change databases within a stored procedure or function

This one was a royal pain in the ASS!!!! Not fully correct info on the web or people saying it CAN'T be done. NEVER NEVER NEVER tell me it CAN'T be done!!!!

here is the revelant code from an SP for truncing logs. rest your weary head!!!

USE [RIXTOOLBOX]
ALTER PROCEDURE [dbo].[spTruncateLogs]

@DBName SYSNAME

AS

BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

DECLARE @command varchar(500), @LogFile VARCHAR(500), @LOGSTR VARCHAR(10);
SET @LOGSTR = '%LOG%'
SET @command = 'DECLARE c CURSOR FOR
SELECT
NAME
FROM
[' + @DBName + '].SYS.SYSFILES
WHERE
name LIKE ''' + @LOGSTR + ''';'

EXEC (@COMMAND)
OPEN c;
FETCH NEXT FROM c INTO @LogFile;

SET @COMMAND =
'ALTER DATABASE "' + @DBName + '" SET RECOVERY SIMPLE';
EXEC (@COMMAND);
CHECKPOINT

BACKUP LOG @DBName WITH NO_LOG

SET @command =
'
USE ' + @DBName + ';
DBCC SHRINKFILE (' + @LogFile + ', 1);
';

EXEC (@COMMAND);
CLOSE c
DEALLOCATE c

SET @COMMAND =
'ALTER DATABASE "' + @DBName + '" SET RECOVERY FULL';
EXEC (@COMMAND);

SET @command =
'SELECT
*
FROM
[' + @DBName + '].SYS.SYSFILES
WHERE
name LIKE ''' + @LOGSTR + ''';'

EXEC (@COMMAND);

END

A bit of SSIS confusion with server names

When registering a new Integration Service in SSMS, you're can only enter a server name. It won't register the instance. When you are importing/exporting a package to/from one instance to another, enter the server name AND instance in the server list even though it may only supply just the server name. Add the "\" and instance name to the server name as you would say registering a new server instance. You'll get an "OLE DB error 0x80004005 (Client unable to establish connection) occurred while enumerating packages" error. You're not able to log into the server because its name is incomplete.

i had found ONE listing that pointed to the correct resolution: http://social.msdn.microsoft.com/forums/en-US/sqlintegrationservices/thread/8da717ef-05e0-4a59-9a91-a96e29d4dfa6/ Read down about midway to get the answer.

When a database is stuck in restore mode

A vendor was working on one of our (their) DEV DBs. They were restoring it with something and did something to interupt it or it just got corrupt. I've tried a variety of things that could stop with no luck. finally upon searching for a solution, in a post of some forum foudn from googling, someone stated that you can:

"If no recovery option is specified in your command then it defaults to WITH RECOVERY...

I don't see any problem deleted the db and restoring it...but you can restore on top of db which is in loading status..."

So, therefore, I did a RESTORE, which I can now do after restarting the SQL and Agent services, WITH RECOVERY successfully! If you can not restore which I had previously tried to do getting an error stating it was still in progress of a restore, restart the services first then re-restore with recovery!

Good Luck!

Freebooting

remember a show on HBO a couple of decades ago called Not Necessarily The News? They had a segment called sniglets which has gotten popular in various media and emails. Its where you create alternative definitions for words or create/combine words to make new definitions. I heard this term being used at work in this manner. I got a giggle out of it. hope you do to!

Freebooting: The act of randomly rebooting multiple servers in the hope that your problem will go away.

Freebooter: One who frequently commits this act in order to save their job…

Windows Integrated Security in Reporting Services 2005

This issue has been tying me in knots. I am moving away from SQL authentication as CAC is the preferred method in the federal govt for accessing computers and servers. I also don't want to use no credentials. thats just nonsense. Unfortunately, I'm going to require users to use their network login barring my being directed to use a SQL account, not use credentialing which I'm loathe to do or Kerberos v5 is used. Therefore, users will have to enter their credentials until a better way is found. wish there was a workaround!

Windows Integrated Security
When you use the Windows Integrated Security option, the report server passes the security token of the user accessing the report to the server hosting the external data source. In this case, the user is not prompted to type a user name or password.

This approach is recommended if Kerberos is enabled. If Kerberos is not enabled, you should only use this approach if all the servers that you want to access are located on the same computer.

Error When Deploying a Large RS Report Model
by David Leibowitz | May 30, 2008

You may recieve the error during the deployment of a Reporting Services model:
There was an exception running the extensions specified in the config file.
Maximum request length exceeded.
To correct, you need to increase the file size limit currently in place in the web.config on the RS Server.
Find the web.config file located in the directory:
[SQL Server Directory]/[MSSQL RS Instance]/Reporting Services/ReportServer
Locate the node and add the attribute maxRequestLength and a number, or simply modify the value until you increase the limit such that you can deploy.
Mine looked like this when I was done:

Save the file and redeploy. You can switch it back if you like when you are done deploying.

Full attribute listing for httpRuntime here:
http://msdn.microsoft.com/en-us/library/e1f13641(VS.80).aspx
http://jetlounge.net/blogs/teched/archive/2008/05/30/error-when-deploying-a-large-rs-report-model.aspx

Here's good link for grasping the resl source of the "login failed" error message. I've supplied a link to it.

http://blogs.msdn.com/sql_protocols/archive/2006/02/21/536201.aspx


Understanding "login failed" (Error 18456) error messages in SQL Server 2005

In continuing with the theme of understanding error messages I'll discuss the "login failed" messages that are surfaced by the client and written to the server's error log (if the auditlevel is set to log failures on login which is the default) in the event of an error during the login process.

If the server encounters an error that prevents a login from succeeding, the client will display the following error mesage.

Msg 18456, Level 14, State 1, Server , Line 1
Login failed for user ''

Note that the message is kept fairly nondescript to prevent information disclosure to unauthenticated clients. In particular, the 'State' will always be shown to be '1' regardless of the nature of the problem. To determine the true reason for the failure, the administrator can look in the server's error log where a corresponding entry will be written. An example of an entry is:

2006-02-27 00:02:00.34 Logon Error: 18456, Severity: 14, State: 8.

2006-02-27 00:02:00.34 Logon Login failed for user ''. [CLIENT: ]




nThe key to the message is the 'State' which the server will accurately set to reflect the source of the problem. In the example above, State 8 indicates that the authentication failed because the user provided an incorrect password. The common error states and their descriptions are provided in the following table:

ERROR STATE
ERROR DESCRIPTION

2 and 5
Invalid userid

6
Attempt to use a Windows login name with SQL Authentication

7
Login disabled and password mismatch

8
Password mismatch

9
Invalid password

11 and 12
Valid login but server access failure

13
SQL Server service paused

18
Change password required



Other error states indicate an internal error and may require assistance from CSS.

Il-Sung Lee
Program Manager, SQL Server Protocols

Connecting a Report Server from Management Studio

To connect to a Report Server from Management Studio, there are a few things to consider.

First, I can not connect to an RS 2000 instance. I'll keep trying. Second, make sure the machine is functional. That really helps too. :-( IT can be a real time killer LOL Last, I have read many a sites/posts all of which say use the syntax for a servername as:

http:///reportserver

What I found that truly works is http://reports_web_site/reportserver

* reports web site example --> rptsvrname.yoursite.com i.e. the url you give your report server in IIS.

* reportserver --> the actual text "reportserver "

rsConfig utility for resetting the rsReportServer.config encrypted settings

Here's a good error that will require use of rsconfig:

The report server cannot open a connection to the report server database. A connection to the database is required for all requests and processing. (rsReportServerDatabaseUnavailable).

This error occurs when the report server cannot connect to the SQL Server relational database that provides internal storage to the server. The connection to the report server database is managed through the Reporting Services Configuration tool. You can run the tool, go to the Database Setup page, and correct the connection information. Using the tool to update connection information is a best practice; the tool ensures that dependent settings are updated and that services are restarted. For more information, see Configuring a Report Server Database Connection and Configuring Service Accounts and Passwords in Reporting Services http://msdn.microsoft.com/en-us/library/ms160340.aspx.

This error can also occur if the Database Engine instance that hosts the report server database is not configured for remote connections. Remote connection is enabled by default in some releases of SQL Server 2005. To verify whether it is enabled on the SQL Server Database Engine instance you are using, run the SQL Server Surface Area Configuration tool. You must enable remote connections for both TCP/IP and named pipes. A report server uses both protocols. For instructions on how to enable remote connections, see the section "How to Configure Remote Connections to the Report Server Database" in Configuring a Report Server for Remote Administration http://msdn.microsoft.com/en-us/library/ms365170.aspx.

If the error includes the following additional text, the password expired on the account used to run the Database Engine instance: "An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not permit remote connections. (provider: SQL Server Network Interfaces, error: 26 - Error Locating Server/Instance Specified)." To resolve this error, reset the password. For more information, see Changing Passwords and User Accounts .


Product: SQL Server Reporting Services
ID: rsReportServerDatabaseUnavailable
Source: Microsoft.ReportingServices.Diagnostics.Utilities.ErrorStrings.resources.Strings
Version: 8.00
Message: The report server cannot open a connection to the report server database. A connection to the database is required for all requests and processing.
Explanation
This error occurs when the report server is unable to connect to the report server database. The rsreportserver.config file stores encrypted values for the database name and credentials that it uses to connect to the report server database. If the database name or credentials have changed, the report server can no longer connect to the database.

User Action
You can use the rsconfig utility to update or repair the configuration settings that the report server uses to connect to the report server database. You can use the -d option of the utility to set the name of the report server database and you can use the -u and -p options to set the username and password. For information about how to use the rsconfig utility, see Books Online.
--------------------------------------------------------------------------------
Currently there are no Microsoft Knowledge Base articles available for this specific error or event message. For information about other support options you can use to find answers online, see http://support.microsoft.com/default.aspx.


Here's how to use rsconfig and where to find it:

\Program Files\Microsoft SQL Server\90\Tools\Binn\rsconfig.exe

http://msdn.microsoft.com/en-us/library/ms162837.aspx

rsconfig –c for the encrypted connection settings –e for the encrypted unattended account settings -m computername that RS resides on –i RS instance name –s server name that the database resides on –d RS database name –a authentication method which is either "windows" or "sql" -u username -p password (-t trace)

rsconfig -? will show you the syntax

Report Server errors

Reports Server errors

things to remember:

RS service account --> DOMAIN ACCOUNT!!!
Login account encrypted in the rsReportServer.config --> also a domain account (go ahead and use the same one!) use the rsConfig.exe utility. see the next post

The problem is the accounts. notice the login failure. Login failed for user 'ReportServer'

w3wp!library!ac4!07/23/2008-10:15:10:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerDatabaseUnavailableException: The report server cannot open a connection to the report server database. A connection to the database is required for all requests and processing., ; Info: Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerDatabaseUnavailableException: The report server cannot open a connection to the report server database. A connection to the database is required for all requests and processing. ---> System.Data.SqlClient.SqlException: Login failed for user 'ReportServer'. at System.Data.SqlClient.ConnectionPool.CreateConnection() at System.Data.SqlClient.ConnectionPool.UserCreateRequest() at System.Data.SqlClient.ConnectionPool.GetConnection(Boolean& isInTransaction) at System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnectionString options, Boolean& isInTransaction) at System.Data.SqlClient.SqlConnection.Open() at Microsoft.ReportingServices.Library.ConnectionManager.OpenConnection() --- End of inner exception stack trace ---w3wp!library!428!07/23/2008-10:15:15:: i INFO: Call to GetSystemPermissionsw3wp!library!428!07/23/2008-10:15:15:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerDatabaseUnavailableException: The report server cannot open a connection to the report server database. A connection to the database is required for all requests and processing., ; Info: Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerDatabaseUnavailableException: The report server cannot open a connection to the report server database. A connection to the database is required for all requests and processing. ---> System.Data.SqlClient.SqlException: Login failed for user 'ReportServer'. at System.Data.SqlClient.ConnectionPool.CreateConnection() at System.Data.SqlClient.ConnectionPool.UserCreateRequest() at System.Data.SqlClient.ConnectionPool.GetConnection(Boolean& isInTransaction) at System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnectionString options, Boolean& isInTransaction) at System.Data.SqlClient.SqlConnection.Open() at Microsoft.ReportingServices.Library.ConnectionManager.OpenConnection() --- End of inner exception stack trace ---


Cool error below - notice .dbo.ExecutionCache. Check to see if the RSTempDB is missing. Most likely it had be removed during an install of an RS server. You'll need to re-attach the mdf/ldf files of the DB that got dropped. Be sure you have admin access to run the attach no matter which method you use.

w3wp!library!e1c!07/23/2008-13:47:28:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException: An internal error occurred on the report server. See the error log for more details., ; Info: Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException: An internal error occurred on the report server. See the error log for more details. ---> System.Data.SqlClient.SqlException: Invalid object name 'ReportServerTempDB.dbo.ExecutionCache'. at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream) at System.Data.SqlClient.SqlCommand.ExecuteReader() at Microsoft.ReportingServices.Library.InstrumentedSqlCommand.ExecuteReader() at Microsoft.ReportingServices.Library.DBInterface.GetReportForExecution(String objectName, String effectiveParams, Boolean& foundInCache, ItemType& type, ReportSnapshot& intermediateSnapshot, ReportSnapshot& snapshotData, Guid& link, String& linkPath, String& properties, String& description, Byte[]& secDesc, Guid& reportID, Int32& execOptions, DateTime& executionDateTime, Boolean& hasData, Boolean& cachingRequested, DateTime& expirationDateTime) at Microsoft.ReportingServices.Library.RSService.GetReportAndDataSourcesForExecution(CatalogItemContext itemContext, String effectiveParametersXml, Boolean& hasData, DataSourceInfoCollection& dataSources, String& reportProperties, ReportSnapshot& intermediateSnapshot, Guid& reportId, ReportSnapshot& snapshotData, String& description, Boolean& cachingRequested, DateTime& executionDateTime, DateTime& expirationDateTime, Int32& execOptions, Boolean& foundInCache) at Microsoft.ReportingServices.Library.RSService.RenderAsLiveOrSnapshot(CatalogItemContext reportContext, ClientRequest session, Warning[]& warnings, ParameterInfoCollection& effectiveParameters) at Microsoft.ReportingServices.Library.RSService.RenderFirst(CatalogItemContext reportContext, ClientRequest session, Warning[]& warnings, ParameterInfoCollection& effectiveParameters, String[]& secondaryStreamNames) --- End of inner exception stack trace ---w3wp!library!e1c!07/23/2008-13:47:28:: i INFO: Initializing EnableExecutionLogging to 'True' as specified in Server system properties.w3wp!webserver!e1c!07/23/2008-13:47:31:: e ERROR: Reporting Services error Microsoft.ReportingServices.Diagnostics.Utilities.RSException: An internal error occurred on the report server. See the error log for more details. ---> Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException: An internal error occurred on the report server. See the error log for more details. ---> System.Data.SqlClient.SqlException: Invalid object name 'ReportServerTempDB.dbo.ExecutionCache'.w3wp!dbcleanup!858!7/23/2008-13:57:06:: e ERROR: Sql Error in CleanExpiredSessions: System.Data.SqlClient.SqlException: Invalid object name 'ReportServerTempDB.dbo.SessionData'. at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at Microsoft.ReportingServices.Library.InstrumentedSqlCommand.ExecuteNonQuery() at Microsoft.ReportingServices.Library.DatabaseSessionStorage.CleanExpiredSessions()w3wp!dbcleanup!858!7/23/2008-13:57:06:: e ERROR: Sql Error in CleanExpiredCache: System.Data.SqlClient.SqlException: Invalid object name 'ReportServerTempDB.dbo.SnapshotData'. at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at Microsoft.ReportingServices.Library.InstrumentedSqlCommand.ExecuteNonQuery() at Microsoft.ReportingServices.Library.DatabaseSessionStorage.CleanExpiredCache()w3wp!dbcleanup!858!7/23/2008-13:57:06:: e ERROR: Sql Error in CleanOrphanedSnapshots: System.Data.SqlClient.SqlException: Invalid object name 'ReportServerTempDB.dbo.SnapshotData'. at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream) at System.Data.SqlClient.SqlCommand.ExecuteReader() at Microsoft.ReportingServices.Library.InstrumentedSqlCommand.ExecuteReader() at Microsoft.ReportingServices.Library.DatabaseSessionStorage.CleanOrphanedSnapshots(Int32& chunksCleaned)w3wp!library!858!7/23/2008-13:57:06:: i INFO: Cleaned 0 batch records, 0 policies, 0 sessions, 0 cache entries, 0 snapshots, 0 chunks, 0 running jobsw3wp!dbcleanup!858!7/23/2008-14:07:06:: e ERROR: Sql Error in CleanExpiredSessions: System.Data.SqlClient.SqlException: Invalid object name 'ReportServerTempDB.dbo.SessionData'. at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at Microsoft.ReportingServices.Library.InstrumentedSqlCommand.ExecuteNonQuery() at Microsoft.ReportingServices.Library.DatabaseSessionStorage.CleanExpiredSessions()w3wp!dbcleanup!858!7/23/2008-14:07:06:: e ERROR: Sql Error in CleanExpiredCache: System.Data.SqlClient.SqlException: Invalid object name 'ReportServerTempDB.dbo.SnapshotData'. at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at Microsoft.ReportingServices.Library.InstrumentedSqlCommand.ExecuteNonQuery() at Microsoft.ReportingServices.Library.DatabaseSessionStorage.CleanExpiredCache()w3wp!dbcleanup!858!7/23/2008-14:07:06:: e ERROR: Sql Error in CleanOrphanedSnapshots: System.Data.SqlClient.SqlException: Invalid object name 'ReportServerTempDB.dbo.SnapshotData'. at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream) at System.Data.SqlClient.SqlCommand.ExecuteReader() at Microsoft.ReportingServices.Library.InstrumentedSqlCommand.ExecuteReader() at Microsoft.ReportingServices.Library.DatabaseSessionStorage.CleanOrphanedSnapshots(Int32& chunksCleaned)w3wp!library!858!7/23/2008-14:07:06:: i INFO: Cleaned 0 batch records, 0 policies, 0 sessions, 0 cache entries, 0 snapshots, 0 chunks, 0 running jobsw3wp!webserver!b70!7/23/2008-14:08:23:: i INFO: Reporting Web Server stopped

cant use USE

this should work - am trying it now. it doesn't fail.

--**************************** --REPLACES USE @NAME;--****************************DECLARE @cmd nvarchar(50)SELECT @cmd = 'USE '+ @NAME+';'EXEC sp_executesql @cmd;--GO;--****************************


more coming!

SQL Server Forensic Analysis

SQL Server Forensic Analysis
By: Kevvie Fowler
Last Updated on Safari: 2008/04/05
Publisher: Addison Wesley Professional
Pub Date: December 12, 2008 (estimated)
ISBN:
Pages: 179





Overview

Nearly all forensic investigations reach a turning point when investigators determine that a database has been breached. There may be no third-party logging system in place, but security personnel are still left wondering if credit card data was stolen or if their system was modified.

The situation is made all the more alarming by the fact that large database breaches are increasing in frequency and that, if investigators are unable to assess and qualify the scope of an intrusion, they are forced to report it as a possible compromise.

These disclosures are painful for companies, their customers, and their shareholders. They are even more painful when, in fact, no sensitive data was ever compromised.

The best way to prevent such breaches and inaccurate reporting is to perform forensic analysis on the SQL server and be certain of extent of any attack or breach.

In SQL Server Forensic Analysis, author Kevvie Fowler presents the first in-depth look into how SQL Server forensics can be used to identify and extract the database evidence needed to confirm, assess, and investigate a digital intrusion.The book begins by giving the reader an overview of SQL server forensics and key SQL server database components, then proceeds to a concise and clear look at database forensic techniques that can be used to gather the evidence hidden within the published and unpublished areas of a SQL server.

Readers will learn how to prioritize, acquire, and analyze database evidence using forensically sound practices and free industry tools. The final chapter will include a case study that demonstrates all the techniques from the book applied in a walk-through of a real-world investigation.This book is appropriate for a wide range of professionals, including digital forensic practitioners, information security analysts, information security managers, database administrators, auditors, and law enforcement professionals. Readers will walk away from the book able to do the following:

• Use SQL Server forensics to verify and assess a digital intrusion
• Identify and extract database information from published and unpolished areas of SQL Server
• Build a SQL sever forensic toolkit
• Detect and Remove SQL Server rootkits
• Recover and reconstruct deleted database material
• SQL Server Forensic Analysis is a must-have book for anyone charged with preventing or investigating modern digital intrusions.
  
  
  Purchase "SQL Server Forensic Analysis" - Retail Price: USD $54.99
  Top of Form
  Online, PDF and Print Book Bundle
  USD $74.23
  Online and PDF Access
  USD $38.49
  Print Book Pre-Order USD $54.99
  USD $38.49
  Bottom of Form
  
  

Linked tables to SQL Server

I have found on more than one occasion since migrating to SQL Server 2005 that if you have an ACCESS 2000 front end there can be an incompatibility of datatypes. Modify all ntext or text columns in SQL to nvarchar(MAX) as those types will no longer be supported. Also, modify bit columns to something like tinyint. Apparently, Access can't handle bit fields.

ORDER BY CLAUSE IN A VIEW

I FOUND THIS ONE INTERESTING. IN CREATING A VIEW IN 2005, YOU CAN USE AN ORDER BY CLAUSE WITHOUT A TOP OR FOR XML. I DON'T REMEMBER THIS BEING THE CASE IN 2000 OR BEFORE.

Msg 1033, Level 15, State 1, Procedure vwTESTING_ORDER_BY, Line 30
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.

SURVIVAL!!!

For the 1st time in nearly 25 years, I've survived a reorg!!!

Global Variables in the new SSIS packages

GOOD QUESTION FROM A COLLEGUE:


Got a question about SSIS. There doesn't seem to be a good equivalent to the Global Variables in old DTS in the new SSIS packages. Are you using some solution that you recommend? I know there is Win environment variables and xml config files, but these are not very friendly to move.

MY RESPONSE:


Ooooooo interesting question! I'll look but I'm willing to bet with it
being a .NET solution/project, you can probably put them in a bas file or
class file. Also, check the Package properties. Click the ... Button for
expressions. See if that helps. Being that it's a VS project and not a
MSSQL project, I'd assume listing variables like you would in VB.NET. I
recently starting thinking of SSIS in terms of VS instead of SQL. It really
opens the door to a lot of things. I'd written replication apps in VB6 with
the DMO. I know you can do the same in DTS. So that's where I'd start
looking. Its still encapsulated but pushed out of the IDE. I just added a
VB script to a project. I guess it could be called from a task I don't
have regular VS.NET on this server or my machine, but I know I should be able to add a bas or cls to a project. I'll look further and let you know
what I find!

GOTTA LOOK INTO THIS ONE FURTHER!

SQL SERVER COPY OBJECTS TASK AND 2000 COMPATIBILITY

HERE'S A GOOD ONE!

IN TRYING TO USE SSIS TO COPY DATABASE OBJECTS FROM ONE SERVER TO ANOTHER, IT FAILED. IT FAILED BECAUSE THE DATABASES HAD 2000 COMPATIBILITY!!! I HAD TO CREATE A 2000 DTS PACKAGE, USE THE SQL SERVER COPY OBJECTS TASK TO PUSH SPs BETWEEN THE SERVERS! THIS ONE NEEDS TO BE LOOKED INTO. I GUESS MS ISN'T LOOKING BACK!

LogParser 2.2

just completed parsing an IIS log into SQL Server with a tool I found yesterday. LogParser 2.2, which can be downloaded from MS downloads. check with the MS IIS site http://www.iis.net/default.aspx?tabid=1 you can parse a wide variety of log files from system logs like the event log to SQL Server logs to IIS logs. You have to do a little work from the command line, but I like the tool. I generally prefer a nice user friendly gui but this gets the job done when you understand the params.

also, about a month or so ago i downloaded an XML editor i remember from several years ago that several developers i knew really liked. its much improved and i highly recommend it - XML Notepad. Its a VS 2007 tool. give it a whirl. It made understanding the XML data in my GPS much easier. web configs are much clearer especially changes are made in a hurry. it can also "beautify" the xml code making life a little easier!

determine the compatibility level prior to migration

Ahhhh here's one from the past couple of weeks!

When moving a database to 2005 from 2000. select properties of the database, go to options and see what the compatibility level is. DSNs and apps can be affected by this. For example, I was working on a database issue with linked tables in Access. Users lost connectivity. i.e. they couldn't edit. The database compatibility level was set to the default 2005 when created yet ODBC connections and ADO.NET connection strings were using the SQL Server 2000 driver not the SQL Native Client driver. In reducing compatibility of the database to 2000 level, users were able to edit data with their current 2000 settings. issue resolved without user interaction. As I had used copies of the 2005 database, I had to delete non-2000 versions and rename. The users simply had to select and delete their linked tables and check the "Always prompt for a new location" checkbox. Basically, you're going to refresh your DSN connection and table linkage. Also, developers of a database that migrated a 2000 database after our team did, kept the default compatibility level yet, the team here set it to 2000. Connectivity issues in code will generate lost profit for the developers team due to code rewrite, process changes, etc.

The moral of the story is to determine the compatibility level prior to migration to 2005 AND communicate this to all stakeholderes!!

SSIS dropping tasks from 2000 DTS packages

I found a new issue with SQL Server Integration Services yesterday!

It seems that if you have tasks from a 2000 DTS package with the same name, that SSIS will drop one. This is not the first issue I've found when migrating 2000 DTSs to 2005. I really like the tool but I've been finding gotchas along the way.

Anyone else run into this? any thoughts?

Welcome to Critical Status - Database-Oriented Issues Plus

Welcome to Critical Status - Database-Oriented Issues Plus!!!

I hope that the ideas and issues I find in the mindfield we call IT is of help to you all. Please share your thoughts with me as we can never stop learning!