Multithreaded run of SSIS packages

Is it safe to run several SSIS packages in parallel in a multithreaded application? The package objects are not accessed from multiple threads: every thread is handling its own package (see below).

void task_inside_thread()
{
System::String ^s = "...";
Microsoft::SqlServer::Dts::Runtime::Package p;
p.LoadFromXML(s, nullptr);

// callback handler
MyEventsClass ^ eventsClass = gcnew MyEventsClass();

DTSExecResult result = p.Execute(nullptr, nullptr, eventsClass, nullptr, nullptr);
return;

}

Is there any relevant documentation with respect to this issue?

Thanks,

Bogdan

Yes, this should be safe, packages are independent of each other.|||

I worried more about the fact the package instances might use global objects for registration and for signalling their current status.

Thanks for your answer.

Bogdan

|||Yes, they sometime do. I have to re-phrase it - they behave as independent objects, taking care of locking global state when needed.

Multirow insert statement,... how?

In MySQL I was able to insert multiple rows into the db like this:

REPLACE INTO Products (productid, price) VALUES (1, 55), (2, 88), (3, 99);

In transact-Sql (Sql Server 2000), this "REPLACE" keyword means something different. What it meant in MySQL is that if there is an existing row that has the same primary key value as one of the new rows being inserted, it will replace that old row with the new row.

How do I do this in SQL Server 2000?

Also, I can do this in MySQL:

INSERT INGORE INTO Products (productid, price) VALUES (1, 55), (2, 88), (3, 99);

This meant in MySQL is that if there is an existing row that has the same primary key value as one of the new rows being inserted, it will ignore that new row's insert and keep the values of the old row.

How do I do that also in SQL Server 2000?

ThanksYou can't easily. MySQL is CISC to MS SQLs RISC.
There is no equiv' REPLACE and no IGNORE. You have do everything the hard way.

You can use insert with a not exists (yuk) or populate a temp table var with the rows you want and load the same keyed values from the database. That way you can see the gaps (inserts) and the ones with data (updates). Still not gr8 but what you gonna do!|||Darn,... I have experience developing aps with both MySQL and SQL server and I think this is the first time ever that it's harder to do something with SQL SERVER. Usually, it's MySql that makes things harder.

No wonder why I couldn't find any documentation for the life of me.
Oh well!|||You could use, and I'm trying not to be sick when saying it, a datagram. I think that's got some of the features you want <shudder>. Might be worth a look though.|||How do I use a datagram? Can you give me some examples?|||try
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sqlxml3/htm/dotnet_8704.asp|||You can insert multiple rows into a table by doing the following (works for SQL 7 & 2000, not sure about MySQL):

insert into yourtable(field1, field2, field3)
select
customerid,
firstname,
lastname
from
customers
where
customerid between 1 and 10

This would insert several rows from the customers table into yourtable.|||youre kiddin me right tingent? That is absolutely off the subject of what we are talking about|||"What it meant in MySQL is that if there is an existing row that has the same primary key value as one of the new rows being inserted, it will replace that old row with the new row."

