how to schedule job to run vbscript file

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...plzzzzzzz
thanks
carolThe way I've used the ActiveXScript option in the past is to physciallypaste the VBScript code into the Command textbox. AFAIK you can'ttype in a path.

How to save "New" / "Updated" records only in to destination Tables

Hi

I have a requirement like, i need to save all the records from my Flat File on Monthly basis to my database table. In the next month, the flat file may be added with 10-20 records and also some updates happened to my old records. Though the faltfile is same for each month, the changes are occured in some records and also added with few records.

So when i am loading this data in to Database table, i need to just update the changed records and also needs to add the new records. I don't need to touch the remaining records.

How can i do that in SSIS 2005 using different data flow tasks ?

Thanks

Kumaran

Answers are right here: http://www.sqlis.com/default.aspx?311

-Jamie

|||

Hi Jamie

As per the URL you mentioned, i tried for the results.

I don't have any problem on identifying new records and inseting in to my database table using the "Conditional Split transformation".

But How to handle my few UPDATED records ? hoc can i identify that and update only those records in to my data base Table.

Thanks

Kumaran

|||

You could use another lookup on the records that find a match which basically does the same - but looks up against ALL columns in the target. If there are any rows that don't find a match then you know something has been changed.

-Jamie

How to run Search on 800,000 records

Hi !!

We are doing a project where we have a table with 800,000 records. We need to implement a search on this 800,000 records. How do we do that?

Assue State = NJ

Criteria: Select County, Select City, Enter Zip Code, First Name and Last Name

This should bring up Information about the person.

How can we implement it efficiently ?

Thanks

Things that will increase performance of a query with a lot of records:

- More CPU power / Ram / faster hard drives on the database server

- Proper use of indexes on the affected table.

- Use of stored procedures or other method that results in only the specific page of the record you are looking for being sent from the database server to the web server - for example -- if you wanted records 1-10 of a 800,000 record query, make sure whatever method you use only returns 10 records.

The web sitesql-server-performance.com has an abundance of tips that might help.

Jason

|||

Jason,

We have dedicated Database sevrer with good RAM, CPU, HDD etc.

Its like people search ...

I showed you my selection/search criteria. Based on that Criteria, we will get 1 or more records. I don't want Query to take long time.

Where should we have our indexes? How can we write query

For eg. Query Like this will Help us?

SELECT ContactInfoID, FirstName, LastName, HouseNo, StreetName, City, State, Zip, County, Municipality, HomePhone
FROM ContactInfo
WHERE County = COALESCE(@.County, County) AND
City = COALESCE(@.City, City) AND
Zip = COALESCE(@.ZipCode, Zip)AND
FirstName = COALESCE(@.FirstName, FirstName) AND
LastName = COALESCE(@.LastName, LastName)

|||

Hello,

Basically if that's the query that generates the results you want, than that's the query you should use. Focus on getting the results you want first, then use the "Index Tuning Wizard" with your query in SQL Query Analyzer to determine what indexes would be helpful.

Jason

|||

Hey !!

I indicated one of the query.. if there is a better way to query I would like to know that .

If we have multiple people with this query, we ask one more question like DOB and that will narrow down records.

|||

Basically you need to figure out which queries you'll most commonly be doing against this table of your database. As you mentioned you'll supply info as you have it, dob etc.. but are their some fields that you'll almost always be using? If so definitely place indexes on these fields this will speed up your query performance. If you find there's one that you use predominantly i would make it a clustered index which will further speed up your performance.

Also, I would write my application query as best you can, try not to use double wildcards ie (select * from table where myfield = '%somevalue%') queries like this will blow the indexes and do full table scans. If you place an index across more than one field if you do not always query it by the fields in your index this also "blows" the index and will result in a full table scan. Moral of the story, use the indexes as you create them (in the same fashion etc..)

Once your app is built, i would use sql profiler to watch the queries, be sure to grab the query plans from sql profiler. Anywhere that it says FULL Table Scan, go back and apply an index if possible to eliminate these full table scans.

Hope this helps

|||

