Hi I have a table with the structure as shown below. This table consists of about +-10 000 000 rows. I then have a query that goes through most of this table using joins from other tables on cli_code, grp_code, sub_code, sub_levl, ben_Code, portcode, investment_date, bus_run_num Now, my clus...more >>

How to create a link table to Excel. I am running on SQL 2000 and I need to create three link table to three Excel worksheets. Can it be done? I need the link table not importing data from Excel to a SQL table as I need the real time link i.e.data in SQL tables can be updated instantly when data...more >>

i want to create an xmldoc from SP.. I have used sp_makewebtask but what happens is -> due to the "Enter" or CRLF (which the sp does implicitly) the formatting goes haywire and thus the xml file is not generated properly as a result the parsing fails.And everything fails. Is there any other way ...more >>

I create a table in server explorer, with the ID column as a unique identifier. I open the table, with the intention of hand-typing in some data. In Visual Studio.NET 2003, Tools->Create Guid, New Guid in Registry Format, Copy I click on ID column in the table, paste, and see new guid ap...more >>

I would like to know how can I use some Jscript code to pass two variables from a script function an input parameters to a SQL Store Procedure... Thanks!...more >>

My server operating system is Windows 2000 Advanced server with SQL Server 2000 Enterprise. I have about 30 performance counters that collects samples every minute on the server. I would like to automated a system that will copy the values to a SQL Server database table daily. Ple...more >>

I need to search and replace a word in a field (description) in the table (TableInventory) in my database? I need to replace 'Michael S.' with 'Jacob S.' within the text string. How can I do this with SQL and TSQL? I need to search and replace in all the rows in the table. Thanks in adva...more >>

I am having problem with updating information to a table from another table. I have table storing the information on a the inventory (TableInventory). Its needs to be updated with information from another table containing specific updates for the inventory (TableUpdates). The items in the Tab...more >>