Surely, though, by definition you will only ever have ONE row that matches the primary key so isn't this in effect an UPDATE statement? Or does the replace statement insert a new row if a match isn't found (you didn't mention it did!) ?|||Ouch...ok, I admit I didn't completly read the post so I didn't know what REPLACE was doing in MySQL. My bad...|||But the SQL i wrote does exactly what the the thread title says, Multi-row insert statement.|||>> But the SQL i wrote does exactly what the the thread title says, Multi-row insert statement

True but you do need to read the question. I think you caused a tiny little bit of offense 'cause judging by the question I think they know how do a standard insert. Don't worry though, no ones perfect - and don't let some of the posters tell you otherwise ;)|||I apologize, I didn't mean it at all the way it sounded. Sometimes things don't come off the right way when you type them over the internet. If we were in person and I would've said that to you, you would've took it the right way because I would've said it in a joking way.

Sorry|||Tingent, again, I apologize for the way that sounded.

The Mult-Row insert statement that you have extracts rows from another area and inserts them into the table. I need to insert brand new rows.|||hat's off to you javan15, what I jolly good egg you are.

Multipls cursors help

Hello,
I am trying to create multiple cursors and update 2 separate tables. But the
data tables seem to be locking after I run them. Is my logic off? My code is
below -
set xact_abort on
declare @.user_id int, @.topic_name varchar(20), @.key_seq_num int
declare GetUserID cursor for
select [user_id] from users where [user_id] not in (select [user_id] from
userdata)
open GetUserID
fetch next from GetUserID into @.user_id
while @.@.fetch_status = 0
begin
begin tran
--new cursor
declare GetUserData cursor for
select topic_name from userdata where [user_id] = 327
open GetUserData
fetch next from GetUserData into @.topic_name
while @.@.fetch_status = 0
begin
begin tran
--new cursor
declare GetNextID cursor for
select key_seq_num from nextkey where key_name = 'userdata_user_data_id'
open GetNextID
fetch next from GetNextID into @.key_seq_num
while @.@.fetch_status = 0
begin
begin tran
insert into UserData(user_data_id, [user_id], topic_name, update_date,
update_user, create_date, create_user)
select @.key_seq_num, @.user_id, @.topic_name, getdate(), 'Default',
getdate(), 'Default'
update nextkey set key_seq_num = @.key_seq_num + 1
from nextkey where key_name = 'userdata_user_data_id'
commit tran
print @.key_seq_num
fetch next from GetNextID into @.key_seq_num
end
close GetNextID
deallocate GetNextID
fetch next from GetUserData into @.topic_name
end
close GetUserData
deallocate GetUserData
fetch next from GetUserID into @.user_id
end
close GetUserID
deallocate GetUserIDAnil,
Can you explain what are you trying to accomplish?
AMB
"Anil" wrote:

> Hello,
> I am trying to create multiple cursors and update 2 separate tables. But t
he
> data tables seem to be locking after I run them. Is my logic off? My code
is
> below -
> set xact_abort on
> declare @.user_id int, @.topic_name varchar(20), @.key_seq_num int
> declare GetUserID cursor for
> select [user_id] from users where [user_id] not in (select [user_id] from
> userdata)
> open GetUserID
> fetch next from GetUserID into @.user_id
> while @.@.fetch_status = 0
> begin
> begin tran
> --new cursor
> declare GetUserData cursor for
> select topic_name from userdata where [user_id] = 327
> open GetUserData
> fetch next from GetUserData into @.topic_name
> while @.@.fetch_status = 0
> begin
> begin tran
> --new cursor
> declare GetNextID cursor for
> select key_seq_num from nextkey where key_name = 'userdata_user_data_id'
> open GetNextID
> fetch next from GetNextID into @.key_seq_num
> while @.@.fetch_status = 0
> begin
> begin tran
> insert into UserData(user_data_id, [user_id], topic_name, update_date,
> update_user, create_date, create_user)
> select @.key_seq_num, @.user_id, @.topic_name, getdate(), 'Default',
> getdate(), 'Default'
> update nextkey set key_seq_num = @.key_seq_num + 1
> from nextkey where key_name = 'userdata_user_data_id'
> commit tran
> print @.key_seq_num
> fetch next from GetNextID into @.key_seq_num
> end
> close GetNextID
> deallocate GetNextID
> fetch next from GetUserData into @.topic_name
> end
> close GetUserData
> deallocate GetUserData
> fetch next from GetUserID into @.user_id
> end
> close GetUserID
> deallocate GetUserID|||I am trying to take all the user id's from cursor 1, some value from cursor
2
and 3 and update them into a table. Cursor 3 has an id that needs to be
incremented each time as it is not an auto number. The way I have laid out m
y
cursors, is that the correct way?
Thanks.
Anil
"Alejandro Mesa" wrote:
> Anil,
> Can you explain what are you trying to accomplish?
>
> AMB
>
> "Anil" wrote:
>|||Anil,
Can you explain it without referencing the cursors?
AMB
"Anil" wrote:
> I am trying to take all the user id's from cursor 1, some value from curso
r 2
> and 3 and update them into a table. Cursor 3 has an id that needs to be
> incremented each time as it is not an auto number. The way I have laid out
my
> cursors, is that the correct way?
> Thanks.
> Anil
> "Alejandro Mesa" wrote:
>|||Please provide DDL and sample data.
http://www.aspfaq.com/etiquette.asp?id=5006
AMB
"Alejandro Mesa" wrote:
> Anil,
> Can you explain it without referencing the cursors?
>
> AMB
>
> "Anil" wrote:
>|||Is the syntax correct for multiple cursors? Is it possible to do what I am
trying to do with cursors?
"Alejandro Mesa" wrote:
> Anil,
> Can you explain what are you trying to accomplish?
>
> AMB
>
> "Anil" wrote:
>|||Is the syntax correct? Is it possible to do what I am trying to do?
"Alejandro Mesa" wrote:
> Anil,
> Can you explain what are you trying to accomplish?
>
> AMB
>
> "Anil" wrote:
>|||Hi Anil,
Looks like the tables which you are accessing is getting locked since you do
all your stuff within a transaction.
You can do 2 things to see if the issue is happening because of a lock
1. Remove all transaction (begin/commit tran) and see if your code actually
works.
2. If your code works after step 1 then use WITH (NOLOCK) clause in your
select statements.
Regards,
Joe.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:47D3D1AF-4E14-4C0B-80B9-66ADCCF91254@.microsoft.com...
> Please provide DDL and sample data.
> http://www.aspfaq.com/etiquette.asp?id=5006
>
> AMB
> "Alejandro Mesa" wrote:
>
cursor 2
be
out my
tables. But the
My code is
[user_id] from
'userdata_user_data_id'
update_date,|||Hey Joe,
Thanks for the help. Worked like a charm!!
Anil
"Joe" wrote:

> Hi Anil,
> Looks like the tables which you are accessing is getting locked since you
do
> all your stuff within a transaction.
> You can do 2 things to see if the issue is happening because of a lock
> 1. Remove all transaction (begin/commit tran) and see if your code actuall
y
> works.
> 2. If your code works after step 1 then use WITH (NOLOCK) clause in your
> select statements.
>
> Regards,
> Joe.
>
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in messag
e
> news:47D3D1AF-4E14-4C0B-80B9-66ADCCF91254@.microsoft.com...
> cursor 2
> be
> out my
> tables. But the
> My code is
> [user_id] from
> 'userdata_user_data_id'
> update_date,
>
>|||Hey Joe,
Thanks for the help. Worked like a charm!!
Anil
"Joe" wrote:

> Hi Anil,
> Looks like the tables which you are accessing is getting locked since you
do
> all your stuff within a transaction.
> You can do 2 things to see if the issue is happening because of a lock
> 1. Remove all transaction (begin/commit tran) and see if your code actuall
y
> works.
> 2. If your code works after step 1 then use WITH (NOLOCK) clause in your
> select statements.
>
> Regards,
> Joe.
>
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in messag
e
> news:47D3D1AF-4E14-4C0B-80B9-66ADCCF91254@.microsoft.com...
> cursor 2
> be
> out my
> tables. But the
> My code is
> [user_id] from
> 'userdata_user_data_id'
> update_date,
>
>sql

multiples charts and tables on the same report from multiple datasets

Hi,
I am trying to use Reporting services toi create a report with a number of
charts and tables from the same datasource but multiple datasets. When I
created a table and dragged the data fields into the column it is
=First(Fields!OrderID.Value, "DataSet1")
It keeps repeating until the end of rows where in the dataset I used top5
SQL what should be the correct way to do it?
Also I am wondering if I can modify the color of and space between bar chart
items.
Thank you in advance,
SunnyRemove "First" from your code, so you end up with =Fields!OrderID.Value.
Also, in the charts, you might have to change your values. At least for me
the values shows up as =Count(Fields!OrderID.Value), and I usually don't
want to have "Count" at all.
You can change the background color of a chart, just right click and choose
properties. (DOn't remeber where, but you'll find it if you look for it.)
DOn't think you can change the spacing, except with just changing the size
of the chart.
Kaisa M. LIndahl
"Sansanee" <sansanee@.nospam.com> wrote in message
news:ufPXgHlNFHA.3704@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I am trying to use Reporting services toi create a report with a number of
> charts and tables from the same datasource but multiple datasets. When I
> created a table and dragged the data fields into the column it is
> =First(Fields!OrderID.Value, "DataSet1")
> It keeps repeating until the end of rows where in the dataset I used top5
> SQL what should be the correct way to do it?
> Also I am wondering if I can modify the color of and space between bar
chart
> items.
>
> Thank you in advance,
> Sunny
>

Multiple/optional parameters

Can I create a report that offers users a choice for the parameter. I want to show a sales report based on either Fiscal year or Calender Year. Can I do that with one report that allows an option on which parameter to choose or do I need two reports.

Thanks.

Hello John,

Yes it can be done in a single report but you would have to create an extra parameter and do all the logic in the Stored procedure.

1.) Create an extra parameter in the Report Parameters called ParamYear as Datatype string.

