/* I am trying to extract a subset of nodes from multiple xml instances into one instance, but I do now know the proper syntax. Can someone please help me out? Finally starting to learn XQuery! See below for DDL and desired result. Thanks so much!! ~PJ */ create table XmlDataTable ( ...more >>

I am running SQL Server 2005 on Virtual PC. I can connect to in VPC using SMS if I set server =win03 SQL Server Authentication Shimon2 and password If I go to my host machine I can't connect the same way - time out. I can see server. I have Team Server running on it and VS has no problem ...more >>

I'm trying to find a way to loop through the testnewsRecipients table and insert the value of the USERID field equal to the USERID field in the tempUsers table. As you can see in my DDL, currently the USERID field in testnewsRecipients table is empty and the only value the 2 tables have in c...more >>

I am running into some odd behavior in a webtask and was looking for any help at all. I have a SQL job that is a web task. For the last few days it has been running fine. Now for some reason, it is failing. I'm getting the following error: Executed as user: dbo. SQL Web Assistant: Could ...more >>

Hi, I wonder if it is possible to have multiple foreign keys between to tables and delete data automatically. We have on table with a number of locations and another table with a number of routes between them. The foreign keys go from location to the beginning and the end of the route. How ...more >>

Hi, I am novice SQL server 2000 guy and I am trying to use variables inside query.... but could not figure out how I could do that ... after so many errors I am turning to this group.... declare @db_name1 varchar(50) declare @db_name2 varchar(50) declare @id varchar(50) set @db_name1 = ...more >>

Hi All, Just a quickie... Is there any perfomance difference between joining tables in the... FROM clause eg. ...FROM a INNER JOIN b ON a.id = b.id to that of the WHERE clause eg. ...FROM a, b WHERE a.id = b.id TIA Tim ...more >>

Dear all, I wanted to create a text file to be passed as input to a Mainframe system. I have created an SP with the query to create the data exactly in the requisite format. I need to be creating the text file from within SQL Server only. I have used osql utility to achieve this and it works....more >>

HI all, If I have 3-4 update triggers, and each of them contain an update statement to the selfsame table that is calling the update trigger, would I end up with a recursive call Thanks Robert ...more >>

SQL SERVER 2000 I got a table with columns ID, CityCode, CitySub Here is the sample data ID CityCode CitySub 1 AA CitySub1 2 BB CitySub2 3 CC CitySub3 4 DD We have a process called ReverseRoute, after that data needs to be in th...more >>

I basically would like to take a sql server 2000 table and have all the properties written out for me into a file. If I were able to do that then, I would be able to put this into a “CREATE TABLE” vb.net 1.1 program and not have to write out all that code myself. This way I could jus...more >>

This is an old issue, which I chose to ignore, since it was for a temporary app with a handful of users (famous last words, I know). The "multiple foreign keys" post today got me thinking about it, and I figured it is worth looking for a solution. I will post DDL at the end, but the theory i...more >>

Hello, I would like to know if there is any SDK i can use to build a WebPage which would enable me to transfer data between sql servers. Purpose: Non technical people who are not familiar with using administrative managers, would like to have a publish button where by they can publish/move...more >>

Hi (I posted this on the full text newsgroup but it seems pretty quiet at the mo and I was hoping someone on this list might know the answer). I have a full text index on a table on a few columns including a text data type field, and I'm searching for JN00001 which I know is in the text col...more >>

Hello, I have some users which want a particular dataset provided on a daily basis. They do not and can not have permission to access SQL server. Their preferred tool is Microsoft Access. Hence, I would like to create a completely automated scheduled task which will run their query and stor...more >>

Hi All, (This is a two-part SQL question) Can someone tell me if there is an easy way to delete records from a table where there are only four data elements on which I wish to base the delete routine? For example, I have a table that has numerous records in it. I want to delete all r...more >>

Looking for a way to import Excell with mulitple worksheets. 1. table that it is going into needs to have an additional column which is automatically populated with the name of excell file 2. the other table it is going to needs to have an additonal column which is to be automatically populat...more >>

SQL2000: I have a table with records of types A,B and C say. In a situation where a C type record is updated, I would like that record (pre update) to be inserted into a legacy table. Can anyone advise on how to achieve this? Thanks S. ...more >>

Can anyone tell me if it is bad practice to use both an INNER JOIN and an LEFT OUTER JOIN in the same query on SQL Server 2000? ...more >>

I'm running into an issue while using coalesce to create a dynamic where clause. To give some background, here is the article that got me started on this: http://www.sqlteam.com/item.asp?ItemID=2077 What I am trying to accomplish is a series of user configurable filters that will ignor...more >>

Hi all, Apologies if this is the incorrect group. I couldn't find a group that specifically handled queries in SQL Server. I originally posted this in the Access Queries group, but since my back-end database is actually SQL Server and I am just using Access as my front-end I need suggestion...more >>

Hi All, I was successfully installing sample database AdventureWork on my server the first time, But realized that I didn't install with all the features with it. I delete the DB and try to reinstall it with all the features. it goes through all the steps but DB don't install or attach. There ...more >>

I recently moved a hosted site to a new server, and now am getting NETWORKIO locks, and am having a hard time resolving the issue. The site is ASP/SQL Server 2000, with fairly low quality code. The ASP was generated by Dreamweaver and in many cases copy/pasted from the web. The site was o...more >>

Hello, whenever I add a record to a table with identity column it increments by one which is what I want. But when I input a record that was reject for any reason (I.e. primary key violation) and then input another record it will not be increment by one....it skips the number that would of be...more >>

When i do a select on my emplee table for rows with null idCompany i dont get any records I then try to modify the table to not allow a null idCompany and i get this error message: 'Employee (aMgmt)' table - Unable to modify table. Cannot insert the value NULL into column 'idCompany', ta...more >>

Create Table tbl1( recID int Identity(1,1) Primary key, col1 varchar(10) not null default '', col2 varchar(10) not null default '' ) CREATE TRIGGER t_tbl1Insert ON [dbo].[tbl1] Instead of INSERT AS Begin Insert Into tbl1(col1, col2) select case when col1 is null then '' else col1...more >>

hello, I am trying to add a column to a view. This is a generic column not pulling from the other tables but will be updated based on the values of the other columns. I thought I simply altering the view and add a column and set a value, if the other values were found true. However, it i...more >>

Is there a way to block another transaction from reading/updating a record while another read/write operation is ocurring? I tried using "SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;" not sure if this is viable. See below: SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; DECLARE @IsO...more >>

Hello SQL Programmers. What is the big difference between distributed SQL and Service Broker? Am I just confusing things here? I am starting a project in SQL 2005 and basically I have regional server and a data warehouse, customer might request reports which have data coming from both ...more >>

Hi, I need to be able to query the database within a Script task. I basically need to get the current connection and then execute some sql or stored proc and find out what's in the database and based on this info make a decision in the script. Does anybody have an example in VB.net how to d...more >>

Hi, SET NOCOUNT ON SET TRANSACTION ISOLATION LEVEL READ COMMITTED SET DEADLOCK_PRIORITY LOW GO These above lines are part of stored procedure creation script but these info is seem to be missing when the sp is actually created. May be silly mistake Pls help Thanks...more >>

I have a procedure that strips unwanted characters in vb.net, is there a way to do this in t-sql? For instance, I want to take all the symbols out of a string: stripped = Regex.Replace(StringToStrip, "[^ .@/:&A-Za-z0-9]", String.Empty) ...more >>

Hello, all. I'm using SQL 2005. I have a job that I'd like to call from a ASP.NET web page. My plan is to create a stored procedure that calls the job, then on my web page all I have to do is execute the store procedure. My question is how can I call the job using T SQL. I will then embed...more >>

I have a question about transaction when lock_timeout error occurs the procedure as follows, CREATE PROCEDURE dbo.usp_glock AS BEGIN SET NOCOUNT ON SET LOCK_TIMEOUT 2000 UPDATE glocktbl SET name='john' WHERE id=1 IF @@Error<>0 BEGIN GOTO Err_...more >>

hi guys, I have a dts package which has 2 unused connections, but I don't know how to delete them using dts package designer... is that possible to do on UI level or do I have to write a script to drop unused connections? -kev...more >>

Hi, I have a problem during the recovery of the sql db dump. A user had sql server installed in driver c, my application backed up a db using 'backup database'. Later on the user reinstalled sql server to driver d, and ran my application to do the recovery. The restore db was successful ...more >>

I need help to write Transaction Log BACKUP JOB with the help of xp_sqlmaint. i try to gather require information, but some how it is not working. i will appreciate if some one can help me to put all together. I am trying to do is to create transaction log backup job which runs every hour & ...more >>

In the stored procedure below, there are 9 different if conditions being checked. I was wondering if there is a more efficient way (or not) to write this procedure, without having to write the same select criteria each time. The only thing different is the where clause. Any suggestions are gr...more >>

I thought it's very easy and wrote: create function easy() returns char(3) begin return 'Uli' end after this typing: select easy() I expected to get the output 'Uli' but I got the error message "easy() can't be recognized as function" (- sorry for my English) What's wrong? -- ...more >>

Hello, I have sql statement that is selecting multiple invoice records and doing multiple calculations. Now, I need to look in each invoice record for Item_detail, any direction on how I would do that would be appreciated? Thanks....more >>

Hi Im a bit of a beginner with sql and need some help with a script. I got a table where I have 2 columns (more, but im focusing on these 2), where I need to switch the data. the data in Column 1 is to be transfered into Column2 and vice versa. How do I script this? I guess I need to fetch th...more >>

Hi All, I have a problem to get this work done. I want to optimize this by using multiple ranges in having cluase.. for the understanding i'm writing down the whole Query here ... ------------------------------------------------------------------------------------------ SELECT ...more >>

Hi, I have a table BehaviorProp with an TinyInt identity BP_ID, I need to change the column to a SmallInt. I tried this: drop index behaviorprop.PK_BehaviorProp alter table behaviorprop alter column BP_ID smallint but I get An explicit DROP INDEX is not allowed on index 'behavior...more >>

Hello there. I was just wondering if there's a way to get the current status of a particular scheduled job using T-SQL. I'm looking at sp_help_jobhistory but I'm not sure if it'll give me the most up-to-date info that I need. Specifically, I want to check to see if a particular job is cu...more >>

When you have a table that continues onto another page, how do you get the table headers to show on the 2nd page? I just get a continuation of the table with no headers on subsequent pages. Anybody know how to do this? *** Sent via Developersdex http://www.developersdex.com ***...more >>

Hi I am trying to do this on a temp table DELETE FROM #output where [tst1] is NULL this for some reason doesnt seem to work. on the other hand when i do the below insert into db_comp select tst1, tst2,tst3 from #output where tst1 is not null seems to work. does anyone know why this is...more >>

Hi This may sound like a stupid question, but I have a query that returns records on a month and year of a the transaction. What I wish to do now, is write a WHERE clause to filter records to a certain period. For example I wish to show transactions up to and including March 2006. so w...more >>

I have 2 tables, Products and Instructions that look like so: Products ProductID, InstructionID, ProductMSRP, ShipDate 123, 345, 119.99, 01/01/2001 456, 678, 20.00, 02/02/2002 789, 890, 345.99, 03/03/2003 Instructions ProductID, InstructionID, InstructionLineNumber, Instruction 456, 67...more >>

I want to create a query that can highlight equal but opposite signed values over the last 5 days. E.g. Sale 500 1 Jan Sale Return -500 3 Jan I this possible within a SQL query? The recordset would be say as below and return the above only. Sale ...more >>

Hey I have database with data in it. I would like some tool to generate Insert statments that will Insert the same data in case I will build another database with the same data model. Just like copying data from one database to the other. It's for testing purposes. I need sample data so I cou...more >>

Hi All I am trying to find an example of how to send messages to a queue on a remote sql server. Books online is cryptic at best and the other examples I have seen are all very basic. Any pointers much appreciated DH ...more >>