I'd like to setup an SQL Auth user in MS SQL 2005 that can restore some, but not all, databases in a particular instance. I'm not sure if I should use Server Roles for this, since they would seem to apply to all databases, but Database Role membership doesn't seem right either (I don't want the SQL user to potentially 'lose' their resto...
I am trying to delete data from a table using a SQLDataAdapter, and to do so I need to give it a DeleteCommand. The SQL I would use to delete a row is: DELETE FROM table WHERE ID = x The problem is thus: How do I specify to the DataAdapter what to replace x with? The SQL to generate the DataAdapter is slightly different (no joins) th...
I have a sql server database that returns byte for the image. If I use the tableadapter wizard and set it to my stored procedure and preview data, it pulls back an image. It automatically turns it into an image in the preview data. I don't see it as a string of Ints or anything. How can I display it on my asp.net webpage with a gridv...
Is there a rule when we must use the Unicode types? I have seen that most of the European languages (German, Italian, English, ...) are fine in the same database in VARCHAR columns. I am looking for something like: If you have Chinese --> use NVARCHAR If you have German and Arabic --> use NVARCHAR What about the collation of the...
If I have a table containing schedule information that implies particular dates, is there a SQL statement that can be written to convert that information into actual rows, using some sort of CROSS JOIN, perhaps? Consider a payment schedule table with these columns: StartDate - the date the schedule begins (1st payment is due on this d...
When writing TSQL stored procedures I find myself wanting to centralize / normalize some parts of the code. For example, if I have a query like this: SELECT * FROM SomeTable WHERE SomeColumn IN (2, 4, 8) For cases like this I would like to put (2,4,8) in some place outside of the procedure that I could reuse in some other query later ...
I have two identical servers with SQL Server 2005 and my application. Hard requirements: I must be able to update data at either server. I must be able to unplug either server without having to reconfigure anything in the database. When a server is plugged back in, it must automatically sync up with the other server. Notes: I pref...
How do I determine which SQLDBType to assign to a parameter depending on the input variable to store in the DB? Is there a GetType equivelant to test with? If IsNumeric(ParameterValue) Then Parameter.SqlDbType = SqlDbType.Float ElseIf IsDate(ParameterValue) Then Parameter.SqlDbType = SqlDbType.DateTime ElseIf IsArray(ParameterV...
The function Scope_Identity() will provide the last generated primary key value from a table insert. Is there any generally accepted way to get multiple keys from an insertion of a set (an insert resulting from a select query)? ...
I have a web service that's been running fine without modification for a couple of years now. Suddenly today it decides that it would not like to function, and throws a SQL timeout: System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. Inte...
I have a table which has an IDENTITY field and a VARBINARY(MAX) field in it. Is there any way I could dump the content of each VARBINARY(MAX) field to the filesystem (eg. c:\temp\images)? Table schema: PhotoId INTEGER NOT NULL IDENTITY Image VARBINARY(MAX) NOT NULL DateCreated SMALLDATETIME Output: c:\temp\images\1.png c:\temp\image...
I read some Books Online about recovery/backup, one stupid question, if I use full database backup/full recovery model, for backup operation itself, will it generate any additional transaction log to source database server? Will full recovery operation generate additional transaction log to destination database? ...
Hi In a webapp I am building, I have a requirement in which I need to backup and restore around 10-15 tables from a SQL Server database. My solution.. -Backup: For each table, Fill a dataset , serialize that to a file -Restore: Deserialize the file into a dataset and use SQLBulkCopy Is this the best was to achieve my goal? ...
Hello, I have a trigger in a table in SQL Server 2000, this is the trigger: ALTER Trigger [dbo].[Set_Asignado_State] ON [dbo].[Tables] FOR INSERT AS BEGIN DECLARE @area varchar(1) SELECT @area = Table_Area_ID FROM inserted IF (@area = 'L') BEGIN INSERT INTO Table_History SELECT (SELECT TOP 1 Table_Are...
Has anyone ever used Oracle Database Migration Verifier tool (DMV)? It's a tool to analyze the source database and compare it against the migrated Oracle database. I have installed the JDBC driver for Microsoft SQL Server. Then, I have edited the property file and run the command java -jar dmv.jar PropertiesFilePath ReportStorePath, w...
What would be the sql for the following, I have a date of birth in an int field, ie YYYYMMDD = 19600518 I would like to get the age. ...
I want to execute UPDATE command over a group of records, but it fails, because some records after the update would violate an table constraint. Is it possible to update only suitable records somehow? ...
Hi, yesterday I found out that setting a database on auto growth is not a very good thing. Are there other basic 'bad' practices (or best practices) when having SQL Server databases in production? thx, Lieven Cardoen ...
The .NET TimeZoneInfo class is great and I thought it would answer all my issues with recording data from multiple time zones in my SQL 2005 database. To convert a UTC datetime in the database to any other time zone i'd just get the time zone into a TimeZoneInfo class using TimeZoneInfo.FindSystemTimeZoneById() and then call the TimeZon...
[edit] I should note that I need to do this on a live production server. The MS SQL Server is already attached to our website and supports a 3rd party ERP package. I want to set up a separate database that I can code against for some auxiliary pages and internal-use apps) which will be completely independent (mostly for security, but a...