Set time to zero in a datetime value

If you have a datetime value that displays in the following format : 2012-11-27 14:53:15.4430000
and you would like it to be displayed this way (time set to "0"): 2012-11-27 00:00:00.0000000

Tip : 

declare @myDate datetime2
select @mydate = DATEADD(DD, 0, DATEDIFF(DD, 0, GETDATE()))

;)

compare 2 tables with different names or schema

Problem:

You have 2 tables that you would like to compare at the column level (retrieve a list of the columns with different data types and/or names).
It happened to me today and the tables had 100+ columns so...

Alternative 1:

if the tables have the same names and a primary key , you can use one hidden gem of SQL Server (since version 90) called tablediff.exe .

Alternative 2:

My problem is the following , both tables belong to the same schema but hold different names. One of the tables is a simple import table (used to import text files) and has no primary key. Archiving the data from the import table to the destination table fails due a potential data truncation.

For the sake  of the example I will a use a database called TestDb that have 2 tables : books and books 1.

As you can see here the only difference is the BookTitle field that has a different length.
Run the following query :

The except statement will select (without duplicates) the elements contained in the left table that are not present in the right table (here books1)
here is the result:

Rgds.

SSIS Error Code DTS_E_OLEDB_EXCEL_NOT_SUPPORTED

just published "another ssis package" on one of our servers runnig Windows 2008R2.
Go the following error :

Description: SSIS Error Code DTS_E_OLEDB_EXCEL_NOT_SUPPORTED: The Excel Connection Manager is not supported in the 64-bit version of SSIS, as no OLE DB provider is available.  End Error  Error: 2012-10-18 15:43:10.76     Code: 0xC00291EC     Source: create xls file for  Execute SQL Task     Description: Failed to acquire connection "DestinationConnectionExcel". Connection may not be configured correctly or you may not have the right permissions on this connection.  End Error  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  15:43:08  Finished: 15:43:10  Elapsed:  2.309 seconds.  The package execution failed.  The step failed.

 the fact is using 64bits you cannot use Microsoft.JET.OLEDB.4.0.
To work around this open the job step properties ans choose the execution option tab.

then check the following option below left of the properties window.

here is its , now you can you Jet Oledb for excel without issue !

SQL Template to delete SQL job, maintenance plan and schedule

Problem: 
on our test servers , or servers that used to be managed by third party companies, the sql job list can become ...messy.
I came with a SQL server template to delete the job, the maintenance plan and the schedule if unused.

1. Create the template:

open the "view Template" window:

the template menu appears by default at the right of the SSMS window.

Create a folder of you choice in the Template explorer list , right click and choose "new template"

Name the template as you wish , right click the template and choose "Edit".

2. Now copy  the code that you can download from here

within the code you will notice special strings , these are the template parameters :

3.Save the template
4. Using the template:

Create a new query on the server on which you want to run the template.
Then simply drag the template you just saved on the blank query window
The template code is then copied on the new query window.
Then here is the cool stuff: click  on CTRL+SHIFT+M and you will be prompted to enter the parameters:

The first parameter  is to specify the job name, the second parameter (Delete_Maintenance_plan) should be  set to 1 if you want to delete the associated maintenance plan. Of course this is only a simple script, error check should be added to check if there is actually a maintenance plan etc..

Nonetheless it is still useful, and shows the basics of using templates.

Free sql online formatter tool

Recently found out this TSQL Formatting tool that is worth sharing : Instant SQL Formatter
Really like it as there are many options, can really save some time and improve readability.

enjoy

sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029)

We still have an SQL 2000 production server from a third party supplier. The backups failed for several days and looking on serveral blogs I found that some had disk space issues.

I did not have a disk space issue BUT a consultant from the third party had restricted the rights of the DATA folder to administrators only !

so if you run into this error :

1. check that you have sufficient disk space
2. check the data folder security :)

How to select column names of a table

select COLUMN_NAME  from INFORMATION_SCHEMA.COLUMNS where  TABLE_NAME ='my_table'

SQL Error 7302 Cannot Create linked server for OleDb Provider

Symptoms:

You Create a linked server for an excel or Access file. The following procedure relates to Excel 2003 (Microsoft.Jet.Oledb.4.0) and Excel 2007. (Ace driver – Microsoft Office 12 Access OleDb Provider.
You cannot make the connection and receive the error message “cannot load Microsoft Jet oledb provider for linked server null – SQL Error 7302”.

Note: Do not install MS Office 2007 on a server to connect to Excel or Access 2007 files. Only connectivity tools are needed (ACE Drivers).
          
If you try to run a select on a worksheet you can see the following message in the Event viewer (Applications):
The machine-default permission settings do not grant Local Activation permission for the COM Server application with CLSID
{2206CDB0-19C1-11D1-89E0-00C04FD7A829}
to the user <user> SID (sid).  This security permission can be modified using the Component Services administrative tool.

RESOLUTION

Grant the user permissions to start the COM component. To do this, follow
these steps:

1. Click Start, click Run, type regedit in the Open box, and
then click OK.

2. Locate and then click the following registry subkey:
HKEY_CLASSES_ROOT\CLSID\CLSID value
Note In this subkey, "CLSID value" is a placeholder for the CLSID
information that appears in the message.

3. In the right pane, double-click AppID.
The Edit String dialog box appears. Leave this dialog box open and continue
to the next step.

4. Click Start, click Run, type dcomcnfg in the Open box, and then click OK.

If a Windows Security Alert message prompts you to keep blocking the
Microsoft Management Console program, click to unblock the program.
5. In Component Services, double-click Component Services, double-click
Computers, double-click My Computer, and then click DCOM Config.

6. In the details pane, locate the program by using the friendly name.
If the AppGUID identifier is listed instead of the friendly name, locate the
program by using this identifier.

7. Right-click the program, and then click Properties.

8. Click the Security tab.

9. In the Launch and Activation Permissions area, click Customize, and then
click Edit.

10. Click Add, type the user's account name, and then click OK.

11. While the user is selected, click to select the Allow check boxes for
the following items: 
.. Local Launch
.. Remote Launch
.. Local Activation
.. Remote Activation

12. Click OK two times.

13. Quit Registry Editor.

Passed Exam 70-433

Currently on the road to the MCITP SQL developer , I already own one in database administration.
Scored 1000/1000 today at the 70-433 so quite happy :)
I used Ucertify prep kit along with the usual vce files, worked great!!