2.) In the available values select Non-Queried and enter FisacalYear in Label and Value and also the CalenderYear.

3.) In the stored Procedure the logic would be like this:

If @.Parm = fisicalyear

Begin

End

Else

Begin

End

Hope this helps..

|||

I'm running all my queries against the cube but I guess the logic would be similar.

I should include another parameter and the first thing they select is Fiscal or Calender and then the proper hirarchies are offered to pick from?

|||It depends on the hirarchies. You can set the default value which one you want first and follow on. In the SP it gets the value for the one it selected. so hirarchies doesn't matter I believe.|||Can you hide a parameter list and then display one parameter or the other depending on the data from the first parameter?|||

Yes , it is possible. I personally did not do it but this link might be helpful.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1087731&SiteID=1

John, I have this saved in my Code-Sample database and this was answered in the same forum.

This is possible. I'll give an example using the AdventureWorks db. Suppose you want to have a report with all the contacts. The first filter on your report will filter on the Title column, the second will filter on LastName. When a user selected a Title in the dropdownlist, the dropdownlist will be filled with all the LastName's that have the same Title as the one selected.

Here is how to do this:

1.) create a dataset 'TITLES' with the following sql statement:
SELECT DISTINCT Title FROM Person.Contact ORDER BY Title

2.) create a second dataset 'LASTNAMES' with the following sql statement:
SELECT DISTINCT LastName FROM Person.Contact WHERE Title = @.Title

3.)create a third dataset 'CONTACTDETAILS' with the following sql statement:
SELECT * FROM Person.Contact WHERE Title = @.Title AND LastName = @.LastName

4.) Go to the Layout tab and open the \Report\Report Parameters menu

Normally you will see two parameters: Title and LastName. Make sure that Title is the first parameter.

Select the Title parameter and set the following settings:
Available values\From Query\DataSet = TITLES
Available values\From Query\Value Field = Title
Available values\From Query\Label Field = Title

