Help with Connections

Hi everybody,

I need a little help...

I created several packages, that after their creation should not be changed again; that's why I created a Package Configuration File in which I set all the Connections I need, so that this is the only file I have to modify.

I set there also the path of the connections for my log file and for my error files (which are both text files), but if I change their paths in the configuration file this change doesn't affect the package, and the path of the log remains the same.

That's an image of one of those packages....

The fact is that I cannot change the ConnectionString of the connection called "log_pkg", but it always remains the same...

How can I resolve this problem?

Thanks everyone

In general this should work. I just did a quick test where I had a package already which contained a Text Log Provider so I added an XML type configuration to the package, on just the connetion string. If I edit the config file and change the name of the file, rerun the package the, the new name is created.

this was section of the config file I was editing, specifically the name in the string "ftp_Log2.txt"

- <Configuration ConfiguredType="Property" Path="\Package.Connections[ftp_Log.txt].Properties[ConnectionString]" ValueType="String">

<ConfiguredValue>C:\_work\SSISPackages\ProtectionLevelTest\ftp_Log2.txt</ConfiguredValue>

</Configuration>

You are doing something similar and it does not work?

When you execute in BI studio, do you see a message similar to the followingin the output window indicating the config file was read?

Information: 0x40016041 at ftptesting: The package is attempting to configure from the XML file "C:\SSISPackages\ftpconfig.dtsConfig".

Help with BCP!

Hi,
I am creating a text file using bcp in SQL2000. When I open the text
file which was created the =A3 sign has been replaced with something
else. WHen I look at the results in a query, the pound sign is there,
it's only when bcp'd to a text file.
It is running on Windows 2003 Server.
ANy help would be appreciated.This is a char, varchar, or text code page issue. The ascii value for pPound
sterling is 163m, which is greater than 127. If you don't want any
conversation, try to use -C RAW on the bcp command line.
Linchi
"nomad" wrote:

> Hi,
> I am creating a text file using bcp in SQL2000. When I open the text
> file which was created the ￡ sign has been replaced with something
> else. WHen I look at the results in a query, the pound sign is there,
> it's only when bcp'd to a text file.
> It is running on Windows 2003 Server.
> ANy help would be appreciated.
>|||On 22 Jun, 16:47, Linchi Shea <LinchiS...@.discussions.microsoft.com>
wrote:
> This is a char, varchar, or text code page issue. The ascii value for pPo=
und
> sterling is 163m, which is greater than 127. If you don't want any
> conversation, try to use -C RAW on the bcp command line.
> Hi,
Thanks for your response. I have tried setting the '=A3' to CHAR(163)
within the view, and the BCP is set to -c but still no joy. Could it
be something to do with the character set of the Server? as I am in
the UK, but it seems not to recognise '=A3', as if it is in America
ascii set.[vbcol=seagreen]
> Linchi
> "nomad" wrote:
>
>
>|||On 26 Jun, 13:43, nomad <d.bedg...@.ntlworld.com> wrote:[vbcol=seagreen]
> On 22 Jun, 16:47, Linchi Shea <LinchiS...@.discussions.microsoft.com>
> wrote:
>
Pound[vbcol=seagreen]
> Thanks for your response. I have tried setting the '=A3' to CHAR(163)
> within the view, and the BCP is set to -c but still no joy. Could it
> be something to do with the character set of the Server? as I am in
> the UK, but it seems not to recognise '=A3', as if it is in America
> ascii set.
>
>
>
>
>
Linchi, scrap that last reply, I wasn't adding -C RAW coorectly. It
worked a dream. Thank you very much for your answer. Much
appreciated.

Help with BCP!

Hi,
I am creating a text file using bcp in SQL2000. When I open the text
file which was created the =A3 sign has been replaced with something
else. WHen I look at the results in a query, the pound sign is there,
it's only when bcp'd to a text file.
It is running on Windows 2003 Server.
ANy help would be appreciated.This is a char, varchar, or text code page issue. The ascii value for pPound
sterling is 163m, which is greater than 127. If you don't want any
conversation, try to use -C RAW on the bcp command line.
Linchi
"nomad" wrote:
> Hi,
> I am creating a text file using bcp in SQL2000. When I open the text
> file which was created the £ sign has been replaced with something
> else. WHen I look at the results in a query, the pound sign is there,
> it's only when bcp'd to a text file.
> It is running on Windows 2003 Server.
> ANy help would be appreciated.
>|||On 22 Jun, 16:47, Linchi Shea <LinchiS...@.discussions.microsoft.com>
wrote:
> This is a char, varchar, or text code page issue. The ascii value for pPo=und
> sterling is 163m, which is greater than 127. If you don't want any
> conversation, try to use -C RAW on the bcp command line.
> Hi,
Thanks for your response. I have tried setting the '=A3' to CHAR(163)
within the view, and the BCP is set to -c but still no joy. Could it
be something to do with the character set of the Server? as I am in
the UK, but it seems not to recognise '=A3', as if it is in America
ascii set.
> Linchi
> "nomad" wrote:
> > Hi,
> > I am creating a text file using bcp in SQL2000. When I open the text
> > file which was created the =A3 sign has been replaced with something
> > else. WHen I look at the results in a query, the pound sign is there,
> > it's only when bcp'd to a text file.
> > It is running on Windows 2003 Server.
> > ANy help would be appreciated.|||On 26 Jun, 13:43, nomad <d.bedg...@.ntlworld.com> wrote:
> On 22 Jun, 16:47, Linchi Shea <LinchiS...@.discussions.microsoft.com>
> wrote:
> > This is a char, varchar, or text code page issue. The ascii value for p=Pound
> > sterling is 163m, which is greater than 127. If you don't want any
> > conversation, try to use -C RAW on the bcp command line.
> > Hi,
> Thanks for your response. I have tried setting the '=A3' to CHAR(163)
> within the view, and the BCP is set to -c but still no joy. Could it
> be something to do with the character set of the Server? as I am in
> the UK, but it seems not to recognise '=A3', as if it is in America
> ascii set.
> > Linchi
> > "nomad" wrote:
> > > Hi,
> > > I am creating a text file using bcp in SQL2000. When I open the text
> > > file which was created the =A3 sign has been replaced with something
> > > else. WHen I look at the results in a query, the pound sign is there,
> > > it's only when bcp'd to a text file.
> > > It is running on Windows 2003 Server.
> > > ANy help would be appreciated.
Linchi, scrap that last reply, I wasn't adding -C RAW coorectly. It
worked a dream. Thank you very much for your answer. Much
appreciated.

Help with a SQL Union query please. Conversion errors!

Hi, I've created a SQL union query that grabs the top three values of a test and puts the results together on one line item by a recordnumber.

The only problem is, one value is generating the error: " Syntax error converting the varchar value '=' to a column of data type int."

This field is an operator field, so it will only display operator values ( <, >, = ). Everything seems to work BUT the union join on that column, which is a varchar data type. I need this union to work, and Im frustrated that I can't seem to figure out why I can't get the logic to work. Can someone take a look at this and help me with it?

The union query looks like this:

