Search results

I've been attempting to connect my SQL Development box at work to Oracle lately, and it's been a real struggle. I'll see if I can document the process a bit, in case someone else ends up running into issues (Why is it so difficult to set up a connection to Oracle to begin with?). Today, after...

Hello and welcome! The error description seems to indicate that when attempting to do a lookup, it did not find a match in the lookup table. This can happen when the lookup table has not been updated yet, or there is no "fall-back" record for unknown/to-be-fixed IDs. However, the reason why...

Hi John, and welcome. When you say "both versions", what versions specifically are you referring to? You'd usually have a CD or ISO file that contain the setup executable and all files needed. Otherwise ask your system administrator for them.

If it would be acceptable to retreieve data that has not yet been committed, you might see a performance benefit from using a NOLOCK index hint. Another option could be to better optimize the indexes on your source database/tables, or taking a snapshot or backup of the database for loading.

It should not be an issue inserting foreign key IDs, unless of course the referenced ID does not exist. If the Primary key is an IDENTITY column, and you know for sure that the values you are inserting do not conflict with the existing values in the table (for instance because the records to...

I figured I would start putting some of the scripts I use regularly onto the site. Here's a small snippet of code to find out the (logical) name of the Transaction Log, and then shrinking it to a new size. I use it whenever I receive a database backup from a client's production database...

Hello and welcome to the forum, I would suggest using a computed column, using the following formula: CONVERT(VARCHAR(10),<your date column>,111) You would replace <your date column> with the name of your actual date/time column. Let me know if that works out for you? Peter

Below is a small script that can be used to check whether or not a particular table in your database contains redundant indexes. By redundant indexes I mean that if there is one index on columns A and B, and a second index on columns A, B, and C, generally speaking, it would be sufficient to...

I just found an easy way to grab individual error messages, and figured I would share it here. In a command window, type: NET HELPMSG <error_id> replacing <error_id> with the actual error number:

Hi Mike, and welcome to MSSQLForum. What is the data type of the NPIID column? In case it is not a bit, try and see if changing the datatype returned by the udf to the same data type as the column does anything? I.e. if the data type of NPIID is int, make the variable in the UDF an int...

I tested this with some testdata, and apart from having to wrap the "VALUES" in squarte brackets (i.e. [VALUES]), the script worked fine for me.The brackets are required due to VALUES being a reserved SQ Server keyword. One thing that might cause some error is that the value of ServiceID...

By default, the stored procedures in SQL Server have a pretty easy naming convention: Regular stored procedures are prefixed with "sp_". Extended stored procedures are prefixed with "xp_". Most people developing against SQL Server will find themselves inclined to prefix their own stored...

While trying to write another script, I suddenly had a need for a password generating script for SQL Server. I previously wrote one in C#, but figured it might also be useful having a T-SQL version of it, so I set off to write one. I ended up doing it as a stored procedure, which in the script...

Should you ever attempt to run an upgrade from a native Navision (CSIDE) database to a SQL Server database, and end up seeing an error "There is not enough memory to complete the current activity", chances are that prior to the upgrade, you ran the Microsoft Upgrade Tool from PartnerSource...

Every so often, a database will end up in a corrupt state. There's nothing much you can do to about it, as normally speaking, these types of failures just happen. You can minimize the impact on your business by using redundant hardware and a solid backup strategy, but you might still have to...

While running a policy check, I stumbled across a number of objects with 'dta_' in their name. If you find similar objects, then these are left-over objects of running a Database Engine Tuning Advisor session that for some reason did not finish and thus did not clean up properly after itself...

Quite a while ago, I received a private message from a reader who wondered whether the advice about the sp_ prefix still was valid. I had to admit that I was not entirely sure, and that perhaps old habits just died hard. But yesterday on Twitter, Kevin Kline happened to tweet a link that shows...

If you're one of the early birds, you grabbed a copy of SQL SErver 2012 - Evaluation Edition. Then chances are you recently received a message that your evluation period ended. If you want to upgrade to another version of SQLServer 2012, this guide might help you do so. First, you should...

A few years ago, during a job interview, I was presented with a scenario where the idea was to perform certain actions upon data being entered into a table. It was not as straight forward as simply being a matter of adding a record (that's when foreign keys would have sufficed), but involved...

Those who have been working with SQL Server administration for a while now undoubtedly have at times refered to the old SQL Server system tables in order to automate some processes, or document their tables by for example combining the sysobjects and syscolumns tables. As per SQL Server 2005 and...