SQLServerCentral / General / SQL Server 7,2000

hyderabad dba institutes

vinod-328175 — Tue, 31 Oct 2006 21:30:00 GMT

Dear Friends,i'm working for a development company, as Junior DBA.for oracle concepts,Wilshire and SQL * are very famous in hyderabad.is there any Good DBA training institute in hyderabad? sorry if the question should not post here.

thank you very much

uninstall sql server 2000

knowledge_base — Mon, 07 May 2012 09:48:43 GMT

Hi,I have sql server 2000 installed in my pc. I don't remember if I installed the Enterprise version or Developer version. How can I find out? I need to know the version because I want to uninstall it. I found instruction from the web saying that I should use sql server 2000 CD to uninstall it. The CD has 2 versions so I need to which one I installed in order to uninstall it.Thanks in advance.

How to know if an object is still in use?

Allen Nugent — Mon, 07 May 2012 00:53:07 GMT

I need to migrate an old database (SQL Server 7 on Windows Server 2000) with about 70 tables, 150 views, and 20 stored procedures. I'm sure that most of it isn't even being used anymore, but parts of it are critical to business planning operations. The end users only know the names of a few of the tables or views and aren't very tech-savy.I would like to be able to assess the various objects and procedures in preparation for decommissioning. Is there a way to query the database to find the last time each object was used (e.g. last write to each table, last read from each table, last read from each view, last execution of each SP) ?Thanks!

Business objects technical online , in house , corporate training

harshita4desu — Mon, 16 Apr 2012 03:17:04 GMT

For sap business objects expert level training provided in Bangalore, Hyderabad, Mumbai ,Delhi and all major cities of India Please reach us at +91 9030204030 or send your updated profile to medhainfo99@gmail.com or visit our web site www.medhainfo.comhttp://medhainfo.com/business-objects-training

Existing Meta Data Services packages should be moved to SQL Server storage

philcruz — Wed, 08 Mar 2006 15:07:00 GMT

In preparation for upgrading to SQL Server 2005, I run the Upgrade Advisor and it gave me the warning: Existing Meta Data Services packages should be moved to SQL Server storage (in the msdb database) or to structured storage files before you upgrade. SQL Server 2005 does not install support for SQL Server 2000 Meta Data Services; therefore the Package Migration Wizard will be unable to access Meta Data Services packages after upgrade in the absence of the SQL Server 2000 Client Tools.I look under the DTS>Meta Data Services Packages node and there are no objects. Is there somewhere else I need to look and how do I move the packages to SQL Server storage? Or can I ignore this warning?

The object name ... contains more than the maximum number of prefixes. The maximum is 2.

jsblakley — Fri, 01 Apr 2005 15:12:00 GMT

I am attempting to call a stored procedure from a separate sql server box and I receive the following message...

The object name 'prod1.tolim.dbo.' contains more than the maximum number of prefixes. The maximum is 2.

Here is the statement in my stored proc....

exec PROD1.ToLIM.dbo.ToLIM_ExtractIndex @vDB, @vDBTable, @vLIMSymbol = 'CIN.CINERGY_LOAD', @vLIMColumn = 'CIN.ESAL_LESS_ORPHAN', @vTimeUnits = 'HOURLY_COLUMNS', @dDateStart = '1999-01-01', @dDateEnd = '2005-03-03'

I am executing from my dev server to my prod server and the prod server is a linked server.

Any help would be appreciated.

Jeff

Syntax Error or Access Violation -> Transform Data Task Properties

atsioplakis — Fri, 27 Apr 2012 08:43:36 GMT

Hello everyone, I have been stuck on this for a while now. I have looked at various sites and through the forums here but I cannot find a solution to this. I refuse to believe that there isn't one.I am trying to setup a transformation that is going into an excel object in a DTS package. The server is SQL 2000. I am trying to parameterize it with Global Variable and I cannot.Here is the statement:[code="other"]Declare @S_Id IntegerDeclare @SA_Id IntegerDECLARE @Academic_Year INTEGERSET @S_Id = ?Set @SA_Id = ? SET @Academic_Year = ?[/code]There is more SQL after it using the variables but for now I do not want to include here. Like I said above I have tried a many different thing to solve this. I get the error message :"Error Source: Microsoft OLE DB Provider for SQL Server""Error Description: Syntax error or access violation"

Sendmail Error

ahthomas — Mon, 23 Apr 2012 09:06:43 GMT

I am unable to find anything on Google for this.SQL server version is 8.0.2279No email is sending from the server. I get this error: xp_sendmail: failed with mail error 0x80040108

Issue with Query

jay1b — Tue, 10 Apr 2012 05:37:11 GMT

