CREATE VIEW View1 AS SELECT Col1, Col2, dbo.fn1(col2) as Col3 FROM TestTable /* --Instead of writing below query I created a new View View2 SELECT Col1, Col2, dbo.fn1(col2) as Col3 dbo.fn2(dbo.fn1(col2)) as Col4 FROM TestTable */ CREATE VIEW View2 AS SELECT Col1, ...
Hi I've recently started using Visual Studio 2008 and SQL Server Express. I'm coming from a Emacs/Django background, so I'm not used to this way of working. Is there any quick and easy way to set up a many-to-many-table between two other tables? This would be the equivalent to the table generated by a ManyToManyField in Django. ...
Hi, I have a question related to T-SQL and SQL Server. Let's say I have a table Orders with 2 columns: ProductId int CustomerId int Date datetime I want the date of the first order for every product, so I perform this type of query: SELECT ProductId, MIN(Date) AS FirstOrder FROM Orders GROUP BY ProductId I have an index on Produc...
So I know this is a pretty dumb question, however (as the rather lengthily title says) I would like to know how do the following: I have a table like this: ID Foo Bar Blagh ---------------- 1 10 20 30 2 10 5 1 3 20 50 40 4 20 75 12 I want to group by Foo, then pull out rows with minimum Bar, i.e. I want the following: I...
I'm trying to restart the service from a remote machine. Here is my code. The problem is that I need to enter startinfo.filename = "sc.exe" since I'm putting "start /wait sc" this is causing an error. Here is my code, any thoughts. Also if anyone has any idea how to keep the cmd window open after this is ran so I could see the code that ...
I'd like to clarify what the optimal maintenance plan set up is for the ASPState database that is used by ASP.Net SQL Server session state. The standard user database maintenance plan doesn't seem suitable. I'll never need to do a point-in-time restore of session data so it seems pointless to back up the database and transaction log fi...
I have a script with an Alter Column statement that's changing the data type from a Bigint to a Varchar. Immediately afterwards, I need to call Substring() on that column. Unfortunately, the syntax checker of SQL Server won't let me do this. I need to add a 'GO' statement to split the commands. The problem with this is, I also need to i...
Is it possible to insert a file in a varbinary(max) column in Transact-SQL? If yes, I would be very please to have a code snippet to at least give me an idea how to do that. Thanks ...
Hi there, can anyone suggest the best way to serialize data (a class actually) to a DB? I am using SQL server 2008 but i presume i need to serialize the class to a string / or other data type before storing in the database? I presume that this field needs to be text or binary?? Does SQL server 2008 (or .net 3.5) support serializing d...
I have three tables - one for shipping rates, one for products and one for exceptions to shipping rate for particular products. Shipping is charged as follows: Each product has a shipping price, but this price can be overridden by an exception. If no exception exists for a product, the default rate is used for the shipping rate selected ...
I'm looking to return the row with biggest create_dt. This works fine, however I would like to know if there is a more proper way of doing this? select * from table1 where job_no='101047' and create_dt in (select max(create_dt) from table1 where job_no='101047') ...
Hello, we have a DAL that needs to wrap many database inserts in a single transaction that we can rollback or commit. What is the best practice for handling that? We are currently doing the following: Create DB Connection and Transaction Fill a collection with all the classes that represent an action to perform. Pass in the conn...
We store documents in an SQL Server 2005 database table with a column format of "Image". Every time I try to store a PDF file which is greater than 1 Meg, it somehow gets corrupted. Is there any particularly efficient method in .NET to serialize and store large files (~10megs) into a database? [Edit] Microsoft actually says the max f...
The title says it all. I'd like to sort the rows returned by an UPDATE statement, but there's no direct way to do this according to the msdn page for the OUTPUT Clause, which says: SQL Server does not guarantee the order in which rows are processed and returned by DML statements using the OUTPUT clause. Since I can't just tack "or...
If this question is too broad, my apologies. I am a DBA and I am working with a developer that thinks column defaults are a bad idea and that just setting the column to disallow nulls is good enough. I am looking for the benefits of column defaults from the developers point of view. Thank you for your comments. ...
For the warehouse work under progress, we have a single solution with multiple projects in it OLTP Database Project Warehouse Database Project SSIS ETL project After the SSIS project is built, I want to move the binaries (XML, really) from the Bin folder to "C:\AutomatedTasks\ETL.Warehouse\" and "C:\AutomatedTasks\ETL" I cannot find...
Given a database schema with a parent table and two or more child tables. For example: Is it possible to create a query, using the for xml statement, that outputs the following XML: <?xml version="1.0"?> <person> <name>Joe Bloggs</name> <age>25</age> <address> <streetAddress>123 Test Street</streetAddress> ...
Hi I have a table like this: tblMembers ID Credit Is_Member --- --- --- 1 45 True 2 20 False 3 25 True 4 -10 True 5 -5 False 6 13 True How can I create a view showing the record with the (Minimum Positive) value of Credit and Is_Member="True"? Thanks! ...
I am curious to know why should a Database person learn LINQ. How can it be useful. ...
I have several pretty large databases located in a SQL 7 box. Whats the best way to get them into SQL 2005 or 2008? As far as I know, there were changes to the underlying file structures so I am not sure that a simple detach/attach is advisable. Failing that, are there any good options? ...