Select the LastName parameter and set the following settings:
Available values\From Query\DataSet = LASTNAMES
Available values\From Query\Value Field = LastName
Available values\From Query\Label Field = LastName

Click OK to leave the Report Parameters menu.

5.)Drag a table on your report and add fields from the CONTACTDETAILS dataset to it.

If everything went well and you run your report, you will first need to select a value for the Title filter. After that, the LastName filter will be filled based on the value selected from the Title filter. When selecting a name in there, the report will be shown.

|||

I'm not quite doing the same thing.

I want a report that will drop down a list of fiscal year/months or calender year/months depending upon user input. I've thought about creating a report with 2 subreports, one a fiscal year report and one a calender year report. The subreports will be toggled from the main report. I can default to one and let the user toggle between reports. No parameters will be passed, the user will make appropriate selection in the subreport.

Multiple/Duplicate SQL Server Worker Processes

I don't even know where to begin looking... I have a page that loads multiple web user controls...
I know I use one connection object class that is used in all my objects when executing the query (calling Stored Procedures).
The problem is when the first page is rendered and each user control queries the database (SQL Server),
it eventually slows down. In my controls, I use a lot of repeaters and internal queries per each repeater item.
So I know it hits the database quite often.
Problem is when I look in SQL Server Enterprise Manager Process Info, I have multiple worker processes sleeping.
My first thought is ASP.net is creating a new session connection (process) to the SQL Server? Why? How?
What do I do to check either my code is creating the connection object properly. Thanks!
Larry
I have discovered, when I created my connection object for ExecuteNonQuery(), I forgot to Close() the connection.
Since I didn't close the connection object when I was finished... anda few new instances of the connection object was created, it created anew connection object.
In other functions, I was using the DataAdapter which opened and closedthe connection for you, so I took that feature for granted andcompletely forgot to close the connection object when doing aExecuteNonQuery().
I get a doht for the day...
Thanks for taking your time in reading...

Multiple Zip Search

I'm heaving a heck of a time to get a sql query working. I'm being passed a series of zip codes and I need to return a list of all the rows that match those zip codes in a database of about 40,000 leads.

Can someone clue me in to what the commandtext of my query should look like?

Thanks!