HelloI've resolved the issue now, but I thought I'd mention it and see if anyone has any ideas on why this could be caused.I've got a query below:DECLARE @str nvarchar(4000)SET @str = ''SELECT top 22@str = @str + CASE WHEN a1.at_style = 'Numeric' THEN ISNULL(CAST(atv_NumericValue AS VARCHAR(100)),'') + ISNULL(a2.at_short,'') + ' ' + a1.at_short WHEN a1.at_style = 'Alphanumeric' THEN ISNULL(CAST(atv_TextValue AS VARCHAR(100)),'') + ISNULL(a2.at_short,'') + ' ' + a1.at_short WHEN a1.at_style = 'Set Value' THEN a1.at_short ELSE 'xxx' END FROM attribute_valueINNER JOIN vw_attribute a1 ON a1.at_lang = 'GB' AND atv_at_id = a1.at_idLEFT JOIN vw_attribute a2 ON a2.at_lang = 'GB' AND a1.at_unit_id = a2.at_idWHERE atv_vv_id = 1537AND atv_atvt_id = 1ORDER BY atv_DisplayOrderSELECT @strThis would output '4 x 45 spdMTM', however if I changed top 22 to top 23 it would incorrectly return 'MTM'. There are only three results in the result set.This could be resolved by removing the [b]+ ISNULL(a2.at_short,'')[/b] from all lines, or by removing either the [b]Numeric[/b] line or the [b]Alphanumeric[/b] line.I tried wrapping ISNULL or Coalesce around everything, and added in Else to all the case statements however nothing resolved. I looked at the Query Execution plan for both Top 22 and Top 23, they were the same except for at the end one had Sort then Compute Scalar and the other had them around the other way.In the end I removed all statistics for all of the underlying tables and it resolved the issue.Does anyone have any idea what could of caused this? Or how I can stop it from happening again the in future.ThanksJamie

lookig for professional trainers

harshita4desu — Mon, 16 Apr 2012 03:16:23 GMT

Hi All,Medha Information Research Is an Esteemed Organization established to serve global learning audience with Extensive technology Knowledge mentors. We are offering In House, Online, Corporate and Campus Training. Looking for Technical Trainers with good hands on Knowledge on Subject Areas Mentioned Below.CoreJavaAdvanced JavaFrameworksEJBWeb ServicesOracle DBAOracle DevelopmentDB2 DBADB2 DevelopmentSQL Server DBASQL Server DevelopmentTeradata Data warehousingSybaseSiebelOBIEEIBM Data StageBODICognos Data managerMSBISAS BIHyperionOracle AppsFlashDream ViewerPhotoshopCSSPHPSAP All ModulesEligibility*** Hands On Experience on technology**Interest and passionate about training*Should be service minded.Please reach us at +91 9030204030 or send your updated profile to medhainfo99@gmail.com or visit our web site www.medhainfo.com

Linked Server to Progress Database...help

donald.chandler — Wed, 31 Dec 2008 09:37:02 GMT

I hope someone can help me out with connecting to a Progress Database.Here is what is going on; I need to create a connection from SQL Server to a Progress database. I need to pull data down regularly to perform a comparison between multiple data sources including this Progress server.I have configured the ODBC Data source as a name of ‘ProgressODBC’ to point to the Progress database server (ProgressServer) that I need. I can access this server through MS Excel by setting the data source to this ODBC. Progress driver is 'DATADIRECT 4.10 32-BIT Progress SQL92 v9.1E' PGPR0918.DLLHowever, I have tried to create a Linked Server to no avail. I seem to be able to create the Linked Server but when I try to access the tables I get the following error: 'OLE DB provider MSDASQL reported an error. Invalid connection Data source name not found and no default driver specified.'What I choose to use for the Linked Server is this:- [b] Linked Server Name: [/b] lsProgress- [b] Server Type: [/b] MS OLE DB Provider for ODBC Driver- [b] Product Name [/b]: 4.10 32-BIT Progress SQL92 v9.1E (I have even used the DLL name)- [b] Data Source: [/b] ProgressODBC (I have even used the DLL name and the server name)- [b] Provider String: [/b] ProgressServer- [b] Location: [/b] I used the port number that was defined on the ProgressServer. I even tried to use the full location of where the database is located.- Catalog: I used the name of the database that I want to connect toOn the Security tab:- [b] Login should be made using this security context: [/b] I used the system userid that was supplied. However, there is no password. So, I used a 'space' as I found from Goggling this should work. Plus, that is the way the ODBC is set up too.On the Server Options tab:- I choose RPC and Use Remote CollationI am at a loss for what I can do. I contact the Progress support guy we use and they weren't too sure what to do.Thanks!Don

Error "Could not find stored procedure XXX" in a DTS Package

christine.lawrie — Mon, 08 Dec 2008 17:59:47 GMT

