I have 2 tables: #tmptable1 and #tmptable2. These tables have an ID to uniquely identify each record. I want to return all records from table 1, but I only want to return the records from table 2 that aren't in table 1 I want this done in 1 query. Thanks! ...
Is it possible to set up SQL Server to give developers read-only access to the contents of stored procedures on our production database? ...
I have a column in the database (SQL Server 2005) that has data with a "\0" at the end. When querying in SQL Server, this character is not visible and does not "seem" to exist. When I look in my C# code, the character is there. This character is causing an error on our website, and we need it removed from all the affected rows. Is th...
I have a string that always reads as follows: re-assigning LastName, FirstName re-assigned the order to LastName, FirstName (Administrator) Reason Code: Reassign order Comment: The name is always diffent but I only want to return the LastName, Firstname part on the first occurence so right after re-assigning. The spaces and every...
How could I make this query better? SELECT ClientID, BatchID, jobid, subjobid, count(clientid) as Total FROM data with(nolock) WHERE batchid in (select BatchID from data with(nolock) where lookupcode = '111111111111') and clientid in (select ClientID from data with(nolock) where lookupcode = '111111111111') and jobid in (select jobi...
I have a datatable in memory that I got from a non SQL source. What is the most elegant way, using ADO.NET to push it "as is into a new SQL (2005) server table? ...
Someone please change my title to better reflect what I am trying to ask. I have a table like Table(id,value,value_type,data) ID is NOT unique. There is no unique key. value_type has two types. lets say A and B. Type B is better than A, but often not available. For each id if any records with value_type B exsits I want all the reco...
Hi, Suppose I have a table a with one column b and three rows(1,2,3), I would like to create a function that will return '1,2,3' that would be called like this : SELECT FUNC(f), ... FROM ... In other words, I have a linked table that have more than one rows linked to each rows of the first table and would like to concatenate the conten...
My function ALTER FUNCTION GetProductCount (@CatID int) RETURNS int AS BEGIN DECLARE @return_value int EXEC @return_value = [dbo].[USP_Products_GetList] @ProductName = NULL, @ProductID = NULL, @Description = NULL, @CatID = @CatID, @CatName = NULL, @Price1 = NULL, @Price2 = NU...
I have a query that I cannot get to work right. I have 3 tables; Person, PersonProgram and Category. Person: ID, .... PersonProgram: ID, PersonID, Category, Code ... Category: ID, ... The Person table has 1 record for each person and the PersonProgram has multiple programs per person. There are 4 categories and I need to pull ...
I am using vb.net and SQL server 2005 I have a project with database classes which are generated from database objects. That is I add a dataset to the project and drag and drop a database object onto it. The problem with this is that I have to give a connectionstring which is stored in the code. This is ok while developing the applicati...
I have a table that defines a heirarchy: Create Table [example] ( id Integer Not Null Primary Key, parentID Integer Null, largeData1 nVarChar(max) Null, largeData2 nVarChar(max) Null); -- largeData3...n also exist Insert Into [example] (id, parentID, largeData1, largeData2) Select 1, null, 'bla...
I've backup from a sqlserver created with this procedure: protected void WriteXML(string tableName) { using (SqlConnection cnn = new SqlConnection(ConnectionString)) { using (SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM [" + tableName + "];", cnn)) { cnn.Open(); DataSet ds...
In SQL Server Management Studio, there is an option to set the default database path for a given instance: This works. When I say CREATE DATABASE test it gets created in the path I specify, E:\data. Great. But how do I get this path in T-SQL (for use in setup scripts)? Contrary to what many pages say, there is no registry key (...
I accidentaly ran a DELETE command against a table with a wrong WHERE clause. I am using SQL Server 2005. Is there a way, or any utility software that could help me recover the lost data? ...
I have log4net configured and working fine on my local machine, however when I deploy to my host (godaddy) it fails silently. I am using the same database/config file on my dev machine, and on the host. My log4net reference is set to copy local, and the log4net.dll, .pdb, and .xml exist in the bin on the host. This is an asp.net mvc app....
Hello all, What's the right way to compare NTEXT column with a constant value? If I use something like [ntext2] <> '1,032.5', I get this error: The data types ntext and varchar are incompatible in the not equal to operator. The best possible solution would be if comparison is implemented in the same way for any column type. (<...
Hello everyone i want search data from invoices and client by today date I'm using DateDiff() GETDATE() functions for example two tables 1 Client - ID int - Name Varcher 2 Invoice - ID int - ClientID int - date Datetime - Total money query Select * from client c inner join invoice i on c.id = i.ClientID WHERE DateD...
I've got a dev database that is a copy of live 30gb, we have deleted some data that is not needed in dev that brings the data file used to 20gb. Its SQL server 2005 So we have about 33% unused. I need to reclaim the space which will allow us to have a second dev DB on the server (based on the cut down version) However I cannot reclai...
How do you get a list of the input parameters for a stored procedure using metadata in SQL Server 2005? I want to do a SELECt on it and then iterate through the variable names. ...