exec('
select

RecordNumber,
Sum(rank1) as HbA1CRank1,
max(Operator1) as HbA1COperator1,
sum(contentValue1) as HbA1CContentvalue1,
max(dos1) as HbA1CDOS1,
Sum(rank2) as HbA1CRank2,
max(Operator2) as HbA1COperator2,
sum(contentValue2) as HbA1CContentvalue2,
max(dos2) as HbA1CDOS2,
Sum(rank3) as HbA1CRank3,
max(Operator3) as HbA1COperator3,
sum(contentValue3) as HbA1CContentvalue3,
max(dos3) as HbA1CDOS3

from

(SELECT DISTINCT
TOP 100 PERCENT recordnumber, Rank AS rank1, cast(Operator as varchar) as Operator1, contentValue AS contentvalue1, DOS AS DOS1, 0 AS rank2, 0 as Operator2, 0 AS contentvalue2, 0 AS DOS2,
0 AS rank3, 0 as Operator3, 0 AS contentvalue3, 0 AS DOS3
FROM (SELECT (SELECT COUNT(*)
FROM tblDiabetic_HgbA1C_Total vw1
WHERE vw1.rECORDnUMBER = vw2.rECORDnUMBER AND vw1.ItemCode = vw2.ItemCode AND vw1.Operator = vw2.Operator AND vw1.DOS > vw2.DOS) AS Rank, *
FROM tblDiabetic_HgbA1C_Total vw2) vw3
WHERE (Rank = 0)
ORDER BY RecordNumber

UNION

SELECT DISTINCT
TOP 100 PERCENT recordnumber, 0 AS rank1, 0 as Operator1, 0 AS contentvalue1, 0 AS DOS1, Rank AS rank2, cast(Operator as varchar) as Operator2, contentValue AS contentvalue2, DOS AS DOS2,
0 AS rank3, 0 as operator3, 0 AS contentvalue3, 0 AS DOS3
FROM (SELECT (SELECT COUNT(*)
FROM tblDiabetic_HgbA1C_Total vw1
WHERE vw1.rECORDnUMBER = vw2.rECORDnUMBER AND vw1.ItemCode = vw2.ItemCode AND vw1.Operator = vw2.Operator AND vw1.DOS > vw2.DOS) AS Rank, *
FROM tblDiabetic_HgbA1C_Total vw2) vw3
WHERE (Rank = 1)
ORDER BY RecordNumber

UNION

SELECT DISTINCT
TOP 100 PERCENT recordnumber, 0 AS rank1, 0 as Operator1, 0 AS contentvalue1, 0 AS DOS1, 0 AS rank2, 0 as operator2, 0 AS contentvalue2, 0 AS DOS2, rank AS rank3, cast(Operator as varchar) as operator3,
contentvalue AS contentvalue3, DOS AS DOS3
FROM (SELECT (SELECT COUNT(*)
FROM tblDiabetic_HgbA1C_Total vw1
WHERE vw1.rECORDnUMBER = vw2.rECORDnUMBER AND vw1.ItemCode = vw2.ItemCode AND vw1.Operator = vw2.Operator AND vw1.DOS > vw2.DOS) AS Rank, *
FROM tblDiabetic_HgbA1C_Total vw2) vw3
WHERE (Rank = 2)
ORDER BY RecordNumber )tblHgA1C

group by RecordNumber

')
GO

Can anyone help? It looks right to me, I just can't figure out why the error keeps coming up =\

Thank you!

The error most likely comes from having "cast(Operator as varchar) as Operator1" in the first query and then "0 as Operator1" in the subsequent ones. It should be "'' as Operator1"

However,write your query like this instead, and you won't have to use a UNION at all

select

RecordNumber,
Sum(CASE WHEN Rank=0 THEN Rank ELSE NULL END) as HbA1CRank1, -- WILL BE 0
max(CASE WHEN Rank=0 THEN Operator ELSE NULL END) as HbA1COperator1,
sum(CASE WHEN Rank=0 THEN contentValue ELSE NULL END) as HbA1CContentvalue1,
max(CASE WHEN Rank=0 THEN DOS ELSE NULL END) as HbA1CDOS1,
Sum(CASE WHEN Rank=1 THEN Rank ELSE NULL END) as HbA1CRank2,
max(CASE WHEN Rank=1 THEN Operator ELSE NULL END) as HbA1COperator2,
sum(CASE WHEN Rank=1 THEN contentValue ELSE NULL END) as HbA1CContentvalue2,
max(CASE WHEN Rank=1 THEN DOS ELSE NULL END) as HbA1CDOS2,
Sum(CASE WHEN Rank=2 THEN Rank ELSE NULL END) as HbA1CRank3,
max(CASE WHEN Rank=2 THEN Operator ELSE NULL END) as HbA1COperator3,
sum(CASE WHEN Rank=2 THEN contentValue ELSE NULL END) as HbA1CContentvalue3,
max(CASE WHEN Rank=2 THEN DOS ELSE NULL END) as HbA1CDOS3

from ...|||

Gunteman! Oh man, thank you so much! I can't believe how easy that was, your solution worked perfectly. That's awesome, you made my night!

Thanks again =)

|||

Great! BTW, why did you use EXEC?

|||

about a year ago someone here recommended wrapping my union statements in the EXEC command when running them within stored procedures. I didnt' bother researching further... it seemed to work, so I just ran with it and kept doing it :)

Should I not bother with it?

sql

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 with a Select/Union statement

I have 3 tables One table is the order Table, Bill to table and ship to table
I have to Views created as followed

This query uses the Ship to table to pull the ship to information to the shipping system.
SELECT Cust_address.NAME, Cust_address.ADDR_1, Cust_address.ADDR_2, Cust_address.ADDR_3, Cust_address.CITY, Cust_address.STATE, Cust_address.ZIPCODE, Cust_address.COUNTRY, Cust_address.SHIP_VIA, customer_order.ID
FROM Cust_address INNER JOIN customer_order ON (Cust_address.CUSTOMER_ID = customer_order.CUSTOMER_ID) AND (Cust_address.ADDR_NO = customer_order.SHIP_TO_ADDR_NO);

This query uss the Bill to as the ship to inforamtion
SELECT CUSTOMER.ID, CUSTOMER.SHIPTO_ID, CUSTOMER.NAME, CUSTOMER.ADDR_1, CUSTOMER.ADDR_2, CUSTOMER.ADDR_3, CUSTOMER.CITY, CUSTOMER.STATE, CUSTOMER.ZIPCODE, CUSTOMER.COUNTRY, CUSTOMER.SHIP_VIA, customer_order.ID, customer_order.SHIP_TO_ADDR_NO
FROM CUSTOMER INNER JOIN customer_order ON CUSTOMER.ID = customer_order.CUSTOMER_ID;

I need this infroamtion in one table which I have done in the UNION statement as followed:
SELECT Cust_address.NAME, Cust_address.ADDR_1, Cust_address.ADDR_2, Cust_address.ADDR_3, Cust_address.CITY, Cust_address.STATE, Cust_address.ZIPCODE, Cust_address.COUNTRY, Cust_address.SHIP_VIA, customer_order.ID
FROM Cust_address INNER JOIN customer_order ON (Cust_address.CUSTOMER_ID = customer_order.CUSTOMER_ID) AND (Cust_address.ADDR_NO = customer_order.SHIP_TO_ADDR_NO)
UNION ALL
SELECT CUSTOMER.NAME, CUSTOMER.ADDR_1, CUSTOMER.ADDR_2, CUSTOMER.ADDR_3, CUSTOMER.CITY, CUSTOMER.STATE, CUSTOMER.ZIPCODE, CUSTOMER.COUNTRY, CUSTOMER.SHIP_VIA, customer_order.ID
FROM CUSTOMER INNER JOIN customer_order ON CUSTOMER.ID=customer_order.CUSTOMER_ID;

Here is the problem when I pull information out of the ship to table I get 2 results as followed My key field to pull this information is the Last field Custoemr ID this custoemr ID exist in both tables but contains different information I want to ONLY pull in the info that I need in this case it would be the first line that is the Correct shipping information.

NAME ADDR_1 ADDR_2 ADDR_3 CITY STATE ZIPCODE COUNTRY SHIP_VIA ID
DIEBOLD INC (4076A) ATTN: RANCE AARON 343 MANOR DR PACIFICA CA 94044 18932
DIEBOLD, INC OH UPS #88X08X 18932

MY POINT: Is there a way to select a over all DISTINCT order ID.

Thank you for any help hope this make sense!...I want to ONLY pull in the info that I need in this case it would be the first line that is the Correct shipping information.Define what you mean by "first line". The order of data in a database has no meaning. Do you have a date field or some other value you can sort on to determine which shipping information is "first"?|||Sorry
In the Union query the first select statement will Always be the correct shipping information when the ORDER form has one(Shipping info) if it does not have a Shipping address printed on the form then the second select statement will pull from the bill table which will only have one record per the order number.

Example Order form:
Bill-to Ship to information
Bill to Compnay Ship to company
Bill to address Ship to address
Bill to city Ship to City
Bill to State Ship to state
Etc Etc

When this order occurs then there is one record in the bill to table with that info and 1 record in the Ship to table with the shipping info This is where the problme occurs when I do the UNION it looks in the Shipping table first and pulls the shipping info and then the other statement in the UNION pulls the BILL to info which give me 2 records in the Query so what happends in this case I pull the info using ODBC to my shipping system and it says it found 2 records but I only what the First select statement to show. Another problem is when the ship to is not fill out I need to use the second part of the UNION select statement in order ot pull the bill to info for the shipping info but this works correctly due to only 1 order number exists in the bill to tableEX:

Example Order form:
Bill-to Ship to information
Bill to Compnay SAME
Bill to address
Bill to city
Bill to State
Etc
When this occurs then I need to use the Bill to table for the shipping info.