Hi everyone,I have a DTS package that controls the load of data from one database to another. There's a particular Execute SQL task in the DTS package that runs a stored procedure called stBT_DYNAMIC_INSERT_TEST, which inserts data from a temporary table into another database table.When I run the DTS package as a package, the Execute SQL task falls over with "Could not find stored procedure dbo.stBT_DYNAMIC_INSERT_TEST." Now, the stored procedure definitely exists, is owned by dbo, and the user logging into the database has permissions to run the stored procedure. Here's the kicker - when I execute that Execute SQL task as a task, it works. When I parse the SQL, the parsing is successful, and if the stored procedure didn't exist, I wouldn't be able to parse the SQL.I have tried deleting and recreating the stored procedure, recreating the SQL code from another file, I've even completely rebuilt the entire DTS package. Nothing works - this task will not run when the entire package is run, only when the task is run individually.Thanks for any help.

Not able to dump the one month records from main table

balaji.ganga 68339 — Sun, 25 Mar 2012 22:40:14 GMT

Hi Everybody,i need to take one moth data from the main table. it will be stored into temp table. while executing the below query. it shows invalid column name..------------ query ---------------------------------------insert into tempdb.dbo.temp1 select a.id,[a.current],a.name,a.age,a.sex from dbo.employee a join sales b on a.empid=b.empidwhere a.datetime > '02/01/2012' and a.datetime < '03/01/2012'current is the keyword. so that i put [] brackets. ------------------------------------------------------------Error Msg :Invalid column name a.current.------------------------------------------------------------But the current column is exist in an employee table.i don't know, why the error is occurred?I would really appreciate, if anybody can helps me to fix it.RegardsBalaji G

AS Cube Processing Fails

katuil — Mon, 09 Nov 2009 13:16:46 GMT

HelloFirst let me give some back ground information on this issue. My company has a very old server (1999/2000) that is running SQL 2000 with AS. The hardware or server is ready to die and I have been asked to move this off the hardware and onto a newer hardware. The new server is in place, running Server 2003 R2 instead of Server 2000 and SQL 2000 with AS. I can get my data warehouse loading processes to run fine and all the data in the databases match exactly. There are some minor differences in the SQL patches and updates, which I will explain as I go. Here's the problem, I can get some cubes to process without any errors while others will not process (log file is attached, scroll to bottom to see the error) and then errors. Here's what I have done:1-Backed up and restored all the databases for the data warehouse and AS.2-Migrated all DTS packages and pointed them to the new server.3-Pointed the new AS to look at the new server databases instead of the old server.4-Ran tests to check for row counts and tests to compare the data table by table.5-Opened the Cube that fails to process and can look at the data in the supporting views and fact tables (not the processed data) without any errors. So I can see the raw unprocessed data.6-Some of the objects use common dimensions, which I processed first, but according to one of the individuals that helped setup the data warehouse, cube processing order does not matter.Here is the error lines from the log file:Completed Processing Partition 'Demand Product 2009'. End time: 12:45:52 PM Duration: 0:14:49 Rows processed: 11405108Completed Processing Cube 'Demand Product 2009'. End time: 12:45:52 PM Duration: 0:14:58Committing transaction in Database 'SE_Demand_Sales'Analysis Server Error: Connection to the server is lost; Time:11/9/2009 12:45:52 PMError(-2147221411): Process operation failed; Time:11/9/2009 12:45:52 PMError(-2147221423): Unable to open server object (Internal (Database 'SE_Demand_Sales' could not be opened on the server)); Time:11/9/2009 12:45:52 PMRolling back transaction in Database 'SE_Demand_Sales'Analysis Server Error: Connection to the server is lost; Time:11/9/2009 12:45:53 PMOkay I have looked up time out issues and have matched the settings on both servers, no change still get this exact error. I have even pushed out the time out window to the max settings, still get the same error. I am lead to think that the error is not a time out issue but some thing else. The account that this runs under has full permissions to the data warehouse and AS as an administrator. There are other things that I have tried but they as well did not work. Another thing that I tried was to open AS Manager on the old server and backup each AS Database and then restore those to the new server. I then changed where the data connections pointed and any other settings that pointed to the old server to point to the new server. Still get the same error. My next step is to call Microsoft. If anyone can suggest anything please do, would rather not call Microsoft. This is the last item in order to turn off the older server. One thing that I did try is that the old server is running SP3a so one fix that I found was to apply SP4, no change there either. Thanks John

Excel connection to sp won't refresh

peteroc — Wed, 21 Mar 2012 21:01:46 GMT