Error formatting query, probably invalid parameters [SQLSTATE 42000] (Error 22050)

I spent 2 days searching for the above error , the situation is as follows :

1. we have 2 servers, one for dev, one for production using the same domain account both for the SQL Server service and SQLAgent service.
2. the user is not sysadmin on either server.
3. Calling sp_send_dbmail  works fine in SSMS on both servers
4. On my production server , it did not work and had the above error.

On some posts, I read that some people added the SQL Server agent account on the sysadmin group and "it worked" (sounds to me as a quick and dirty solution).

Finally noticed that job owners were different on both servers:
- on the development server , the job owner is a database user (db_owner), with DatabaseMailUserRole.
- on the production server, the job owner is our sysadmin user (not listed in DatabaseMailUserRole).

I just changed the job owner on the production server and it worked !! No sysadmin user to set up, it works with the application user.

VS2010 Network BIOS Command Limit has been reached

Resolution :

1. Start Menu | Run | Regedit.exe
2. Create a DWORD key (32 bits) in HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\LanmanWorkstation\parameters , name the key "MaxCmds" with a decimal value of 500.
3. Create a DWORD key (32 bits) in  HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\LanmanServer\parameters, name the key "MaxMpxCt" and give it a decimal value of 500.
4. Reboot the computer.

Ace oledb cannot be found with oledb connection (ssis)

Currently on my workstation I have Office 2010 64 bits, and Ace oledb driver 64 bits. Working in BIDS I could not find the Ace driver available to create a new Oledb Connection.

Since BIDS is 32 bits I had to :

1. Remove Ace driver 64 bits
2. Remove 64 bits Office Products
3. Reinstall Ace 32 bits
4. Reinstall Office 32 bits (!)
5. In Bids, choose Project Properties and  in "Debugging"  set Run64bitsRuntime to "False".

Hope this helps :)

stop ssis package when a For Each loop enumerator is empty

Problem : You want to stop a package when a For Each Loop Enumerator is empty.

Solution : Add a OnWarning event to the for each loop checking for right Error code.

Steps :

1. Add an OnWarning Event handler to the For Each Loop Enumerator:

2. Add A Script component to the Event handler that can read the following system variables:

3. Add the following code to test if the For each loop is empty:

SSIS Error : Failed to decrypt protected XML node DTS:Password" with error 0x80070002

error Message :
 Description: Failed to decrypt protected XML node "DTS:Password" with error 0x80070002 "The system cannot find the file specified.". You may not be authorized to access this information. This error occurs when there is a cryptographic error 

 I've had the same error and just solved it.
As well as changing the protection level of the packages to DontSaveSensitive, you also need to make sure that anywhere within the package that points to a network drive i.e. all connection managers, and within any execute package containers, all the the file paths do not start off with the letter of the drive, instead use the server name.

i.e. K:\foldername\inputfile.xls

becomes: -

\\ServerName\foldername\inputfile.xls

Unable to start mail session (reason: No mail profile defined) - Message in Error Log

Message
[098] SQLServerAgent terminated (normally)
This is: the most common message that will be logged when there is no "Mail Session" defined for the SQL Server Agent Alert System.

To Fix this or to make this message disappear in the Error Log, you have to enable "Mail Session" and re-start the SQL Server Agent.
To Do this,

• Connect to the Server
• Right Click on the "SQL Server Agent" and go to "Properties"
• Then Go to "Alert System" Tab
• Make sure the check box "Enable Mail Profile" is checked
• Click OK to exit
• Re-start the SQL Server Agent Service

SQL setup fails to update - installer cache is missing files

installing the service pack you are receiving the following error message:

a link to correct this problem can be found here but still it is quite tricky.

You can download a file, FindSQLInstalls.vbs to gather the details of your SQL Server  installation. I had errors running the file at first , removing the comments at the top of the file solved the issue.

Mostly I found errors relating to missing path, both the original cd that was copied in a directory, and directory created  by the SQL server setup that were missing like d:\6e712df430a776566d80fe027ca507.

recreating the file to the original setup cd on the disk was mandatory, I tried to recreate the setup directory above (d:\6e712df430a776566d80fe027ca507) as a shortcut for the setup to relink source and destination files within  the c:\windows\installer directory, but the latter did not work.

I didn't have to copy all the files listed in the one created by the vbs script - I relaunched the Setup 4-5 times, looked for the missing files in the .txt file and finally went through the SP3 setup normally.

Steps:

1. Go to http://support.microsoft.com/kb/969052 and download the FindSQLInstall.vbs script
2. Using a command prompt (elevated privileges), run  Cscript FindSQLInstalls.vbs %computername%_sql_install_details.txt
3. open the text file to view the details.
4. Recreate the path to the original setup cd if required
5. Try to rerun setup and look in the text file for the missing file, copy it in the %windir%\installer directory (serveral trials may be required if serveral files are missing as in my case)