Hopfully this clears things up.|||So this query:SELECT Cust_address.NAME,
Cust_address.ADDR_1,
Cust_address.ADDR_2,
Cust_address.ADDR_3,
Cust_address.CITY,
Cust_address.STATE,
Cust_address.ZIPCODE,
Cust_address.COUNTRY,
Cust_address.SHIP_VIA,
customer_order.ID
FROM Cust_address
INNER JOIN customer_order
ON Cust_address.CUSTOMER_ID = customer_order.CUSTOMER_ID
AND Cust_address.ADDR_NO = customer_order.SHIP_TO_ADDR_NO...contains all the orders that you need, but in some cases the address information is missing and you want to pull address information from the CUSTOMER table to fill it in?|||Yes Sir that is correct!

help with 2005 profiler

i'm trying to use the sql2005 profiler to run traces against both
sql2000 and sql2005 databases. i created a trace template when
connected to a sql2000 db. i want to use the exact same template for
traces against sql2005. how do i get that template so that i can use it
when connected to a sql2005 instance? i've tried exporting, importing,
saving as a trace file, etc. i can't get that trace template to load
when connected to sql2005.
ch wrote:
> i'm trying to use the sql2005 profiler to run traces against both
> sql2000 and sql2005 databases. i created a trace template when
> connected to a sql2000 db. i want to use the exact same template for
> traces against sql2005. how do i get that template so that i can use
> it when connected to a sql2005 instance? i've tried exporting,
> importing, saving as a trace file, etc. i can't get that trace
> template to load when connected to sql2005.
The trace templates are saved according to the SQL Server version. I'm
not sure exactly where they are saved. I did a quick check and could not
find them on the local PC or in the registration database - unless they
were exported.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||Local machine, it seems:
C:\Program Files\Microsoft SQL Server\90\Tools\Profiler\Templates\Microsoft SQL Server\80
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:eNbokIw7FHA.3984@.TK2MSFTNGP11.phx.gbl...
> ch wrote:
> The trace templates are saved according to the SQL Server version. I'm not sure exactly where they
> are saved. I did a quick check and could not find them on the local PC or in the registration
> database - unless they were exported.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
|||Tibor Karaszi wrote:
> Local machine, it seems:
> C:\Program Files\Microsoft SQL
> Server\90\Tools\Profiler\Templates\Microsoft SQL Server\80
Tibor,
I checked in that folder and did not see the (user) templates. I created
two custom templates for SQL 2000 and SQL 2005 with the same name and
did not see either in the templates folder (I did not physically export
them to disk). I think that the "Templates" folder stores the default
templates that are delivered with SQL Server (for SQL 2000 and SQL
2005). But it's not clear to me where the user templates are stored. I'm
guessing they are in encrypted format either in the registration
database or in another file. Searching all files that contained the
string representing the template name revealed nothing on my server.
Still searching...
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||Maybe in your "My Documents" under the "SQL Server Management Studio"
folder?
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:eHShgB47FHA.3224@.TK2MSFTNGP09.phx.gbl...
> Tibor Karaszi wrote:
> Tibor,
> I checked in that folder and did not see the (user) templates. I created
> two custom templates for SQL 2000 and SQL 2005 with the same name and did
> not see either in the templates folder (I did not physically export them
> to disk). I think that the "Templates" folder stores the default templates
> that are delivered with SQL Server (for SQL 2000 and SQL 2005). But it's
> not clear to me where the user templates are stored. I'm guessing they are
> in encrypted format either in the registration database or in another
> file. Searching all files that contained the string representing the
> template name revealed nothing on my server. Still searching...
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
|||Sorry, David. I just noticed some files in that directory and incorrectly assumed that user defined
templates would also be stored there.
However, after some searching, I did find the location for user defined trace templates:
C:\Documents and Settings\Tibor\Application Data\Microsoft\SQL Profiler\9.0\Templates\Microsoft SQL
Server\90
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:eHShgB47FHA.3224@.TK2MSFTNGP09.phx.gbl...
> Tibor Karaszi wrote:
> Tibor,
> I checked in that folder and did not see the (user) templates. I created two custom templates for
> SQL 2000 and SQL 2005 with the same name and did not see either in the templates folder (I did not
> physically export them to disk). I think that the "Templates" folder stores the default templates
> that are delivered with SQL Server (for SQL 2000 and SQL 2005). But it's not clear to me where the
> user templates are stored. I'm guessing they are in encrypted format either in the registration
> database or in another file. Searching all files that contained the string representing the
> template name revealed nothing on my server. Still searching...
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com

help with 2005 profiler

i'm trying to use the sql2005 profiler to run traces against both
sql2000 and sql2005 databases. i created a trace template when
connected to a sql2000 db. i want to use the exact same template for
traces against sql2005. how do i get that template so that i can use it
when connected to a sql2005 instance? i've tried exporting, importing,
saving as a trace file, etc. i can't get that trace template to load
when connected to sql2005.ch wrote:
> i'm trying to use the sql2005 profiler to run traces against both
> sql2000 and sql2005 databases. i created a trace template when
> connected to a sql2000 db. i want to use the exact same template for
> traces against sql2005. how do i get that template so that i can use
> it when connected to a sql2005 instance? i've tried exporting,
> importing, saving as a trace file, etc. i can't get that trace
> template to load when connected to sql2005.
The trace templates are saved according to the SQL Server version. I'm
not sure exactly where they are saved. I did a quick check and could not
find them on the local PC or in the registration database - unless they
were exported.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Local machine, it seems:
C:\Program Files\Microsoft SQL Server\90\Tools\Profiler\Templates\Micro
soft
SQL Server\80
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:eNbokIw7FHA.3984@.TK2MSFTNGP11.phx.gbl...
> ch wrote:
> The trace templates are saved according to the SQL Server version. I'm not
sure exactly where they
> are saved. I did a quick check and could not find them on the local PC or
in the registration
> database - unless they were exported.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com|||Tibor Karaszi wrote:
> Local machine, it seems:
> C:\Program Files\Microsoft SQL
> Server\90\Tools\Profiler\Templates\Micro
soft SQL Server\80
Tibor,
I checked in that folder and did not see the (user) templates. I created
two custom templates for SQL 2000 and SQL 2005 with the same name and
did not see either in the templates folder (I did not physically export
them to disk). I think that the "Templates" folder stores the default
templates that are delivered with SQL Server (for SQL 2000 and SQL
2005). But it's not clear to me where the user templates are stored. I'm
guessing they are in encrypted format either in the registration
database or in another file. Searching all files that contained the
string representing the template name revealed nothing on my server.
Still searching...
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Maybe in your "My Documents" under the "SQL Server Management Studio"
folder?
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:eHShgB47FHA.3224@.TK2MSFTNGP09.phx.gbl...
> Tibor Karaszi wrote:
> Tibor,
> I checked in that folder and did not see the (user) templates. I created
> two custom templates for SQL 2000 and SQL 2005 with the same name and did
> not see either in the templates folder (I did not physically export them
> to disk). I think that the "Templates" folder stores the default templates
> that are delivered with SQL Server (for SQL 2000 and SQL 2005). But it's
> not clear to me where the user templates are stored. I'm guessing they are
> in encrypted format either in the registration database or in another
> file. Searching all files that contained the string representing the
> template name revealed nothing on my server. Still searching...
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com|||Sorry, David. I just noticed some files in that directory and incorrectly as
sumed that user defined
templates would also be stored there.
However, after some searching, I did find the location for user defined trac
e templates:
C:\Documents and Settings\Tibor\Application Data\Microsoft\SQL Profiler\9.0\
Templates\Microsoft SQL
Server\90
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:eHShgB47FHA.3224@.TK2MSFTNGP09.phx.gbl...
> Tibor Karaszi wrote:
> Tibor,
> I checked in that folder and did not see the (user) templates. I created t
wo custom templates for
> SQL 2000 and SQL 2005 with the same name and did not see either in the tem
plates folder (I did not
> physically export them to disk). I think that the "Templates" folder store
s the default templates
> that are delivered with SQL Server (for SQL 2000 and SQL 2005). But it's n
ot clear to me where the
> user templates are stored. I'm guessing they are in encrypted format eithe
r in the registration
> database or in another file. Searching all files that contained the string
representing the
> template name revealed nothing on my server. Still searching...
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com

help with 2005 profiler