Hi, I want to convert an Oracle PL/SQL Script for a trigger into a SQL Server 2000 T-SQL Script. The Oracle script is as follows: CREATE TRIGGER SET_GCDR_ENDTIMEKEY_TRIGGER AFTER INSERT ON GCDR FOR EACH ROW BEGIN INSERT INTO GCDR_ENDTIME VALUES (:NEW.ROWID, (:NEW.STTIME + :NE...more >>

I have implemented a queue in a database table and I am getting very poor performance. I want multiple processes to be able to read rows off my queue simultaneously. Running 1 process, I can read 5,000 accounts off the queue in 13 seconds, and running 2 processes, I can read 10,000 (5,000 each...more >>

is there a way to select all records but not certain records within the same select statement? something like, select * from table1 but not these if they exist in this subquery. select * from table1 where name exists (select * from table1 where name ='test')? no results. ...more >>

I am getting an Divide by zero error even when I check for zeros. SELECT (CASE WHEN (Revenue) > 0 AND (NoDays) > 0 THEN (SUM(Revenue / NoDays)) ELSE 0 END) AS ADR FROM solditems Any ideas where I am going wrong? Thanks Peace in Christ Marco Napoli http://www.ourlovingmother.org ...more >>

Need help - Trying to write a query that accepts multiple values into an input variable. I would then like to do a wildcard search against a column using all the values accepted as input (seperately), without having to write 'n' number of LIKE statements in the WHERE clause. I understand that the IN...more >>

Hi everyone, I backed up a database with VDI. Then when I try to restore I get an error AFTER it has streamed all the data back to SQL Server. I get an error returned from sql server: Msg 3013, SevLevel 16, State 1, SQLState 42000 [Microsoft][ODBC SQL Server Driver][SQL Server]Backup or rest...more >>

How can I assign my dynamic query result to the @v_simvalue parameter? Thanks. Jen Snippet in question: SELECT @v_simvalue EXECUTE @v_sql The whole proc: SET NOCOUNT ON DECLARE @v_sku1 varchar(200); DECLARE @v_sku2 varchar(200); DECLARE @v_sku1c varchar(200); DECLARE @v_sku2c ...more >>

I am trying to put an audit system on a table to be used as a change order system. I have it all ironed out except for the following. The trigger runs on an update and checks each field to see whether or not it changes. If the field changes then it inserts the new and old values into an audi...more >>

Is it possible to create a View which uses parameters? I mean something like that CREATE VIEW MyView AS SELECT * FROM MyTable WHERE MyField = 'StandardValue' UNION ALL SELECT * FROM MyTable WHERE MyField = Parameter Where StandardValue is a fixed value which doesn't change whatever is th...more >>

I would like to two jobs in my database based on specific dates in a year. How can I do that? Can it get the specific dates from a table? If so any code help would be highly appreciated. thanks...more >>

I'm not sure if I'm in the correct group or not, but I need some help on something I'm new to this so, What I need to do is create a stored procdure were the server name is being passed in to it from a Batch File. How can I make the server name a variable and an input field to the sp? I've t...more >>

I dont know if this is possible but.. I would like to trigger a web based process from a sql job. I need the added versilility of .net to preform a bunch of actions nightly. the sql job dosent need to send any information or process any information from the webpage. It just needs to trigge...more >>

I want to use an input to perform a select from a table. I want to assign that result a table name to a variable (I will have many tables to choose from). That variable I want to use in my next select statement. I am trying to build an application that is based on a vba and I am accessing an SQ...more >>

Hi All, I have a proc which returns a value after some DML operations on a table. This proc is being called by a C# program. Problem is there are instances in which same value was returned back to the C# program. How do I prevent this from happening. "SET TRANSACTION ISOLATION LEVEL SERIALIZABLE...more >>

Is there a way to run a trigger only if the update comes from a certain asp page? If the update is from page 1, I do not want the trigger to run and if the update comes from page 2, I do want it to run. Is this possible. Thanks! Darren MCP ...more >>

I'd like to put a timestamp for any time a row is updated or inserted. I wrote something like: CREATE TRIGGER SetStamp ON MyTable FOR INSERT, UPDATE AS UPDATE Test1 SET Stamp = GetDate() but that updates ALL rows. How can I set the trigger to only update the changed/inserted rows? ...more >>

I have just noticed a cock-up in my datawarehouse as follows: Primary key on products table is type BigInt, foriegn key on Fact table is type Int. There are a few tens of millions rows on each and the system is live in production. Everything works OK, except when some bright spark queries usin...more >>

hi, I have a sql server 2000 server (4 processors, windows 2000 OS). I have a db of 40 gig, one of the data file is over 28 gig and has 16 gig free space in it. So I went into Enterprise Manager and picked shrink db option, and selected this data file and choose to shrink to the min ...more >>

Hi, I am not very sure about WITH PAD_INDEX function, when creating an index using WITH PAD_INDEX along with FILLFACTOR=90, Am I using more disk spaces compare to creating an index using just FILLFACTOR=90? I have a table which is very busy, updat/insert rows almost every seconds, But right n...more >>

Hello, Why If a table have update on cascade for 2 tables, and I have an update trigger on the second table; the update trigger returns more than 1 row, if I update the primary key on the first table. The second questiong is why the trigger gets executed on the second table, if the software...more >>

Can somone point me to a document that shows how to uninstall SQL 2000 in a clustered environment....more >>

Hi, I have a stored proc that takes the first character of result set and outputs it as follows: Character Valid 0 1 4 1 A 1 C 1 F 1 W 1 Z ...more >>

Hi all, I may be in the wrong group. If I am, I apologize and I humbly ask to be pointed to the proper group. I've got several linked servers set up on my MSDE, so I can administer any of our various datasources (which includes 3 SQL2k boxes and 2 AS400's). I'm trying to set up a MySQL...more >>

I have a UDF that I wrote in an attempt to convert a number to BCD on the fly. The problem comes through where there would be embedded 0's in the number, how do I fix this. The idea is that a vb program will call a SPROC, within the sproc each record will be returned as a string of characters tha...more >>

I have sql server 2000 running in an office behind a firewall. I want to have a frontend application connect to a database from homes or offices. (There may be as many as 10 different persons connecting at a time from outside locations.) Where can I get information on different ways to make ...more >>

Let's say I have a user "bob", he has execute permission on SProcA. = SProcA calls SProcB. does bob need to have permissions set for SProcB = too? How does this work as far as access to other objects, if SprocA also = selects from TableA, does "bob" need permissions on TableA? Thanks!...more >>

Hi All, Have you ever tried to delete a SQL Agent job within itself while the job is running? I wonder it it is feasible to do such a thing. Basically, I have a SQL Agent job that extracts data from many source databases to a central warehouse database. I want to add a version check at t...more >>

I have to put a mechanism on a table to prevent row updates under certain conditions; if the condition is met, an error will be raised, the transaction will be rolled back, and no update will occur. This need to be an Instead Of trigger so I can see the row values before the update occurs. Work ...more >>

What I am trying to ask the experts is: my db/tables/columns probably don't care what their collation is. They are populated by customers. (If customers want French collation, that's fine. I expected UK & US collations to be same, but they are not by default.) GIVEN THIS, WOULD I BE BEST REMOV...more >>

I have a table that lists products in it. There are 2 columns. One is the product number and one is an abbreviated name. The abbreviated name is sometimes difficult to understand so I created a second table with two columns: the product number and a descriptive/long name. The long name is not ...more >>

Hello there, For setting up a test db with critical test cases I'm looking for a way to copy test data out from the production server to the test server. Ideally on the production server I will write a SQL script file with different INSERT stmts for all my records of choice an then reaply this ...more >>

I am running Sql Server 7.0 and I am using a store procedure to drop a global temp table if it exists. Currently, it looks like this: if object_id('##Table') is null print 'True' else print 'False' Drop Table ##tmpTable It works fine through Query Analyzer, but when I try to execut...more >>

Hi All, I am designing a Crystal report have to combine data from two servers and it has to be done in a stored procedure. I thought it was a snap...just create a linked server. And naturally the stored procedure runs fine from the Query Analyzer, but Crystal does not let it pass the inspectio...more >>

The requirement: Local SQL Server for day-to-day operations Hosted SQL Server for web app (customer order entry) Synch these two "periodically" throughout the day. Problem: The hosted SQL Server is shared, and I cannot use replication. My workaround: Write Insert, Update and Delete state...more >>

Hi Folks, We have a SQL 7 database that used to have 65 compatibility, it was switched to 70 and when testing the outer join it returs different results. 1) SELECT dbo.NAE_Person_Name_Data.Non_Agency_Emp_Id, dbo.NAE_Person_Name_Data.person_name_type_code, dbo.NAE_Pers...more >>

