Get Server Name Automatically In DTS ActiveX
Nov 2, 2000
I'm looking for a way to get the name of the server on which the DTS package lives.
I copy packages between servers. The problem is that everytime a package is copied
to different server, I have to change the reference in the connection strings to point
to the new server name. I'd like to find an automatic way to interrogate the server
name where the package currently lives and dynamically change connection strings
from within an ActiveX task. That would cut maintenance way down.
View 1 Replies
ADVERTISEMENT
Jul 18, 2000
Thanks in advance for your help...
I have been trying to create, then call an activeX object. I use sp_OAcreate and then sp_OAmethod to create the object, the call the method within. I have registered the DLL but I can not get it to work. I used the examples within T-SQL help but it doesn't seem to work. I am calling from within a sp within the northwind db. I need to pass 4 parms to the method. Here is the proc.
CREATE PROCEDURE SIMPLE2_SP
(@CUSTOMERID nVARCHAR(25) = NULL,
@ORDERID INT = NULL)
AS
DECLARE @BLOB nVARCHAR(1200),
@PARM1 nVARCHAR(25),
@SF INT,
@pOBJ INT
/***Make sure that the customerid is specified, if not, get out. sfogli 7-17-00
***/
IF (@CUSTOMERID is NULL) OR (@CUSTOMERID = '')
BEGIN
RAISERROR('Must specify a CUSTOMER name.',-1,-1)
RETURN (1)
END
/***Hardcode the path, customerid, and orderid for testing purposes. sfogli 7-17-00.
***/
--SELECT @BLOB = 'PATH=C:REPORT.HTM;CUSTOMERID=' + @CUSTOMERID + ';' + 'ORDERID=' + CONVERT(VARCHAR(5),@ORDERID)+ ';'
SELECT@PARM1 = 'PATH=C:REPORT.HTM;'
/***Append BLOB with revelant data from customer table, sfogli 7-17-00.
***/
SELECT @BLOB = (SELECT'CustomerID=' + CUSTOMERID + ';' +
'CompanyName=' + COMPANYNAME + ';' +
'ContactName=' + CONTACTNAME + ';' +
'ContactTitle=' + CONTACTTITLE + ';' +
'Address=' + ADDRESS + ';' +
'City=' + CITY + ';' +
--Contains null values'Region=' + REGION + ';' +
'PostalCode=' + POSTALCODE + ';' +
'Country=' + COUNTRY + ';' +
'Phone=' + PHONE + ';' +
'Fax=' + FAX + ';'
FROMCUSTOMERS
WHERECUSTOMERID = @CUSTOMERID)
/***Make sure that the orderid is specified, if not, raise message. sfogli 7-17-00
***/
IF (@ORDERID is NULL) OR (@ORDERID = '')
BEGIN
RAISERROR('To continue process, please specify an ORDERID and retry.',-1,-1)
PRINT @BLOB
RETURN (1)
END
/***Append BLOB with revelant data from orders table, sfogli 7-17-00.
***/
SELECT@BLOB = @BLOB + (SELECT'OrderID=' + CONVERT(VARCHAR(5),ORDERID) + ';' +
'CustomerID=' + CUSTOMERID + ';' +
'EmployeeID=' + CONVERT(VARCHAR(7),EMPLOYEEID) + ';' +
'OrderDate=' + CONVERT(VARCHAR(15),ORDERDATE) + ';'
/***'RequiredDate=' + REQUIREDDATE + ';' +
'ShippedDate=' + SHIPPEDDATE + ';' +
'ShipVia=' + SHIPVIA + ';' +
'Freight=' + FREIGHT + ';' +
'ShipName=' + SHIPNAME + ';' +
'ShipAddress=' + SHIPADDRESS + ';' +
'ShipCity=' + SHIPCITY + ';' +
'ShipRegion=' + SHIPREGION + ';' +
'ShipPostalCode=' + SHIPPOSTALCODE + ';' +
'ShipCountry=' + SHIPCOUNTRY + ';'
***/
FROMORDERS
WHEREORDERID = CONVERT(VARCHAR(5),@ORDERID))
EXEC @SF = SP_OACREATE "PMIDocHandler.HTMLDocs", @pOBJ OUT
IF @SF <> 0 GOTO ERR
--(sPath, sCustomer, sContractID, sKeyValuePairs)
EXEC @SF = SP_OAMETHOD @pOBJ, "CreateDocument" ,@PARM1, @CUSTOMERID, @ORDERID, @BLOB
IF @SF <> 0 GOTO ERR
ERR:
RAISERROR('THIS IS ONLY A TEST.',16,1)
--SELECT @BLOB AS 'VARIABLES PASSED'
RETURN
View 2 Replies
View Related
Nov 2, 2000
I'm looking for a way to get the name of the server on which the DTS package lives.
I copy packages between servers. The problem is that everytime a package is copied
to different server, I have to change the reference in the connection strings to point
to the new server name. I'd like to find an automatic way to interrogate the server
name where the package currently lives and dynamically change connection strings
from within an ActiveX task. That would cut maintenance way down.
View 2 Replies
View Related
Mar 21, 2007
Because the IIS threads are recycled at night, the first user that calls up Report Manager in the morning has to wait 30 seconds before the page loads, as IIS is creating a thread.
I'd like to set up a job in SQL Agent that will navigate to http://localhost/reports/ every morning before users come in to work, so they won't have to wait.
I think a SAL Agent job of type ActiveX Script would be suitable for this, so I need to write the code to navigate to the reports page in VBScript.
Anyone knows how to navigate to a page in VBScript ?
tia.
View 4 Replies
View Related
Mar 29, 2006
Hi all
I am migrating some DTS2000 packages to SSIS via the migration wizard. However there is an activex script in each DTS that connects to an AS400 to retrieve record count for integrity checking. Under SQL server 2000 this worked fine but now it errors with "The OLE DB provider "MSADSQL" has not been registered" how do i get this to work i suspect its because this is a 32 bit dll and my new system is a 64bit sql server on windows 2003. The odbc driver for the as400 has been installed also. The script is as follows:
dim cn 'sql connection
dim rs 'sql recordset for the insertion of new as400 records
dim insertstr 'sql insert string
dim rs1 'sql recordset for the insertion of new as400 records
dim insertstr1 'sql insert string
set cn = createobject("ADODB.Connection")
set rs = createobject("ADODB.Recordset")
Function Main()
'set up connection for sql
cn.provider="sqloledb"
cn.commandtimeout = 3600 'timeout in seconds
cn.open %sqlserver05%, %user%, %password%
'populate temporary table in sql with recordset from as400 odbc
insertstr1 = "Insert into db.dbo.%table% select 'table name',a.* from openrowset ('MSDASQL','DRIVER={Client Access ODBC Driver (32-bit)};SYSTEM=%system name%;UID=%username%;PWD=%password%','SELECT count(*) from %table%') as a"
set rs1 = cn.execute (insertstr1)
'close all objects
cn.close
Main = DTSTaskExecResult_Success
End Function
Does anyone know how i change this to work under sql server 2005. i need to resolve this quickly in order to deliver a project on time. Many thanks
Chris
View 6 Replies
View Related
Feb 27, 2007
when i run activex Script it's shows this error
[ActiveX Script Task] Error: Retrieving the file name for a component failed with error code 0x001B6438
View 2 Replies
View Related
Jan 21, 2008
Hi All,
I' m trying to execute activeX script from a job. I could do it successfully. But when i tried to access data base the job is failed when running.
My Requirement : I would like to send a mail in a scheduled base using a job and activeX script.
I could send a mail giving static parameters. But as i need to make the parameter values of activeX script dynamic, i tried to connect data base and retrieve the required data as shown below.
When i parse the script it showed success.But this is failing when it is running.
while creating a job i had selected ActiveX script option with vbscript language
//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
Set con = Server.CreateObject("ADODB.Connection")
con.Open "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;password=welcome;Initial Catalog=MyDatabase;Data Source=(local); "
Set rs = Server.CreateObject("ADODB.Recordset")
Set rs = con.execute("select fromid,toid,seqid from MailsLog where status='N'")
while rs.eof <> true
con.execute ("update MailsLog set status='Y' where seqid = " & rs("seqid"))
wend
set rs = nothing
set con = nothing
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
Could any one please help me in this regard, of accessing activeX script with connecting to data base.
I' ll be so kind of any immediate response.
Thanking You
Ganesh
View 1 Replies
View Related
Sep 13, 2006
I have a DTS package that I brought over from SQL server 2000 in to SQL Server 2005. I have installed all of the legacy components to run the DTS packages but I need to debug an ActiveX script task. In SQL Server 2000 I could turn on Just-In-Time debugging and use the stop operator (in my vbscript) to break the running script and launch the debugger.
I don't see how to do this in SQL Server 2005 Management Studio. Is it possible to debug a script object in a DTS package running in SQL Server 2005?
Jay Abbott
View 1 Replies
View Related
Feb 4, 2015
I need to load the latest csv files from file server , The files are placed in a folder called -
Posted 02022015- --> csv files .
I am able to copy the csv files from filserver using bulk insert (manually) , giving the file location
I am having difficulty picking up the latest folder which is posted on the server and import it into database using a stored proc .
View 2 Replies
View Related
Mar 9, 2007
How do I stop a remote query that INSERTS into a local table from being automatically "upgraded" to a distributed transaction?
I am using Windows 2000 server and SQL Server 2000 SP3a on both machines.
I am executing the following statement in Query analyzer.
INSERT MyLocalServer (col1)
EXECUTE MyRemoteServer.Master.dbo.sp_executesql
@RemoteQuery,
@ParameterDefinition,
@Paramter = 'somevalue'
@RemoteQuery consists of a SELECT four-table join, all tables are on the same linked server.
The Linked server has been set up on MyLocalServer using the "Microsoft OLE DB for SQL Server" provider. In the "Provider Options" for the linked server properties I checked "Non transacted updates" and "dynamic parameters". In the "Server Options" tab I have checked "RPC", "RPC Out", "Data Access".
The EXECUTE part of the query runs great (and returns the data very fast) by itself. But with the INSERT part, the query fails and returns the error:
"Server: Msg 7391, Level 16, State 1, Line 17
The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a]."
The two servers are seperated by firewalls, so I believe the reason the query is failing is that I haven't followed the procedures for setting up the ports etc described in one of the microsoft support articles: e.g.: 250367.
Configuring the ports involves too much company politics, and besides, for what this query does, it does not need the benefits of a distributed transaction.
How can I execute my query without SQL Server automatically trying to upgrade it to a distributed transaction?
More Info: I can execute the query as a straight INSERT/SELECT linked-server query and it does the INSERT on the local SQL Server just like I want it to, so I assume it is not trying to use distributed transactions; but it takes around 7 seconds to run even though the entire SELECT is executed on the linked server, whereas executing with sp_executesql takes only 1 second.
I thought selected "non-transacted updates" in the provider would solve this problem, but it did not.
Anyone know the answer?
View 2 Replies
View Related
Sep 13, 2001
I have table A and table B. i want if table A add records, automatically table b add records too.
thanks,
andi
View 1 Replies
View Related
May 7, 2002
I installed SQL Sp2 and at the same time took Carbon Copy off. No when the machine comes up neither SQL Server or the Agent will not come up. If I go to Service manager I can start both just fine. Nothing in either log to show why. Anyplace or any thoughts
View 2 Replies
View Related
Feb 18, 2004
Hi,
I have SQL server running on windows Adv Server 2000. Since last couple days whole computer restart every one hours. Only thing I remember doing was to shrink database db size is 200+GB.
How can I fix this problem.
Any help will be highly appreciated?
Thanks.
Samir
View 1 Replies
View Related
Mar 4, 2008
Hi all,
I am wondering if there is a way that the SQL server resets every few hours. I am using an application which is heavily utilising SQL server and the server crashes every now and then. I can reset it manually but I was wondering if there is a way that it detects the crash and reset itself automatically. Thanks a million in advance.
View 2 Replies
View Related
May 2, 2008
I have a laptop with vista and sql 2005. When i start my computer, after it boots up and is ready to go, I look in the task manager at the process tab. Some sort of SQL Server component is running and eating up about 50k's worth of memory. What is it and how do set either Vista or SQL Server to not start whatever this is unless I do it manually(oh yeah...how would I turn it on?)and what are the ramifications of not having it start up when i start my laptop?
I know it is a rather rambling question, but its the best I can describe it. Thanks in advance for your help!
View 4 Replies
View Related
Mar 3, 2008
I have installed sql server 2005 in cluster. after all the process is finished the memory usage is showing 2 gb where as in the activity monitor there is no process running. what should i do to clear the memory automatically.
If i stop and start the serverice it is cleared, nor do i want to run any command to clear the memory.
View 1 Replies
View Related
Mar 26, 2008
Hi all
I have a mainteenance Server,SQL Server 2000 Installed on that server,every day some time in mid-night Agent it self stoping
What may be the reason...where I have to find out reason for stop.
Any advice and Help is appreciated.
View 11 Replies
View Related
Mar 13, 2007
I been asked recently in the interview thatHow can I detect changes automatically in the SQL Server Database when anything is updated, deleted or inserted?if anyone can help me in this that will be really great.
I dont actually know whether I can ask this here but I wanted to know the answer for this and thought this might be right place to ask?
sorry if I am wrong?
Thanks in Advance
View 2 Replies
View Related
Jun 17, 2004
A section of this company's intranet site where I just started interning at has little company anniversary and birthday sections that look like (for the anniversary section.. in the birthday section, it looks the same, except it doesn't say how old the comployee is):
-Steve Cunningham 6/1 - 6 yrs
-Andrew Brown 6/3 - 11yrs
-Lisa Stone 6/4 - 3 yrs
How can I get it so instead of manually changing that text every month, it will look at a SQL database and automatically change that text every month? I'm guessing the pseudocode would be if the b-day or anniv. month matches the current month, display the first and last name, the date, and number of years (which would have to be calculated maybe?) Any help would be GREAT! Thanks!!
~Michael
View 2 Replies
View Related
Jul 25, 2014
We have two SQL 2012 servers, sever A and B.
We need to sync a database on A so that there is a copy of it on B.
We need to be able to query both databases so mirroring isn't an option.
View 7 Replies
View Related
Feb 6, 2008
Hi all,
I import MS Excel 2003 spread sheet in MS SQL Server 2000 through MS SQL Server 2000 Enterprise Manager (rightclick on the table to be filled with dataall taskimport data). My excel file have 2000 rows and 100 columns of data. All the data are imported in relevant attributes cells in good manner. But the rows are sorted automatically. I am trying to say that first row data is match with my excel file. But second row data have gone to 7th row and 7th row have gone to 5th row like that. I need the data sequence what I have in my excel file. What is the problem occurred? How can I solve this?
Can I export my MS Excel 2003 file to MS SQL Server database?
Please help me. I don't have more knowledge in MS SQL Server 2000.
If your answer has any query to run then please mention where should I run that query.
Thanks,
With Regards,
bala.
View 3 Replies
View Related
May 25, 2007
Hi!
How to install sql server 2005 automatically with sa password
I want to know that i am creating a setup in visual studio 2005, i have set up sql server as prerequsite, by default the sql server installation is silent, it does not ask anything, in MSDE we had the facility of setyp.ini file which automatically creates the database with the settings provided in the ini file. Is there any option like this in sql server 2005 express editon.
[Options]
INSTANCENAME="MSDE"
SECURITYMODE=SQL
SAPWD="admin"
now what can be done in sql server 2005 express edtion
View 1 Replies
View Related
Jul 17, 2015
I have developing sample application for automatically send failure mail to respective person. Also i have storing all the failure mail details to database for unavailability of mail server. Once if the mail server available that time we will send all the failure mail to respective person.
View 5 Replies
View Related
Jun 21, 2001
Can anyone direct me to a code that would automatically apply transaction logs to a DB in the standby server? We have a process that dumps the transaction log backup from the primary server into the backup server every hour on the hour, but I need to apply that transaction log as soon as it is in the standby server.
I am sure someone will ask, why not do transactional replication or log shipping? My answer to that is I have yet to learn how to setup replication between servers. I need to get our backup server up and running in the next few days.
Thanks in advance for your help.
Helen
View 1 Replies
View Related
Oct 5, 2006
This is the weirdest this I have ever seen in a long time. I have MS SQL Server running on a server and use Enterprise Manager a lot. Well, the damndest thing happens when I log onto the server from the console and run Enterprise Manager. If I go into Enterprise Manager, and go to a database and then select a table and right-click, and run the "Open Table" option; the entire Enterprise Manager application mysterously closes.
This only happens from the server console and through Remotely Anywhere...it does not happen when I log onto the server from Remote Desktop.
Has anyone ever seen this before? Does anyone know a fix for this?
View 1 Replies
View Related
May 16, 2008
I have a SSIS data flow task that downloads data from an Oracle source which has some real dates and some date values of 0001-01-01 which I am trying to convert to '1900-01-01' using a decode(Date1,'0001-01-01','1900-01-01',Date1). The problem I am having is that when I run the package in Business Intelligence Development Studio on my local machine the date value is stored correctly in the SQL table in a datetime field as 1/1/1900 12:00:00 AM, however when I run the package from the server the field gets saved as 1/1/2001 12:00:00 AM.
I have tried a bunch of ways to work around this issue and the only solution I have found that works it to download into a temp table and then load into the live table. Does anyone have any idea of what might be causing this issue when I run the package from the server?
View 1 Replies
View Related
Aug 17, 2015
I have a problem setting the default value of a column. I am trying to set it to
(CONVERT([float],getdate()+(2),0))
However, SQL Server automatically sets it to
(CONVERT([float],getdate()+(2),(0)))
While it functionally does not change anything, we have a tool which compares the database schema against a pre-existing schema and shows this as an error.I have tried setting the value directly and through scripts but it does not work either way.
View 2 Replies
View Related
Mar 8, 2008
All -
I have a SQL Server 2005 HTTP endpoint. I create a transaction scope in a client application and call two methods on that endpoint. Will the operations performed in the two methods, really, be in a transaction?
In other words, will SQL Server enlist in the ambient transaction even when I am accessing it through a HTTP endpoint?
Thanks.
View 4 Replies
View Related
May 13, 2008
Hello all,
We are deploying an application using SQL Server Express to a wide variety of desktop and laptop machines. We are currently trying to decide whether we should install the SQL Server instance service to start automatically at startup or not. In the latter case, our application would start and stop the service.
Starting automatically will make our app load more quickly, which is important to our users. However, our support team is concerned that leaving the service running will leave our databases vulnerable to becoming "suspect" if, for example, a laptop user's battery dies suddenly. Of course this will always be a problem if the app is running, but our support team believes that having the service running all the time will increase the chances of this happening. One reason for their concern is that an older version of the software used MSDE which was running all the time, and they saw a good number of suspect database issues.
What is the best practice for leaving the service running, especially when there are a lot of laptop users?
Thanks,
Ben
View 3 Replies
View Related
Oct 28, 2013
automatically replicates new databases to Availability Group partners - if you do a little prep work on your environment first.To make it work:
1) Create linked servers on all group members pointing to all other servers in the group, with names matching the hostnames they represent.
2) Ensure suitable credentials (or 'current context' impersonation) for linked servers. Also: Enable RPC and RPC OUT
3) Run the DDL code below.
4) Schedule hadr_replicate_queue on [master] to run as often as you want initial syncs to occur. Every 5-10 minutes is plenty for most purposes.
5) Connect to an availability group listener and call CREATE DATABASE :)
I use a slightly more extended version of this code at home to do things like permissions synchronization across replicas - I essentially allow applications to install direct to an availability group replica and then have all the relevant objects replicate to other nodes. I don't really like going through manually and doing things, even though there's an AddIn from SQLSkills for management studio - it still requires manual intervention.
The main use I have for this at home is that I'm using the Azure pack, and want to automatically ensure that my newly created 'SQL Server Cloud' databases are highly available, plus it means when I install a non-alwayson aware product it doesn't require any extra work afterwards to allow failover to another machine.
* AlwaysOn Self-Population Script
* By: Steve Gray / steve@mostlyharmful.net
* Usage: Free, but buy me a beer if you're ever in Brisbane.
**/
USE [master]
GO
IF EXISTS (SELECT * FROM sys.tables WHERE name='hadr_pending_replicate')
[code]....
View 4 Replies
View Related
Jul 1, 2015
I have a client data which has the candidate id, a start date which will have only the date in varchar format and a time value associated to that date in varchar format in a seperate column.
To give a brief explanation about the data a candidate will come to study center at any time point in a day for example 10:00 AM on 20-10-2014. The start date will be 20-10-2014 and from 10:00 AM he will have test based on the time point. So if the time point is 2 HR, then at 12:00 PM he will have a test. If the time point is 8 HR, it will get added with 10:00 AM and based on this he will have the test at 06:00 PM. When the timepoint reaches 00:00 the start date needs to be the next date instead of 20-10-2014.
The start date needs to be appended with the time value such that when it crosses over the time 00:00, the start date needs to get increased by 1 ie the next day. I have added the start date to the time by using the code below
CAST(STARTDATE as datetime) + CAST(CAST(STUFF([TIME],3,0,':') as time(0)) as datetime) as [EXPECTEDDATETIME]
By this above code i have created the expected datetime however
I will not be able to hardcode the value due to dynamic data. I tried to apply >= and < to the time value something like
case when MyTime >= '00:00' and MyTime < '08:10' the Dateadd(day, 1, date)
This works perfect but my concern is that I cannot put the value 08:10 because it will not a constant value for all rows.
I have provided a screenshot of my data and a expected date column for reference.
Candidate StartDateStartTimeExpected DateTime Timepoint
1 20141020 1000 2014-10-20 10:00:00 0 HR
1 201410201200 2014-10-20 12:00:00 02 HR
1 201410201400 2014-10-20 14:00:00 04 HR
1 201410201800 2014-10-20 18:00:00 08 HR
1 201410200000 2014-10-21 00:00:00 12 HR
1 201410201200 2014-10-21 12:00:00 24 HR
2 20141020 1100 2014-10-20 11:00:00 0 HR
[Code] ....
I have also attached the data for reference.
View 9 Replies
View Related
Jul 29, 2015
our clients have the flexibility to detach and attach databases (I know there are a lot of considerations around this but there is now way of changing it), once they attach a database we need to run some code to update a bunch of values in the database.
Other than creating a SQL Agent job are there any other options available to automatically execute a script once the database has been attached?
View 1 Replies
View Related
Mar 3, 2008
I come cross some post about how to backup a database in Sql Server Management Studio Express automatically. The scripts look like as below
USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_BackupDatabase]
@databaseName sysname
AS
BEGIN
SET NOCOUNT ON;
DECLARE @sqlCommand NVARCHAR(1000)
DECLARE @dateTime NVARCHAR(20)
SELECT @dateTime = REPLACE(CONVERT(VARCHAR, GETDATE(),111),'/','') +
REPLACE(CONVERT(VARCHAR, GETDATE(),108),':','')
SET @sqlCommand = 'BACKUP DATABASE ' + @databaseName +
' TO DISK = ''C:Backup' + @databaseName + '_Full_' + @dateTime + '.BAK'''
EXECUTE sp_executesql @sqlCommand
END
It throws that the DDL statement is not allowed. What's wrong with it?
View 5 Replies
View Related