i'm trying to use the sql2005 profiler to run traces against both
sql2000 and sql2005 databases. i created a trace template when
connected to a sql2000 db. i want to use the exact same template for
traces against sql2005. how do i get that template so that i can use it
when connected to a sql2005 instance? i've tried exporting, importing,
saving as a trace file, etc. i can't get that trace template to load
when connected to sql2005.ch wrote:
> i'm trying to use the sql2005 profiler to run traces against both
> sql2000 and sql2005 databases. i created a trace template when
> connected to a sql2000 db. i want to use the exact same template for
> traces against sql2005. how do i get that template so that i can use
> it when connected to a sql2005 instance? i've tried exporting,
> importing, saving as a trace file, etc. i can't get that trace
> template to load when connected to sql2005.
The trace templates are saved according to the SQL Server version. I'm
not sure exactly where they are saved. I did a quick check and could not
find them on the local PC or in the registration database - unless they
were exported.
--
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Local machine, it seems:
C:\Program Files\Microsoft SQL Server\90\Tools\Profiler\Templates\Microsoft SQL Server\80
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:eNbokIw7FHA.3984@.TK2MSFTNGP11.phx.gbl...
> ch wrote:
>> i'm trying to use the sql2005 profiler to run traces against both
>> sql2000 and sql2005 databases. i created a trace template when
>> connected to a sql2000 db. i want to use the exact same template for
>> traces against sql2005. how do i get that template so that i can use
>> it when connected to a sql2005 instance? i've tried exporting,
>> importing, saving as a trace file, etc. i can't get that trace
>> template to load when connected to sql2005.
> The trace templates are saved according to the SQL Server version. I'm not sure exactly where they
> are saved. I did a quick check and could not find them on the local PC or in the registration
> database - unless they were exported.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com|||Tibor Karaszi wrote:
> Local machine, it seems:
> C:\Program Files\Microsoft SQL
> Server\90\Tools\Profiler\Templates\Microsoft SQL Server\80
Tibor,
I checked in that folder and did not see the (user) templates. I created
two custom templates for SQL 2000 and SQL 2005 with the same name and
did not see either in the templates folder (I did not physically export
them to disk). I think that the "Templates" folder stores the default
templates that are delivered with SQL Server (for SQL 2000 and SQL
2005). But it's not clear to me where the user templates are stored. I'm
guessing they are in encrypted format either in the registration
database or in another file. Searching all files that contained the
string representing the template name revealed nothing on my server.
Still searching...
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Maybe in your "My Documents" under the "SQL Server Management Studio"
folder?
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:eHShgB47FHA.3224@.TK2MSFTNGP09.phx.gbl...
> Tibor Karaszi wrote:
>> Local machine, it seems:
>> C:\Program Files\Microsoft SQL
>> Server\90\Tools\Profiler\Templates\Microsoft SQL Server\80
> Tibor,
> I checked in that folder and did not see the (user) templates. I created
> two custom templates for SQL 2000 and SQL 2005 with the same name and did
> not see either in the templates folder (I did not physically export them
> to disk). I think that the "Templates" folder stores the default templates
> that are delivered with SQL Server (for SQL 2000 and SQL 2005). But it's
> not clear to me where the user templates are stored. I'm guessing they are
> in encrypted format either in the registration database or in another
> file. Searching all files that contained the string representing the
> template name revealed nothing on my server. Still searching...
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com|||Sorry, David. I just noticed some files in that directory and incorrectly assumed that user defined
templates would also be stored there.
However, after some searching, I did find the location for user defined trace templates:
C:\Documents and Settings\Tibor\Application Data\Microsoft\SQL Profiler\9.0\Templates\Microsoft SQL
Server\90
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:eHShgB47FHA.3224@.TK2MSFTNGP09.phx.gbl...
> Tibor Karaszi wrote:
>> Local machine, it seems:
>> C:\Program Files\Microsoft SQL
>> Server\90\Tools\Profiler\Templates\Microsoft SQL Server\80
> Tibor,
> I checked in that folder and did not see the (user) templates. I created two custom templates for
> SQL 2000 and SQL 2005 with the same name and did not see either in the templates folder (I did not
> physically export them to disk). I think that the "Templates" folder stores the default templates
> that are delivered with SQL Server (for SQL 2000 and SQL 2005). But it's not clear to me where the
> user templates are stored. I'm guessing they are in encrypted format either in the registration
> database or in another file. Searching all files that contained the string representing the
> template name revealed nothing on my server. Still searching...
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com

Help with "flattening" a table

Hi All,

I have a database that stores some data from two questionnaires. Table One has been created in a fairly straightforward manner. It has the following fields:

ID, variable1, variable2, variable3, ..., variablen

Table Two is a bit more complicated. I'm guessing whoever created it wanted to do things a bit more "efficiently" and it has the following fields.

ID, variable, response.

If I wanted to, say, extract variables 2, 3 and 5 from Table One I could write:

SELECT ID, variable2, variable3, variable 5 FROM TableOne

I don't know how to do a similar thing with Table 2 using only one statement. ie, I can only think of doing:

SELECT ID, Response FROM Table2 WHERE variable = 2

SELECT ID, Response FROM Table2 WHERE variable = 3

SELECT ID, Response FROM Table2 WHERE variable = 5

This isn't much good to me as I need the data from Table Two in a single table. Once I've done that, I need to join it to Table One. Ordinarily I'd know how to do that, but I don't know how to do given the format of Table Two.

So... I was wondering if anyone could offer advice on how to "flatten" Table Two?

I would like to be able to do something like:

SELECT t1.ID, t1.variable1, t1.variable2, t2.variable6, t2.variable7

FROM TableOne t1 JOIN TableTwo t2 ON t1.ID = t2.ID

Any help would be much appreciated!

To tell the truth I dont understand the question.

What is the meaning of your tables? What is variable? What is ID? is that a unique key for one of tables? Maybe for both?
As I understood from your question in second table if, say, variable = 3, then the response field womehow relates to the value of variable3 in Table1? Can't you write
select ID, Response from TABLE2 where variable in (2, 3, 5) instead of

"SELECT ID, Response FROM Table2 WHERE variable = 2

SELECT ID, Response FROM Table2 WHERE variable = 3

SELECT ID, Response FROM Table2 WHERE variable =5"?

And, are you considering any table schema reorganization? Because if the way I understood your table schemas is right, then you should consider some other way to store data-storing variable number in one table as a column name and as a value in the other seems rather illogical to me.

Help with "start job" error

good morning everyone,
I have created some scheduled jobs which basically are transfer data from Oracle DB to SQL server through DTS package. I created DTS pacakages and right click to schedule the job for daily run.
I was able to execute DTS package to transfer data w/o problems at all. However it seemed scheduled job could not run automatically nor I kick off the job manually.
I have attached the error message below. It seemed I need some components be installed. Would someone take a look and let me know what is it, name of the components, probably cost information? Thank you very much for the help in advance.When you manually execute a DTS job, it runs under your login and with the resources of your local machine. When the scheduled job is run by the SQLServer Agent, it runs under the permissions of the login property that was specified in the Administrative Services tab with the physical resources (disk drives, folder mappings, etc.) of the SQL Server that the agent resides on.

The attached error looks like the Oracle components are missing from the SQL Server. Once you have those installed, you might want to also verify that the login used by SQL Server Agent has permisisons on the Oracle server.|||check to make sure your Sqlagent startup acct has proper permission. If the job is invoked by non-admin, agent proxy acct will be used. Take a look at the following to set the proxy acct.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_xp_aa-sz_8sdm.asp

Help Trigger

i want to audit transactions done to table TOrig.
I created table TAudit same as TOrig in addition to ActionID (1 for
insert, 2 for update, 3 for delete), System Date and System User.
I created triggers on TOrigto insert into TAudit in case of insert,
update & delete.

TOrig contains text column. So i created instead of Trigger like:

Create TRIGGER TrigDelete
ON dbo.TOrig
Instead of Delete
AS
declare @.id int
select @.id=Liq_ID from Deleted

If Not Exists (Select * from TAudit where Liq_ID=@.id and ActionID=3 )
BEGIN
Insert into TAudit
select * , 3, GetDate(), System_USer
from Deleted

Delete from TOrig where Liq_ID=@.id
END
GO

PROBLEM: is that Torig is a detail table to a master table and has a
cascade delete relationship with the master table.
So Instead Trigger does it work. What do I DO??On 4 Oct 2004 06:21:50 -0700, Marie-Christine wrote:

>PROBLEM: is that Torig is a detail table to a master table and has a
>cascade delete relationship with the master table.
>So Instead Trigger does it work. What do I DO??

Hi Marie-Christine,

1. Create the trigger as an AFTER trigger, not as an INSTEAD OF trigger.

