hi, I want to run a update query in a remote machine running sql 2005. how to do that, use isql like sql 2000 or other good ways? Thanks. ...more >>

For dozens of entities, I have a Select (returns one record with @ID param) and a SelectList (returns all records.. no param passed) stored procedure. E.g... CREATE PROCEDURE dbo.SelectEmployeeList AS SELECT * FROM Employee CREATE PROCEDURE dbo.SelectEmployee @EmployeeID uniqueidentifi...more >>

I am trying to reconcile some data across two SQL servers. The tables being queried in each server are supposed to be in sync as regards invoice numbers and amounts. I have querys A, B & C A) select sum(amount) from [ANOTHER].[SQLSERVER].[dbo].[Paid Billing] where [date applied] >= '2007...more >>

Using sql 2005 I would like to create a listing of all the tables, column names, and column types in a database. It does not look like I can build a view using the system tables any more (as was done in 2000) ? Thanks ! ...more >>

Hi, Say I have this data: id name null John null Joe null Jack Is there a simple sql i can use in my update statement? No procedure, sequence object, etc. Just sql. Tried below but didn't work: update table set ID=(SELECT COALESCE(ID),0)+1 FROM table) Any input appr...more >>

I'd like to do something like the below (that would eventually be in a proc) but with null instead of 0. I'm not sure why replacing 0 with null doesn't work. Also, I'm thinking I should build with dynamic SQL or something so I don't duplicate stuff I don't need to? DECLARE @ID int SET @I...more >>

I'm looking to have a database at a central location be in sync with another database at another location over the internet. I want to be able to have the data at the central office mirror the other location in as close to real time as possible, preferably they wouldn't be out of sync by more th...more >>

This is a hard one to find online because searching for WITH isn't exactly easy. Does anyone have an explanation or a reference that may explain: 1) What the WITH statement really does 2) What the pros and cons are of using it versus temp table syntax examples: WITH TempTable AS (Sel...more >>

