We are using OleDbConnection in a C# legacy application to connecto to a SQL Server 2000 where the connect string contains "Connect Timeout=5;" and we have the CommandTimeout set to 30 seconds. Yet when we run a command (a few inserts in a transaction) when the server is slow the query times out after only 10 seconds. System.Data.S...
I'm using MS SQL Server, and I'd like to alter a view from within a stored procedure, by executing something like "alter view VIEWNAME as ([some sql])". A few pages thrown up by google assert that this is not supported directly (and neither are related alter-table statements), but there are also examples of how to work around it using c...
I have a T-SQL query that is causing performance issues. Its a chunky one but the part that seems to be causing an issue is a simple LEFT JOIN. This can be resolved by removing the left join and using a subquery in the select, however this seems unsatisfactory to me as I can't see why one works quickly and not the other. Theres not a ...
As the question title, is there a way to do this? eg: DrivingLicence --------------- CanDriveCar (bool) CanDriveMotorbike (bool) CanDriveBus (bool) You can't drive a bus without a car licence, so I want the DB to throw an exception if anyone tries to give someone a bus licence when they haven't a car licence. ...
I have a table like this named "Days" Day 1 Day 2 Day 3 Day 4 Day 5 Day 6 Day 7 1 1 1 1 0 0 0 1 1 1 1 0 0 0 which has the corresponding details. Where 1 is 'true' and 0 is false. DaY 1 - sunday day 2- monday day 3 - tuesday day 4- w...
I have a table with 2 primary keys (int, int) and was wondering if it was possible to have one of the primary keys set to auto increment and the other not? The following is the basics of the table structure: Table { Id - Int, VersionId - Int } Basically I have an Id with a Version so that there is a complete history of that reco...
If I have two trivial stored procedures, created like so: create procedure root as select 1 go create procedure dependant as exec root go (Where dependant depends on root). When I check the sys.sql_dependencies table for the second procedure, I see an entry (as i would expect). If, however, I add the dependant procedure first, I ...
I have a console app that needs to connect to a remote sql server 2008 instance.... this particular line throws an error even though i have access to that database... connection.open() is the line that is throwing the error... ...
I beg you don't ask me why am I using SQL Server 6.5 There is no SELECT TOP command in SQL Server 6.5, and guess what, I need it :) I need to perform something like Select top 1 * from persons where name ='Mike' order by id desc I've tried something with SET ROWCOUNT 1, but in that case you cannot use order by. I end up with Sele...
I am tinkering with creating a query to find missing indexes. I've taken a base query created by the Red-Gate folks in their SQL Server DMV Starter Pack eBook and am modifying it a bit. There are a couple columns in sys.dm_db_missing_index_group_stats which I don't know how to interpret. They are: avg_total_user_cost avg_user_impact A...
Thanks everyone for the feedback. This is a favorite. Hi All, Log Table Id, Message, Type 1, John Doe 2, Jane Smith 3, Error 4, Jane Smith Is there a way to get the error record and the surrounding records? Find all Errors and the record before and after them. Thanks, rod. ...
Please suggest me a query to find temp database tables. ...
Hi, all, I am trying to execute an insert command into the database and one of the columns is of type nvarchar(MAX). Insert command is created by using .NET SqlCommand class and each of the parameters is represented by one SqlParameter object. My command gets executed always, but when I pass string which length is large (10000+ charac...
I'm sure this is a stupid n00b question, but I have the following results table (which is created through a long-winded query I only half understand) and what I'd like is to be able to add in two columns that work out the row percentage relative to the total: Sample results table: CREATE TABLE #temp (category varchar(30) ,count_people ...
I want to make a field, which will be automatically filled, and stay unique. More explanation: I have an ID field, which should be filled by my program not the user, and is my primary key. How may I do it in SQL Server? I'm using Visual Studio 2010 ...
I’ve got a page in my ASP.Net application that is used to search a database via a store procedure. One of the parameters passed to the stored produced is bound to a text box via a ControlParameter binding. <asp:ControlParameter Name="EventDescription" Type="String" ControlID="ucTestLogSearch" PropertyName="EventDescription" /> The te...
I have two tables and I'm looking for the rows in one table where a time column is not near any of the values in another table's time column. (Near is defined as within a minute). Here's a code sample: create table temp1 ( id int identity primary key, value datetime not null ) GO create index ix_temp1 on temp1(value, id); GO ...
I have a problem with regional string characters inserted to MS SQL Server database. There is a PHP application that connects with mssql server and inserts some data. But instead of inserting characters such as: ą, Ą, ć, Ł, ź (and so on - btw. these are polish regional characters), when inserted into mssql table they appear as a, A, c,...
Hello! I have a PHP application currently connectig to a SQL Server via ODBC using Windows Authentication (odbc_connect). Under Apache this works fine. However I need to run this under PHP-IIS. While I can run PHP under IIS, and get this very same application connect to SQL Server when I run it from Apache, trying to run via IIS gives...
Hi, I want to retrieve a list of all schemas in a given Sql Server database. Using the ADO.NET schema retrieval API I get a list of all collections but there is no collection for 'Schemas'. I could traverse the 'Tables', 'Procedures' collections (and others if required) and obtain a list of unique schema names but isn't there a easier/s...