EXEC( 'SELECT * FROM YourTable where YourZip IN (''' + @.z + ''')' )

where @.z = list of zip codes.

hth|||Well if you are inclined to avoid D-SQL and are using SQL Server 2000, you can use a UDF
or many other techniques which would prove much better than D-SQL.|||My problem is that I'm a web guy with just enough SQL 2000 experience to be dangerous. I'm not exactly sure that the first answer given would help me because the zip codes are different for every query. I have a list of zip codes and I have a parameter set up in my dataadapter (@.zip), but I can't get any results. It's probably an issue with single or double quotes, but i'm not sure. I'm putting all the zip codes together into a session variable, trying to get something like this:

WHERE ZIP LIKE '%80000%' OR ZIP LIKE '%72322%' etc. There can be up to 75 zips in one query.

I really appreciate the help!|||A user defined function to create an query with IN ('zip,zip,etc') is your best bet.

Here is someting I wrote a long time ago but it was similar to what you could do.

IF EXISTS (SELECT 1 FROM sysobjects WHERE name = N'fnConvertListToStringTable')
DROP FUNCTION [dbo].[fnConvertListToStringTable]
GO
CREATE FUNCTION [dbo].[fnConvertListToStringTable] (
@.list varchar(1000)
)
RETURNS @.ListTable table (ItemID varchar(250) NULL)
AS
BEGIN
IF(SUBSTRING(@.list,LEN(@.list),1) <> ',')
BEGIN
SET @.list = @.list + ','
END
DECLARE @.StartLocation int
DECLARE@.Length int
SET @.StartLocation = 0
SET @.Length = 0
SELECT @.StartLocation = CHARINDEX(',',@.list,@.StartLocation+1)
WHILE @.StartLocation > 0
BEGIN
IF(LOWER(RTRIM(LTRIM(SUBSTRING(@.list,@.Length+1,(@.StartLocation-@.length) -1))))) = 'null'
INSERT INTO @.ListTable(ItemID) VALUES (Null)
ELSE
BEGIN
INSERT INTO @.ListTable(ItemID)
VALUES (RTRIM(LTRIM(SUBSTRING(@.list,@.Length+1,(@.StartLocation-@.length) -1))))
END
SET @.Length = @.StartLocation
SET @.StartLocation = CHARINDEX(',', @.list, @.StartLocation +1)
END
RETURN
END
GO
select * from dbo.fnConvertListToStringTable(' yjyj, 1345, 1234,NUll')

The code above actually put the values in a table and then used a where in (Select from that table) but in your case you can juse use the part that does the parsing at the beginning.sql

Multiple Y-Axis values in report

I will try to explain this best I can. I have a report where it shows total number of pieces over time. This range is from 0 to like 100 pieces. I have done a UNION query to add average accumulation (in inches).

If adding average accumulation to the "Data fields" section of the report in report designer, It shows the average accumulation on the bottom in releation to the number of pieces. (which is what I want.. But.. ) The numbers are not proportional obviously there wont be 60 inches of snow.. more like 1 or two inches.. So the line is very close to the bottom almost invisible..

Now that ive dont a muck up job of explaining.. This is what im looking for.. Some type of chart that will overlay the average accumulation but somehow be smart enough to show the different scale of precipitation vs pieces of equipment.

VS 2003 SQL Server 2000

Multiple y-axis are currently not supported through the built-in charts in Reporting Services. However, with RS 2005 there is a new feature called CustomReportItem which third party ISVs (Dundas, ChartFX, etc.) take advantage of to integrate their own charting solutions directly into RS 2005. These charts do support multiple y-axis within RS 2005.

-- Robert

|||It close to 1 year since the previous reply. Is multiple y-axis currently supported in reporting service now?

Multiple Y-Axis values in report

I will try to explain this best I can. I have a report where it shows total number of pieces over time. This range is from 0 to like 100 pieces. I have done a UNION query to add average accumulation (in inches).

If adding average accumulation to the "Data fields" section of the report in report designer, It shows the average accumulation on the bottom in releation to the number of pieces. (which is what I want.. But.. ) The numbers are not proportional obviously there wont be 60 inches of snow.. more like 1 or two inches.. So the line is very close to the bottom almost invisible..

Now that ive dont a muck up job of explaining.. This is what im looking for.. Some type of chart that will overlay the average accumulation but somehow be smart enough to show the different scale of precipitation vs pieces of equipment.

VS 2003 SQL Server 2000

Multiple y-axis are currently not supported through the built-in charts in Reporting Services. However, with RS 2005 there is a new feature called CustomReportItem which third party ISVs (Dundas, ChartFX, etc.) take advantage of to integrate their own charting solutions directly into RS 2005. These charts do support multiple y-axis within RS 2005.

-- Robert

|||It close to 1 year since the previous reply. Is multiple y-axis currently supported in reporting service now?

multiple xml namespaces

I have an xml document with nested fragments, each with their own default
namespace. My xquery select statement fails because I can only declare one
default namespace. Has anyone encountered this issue before? Am I
overlooking something?
Random wrote:
> I have an xml document with nested fragments, each with their own default
> namespace. My xquery select statement fails because I can only declare one
> default namespace. Has anyone encountered this issue before? Am I
> overlooking something?
If the XML has several default namespaces then with your XQuery you
cannot use the declare default namespace directive for all of them. You
can however declare your own prefixes for those namespaces and use those
in XQuery expressions like in this example:
DECLARE @.x XML;
SET @.x = '<foo xmlns="http://example.com/ns1">
<bar xmlns="http://example.com/ns2">
<foobar xmlns="http://example.com/ns3">foobar</foobar>
</bar>
</foo>';
SELECT @.x.query('
declare namespace pf1="http://example.com/ns1";
declare namespace pf2="http://example.com/ns2";
declare namespace pf3="http://example.com/ns3";
pf1:foo/pf2:bar/pf3:foobar
');

Martin Honnen -- MVP XML
http://JavaScript.FAQTs.com/

multiple xml namespaces

I have an xml document with nested fragments, each with their own default
namespace. My xquery select statement fails because I can only declare one
default namespace. Has anyone encountered this issue before? Am I
overlooking something?Random wrote:
> I have an xml document with nested fragments, each with their own default
> namespace. My xquery select statement fails because I can only declare on
e
> default namespace. Has anyone encountered this issue before? Am I
> overlooking something?
If the XML has several default namespaces then with your XQuery you
cannot use the declare default namespace directive for all of them. You
can however declare your own prefixes for those namespaces and use those
in XQuery expressions like in this example:
DECLARE @.x XML;
SET @.x = '<foo xmlns="http://example.com/ns1">
<bar xmlns="http://example.com/ns2">
<foobar xmlns="http://example.com/ns3">foobar</foobar>
</bar>
</foo>';
SELECT @.x.query('
declare namespace pf1="http://example.com/ns1";
declare namespace pf2="http://example.com/ns2";
declare namespace pf3="http://example.com/ns3";
pf1:foo/pf2:bar/pf3:foobar
');
Martin Honnen -- MVP XML
http://JavaScript.FAQTs.com/

Multiple XML Files as DataSource

Hi All,

New to the forums, so, hello.

I want to import data from multiple XML files into SQL 2005 using SSIS. I know how to set up a flat data source etc... but am unsure how I would go about importing from multiple files.

Anyone got any ideas?

Thanks,
Drammy

For XML files, you obviously use the XML source.

The multiple files issue is the same for flat files or XML files, generally the For Each Loop container, which has a For Each File enumerator.

There are plenty of example of how to use this, including Books Online. Here is just one -

Looping over files with the Foreach Loop
(http://www.sqlis.com/55.aspx)

|||Thanks DarrenSQLIS,

Drammy
|||I have actually found that this doesn't work with XML files...

I'm getting an error:

TITLE: Editing Component

The component has detected potential metadata corruption during validation.
Error at Import XML Files [XML Source [1]]: The property "XMLDataVariable" on the component "XML Source" (1) was empty.

Error at Import XML Files [XML Source [1]]: The component "XML Source" (1) was unable to read the XML data.

Error at Import XML Files [XML Source [1]]: There was an error setting up the mapping. Pipeline component has returned HRESULT error code 0xC02090D0 from a method call.

It may not be possible to recover to a valid state using a component-specific editor. Do you want to use the Advanced Editor dialog box for editing this component?

BUTTONS:

&Yes
&No

Any ideas?
|||

Even if you are using expressions to populate certain properties at run time; BIDS requires some properties have an 'initia' or 'design time' value; so the task/package can be validate. Make sure you provide a value for XMLDataVariable property in XML Source component.

If you are doing so, then provide more details of what you are doing in the package.

sql

multiple wildcards in Foreach Loop

Hi, I am using Foreach Loop to loop through files in a directory...
I would like to use more than one wildcards (e.g. *.txt *.log ).. but the container does not seem to work that way. It only takes one wildcard...

Is there anyway i can pass in multiple file extensions ?
thanks

Hi mf915,

I do not know if this will help.

If you have only .txt and .log files in the directory then you can use filename.* or *.*. If there is some files in the directory that you do not want to get picked up by the foreach loop, then you will have to move the files to a seperate folder. This will only work if your .txt and .log files is in the same format for example comma separated, have the same number of columns and headings.

Kind Regards,

Joos Nieuwoudt

Multiple while fetch cursor code

I seem to have a few problems with the below double cursor procedure. Probably due to the fact that I have two while loops based on fetch status. Or?

What I want to do is select out a series of numbers in medlemmer_cursor(currently set to only one number, for which I know I get results) and for each of these numbers select their MCPS code and gather these in a single string.

For some reason the outpiut (the insert into statement) returns the correct number 9611 but the second variable @.instrumentlinje remains empty.

If I test the select clause for 9611, it gets 4 lines. So to me its like the "SELECT @.instrumentlinje = @.instrumentlinje + ' ' + @.instrument" statement doesn't execute.

DELETE FROM ALL_tbl_instrumentkoder

DECLARE @.medlem int
DECLARE @.instrument varchar(10)
DECLARE @.instrumentlinje varchar(150)

DECLARE medlemmer_cursor CURSOR FOR
SELECT medlemsnummer
FROM ket.ALL_tbl_medlemsinfo (NOLOCK)
WHERE medlemsnummer = 9611

DECLARE instrumenter_cursor CURSOR FOR
SELECT [MCPS Kode]
FROM Gramex_DW.dbo.Instrumentlinie (NOLOCK)
WHERE Medlemsnummer = @.medlem

OPEN medlemmer_cursor

FETCH NEXT FROM medlemmer_cursor INTO @.medlem

WHILE @.@.FETCH_STATUS = 0
BEGIN

OPEN instrumenter_cursor
FETCH NEXT FROM instrumenter_cursor INTO @.instrument

WHILE @.@.FETCH_STATUS = 0
BEGIN
SELECT @.instrumentlinje = @.instrumentlinje + ' ' + @.instrument
FETCH NEXT FROM instrumenter_cursor INTO @.instrument
END

CLOSE instrumenter_cursor

INSERT INTO ALL_tbl_instrumentkoder VALUES(@.medlem, @.instrumentlinje)

FETCH NEXT FROM medlemmer_cursor INTO @.medlem

END

CLOSE medlemmer_cursor
DEALLOCATE medlemmer_cursor
DEALLOCATE instrumenter_cursorWell, I suspect the problem is related to referencing a variable in your cursor definition, but you shouldn't be using a cursor anyway.

Here is a simpler (non-cursor) method:

First, create this function:
create function dbo.instrumentlinje(@.medlem int)
returns varchar(4000) as
begin
declare @.instrumentlinje
select @.instrumentlinje = isnull(@.instrumentlinje + ' ', '') + MCPS Kode
from Gramex_DW.dbo.Instrumentlinie (NOLOCK)
WHERE Medlemsnummer = @.medlem

return @.instrumentlinje
end

Then, run this code:
insert into ALL_tbl_instrumentkoder
(medlem,
instrumentlinje)
select medlemsnummer,
dbo.instrumentlinje(medlem)
FROM ket.ALL_tbl_medlemsinfo (NOLOCK)
WHERE medlemsnummer = 9611

Warning! Not tested for syntax errors, and you may need to edit object ownership.|||Basically the same way I did it in Access.. just a greenhorn when it comes to SQL-server.

Thanks man :-)|||TSQL is similar to Access SQL, though there are a few syntactical differences. The concept of avoiding cursors and loops in favor of set-based operations is the same, though.

Multiple WHEREs in a query?

I want a query ro select the number of wins and the number of games played for a team. The pertinent columns are all in the same table and I was trying to get the information all in one query. Is something like this possible?
SELECT COUNT(*) AS Wins
FROM tbl_Schedule
WHERE Winner = 'IND'
SELECT COUNT(*) AS GamesPlayed
FROM tbl_Schedule
WHERE HomeID = 'IND' OR VisitorID = 'IND'
How would I merge the two, or can I even do that?SELECT
'Wins' = (SELECT COUNT(*) FROM tbl_Schedule WHERE Winner = 'IND'),
'GamesPlayed' = (SELECT COUNT(*) AS GamesPlayed FROM tbl_Schedule WHERE HomeID = 'IND' OR VisitorID = 'IND')|||Oh, very cool. I had no idea you could format them like that. Thanks for the help!

multiple where statement in a SP

Hello, a small question. I have a search page on the site where the user can search for other users, they can fill in age, gender etc

The question is, how should i extract all that information from the database, i am using SP in MS SQL 2005, C#.

For each of the options is there a possibility to press "not specified", how do i build up a query in the sql server?

Should i just

IF (@.Age1 <> '')
BEGIN
SET @.SearchString = @.SearchString + 'AND (profile_publicinfo.age = ' + @.Age1 + ')'
END

and then continue like this?

so it would look something like this:

SELECT username, gender, signupdate .... FROM profile_publicinfo FULL OUTER JOIN ..... WHERE (@.SearchString)

any ideas?

I highly recomomend Erland Sommarskog's article:Dynamic Search Conditions in T-SQL.|||Thanks, Erland Sommarskog's article solved my problems.sql

Multiple WHERE replacement??

Hello,
I have a table relationship that connects a list of Amenities provided by
each Property, such as:
Properties
--
PropertyID int
PropertyName nvarchar(100)
Amenities
--
AmenityID int
AmenityName nvarchar(50)
PropertyAmenities
--
PropertyID int,
AmenityID int
I want to be able to pull a list of Properties that have a certiain
Amenities, such as this static query:
SELECT PropertyID, PropertyName
FROM Properties p
WHERE 1 = 1
AND PropertyID IN (SELECT PropertyID FROM PropertyAmenities WHERE
AmenityID = 1)
AND PropertyID IN (SELECT PropertyID FROM PropertyAmenities WHERE
AmenityID = 2)
AND PropertyID IN (SELECT PropertyID FROM PropertyAmenities WHERE
AmenityID = 3)
AND PropertyID IN (SELECT PropertyID FROM PropertyAmenities WHERE
AmenityID = 4)
QUESTION:
What is the best way to setup an efficient Stored Procedure to run this
query, based on a dynamic list of AmenityID's? I'm thinking that there's a
better way, than to dynamically build these WHERE clauses and pass them to
the SQL Server.
I might pass seven integer values on one execution, and three on another.
Thank you for any light you can shed on this.Arrays and Lists in SQL Server
http://www.sommarskog.se/arrays-in-sql.html
AMB
"Vito D" wrote:

> Hello,
> I have a table relationship that connects a list of Amenities provided by
> each Property, such as:
> Properties
> --
> PropertyID int
> PropertyName nvarchar(100)
>
> Amenities
> --
> AmenityID int
> AmenityName nvarchar(50)
>
> PropertyAmenities
> --
> PropertyID int,
> AmenityID int
>
> I want to be able to pull a list of Properties that have a certiain
> Amenities, such as this static query:
> SELECT PropertyID, PropertyName
> FROM Properties p
> WHERE 1 = 1
> AND PropertyID IN (SELECT PropertyID FROM PropertyAmenities WHERE
> AmenityID = 1)
> AND PropertyID IN (SELECT PropertyID FROM PropertyAmenities WHERE
> AmenityID = 2)
> AND PropertyID IN (SELECT PropertyID FROM PropertyAmenities WHERE
> AmenityID = 3)
> AND PropertyID IN (SELECT PropertyID FROM PropertyAmenities WHERE
> AmenityID = 4)
> QUESTION:
> What is the best way to setup an efficient Stored Procedure to run this
> query, based on a dynamic list of AmenityID's? I'm thinking that there's
a
> better way, than to dynamically build these WHERE clauses and pass them to
> the SQL Server.
> I might pass seven integer values on one execution, and three on another.
> Thank you for any light you can shed on this.
>
>|||Since you didnt post DDL, I didn't build the tables and test this, but give
it a shot.
This is basically relational division, which you can find better examples of
if you search for posts by CELKO in this news group, or just do a google
search. I have nto really worked with this much, but I think this should
work.
Combine this with dynamic SQL to build your in clause (or populate a temp
table with the values for your in clause).
This code assumes that you have PKs on properties(PropertyID),
PropertyAmenities (PropertyID, AmenityID) and Amenities(AmenityID).
SELECT prop.PropertyID, prop.PropertyName, count(1)
FROM Properties prop
inner join PropertyAmenities propamen
on propamen.PropertyID = prop.PropertyID
where propamen.AmenityID in (1,2,3,4)
having count(1) = (select count(1)
from Amenities amen
where amen.AmenityID in (1,2,3,4)
)
"Vito D" <VdeleteDeCarlo@.ThePulse.remove.com> wrote in message
news:uVEt8btbGHA.536@.TK2MSFTNGP02.phx.gbl...
> Hello,
> I have a table relationship that connects a list of Amenities provided by
> each Property, such as:
> Properties
> --
> PropertyID int
> PropertyName nvarchar(100)
>
> Amenities
> --
> AmenityID int
> AmenityName nvarchar(50)
>
> PropertyAmenities
> --
> PropertyID int,
> AmenityID int
>
> I want to be able to pull a list of Properties that have a certiain
> Amenities, such as this static query:
> SELECT PropertyID, PropertyName
> FROM Properties p
> WHERE 1 = 1
> AND PropertyID IN (SELECT PropertyID FROM PropertyAmenities WHERE
> AmenityID = 1)
> AND PropertyID IN (SELECT PropertyID FROM PropertyAmenities WHERE
> AmenityID = 2)
> AND PropertyID IN (SELECT PropertyID FROM PropertyAmenities WHERE
> AmenityID = 3)
> AND PropertyID IN (SELECT PropertyID FROM PropertyAmenities WHERE
> AmenityID = 4)
> QUESTION:
> What is the best way to setup an efficient Stored Procedure to run this
> query, based on a dynamic list of AmenityID's? I'm thinking that there's
a
> better way, than to dynamically build these WHERE clauses and pass them to
> the SQL Server.
> I might pass seven integer values on one execution, and three on another.
> Thank you for any light you can shed on this.
>

multiple where clauses

Hello helpful people :)

I have this sproc.

ALTER PROCEDURE

dbo.cis_UpdateCourseUserWithGrade

@.Grade

nvarchar,

@.UaaStudentId

nchar,

@.CourseId

int

AS

UPDATEcis_CourseUserSETGrade =@.GradeWHEREUaaStudentId = @.UaaStudentId

AND WHERE CourseID = @.CourseId

RETURN

the ANE WHERE isn't much liked. How can I update the field with the grade where both of the conditions are true? I'm getting an invalid syntax near @.UaaStudentId

Thanks.

ALTER PROCEDURE

dbo.cis_UpdateCourseUserWithGrade

@.Gradenvarchar,
@.UaaStudentIdnchar,
@.CourseId int

AS

UPDATEcis_CourseUserSETGrade =@.GradeWHEREUaaStudentId = @.UaaStudentId AND CourseID = @.CourseId

RETURN

In other words, don't repeat the word WHERE.

|||

Mikesdotnetting:

ALTER PROCEDURE

dbo.cis_UpdateCourseUserWithGrade

@.Gradenvarchar,
@.UaaStudentIdnchar,
@.CourseId int

AS

UPDATEcis_CourseUserSETGrade =@.GradeWHEREUaaStudentId = @.UaaStudentId AND CourseID = @.CourseId

RETURN

In other words, don't repeat the word WHERE.

thanks a million

|||I would also recommend defining the size for the parameters @.Grade and @.UaaStudentId. If you dont specify a size SQL uses the default size (I am making a wild guess its around 32) so if you send in a value anything more than that it could get truncated.|||

Could you mark my post as the answer, please?

Multiple Where Clause on One Report

Hello People, Please help. I have a basic report with a parameter in the 'Where" clause called (@.Stat) from the statement below:

" WHERE contractinfo.termdate >= GETDATE()
AND provider.status= 'Active' AND provider.credentialstatus = (@.Stat)"

This variable has one of two values: 'A' or 'B' that the user selects, how do I set it up so that if user selects say 'A' then the Where clause would go to one set of constraints ie

"WHERE contract.description NOT LIKE 'NON%' "

But if the user selects 'B' then the Where clause would go to a different set of constraint ie

"WHERE contract.description LIKE 'NON%' "

Thanks

You might be able to change your WHERE clause from this:

Code Snippet

WHERE contractinfo.termdate >= GETDATE()

AND provider.status= 'Active' AND provider.credentialstatus = (@.Stat)

to this:

Code Snippet

WHERE contractinfo.termdate >= GETDATE()

AND provider.status= 'Active'

AND ( @.stat = 'A' AND contact.description not like 'NON%' OR

@.stat = 'B' AND contact.description like 'NON%'

)

|||Thanks for the help Kent that worked great, the only thing I did different for my report was leaving the original (@.Stat) parameter in also otherwise it would not have filtered it by the "A or B" condition and then adding your script suggestion.. Thanks again

Multiple where clause help please

Hello,
What is wrong with this query? There are no messages other than (0 row(s)
affected).
The criteria 5 and 16 does exist.
UPDATE articles
SET DEPART_REF = '19'
where ARTICLE_PARENT_ID = '5' or ARTICLE_PARENT_ID = '16'
Thanks in advance from a sql newbie
JakeNevermind, I am asking for the wrong fields in the where clause!
"GitarJake" <spamaintme@.oz.net> wrote in message
news:SXEXd.58461$SF.35123@.lakeread08...
> Hello,
> What is wrong with this query? There are no messages other than (0 row(s)
> affected).
> The criteria 5 and 16 does exist.
> UPDATE articles
> SET DEPART_REF = '19'
> where ARTICLE_PARENT_ID = '5' or ARTICLE_PARENT_ID = '16'
> Thanks in advance from a sql newbie
> Jake
>