I inherited a system that stores default values for some fields in some tables in the database. These default values are used in the application to prepopulate control values. So, essentially, every field in every table in the database can potentially have a default value. The previous developer decided to store these values in a single ...
Hi all, I'm currently benchmarking partitioned tables with SQL Server 2005 to compare them to using two tables (a "live" table and an "archive" table) for a processing queue. The partitioning's being performed on a bit column 'archive', so that when the archive bit is set, the row automagically moves. Initial testing seems to show that...
Following podcast #59, I've been considering moving some images from an MS Access DB into MS SQL Server (it's the only piece I haven't migrate over yet). Right now they are stored as OLE Objects in the MS Access DB. However, I haven't got a clue what data type they should be stored as on the SQL Server side, or how to get them inserted ...
How critical are transaction logs after a full backup of a SQL2005 database? Example: I have a database called Test and I don't care about point in time recovery using transaction logs except that I might want to revert back to the database version taken at the time of the last FULL backup. Now, in the backups directory I have a FULL b...
I can use the PRINT statement in a stored procedure to debug my code. I see the output in the Messages tab of SQL Server Management Studio. How can I dump one or more entire SELECT statement outputs to that Messages tab? My stored procedure returns several output variables so returning a single dataset isn't an option here. I am strug...
I've got a large table in a SQL Server 2005 database and I'd like to copy it over to another database. What's the fastest way to do this? Is there a shortcut to linking servers and doing inserts? ...
Out of curiousity, is there any way to edit an existing synonym? That is, change which table the synonym is pointing to... Thus far I seem to have had to delete and re-create them, because they're locked from being edited. It's not a big deal, but at the same time it's a little irritating. GUI or scripting, but preferably GUI. ...
DECLARE @EmailEnc varbinary(maX) declare @keyid uniqueidentifier DECLARE @Email_test nvarchar(max) set @Email_test = N'Sg@hotmail' SELECT top 1 @keyid=[key_guid] FROM sys.symmetric_keys order by newid() SET @EmailEnc=CAST(ENCRYPTBYKEY(@keyid,@Email_test) AS varbinary(max)) select @Email_test,@keyid,@EmailEnc Results Sg@hotmail ...
Hi! I'm looking for a tool (best for free) which can synchronize two SQL Server 2005 databases. I have a sql database that is hosted on a server and another sql database that is hosted on aclient. Both have the same database schema. Each client has its own sql server because the application needs to run offline. The client is being co...
I was just prototyping a new system for deferring certain operations until out of hours on one of our databases. I've come up with (what I think) a pretty simple schema. I was first prototyping on SQL Server 2005 Express, but have confirmed the same problem on 2008 Developer. The error I'm getting is: Msg 8646, Level 21, State 1, Pro...
SQL Server 2008 Profiler always profiles all databases. I want to restrict profiling or output to a single database. How can that be done most easily? ...
I have a serious problem. One of our servers crashed. Now I reinstalled the server from scratch and want to restore the sql server 2005 databased. I chose "Databases -> Attach" to do this, which worked for most of my db files. However, with one mdf file I get a error message during recover: The error message is in german, I haven't tran...
Hi, I have a problem which I already solved using T-SQL(cursor & loop).(SQL server 2005) But I am looking for the solution using SQL. I have a Master Table with a column say MasterRecord(all are unique and type Varchar and PK) MasterRecord MRecord1 MRecord2 MRecord3 MRecord4 ................ .................. MRecord[n] Now...
Below is the code snippet with comments which describes the problem statement. We have an update trigger which internally calls another update trigger on the same table inspite of Recursive Trigger Enabled Property Set to false. Would like to understand the reason for this as this is causing a havoc in my applications. /* Drop statemen...
I've got a rather large SQL Server 2005 database that is under constant development. Every so often, I either get a new developer or need to deploy wide-scale schema changes to the production server. My main concern is deploying schema + data updates to developer machines from the "master" development copy. Is there some built-in funct...
I have a table of categories. Each category can either be a root level category (parent is NULL), or have a parent which is a root level category. There can't be more than one level of nesting. I have the following table structure: Is there any way I could use a query which produced the following output: Free Stuff Hardware Movies ...
I would like to develop a Forum from scratch, with special needs and customization. I would like to prepare my forum for intensive usage and wondering how to cache things like User posts count and User replies count. Having only three tables, tblForum, tblForumTopics, tblForumReplies, what is the best approach of cache the User topics ...
In an Access Project (ADP), there is a macro set up the calls a stored procedure on a SQL Server 2005 database (which updates a table, but does not return any results). This macro is called when a user clicks on a button in a form. It works when logged in as the database owner and the ADP is opened, but when opened by a user with more r...
I'm using Business Objects Collections. (Not using datasets. Generics collections only.) Collections of Business objects are filled using SQL Reader I'd like to know your opinion what is best approach to fill master details (or parent-child) collections Assume I have 2 objects: Invoice and Invoice_Details Invoice Object has generic col...
In a normal SQL Server 2005 connection string, it's possible to specify the desired protocol in the following format: Data Source=tcp:myServerAddress; Initial Catalog=myDataBase; Integrated Security=SSPI; Is there a way to do something similar in a JDBC connection string to SQL Server? Edit: My JDBC Connection String looks like this...