If these are the results you are looking for, then there is no better query! :) If these aren't the results you are looking for, then the simplest query (uses the least functions, joins, etc) that returns the least # of results is the best.

Jason

|||

Open the Database then click on tools then click on wizards, then Index tuning wizard it will give you hints on indexes you need on the table. Make the size of your clustered index small and you can create 249 none clustered indexes on the table. The wizards tool is context sensitive you may not see it if your database is not open. Hope this helps.

Kind regards,

Gift Peddie

|||

I have read the article and implemented the Dynamic Sql. We are giving users capability of Ad Hoc Query on 800,000+ records. Sometime query takes long time to return results and we have time out issue. OR sometime return results are too many (150,000) records.

We have designed Ad Hoc Query tool with idea of

http://www.aspose.com/Products/Aspose.AdHoc/Demos/

Since it is Dynamci SQL I can't have indexes. Can someone help how to reduce execution time on Dynamic SQL?

Our Dynamic SQL Looks like this

CREATE PROCEDURE TEST

(

@.whereClause varchar(4000)

)

DECLARE @.sql varchar(2000)

SET @.sql = 'SELECT FirstName, LastName, Address, City, State, Zip, County, Township, Phone

FROM VotersProfile

WHERE + ' @.whereClause

"whereClause" is build into C# code based on the columns user select in Tool

For eg @.whereClause is FirstName = 'John' AND 'County = 'Bergen' AND (Gender = 'M' OR Gender = 'F')

Can partitioned view or some other technique can help me? Its like search.. can we you some other search techniques?

Thanks

|||Tip: Use FieldName IN('Value1', 'Value2') instead of multiple ANDs...|||

Ok, let's see...

Sometime query takes long time to return results and we have time out issue. OR sometime return results are too many (150,000) records.

Your problem, really. If you get too many results, limit the number of results you get. Say you want to only get 500 rows, limit the returning rows to 501 - fif you get 501 tell the user "more than 500 items were returned" and be done. One of the clasical SQL problems - people not limiting their results to what they need.

If query processing takes too long then put indices on the table, and/or normalize the data into multiple tables.

Since it is Dynamci SQL I can't have indexes.

Ok, where exactly did you get this idea? The whole genius idea of SQL databases was that you define query and indices separatly and that the query analyzer can then decide which indices to use or not to use. In the times before SQL people had to manually select which indices to use (in which order).

That you can not have indices as you use dynamic SQL is silmply not true. Get a beginner book about SQL and read it. Actually you CAN have as many indices as you want, and the system will use as many as it thinks make sense (sometimes an arguable decision).

Now, going down to your problems....

There is a lot you can do to speed up your queries. First, do you use the proper database structure? For a fully query optimized system I would propably use four to six different tables, at elast to identify a person, putting it into NF5 (5th normal form) This would significantly cut down the size of the data and make processing much faster, by having less string comparisons. It is a terrible mess to update then on most databases, but on SQL Server an instead of update query on a view can handle the updates nicely without the obvious use of a stored procedure.

Then, second, can you identify "main query parameters"? Given your six parameters - are there some people will always use? This can be important for the main selection axis for the indices.

Or, just being lazy, run 10000 queries and just let query analyzer make recommendations. It is not perfect, though.

Your static SQL Statement that you gave, though, is a classical beginner mistake example:

WHERE County = COALESCE(@.County, County) AND
City = COALESCE(@.City, City) AND
Zip = COALESCE(@.ZipCode, Zip)AND
FirstName = COALESCE(@.FirstName, FirstName) AND
LastName = COALESCE(@.LastName, LastName)

See, the problem is your use of "Coalesce" and the field on the right side. Take "City =COALESCE(@.County, County)". This statement needs to be evaluated for every row, despite of whether @.City has a value or not. In order to optimize this out, SQL Server's Query optimizer would have to identify that:

* Coalesce returns the same value for the same type of input. It propably can do it. NOte though that this would mean one evaluation for every different city name stored, at least.

* Coalesce returns the same value regardless of the field entered (second parameter) if @.City is not null. Now, it definitly does not do this.

Result: brutally said you force a table scan.