Hi,I've been creating a few parameterised reports in excel which link to sql server stored procedures. This has been working very well but the latest one I've created is failing to refresh. The sp's that are working are all basic selects. The one that isn't is creating a table, executing a couple of other sp's to insert data into the table then selecting from that. Is it possible to make this work and return the data to excel?Here's the code:[code="sql"]CREATE procedure dbo.sp_EHC (@DateStart DateTime, @DateEnd DateTime)asDECLARE @RC intIF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'EHC') DROP TABLE EHCCreate Table EHC (OperationDate datetime,MachineNumber int,Model nvarchar(30),Make nvarchar(50),Type nvarchar(50),ClockHours int,OperatorHours float(8),FuelUsage int,CostCode nvarchar(20),Units int,Rate int,Amount int)Insert EHCEXEC @RC = [MSDSQL].[dbo].[sp_EquipmentHourlyCostReport] @DateStart, @DateEndInsert EHCEXEC @RC = [MSDSQL].[dbo].[sp_EquipmentHourlyCostReportTL] @DateStart, @DateEndSelect * from dbo.EHCGO[/code]Any help would be much appreciated,Peter

Disable and enable trigger

anbillava — Mon, 19 Jan 2009 17:44:10 GMT

Is there any way to enable and disable triggers during transaction. ?

Virtual Cooperation (mughal959)

awmughal959 — Mon, 19 Mar 2012 11:19:45 GMT

