I've got a .Net web system with a VB.Net front end talking to a SQL Server 2005 back end over ADO.Net. Essentially, what I want to do is this: Dim command As SqlCommand = New SqlCommand("", connection) command.CommandText = "SELECT * FROM someTable ORDER BY orderValue @SortOrder" Dim sortParam As SqlParameter = New SqlParameter("@SortO...
Question Hello All, I've had some confusion for quite some time with essentially flooring a DateTime SQL type using T-SQL. Essentially, I want to take a DateTime value of say 2008-12-1 14:30:12 and make it 2008-12-1 00:00:00. Alot of the queries we run for reports use a date value in the WHERE clause, but I either have a start and en...
I've got a problem on an SQL Server 2005 Express instance, and I need to see exactly what is being run, since what I believe is being run works fine when I run it from Management Studio. It appears that Profiler is not available for SQL Server 2005 Express. When I attempt to run SQL Server 2000 Profiler against the 2005 Express edition...
Duplicate: http://stackoverflow.com/questions/263836/generating-sql-schema-from-xml In a project i am working on, i have a need to support either a strongly-typed dataset for storing the data as XML, or storing the data in sql server. Now i already have the XSD schema created and i would like to be able to create a sql server database...
Any pointers on how I can programmatically get exactly the identical stored procedure source from SQL Server 2005, as when I right-click on that stored procedure in SQL Server Management Studio and select modify? I'm trying using SMO, but there are some textual differences. The procedure always has CREATE, not ALTER, and there are some...
Today again, I have a MAJOR issue with what appears to be parameter sniffing in SQL Server 2005. I have a query comparing some results with known good results. I added a column to the results and the known good results, so that each month, I can load a new months results in both sides and compare only the current month. The new column...
I've been doing a little work with SQL Server and C# and every time I run the darn thing the connection times out unless I start services.msc and manually restart the service (SQL Browser Service). It's already set to automatic, does anyone know why it would do this? I'm not an advanced programmer, more of an intermediate one. Once I do ...
What is the proper way using .NET to discover the Microsoft SQL Servers on your internal network? I think the SQL Management interface back in the SQL Server 7 days gave you a list of servers in a dropdown. How could I do the same thing with .NET 3.5. ...
Has someone experience with database comparison tools? Which one you would recommend? We are currently using "SQLCompare" from Redgate, but I am curious to know if there are better tools on the market. The main requirement is that they should be able to compare scripts folder against a live database. Thanks, Dimi ...
Hello all, I've currently got a database with about 20 reference tables, i.e. stuff like products, assets, depots, users, etc. This information is stored in a central database and is downloaded to PDAs of engineers who are out on the road. Every table in my database has a PK of UniqueIdentifier (i.e. a GUID). I've realised after 2 year...
How to create a Stored procedure (SQL 2005/2000) to swap 2 records excluding few columns, it has to get "table name", "record id (2 id)" and "column to exclude" as a parameter. Here columns are dynamic as we get 'table' as a parameter and number of columns may be more than 50. Here I believe, "update [table name] set [field] = [value...
Hi, We have runned into a problem with an sp. We have a pretty simple sp containing a declared table and a couple of outer joins that in the end returns between 20 and 100 rows. Since querying this sp has been giving us poor performance both in production and in testenvironment we recently rewrote it to be more efficient and has teste...
I use a view based on a complex query with 17 joins (both inner and left/right outer) and subqueries. All view rows are shown in about 5 seconds. SELECT * FROM a_view; One of the view columns has type BIT. And when I filter view rows comparing it with 1, a query works again about 5 seconds. SELECT * FROM a_view WHERE c = 1; But wh...
SQL Server 2005 has great sys.XXX views on the system catalog which I use frequently. What stumbles me is this: why is there a "sys.procedures" view to see info about your stored procedures, but there is no "sys.functions" view to see the same for your stored functions? Doesn't anybody use stored functions? I find them very handy for e...
How to deploy Sql server reports on productions boxes? Locally it's not a problem, I just specify the url and then right click on project and say deploy which deploy on my local server. But it's not gonna be the case for the production server ...
Hi I have been wondering how to put all the stored procedures on a SQL 2000 under source control. We are using Subversion for all our normal source code, so it would be great if there were a solution to the problem using Subversion. Do you have any ideas? Update 16-02-2009: This is the vbs script i used to export all the stored proce...
We have an application written in C#, using .NET Framework 3.0 or 3.5 or something like that. As storage we use SQL Server, and we use Linq 2 SQL to talk with it. Currently most (if not all) text columns in the database, are set to the varchar type (of different lengths of course). But I started thinking... according to MSDN "The stri...
Main problem is about changing the index of rows to 1,2,3.. where contact-id and type is the same. but all columns can contain exactly the same data because of some ex-employee messed up and update all rows by contact-id and type. somehow there are rows that aren't messed but index rows are same. It is total chaos. I tried to use an in...
I have a stored procedure that ideally should be able to accept a list/table of NVARCHARs from the database client. I'm aware of table parameters in SQL Server 2008 but I'm stuck with running SQL Server 2003. Currently, I'm concatenating the strings with a separator character on the client side, passing the resulting string as a NVARCH...
There were a number of questions related to sharing data results of one stored procedure in another in MS SqlServer. Depending of the version of SQL Server people would suggest using temporary tables, xml (SQLServer 2005) or table variables (SQL Server 2008). There is a great article written by Erland Sommarskog that provides comprehe...