Beginner rule: try to formualte your queries in such a way that database row fields only appear on ONE side of the condition (normally the left one) and do not involve ANY processing.

You break the rule by having field references in both sides, which results in the same logic, logically, ut it forces a table scan and makes any index totally useless.

And it does so because you do not use dynami SQL. Static SQL and dynamic query conditions simply to not match.

I can see you already got rid of this in the dynamic SQL you use (shown later).

Well, all I can suggest then I do dump the table and come up with a normalized form. Last time I had to do something like this we reduced the database size by about 50% and performance really screamed up. Keeping all the data in one table does not follow proper procedures for query optimized databases - that simple. The data is not normalized. Not being normalized the query otimizer is really limited in what it can do.

|||

Thanks for the suggestions. Our problem is Voters Profile table is coming to us every year from Political Parties. They have same structure. As a result we can't normalize it.

Table has about 25+ columns:

In case of GXX G=General Voting XX = Year and PXX P=Primary Voting XX = Year

We created 2 computer columns G00_04 & P00_04 i.e. How many times person voted in last 4 yrs

FirstName, LastName, Address1, Address2, City, State, Zip, Zip4, County, Township, district, ward, HomePhone, Ethinicity, Gender, DOB, Age, Registered Party, VotingID, MailID, G04, P04, G03, P03, .... G96, P96, VotingHistory

Out of this we gave user to do "Adhoc Search" based on any or all combination of County, Township, Zip, Gender, Age, General Voting and Primary Voting

In the Result Set we display Name, Address, city, State, Zip, HomePhone, General Voting and Primary voting

As I meantioned Where Clause is composed in C# code and passed to Dynamic SQL

CREATE PROCEDURE TEST

(

@.whereClause varchar(4000)

)

DECLARE @.sql varchar(2000)

SET @.sql = 'SELECT FirstName, LastName, Address, City, State, Zip, County, Township, Phone, G00_04, P00_04

FROM VotersProfile

WHERE + ' @.whereClause

So if they Select only County is Where Condition

@.whereClause = 'County = ''Atlantic''' and result may be 90000 records.

But if they add some more condition like 1) Age Between 25 and 35 2) Gender = M etc they get less records as query is more specific.

We need general way to optimize the performance. I know the COALESCE but, if some values are NULL they will not show up in result set.

Reading the Dynamic SQL articles we decided to follow them and implement it. Now, we need to know how to do performance tunning.

I have seen that some query result into 4 table scan. How can we reduce that.

I hope this should help to understand our problem and giving suggestion !!

Thanks,

|||Why not use DTS to take the supplied data and normalize it upon import? That way your queries will execute faster. :)
Jason
|||

Our need is growing from 800K records to 4 million record. Is there a better way to search ?

How about Partitioned view/table ?

|||Change your table layout to 5th normal form.

How to return all records when date filter parameter is missing

I'm using an objectDataSource connected to a strongly typed dataset to populate a GridView. I want to be able to show all the records, or let the user to select only those records that expire in a certain month. The expire field is of type date

I'm used to all records being returned when a parameter is missing. If I have Select * from table where last=@.last, only the records where the last name is 'Smith' will be returned if @.last = 'Smith', but all records are returned is @.last = "". But that's not how it's working with the date.

I'm passing an integer from 1 to 12 in a querystring. I have the equivalent of

select * from table where (MONTH([AD ENDS]) = @.month)

MONTH(datefield) always returns an integer from 1 to 12. If @.month is empty, I want all the records to be displayed, but nothing is. If @.month is an int form 1 to 12, it works fine. How can I get all the records if no month is selected? Can I have two objectdatasources and programmatically select which one populates the gridview depending on if I want to filter the data or not?

Diane

HI Diane,

It think there are two options that might be better than having two objectdatasources.

1:change the select statement programmatically
strSQL = "select * from table ";
if (strMyMonth == string.empty)
{ strSQL+= "where (MONTH([AD ENDS]) = @.month)"}
else
{srSQL += "where (MONTH([AD ENDS]) > Date('01/01/0001')}

2:use the databases own If Then Else capabilities (this is dependent on your DB.. I know oracle, sqlserver and even access allows this)

Access Example: If [targetgoal] <> 0 then ([Total Of $FeeBilled]/6)*12)/[TargetGoal] else [targetgoal]= 0