Virtual Instruments helps IT organizations maximize the performance, utilization, and availability of Fiber Channel Storage Area Networks (SANs) and other Online Services. [url=http://www.virtualonlinejobs.net]http://www.virtualonlinejobs.net[/url]

Compare two databases

Khalidhussain — Wed, 24 Sep 2003 03:59:00 GMT

I need to compare two databases (e.g. Development DB and Test DB) to make sure they are 100% identical. Is there a way to create a SQL view, which can do this job for me? I want to compare following:Table name in Development and Test databasesTable structure in Development and Test databases (including identity seed and identity increment in each table)View name in Development and Test databasesView structure in Development and Test databasesStored procedure name in Development and Test databasesStored procedure structure in Development and Test databasesWe have ‘system data’ tables they hold data, which we need in our VB6 system.These tables can be identify as prefix ‘sysd’ and table name e.g. sysdStatus hold all possible statuses for a policy.I need to compare those system data in Development and Test databases as well.Thank you

EXCEPTION_ACCESS_VIOLATION

jwmadison — Mon, 26 Aug 2002 07:13:00 GMT

This is an error message that happens several times daily (SQL 2000):Error: 0, Severity: 19, State:0SqlDumpExceptionHandler: Process 59 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.I have checked the error log, but it doesn't provide any addition information. There are no other problem with this server, and the error does not seem to cause any problems for users (that I am aware of). But I would be so happy to stop this from happening, if possible.Also, SP 2 has already been installed.Any help or advice would be greatly appreciated!

Passing values of the table variables from the child package into the parent package

ray.abak-1147615 — Thu, 01 Mar 2012 03:14:03 GMT

I have over 100 child packages, each designed to load a specific flat file into its own table due to differences in the structures of the data. I am trying to call each child package from the parent package by pointing the latter to the former in the File System using Execute Package Task that sits in a Foreach Loop Container. The enumerator type selected in the Collection tab is "Foreach From Variable Enumerator". I have an Excecute SQL Task (just before the Execute Package Task) whose Parameter Mapping is populated by a table variable name (vTempTableName - see below). The SQLStatement property of the Execute SQL Task has a stored procedure which truncates the staging table. The stored procedure expects the table variable name to be passed to it as a parameter and this seems to work fine. Outside the Foreach Loop Container is a Script Task that gets a list of data files, which the former loops through. The parent package has, among others, the following variables: [b]NAME SCOPE DATA TYPE VALUE [/b] DataFileList Parent Object System.Object DataFilePath Parent String PkgName Parent String E:\GEH_SSIS\MR5\MR5 PKName Parent String vTableName Parent String vTempTableName Parent String The variable settings in the child package are as follows: [b]NAME SCOPE DATATYPE VALUE [/b] FileName Child String PkgName Child String E:\GEH_SSIS\MR5\MR5 PKName Child String ADMDC_REFNO vTableName Child String S_ADM_DECISIONS vTempTableName Child String TMP_ADM_DECISIONS I have added a parent package variable package configuration in the child package to pass the parent variable (DataFilePath) value into the child variable (FileName). Inside the Foreach Loop Container, just after the Execute Package Task, is another Execute SQL Task whose Parameter Mapping is configured as below:[b]VARIABLE NAME DIRECTION DATATYPE PARAMETER NAME PARAMETER SIZE [/b] User::vTableName Input Varchar 0 -1 User::vTempTableName Input Varchar 1 -1 User::PKName Input Varchar 2 -1 The Execute SQL Task has a stored procedure to which three parameters must be passed (EXEC usp_ManageCDC_Test ?,?,?). [code="sql"]Create Procedure [dbo].[usp_ManageCDC_Test] @TableName Varchar(100), @TempTableName Varchar(100), @PrimayColName Varchar(100)AsSet @SQL = 'DELETE ' + @TableName + 'WHERE EXISTS (SELECT TMP.' + @PrimayColName + ' FROM ' + @TempTableName + ' TMP WHERE ' + @TableName + '.' + @PrimayColName + '=TMP.' + @PrimayColName + ')INSERT INTO ' + @TableName + ' SELECT * FROM ' + @TempTableName + '' [/code]Upon execution, the parent package calls the child package and loads the data into the staging table (TMP_ADM_DECISIONS). My problem is even though it completes successfully, it does not transfer the data from the staging table to the storage table .(S_ADM_DECISIONS). When I insert this script ("EXEC usp_ManageCDC_Test " + @[User::vTableName] + "?," + @[User::vTempTableName] + "?," + @[User::PKName] + "?") in the expression property - SQLStatementSource, I get the same results. When I move the values of table variables from the child package and place them in the parent package, it runs and transfers the data from the staging to the storage table successfully. However, this is not how I want it to work as it would mean creating different variables for each table in the parent package. I want to use the same table variable names (created in the child package) for each flat file even though they are being loaded into different tables. Assuming the above stored procedure (usp_ManageCDC_Test) was working as expected, i.e transfering data to the storage table, I would know whether the next child package would be called or not. As things stand, I can't say for sure that the next package would be called since the package completes without any errors (and/or wihout calling the next child package).I have tried adding a child package variable package configuration in the parent package to pass the child variable values into the parent variable but it has not worked either. I want the parent package to call each child package and load every flat file type one at a time. I have got two queries:1) How do you get the values of the table variables from the child package into the parent package so that data in the staging table can be copied into the storage table? 2) Assuming the parent package is working (in its current state) simply because the Connection Manager I have named "CALL_CHILD" is pointing to the child package (CHILD_ADM_DECISIONS.dtsx), how do you configure the package to call other child packages? Thank you so much in advance for your help. Ray

Issue of Port Binding in Active Passive Clustering ( SQL 2000 SP4 version)

chetanr.jain — Fri, 24 Feb 2012 04:57:26 GMT

Hello Team,I have faced a strage issue.I am having SQL 2000 Cluster (Active - Passive) on Windows 2003. There are 4 instancesInstance D - 8.00.760Instance E - 8.00.2039Instance F - 8.00.2039Instance G - 8.00.2039.We have a unexpected failure of the Node A due to which Instance D, E & G are failed over to Node B.All instances came up but we got an error in instance G where it was not able to connect to its static port. (SuperSocket Info: Bind failed on TCP port 2539). All other instances were running perfectly on its static port.After 3 hours of effort, we found that Agent process of Instance F has captured the said port (2539). We stopped the Agent resource of Instance F and restarted the Instance G. Problem was solved.However, i am still not able to find reason why at all the port was captured by Agent resource of Instance F.Does anybody faced any issue like that or any info.Thanks in advance.

converting column data as column name

visaforuk — Wed, 22 Feb 2012 22:42:13 GMT

Hi , I have a query which return the data like in sql serverparam_value display_value64.60.231.79 BROADCAST_20 GATEWAY_264.60.231.72 IP_SUBNET_22 IP_TYPE255.255.255.248 NET_MASK_274-78 USABLE_RANGE_264.60.231.73 WAN_IP_2Now i want the right side output to make as a column nameBROADCAST_2 GATEWAY_2 IP_SUBNET_2 IP_TYPE NET_MASK_2 USABLE_RANGE_2 WAN_IP_2and the param_values should be below that in a row.How can i do thatPFB for my queryselect a.param_value , p.display_value from service_ext_data a , param_values pwhere view_id in (select top 1 view_id from service_viewwhere subscr_no in (select subscr_no from ord_service_order where service_order_id in (select service_order_id from ord_service_order where order_id = 405767003and wp_process_id = 'Rcnfg-Circuit')) ) and p.param_id in(10268,10298,10354,10170,10485,10474,10270,10326,10261)and p.param_id = a.param_idcan some one help me please

Missing Emails -> Undeliverable

Martin M — Tue, 21 Feb 2012 07:47:35 GMT

Got an oddity happening in our SQL2K environment. When an invalid internal domain email appers in To, CC, or BCC, all for the domain are killed. When we sent using Groupwise, external emails were not impacted. Now that we've migrated to Exchange 2007, the externals are dying too. When testing through Outlook or from the SQL2008 box, there are no problems.To: MyName@Domain.com; MyNaame@Domain.comCC: MyName@Yahoo.comGroupwise would send the email to Yahoo. Exchange kills all of it. This is a major problem with our monitoring tools and the recent shift in naming convention from LOGIN@domain.com to First.Last@Domain.com and the heavy imbedding of personal emails in SPs and DTS packages.Our hardware support team is point the finger at SQL. We've shown that its just this one machine...our primary production box that will be in use for at least another 3 years before enough code is rewritten to SQL2008 and .Net for it to be shutdown.Any thoughts? I am not sure of the email setup. Its an inherited box that our outsourced hardware team has at least one teammate FUBAR beyond relief. Fairly sure its MAPI, but beyond that not real sure.Thanks,

Large amount of account

tommyh — Tue, 21 Feb 2012 05:21:40 GMT

HiWas wondering if anyone has had any experiance with having a large amount of user accounts in SQL and potential problems/bottlenecks. Im looking at having something like 10-20000 unique SQL accounts (potentially more). With maybe like 100-200 concurrent users. The concurrent users is offcourse not a problem, its low. But thought it might be useful information. SQL version is currently 2000 but might be 2008 or if lucky 2012. /T

Raima Database Data Import

Greg Brunacci — Fri, 04 Jul 2008 16:20:20 GMT

Have many old raima database that i need to import into SQL databases for review. How can this be done? does anyone have that experience?

Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32)

