I used the standard import / export tool to bring a table into my SQL database. The dates all came over as CHAR types. Now, I keep getting a conversion error stating the CHAR to datetime resulted in an out of range condition. Help please. ...
I have a table/dataset like: 1/1/2009 | Training 1 | Mike 1/1/2009 | Training 1 | Bill 1/1/2009 | Training 1 | Steve I would like to display as 1/1/2009 | Training 1 Mike, Bill, Steve The idea is that the resulting printed page space is not wasted by printing one column on most of the page down but shortening the space to either a...
How do you store a file that was uploaded by an ASP.net webform into a sql server 2005 varbinary(max) field? Here is what I have so far: protected void btnUpload_Click(object sender, EventArgs e) { for (int i = 0; i < Request.Files.Count; i++) { StoreFile(Request.Files[i]); } } private void StoreFile(HttpPostedFile...
Hi guys, following is my stored procedure. It contains a subquery Select StartTime From DrTimings Where DrID = @DrID If this subquery returns more than one value, I get an error. Subquery returns multiple rows. I want to get each @StartTime and @EndTime in cursor. Means I want to "fetch next from Doctor into @StTime and @EndTime" C...
I have a table with a lot of rows. It is indexed. One of the operations that I'm routinely doing is selecting a random record from this table. To do this, I use following SQL statement: SELECT TOP 1 * FROM ( SELECT TOP (@RecNo) * FROM Table ORDER BY Date ASC ) AS subquery1 ORDER BY Date DESC ; Where @RecNo is the random number. ...
I have a recent unfortunate event. I host a business partner's SQLServer 2005 server, and the "sa" password was mysteriously changed (nobody wants to take responsibility on it). So I was wondering, is there a way I can configure SQL Server 2005 to log all password changes? I know that could be achieved with Windows Server 2003, Windows ...
I have the following SQL script: DECLARE @temp table ( ID int IDENTITY(1, 1), data nvarchar(100) ) INSERT INTO @temp (data) VALUES ('a,b,c') INSERT INTO @temp (data) VALUES ('d,e,f') SELECT * FROM @temp AS T INNER JOIN (SELECT * FROM dbo.__StringSplit(T.data, ',', T.ID)) AS S ON T.ID = S.RefID And on ...
I have been asked to perform a performance test using SQL Server 2008. As part of this, I am comparing the speed of IDENTITY columns as PKs using INTs and BIGINTs. I have a simple routine to create 100,000 rows for each type and time the insert speed. The script looks like this: SET NOCOUNT ON CREATE TABLE TestData ( PK INT IDENTI...
I succeed to upload and image and resize it. Now I want to insert my image's name into the database. I'm using SQL Server. The table is named images and has two columns, an integer imageid and imagename as string(invarchar(max)). I need the file name saved in the imagename column and imageid must be an identity. Here's the code I have ...
I'm using SQL Management Studio 2008 Express as a graphic interface to my local SQL Server 2008 Express instance, both of which I have locally only as a test and developement interface for my web projects. I have recently grown more confident in SQL coding, and started to use some more complicated sql stuff - my latest field of explorat...
I have the following SQL statement in a trigger that fires on deletion: UPDATE bk2_InfoPages SET SortOrder = SortOrder - (SELECT COUNT(*) FROM Deleted d WHERE d.SortOrder <= SortOrder) My problem is that the very last SortOrder refers to the Deleted table and not to the bk2_InfoPages table. I am not allowed to add an alias to the bk2_...
Is it possible to access the SQL Server "by-product messages" via ADO.NET? Due to the lack of words, by "by-product messages" I mean the output which appears in the Messages tab in Microsoft SQL Server Management Studio. What I particularly have it mind is to read the output of SET STATISTICS TIME ON. It appears that SqlDataReader does n...
Following on from my last question: I've written some code to upgrade a SQL Server database. Before I upgrade the database, I plan to limit access to the database with the following statement: ALTER DATABASE Test SET SINGLE_USER WITH ROLLBACK IMMEDIATE Before running this code, I'll give the user an opportunity to opt out. At the tim...
I need to change some primary keys from non-clustered to clustered but I can't drop the constraint because it is referenced from other foreign keys. How can I find the tables that reference a primary key in the parent table as part of a foreign relation without looping through all tables in the DB? I need to disable the constraints on t...
Google search turns up some links to tsql questions. I was wondering what would SO experts would ask in an interview for TSQL. ...
How do I get a list of all index & index columns in SQL Server 2005+? The closest I could get is: select s.name, t.name, i.name, c.name from sys.tables t inner join sys.schemas s on t.schema_id = s.schema_id inner join sys.indexes i on i.object_id = t.object_id inner join sys.index_columns ic on ic.object_id = t.object_id inner join s...
My team is evaluating tools and processes for managing database migrations/database refactoring as described by Martin Fowler, Pramod Sadalage, et. al. We're interested in automated, repeatable, testable processes, so we're not interested in techniques like manually running SQL Compare every time we deploy. We're currently using Cruise...
My team is evaluating dbdeploy for managing database migrations. As I understand it, using migrations requires a bit of process discipline, namely that a migration is written for every change, and that to reach production, it would have to be promoted from local to development to test to production. Occasionally our production DBA te...
Im trying to work out the best way scale my site, and i have a question on how mssql will scale. The way the table currently is: cache_id - int - identifier cache_name - nvchar 256 - Used for lookup along with event_id cache_event_id - int - Basicly a way of grouping cache_creation_date - datetime cache_data - varbinary(MA...
I have triggers that manipulate and insert a lot of data into a Change tracking table for audit purposes on every insert, update and delete. This trigger does its job very well, in other words, we are able to log the desired oldvalues/newvalues as per the business requirements for every transaction. However in some cases where the sou...