2. Rewrite the trigger, so that it will handle multi-rows inserts, updates
and deletes as well.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||
I would've liked to use For Delete trigger but i can't since there are
text columns in the table. Neither can i use After Trigger. And i can't
retrieve rows from Deleted table when there are text columns. That's why
i used Instead of Delte trigger. But now i'm hung cause Instead of
Delete trigger doesn't work with Cascade. That's why i'm confused now.
There are 2 problems: text columns in the table, and cascade delete. How
do i solve the 2 problems?

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||
ANyone!!! no solution???

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||MC <anonymous@.discussions.microsoft.com> wrote in message news:<4163a5d8$0$26140$c397aba@.news.newsgroups.ws>...
> ANyone!!! no solution???
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

If you can't use a trigger, you could put the DELETE and the audit
logic into a procedure, then make users and applications use the
procedure for all deletions.

Simon

HELP to write stored procedure whose values are calculated automatically in database

Hi frdz,

I m creating my web-application in asp.net with C# 2005 and using sql server 2005.

I have created the stored procedure for the insert,update.

I want to know how to write the mathematical calculations in the stored procedure..

Pls tell me from the below stored procedure were i m making the mistake ??

As the discount and the total amount are not calculated by itself...and stored in the database

How to convert the

@.discpercentnumeric(5,2) to

@.discpercent ="NoDiscount" should be displayed when no discount is being given to the customers...

ALTER PROCEDURE CalculationStoredProcedure @.accountidint output, @.accountnamevarchar(20), @.opbalnumeric(10, 2), @.opbalcodechar(2), @.totalnumeric(10, 2), @.clbalnumeric(10, 2), @.clbalcodechar(2), @.discpercentnumeric(5,2), @.discamtnumeric(10, 2)asbeginset nocount on if @.opbalISNULL OR @.opbal = 0beginselect @.opbal=0select @.opbalcode=' 'select @.clbal= 0select @.total= 0select @.clbalcode=' ' @.discpercent ="NoDiscount" @.discamt=0end select @.accountid =isnull(max(accountid),0) + 1from accountmasterselect @.total=@.opbal - @.clbalfrom accountmasterselect @.discamt=@.total* @.discpercent/100from accountmasterbegin insert into accountmaster(accountname,opbal,opbalcode,clbal,clbalcode )values ( @.accountname,@.opbal,@.opbalcode,@.clbal,@.clbalcode )end set nocount offend

Thanxs in adv...

Hello my friend,

It appears you are using 1 field for 2 purposes; numeric calculation and text display. I would avoid doing this.

Declare another parameter (e.g. @.DiscDisplay) to hold a copy of the @.discpercent variable when a discount is being applied, and have it set to 'NoDiscount' if @.discpercent is 0. Then just display @.DiscDisplay on the web page in both situations.

Kind regards

Scotty

|||

thanxs for the reply...

can u pls help to calculate this for my above stored procedure its not working..

select @.total=@.opbal - @.clbalfrom accountmaster
select @.discamt=@.total* @.discpercent/100from accountmaster

|||

Looks like there is some confusion here. You are calculating a percentage from values in parameters. Then why do you have a "..FROM AccountMaster" in your SELECT?

select @.discamt=(@.opbal - @.clbal )* @.discpercent/100

should do.

|||

select @.total=(@.opbal - @.clbal )
not working in database it shows me NULL only

I tried with this also but don't work pls help me...

if (@.total IS NULL or @.total = 0)
begin
select @.total=(@.opbal - @.clbal )
end

thanxs for ur replies....

|||

Could it be because either @.opbal or @.clbal is null?

I would use the following: -

IF (ISNULL(@.Total, 0) = 0)
BEGIN
SET @.total = (ISNULL(@.opbal, 0) - ISNULL(@.clbal, 0))
END

SELECT @.Total

Kind regards

Scotty

|||

thanxs very much again...

Tried with ur suggestion...but don't work...why it does not returns/set/stores the value into the database...??

What can be the problem ?? Not understanding...i think everything is correct now...putting all of ur suggestions..together but not able to get the solution

|||

Hello again Sheenaa,

You say that you have put it all together based on our help. Can you show us all of the procedure that you have put together and we can see if you have made any mistakes.

Kind regards

Scotty

Help to deleting n numbers of records