CREATE TABLE [dbo].[Category]( [CatID] [smallint] NOT NULL, [CatDesc] [nvarchar](16) NULL CONSTRAINT [Category$PrimaryKey] PRIMARY KEY CLUSTERED ( [CatID] ASC )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] insert into dbo.Category values('1', ...more >>

I need to change the datatype of a column from int to bigint. The column is the identity column of the table and the table does have about 3 million records in it. How can I do this? I'm trying to use the following statement but keep getting an error message, saying "Incorrect syntax ne...more >>

Here's an interesting problem that I'm trying to find a set-based solution for. Let's say I have two tables: CREATE TABLE OrderDetail (OrderNum INT NOT NULL, OrderLine INT NOT NULL, ItemID INT NOT NULL, Quantity INT NOT NULL, PRIMARY KEY (OrderNum, OrderLine)) CREATE TABLE Inventory...more >>

How can I find a number in a string? For example, myTable.code equals "AB99". I would need to update myTable.Alpha to "AB" and myTable.Num would be updated to "99". Is this possible? Thanks. ...more >>

I had created about 30 new stored procedures on my development machine (i am using SQL Server 2005). In sql server 2000, you were able to select multiple stored procedures and generate sql scripts and with one swoop, you were able to transfer stored procedures in the production box. It seems...more >>

I have a table similar to the one shown here: Counter Q1 Q2 Q3 Created ----------- ------- ----- ------ -------------- 1 1 2 3 5/1/2007 2 2 3 4 5/2/2007 3 3 4 5 5/3/2007 ...more >>

I need to select entire rows from the dataset below where each recID group contains at least one row where NR = 'n'. In the group of rows for recID = 345, none of the rows from this group contains NR = 'n', so I need to exclude the rows from this group. What query will retrieve the desired ...more >>

Hi Table T_VesselRoute { VR_VesselTrip, VR_DateTime, VR_Distance } -- shortened of course For Each { TODAY, TODAY+1, .. TODAY+6, REST } need COUNT(VR_VesselTrip), COUNT(VR_DateTime), SUM(VR_Distance) I have attempted the following solution. I'm a SQL newbie and was wondering if th...more >>

Being a very newbie I have some questions regarding the select statement with % . As we know that the % is used for getting approximated values in the select statement. I wonder how I should use the % mark if I am going to grab column values with % themselves. For example, the NAME column i...more >>

Hello Everyone, I am trying to replace non-ASCII characters with a '' in a text column. I wrote a stored procedure to do this, using the following code. begin if unicode(substring(@old_string, @count, 1)) < 127 begin set @new_string =3D @new_string+substring(@old_string, @coun...more >>

How can I obtain a strored procedure or a trigger text in SQL Server 2000? I'm pretty sure that the code should be stored in a memo somewhere in a system table, right? The reason for this is that I want to "compare" the code of my stored procedures and triggers with the code it should cont...more >>

Hello. I'm inserting rows into a table. Depending on the value of Table1.CodeType I need to Inner Join to another table. If Table1.CodeType = "A" and "B" I need to Inner Join to another table. If Table1.CodeType = "C" then the Inner Join is not required. Rather than doing two inserts, one w...more >>

I have a table of Sales statistics as follows, with each record bearing invoiceID, salesRep who contributed towards the sales, the SalesDepartment of the salesRep and the SalesAmount the SalesRep contributed Create Table sales (invoiceID INT NOT NULL, salesRepID INT NOT NULL, Sales...more >>

In a SQL 2000 database the following BCP command runs successfully. It = essentially reads from the ELIGDATA1 table in a database named GW301 and = puts all of the data from that table into a file named eligdata1.txt. EXECUTE master..xp_cmdshell "BCP GW301..ELIGDATA1 OUT = F:\Data\eligdata1.tx...more >>

Hi, I'm logging some production totals every day in a table. I've managed to use the query below to give me monthly totals how can adapt it to give me weekly totals. Unforunately there doesn't seem to be a week function? SELECT MONTH(Date) AS Month, SUM(Total3) AS Production FROM ...more >>

Hi can anyone put me right here I would like to open an access 2003 database by its date using sql. I have a table created each day and renamed by that days date. this is what i have so far but it does not work. please help Call BarGraphSQLData( _ "SELECT id, DataReading FROM DAY(CURR...more >>

Hello world, I have a proble with a stored procedure I'm working on. I am selecting data from 2 different views which stores invoice data. The select statement is joining the 2 view by Invoice_NO (INNER JOIN). the stored procecude has some ackward results. Every x number of displays the ...more >>

select datepart(mm,CAST(getdate() as varchar)) is 5 How can I get 'May'...more >>

The Transact-SQL Reference for OBJECT_ID Returns the database object identification number. What are the Consequences of Using Reserved Keyword OBJECT_ID as a column name in SQL Server 2000? Has anybody out there ran into any problems where somebody named a column OBJECT_ID? If so woul...more >>

Hello folks. I really have tried searching on this, but couldn't find an exact scenario that corresponds to my situation. I've got 1 table that I'm concerned with right now. It's called LoanSummary. It contains a bunch of fields, but for ease let's assume just a few columns, LoanNumber, Dat...more >>

Here is sample table and data. Create Table Data (DataKey int identity (1,1) not null, DataPeriod smalldatetime not null, DataValue real null, DataFlag null) insert into Data values('2007-01-01 00:00:00', 19.2, null) insert into Data values('2007-01-01 00:03:00', 18.2, null) insert into ...more >>

need assistance. create table t1( c1 int , c2 int , c3 char(2) ) insert into t1 select '1','1','one' union select '1','2','two' union select '2','1','three' union select '2','2','four' union select '3','1','five' union select '3','2','six' desired output :- c1 c3 1 ...more >>

Hello! I have this OpportunityHistory table (pasted at the end) which has snapshots in time of the field OpportunityId with a CreatedDate datestamp and an associated Amount. So the OpportunityId can appear more than once in the table and together with the CreatedDate defines a unique record...more >>

LS, I read some earlier articles which explain how to rewrite a query with a not exists sub query to a query with an outerjoin. I did not succeed. Can some one give me a hand with this? The query I want to rewrite is: select instrument_id, ODS_TB_STAG_SD_A_INTADJUST.int_date as int...more >>

I have created a daily database Maintenance Plan in Enterprise Mgr using the wizard. However the Job does not appear to run and I am not sure what is going on. Can anyone tell me what to check for or any bright ideas. I am completely new to SQL and so I am stubling a little. I am using Ente...more >>

Hi everyone, I have a blog at http://blog.namwarrizvi.com/ focusing specifically on TSQL programming and day to day issues. Some of the recent posts include: Removing Duplicates Rows Case Sensitive string comparison in SQL Server 200... Secure Your Data: Simple solution to Encrypt...more >>

Hi, I am new to SQL Server so excuse me for this stupid question. I have got some XLM code which I want to send on some remote application server via POST method. The server will return XML code as well. I can't find a proper T-SQL command(s) which I could use to send XML on the server by...more >>

How to Fix Error 207 in SQL SERVER 2000 with out using Service Pack SP4. ...more >>

Hi, i need to create a stored procedure which execute (evaluate) a query string passed as param. Is it possible? The query is an update or a create query, so i don't need any return result... Thank you. Ale ...more >>

Hi all. In a varchar(8000) i get a list delimited by commas. I want to know how many items it is between the commas or just count commas + 1. Declare @List varchar(8000) Set @List = '100065, 100039, ¤, 100191' I want the result 4 in an easy way. best regards geir ...more >>

I have AccountRegister table in SQL 2000 database with the following fields: ID, EntryDate, DebitAmount, CreditAmount and let's say values are: 1 04/01/2007 100.00 NULL 2 04/02/2007 110.00 NULL 3 04/03/2007 NULL 25.50 4 04/...more >>

I am trying to convert a heap of Access queries to SQLServer, but am stumped as to why the following two have errors. I didn't write the original Access queries so am trying not to radically change them. 1. This one doesn't like the '*', so I replaced MECRMapping.* with the actual field nam...more >>

Hello I have a slight problem, I have a view created in sql 2005 that has 5 tables joined, and a column that counts records from another table. But it's impossible to add an index on it since its a subquery, what to do? feels odd that its not doable to count and put an index on it, its 2007 af...more >>

I'm trying to finish up this project and I'm getting a very frustrating error just trying to update the status on the main table AFTER I'm done processing. I have 3 stored procs, one invokes the other 2 depending on whether the "paying" company matches the "charge" company. It all runs fine ...more >>