We have an application built on top of SQL server 2005 that we have no control over. We've recently discovered that this application is sending some very inefficient SELECT queries to SQL that is causing serious capacity issues on the database. I know the right solution is to crack open the code for the application and change the queries...
I have a dynamic query in which I want to concatenate uniqueidentifier, but + and & operators are not supporting this, is there a way I can concatenate uniqueidentifier to a dynamic string. Any sample or any help in this regard will be highly appricated. ...
I have the following dynamic query which is working fine without the WHERE clause, which is expecting UNIQUEIDENTIFIER. When I pass it in, I don't get a result. I tried CAST and CONVERT, but no result. I might be doing it wrong, can anybody help? CREATE PROCEDURE [dbo].[sp_Test1] /* 'b0da56dc-fc73-4c0e-85f7-541e3e8f249d' */ ( @p_Creat...
I'd like if its possible to work out from inside sql server how long sql server has been running. Would like to use this in conjunction with one of the DMV's for unused indexes, but the couters are re-set every time sql server loads, so I'd like to know how useful they're going to be. ...
i installed sql server in my home pc for personal use. When i planned to configure sql server through Surface Area configuration, i got one error like this : "Could not load file or assembly 'microsoft.sqlserver.customcontrols, version= 9.0.242..0, culture = neutral, Publickeytocken = 89845dcd8080cc91' or one of its dependancies. The Sys...
Hi Folks, I'm trying to generate some sql that is used to calculate some final scores -> think kids at school and their end of year scores. I was going to have about 5 or so Scalar UDF's that, accept some simple values (eg. current score, subjectid, whatever) and then spit out a decimal value. eg. CREATE FUNCTION [dbo].[GetRatingModi...
I'm trying to switch the current database with a SQL statement. I have tried the following, but all attempts failed: USE @DatabaseName EXEC sp_sqlexec @Sql -- where @Sql = 'USE [' + @DatabaseName + ']' To add a little more detail. EDIT: I would like to perform several things on two separate database, where both are configured with a...
I need to make a column unique in one of our database tables, and we want to completely remove any duplicates from the table. There is however a snag, in that there are a bunch of dependencies to other tables that will be affected. For example, let’s say we’ve got the following relationship: ------------------- ------------------- * ...
SQL Server 2005 - How can I save the results of a query to a text file programatically? (i.e without choosing the Results to file option in Analyzer and not through sql command)? For example select * from northwind.dbo.suppliers And I save my results to CSV file? ...
I''ve a little problem while using SQL Server Full Text Search. Let me explain, I've a table with a BLOB inside (a PDF file). I've created the full text index in that table like it should be. I've the PDF iFilter from Adobe. BUT, when I put some files in my table and execute a search like: SELECT * FROM MyTable WHERE FREETEXT(*, N'...
Hi All, I have SQL server 2008 and it was working fine ,but today while starting SQL server I am getting the following error : "A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server i...
I have a datalogging application (c#/.net) that logs data to a SQLite database. This database is written to constantly while the application is running. It is also possible for the database to be archived and a new database created once the size of the SQLite database reaches a predefined size. I'm writing a web application for reportin...
How do you find current database's transaction level on SQL Server? ...
I have an expensive query using the row_number over() functionality in SQL Server 2005. I return only a sub list of those records as the query is paginated. However, I would like to also return the total number of records, not just the paginated subset. Running the query effectively twice to get the count is out of the question. Sele...
declare @fieldForceCounter as int declare @SaleDate as dateTime declare @RandomNoSeed as decimal set @fieldForceCounter = 1 set @SaleDate = '1 Jan 2009' set @RandomNoSeed = 0.0 WHILE @fieldForceCounter <= 3 BEGIN while @SaleDate <= '1 Dec 2009' begin INSERT INTO MonthlySales(FFCode, SaleDate, SaleValue) VALUES(@fie...
Given a table: create table #orders ( orderid int, orderdatetime datetime ) What is the best way to write sql to output a report containing the count of orders from the current and previous 24 hours, total orders for the current day and previous 7 days, total orders for the current week and previous 4 weeks, and total orders f...
Hi there. I have a table which has no identity column. I want to change a column's identity specification, but SQL Server 2008 doesn't allow that. So, how can I change identity property in SQL Server 2008? ...
I keep getting the following error when I try to register a db server: TITLE: Connect to Server Cannot connect to p3swhsql-v14.shr.phx3.secureserver.net. ADDITIONAL INFORMATION: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that u...
What should be the most recommended datatype for storing an IPv4 address in SQL server? Or maybe someone has already created a user SQL data-type (.Net assembly) for it? I don't need sorting. ...
Guys, This is a two-pronged question: Scenario: I have a script to query MSDB and get me details of job schedules. Obviously, the tables differ from SQL 2000 to SQL 2005. Hence, I want to check the version running on the box and query accordingly. Now the questions: Question 1: This is what I am doing. IF LEFT(CAST(SERVERPROPERTY('P...