Hi
One of our users have by mistake created a number of dublicate records in
the database. Now I need to delete the "faulty" records from the table and
just keep one of them. The records are linked to a customer table, so I have
a Recordid column in the table that I can use to group on. E.g. if I in the
table have 10 records with RecordID 1, I need to delete 9 of them and keep
1, 8 records with RecordID 2 I need to delete 7 and keep 1 etc.
I'll have to search the customer table to find the recordID's that is used
to link to the child table, so my plan is to use a cursor to find these and
put them into a variable. I'll then use this to find the record(s) in the
child table, get the number of records and then either delete them one by
one until I've only 1 pr. RecordID left or use SELECT Top x based on the
number of records found with each recordid and then delete all but 1 record.
It's not a huge number of records I need to delete so from a practical
and/or performance point of wiev it's not critical how I do it. It's more
that I'm currious to hear if my approach is the best one or if any of you
have any other ideas?
TIA
Regards
SteenTo answer this properly we'll need to know the keys and constraints in
your tables. Please post DDL (CREATE TABLE) and some sample data
(INSERTs) if you want help with the actual code.
You missed out one critical step from your solution: Add a new unique
constraint so that this can't happen again.
David Portas
SQL Server MVP
--|||Hi
First of all, it's a vendor application, so I can't change anything in the
database or application. I this case it's not a problem though, since it's
ok to create several records as they did, but in this case it's just because
they had some problems with a printer and therefore thay ran a wizard
several times which generated a number of records that where baiscally same.
It's these records thay now want to get deleted.
The 2 tables that's involved is called Lejer and Note.
CREATE TABLE [Lejer] (
[EjendomNr] [EjendomNr] NOT NULL ,
[LejemaalNr] [LejemaalNr] NOT NULL ,
[LejerNr] [LejerNr] NOT NULL ,
[LejerID] [RecordID] IDENTITY (1, 1) NOT NULL ,
....and about 200 more column definitions
CREATE TABLE [Note] (
[NoteID] [RecordID] NOT NULL ,
[Tabelnavn] [TabelNavn] NOT NULL ,
[RecordID] [RecordID] NOT NULL ,
[Dato] [Dato] NOT NULL ,
[NoteType] [KodeId] NOT NULL ,
....and some more column definitions.
The fields that links the tables are Lejer.LejerID and Note.RecordID.
Below is ans example of sample data
Lejer:
Ejendomnr Lejemaalnr Lejernr LejerID
1 1 1 1000
1 2 2 1001
1 3 3 1002
2 1 1 1003
2 2 2 1004
3 1 1 1005
3 2 2 1006
Note
NoteID RecordID NoteType
1 1000 29000
2 1000 29000
3 1000 29000
4 1000 29000
5 1001 29000
6 1001 29000
7 1001 29000
8 1002 29000
9 1002 29000
10 1002 29000
11 1003 29000
12 1003 29000
I'd like to find the notes where the type is e.g. 29000 and are linked a
record in the Lejer table with the Ejendomnr of e.g. 1.
and then delete all notes but 1.
In the above example, it means that if I use Ejendomnr = 1, I'll have the
Note records with NoteID 1 to 10. Out of these I'd like to delete 3 of the
ones with RecordID 1000, 2 of them with RecordID 1001 and 2 of them with
RecordID 1002.
As mentioned in my original post, I could get all the RecordID's into a
cursor and then e.g. count the number of occurences for each of them and
then do a delete n-1 times. I just don't know if that's the smartest way to
do it or if there's a better approach?
Regards
Steen
David Portas wrote:
> To answer this properly we'll need to know the keys and constraints in
> your tables. Please post DDL (CREATE TABLE) and some sample data
> (INSERTs) if you want help with the actual code.
> You missed out one critical step from your solution: Add a new unique
> constraint so that this can't happen again.
> --
> David Portas
> SQL Server MVP|||How about
Delete Note
Where NoteType = '29000'
And NoteId <> (
Select Min(N1.NoteId)
From Note As N1
Where Note.RecordId = N1.RecordId
And N1.NoteType = Note.NoteType
)
Here I'm assuming that a given Note.RecordId must exist in Lejer.LejerId.
However, it is possible that this is not the case and you want to ensure tha
t
the record does exist in the Lejer table then you could add an Exists clause
like so:
Delete Note
Where NoteType = '29000'
And Exists(
Select *
From Lejer As L1
Where L1.LegerId = Note.RecordId
)
And NoteId <> (
Select Min(N1.NoteId)
From Note As N1
Where Note.RecordId = N1.RecordId
And N1.NoteType = Note.NoteType
)
Obviously, you should execute this code carefully to ensure that it is produ
ce
the results you want before you commit against production data.
HTH
Thomas
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:OfARdISYFHA.2348@.TK2MSFTNGP14.phx.gbl...
> Hi
> First of all, it's a vendor application, so I can't change anything in the
> database or application. I this case it's not a problem though, since it's
ok
> to create several records as they did, but in this case it's just because
they
> had some problems with a printer and therefore thay ran a wizard several t
imes
> which generated a number of records that where baiscally same. It's these
> records thay now want to get deleted.
> The 2 tables that's involved is called Lejer and Note.
>
> CREATE TABLE [Lejer] (
> [EjendomNr] [EjendomNr] NOT NULL ,
> [LejemaalNr] [LejemaalNr] NOT NULL ,
> [LejerNr] [LejerNr] NOT NULL ,
> [LejerID] [RecordID] IDENTITY (1, 1) NOT NULL ,
> .....and about 200 more column definitions
>
> CREATE TABLE [Note] (
> [NoteID] [RecordID] NOT NULL ,
> [Tabelnavn] [TabelNavn] NOT NULL ,
> [RecordID] [RecordID] NOT NULL ,
> [Dato] [Dato] NOT NULL ,
> [NoteType] [KodeId] NOT NULL ,
> ....and some more column definitions.
> The fields that links the tables are Lejer.LejerID and Note.RecordID.
>
> Below is ans example of sample data
> Lejer:
> Ejendomnr Lejemaalnr Lejernr LejerID
> 1 1 1 1000
> 1 2 2 1001
> 1 3 3 1002
> 2 1 1 1003
> 2 2 2 1004
> 3 1 1 1005
> 3 2 2 1006
> Note
> NoteID RecordID NoteType
> 1 1000 29000
> 2 1000 29000
> 3 1000 29000
> 4 1000 29000
> 5 1001 29000
> 6 1001 29000
> 7 1001 29000
> 8 1002 29000
> 9 1002 29000
> 10 1002 29000
> 11 1003 29000
> 12 1003 29000
>
> I'd like to find the notes where the type is e.g. 29000 and are linked a
> record in the Lejer table with the Ejendomnr of e.g. 1.
> and then delete all notes but 1.
> In the above example, it means that if I use Ejendomnr = 1, I'll have the
Note
> records with NoteID 1 to 10. Out of these I'd like to delete 3 of the ones
> with RecordID 1000, 2 of them with RecordID 1001 and 2 of them with Record
ID
> 1002.
> As mentioned in my original post, I could get all the RecordID's into a cu
rsor
> and then e.g. count the number of occurences for each of them and then do
a
> delete n-1 times. I just don't know if that's the smartest way to do it or
if
> there's a better approach?
> Regards
> Steen
>
> David Portas wrote:
>|||Hi Thomas
Thanks for you input. That was another way of doing it than I had in my
mind. I'll try it out in my test db.
Regards
Steen
Thomas Coleman wrote:
> How about
> Delete Note
> Where NoteType = '29000'
> And NoteId <> (
> Select Min(N1.NoteId)
> From Note As N1
> Where Note.RecordId = N1.RecordId
> And N1.NoteType = Note.NoteType
> )
> Here I'm assuming that a given Note.RecordId must exist in
> Lejer.LejerId. However, it is possible that this is not the case and
> you want to ensure that the record does exist in the Lejer table then
> you could add an Exists clause like so:
> Delete Note
> Where NoteType = '29000'
> And Exists(
> Select *
> From Lejer As L1
> Where L1.LegerId = Note.RecordId
> )
> And NoteId <> (
> Select Min(N1.NoteId)
> From Note As N1
> Where Note.RecordId = N1.RecordId
> And N1.NoteType = Note.NoteType
> )
> Obviously, you should execute this code carefully to ensure that it
> is produce the results you want before you commit against production
> data.
> HTH
>
> Thomas
> "Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
> news:OfARdISYFHA.2348@.TK2MSFTNGP14.phx.gbl...

Help setting up a trace for a sp

Hello,
I haven't really used the trace program much. I am having a problem with
a sp that we created it seems to skip records during the cursor. I want to
setup a trace on that specific store procedure only is there a way to do
this? Thanks in advance. The name of the sp is called "datacollection"
Jake
I'm a little confused as to what you want to trace. Would you explain
more? Sounds like you want to trace the progress through a SP. Meaning you
want to show every command it executes? Sounds like you what you want to do
is use the Transact-SQL debugger. Is this correct? I have not used the
T-SQL debugger, so I really can't help you if this is what you are looking
for. But here is an msdn article that might get you started:
http://msdn.microsoft.com/library/de...qldebugger.asp
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Jake" <rondican@.hotmail.com> wrote in message
news:O7WS7hTWEHA.2940@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I haven't really used the trace program much. I am having a problem
with
> a sp that we created it seems to skip records during the cursor. I want to
> setup a trace on that specific store procedure only is there a way to do
> this? Thanks in advance. The name of the sp is called "datacollection"
> Jake
>
|||Gregory,
I was looking at the profiler tool that comes with SQL. You are correct
in that I want to see each line being processed for the sp. I will take a
look at this and see if I can tweak it to work for our needs. Thanks.
Jake
"Gregory A. Larsen" <greg.larsen@.netzero.com> wrote in message
news:O3SQ8uTWEHA.3012@.tk2msftngp13.phx.gbl...
> I'm a little confused as to what you want to trace. Would you explain
> more? Sounds like you want to trace the progress through a SP. Meaning
you
> want to show every command it executes? Sounds like you what you want to
do
> is use the Transact-SQL debugger. Is this correct? I have not used the
> T-SQL debugger, so I really can't help you if this is what you are looking
> for. But here is an msdn article that might get you started:
>
http://msdn.microsoft.com/library/de...qldebugger.asp
>
> --
> ----
--
> ----
--[vbcol=seagreen]
> --
> Need SQL Server Examples check out my website at
> http://www.geocities.com/sqlserverexamples
> "Jake" <rondican@.hotmail.com> wrote in message
> news:O7WS7hTWEHA.2940@.TK2MSFTNGP09.phx.gbl...
> with
to
>
|||You might also want to review the "Transact-SQL Debugger Window" topic in
BOL.
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Jake" <rondican@.hotmail.com> wrote in message
news:OM8ThzTWEHA.1048@.tk2msftngp13.phx.gbl...
> Gregory,
> I was looking at the profiler tool that comes with SQL. You are
correct[vbcol=seagreen]
> in that I want to see each line being processed for the sp. I will take a
> look at this and see if I can tweak it to work for our needs. Thanks.
> Jake
> "Gregory A. Larsen" <greg.larsen@.netzero.com> wrote in message
> news:O3SQ8uTWEHA.3012@.tk2msftngp13.phx.gbl...
> you
to[vbcol=seagreen]
> do
looking
>
http://msdn.microsoft.com/library/de...qldebugger.asp[vbcol=seagreen]
> ----
> --
> ----
> --
problem[vbcol=seagreen]
want[vbcol=seagreen]
> to
do
>

Help setting up a trace for a sp

Hello,
I haven't really used the trace program much. I am having a problem with
a sp that we created it seems to skip records during the cursor. I want to
setup a trace on that specific store procedure only is there a way to do
this? Thanks in advance. The name of the sp is called "datacollection"
JakeI'm a little confused as to what you want to trace. Would you explain
more? Sounds like you want to trace the progress through a SP. Meaning you
want to show every command it executes? Sounds like you what you want to do
is use the Transact-SQL debugger. Is this correct? I have not used the
T-SQL debugger, so I really can't help you if this is what you are looking
for. But here is an msdn article that might get you started:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbcon98/html/vbconthetsqldebugger.asp
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Jake" <rondican@.hotmail.com> wrote in message
news:O7WS7hTWEHA.2940@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I haven't really used the trace program much. I am having a problem
with
> a sp that we created it seems to skip records during the cursor. I want to
> setup a trace on that specific store procedure only is there a way to do
> this? Thanks in advance. The name of the sp is called "datacollection"
> Jake
>|||Gregory,
I was looking at the profiler tool that comes with SQL. You are correct
in that I want to see each line being processed for the sp. I will take a
look at this and see if I can tweak it to work for our needs. Thanks.
Jake
"Gregory A. Larsen" <greg.larsen@.netzero.com> wrote in message
news:O3SQ8uTWEHA.3012@.tk2msftngp13.phx.gbl...
> I'm a little confused as to what you want to trace. Would you explain
> more? Sounds like you want to trace the progress through a SP. Meaning
you
> want to show every command it executes? Sounds like you what you want to
do
> is use the Transact-SQL debugger. Is this correct? I have not used the
> T-SQL debugger, so I really can't help you if this is what you are looking
> for. But here is an msdn article that might get you started:
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbcon98/html/vbconthetsqldebugger.asp
>
> --
> ----
--
> ----
--
> --
> Need SQL Server Examples check out my website at
> http://www.geocities.com/sqlserverexamples
> "Jake" <rondican@.hotmail.com> wrote in message
> news:O7WS7hTWEHA.2940@.TK2MSFTNGP09.phx.gbl...
> > Hello,
> >
> > I haven't really used the trace program much. I am having a problem
> with
> > a sp that we created it seems to skip records during the cursor. I want
to
> > setup a trace on that specific store procedure only is there a way to do
> > this? Thanks in advance. The name of the sp is called "datacollection"
> >
> > Jake
> >
> >
>|||You might also want to review the "Transact-SQL Debugger Window" topic in
BOL.
--
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Jake" <rondican@.hotmail.com> wrote in message
news:OM8ThzTWEHA.1048@.tk2msftngp13.phx.gbl...
> Gregory,
> I was looking at the profiler tool that comes with SQL. You are
correct
> in that I want to see each line being processed for the sp. I will take a
> look at this and see if I can tweak it to work for our needs. Thanks.
> Jake
> "Gregory A. Larsen" <greg.larsen@.netzero.com> wrote in message
> news:O3SQ8uTWEHA.3012@.tk2msftngp13.phx.gbl...
> > I'm a little confused as to what you want to trace. Would you explain
> > more? Sounds like you want to trace the progress through a SP. Meaning
> you
> > want to show every command it executes? Sounds like you what you want
to
> do
> > is use the Transact-SQL debugger. Is this correct? I have not used the
> > T-SQL debugger, so I really can't help you if this is what you are
looking
> > for. But here is an msdn article that might get you started:
> >
> >
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbcon98/html/vbconthetsqldebugger.asp
> >
> >
> > --
> >
> ----
> --
> ----
> --
> > --
> >
> > Need SQL Server Examples check out my website at
> > http://www.geocities.com/sqlserverexamples
> > "Jake" <rondican@.hotmail.com> wrote in message
> > news:O7WS7hTWEHA.2940@.TK2MSFTNGP09.phx.gbl...
> > > Hello,
> > >
> > > I haven't really used the trace program much. I am having a
problem
> > with
> > > a sp that we created it seems to skip records during the cursor. I
want
> to
> > > setup a trace on that specific store procedure only is there a way to
do
> > > this? Thanks in advance. The name of the sp is called "datacollection"
> > >
> > > Jake
> > >
> > >
> >
> >
>

Help setting up a trace for a sp

Hello,
I haven't really used the trace program much. I am having a problem with
a sp that we created it seems to skip records during the cursor. I want to
setup a trace on that specific store procedure only is there a way to do
this? Thanks in advance. The name of the sp is called "datacollection"
JakeI'm a little confused as to what you want to trace. Would you explain
more? Sounds like you want to trace the progress through a SP. Meaning you
want to show every command it executes? Sounds like you what you want to do
is use the Transact-SQL debugger. Is this correct? I have not used the
T-SQL debugger, so I really can't help you if this is what you are looking
for. But here is an msdn article that might get you started:
nthetsqldebugger.asp" target="_blank">http://msdn.microsoft.com/library/d...sqldebugger.asp
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Jake" <rondican@.hotmail.com> wrote in message
news:O7WS7hTWEHA.2940@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I haven't really used the trace program much. I am having a problem
with
> a sp that we created it seems to skip records during the cursor. I want to
> setup a trace on that specific store procedure only is there a way to do
> this? Thanks in advance. The name of the sp is called "datacollection"
> Jake
>|||Gregory,
I was looking at the profiler tool that comes with SQL. You are correct
in that I want to see each line being processed for the sp. I will take a
look at this and see if I can tweak it to work for our needs. Thanks.
Jake
"Gregory A. Larsen" <greg.larsen@.netzero.com> wrote in message
news:O3SQ8uTWEHA.3012@.tk2msftngp13.phx.gbl...
> I'm a little confused as to what you want to trace. Would you explain
> more? Sounds like you want to trace the progress through a SP. Meaning
you
> want to show every command it executes? Sounds like you what you want to
do
> is use the Transact-SQL debugger. Is this correct? I have not used the
> T-SQL debugger, so I really can't help you if this is what you are looking
> for. But here is an msdn article that might get you started:
>
.asp" target="_blank">http://msdn.microsoft.com/library/d...ger
.asp
>
> --
> ----
--
> ----
--
> --
> Need SQL Server Examples check out my website at
> http://www.geocities.com/sqlserverexamples
> "Jake" <rondican@.hotmail.com> wrote in message
> news:O7WS7hTWEHA.2940@.TK2MSFTNGP09.phx.gbl...
> with
to[vbcol=seagreen]
>|||You might also want to review the "Transact-SQL Debugger Window" topic in
BOL.
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Jake" <rondican@.hotmail.com> wrote in message
news:OM8ThzTWEHA.1048@.tk2msftngp13.phx.gbl...
> Gregory,
> I was looking at the profiler tool that comes with SQL. You are
correct
> in that I want to see each line being processed for the sp. I will take a
> look at this and see if I can tweak it to work for our needs. Thanks.
> Jake
> "Gregory A. Larsen" <greg.larsen@.netzero.com> wrote in message
> news:O3SQ8uTWEHA.3012@.tk2msftngp13.phx.gbl...
> you
to[vbcol=seagreen]
> do
looking[vbcol=seagreen]
>
.asp" target="_blank">http://msdn.microsoft.com/library/d...ger
.asp
> ----
> --
> ----
> --
problem[vbcol=seagreen]
want[vbcol=seagreen]
> to
do[vbcol=seagreen]
>

Help setting datasource programatiically...

Hi gang...
I have sucessfully created a deployment application for my reports to be
deployed via web services to our customers. However, the only thing I am not
able to set correctly is the shared datasource for the report itself. Below
is a snippet of my code:
/Test/Test is my shared datasource. The curItem object is a catalog item
pointing to a report. The code below does not work - can anyone help me out?
Dim reference As New DataSourceReference
Dim dataSources(0) As DataSource
Dim ds As New DataSource
reference.Reference = "/Test/Test"
ds.Item = CType(reference, DataSourceDefinitionOrReference)
ds.Name = "/Test/Test"
dataSources(0) = ds
rsDeliverTo.SetReportDataSources(curItem.Path, dataSources)
Console.WriteLine("New reference set for the report.")
=-ChrisTry
ds.Name = "Test"
instead of
ds.Name = "/Test/Test"
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Christopher Conner" <someone@.someplace.com> wrote in message
news:u5OXwr3EFHA.3984@.TK2MSFTNGP14.phx.gbl...
> Hi gang...
> I have sucessfully created a deployment application for my reports to be
> deployed via web services to our customers. However, the only thing I am
> not able to set correctly is the shared datasource for the report itself.
> Below is a snippet of my code:
> /Test/Test is my shared datasource. The curItem object is a catalog item
> pointing to a report. The code below does not work - can anyone help me
> out?
> Dim reference As New DataSourceReference
> Dim dataSources(0) As DataSource
> Dim ds As New DataSource
> reference.Reference = "/Test/Test"
> ds.Item = CType(reference, DataSourceDefinitionOrReference)
> ds.Name = "/Test/Test"
> dataSources(0) = ds
> rsDeliverTo.SetReportDataSources(curItem.Path, dataSources)
> Console.WriteLine("New reference set for the report.")
> =-Chris
>|||Lev,
Thanks for the reply. I changed the ds.Name = "Test" and this is the error
I get back:
<Message msrs:ErrorCode="rsDataSourceNotFound"
msrs:HelpLink="http://g
o.microsoft.com/fwlink/?LinkId=20476&EvtSrc=Microsoft.ReportingServices.Diag
nostics.Utilities.ErrorStrings.resources.Strings&EvtID=rsDataSourceNotFound&
amp;ProdName=Microsoft%20SQL%20Server%20Reporting%20Services&ProdVer=8.00"
x
mlns:msrs="The">http://www.microsoft.com/sql/reportingservices">The data source
'Test' cannot be found in this report.</Message>
</MoreInformation>
<Warnings xmlns="http://www.microsoft.com/sql/reportingservices" />
What is weird is that I *know* that the datasource 'Test' is not in the
report -> as when I upload a report definition, it will not have it - in
this case I am trying to set the datasource connection information for the
report to point to the shared datasource Test...
You know what? Looking at the documentation - It says that the method
SetReportDataSources "Sets the properties that are associated with the data
sources of a specified report." So the method I am using is not going to
work, since my report does not have a report datasource assoicated with it
yet. The documentation says in the remarks section:
Remarks
The report server throws an exception if the SetReportDataSources method is
used to set the data source properties of a linked report. If a data source
that is passed in the DataSources parameter is not associated with the given
report, a SOAP exception is thrown with the error code rsDataSourceNotFound
Which is exactly what I am getting...
I am trying to set my report to use a shared datasource.
The sample in the docuementation for SetReportDataSources does not work.
Do you have any other suggestions Lev?
Do I need to set a report property? This is crazy - I have everything elese
done but setting the datasource information for a report to use a shared
datasource. The report doesn't have any datasources associated with it
because they are invalid when I copied the report from one server to the
other. I can manually set the shared datasource for the report - but it
would be nice to do it via code since I have over 200 reports.
=-Chris
"Lev Semenets [MSFT]" <levs@.microsoft.com> wrote in message
news:ueFDTi%23EFHA.1292@.TK2MSFTNGP10.phx.gbl...
> Try
> ds.Name = "Test"
> instead of
> ds.Name = "/Test/Test"
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> "Christopher Conner" <someone@.someplace.com> wrote in message
> news:u5OXwr3EFHA.3984@.TK2MSFTNGP14.phx.gbl...
>> Hi gang...
>> I have sucessfully created a deployment application for my reports to be
>> deployed via web services to our customers. However, the only thing I am
>> not able to set correctly is the shared datasource for the report itself.
>> Below is a snippet of my code:
>> /Test/Test is my shared datasource. The curItem object is a catalog item
>> pointing to a report. The code below does not work - can anyone help me
>> out?
>> Dim reference As New DataSourceReference
>> Dim dataSources(0) As DataSource
>> Dim ds As New DataSource
>> reference.Reference = "/Test/Test"
>> ds.Item = CType(reference, DataSourceDefinitionOrReference)
>> ds.Name = "/Test/Test"
>> dataSources(0) = ds
>> rsDeliverTo.SetReportDataSources(curItem.Path, dataSources)
>> Console.WriteLine("New reference set for the report.")
>> =-Chris
>|||Lev - I figured it out. I have posted the solution above to a newer post of
mine asking if anyone has figured it out. Thanks for trying to help.
=-Chris
"Lev Semenets [MSFT]" <levs@.microsoft.com> wrote in message
news:ueFDTi%23EFHA.1292@.TK2MSFTNGP10.phx.gbl...
> Try
> ds.Name = "Test"
> instead of
> ds.Name = "/Test/Test"
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> "Christopher Conner" <someone@.someplace.com> wrote in message
> news:u5OXwr3EFHA.3984@.TK2MSFTNGP14.phx.gbl...
>> Hi gang...
>> I have sucessfully created a deployment application for my reports to be
>> deployed via web services to our customers. However, the only thing I am
>> not able to set correctly is the shared datasource for the report itself.
>> Below is a snippet of my code:
>> /Test/Test is my shared datasource. The curItem object is a catalog item
>> pointing to a report. The code below does not work - can anyone help me
>> out?
>> Dim reference As New DataSourceReference
>> Dim dataSources(0) As DataSource
>> Dim ds As New DataSource
>> reference.Reference = "/Test/Test"
>> ds.Item = CType(reference, DataSourceDefinitionOrReference)
>> ds.Name = "/Test/Test"
>> dataSources(0) = ds
>> rsDeliverTo.SetReportDataSources(curItem.Path, dataSources)
>> Console.WriteLine("New reference set for the report.")
>> =-Chris
>|||Chris,
I'll check that sample. It could be doc bug.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Christopher Conner" <someone@.someplace.com> wrote in message
news:%23WfKDmFFFHA.1408@.TK2MSFTNGP10.phx.gbl...
> Lev - I figured it out. I have posted the solution above to a newer post
> of mine asking if anyone has figured it out. Thanks for trying to help.
> =-Chris
> "Lev Semenets [MSFT]" <levs@.microsoft.com> wrote in message
> news:ueFDTi%23EFHA.1292@.TK2MSFTNGP10.phx.gbl...
>> Try
>> ds.Name = "Test"
>> instead of
>> ds.Name = "/Test/Test"
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "Christopher Conner" <someone@.someplace.com> wrote in message
>> news:u5OXwr3EFHA.3984@.TK2MSFTNGP14.phx.gbl...
>> Hi gang...
>> I have sucessfully created a deployment application for my reports to be
>> deployed via web services to our customers. However, the only thing I am
>> not able to set correctly is the shared datasource for the report
>> itself. Below is a snippet of my code:
>> /Test/Test is my shared datasource. The curItem object is a catalog item
>> pointing to a report. The code below does not work - can anyone help me
>> out?
>> Dim reference As New DataSourceReference
>> Dim dataSources(0) As DataSource
>> Dim ds As New DataSource
>> reference.Reference = "/Test/Test"
>> ds.Item = CType(reference, DataSourceDefinitionOrReference)
>> ds.Name = "/Test/Test"
>> dataSources(0) = ds
>> rsDeliverTo.SetReportDataSources(curItem.Path, dataSources)
>> Console.WriteLine("New reference set for the report.")
>> =-Chris
>>
>

Help retrieving values from 2 similar stored procs

I created one stored proc, then copied it to create another similar stored proc with just some filtering changes in the second. Now I want to obtain the results from both like this:
ProjFee ProjGross DailyRunRate Var1 InHouse1 InHouse2 GrossGoal Group Name PostedAmount
---------
Row# 1 from current stored proc
Row # 2 from called stored proc
so something like this I should get in the end for example when both results are combined:
ProjFee ProjGross DailyRunRate Var1 InHouse1 InHouse2 GrossGoal Group Name PostedAmount
---------
100000 33455 200 300 345555 4455555 5666666 Arizona 56000
103400 22455 900 700 777555 3333555 5444666 Illinois 660000

The first stored proc (CurrentMonthCollections_AZ) attempts to include the results of the second stored proc at the end (CurrentMonthCollections_IL) by calling it and inserting the fields into a temp table.
I was told by Angel to instead convert the second stored proc into a UDF...but having trouble with this.
What is the best approach to get the desired 2 rows back that I need in that fasion?
Here is the code for the 2 stored procs (very long so I will post as as links):
http://www.webfound.net/CurrentMonthCollections_AZ.txt
http://www.webfound.net/CurrentMonthCollections_IL.txt
Look at the end of CurrentMonthCollections_AZ.txt to see where I'm stuck in trying to select the results (ProjFee ProjGross DailyRunRate Var1 InHouse1 InHouse2 GrossGoal Var1 PostedAmount
) from both stored procs.
I don't think UNION is what I want because it will combine stuff...I just want 2 separated rows

"I don't think UNION is what I want because it will combine stuff...I just want 2 separated rows"

Union is what you want:

select 1
union
select 2

returns

1
2

It seems to me that you could parameterize the procedures and get back the two rows that you want, or at least have one procedure and pass in the state you are interested in...that is a lot of proc to deal with though...

|||what about union all ?|||

The difference between the 2 is that union removes duplicates and union all does not

union all is also faster because of that

select 1
union
select 2
union
select 2

select 1
union all
select 2
union all
select 2

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||

Sorry, I should have been clearer:

UNION ALL doesn't eliminate duplicates, UNION does.

UNION ALL is probably the more correct way to go in this case, but it won't really make a difference with such a small set.

|||in this case, there are no dups so it doesn't matter I guess then.|||so I thought UNION puts it all in one row? I must be wrong...

Help required in import Data into sql 2005 from excel 4.0

Hi
I have to import data from a number of excel files to corresponding tables in SQL 2005. The excel files are created using excel 4.0. I have created an excel connection manager and provided it with the path of the excel sheet.Next i have added an excel source from the toolbox to the dataflow. I have set the connection manger, data access mode, and the name of the excel sheet (the wizard detects the sheet correctly) in the dialog window i get when i double click the excel source. Every thing goes fine till here. Now when i select the 'columns' in this dialog window or the preview button, i get this error

TITLE: Microsoft Visual Studio

Error at Data Flow Task [Excel Source [1]]: An OLE DB error has occurred. Error code: 0x80004005.
Error at Data Flow Task [Excel Source [1]]: Opening a rowset for "test4$" failed. Check that the object exists in the database.

ADDITIONAL INFORMATION:
Exception from HRESULT: 0xC02020E8 (Microsoft.SqlServer.DTSPipelineWrap)


Any ideas about why is this happening?
UmerI have the exact error. Please someone help.