Archive for the 'SQL Server' Category

I was recently dealing with importing a DBISAM (Btrieve) database to SQL Server, which had separate date and time columns where I wanted to import a DateTime.  Using the ODBC driver and a DataReader data source, it was returning the date as the DT_DBTIMESTAMP type and the time as a DT_I8 long integer. I tried [...]

If you want to add a constant GUID to a table during a DTS transformation, it’s a little tricky.  Here’s the solution. Use the Derived Column transformation in order to add a new column.  For the formula, use the GUID surrounded by double quotes.  The trick is that you MUST include the curly braces, like [...]

Ever wondered how to quickly and easily get the date portion of a datetime field in a SQL query? Seems like it should be easy, right? Unfortunately, it isn’t. I’ve found a lot of solutions, but this one is the most elegant I’ve seen. CONVERT(datetime, FLOOR(CONVERT(float, DateTime))) I’ve put this into a user-defined function below: [...]

Another issue I’ve run into with the service broker and moving databases between servers is this error message: Error code:25. The master key has to exist and the service master key encryption is required. The solution that I found was the regenerate the key for the database using the following code: CREATE MASTER KEY ENCRYPTION [...]

Sometimes I’ve run into the following error in the event log when using the service broker to do SQL command notifications with SQL 2005: An exception occurred while enqueueing a message in the target queue. Error: 15517, State: 1. Cannot execute as the database principal because the principal “dbo” does not exist, this type of [...]