|||I don't know how you can get all records without send anything in with Select * from table wherelast=@.last

But I would try to use Select * from table where last=ISNULL(@.last,last).

For the month query,please try something like this:

WHEREMONTH(tDate)=ISNULL(@.month,MONTH(tDate))

|||

Yup, those are much better options. I forgot about changing the select statement programmatically. Been at this too many hours I guess. I like the idea of using the database's capabilities though. If my current select statement is select * from table where (MONTH([AD ENDS]) = @.month) and I'm using SQL Server 2005, what would the new select statement be?

select * from table where ( If @.month <> 0 then (MONTH([AD ENDS]) = @.month) )?

or

select * from table ( If @.month <> 0 then (whereMONTH([AD ENDS]) = @.month) )?

Diane

|||SELECT * FROM Table WHERE MONTH([AD ENDS]) = ISNULL(@.month, MONTH([AD ENDS])|||

Thank you Dinakar! That worked!

Linmo, in a strongly typed dataset (which are pretty much all I know in .NET so far), if an empty parameter is returned, the filter for that parameter isn't set. This behavior is a life saver IMHO. It kust had me stumped this time around.

Diane

How to return a value if recordcount = 0

How would I return a select statement if the records returned is 0
i.e
Select * from orders where employeeID = 0
if @.RowCount = 0
--Return Shipname as 'None'
Select 'None' = shipname ect,
Thanks
Stephen K. Miyasato
MDsyncHi Stephen,
I would do something like...
SELECT * FROM Orders where EmployeeID = 0
IF @.@.ROWCOUNT = 0
SELECT 'None' As ShipName
or
IF EXISTS (SELECT * FROM Orders Where EmployeeID = 0)
SELECT * FROM Orders WHERE EmployeeID = 0
ELSE
SELECT 'None' As ShipName
Both examples would return a single row with a single column called
[ShipName] with the value 'None' in the event that no rows exist with an
employeeID of 0.
Rich.
"Stephen K. Miyasato" <miyasat@.flex.com> wrote in message
news:OWMcSE6cGHA.564@.TK2MSFTNGP02.phx.gbl...
> How would I return a select statement if the records returned is 0
> i.e
> Select * from orders where employeeID = 0
> if @.RowCount = 0
> --Return Shipname as 'None'
> Select 'None' = shipname ect,
>
> Thanks
> Stephen K. Miyasato
> MDsync
>|||Try this:
Select * from orders where employeeID = 0
if @.@.RowCount = 0
begin
--Return Shipname as 'None'
Select 'None' as 'shipname'
end|||Thanks but I could not get this to work.
Here is the actual query:
The query as it now is return two sets. The first set has 0 rows, the next
set is the one I need to produce.
if I use Exist(Select ..), I get and error since I have NTEXT values and
order by is not allowed.
Any other suggustions?
Stephen K. Miyasato
/****** Object: Table [dbo].[orderTest] Script Date: 5/9/2006 12:42:23
PM ******/
if not exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[orderTest]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [orderTest] (
[PatNo] [int] NULL ,
[Name] [varchar] (24) NULL ,
[Dosage] [varchar] (25) NULL ,
[Sig] [varchar] (30) NULL ,
[PatSig] [varchar] (50) NULL ,
[RxNo] [int] NULL ,
[Breakfast] [varchar] (1) NULL ,
[Lunch] [varchar] (1) NULL ,
[Dinner] [varchar] (1) NULL ,
[QHS] [varchar] (1) NULL ,
[Comments] [varchar] (60) NULL ,
[QuantityL] [varchar] (20) NULL ,
[Quantity] [float] NULL ,
[Generic] [varchar] (60) NULL ,
[BrandName] [varchar] (60) NULL ,
[MDrx] [int] NULL ,
[Category_id] [int] NULL ,
[Daily] [smallint] NULL ,
[Memo] [ntext] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
END
GO
Declare @.PatNo int
Set @.PatNo = 7160
SELECT PatNo, Name, Dosage, Sig, PatSig, RxNo, Breakfast, Lunch,
Dinner, QHS,
Comments, QuantityL, Quantity, Generic, BrandName,
MDrx, Category_id, Daily,
Memo
FROM orderTest
WHERE (PatNo = @.PatNo) AND (StopDate IS NULL)
ORDER BY Daily
if @.@.RowCount = 0
SELECT @.PatNo AS PatNo,Null as Name, Null as Dosage, Null as Sig, Null
as PatSig, Null as RxNo, Null as Breakfast, Null as Lunch, Null as
Dinner,Null as QHS,
Null as Comments,Null as QuantityL, Null as Quantity,
Null as Generic, 'None' AS BrandName, Null as MDrx, Null as Category_id,
Null as Daily,
Null as Memo
"Richard O'Brien" <groups@.rjoconsulting.co.uk> wrote in message
news:ecp6gH6cGHA.4912@.TK2MSFTNGP05.phx.gbl...
> Hi Stephen,
> I would do something like...
> SELECT * FROM Orders where EmployeeID = 0
> IF @.@.ROWCOUNT = 0
> SELECT 'None' As ShipName
> or
> IF EXISTS (SELECT * FROM Orders Where EmployeeID = 0)
> SELECT * FROM Orders WHERE EmployeeID = 0
> ELSE
> SELECT 'None' As ShipName
> Both examples would return a single row with a single column called
> [ShipName] with the value 'None' in the event that no rows exist with an
> employeeID of 0.
> Rich.
> "Stephen K. Miyasato" <miyasat@.flex.com> wrote in message
> news:OWMcSE6cGHA.564@.TK2MSFTNGP02.phx.gbl...
>|||Stephen,
Select * from orders where employeeID = 0
union all
select
-- whatever values you want
1,2,3,4,...
where not exists(Select 1 from orders where employeeID = 0)|||StopDate isn't a column in the table definition you gave.
Try this.
Declare @.PatNo int
Set @.PatNo = 7160
if exists (select * from ordertest where (PatNo = @.PatNo)) -- AND
(StopDate IS NULL))
begin
SELECT PatNo, Name, Dosage, Sig, PatSig, RxNo, Breakfast, Lunch,
Dinner, QHS,
Comments, QuantityL, Quantity, Generic,
BrandName,
MDrx, Category_id, Daily,
Memo
FROM orderTest
WHERE (PatNo = @.PatNo)-- AND (StopDate IS NULL)
ORDER BY Daily
end
else begin
SELECT @.PatNo AS PatNo,Null as Name, Null as Dosage, Null as Sig,
Null
as PatSig, Null as RxNo, Null as Breakfast, Null as Lunch, Null as
Dinner,Null as QHS,
Null as Comments,Null as QuantityL, Null as
Quantity,
Null as Generic, 'None' AS BrandName, Null as MDrx, Null as
Category_id,
Null as Daily,
Null as Memo
end

How to retrieve Top 3 records in the group level

Hi all,

I have a report which is grouped by a field called R_ID, which gives me a list of records for each R_ID. So here is the thing, I want to get only top 3 records for each R_ID. Is there any way to do this thing in the report level. I tried it from the query level, but the result is not like what I wanted.

Please let me know if some body has any idea.

Thx.

Doing this in a report wouldn′t be the best way as you would get all the data from the server and then only would display a part of it. better filter the data on the server and send only back the appropiate results, then you would have no problem on the client / reporting service. Anway, if you want to do this, you might have a look at the RowNumber() property in Reporting Services. You could set the Visibility of the row to Visible=True if the RowNumber("GroupName") equals or is less than 3 =IIF(Rownumber("GroupName") <= 3;True;False)

Doing this on the server depends on which server version you are using. SQL Server 2005 probably would enable you to use ROW_NUMBER() on the server side. SQL Server 2k does not implement this new function, so you would have to use something else instead.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

How to retrieve results in predefined chunks

Hello,
Let's say I am planning retrieve thousands of records and load them in my
datagrid in a windows form. This is very slow and I want to retrieve the
result in small chunks. For example I want to retrieve first 100 rows and
load it and then when user scrolls down on the grid I retrieve the next 100
rows and so on.
Can anyone give me some hints? Can this be done by writing a SQL statement
or there are other things to take into consideration?
Thanks for your help.http://www.aspfaq.com/show.asp?id=2120
David Portas
SQL Server MVP
--|||Great article thank you very much!
"David Portas" wrote:

> http://www.aspfaq.com/show.asp?id=2120
> --
> David Portas
> SQL Server MVP
> --
>
>

how to retrieve random records from table

Hi,
I m doing slideshow application. fro that i m using Ms Sql 7.0 server.
Now i want to retrieve each time 10 different records order by date
(it is the column) from table out of 100 records.
Plz, help me for writing the sql query for this.

Quote:

Originally Posted by pritisarode

Hi,
I m doing slideshow application. fro that i m using Ms Sql 7.0 server.
Now i want to retrieve each time 10 different records order by date
(it is the column) from table out of 100 records.
Plz, help me for writing the sql query for this.

select top 10 * from table order by newid(), mydate

How to retrieve last updated record

I have some set of records in my table.

The same set of records will be updated often. Now I have a column as "lastupdated"

While i am displaying the records in a datagrid, The LAST UPDATED record should only be displayed.

Means, the recently updated records should be displayed in datagrid.

Pls give me the sql code / i am also in need of a Stored procedure for this.

I am working in SQL 2005

SELECT TOP (1) * FROM yourTable ORDER BY lastupdated DESC

You can wrap it into a Stored Procedure:

CREATE PROCEDURE yourSP

AS

SELECT TOP (1) * FROM yourTable ORDER BY lastupdated DESC

|||

Thankyou Thankyou Thankyou,

Thanks a lot for your reply.

how to retreive data depends on another data ...

my application will add and delete and update records in db

my problem is when to update and delete ..

I have two dropdownboxes one to retreive servers and one to retreive dbs in the server ...

SqlDataAdapter daa =

new SqlDataAdapter("SELECT * FROM DB_tbl"+","+"Servers_tbl where Servers_tbl.Server_Name == '"+ DropDownList4.SelectedValue +"' ", sqlConnection1 );

this is the structure of each table >>

servers_tbl : SRV_ID,Server_name

DB_teb : DB_ID,SRV_ID,DB_name

the dropdown box retreive just the names ... so what is the base of where part of the command

hint : the DB_tbl does not have the server name but has the sever id

Hi Adorer,

Based on my understanding, you need to know when to issue a data bind on the dropdownlist which displays db names.

I think you can handle the SelectedIndexChanged event of the Server DropDownList to fill the db names.

You can use 2 SqlDataSources and use a ControlParameter to achieve this. Also, make sure you have set AutoPostback property to true.

HTH. If this does not answer you question, please feel free to mark it as Not Answered and post your reply. Thanks!

How to Restrict

I have one table named "serving" and I want that against serving ID there
should be 42 records not more or less than that, how can I restrict that ?
Waiting for your reply
Thanks
NOOR
Use an Insert Trigger taht will do Select Count(*) from the table, and if
the number is > 42, rollback the transaction.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Noorali Issani" <naissani@.softhome.net> wrote in message
news:OG7VB%23xMEHA.3208@.TK2MSFTNGP10.phx.gbl...
> I have one table named "serving" and I want that against serving ID there
> should be 42 records not more or less than that, how can I restrict that ?
> Waiting for your reply
> Thanks
> NOOR
>

How to restore the deleted records?

Hi,
Plz help me to restore deleted records.Pl zsuggest the ways to do it.
Thanks!!There's no way within the SQL Server product itself. Rows aren't "flagged for deletion" like they are in some products (which isn't strictly true, but close enough for this discussion). Once SQL deletes them, they are truly gone.

There are some third party products that mitigate this, such as Lumigent's Log Explorer.

-PatP|||Or restore the data from last good known backup ;)