I have some data in my user database that I would prefer to be encrypted. Most of the data will need to be decrypted when requested, but there are also passwords that can stay encrypted (in the old days we would use pwdcompare but I believe this is obsolete now). I have followed the steps here, so I have now successfully encrypted my da...
I've written a stored procedure that takes in a date parameter. My concern is that there will be confusion between American and British date formats. What is the best way to ensure that there is no ambiguity between dates such as 02/12/2008. One possibility would be for users to enter a date in a format such as 20081202 (yyyymmdd). Is th...
How to Set a existing column of MS SQL table as NOT NULL? ...
When restoring my database i have a problem with the physical file of the full text catalog being in use. The file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\FTData\MyCatalog' cannot be overwritten. It is being used by database 'demo2'. I use this restore statement RESTORE database demo from disk = N'c:\temp\demo.bak' WIT...
I need a quick "no" for DELETE/UPDATE/INSERT, since 3p reporting tool allows users to write their own SQL. I know that I should probably add a new user and set permissions on tables/sp/views/etc..., and then create a new connection as restricted user. Is there a quicker way to force a transaction or connection in SQL Server to read o...
Hi, Is there any way in which i can clean a db in SQl Server 2005 i.e by dropping all the tables and deleting stored procedures, triggers, constraints and all the dependencies in one sql statement. REASON FOR REQUEST: I want to have a db script for cleaning up an existing db which is not in use rather than creating new ones, specially ...
I have an @XML document created from a single select statement. <root> <node> <node1> <targetNode> </targetNode> </node1> <node1> <targetNode> </targetNode> </node1> <node1> <targetNode> </targetNode> </node1> </node> <node> ...... </node> </root> I want to insert the xsi:nil as an attribute of 'ta...
Hi, my sql statement is something like this below DECLARE @OLD_NAV_VALUE AS INT DECLARE @FINAL AS INT SELECT @OLD_NAV_VALUE = [col1] from TBL_BA where DATE = @id_Date SET @FINAL = @OLD_NAV_VALUE * 50 But the problem i am haveing here is that the column name in the select statement which is given as [col1] is a dynamic value. So i a...
Is there a way to pass the array of values to the IN section of a SP as a single paramter for SQL Server 2005? Ex: Select * from MyTable where ID IN(@MyValueArray) ...
We have a system that uses UniqueIdentifier as the primary key of each of the tables. It has been brought to our attention that this is a bad idea. I have seen similar post on the subject but I am interested in any MS SQL performance and other potential problems I may encounter due to this decision. ...
Somewhat related to my question about integers instead of decimals; my vendor provides a lot of "boolean" fields in the char(1) format (i.e. Y/N). Some of these fields legitimately cannot be a boolean because they can have several values, but the majority can be treated as a boolean. In my previous question the advice was to do what's ...
I'm working with a SQL Server DB that's got tables spread across multiple schemas (not my idea), so queries end up looking like this: select col1, col2 from some_ridiculously_long_schema_name.table1 t1 inner join another_really_long_schema_location.table2 t2 on... ... you get the idea. This is a small inconvenience when I put...
Here are some (certainly there could be more) of the guiding principles for a new project I'm starting. Addressable URLs Very fast Mobile - cater to iPhones and other smart devices Use existing social networks Live Facebook LinkedIn Twitter Craigslist Etc. Use existing ID services Live ID Open ID Etc. Client and online management a...
Ok, I've scoured the web, BOL, various forums and I'm no closer to an answer...hopefully you fine folks can lend a hand... We've got a dozen or so SQL Servers (some 2k, some 2005) on a network. I'm using SMO objects in a .NET application to get some standard information. My problem appears to boil down to a missing DLL - Microsoft.SqlSe...
I need a tool or method that allows the transfer of data and automatically updates the foreign keys at the destination table. The SET IDENTITY_INSERT ON/OFF is not what I'm looking for. Example: table master (id int identity, name char) table slave (id int identity, master_id int, name char) I would like to create a script like thi...
I have a Windows 6 app I am developing (in VB). I am pulling data from a MSSQL 2005 database. Everything worked fine running it locally (with an emulator) but when I moved the database to a test box, I get the following error: System.Data.SqlClient.SqlException {"Specified SQL server not found: xx.xx.xx.xxx\sql2005"} My connection s...
I want to store times in a database table but only need to store the hours and minutes. I know I could just use DATETIME and ignore the other components of the date, but what's the best way to do this without storing more info than I actually need? ...
When I add this to my where clause: DueDate <= getDate() AND DueDate IS NOT null I get a nice little failure that says: Could not create child: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.Data.EvaluateException: The expression contains undefined function call getDa...
I would like to set up some solid testing on my project and the way I'd like to do it is have a commandline program that I can run that will copy just the structure of a database and not the actual data. Then I can run my tests on that new database. Ideas? Update: someone said I should specify a language. I was thinking TSQL as that ...
I have two tables, one an import table, the other a FK constraint on the table the import table will eventually be put into. In the import table a user can provide a list of semicolon separated values that correspond to values in the 2nd table. So we're looking at something like this: TABLE 1 ID | Column1 1 | A; B; C; D TABLE 2 ID ...