SG_Explorer — Thu, 22 Jan 2009 23:51:20 GMT

I have a Stored Procedure and getting a Error "Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32)".Please help.

Ignore Data if duplicate

rheinhardtp — Wed, 15 Feb 2012 01:26:52 GMT

Hi All, I am very new to databases so please bare with me. I have the following Stored Procedure.CREATE PROCEDURE VRWatsysProcASINSERT INTO dbo.VRWATSYSTABLESELECT *FROM [ZAVRCICAHS1].[Environmental].[dbo].[EnviroTable]ORDER BY DateTimeGOMY problem is there are records in my source table [ZAVRCICAHS1].[Environmental].[dbo].[EnviroTable] that do not change, in this case i want to ignore these rows and not transfer them into my destination table dbo.VRWATSYSTABLE. What is the best method to do this??DO i first transfer the complete table and then check for duplicates and delete them or can i prevent the duplicates in my select query from being inserted into the table.Rheinhardt

CDO.Message.1 The "SendUsing" configuration value is invalid.

Borik — Wed, 07 Jun 2006 14:39:00 GMT

I am trying to Send Email from SQL 2000 on Win 2003 using a remote SMTP.i am not running smtp serive on that machine... and have no iis, but CDOSYS.dll is present...Please help......CODE------------------------------DECLARE @From as varchar(50); DECLARE @To as varchar(50); DECLARE @Subject as varchar(255); DECLARE @Body as varchar(512); DECLARE @message int; DECLARE @config int; DECLARE @hr int; DECLARE @src varchar(255), @desc varchar(255)SELECT @From = 'a@b.com'; SELECT @to = 'c@d.com'; SELECT @Subject = 'Subjecttext 3';SELECT @Body ='MessagetextEXEC @hr = sp_OACreate 'CDO.Message', @message OUT -- create the message objectEXEC @hr = sp_OACreate 'CDO.Configuration', @config OUT -- create the configuration objectEXEC @hr = sp_OASetProperty @config, 'Fields(cdoSendUsingMethod)', 'cdoSendUsingPort' -- Send the message using the networkEXEC @hr = sp_OASetProperty @config, 'Fields(cdosmtpconnectiontimeout)', '10' -- Send the message using the networkEXEC @hr = sp_OASetProperty @config, 'Fields(cdoSMTPServer)', 'mail.smtpserver.com' -- SMTP ServerEXEC @hr = sp_OASetProperty @config, 'Fields(cdoSMTPServerPort)', 25 -- Server SMTP PortEXEC @hr = sp_OASetProperty @config, 'Fields(cdoSMTPAuthenticate)', 'cdoBasic' -- Anonymous SMTPEXEC @hr = sp_OASetProperty @config, 'Fields(cdosendusername)', 'user' -- Anonymous SMTPEXEC @hr = sp_OASetProperty @config, 'Fields(cdosebdpassword)', 'pass' -- Anonymous SMTPEXEC sp_OAMethod @config, 'Fields.Update'-- Message ObjectEXEC @hr = sp_OASetProperty @message, 'Configuration', @config -- set message.configuration = configEXEC @hr = sp_OASetProperty @message, 'To', @ToEXEC @hr = sp_OASetProperty @message, 'From', @FromEXEC @hr = sp_OASetProperty @message, 'Subject', @SubjectEXEC @hr = sp_OASetProperty @message, 'TextBody', @BodyEXEC sp_OAMethod @message, 'Send()'EXEC sp_OAGetErrorInfo @message, @src OUT, @desc OUTSELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc-- Destroys the objectsEXEC @hr = sp_OADestroy @messageEXEC @hr = sp_OADestroy @config-- ErrorhandlerIF @hr <> 0BEGINEXEC sp_OAGetErrorInfo @message, @src OUT, @desc OUTSELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc--RETURNEND

Trigger gives error on filre of bulk update query

KcV — Wed, 08 Feb 2012 03:37:08 GMT

