Hi there - i am relatively new to SQL server so please bear with me. I
am attempting to join several tables so that I can generate an asset
report. The query I am runnins is
-- Start of Main Select Query
select t_humanresources.fname as 'First Name',
t_humanresources.lname as 'Last Name',
t_humanresources.hridentifier1 as 'HR Identifier',
t_humanresources.locationid1 as 'Location'
--t_catalogs.model as 'Catalog Item Name',
--t_catalogs.manufacturerproductcode
from T_Catalogs
inner join t_assets
on t_catalogs.t_catalog_id = t_assets.t_catalog_id
inner join t_Teamresponsible_LUT
on t_assets.t_teamresponsible_id =
t_teamresponsible_lut.t_teamresponsible_id
inner join t_internallocations
on t_assets.t_internallocation_id =
t_internallocations.t_internallocation_id
inner join t_activedescriptions_LUT
on t_assets.t_activedescription_id =
t_activedescriptions_LUT.t_activedescription_id
inner join t_category_lut
on t_assets.t_category_id = t_category_lut.t_category_id
inner join t_assetusers
on t_assets.t_asset_id = t_assetusers.t_asset_id
inner join t_humanresources
on t_assetusers.t_hr_id = t_humanresources.t_hr_id
where t_humanresources.Lname = 'Capp'
My problem is when I run the above query I am getting multiple returns
against the particular surname even though there is only 1 person with
the surname in the particular t_humanresources table. The same is
happening for other fields i have listed as well (although I have
commented these out above).
Does anyone have any ideas as to why this is happening as all I want to
see is a list of Users and the particular hardware assigned to them.
Thanks
Ronnie
<ronnie.walker@.celticgems.co.uk> wrote in message
news:1123857858.974507.278050@.z14g2000cwz.googlegr oups.com...
> Does anyone have any ideas as to why this is happening as all I want to
> see is a list of Users and the particular hardware assigned to them.
Can't users have more than one piece of hardware assigned to them?
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
|||Try to use Left Join instead of Inner Join.
Perayu
"ronnie.walker@.celticgems.co.uk" wrote:
> Hi there - i am relatively new to SQL server so please bear with me. I
> am attempting to join several tables so that I can generate an asset
> report. The query I am runnins is
> -- Start of Main Select Query
> select t_humanresources.fname as 'First Name',
> t_humanresources.lname as 'Last Name',
> t_humanresources.hridentifier1 as 'HR Identifier',
> t_humanresources.locationid1 as 'Location'
> --t_catalogs.model as 'Catalog Item Name',
> --t_catalogs.manufacturerproductcode
> from T_Catalogs
> inner join t_assets
> on t_catalogs.t_catalog_id = t_assets.t_catalog_id
> inner join t_Teamresponsible_LUT
> on t_assets.t_teamresponsible_id =
> t_teamresponsible_lut.t_teamresponsible_id
> inner join t_internallocations
> on t_assets.t_internallocation_id =
> t_internallocations.t_internallocation_id
> inner join t_activedescriptions_LUT
> on t_assets.t_activedescription_id =
> t_activedescriptions_LUT.t_activedescription_id
> inner join t_category_lut
> on t_assets.t_category_id = t_category_lut.t_category_id
> inner join t_assetusers
> on t_assets.t_asset_id = t_assetusers.t_asset_id
> inner join t_humanresources
> on t_assetusers.t_hr_id = t_humanresources.t_hr_id
> where t_humanresources.Lname = 'Capp'
>
> My problem is when I run the above query I am getting multiple returns
> against the particular surname even though there is only 1 person with
> the surname in the particular t_humanresources table. The same is
> happening for other fields i have listed as well (although I have
> commented these out above).
> Does anyone have any ideas as to why this is happening as all I want to
> see is a list of Users and the particular hardware assigned to them.
> Thanks
> Ronnie
>
|||On 12 Aug 2005 07:44:19 -0700, ronnie.walker@.celticgems.co.uk wrote:
>Hi there - i am relatively new to SQL server so please bear with me. I
>am attempting to join several tables so that I can generate an asset
>report. The query I am runnins is
(snip)
Hi Ronnie,
In this query, you are joining to several tables that are not used. If
one row from your data matches several rows in those unused tables, the
row will appear to be duplicated - each occurence of the "duplicated"
row is linked to another row in the other table, but you can't see that
because those values are not displayed.
Try what happens if you remove the unused tables:
-- Start of Main Select Query
select t_humanresources.fname as 'First Name',
t_humanresources.lname as 'Last Name',
t_humanresources.hridentifier1 as 'HR Identifier',
t_humanresources.locationid1 as 'Location',
t_catalogs.model as 'Catalog Item Name',
t_catalogs.manufacturerproductcode
from T_Catalogs
inner join t_assets
on t_catalogs.t_catalog_id = t_assets.t_catalog_id
inner join t_assetusers
on t_assets.t_asset_id = t_assetusers.t_asset_id
inner join t_humanresources
on t_assetusers.t_hr_id = t_humanresources.t_hr_id
where t_humanresources.Lname = 'Capp'
And if you comment the two columns you take from t_catalogs, then you
should remove that table from the FROM clause as well.
If the above doesn't help, then see www.aspfaq.com/5006.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||On Fri, 12 Aug 2005 11:25:57 -0700, Perayu wrote:
>Try to use Left Join instead of Inner Join.
>Perayu
Hi Perayu,
How would a left join help to remove unwanted duplicates from the
results of the query?
I do see how it might return even more unwanted rows. And how it would
lower performance. But removing duplicates?
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
Showing posts with label relatively. Show all posts
Showing posts with label relatively. Show all posts
Tuesday, March 20, 2012
Question on results from Table Joins.
Hi there - i am relatively new to SQL server so please bear with me. I
am attempting to join several tables so that I can generate an asset
report. The query I am runnins is
-- Start of Main Select Query
select t_humanresources.fname as 'First Name',
t_humanresources.lname as 'Last Name',
t_humanresources.hridentifier1 as 'HR Identifier',
t_humanresources.locationid1 as 'Location'
--t_catalogs.model as 'Catalog Item Name',
--t_catalogs.manufacturerproductcode
from T_Catalogs
inner join t_assets
on t_catalogs.t_catalog_id = t_assets.t_catalog_id
inner join t_Teamresponsible_LUT
on t_assets.t_teamresponsible_id = t_teamresponsible_lut.t_teamresponsible_id
inner join t_internallocations
on t_assets.t_internallocation_id = t_internallocations.t_internallocation_id
inner join t_activedescriptions_LUT
on t_assets.t_activedescription_id = t_activedescriptions_LUT.t_activedescription_id
inner join t_category_lut
on t_assets.t_category_id = t_category_lut.t_category_id
inner join t_assetusers
on t_assets.t_asset_id = t_assetusers.t_asset_id
inner join t_humanresources
on t_assetusers.t_hr_id = t_humanresources.t_hr_id
where t_humanresources.Lname = 'Capp'
My problem is when I run the above query I am getting multiple returns
against the particular surname even though there is only 1 person with
the surname in the particular t_humanresources table. The same is
happening for other fields i have listed as well (although I have
commented these out above).
Does anyone have any ideas as to why this is happening as all I want to
see is a list of Users and the particular hardware assigned to them.
Thanks
Ronnie<ronnie.walker@.celticgems.co.uk> wrote in message
news:1123857858.974507.278050@.z14g2000cwz.googlegroups.com...
> Does anyone have any ideas as to why this is happening as all I want to
> see is a list of Users and the particular hardware assigned to them.
Can't users have more than one piece of hardware assigned to them?
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--|||Try to use Left Join instead of Inner Join.
Perayu
"ronnie.walker@.celticgems.co.uk" wrote:
> Hi there - i am relatively new to SQL server so please bear with me. I
> am attempting to join several tables so that I can generate an asset
> report. The query I am runnins is
> -- Start of Main Select Query
> select t_humanresources.fname as 'First Name',
> t_humanresources.lname as 'Last Name',
> t_humanresources.hridentifier1 as 'HR Identifier',
> t_humanresources.locationid1 as 'Location'
> --t_catalogs.model as 'Catalog Item Name',
> --t_catalogs.manufacturerproductcode
> from T_Catalogs
> inner join t_assets
> on t_catalogs.t_catalog_id = t_assets.t_catalog_id
> inner join t_Teamresponsible_LUT
> on t_assets.t_teamresponsible_id => t_teamresponsible_lut.t_teamresponsible_id
> inner join t_internallocations
> on t_assets.t_internallocation_id => t_internallocations.t_internallocation_id
> inner join t_activedescriptions_LUT
> on t_assets.t_activedescription_id => t_activedescriptions_LUT.t_activedescription_id
> inner join t_category_lut
> on t_assets.t_category_id = t_category_lut.t_category_id
> inner join t_assetusers
> on t_assets.t_asset_id = t_assetusers.t_asset_id
> inner join t_humanresources
> on t_assetusers.t_hr_id = t_humanresources.t_hr_id
> where t_humanresources.Lname = 'Capp'
>
> My problem is when I run the above query I am getting multiple returns
> against the particular surname even though there is only 1 person with
> the surname in the particular t_humanresources table. The same is
> happening for other fields i have listed as well (although I have
> commented these out above).
> Does anyone have any ideas as to why this is happening as all I want to
> see is a list of Users and the particular hardware assigned to them.
> Thanks
> Ronnie
>|||On 12 Aug 2005 07:44:19 -0700, ronnie.walker@.celticgems.co.uk wrote:
>Hi there - i am relatively new to SQL server so please bear with me. I
>am attempting to join several tables so that I can generate an asset
>report. The query I am runnins is
(snip)
Hi Ronnie,
In this query, you are joining to several tables that are not used. If
one row from your data matches several rows in those unused tables, the
row will appear to be duplicated - each occurence of the "duplicated"
row is linked to another row in the other table, but you can't see that
because those values are not displayed.
Try what happens if you remove the unused tables:
-- Start of Main Select Query
select t_humanresources.fname as 'First Name',
t_humanresources.lname as 'Last Name',
t_humanresources.hridentifier1 as 'HR Identifier',
t_humanresources.locationid1 as 'Location',
t_catalogs.model as 'Catalog Item Name',
t_catalogs.manufacturerproductcode
from T_Catalogs
inner join t_assets
on t_catalogs.t_catalog_id = t_assets.t_catalog_id
inner join t_assetusers
on t_assets.t_asset_id = t_assetusers.t_asset_id
inner join t_humanresources
on t_assetusers.t_hr_id = t_humanresources.t_hr_id
where t_humanresources.Lname = 'Capp'
And if you comment the two columns you take from t_catalogs, then you
should remove that table from the FROM clause as well.
If the above doesn't help, then see www.aspfaq.com/5006.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||On Fri, 12 Aug 2005 11:25:57 -0700, Perayu wrote:
>Try to use Left Join instead of Inner Join.
>Perayu
Hi Perayu,
How would a left join help to remove unwanted duplicates from the
results of the query?
I do see how it might return even more unwanted rows. And how it would
lower performance. But removing duplicates?
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
am attempting to join several tables so that I can generate an asset
report. The query I am runnins is
-- Start of Main Select Query
select t_humanresources.fname as 'First Name',
t_humanresources.lname as 'Last Name',
t_humanresources.hridentifier1 as 'HR Identifier',
t_humanresources.locationid1 as 'Location'
--t_catalogs.model as 'Catalog Item Name',
--t_catalogs.manufacturerproductcode
from T_Catalogs
inner join t_assets
on t_catalogs.t_catalog_id = t_assets.t_catalog_id
inner join t_Teamresponsible_LUT
on t_assets.t_teamresponsible_id = t_teamresponsible_lut.t_teamresponsible_id
inner join t_internallocations
on t_assets.t_internallocation_id = t_internallocations.t_internallocation_id
inner join t_activedescriptions_LUT
on t_assets.t_activedescription_id = t_activedescriptions_LUT.t_activedescription_id
inner join t_category_lut
on t_assets.t_category_id = t_category_lut.t_category_id
inner join t_assetusers
on t_assets.t_asset_id = t_assetusers.t_asset_id
inner join t_humanresources
on t_assetusers.t_hr_id = t_humanresources.t_hr_id
where t_humanresources.Lname = 'Capp'
My problem is when I run the above query I am getting multiple returns
against the particular surname even though there is only 1 person with
the surname in the particular t_humanresources table. The same is
happening for other fields i have listed as well (although I have
commented these out above).
Does anyone have any ideas as to why this is happening as all I want to
see is a list of Users and the particular hardware assigned to them.
Thanks
Ronnie<ronnie.walker@.celticgems.co.uk> wrote in message
news:1123857858.974507.278050@.z14g2000cwz.googlegroups.com...
> Does anyone have any ideas as to why this is happening as all I want to
> see is a list of Users and the particular hardware assigned to them.
Can't users have more than one piece of hardware assigned to them?
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--|||Try to use Left Join instead of Inner Join.
Perayu
"ronnie.walker@.celticgems.co.uk" wrote:
> Hi there - i am relatively new to SQL server so please bear with me. I
> am attempting to join several tables so that I can generate an asset
> report. The query I am runnins is
> -- Start of Main Select Query
> select t_humanresources.fname as 'First Name',
> t_humanresources.lname as 'Last Name',
> t_humanresources.hridentifier1 as 'HR Identifier',
> t_humanresources.locationid1 as 'Location'
> --t_catalogs.model as 'Catalog Item Name',
> --t_catalogs.manufacturerproductcode
> from T_Catalogs
> inner join t_assets
> on t_catalogs.t_catalog_id = t_assets.t_catalog_id
> inner join t_Teamresponsible_LUT
> on t_assets.t_teamresponsible_id => t_teamresponsible_lut.t_teamresponsible_id
> inner join t_internallocations
> on t_assets.t_internallocation_id => t_internallocations.t_internallocation_id
> inner join t_activedescriptions_LUT
> on t_assets.t_activedescription_id => t_activedescriptions_LUT.t_activedescription_id
> inner join t_category_lut
> on t_assets.t_category_id = t_category_lut.t_category_id
> inner join t_assetusers
> on t_assets.t_asset_id = t_assetusers.t_asset_id
> inner join t_humanresources
> on t_assetusers.t_hr_id = t_humanresources.t_hr_id
> where t_humanresources.Lname = 'Capp'
>
> My problem is when I run the above query I am getting multiple returns
> against the particular surname even though there is only 1 person with
> the surname in the particular t_humanresources table. The same is
> happening for other fields i have listed as well (although I have
> commented these out above).
> Does anyone have any ideas as to why this is happening as all I want to
> see is a list of Users and the particular hardware assigned to them.
> Thanks
> Ronnie
>|||On 12 Aug 2005 07:44:19 -0700, ronnie.walker@.celticgems.co.uk wrote:
>Hi there - i am relatively new to SQL server so please bear with me. I
>am attempting to join several tables so that I can generate an asset
>report. The query I am runnins is
(snip)
Hi Ronnie,
In this query, you are joining to several tables that are not used. If
one row from your data matches several rows in those unused tables, the
row will appear to be duplicated - each occurence of the "duplicated"
row is linked to another row in the other table, but you can't see that
because those values are not displayed.
Try what happens if you remove the unused tables:
-- Start of Main Select Query
select t_humanresources.fname as 'First Name',
t_humanresources.lname as 'Last Name',
t_humanresources.hridentifier1 as 'HR Identifier',
t_humanresources.locationid1 as 'Location',
t_catalogs.model as 'Catalog Item Name',
t_catalogs.manufacturerproductcode
from T_Catalogs
inner join t_assets
on t_catalogs.t_catalog_id = t_assets.t_catalog_id
inner join t_assetusers
on t_assets.t_asset_id = t_assetusers.t_asset_id
inner join t_humanresources
on t_assetusers.t_hr_id = t_humanresources.t_hr_id
where t_humanresources.Lname = 'Capp'
And if you comment the two columns you take from t_catalogs, then you
should remove that table from the FROM clause as well.
If the above doesn't help, then see www.aspfaq.com/5006.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||On Fri, 12 Aug 2005 11:25:57 -0700, Perayu wrote:
>Try to use Left Join instead of Inner Join.
>Perayu
Hi Perayu,
How would a left join help to remove unwanted duplicates from the
results of the query?
I do see how it might return even more unwanted rows. And how it would
lower performance. But removing duplicates?
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
Question on results from Table Joins.
Hi there - i am relatively new to SQL server so please bear with me. I
am attempting to join several tables so that I can generate an asset
report. The query I am runnins is
-- Start of Main Select Query
select t_humanresources.fname as 'First Name',
t_humanresources.lname as 'Last Name',
t_humanresources.hridentifier1 as 'HR Identifier',
t_humanresources.locationid1 as 'Location'
--t_catalogs.model as 'Catalog Item Name',
--t_catalogs.manufacturerproductcode
from T_Catalogs
inner join t_assets
on t_catalogs.t_catalog_id = t_assets.t_catalog_id
inner join t_Teamresponsible_LUT
on t_assets.t_teamresponsible_id =
t_teamresponsible_lut.t_teamresponsible_id
inner join t_internallocations
on t_assets.t_internallocation_id =
t_internallocations.t_internallocation_id
inner join t_activedescriptions_LUT
on t_assets.t_activedescription_id =
t_activedescriptions_LUT.t_activedescription_id
inner join t_category_lut
on t_assets.t_category_id = t_category_lut.t_category_id
inner join t_assetusers
on t_assets.t_asset_id = t_assetusers.t_asset_id
inner join t_humanresources
on t_assetusers.t_hr_id = t_humanresources.t_hr_id
where t_humanresources.Lname = 'Capp'
My problem is when I run the above query I am getting multiple returns
against the particular surname even though there is only 1 person with
the surname in the particular t_humanresources table. The same is
happening for other fields i have listed as well (although I have
commented these out above).
Does anyone have any ideas as to why this is happening as all I want to
see is a list of Users and the particular hardware assigned to them.
Thanks
Ronnie<ronnie.walker@.celticgems.co.uk> wrote in message
news:1123857858.974507.278050@.z14g2000cwz.googlegroups.com...
> Does anyone have any ideas as to why this is happening as all I want to
> see is a list of Users and the particular hardware assigned to them.
Can't users have more than one piece of hardware assigned to them?
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--|||Try to use Left Join instead of Inner Join.
Perayu
"ronnie.walker@.celticgems.co.uk" wrote:
> Hi there - i am relatively new to SQL server so please bear with me. I
> am attempting to join several tables so that I can generate an asset
> report. The query I am runnins is
> -- Start of Main Select Query
> select t_humanresources.fname as 'First Name',
> t_humanresources.lname as 'Last Name',
> t_humanresources.hridentifier1 as 'HR Identifier',
> t_humanresources.locationid1 as 'Location'
> --t_catalogs.model as 'Catalog Item Name',
> --t_catalogs.manufacturerproductcode
> from T_Catalogs
> inner join t_assets
> on t_catalogs.t_catalog_id = t_assets.t_catalog_id
> inner join t_Teamresponsible_LUT
> on t_assets.t_teamresponsible_id =
> t_teamresponsible_lut.t_teamresponsible_id
> inner join t_internallocations
> on t_assets.t_internallocation_id =
> t_internallocations.t_internallocation_id
> inner join t_activedescriptions_LUT
> on t_assets.t_activedescription_id =
> t_activedescriptions_LUT.t_activedescription_id
> inner join t_category_lut
> on t_assets.t_category_id = t_category_lut.t_category_id
> inner join t_assetusers
> on t_assets.t_asset_id = t_assetusers.t_asset_id
> inner join t_humanresources
> on t_assetusers.t_hr_id = t_humanresources.t_hr_id
> where t_humanresources.Lname = 'Capp'
>
> My problem is when I run the above query I am getting multiple returns
> against the particular surname even though there is only 1 person with
> the surname in the particular t_humanresources table. The same is
> happening for other fields i have listed as well (although I have
> commented these out above).
> Does anyone have any ideas as to why this is happening as all I want to
> see is a list of Users and the particular hardware assigned to them.
> Thanks
> Ronnie
>|||On 12 Aug 2005 07:44:19 -0700, ronnie.walker@.celticgems.co.uk wrote:
>Hi there - i am relatively new to SQL server so please bear with me. I
>am attempting to join several tables so that I can generate an asset
>report. The query I am runnins is
(snip)
Hi Ronnie,
In this query, you are joining to several tables that are not used. If
one row from your data matches several rows in those unused tables, the
row will appear to be duplicated - each occurence of the "duplicated"
row is linked to another row in the other table, but you can't see that
because those values are not displayed.
Try what happens if you remove the unused tables:
-- Start of Main Select Query
select t_humanresources.fname as 'First Name',
t_humanresources.lname as 'Last Name',
t_humanresources.hridentifier1 as 'HR Identifier',
t_humanresources.locationid1 as 'Location',
t_catalogs.model as 'Catalog Item Name',
t_catalogs.manufacturerproductcode
from T_Catalogs
inner join t_assets
on t_catalogs.t_catalog_id = t_assets.t_catalog_id
inner join t_assetusers
on t_assets.t_asset_id = t_assetusers.t_asset_id
inner join t_humanresources
on t_assetusers.t_hr_id = t_humanresources.t_hr_id
where t_humanresources.Lname = 'Capp'
And if you comment the two columns you take from t_catalogs, then you
should remove that table from the FROM clause as well.
If the above doesn't help, then see www.aspfaq.com/5006.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||On Fri, 12 Aug 2005 11:25:57 -0700, Perayu wrote:
>Try to use Left Join instead of Inner Join.
>Perayu
Hi Perayu,
How would a left join help to remove unwanted duplicates from the
results of the query?
I do see how it might return even more unwanted rows. And how it would
lower performance. But removing duplicates?
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
am attempting to join several tables so that I can generate an asset
report. The query I am runnins is
-- Start of Main Select Query
select t_humanresources.fname as 'First Name',
t_humanresources.lname as 'Last Name',
t_humanresources.hridentifier1 as 'HR Identifier',
t_humanresources.locationid1 as 'Location'
--t_catalogs.model as 'Catalog Item Name',
--t_catalogs.manufacturerproductcode
from T_Catalogs
inner join t_assets
on t_catalogs.t_catalog_id = t_assets.t_catalog_id
inner join t_Teamresponsible_LUT
on t_assets.t_teamresponsible_id =
t_teamresponsible_lut.t_teamresponsible_id
inner join t_internallocations
on t_assets.t_internallocation_id =
t_internallocations.t_internallocation_id
inner join t_activedescriptions_LUT
on t_assets.t_activedescription_id =
t_activedescriptions_LUT.t_activedescription_id
inner join t_category_lut
on t_assets.t_category_id = t_category_lut.t_category_id
inner join t_assetusers
on t_assets.t_asset_id = t_assetusers.t_asset_id
inner join t_humanresources
on t_assetusers.t_hr_id = t_humanresources.t_hr_id
where t_humanresources.Lname = 'Capp'
My problem is when I run the above query I am getting multiple returns
against the particular surname even though there is only 1 person with
the surname in the particular t_humanresources table. The same is
happening for other fields i have listed as well (although I have
commented these out above).
Does anyone have any ideas as to why this is happening as all I want to
see is a list of Users and the particular hardware assigned to them.
Thanks
Ronnie<ronnie.walker@.celticgems.co.uk> wrote in message
news:1123857858.974507.278050@.z14g2000cwz.googlegroups.com...
> Does anyone have any ideas as to why this is happening as all I want to
> see is a list of Users and the particular hardware assigned to them.
Can't users have more than one piece of hardware assigned to them?
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--|||Try to use Left Join instead of Inner Join.
Perayu
"ronnie.walker@.celticgems.co.uk" wrote:
> Hi there - i am relatively new to SQL server so please bear with me. I
> am attempting to join several tables so that I can generate an asset
> report. The query I am runnins is
> -- Start of Main Select Query
> select t_humanresources.fname as 'First Name',
> t_humanresources.lname as 'Last Name',
> t_humanresources.hridentifier1 as 'HR Identifier',
> t_humanresources.locationid1 as 'Location'
> --t_catalogs.model as 'Catalog Item Name',
> --t_catalogs.manufacturerproductcode
> from T_Catalogs
> inner join t_assets
> on t_catalogs.t_catalog_id = t_assets.t_catalog_id
> inner join t_Teamresponsible_LUT
> on t_assets.t_teamresponsible_id =
> t_teamresponsible_lut.t_teamresponsible_id
> inner join t_internallocations
> on t_assets.t_internallocation_id =
> t_internallocations.t_internallocation_id
> inner join t_activedescriptions_LUT
> on t_assets.t_activedescription_id =
> t_activedescriptions_LUT.t_activedescription_id
> inner join t_category_lut
> on t_assets.t_category_id = t_category_lut.t_category_id
> inner join t_assetusers
> on t_assets.t_asset_id = t_assetusers.t_asset_id
> inner join t_humanresources
> on t_assetusers.t_hr_id = t_humanresources.t_hr_id
> where t_humanresources.Lname = 'Capp'
>
> My problem is when I run the above query I am getting multiple returns
> against the particular surname even though there is only 1 person with
> the surname in the particular t_humanresources table. The same is
> happening for other fields i have listed as well (although I have
> commented these out above).
> Does anyone have any ideas as to why this is happening as all I want to
> see is a list of Users and the particular hardware assigned to them.
> Thanks
> Ronnie
>|||On 12 Aug 2005 07:44:19 -0700, ronnie.walker@.celticgems.co.uk wrote:
>Hi there - i am relatively new to SQL server so please bear with me. I
>am attempting to join several tables so that I can generate an asset
>report. The query I am runnins is
(snip)
Hi Ronnie,
In this query, you are joining to several tables that are not used. If
one row from your data matches several rows in those unused tables, the
row will appear to be duplicated - each occurence of the "duplicated"
row is linked to another row in the other table, but you can't see that
because those values are not displayed.
Try what happens if you remove the unused tables:
-- Start of Main Select Query
select t_humanresources.fname as 'First Name',
t_humanresources.lname as 'Last Name',
t_humanresources.hridentifier1 as 'HR Identifier',
t_humanresources.locationid1 as 'Location',
t_catalogs.model as 'Catalog Item Name',
t_catalogs.manufacturerproductcode
from T_Catalogs
inner join t_assets
on t_catalogs.t_catalog_id = t_assets.t_catalog_id
inner join t_assetusers
on t_assets.t_asset_id = t_assetusers.t_asset_id
inner join t_humanresources
on t_assetusers.t_hr_id = t_humanresources.t_hr_id
where t_humanresources.Lname = 'Capp'
And if you comment the two columns you take from t_catalogs, then you
should remove that table from the FROM clause as well.
If the above doesn't help, then see www.aspfaq.com/5006.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||On Fri, 12 Aug 2005 11:25:57 -0700, Perayu wrote:
>Try to use Left Join instead of Inner Join.
>Perayu
Hi Perayu,
How would a left join help to remove unwanted duplicates from the
results of the query?
I do see how it might return even more unwanted rows. And how it would
lower performance. But removing duplicates?
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
Wednesday, March 7, 2012
Question on db maintenance scripts
Relatively new to SQL Server, running both 2000 and 2005.
I have to set up database maintenance scripts, db and log backups, optimizations etc in batch files so that they can be run by an external scheduler.
When I produce a script from the SQL Server database maintenance wizards and save it off in a bat file, the script will not work when run manually, probably due to the difference in syntax.
I have some examples of backup scripts using sqlmaint.exe, but can't find anything on how to script optimization/reorg jobs so that they can be run by an external scheduler.
Can anybody point me to any docs or guidance on how to produce backup and reorg scripts that can be run externally?
Thanks in advance.
GerryIt sounds like you want to use the osql utility. Go to BOL and look it up uner the index tab. When you double-click on the index utility, chose the "osql utility" under the Command Pronpt Utilities.
I have to set up database maintenance scripts, db and log backups, optimizations etc in batch files so that they can be run by an external scheduler.
When I produce a script from the SQL Server database maintenance wizards and save it off in a bat file, the script will not work when run manually, probably due to the difference in syntax.
I have some examples of backup scripts using sqlmaint.exe, but can't find anything on how to script optimization/reorg jobs so that they can be run by an external scheduler.
Can anybody point me to any docs or guidance on how to produce backup and reorg scripts that can be run externally?
Thanks in advance.
GerryIt sounds like you want to use the osql utility. Go to BOL and look it up uner the index tab. When you double-click on the index utility, chose the "osql utility" under the Command Pronpt Utilities.
Question on db maintenance scripts
Relatively new to SQL Server, running both 2000 and 2005.
I have to set up database maintenance scripts, db and log backups,
optimizations etc in batch files so that they can be run by an external
scheduler.
When I produce a script from the SQL Server database maintenance
wizards and save it off in a bat file, the script will not work when
run manually, probably due to the difference in syntax.
I have some examples of backup scripts using sqlmaint.exe, but can't
find anything on how to script optimization/reorg jobs so that they can
be run by an external scheduler.
Can anybody point me to any docs or guidance on this?
Thanks in advance.
Gerry
Tibor:
Thanks for the advice. I will do that.
On 2005 we have a scheduler (Control M) running business processing
jobs outside of SQL Server and I will have to schedule jobs that are
dependent on these jobs finishing.
How would you approach a db maintenance job that was job dependent?
Tibor Karaszi wrote:
> I suggest you skip the scripting part and read up on sqlmaint.exe. So instead of calling
> xp_sqlmaint, which in turn call sqlmaint.exe, you call sqlmaint.exe directly. this gives you much
> better control and options.
> 2005 is another story, where a maint plan is an SSIS job, but you can just let the Wizard and
> designer create package for you and schedule a job using DTEXEC.EXE to execute that package.
>
|||Tibor:
I have to empty certain tables every evening. and that emptying task is
dependent on another job finishing.
I'm thinking an sql statement...but with something like this is
sqlmaint.exe used or should I be looking at osql utility or something
else.
Thanks in advance
Gerry
Tibor Karaszi wrote:[vbcol=seagreen]
>
> Not sure I understand your question, as Control M apparently already has that feature. Can you
> elaborate?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "DataPro" <datapro01@.yahoo.com> wrote in message
> news:1164735316.116658.270460@.j44g2000cwa.googlegr oups.com...
|||Tibor Karaszi wrote:
> Yes, you can use OSQL.EXE with either an input file or specify the TUNCATE TABLE or DELETE command
> using the /Q switch for OSQL.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
Thanks much
I have to set up database maintenance scripts, db and log backups,
optimizations etc in batch files so that they can be run by an external
scheduler.
When I produce a script from the SQL Server database maintenance
wizards and save it off in a bat file, the script will not work when
run manually, probably due to the difference in syntax.
I have some examples of backup scripts using sqlmaint.exe, but can't
find anything on how to script optimization/reorg jobs so that they can
be run by an external scheduler.
Can anybody point me to any docs or guidance on this?
Thanks in advance.
Gerry
Tibor:
Thanks for the advice. I will do that.
On 2005 we have a scheduler (Control M) running business processing
jobs outside of SQL Server and I will have to schedule jobs that are
dependent on these jobs finishing.
How would you approach a db maintenance job that was job dependent?
Tibor Karaszi wrote:
> I suggest you skip the scripting part and read up on sqlmaint.exe. So instead of calling
> xp_sqlmaint, which in turn call sqlmaint.exe, you call sqlmaint.exe directly. this gives you much
> better control and options.
> 2005 is another story, where a maint plan is an SSIS job, but you can just let the Wizard and
> designer create package for you and schedule a job using DTEXEC.EXE to execute that package.
>
|||Tibor:
I have to empty certain tables every evening. and that emptying task is
dependent on another job finishing.
I'm thinking an sql statement...but with something like this is
sqlmaint.exe used or should I be looking at osql utility or something
else.
Thanks in advance
Gerry
Tibor Karaszi wrote:[vbcol=seagreen]
>
> Not sure I understand your question, as Control M apparently already has that feature. Can you
> elaborate?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "DataPro" <datapro01@.yahoo.com> wrote in message
> news:1164735316.116658.270460@.j44g2000cwa.googlegr oups.com...
|||Tibor Karaszi wrote:
> Yes, you can use OSQL.EXE with either an input file or specify the TUNCATE TABLE or DELETE command
> using the /Q switch for OSQL.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
Thanks much
Saturday, February 25, 2012
Question on db maintenance scripts
Relatively new to SQL Server, running both 2000 and 2005.
I have to set up database maintenance scripts, db and log backups,
optimizations etc in batch files so that they can be run by an external
scheduler.
When I produce a script from the SQL Server database maintenance
wizards and save it off in a bat file, the script will not work when
run manually, probably due to the difference in syntax.
I have some examples of backup scripts using sqlmaint.exe, but can't
find anything on how to script optimization/reorg jobs so that they can
be run by an external scheduler.
Can anybody point me to any docs or guidance on this?
Thanks in advance.
GerryI suggest you skip the scripting part and read up on sqlmaint.exe. So instea
d of calling
xp_sqlmaint, which in turn call sqlmaint.exe, you call sqlmaint.exe directly
. this gives you much
better control and options.
2005 is another story, where a maint plan is an SSIS job, but you can just l
et the Wizard and
designer create package for you and schedule a job using DTEXEC.EXE to execu
te that package.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"DataPro" <datapro01@.yahoo.com> wrote in message
news:1164723926.064627.125300@.l12g2000cwl.googlegroups.com...
> Relatively new to SQL Server, running both 2000 and 2005.
> I have to set up database maintenance scripts, db and log backups,
> optimizations etc in batch files so that they can be run by an external
> scheduler.
> When I produce a script from the SQL Server database maintenance
> wizards and save it off in a bat file, the script will not work when
> run manually, probably due to the difference in syntax.
> I have some examples of backup scripts using sqlmaint.exe, but can't
> find anything on how to script optimization/reorg jobs so that they can
> be run by an external scheduler.
> Can anybody point me to any docs or guidance on this?
> Thanks in advance.
> Gerry
>|||Tibor:
Thanks for the advice. I will do that.
On 2005 we have a scheduler (Control M) running business processing
jobs outside of SQL Server and I will have to schedule jobs that are
dependent on these jobs finishing.
How would you approach a db maintenance job that was job dependent?
Tibor Karaszi wrote:
> I suggest you skip the scripting part and read up on sqlmaint.exe. So inst
ead of calling
> xp_sqlmaint, which in turn call sqlmaint.exe, you call sqlmaint.exe direct
ly. this gives you much
> better control and options.
> 2005 is another story, where a maint plan is an SSIS job, but you can just
let the Wizard and
> designer create package for you and schedule a job using DTEXEC.EXE to exe
cute that package.
>|||> How would you approach a db maintenance job that was job dependent?
Not sure I understand your question, as Control M apparently already has tha
t feature. Can you
elaborate?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"DataPro" <datapro01@.yahoo.com> wrote in message
news:1164735316.116658.270460@.j44g2000cwa.googlegroups.com...
> Tibor:
> Thanks for the advice. I will do that.
> On 2005 we have a scheduler (Control M) running business processing
> jobs outside of SQL Server and I will have to schedule jobs that are
> dependent on these jobs finishing.
> How would you approach a db maintenance job that was job dependent?
>
> Tibor Karaszi wrote:
>|||Tibor:
I have to empty certain tables every evening. and that emptying task is
dependent on another job finishing.
I'm thinking an sql statement...but with something like this is
sqlmaint.exe used or should I be looking at osql utility or something
else.
Thanks in advance
Gerry
Tibor Karaszi wrote:[vbcol=seagreen]
>
> Not sure I understand your question, as Control M apparently already has t
hat feature. Can you
> elaborate?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "DataPro" <datapro01@.yahoo.com> wrote in message
> news:1164735316.116658.270460@.j44g2000cwa.googlegroups.com...|||Yes, you can use OSQL.EXE with either an input file or specify the TUNCATE T
ABLE or DELETE command
using the /Q switch for OSQL.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"DataPro" <datapro01@.yahoo.com> wrote in message
news:1164801015.266092.235400@.l12g2000cwl.googlegroups.com...
> Tibor:
>
> I have to empty certain tables every evening. and that emptying task is
> dependent on another job finishing.
> I'm thinking an sql statement...but with something like this is
> sqlmaint.exe used or should I be looking at osql utility or something
> else.
> Thanks in advance
> Gerry
>
> Tibor Karaszi wrote:
>|||Tibor Karaszi wrote:
> Yes, you can use OSQL.EXE with either an input file or specify the TUNCATE
TABLE or DELETE command
> using the /Q switch for OSQL.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
Thanks much
I have to set up database maintenance scripts, db and log backups,
optimizations etc in batch files so that they can be run by an external
scheduler.
When I produce a script from the SQL Server database maintenance
wizards and save it off in a bat file, the script will not work when
run manually, probably due to the difference in syntax.
I have some examples of backup scripts using sqlmaint.exe, but can't
find anything on how to script optimization/reorg jobs so that they can
be run by an external scheduler.
Can anybody point me to any docs or guidance on this?
Thanks in advance.
GerryI suggest you skip the scripting part and read up on sqlmaint.exe. So instea
d of calling
xp_sqlmaint, which in turn call sqlmaint.exe, you call sqlmaint.exe directly
. this gives you much
better control and options.
2005 is another story, where a maint plan is an SSIS job, but you can just l
et the Wizard and
designer create package for you and schedule a job using DTEXEC.EXE to execu
te that package.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"DataPro" <datapro01@.yahoo.com> wrote in message
news:1164723926.064627.125300@.l12g2000cwl.googlegroups.com...
> Relatively new to SQL Server, running both 2000 and 2005.
> I have to set up database maintenance scripts, db and log backups,
> optimizations etc in batch files so that they can be run by an external
> scheduler.
> When I produce a script from the SQL Server database maintenance
> wizards and save it off in a bat file, the script will not work when
> run manually, probably due to the difference in syntax.
> I have some examples of backup scripts using sqlmaint.exe, but can't
> find anything on how to script optimization/reorg jobs so that they can
> be run by an external scheduler.
> Can anybody point me to any docs or guidance on this?
> Thanks in advance.
> Gerry
>|||Tibor:
Thanks for the advice. I will do that.
On 2005 we have a scheduler (Control M) running business processing
jobs outside of SQL Server and I will have to schedule jobs that are
dependent on these jobs finishing.
How would you approach a db maintenance job that was job dependent?
Tibor Karaszi wrote:
> I suggest you skip the scripting part and read up on sqlmaint.exe. So inst
ead of calling
> xp_sqlmaint, which in turn call sqlmaint.exe, you call sqlmaint.exe direct
ly. this gives you much
> better control and options.
> 2005 is another story, where a maint plan is an SSIS job, but you can just
let the Wizard and
> designer create package for you and schedule a job using DTEXEC.EXE to exe
cute that package.
>|||> How would you approach a db maintenance job that was job dependent?
Not sure I understand your question, as Control M apparently already has tha
t feature. Can you
elaborate?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"DataPro" <datapro01@.yahoo.com> wrote in message
news:1164735316.116658.270460@.j44g2000cwa.googlegroups.com...
> Tibor:
> Thanks for the advice. I will do that.
> On 2005 we have a scheduler (Control M) running business processing
> jobs outside of SQL Server and I will have to schedule jobs that are
> dependent on these jobs finishing.
> How would you approach a db maintenance job that was job dependent?
>
> Tibor Karaszi wrote:
>|||Tibor:
I have to empty certain tables every evening. and that emptying task is
dependent on another job finishing.
I'm thinking an sql statement...but with something like this is
sqlmaint.exe used or should I be looking at osql utility or something
else.
Thanks in advance
Gerry
Tibor Karaszi wrote:[vbcol=seagreen]
>
> Not sure I understand your question, as Control M apparently already has t
hat feature. Can you
> elaborate?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "DataPro" <datapro01@.yahoo.com> wrote in message
> news:1164735316.116658.270460@.j44g2000cwa.googlegroups.com...|||Yes, you can use OSQL.EXE with either an input file or specify the TUNCATE T
ABLE or DELETE command
using the /Q switch for OSQL.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"DataPro" <datapro01@.yahoo.com> wrote in message
news:1164801015.266092.235400@.l12g2000cwl.googlegroups.com...
> Tibor:
>
> I have to empty certain tables every evening. and that emptying task is
> dependent on another job finishing.
> I'm thinking an sql statement...but with something like this is
> sqlmaint.exe used or should I be looking at osql utility or something
> else.
> Thanks in advance
> Gerry
>
> Tibor Karaszi wrote:
>|||Tibor Karaszi wrote:
> Yes, you can use OSQL.EXE with either an input file or specify the TUNCATE
TABLE or DELETE command
> using the /Q switch for OSQL.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
Thanks much
Question on db maintenance scripts
Relatively new to SQL Server, running both 2000 and 2005.
I have to set up database maintenance scripts, db and log backups,
optimizations etc in batch files so that they can be run by an external
scheduler.
When I produce a script from the SQL Server database maintenance
wizards and save it off in a bat file, the script will not work when
run manually, probably due to the difference in syntax.
I have some examples of backup scripts using sqlmaint.exe, but can't
find anything on how to script optimization/reorg jobs so that they can
be run by an external scheduler.
Can anybody point me to any docs or guidance on this?
Thanks in advance.
GerryI suggest you skip the scripting part and read up on sqlmaint.exe. So instead of calling
xp_sqlmaint, which in turn call sqlmaint.exe, you call sqlmaint.exe directly. this gives you much
better control and options.
2005 is another story, where a maint plan is an SSIS job, but you can just let the Wizard and
designer create package for you and schedule a job using DTEXEC.EXE to execute that package.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"DataPro" <datapro01@.yahoo.com> wrote in message
news:1164723926.064627.125300@.l12g2000cwl.googlegroups.com...
> Relatively new to SQL Server, running both 2000 and 2005.
> I have to set up database maintenance scripts, db and log backups,
> optimizations etc in batch files so that they can be run by an external
> scheduler.
> When I produce a script from the SQL Server database maintenance
> wizards and save it off in a bat file, the script will not work when
> run manually, probably due to the difference in syntax.
> I have some examples of backup scripts using sqlmaint.exe, but can't
> find anything on how to script optimization/reorg jobs so that they can
> be run by an external scheduler.
> Can anybody point me to any docs or guidance on this?
> Thanks in advance.
> Gerry
>|||Tibor:
Thanks for the advice. I will do that.
On 2005 we have a scheduler (Control M) running business processing
jobs outside of SQL Server and I will have to schedule jobs that are
dependent on these jobs finishing.
How would you approach a db maintenance job that was job dependent?
Tibor Karaszi wrote:
> I suggest you skip the scripting part and read up on sqlmaint.exe. So instead of calling
> xp_sqlmaint, which in turn call sqlmaint.exe, you call sqlmaint.exe directly. this gives you much
> better control and options.
> 2005 is another story, where a maint plan is an SSIS job, but you can just let the Wizard and
> designer create package for you and schedule a job using DTEXEC.EXE to execute that package.
>|||> How would you approach a db maintenance job that was job dependent?
Not sure I understand your question, as Control M apparently already has that feature. Can you
elaborate?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"DataPro" <datapro01@.yahoo.com> wrote in message
news:1164735316.116658.270460@.j44g2000cwa.googlegroups.com...
> Tibor:
> Thanks for the advice. I will do that.
> On 2005 we have a scheduler (Control M) running business processing
> jobs outside of SQL Server and I will have to schedule jobs that are
> dependent on these jobs finishing.
> How would you approach a db maintenance job that was job dependent?
>
> Tibor Karaszi wrote:
>> I suggest you skip the scripting part and read up on sqlmaint.exe. So instead of calling
>> xp_sqlmaint, which in turn call sqlmaint.exe, you call sqlmaint.exe directly. this gives you much
>> better control and options.
>> 2005 is another story, where a maint plan is an SSIS job, but you can just let the Wizard and
>> designer create package for you and schedule a job using DTEXEC.EXE to execute that package.
>>
>|||Tibor:
I have to empty certain tables every evening. and that emptying task is
dependent on another job finishing.
I'm thinking an sql statement...but with something like this is
sqlmaint.exe used or should I be looking at osql utility or something
else.
Thanks in advance
Gerry
Tibor Karaszi wrote:
> > How would you approach a db maintenance job that was job dependent?
>
> Not sure I understand your question, as Control M apparently already has that feature. Can you
> elaborate?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "DataPro" <datapro01@.yahoo.com> wrote in message
> news:1164735316.116658.270460@.j44g2000cwa.googlegroups.com...
> > Tibor:
> >
> > Thanks for the advice. I will do that.
> >
> > On 2005 we have a scheduler (Control M) running business processing
> > jobs outside of SQL Server and I will have to schedule jobs that are
> > dependent on these jobs finishing.
> >
> > How would you approach a db maintenance job that was job dependent?
> >
> >
> > Tibor Karaszi wrote:
> >> I suggest you skip the scripting part and read up on sqlmaint.exe. So instead of calling
> >> xp_sqlmaint, which in turn call sqlmaint.exe, you call sqlmaint.exe directly. this gives you much
> >> better control and options.
> >>
> >> 2005 is another story, where a maint plan is an SSIS job, but you can just let the Wizard and
> >> designer create package for you and schedule a job using DTEXEC.EXE to execute that package.
> >>
> >>
> >|||Yes, you can use OSQL.EXE with either an input file or specify the TUNCATE TABLE or DELETE command
using the /Q switch for OSQL.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"DataPro" <datapro01@.yahoo.com> wrote in message
news:1164801015.266092.235400@.l12g2000cwl.googlegroups.com...
> Tibor:
>
> I have to empty certain tables every evening. and that emptying task is
> dependent on another job finishing.
> I'm thinking an sql statement...but with something like this is
> sqlmaint.exe used or should I be looking at osql utility or something
> else.
> Thanks in advance
> Gerry
>
> Tibor Karaszi wrote:
>> > How would you approach a db maintenance job that was job dependent?
>>
>> Not sure I understand your question, as Control M apparently already has that feature. Can you
>> elaborate?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "DataPro" <datapro01@.yahoo.com> wrote in message
>> news:1164735316.116658.270460@.j44g2000cwa.googlegroups.com...
>> > Tibor:
>> >
>> > Thanks for the advice. I will do that.
>> >
>> > On 2005 we have a scheduler (Control M) running business processing
>> > jobs outside of SQL Server and I will have to schedule jobs that are
>> > dependent on these jobs finishing.
>> >
>> > How would you approach a db maintenance job that was job dependent?
>> >
>> >
>> > Tibor Karaszi wrote:
>> >> I suggest you skip the scripting part and read up on sqlmaint.exe. So instead of calling
>> >> xp_sqlmaint, which in turn call sqlmaint.exe, you call sqlmaint.exe directly. this gives you
>> >> much
>> >> better control and options.
>> >>
>> >> 2005 is another story, where a maint plan is an SSIS job, but you can just let the Wizard and
>> >> designer create package for you and schedule a job using DTEXEC.EXE to execute that package.
>> >>
>> >>
>> >
>|||Tibor Karaszi wrote:
> Yes, you can use OSQL.EXE with either an input file or specify the TUNCATE TABLE or DELETE command
> using the /Q switch for OSQL.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
Thanks much
I have to set up database maintenance scripts, db and log backups,
optimizations etc in batch files so that they can be run by an external
scheduler.
When I produce a script from the SQL Server database maintenance
wizards and save it off in a bat file, the script will not work when
run manually, probably due to the difference in syntax.
I have some examples of backup scripts using sqlmaint.exe, but can't
find anything on how to script optimization/reorg jobs so that they can
be run by an external scheduler.
Can anybody point me to any docs or guidance on this?
Thanks in advance.
GerryI suggest you skip the scripting part and read up on sqlmaint.exe. So instead of calling
xp_sqlmaint, which in turn call sqlmaint.exe, you call sqlmaint.exe directly. this gives you much
better control and options.
2005 is another story, where a maint plan is an SSIS job, but you can just let the Wizard and
designer create package for you and schedule a job using DTEXEC.EXE to execute that package.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"DataPro" <datapro01@.yahoo.com> wrote in message
news:1164723926.064627.125300@.l12g2000cwl.googlegroups.com...
> Relatively new to SQL Server, running both 2000 and 2005.
> I have to set up database maintenance scripts, db and log backups,
> optimizations etc in batch files so that they can be run by an external
> scheduler.
> When I produce a script from the SQL Server database maintenance
> wizards and save it off in a bat file, the script will not work when
> run manually, probably due to the difference in syntax.
> I have some examples of backup scripts using sqlmaint.exe, but can't
> find anything on how to script optimization/reorg jobs so that they can
> be run by an external scheduler.
> Can anybody point me to any docs or guidance on this?
> Thanks in advance.
> Gerry
>|||Tibor:
Thanks for the advice. I will do that.
On 2005 we have a scheduler (Control M) running business processing
jobs outside of SQL Server and I will have to schedule jobs that are
dependent on these jobs finishing.
How would you approach a db maintenance job that was job dependent?
Tibor Karaszi wrote:
> I suggest you skip the scripting part and read up on sqlmaint.exe. So instead of calling
> xp_sqlmaint, which in turn call sqlmaint.exe, you call sqlmaint.exe directly. this gives you much
> better control and options.
> 2005 is another story, where a maint plan is an SSIS job, but you can just let the Wizard and
> designer create package for you and schedule a job using DTEXEC.EXE to execute that package.
>|||> How would you approach a db maintenance job that was job dependent?
Not sure I understand your question, as Control M apparently already has that feature. Can you
elaborate?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"DataPro" <datapro01@.yahoo.com> wrote in message
news:1164735316.116658.270460@.j44g2000cwa.googlegroups.com...
> Tibor:
> Thanks for the advice. I will do that.
> On 2005 we have a scheduler (Control M) running business processing
> jobs outside of SQL Server and I will have to schedule jobs that are
> dependent on these jobs finishing.
> How would you approach a db maintenance job that was job dependent?
>
> Tibor Karaszi wrote:
>> I suggest you skip the scripting part and read up on sqlmaint.exe. So instead of calling
>> xp_sqlmaint, which in turn call sqlmaint.exe, you call sqlmaint.exe directly. this gives you much
>> better control and options.
>> 2005 is another story, where a maint plan is an SSIS job, but you can just let the Wizard and
>> designer create package for you and schedule a job using DTEXEC.EXE to execute that package.
>>
>|||Tibor:
I have to empty certain tables every evening. and that emptying task is
dependent on another job finishing.
I'm thinking an sql statement...but with something like this is
sqlmaint.exe used or should I be looking at osql utility or something
else.
Thanks in advance
Gerry
Tibor Karaszi wrote:
> > How would you approach a db maintenance job that was job dependent?
>
> Not sure I understand your question, as Control M apparently already has that feature. Can you
> elaborate?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "DataPro" <datapro01@.yahoo.com> wrote in message
> news:1164735316.116658.270460@.j44g2000cwa.googlegroups.com...
> > Tibor:
> >
> > Thanks for the advice. I will do that.
> >
> > On 2005 we have a scheduler (Control M) running business processing
> > jobs outside of SQL Server and I will have to schedule jobs that are
> > dependent on these jobs finishing.
> >
> > How would you approach a db maintenance job that was job dependent?
> >
> >
> > Tibor Karaszi wrote:
> >> I suggest you skip the scripting part and read up on sqlmaint.exe. So instead of calling
> >> xp_sqlmaint, which in turn call sqlmaint.exe, you call sqlmaint.exe directly. this gives you much
> >> better control and options.
> >>
> >> 2005 is another story, where a maint plan is an SSIS job, but you can just let the Wizard and
> >> designer create package for you and schedule a job using DTEXEC.EXE to execute that package.
> >>
> >>
> >|||Yes, you can use OSQL.EXE with either an input file or specify the TUNCATE TABLE or DELETE command
using the /Q switch for OSQL.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"DataPro" <datapro01@.yahoo.com> wrote in message
news:1164801015.266092.235400@.l12g2000cwl.googlegroups.com...
> Tibor:
>
> I have to empty certain tables every evening. and that emptying task is
> dependent on another job finishing.
> I'm thinking an sql statement...but with something like this is
> sqlmaint.exe used or should I be looking at osql utility or something
> else.
> Thanks in advance
> Gerry
>
> Tibor Karaszi wrote:
>> > How would you approach a db maintenance job that was job dependent?
>>
>> Not sure I understand your question, as Control M apparently already has that feature. Can you
>> elaborate?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "DataPro" <datapro01@.yahoo.com> wrote in message
>> news:1164735316.116658.270460@.j44g2000cwa.googlegroups.com...
>> > Tibor:
>> >
>> > Thanks for the advice. I will do that.
>> >
>> > On 2005 we have a scheduler (Control M) running business processing
>> > jobs outside of SQL Server and I will have to schedule jobs that are
>> > dependent on these jobs finishing.
>> >
>> > How would you approach a db maintenance job that was job dependent?
>> >
>> >
>> > Tibor Karaszi wrote:
>> >> I suggest you skip the scripting part and read up on sqlmaint.exe. So instead of calling
>> >> xp_sqlmaint, which in turn call sqlmaint.exe, you call sqlmaint.exe directly. this gives you
>> >> much
>> >> better control and options.
>> >>
>> >> 2005 is another story, where a maint plan is an SSIS job, but you can just let the Wizard and
>> >> designer create package for you and schedule a job using DTEXEC.EXE to execute that package.
>> >>
>> >>
>> >
>|||Tibor Karaszi wrote:
> Yes, you can use OSQL.EXE with either an input file or specify the TUNCATE TABLE or DELETE command
> using the /Q switch for OSQL.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
Thanks much
Subscribe to:
Posts (Atom)