im having trouble converting access sql queries into mssql, i know to replace certain characters (eg _ and *) but im having trouble importing expressions - how do i do these in mssql ? eg an example access query i use :- SELECT dbo_Personal.ID, dbo_Personal.Surname1, dbo_Lead.SourceOfLead, db...more >>

Helloe- I am having a bit of difficulty creating the following function: Create Function fn_Money (@InputString varchar(10)) Returns int as Begin Declare @ResultMoney int Declare @varsign char (1) Declare @IntMultiplier int Declare @ftCost int Set @varsign= Right(@InputString,1) ...more >>

I need to insert data into one table from one table while collecting information from two other tables. Here is what it would look like split out: SELECT A.divisionID FROM Divisions A, DMValid B WHERE A.divisionshortname = B.DivisionShortName A.divisionID will be inserted into table below SELE...more >>

Hi all, I'm using the following stored proc. It returns 5 datasets as a single dataset. One on top of the other. I want to group them. But the syntax checker won't allow it. Does anyone know how to group this type or any type of stored procedure? Thanks, baz ----- CREATE PROC...more >>

Hello Experts! Would someone show me how to combine 2 set statements: declare @X int,@Y int set @X=10 set @Y=15 Can we combine these 2 statements into one? ThankX Hai ...more >>

Hi! I am trying to run this command as a job (cmdExec) i.e. dtsrun /SMyserver700 /Ntest /E server name : My server700 package name: test Windows authentication (Do I need to specify an other parameter?) It is giving me this error message: Error: -2147467259 (80004005); Provider Er...more >>

There's got to be a better way to do this! Basically, 3 table - tcaTime, tcaProject and tcaClient - and I want to pump total values into tcaProject for all projectid+clientid then total vals into tcaClient for all clientid. I am using two temp tables to do this. Anyone suggest a better wa...more >>

Does anyone know why I am getting this error? UNION ALL view 'DCU MessagesTest' is not updatable because a partitioning column was not found. Here is the tables and the view. CREATE TABLE [DCU MessagesQtr1] ( [DCU ID] [int] NOT NULL , [Reading Time] [datetime] NOT NULL , [Packet Num...more >>

Hello, select so.[ID], so.[zakazka], so.[Cislo_vykresu], so.[velkost_davky], so.[Meno], --- no working (select Max([Datum cas]) From (select sysobjects.Name from sysobjects where id = Object_Id('data_' + so.[zakazka]))) As dlll --- from [Zoznam zakazok] so I need put in to "from" table n...more >>

Hi, I have a query that I send to sql server 2000 from C++ code thru ADO. I'm just wondering which way would be better / faster. Here's the two queries : SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT * FROM Table WHERE Table.column1 = 'first' --------------------- SELECT * F...more >>

Hi all, I have a problem with a very large field that I am using, I have created a table with a field type of ntext as the data that I am inserting is very large, I need to do some replace's on the data but cannot do a convert(varchar) as the varchar value of 8000 is too small is there any w...more >>

Hi, A couple of replies to questions raised by users here contains this function : SCOPE_IDENTITY(). Is this available in MS SQL 7??? I can't find it in BOL. I'm just curious with this function. Thanks -- Nonoy Makati, Philippines ...more >>

Hi, The SQL always adds an '0x' prefix to binary data. Is there a way that I can convert the binary data to a character data type so that I can get just the part that I need from the binary data? For example, the SQL returns 0x0000001 as hex equavalent of 1. I need to get just the "00000001" ...more >>

Hi, I would like to know why and how the performance difference is noticed with the following 2 queries: 1) Select a,b,c from table1 Union Select a,b,c from table2 2) Select a,b,c from table2 Union Select a,b,c from table1 In my case, when I made the second query in the union claus...more >>

Hi, I have problem with inserting data query. I have 3 tables. The primary key from the first one has to be entered into the others two. And I can not use simple INSERT and then SELECT to get the primary key from the first table. Can somebody help me with this problem? Thanks...more >>