Hi,I use SQL 2000.I have a master table in which i save Master details like Name,contactNo etc..I have a trigger on this master table on INSERT and UPDATE. (in this tirgger i bifurcate contactNos into different categories, and save in other table.)In normal condition Query and Trigger are working OK. but when i fire a query like ( which updates bulk records in table ) :"Update EmpMaster set contactNo ='12345678' where empid < 200"On fire of above query error comes as under :"Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."In the trigger i have used Inserted and Deleted tables to check whether No. is updated or not.Yes, there is subquery in my trigger.Thnks.:angry:

Replace Function issue

balaji.ganga 68339 — Fri, 03 Feb 2012 00:35:57 GMT

Hi,I have created one table. it contains id, textdata columns, datatype is text only. It has more than 10000 characters stored for a single textdata column.I need to remove the specific line from the textdata column. So i used the below function.select replace(textdata,' -----sample data are available','')if i used the above query. it throws an error. like Argument datatype text is invalid for argument 1 of replace function. Could you please help us to resolve this issue..Thanks in AdvanceRegardsBalaji G

Junk value getting inserted into table

rahul.rahuzz — Mon, 26 Dec 2011 06:27:26 GMT

We have one application in which we insert into table (say A) from table B INSERT INTO A SELECT B.IPT_CODE FROM B INNER JOIN PM ON PM.P_CODE = B.P_CODE.At client place we are getting junk values(example ´) in table A some times but there is no junk value in table B.Junk value like 'ABC-218005P19´' instead of 'ABC-218005P194'.This does not happen regularly and is happening for other columns in other client Database as well in a week time or so.Its happening in Sql server 2000 and Sql server 2005.What can be the reason for this.Please guide..

I want to optimise the query

vikram_prince2003 — Mon, 06 Feb 2012 08:12:36 GMT

Here is the query, it takes 13 seconds to execute the query. By somehow I want to reduce the execution time.I want to execute this query less than 5 seconds. Please help me outSELECT DISTINCT(C.DPCodes) [ICD9Code],--ICD9 Code CASE S.ICD_10_DCode WHEN 'NoDx' THEN 'NoDx' WHEN 'NO' THEN 'NO' WHEN NULL THEN 'NA' ELSE S.ICD_10_DCode END [ICD10Code],--ICD10 Code CAST(S.Approximate_Flag AS char(1))+ CAST(S.No_Map_Flag AS char(1))+ CAST(S.Combination_Flag AS char(1))+ CAST(S.Scenario AS char(1))+ CAST(S.Choice_List AS char(1)) [Flags],--Combining all flags al one value CASE-- WHEN ( SELECT COUNT(*) FROM TBL_ICD_FORWARD_D_GEMMAPPINGS WHERE ICD_9_DCode=S.ICD_9_DCode )=1 THEN 'YES' ELSE 'NO' END [Unique],--Unique Mappings ( SELECT Count(*) FROM TBL_ICD_837_DPCodes WHERE DPCodes=C.DPCodes ) [Count]--Count of code occurence FROM TBL_ICD_837_DPCodes C Left JOIN TBL_ICD_FORWARD_D_GEMMAPPINGS S ON C.DPCodes = S.ICD_9_DCode AND S.Default_Flag=1 WHERE C.Qualifier IN ('BK','BJ','PR','BN','BF') AND C.File_Id --Searchable file ID IN ( SELECT File_Id FROM TBL_ICD_FileSubmission WHERE Transaction_Name ='837' AND Search_Flag='True' AND CONVERT(VARCHAR(11),creation_date) --Searchable date IN ( SELECT creation_date FROM TBL_ICD_FileDates WHERE files_status = 'True' AND transaction_name = '837' ) ) ORDER BY [Count] DESC, [ICD9Code] ASC

ODBC not showing SQL 2000 tables

gweiho4xd2 — Thu, 19 Jan 2012 07:33:38 GMT

Hi,Please can someone help me with the following:I can't see any tables (or views) in MS Access (ODBC) when I attempt to add a NEW linked table to ONE of our SQL 2000 databases.I can successfully link/import data via ODBC from other databases on the server (SQL 2000 SP3,NT4 SP6a).Previously linked tables in MS Access work fine, it's only new links ones which are the problem.It was working fine on the 22-Dec-11 and nothing has been knowingly changed on the server.I've tried 5 different machines (all XP) with various versions of MS Access (2000,2002,2003); all of them have the same problem.How can I see the SQL tables/views in MS Access (Get External Data, Link Tables..)?ThanksSimon

Query Time out

indraneelkurane — Wed, 01 Feb 2012 05:35:54 GMT

We have a table with 150000 records. It has a primary key on 3 columns and a index on 5 columns. When a select query is executed on this table usully it takes 6 seconds to execute but some time it takes more than a 60 seconds and then 'Query Time out' message is reported.This problem is seen intermittently.What could be the solution for this? or What approach I should follow to derive proper solution?

Using the ROWGUIDCOL attribute on primary key

jonashilmersson — Wed, 14 Sep 2005 04:42:00 GMT

