im writing a DTS package and i have am using vbscript to check if a file exists on an ftp server.
I can't figure out how to make my process sleep b/t checks on if the file exists.
in vb u can just
Declare Sub Sleep Lib "kernel32.dll" (ByVal dwMilliseconds As Long)
but i cant seem to find a counterpart in vbscript.
thanks in advance,
pete
Dear all:
Does sql server has BIF like vb sleep()?
I want to use this kind of function to balance the sql server loading,
or do you have better way to implement this task?
thanks for your kindly assistance
regards,
Stanley Huang
HiI am trying to use the WAITFOR function to make each loop in a cursoroccur every 4 seconds until the curdb is emptythe prtocedure is as follows---------------- startcreate procedure q_additionalrabatt@additionalrabatt float,@ordernr intASdeclare @ordradnr intdeclare curdb cursor for select ordernr, ordradnr from orp whereordernr = @ordernrfor read onlyopen curdbfetch curdb into @ordernr, @ordradnrwhile @@fetch_status = 0beginupdateorpsetorp.rabatt1 = (orp.rabatt1 + @additionalrabatt)whereorp.ordernr = @ordernr andorp.ordradnr = @ordradnrfetch curdb into @ordernr, @ordradnrendclose curdbdeallocate curdb------------------- endI need to make sure, that before it fetches the next row it waits 4seconds before executing the next loop.Matt
View 1 Replies View RelatedHi,
Im currently running a website in conjuction with MSSQL Express. However at the moment the website/database seems to 'sleep' when no-one is using it, then there are huge dealays accessing the data while the database/website kicks in again. I tried to google this but truthfully i don't know what i am looking for. Does anyone have any ideas? Or whether this problem is Database or website driven? I thought there might be a control in the web.config but i haven't found a solution as yet!
Thanks
Daniel
Can you use 'Pause' or 'Sleep' in SQL query?
View 3 Replies View Related
My reporting server seems to go to sleep after some time of non-use. This is affecting the users who create reports at the start of the day. A report that takes 1 second to generate takes 11 seconds on first hit in the morning. After the initial "wake up" call, the report server generates the reports normally without any delay. Can I keep it awake the whole time? Is there a setting somewhere?
I created a report using the wizard. Every thing works fine. but in one section the report returns the value of a subtotal just fine. Now I'm trying to get it to do a %(percent) using the subtotal and it keeps returning a percentage using the grand total value of what I'm adding.
i have a
group total in the group <this fine> this is want i need look down |
V
catalog %
-------------------------------------------
person total to the group<want a % based on the group>
activity total based on the total of the person activity
can any help
Has anyone figured out why it takes about 30-60 seconds to load a report or even get the login webpage in SSRS2005?? This seems to happen in 2 situations. One, the server has just started up and its the first request for a report or the SSRS webpage. Two, no one has requested a report or a SSRS webpage for a while (say 30min, maybe less). Its like part of SSRS has gone to sleep. Is there anyway to keep this from happening??
View 4 Replies View Relatedis there a way to pause a stored proc for x amount of time and then continue. I rather not go through a loop x number of times if I had to.
thanks,
DMW
I setup a maintenance plan in SQL and added some vbscript codes. One line is just wscript.sleep 10000. When I tried to run the job, I received error description: variable is undefined: 'wscript'
What's up with SQL?
Hi,
I have a script task in SSIS which uses a sleep command. sleep(600000) which waits for 10 minutes. This works fine when i execute the package through GUI. But when i execute the same with Commandline, it does not work. Is there some thing which I have not set. I have also tried the Windows API call for sleep still it behaves the same way.
Any help??
Thanks in advance
Srividya
The known issue about the report server going to sleep after being idle for a while is causing me a problem. I have set up a dummy sibscription to create and email a report every hour and also have set the IIS setting on the application pool the RS uses (shut down worker process after being idle for x mins, I have set this to 860!).
But the report server still goes to sleep and it can take up to 60 seconds for a report to start up.
What else can I set to reduce this problem?
So every time i run a DBCC command like DBCC SHOW_STATISTICS and DBCC INDEXFRAG, the query executes and goes on for ever. When i do a sp_who2 i notice that my DBCC SPID goes to sleep mode with high CPUTime and High DiskIO's. i also noticed that my server has no activity, no inserts, no updates and no select statements running by other users. Why cant i run a DBCC command?
You thoughts and suggestions are highly appreciated!
Thanks in advance
Lana
THE LADDERS (The Most $100k+ Jobs.)
www.TheLadders.com
Hi:
I have one table in which there are almost 800,000 records, and I tried to delete them by SQL stored-procedure in which executing SQL like this: "delete from tablename where date='YYYYMMDD'", once I started this procedure, I noticed SQL server will take most of CPU resources in a short period, but that's not acceptable. and there are some other useful records in this table, we can't use Drop Table.
So my question is "can we delete 1000 records and then sleep awhile, and repeat until all these records are deleted !"
Any refernece will be appreciated, thanks!
Hello All & Thanks in advance for your help!Background:1) tblT_Documents is the primary parent transaction table that has 10fields and about 250,000 rows2) There are 9 child tables with each having 3 fields each, their ownPK; the FK back to the parent table; and the unique data for thattable. There is a one to many relation between the parent and each ofthe 9 child rows. Each child table has between 100,000 and 300,000rows.3) There are indexes on every field of the child tables (though Idon't believe that they are helping in this situation)4) The client needs to be presented a view that has 5 of the mainfields from the parent table, along with any and all correspondingdata from the child tables.5) The client will select this view by doing some pattern-matchingsearch on one of the child records' detail (e.g. field-name LIKE%search-item% - so much for the indexes...)Problem:When I do the simple join of just the parent with one of the children,the search works *fairly* well and returns the five parent fields andthe corresponding matching child field.However, as soon as I add any one of the other child records to simplydisplay it's unique data along with the previously obtained results,the resulting query hangs.Is the overall structure of the tables not conducive to this kind ofquery? Is this a situation where de-normalization will be required toobtain the desired results? Or, more hopefully, am I just an idiotand there is some simpler solution to this problem?!Thanks again for your assistance!- Ed
View 9 Replies View RelatedI have an application written in C#, which allows the user to load their destination schema (in xml format) and writer their transformation expressions either in VBScript or in T-SQL. It uses DTS to do the ETL job.
Everything works great except with one problem. If there is a hypen ("-") in any of the destination attributes, the transformation fails with syntax error. It happens only with VBScript.
Here is my transformation:
"Function Main()
DTSDestination("Field-1") = #01/01/2004#
End Function"
If I replace the hypen with underscore, it works without any problem.
Does anyone have any idea about this problem?
How do i get my server to run a vbscript (yet to be created but i guess it will be in a DTS package???) when an insert runs on a table?
We need to analyse the browser versions hitting our intranet and catch the ones that are too old (earlier than IE5.5). I have the asp inserting a row with the IP address of the browser but i need to run a command line query to get the machine name from DNS (nslookup 111.222.333.444). This nslookup can be run from a vbscript.
Do i need to used a stored procedure in the process somewhere?
any thoughts welcomed
I am trying to write a DTS package at work that uses a loop to fire a query off against a different server/database at each loop iteration. Prior to the execute SQL task , I use ActiveX (VBScript) to change the Catalog and DataSource of the connection (created by drag and drop if icon). My Execute SQL task selects @@servername and getdate() just as a dummy query to make sure I am actually pointed at a different server.
While testing the properties of the connection using global variables and msgbox shows that the database (catalog) and server (data source) ip address is being changed, the result returned is always from the first server. I feel like I need to disconnect the connection object, change the parameters and then reconnect to the new server but there doesn't appear to be anyway to do this.
Anyone out there able to successfully change a connection object inthis manner??
I am trying to call a VB script file with the following command,
xp_cmdshell 'cscript c:file.vbs'
But its giving the following output -
Microsoft (R) Windows Script Host Version 5.1 for Windows
Copyright (C) Microsoft Corporation 1996-1999. All rights reserved.
NULL
Input Error: Can not find script file "c:file.vbs".
NULL
------------
If i changed the command to
xp_cmdshell 'cscript c:file.vbs'
it gives the folowing error -
Input Error: Can not find script file "C:WINNTsystem32file.vbs".
Actually my main intention is to call a url from the sql server where '
i can pass some value. Plz let me know if its the right step and whts
going wrong.
Thanks & regards,
-dkoushik
I have an SQL job that runs a .vbs script. It has the following code:
Cmd2.ActiveConnection = Conn2
Cmd2.CommandText = "USE msdb EXEC sp_update_job @job_name = 'Email Download',@enabled = 0"
Cmd2.CommandType = 1
Cmd2.Execute()
It's not disabling the job. If I execute in Query Analyzer, it works. I'm thinking it might be a permissions issue. Any suggestions?
TIA
I'm trying to run a TQSL script against a SQL Server 2000 database using SQL-DMO and Vbscript. The output of this query would be use to populate a table in another SQL Server database on a different server. Has anyone use SQL-DMO to connect SQL Server and run a t-sql script against a database and use the output to populate a table on a different server? Any informatiom would be helpfull.
Regards
Magictech
Hi All,I wrote a vbscript file which copies one table records to another database table. I wrote error handling to send an email if error occurs when copying records from one table to another. I want to schedule a sql job and give this file to execute. Could anyone plz tell me how to schedule sql job which runs vbscript file. i selected ActiveXScript and choose vbscript and gave path. I'm not understanding what the command should be given to run vbscript file. The result of giving path is job is failing.Any help would be appreciated...plzzzzzzzthankscarol
View 1 Replies View RelatedThis function is useful for fixed width output. E.g.
String(expr,10-len(expr),'0') will give you a number with leading zeros so it is always 10 characters long.
Thanks in advance for your time.
Ok, I've been charged with the task of setting up an IVR(interactive voice response) system for remote data entry. It was all going great till I got to the utility's Database connector. It wouldn't work, but I saw that the program also supported VBScript, great! I know VBScript.
Simple task, setup a simple flow where data can be entered. I setup a DSN for the access database(not quite MS SQL, but close enough) I'd created. It gave me a syntax error when I tried to use '' in an auto increment field. I think the exact query was:
"INSERT INTO test_table VALUES('',$RV[Store Number],'$RV[Objective]','question1','question2',question3');"
The database setup was:
id(AutoNumber),store_number(number),objective(text),q1(text),q2(text),q3(text)
Changed the auto number in the query to a 0 and it worked fine, but of course it was only to test if it would work. It would create a duplicate row if it went further than the first row.
I don't understand what my syntax error was. The $RV[*] are variables retrieved by procedures in the IVR scripting program. Need this for work so I can setup a demo of the system to present to my boss' bosses' bosses
Hello,
I am trying to call a Stored Procedure from VBScript. It worked fine the first time but each time after it doesn't seem to be returning any data is there any special command I need to send?
Thanks in Advance,
John
Hey guys,
I have a database import that I'm doing and I'm using DTS/VBscript to manipulate data.
I created a varchar field to import a date in the following format 030906.
Does anyone have a suggestion on making it 03/09/06?
Or just the best way to handle this type of data
thanks,
Jonathan
hi
I have a VBScript converted from an AccessDB Macro to VB.
Option Compare Database
'------------------------------------------------------------
' Macro2_Export2Text5
'
'------------------------------------------------------------
Function Macro2_Export2Text5()
On Error GoTo Macro2_Export2Text5_Err
DoCmd.TransferText acExportDelim, "", "MTHCrosstabCountingUnits", "c:IMSMthCountingUnit.txt", True, ""
DoCmd.TransferText acExportDelim, "", "MTHCrosstabValue", "c:IMSMTHValue.txt", True, ""
DoCmd.TransferText acExportDelim, "", "MATCrosstabValue", "c:IMSMatValue.txt", True, ""
DoCmd.TransferText acExportDelim, "", "MATCrosstabCountingUnits", "c:IMSMATCountingUnit.txt", True, ""
Macro2_Export2Text5_Exit:
Exit Function
Macro2_Export2Text5_Err:
MsgBox Error$
Resume Macro2_Export2Text5_Exit
End Function
What I am trying to do is instead of Opening the Access application and run the macro manually, i converted the macro to VB and created an ActiveX Script Task package in SQL2000 and Copy and Paste the VB Code to ActiveX Script Task. But it didn't work. Is there a work around for this?
Many thanks in advance.
I have a stored procedure that I run in Management studio just fine. I need to provide 2 integer variables @id and @version.How do I run the stored proc from vbscript and reading the output?
SP:
USE [ProjectBudget]
GO
DECLARE @return_value int,
@name varchar(500),
@maconomy varchar(100),
@creator varchar(100),
@created datetime,
@active bit
[code]....
How to compare db2 and sql7 database using VB script
View 2 Replies View RelatedHi!
I'm trying to insert rows into an SQL Server table using a semicolon-separated text file as the source. The file continues approximately 4000 rows and I use the readLine() function to get the data ready for insert.
After reading a line I insert the row into SQL Server using a normal INSERT statement. I have tried this using only 100 test lines nand everything worked OK, but the first time I tried inserting all 4000 the web server seemed to go down.
Is this because SQL Server is not capable of such a bulk insert via web?
Is there something I could do to makes this easier?
Hi guys. I'm now stuck with something and wondering if anyone can shed me some lights.
I'm now building a backup procedure for an application (business objects), and the sequence is
1) stop the service of the application
2) SQL backs up the database and at the same time, copy a folder to a backup location
3) restart the service of the application
I've come up with the scripts to stop/start the service and copy the folder to a backup location. Now, my boss wants me to put everything into a single procedure, but I have no clue how. My question is how do I trigger a SQL backup after the script stops the service of the application?
Hello All
I want to fetch data from SQL Server using VB Script. Can any one please send me any sample code or article.
Looking forward for your help
Amir
I'm looking for an example of how to execute an existing DTS* packagefrom anASP (VB)script and would appreciate any and all response. *I don'tevenknow if it's possibleThanks- Chuck GattoDan Guzman Apr 27 2000, 12:00 am show optionsNewsgroups: comp.databases.ms-sqlserverFrom: "Dan Guzman" <DGuz...@nospamplease-earthlink.net&g*t; - Findmessages by this authorDate: 2000/04/27Subject: Re: Executing a DTS Package using an ASP (VBScript) ScriptReply to Author | Forward | Print | Individual Message | Show original| Report AbuseThis VBScript example loads and executes an existing DTS pac*kage fromSQLServer.Option ExplicitConst PackageName = "PackageName"Const ServerName = "ServerName"Const UserName = "UserName"Const Password = "Password"Dim DTSPkSet DTSPk = CreateObject("dts.package")DTSPk.LoadFromSQLServer ServerName, UserName ,Password,,,,,*PackageNameDTSPk.ExecuteIf DTSPk.Steps(1).ExecutionResult = 0 ThenResponse.Write "Package execution completed"ElseResponse.Write "Package execution failed"End IfSet DTSPk = NothingYou can also create the entire package from scratch from wit*hin yourasp andexecute it.Hope this helps.Chuck Gatto <cga...@anchorsystems.com> wrote in messagenews:8eapo8$frj$1@slb7.atl.mindspring.net...- Hide quoted text -- Show quoted text -[color=blue]> I'm looking for an example of how to execute an existing D*TS[/color]package froman[color=blue]> ASP (VB)script and would appreciate any and all response.* I don't[/color]even[color=blue]> know if it's possible> Thanks> - Chuck Gatto[/color]Chuck Gatto May 1 2000, 12:00 am show optionsNewsgroups: comp.databases.ms-sqlserverFrom: "Chuck Gatto" <cga...@anchorsystems.com> - Find messages by thisauthorDate: 2000/05/01Subject: Re: Executing a DTS Package using an ASP (VBScript) ScriptReply to Author | Forward | Print | Individual Message | Show original| Report AbuseBelow code works 100% in VB but the load fails in ASP. I g*et..."Microsoft OLE DB Provider for SQL Server. Login failed for *user "".error.I think the IIS (server a) is set for NT auth. and sql7 (on *server b)aswell but I can't be sure.Any idea what I should look for.Thanks"Dan Guzman" <DGuz...@nospamplease-earthlink.net> wrote in m*essagenews:sgi3hi617qo89@corp.supernews.com...- Hide quoted text -- Show quoted text -[color=blue]> This VBScript example loads and executes an existing DTS p*ackage[/color]from SQL[color=blue]> Server.[/color][color=blue]> Option Explicit> Const PackageName = "PackageName"> Const ServerName = "ServerName"> Const UserName = "UserName"> Const Password = "Password"> Dim DTSPk> Set DTSPk = CreateObject("dts.package")> DTSPk.LoadFromSQLServer ServerName, UserName ,Password[/color],,,*,,PackageName[color=blue]> DTSPk.Execute> If DTSPk.Steps(1).ExecutionResult = 0 Then> Response.Write "Package execution completed"> Else> Response.Write "Package execution failed"> End If> Set DTSPk = Nothing[/color][color=blue]> You can also create the entire package from scratch from w*ithin[/color]your aspand[color=blue]> execute it.[/color][color=blue]> Hope this helps.[/color][color=blue]> Chuck Gatto <cga...@anchorsystems.com> wrote in message> news:8eapo8$frj$1@slb7.atl.mindspring.net...[color=green]> > I'm looking for an example of how to execute an existing* DTS[/color][/color]packagefrom[color=blue]> an[color=green]> > ASP (VB)script and would appreciate any and all respons*e. I[/color][/color]don't even[color=blue][color=green]> > know if it's possible> > Thanks> > - Chuck Gatto[/color][/color]Dan Guzman May 1 2000, 12:00 am show optionsNewsgroups: comp.databases.ms-sqlserverFrom: "Dan Guzman" <DGuz...@nospamplease-earthlink.net&g*t; - Findmessages by this authorDate: 2000/05/01Subject: Re: Executing a DTS Package using an ASP (VBScript) ScriptReply to Author | Forward | Print | Individual Message | Show original| Report AbuseYou can specify a trusted connection with flag 256 instead o*fusername andpassword. For example:DTSPk.LoadFromSQLServer ServerName, , , 256,,,,PackageNa*meAssuming this is an intranet application running under NT 4.*0 and youwantto execute the package under the invoking user's account, yo*u can dothis asfollows:Specify 'clear text' for the IIS Directory Securityauth*enticationRemove 'Everyone' from the access list on the files (req*uiresNTFS) andgrant permissions to the usersGrant logins access to the database serverWith this method, users must enter their DomainUserName and* passwordwhenprompted.NT authentication presents a challenge when multiple servers* areinvolvedbecause NT 4.0 does not support delegation. Seehttp://msdn.microsoft.com/workshop/...re/security.asp fordetails.I understand Windows 2000 provides delegation capabilities b*ut thiscan be abit tricky to implement.BTW, if your DTS package does not access SQL Server, you can* save itto afile and use the LoadFromStorageFile method instead.Hope this helps.If you need to useChuck Gatto <cga...@anchorsystems.com> wrote in messagenews:8ekrkd$pmq$1@slb7.atl.mindspring.net...- Hide quoted text -- Show quoted text -[color=blue]> Below code works 100% in VB but the load fails in ASP. I* get...> "Microsoft OLE DB Provider for SQL Server. Login failed fo*r user[/color]"".error.[color=blue]> I think the IIS (server a) is set for NT auth. and sql7 (o*n server[/color]b) as[color=blue]> well but I can't be sure.> Any idea what I should look for.> Thanks[/color]Chuck Gatto May 6 2000, 12:00 am show optionsNewsgroups: comp.databases.ms-sqlserverFrom: "Chuck Gatto" <cga...@anchorsystems.com> - Find messages by thisauthorDate: 2000/05/06Subject: Re: Executing a DTS Package using an ASP (VBScript) ScriptReply to Author | Forward | Print | Individual Message | Show original| Report AbuseHey DanThanks again. I really appreciate your input and help.I actually solved the problem by calling dtsrun...I apologize for the delay getting back w/you but thee recent* virusatacksidetracked me.Thanks again."Dan Guzman" <DGuz...@nospamplease-earthlink.net> wrote in m*essagenews:sgsfh8spoog87@corp.supernews.com...- Hide quoted text -- Show quoted text -[color=blue]> You can specify a trusted connection with flag 256 instead* of[/color]username and[color=blue]> password. For example:[/color][color=blue]> DTSPk.LoadFromSQLServer ServerName, , , 256,,,,Package*Name[/color][color=blue]> Assuming this is an intranet application running under NT *4.0 and[/color]you want[color=blue]> to execute the package under the invoking user's account, *you can[/color]do thisas[color=blue]> follows:[/color][color=blue]> Specify 'clear text' for the IIS Directory Security[/color]au*thentication[color=blue]> Remove 'Everyone' from the access list on the files (r*equires[/color]NTFS)and[color=blue]> grant permissions to the users> Grant logins access to the database server[/color][color=blue]> With this method, users must enter their DomainUserName a*nd[/color]password when[color=blue]> prompted.[/color][color=blue]> NT authentication presents a challenge when multiple serve*rs are[/color]involved[color=blue]> because NT 4.0 does not support delegation. See> http://msdn.microsoft.com/workshop/...re/security.asp for[/color]details.[color=blue]> I understand Windows 2000 provides delegation capabilities* but this[/color]can bea[color=blue]> bit tricky to implement.[/color][color=blue]> BTW, if your DTS package does not access SQL Server, you c*an save[/color]it to a[color=blue]> file and use the LoadFromStorageFile method instead.[/color][color=blue]> Hope this helps.[/color][color=blue]> If you need to use> Chuck Gatto <cga...@anchorsystems.com> wrote in message> news:8ekrkd$pmq$1@slb7.atl.mindspring.net...[color=green]> > Below code works 100% in VB but the load fails in ASP. * I get...[/color][/color][color=blue][color=green]> > "Microsoft OLE DB Provider for SQL Server. Login failed *for user[/color][/color]"".[color=blue]> error.[color=green]> > I think the IIS (server a) is set for NT auth. and sql7 *(on[/color][/color]server b) as[color=blue][color=green]> > well but I can't be sure.> > Any idea what I should look for.> > Thanks[/color][/color]chris.duni...@agwsha.nhs.uk Jan 31, 10:05 am show optionsNewsgroups: comp.databases.ms-sqlserverFrom: chris.duni...@agwsha.nhs.uk - Find messages by this authorDate: 31 Jan 2005 10:05:00 -0800Subject: Re: Executing a DTS Package using an ASP (VBScript) ScriptReply | Reply to Author | Forward | Print | Individual Message | Showoriginal | Report AbuseHi,I've tried adding this function to my ASP pages and get the *followingerror message;Microsoft VBScript runtime error '800a01ad'ActiveX component can't create object: 'DTS.Package'/asp/pages/dts.asp, line 21Does anyone have any idea what I need to fix to get the DTS *to work?I'm v. new to SQL Server and ASP so any help would be apprec*iated.Many thanks, Chris Dunigan
View 4 Replies View Related