I have some data like at the bottom. I use SQL Server 2000 stored proc to process this data using sp_xml_preparedocument . I would like to get the data within the node PartAuxiliaryID as it is below(the exact xml as it is, not the values or attribute) to a variable. What will be the best way to do that in stored proc using sp_xml_prepar...
Hella all, What I want to do is something like that, I will have an SQL table depending on my parameter, DECLARE @find varchar(30) SET @find = 'no' SELECT * FROM ( if @find = 'yes' ( SELECT * FROM myTable WHERE ID= '5882' ) ELSE ( SELECT * FROM myTable WHERE OLD_ID= '5882' ) ) X This is ...
Hello All, How can I do the following in SQL Server DECLARE @Local nvarchar(20) SET @Local = 'True' SELECT * FROM My_Table WHERE my_ID IN (IF @Local = 'True' SELECT AllIDs FROM ATable ELSE SELECT TeamIDs FROM TeamTable ) ...
I was thinking about using bcp command to solve the user authentication, but does a bcp command capable to import to a table in my database? By the way, I am using SQL Server 2000 environment. Here's the code I have got so far: SET @Command = 'bcp "SELECT vwTest.* from [myserver\sql].test.dbo.vwTest" queryout dbo.Test -C ACP -c -r \...
On an MS SQL Server 2000 installation I have numerous stored procedures that pull data from databases other than the one it's stored in. All selects occur on the same database server. For example: select * from [OtherDatabase]..table How can I find which procedures do that sort of thing without eyeballing each one? ...
I know this must be simple, but how do I preface the creation of a function with a check to see if it already exists? If it exists, I want to drop and re-create it. ...
I cannot view the sql jobs in sql enterprise manager when I log on to the machine that has the SQL Server database. I can see the jobs when I open the Database using Enterprise Manager on a different machine. ...
Hi I want to check if SQL logins have passwords same as thier login name. E.g. login name 'abc123' has password= 'abc123'. I need to do this for a security audit across many 2000 and 2005 servers. Is it possible to check using TSQL? Thanks in advance ...
I am using SQL Server 2000, I have a situation where I am copying data over from one table to another, the destination data table requires each Name row to be unique. Here is a quick example of my issue Source table RowID | Name 1 A 2 B 3 B 4 B 5 C 6 D 7 C What I want to do is turn it in to th...
I work as a researcher at Tata Research Development and Design Centre, India . I am exploring the available Natural Language Interfaces for database.While exploring MS English Query, I found that certain type of relationship does not give appropriate answer. Our schema looks like as shown below: Schema: Customer ( customer_id , cust...
Here is the T-SQL code that I coded, which I imagined to work but didn't work: DECLARE @Local nvarchar(20) SET @Local = 'Yes' SELECT (CASE WHEN @Local = 'Yes' THEN ID ELSE COUNT(ID) END) FROM myTable Is there something I am doing wrong or is there any other way to do that? Thanks. ...
I have the field: APP_DATE (smalldatetime) I'm doing this query: INSERT INTO table (TYPE, CODE, APP_DATE, DATE) VALUES ('APP', '123', '02/10/2010 12.30', GETDATE()) It fails: Msg 296, Level 16, State 3, Line 1 Conversion from datatype char to smalldatetime generated a value not between the interval of valid values. Instruction has...
I need to save the DataSet, results of a reporting app, to a table in SQL Server 2000. Do I save the datasets the same way I would store files in the DB, via Byte Array()? ...
I am having a problem with a FreeText search, currently running on a SQL 2000 server. In a table of approximately 1.3 million rows which contain company names, I am attempting to use a FreeText query. However since SQL Server strips out special characters and single characters when building its index, our code does the same when subm...
This is going to sound like a crazy request. The databases that I report from do not have any foreign keys, and every single primary key is an identity_column. This makes using tools such as TOAD difficult because the Intellisense works by reading the PK and FK relationships. Anyone have a script to remove the primary keys from every t...
I need to find if /3GB switch and /PAE is enabled on a server. Also, I want to know the size of page file and physical RAM on the server. I can check them manually but how can I check them using TSQL on both SQL 2000 and SQL 2005? ...
If I have a parent table and a child table where there can be one parent to many children, how can I go about returning the following xml from a stored procedure in a stored procedure? <Parents> <Parent> <ID>Integer</ID> <Children> <Child> <ID>Integer</ID> <Text>String</Text> ...
I'm trying to create my first data mart. I am creating my dimension table with a "Select Into" The code is as follows: select distinct fpc_number, fpc_desc into m2mdata01dw..ProdClass from m2mdata01..INPROD How can I set up a autonumber primary key in this situation? ...
i have a process to move rows from one database to another. Because of some circular foreign key reference chains i cannot remove the rows from the old database, nor can i insert them into the new database. Since the entire operation happens in a transaction1, i want SQL Server to ignore referential integrity failures until i call COMMI...
Is there a way to query the database and retrieve a list of all stored procedures and their parameters? I am using SQL Server 2000. ...