Hi!I'm using unqiueidentifiers as my primary keys for all tables with a default value of NEWID(). Are there any advantages/disadvantages of adding the ROWGUIDCOL attribute on the primary key column?TIAJonas

Tool for Code review

Sudarsan Srinivasan — Thu, 06 Mar 2008 06:21:58 GMT

Hi All, Can anybody suggest me any tool which can be used to automate the code review for SQL 2000 apart from MICROSOFT's SQL BPA Thanks in advance

SQL-08S01

Erico Galdino Almeida — Thu, 03 Feb 2005 06:16:00 GMT

Hello All

I'm facing a problem with an application, but I couldn't solve it even with the posts that I found here.

Enviromemnt:I have one server with an LMS (Blackboard - Win2003 - SQL Server 2000 Client Tools - JAVA) and a Database server (Win2003 - SQL SERVER).Application and Database are running in different networks.

Problem:When I access the home page of the application, sometimes it appears an error message: "Database Error" Then, if I just reload the page, one or two times, it works properly, with no error message at all. After one hour or so, the same error occurs again.

In the log messages of the application I have the following:*** 'E:\blackboard\bin\entry_rightpanel.pl' log message at: 2005/02/01 17:08:00[Tue Feb 1 17:08:00 2005]: can't execute: SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT pk1,sos_id_pk2,bgcolor,vlink,link,short_name,page_title,name,vhost,homepage,style,course_image_main_src,course_image_main_link,course_image_main_name,course_image_2_src,course_image_2_link,course_image_2_name,course_image_nav_src,course_image_nav_link,course_image_nav_name,courses_row_height,org_image_main_src,org_image_main_link,org_image_main_name,org_image_2_src,org_image_2_link,org_image_2_name,org_image_nav_src,org_image_nav_link,org_image_nav_name,portal_options_banner_src,portal_options_banner_link,portal_options_banner_name,portal_options_icon_set,portal_options_support_link,background_image_src,portal_src,portal_row_height,reg_level_ind,gateway_src,renew_date,contact_person,contact_phone,street_1,street_2,city,state,zip,country FROM virtual_hosts WHERE LOWER(vhost) = ? AND row_status = 0 ARGS: gead-black02 ERR: [Microsoft][ODBC SQL Server Driver]Communication link failure (SQL-08S01)(DBD: st_execute/SQLExecute err=-1)

And this one (I dropped the query):execute failed: [Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]ConnectionWrite (send()). (SQL-01000)[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]General network error. Check your network documentation. (SQL-08S01)(DBD: st_execute/SQLExecute err=-1)

Could you please help to find where the cause of this error can be?

I'm struggling on this! Don't know where to look for anymore.

Regards,Erico Galdino Almeida

Create data for missing date

shivani.kataria — Fri, 13 Jan 2012 15:10:12 GMT

I have a problem where i have data for last 5 year and its 7 days apart each date.Say i have data like:Hotel| date| expenseXXX | 1 Jan| 23XXX | 7 Jan | 34YYY| 7 Jan | 34YYY| 21 Jan 34XXX| 28 Jan | 45Data is missing for XXX for 21 Jan and for YYY for 1 and 28th Jan. What i need to do is YYY never existed before 7 Jan , expense for YYY for 1 Jan is zero but if record existed last week or any week before current week just insert missing weeks data with last occured data.Any suggestions deeply apprciated.

T-SQL programming question

cmartin 18519 — Wed, 11 Jan 2012 23:17:18 GMT

I have a tough programming problem in SQL Server 2000 that I'm hoping I can get help with. I am a novice with T-SQL (I mostly use MySQL) so maybe someone could look at this and see an easy way to do it. I have a table with lots of columns that contain either a 0 or 1. For example, I have columns labeled var1, var2, var5, var9. etc. up to about 120 columns total. Notice that the suffixes on these columns aren't necessarily consecutive. Each of these columns contain a 0 or 1. What I need to do is create a new column the contains the column[i] names[/i] of every column that contains a 1.For example, my data look like this:ID var1 var2 var5 var7 var91 0 1 0 0 12 0 1 1 1 0I need a new column that contains the valuesID NewCol1 var2,var92 var2,var5,var7and so on. For reasons I won't go in to, I can't use any client side code like VB, Perl, etc. I have to do this in a stored procedure. I feel really limited by my T-SQL knowledge. Can this be done??TIA

TEMPDB' because the 'DEFAULT' filegroup is full

Alone — Wed, 21 Dec 2011 08:51:43 GMT

I have one DTS configured in prod failing suddenly with below error msg:Could not allocate space for object '(SYSTEM table id: -210087109)' in database 'TEMPDB' because the 'DEFAULT' filegroup is full. ---------I have done below activities till now but no luck:1.Failover the cluster server2.Drive has enough space.3.Created ndf file in different drivePls advise..