I have seen the references to VistaDB over the years and with tools like SQLite, Firebird, MS SQL et. al. I have never had a reason to consider it. What are the benefits of paying for VistaDB vs using another technology? Things I have thought of: 1. Compact Framework Support. SQLite+MSSQL support the CF. 2. Need migration path to a 'm...
Hi folks - I'm just wondering if there's a better way of doing this in SQL Server 2005. Effectively, I'm taking an originator_id (a number between 0 and 99) and a 'next_element' (it's really just a sequential counter between 1 and 999,999). We are trying to create a 6-character 'code' from them. The originator_id is multiplied up by a...
As part of my integration strategy, I have a few SQL scripts that run in order to update the database. The first thing all of these scripts do is check to see if they need to run, e.g.: if @version <> @expects begin declare @error varchar(100); set @error = 'Invalid version. Your version is ' + convert(varchar, @version) +...
The default asp.net membership provider uses a .mdf sql server database file in the app_code database. How scalable is this in terms of calling a flat file database instead of running it in a standard sql environment? Is this recommended only for small/medium traffic sites? Thanks. ...
I'm not a SQL expert, and I'm reminded of the fact every time I need to do something beyond the basics. I have a test database that is not large in size, but the transaction log definitely is. How do I clear out the transaction log? ...
I'm storing a Word document in a SQL Server 2005 database in a varbinary(max) column. Is there a way to open this document from a VB.NET Windows Forms application without serialising to a file first (i.e. directly from the byte array I've read from the database)? ...
Just what the title says, I need to change the password for an existing sql server login and I want to do it via sql script. ...
We all know T-SQL's string manipulation capabilities sometimes leaves much to be desired... I have a numeric field that needs to be output in T-SQL as a right-aligned text column. Example: Value ---------- 143.55 3532.13 1.75 How would you go about that? A good solution ought to be clear and compact, but remember there is s...
I have two identical tables and need to copy rows from table to another. What is the best way to do that? (I need to programmatically copy just a few rows, I don't need to use the bulk copy utility). Thanks rp ...
I'm familiar with SQL Server Indexed Views (or Oracle Materialized Views), we use them in our OLAP applications. They have the really cool feature of being able to usurp an execution plan and remap it to the indexed view w/out having to change existing code. IE. Let's say I had a SPROC that was a really expensive join. SELECT [SOME ...
I'm a bit confused here. Microsoft as far as I can tell claims that parametrization is the best way to protect your database from SQL injection attacks. But I find two conflicting sources of information here: This page says to use the ADO command object. But this page says that the command object isn't safe for scripting. I seem to ...
This one will take some explaining. What I've done is create a specific custom message queue in SQL Server 2005. I have a table with messages that contain timestamps for both acknowledgment and completion. The stored procedure that callers execute to obtain the next message in their queue also acknowledges the message. So far so good. We...
I Have a request for the TOTAL's and subtotals column to be moved to the top/left of columns it represents, and by default SSRS does it on the bottom or right hand side of the columns being totaled. Is there a way to this? Thanks! ...
We have a whole bunch fo queries that "search" for clients, customers, etc. You can search by firstname, email, etc. We're using LIKE statements in the following manner: select * from customer where fname like '%someName%' Does full-text indexing help in the scenario? Edit: I failed to mention we're using MSSQL 2005. ...
I knew of some performance reasons back in the SQL 7 days, but do the same issues still exist in SQL Server 2005? If I have a resultset in a stored procedure that I want to act upon individually, are cursors still a bad choice? If so, why? ...
My current view is no, prefer Transact SQL stored procedures because they are a lighter weight and (possibly) higher performing option, while CLR procedures allow developers to get up to all sorts of mischief. However recently I have needed to debug some very poorly written TSQL stored procs. As usual I found many of the problems due t...
How can I have SQL repeat some set-based operation an arbitrary number of times without looping? How can I have SQL perform an operation against a range of numbers? I'm basically looking for a way to do a set-based for loop. I know I can just create a small table with integers in it, say from 1 to 1000 and then use it for range operation...
How do you randomly select a table row in T-SQL based on an applied weight for all candidate rows? For example, I have a set of rows in a table weighted at 50, 25, and 25 (which adds up to 100 but does not need to), and I want to select one of them randomly with a statistical outcome equivalent to the respective weight. ...
I'm using ADO.NET to access SQL Server 2005 and would like to be able to log from inside the T-SQL stored procedures that I'm calling. Is that somehow possible? I'm unable to see output from the 'print'-statement when using ADO.NET and since I want to use logging just for debuging the ideal solution would be to emit messages to DebugVie...
Suppose I have a table called Companies that has a DepartmentID column. There's also a Departaments table that has as EmployeeID column. Of course I have an Employee table as well. The problem is that I want to delete a company, so first i have to delete all the employees for every departament and then all the departaments in the company...