Help with agent job to import from ODBC?

SQL Server 2005 Standard Edition.
Using wizard, no problem to use SQL to import from ODBC data source. Saved
the DTS job to an SSIS package, unable to use with agent scheduler.
Error is DTS_E_PRODUCTLEVELTOLOW.
Please help why is this happening?Frank
Do you want to move jobs to SQL Server 2005?
"Frank Ricciardi" <FrankRicciardi@.discussions.microsoft.com> wrote in
message news:0EE47C4D-589D-4B95-81EB-7F1DE5988A2E@.microsoft.com...
> SQL Server 2005 Standard Edition.
> Using wizard, no problem to use SQL to import from ODBC data source. Saved
> the DTS job to an SSIS package, unable to use with agent scheduler.
> Error is DTS_E_PRODUCTLEVELTOLOW.
> Please help why is this happening?|||I just want them to run as scheduled without operator intervention. I don't
really care how it do it.
Does that make sense?
thanks in advance..
"Uri Dimant" wrote:
> Frank
> Do you want to move jobs to SQL Server 2005?
>
> "Frank Ricciardi" <FrankRicciardi@.discussions.microsoft.com> wrote in
> message news:0EE47C4D-589D-4B95-81EB-7F1DE5988A2E@.microsoft.com...
> > SQL Server 2005 Standard Edition.
> >
> > Using wizard, no problem to use SQL to import from ODBC data source. Saved
> > the DTS job to an SSIS package, unable to use with agent scheduler.
> >
> > Error is DTS_E_PRODUCTLEVELTOLOW.
> >
> > Please help why is this happening?
>
>

Help with agent job to import from ODBC?

SQL Server 2005 Standard Edition.
Using wizard, no problem to use SQL to import from ODBC data source. Saved
the DTS job to an SSIS package, unable to use with agent scheduler.
Error is DTS_E_PRODUCTLEVELTOLOW.
Please help why is this happening?
Frank
Do you want to move jobs to SQL Server 2005?
"Frank Ricciardi" <FrankRicciardi@.discussions.microsoft.com> wrote in
message news:0EE47C4D-589D-4B95-81EB-7F1DE5988A2E@.microsoft.com...
> SQL Server 2005 Standard Edition.
> Using wizard, no problem to use SQL to import from ODBC data source. Saved
> the DTS job to an SSIS package, unable to use with agent scheduler.
> Error is DTS_E_PRODUCTLEVELTOLOW.
> Please help why is this happening?
|||I just want them to run as scheduled without operator intervention. I don't
really care how it do it.
Does that make sense?
thanks in advance..
"Uri Dimant" wrote:

> Frank
> Do you want to move jobs to SQL Server 2005?
>
> "Frank Ricciardi" <FrankRicciardi@.discussions.microsoft.com> wrote in
> message news:0EE47C4D-589D-4B95-81EB-7F1DE5988A2E@.microsoft.com...
>
>

Help with a SQL Query using temp tables

Hi All,

I have 4 temporary tables that hold criteria selected through a report wizard.
I've created a SQL statement and used the four tables in my WHERE/ AND clauses but the results retuned are not being filtered correctly.

Would somebody be kind enough to help me out please.

To briefly summarise, I have created a SQL statement that returns all rows in my recordset, I now need to implement some additional SQL to filter the recordset using my temporary tables, which contain the filters as follows:

(1) Temp table 1 (##tblTempAssetFilt) is mandatory and will always contain at least one row.
(2) Temp table 2 (##tblTempRepairTypeFilter) is optional and may never contain any rows. If this is the case then I have no reason to filter my resultset against this table.
(3) Temp table 3 (##tblTempRepairFilter) / Temp table 4 (##tblTempRepairElementFilter) are both optional, only one of these tables will contain data at one time. Again, as an optional filter the tables may never contain rows, and thus need to be ignored.

I have the following SQL, can somebody tell me how I would go about filtering the recordset using the temporary tables. The creation of the temporary tables occurs at the beginning so will always exist even when no rows have been inserted.

SELECT *
FROM tblActualWork [ActualWork]
JOIN tblRepair [Repair] ON ActualWork.intRepairID = Repair.intRepairID
JOIN tblRepairElement [RepairElement] ON Repair.intRepairElementID = RepairElement.intRepairElementID
JOIN tblRepairType [RepairType] ON Repair.intRepairTypeID = RepairType.intRepairTypeID
JOIN tblAsset [Asset] ON ActualWork.intAssetID = Asset.intAssetID
WHERE ActualWork.intAssetID IN (Select intAssetID From ##tblTempAssetFilter) AND Repair.intRepairTypeID IN (Select intRepairTypeID From ##tblTempRepairTypeFilter)
AND Repair.intRepairID IN (Select intRepairID From ##tblTempRepairFilter)
AND Repair.intRepairElementID IN (Select intRepairElementID From ##tblTempRepairElementFilter)

Any filtering must be based on the recordset filtered by temp table 1, which is a mandatory filter. Rows will always exist in this temp table.

Please help, not having much joy with this. Many thanks.Basically all I did was move your manditory temp table out of the where clause and add ORs to the where so that if a table was empty you would evaluate to TRUE for that table. Maybe not the best solution but this should get you going.

SELECT *
FROM tblActualWork [ActualWork]
JOIN tblRepair [Repair] ON ActualWork.intRepairID = Repair.intRepairID
JOIN tblRepairElement [RepairElement] ON Repair.intRepairElementID = RepairElement.intRepairElementID
JOIN tblRepairType [RepairType] ON Repair.intRepairTypeID = RepairType.intRepairTypeID
JOIN tblAsset [Asset] ON ActualWork.intAssetID = Asset.intAssetID
join ##tblTempAssetFilter [TempAssetFilter] on ActualWork.intAssetID = TempAssetFilter.intAssetID
WHERE (Repair.intRepairTypeID IN (Select intRepairTypeID From ##tblTempRepairTypeFilter) or not exists(select * from ##tblTempRepairTypeFilter)
AND (Repair.intRepairID IN (Select intRepairID From ##tblTempRepairFilter) or not exists(select * from ##tblTempRepairFilter)
AND (Repair.intRepairElementID IN (Select intRepairElementID From ##tblTempRepairElementFilter) or not exists(select * from ##tblTempRepairElementFilter)|||Paul,

Thank you for your reply, this has indeed fixed my problem. I am now returning a recordset with the desired results using my temporary tables.

Thanks again.|||Cool!

Here is a twist, if you do not need global temp tables I would switch to using table variables, there are some restrictions to using table variables but they can be much faster.

Also, if you can pre test the emptiness of your tables and store the reslts in a bit variable you can speed things up even more. As it is you are testing for empty temp tables each time you move to a new row.

Help using copy database wizard

I am trying to copy a database from one sql server 2005 to another. I am a sysadmin on both servers. I first tried copying the same database on the same server using the wizard and it worked :). However when I tried copying it to a different server, Execute SQL Server Agent Job fails. Unfortunately, I don't know the exact error; the error message ought to have been logged to windows event log, however after I open the event viewer, I can't find any error whatsoever logged to application. I tried having the error logged to a local directory as a text file and after the error occured, I still cannot find the error log :(
The difference between copying a database from the same server to copying on different servers is the "Location of Source Database Files", included in the wizard. I am not sure what to place in the field "File share on source server". Do I need to create a file share folder? Where can I have information on how and why it is necessary?
Thanks for taking your time to help me.

Candy

The online or local help for Copy Database Wizard goes into more detail, but I can give a couple of possible tips:

1. Remote job event logs or files will show up on the destination server machine not the machine running CDW (if they are different machines). Look in the WIndows event log on that machine, or in the file system there if you redirected error output logging to a file.

2. Location of Source Database Files refers to the path to the .mdf/.ldf files of the source datatase as seen from the destination machine. Usually in the form of a net share path of the form \\machine\share\... which you share out to get at the data files for the database you want to copy. CDW will invoke a job on the destination machine under either your domain account or some proxy account (depending on how you indicate it) which will use the file share path you gave it in the wizard to try to access the source machine's database data and log files. Share security permissions are also material just like you were trying to map the source machine share and copy the files yourself using Windows Explorer on the destination machine.

Hope that helps, and the online or local help can give some more details on what I summarized above.

|||What is the service pack level on both SQL Server instances?|||I am running service pack 2 on the source machine and service pack 1 on the destination machine.|||

Best way to solve look microsoft online help issue about SQL server 2005

Thanks

Faiz Farazi

www.databasetimes.net

Best learning center for Microsoft

http://www.lascomp.com

Help setting up Peer-to-peer replication for approximately 500 db'

We are looking for a script that will setup Peer-to-peer replication for
approximately 500 databases.
Obviously the Wizard will do it, but it would be extremely time consuming.
And, after setting up the publication you would still have to setup the
Peer-to-Peer topology.
Is there a simple way script the entire process?
peer-to-peer is only really scalable to 10 or so nodes.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Jarek Gal" <Jarek Gal@.discussions.microsoft.com> wrote in message
news:916ACC30-728E-4AA6-9D7D-0CDA7DD50407@.microsoft.com...
> We are looking for a script that will setup Peer-to-peer replication for
> approximately 500 databases.
> Obviously the Wizard will do it, but it would be extremely time consuming.
> And, after setting up the publication you would still have to setup the
> Peer-to-Peer topology.
> Is there a simple way script the entire process?
|||We have 2 SQL servers but need to do that 500 times. So the 10 nodes is not
an issue.
"Hilary Cotter" wrote:

> peer-to-peer is only really scalable to 10 or so nodes.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Jarek Gal" <Jarek Gal@.discussions.microsoft.com> wrote in message
> news:916ACC30-728E-4AA6-9D7D-0CDA7DD50407@.microsoft.com...
>
>
|||Once you've set it up for one node, scripting it out and amending the
scripts for each subsequent node shouldn't be too difficult:
http://www.replicationanswers.com/Script3.asp. You'll still need to get the
backup files restored on each node before commencing though.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .