I have a large well formed XML document which I recieved from a program. In
order to quickly insert it into the database, I set up a table with an
identity column and an ntext column. This works great for queuing the
documents, but I need to retrieve the ntext column and use
sp_xml_preparedocument on it so that I can shred the document and process th
e
hierarchal data. I have not found a way to dynamically use the column from m
y
table since you can not declare an ntext column as a local variable. Any
ideas?
--
Bob bAnd you won't. I understand your issue and you certainly aren't
the first to have this problem. You'll need to use ADO or ADO.NET
to at least retrieve it and pass it back to a stored procedure as an
input parameter. Or, parse the XmlDocument client side and send
the data back normally.
This is sort of on target:
http://www.eggheadcafe.com/articles...er_bulkload.asp
2005 Microsoft MVP C#
Robbe Morris
http://www.robbemorris.com
http://www.learncsharp.net/home/listings.aspx
"bob b" <bobb@.discussions.microsoft.com> wrote in message
news:0194A7E4-952D-4D7D-9CF1-10A9ED508178@.microsoft.com...
>I have a large well formed XML document which I recieved from a program. In
> order to quickly insert it into the database, I set up a table with an
> identity column and an ntext column. This works great for queuing the
> documents, but I need to retrieve the ntext column and use
> sp_xml_preparedocument on it so that I can shred the document and process
> the
> hierarchal data. I have not found a way to dynamically use the column from
> my
> table since you can not declare an ntext column as a local variable. Any
> ideas?
> --
> Bob b|||Besides very ugly workarounds such as using nested EXEC or sp_OA stored
procs, you better use the SQLXML 3.0 XML Bulkload object client-side.
Note that SQL Server 2005 has fortunately no such limits anymore.
Best regards
Michael
"Robbe Morris [C# MVP]" <info@.turnkeytools.com> wrote in message
news:%23MfyA2fHFHA.3352@.TK2MSFTNGP10.phx.gbl...
> And you won't. I understand your issue and you certainly aren't
> the first to have this problem. You'll need to use ADO or ADO.NET
> to at least retrieve it and pass it back to a stored procedure as an
> input parameter. Or, parse the XmlDocument client side and send
> the data back normally.
> This is sort of on target:
> http://www.eggheadcafe.com/articles...er_bulkload.asp
> --
> 2005 Microsoft MVP C#
> Robbe Morris
> http://www.robbemorris.com
> http://www.learncsharp.net/home/listings.aspx
>
> "bob b" <bobb@.discussions.microsoft.com> wrote in message
> news:0194A7E4-952D-4D7D-9CF1-10A9ED508178@.microsoft.com...
>
Showing posts with label reusing. Show all posts
Showing posts with label reusing. Show all posts
Monday, March 12, 2012
Reusing XML from a table
I have a large well formed XML document which I recieved from a program. In
order to quickly insert it into the database, I set up a table with an
identity column and an ntext column. This works great for queuing the
documents, but I need to retrieve the ntext column and use
sp_xml_preparedocument on it so that I can shred the document and process the
hierarchal data. I have not found a way to dynamically use the column from my
table since you can not declare an ntext column as a local variable. Any
ideas?
Bob b
And you won't. I understand your issue and you certainly aren't
the first to have this problem. You'll need to use ADO or ADO.NET
to at least retrieve it and pass it back to a stored procedure as an
input parameter. Or, parse the XmlDocument client side and send
the data back normally.
This is sort of on target:
http://www.eggheadcafe.com/articles/...r_bulkload.asp
2005 Microsoft MVP C#
Robbe Morris
http://www.robbemorris.com
http://www.learncsharp.net/home/listings.aspx
"bob b" <bobb@.discussions.microsoft.com> wrote in message
news:0194A7E4-952D-4D7D-9CF1-10A9ED508178@.microsoft.com...
>I have a large well formed XML document which I recieved from a program. In
> order to quickly insert it into the database, I set up a table with an
> identity column and an ntext column. This works great for queuing the
> documents, but I need to retrieve the ntext column and use
> sp_xml_preparedocument on it so that I can shred the document and process
> the
> hierarchal data. I have not found a way to dynamically use the column from
> my
> table since you can not declare an ntext column as a local variable. Any
> ideas?
> --
> Bob b
|||Besides very ugly workarounds such as using nested EXEC or sp_OA stored
procs, you better use the SQLXML 3.0 XML Bulkload object client-side.
Note that SQL Server 2005 has fortunately no such limits anymore.
Best regards
Michael
"Robbe Morris [C# MVP]" <info@.turnkeytools.com> wrote in message
news:%23MfyA2fHFHA.3352@.TK2MSFTNGP10.phx.gbl...
> And you won't. I understand your issue and you certainly aren't
> the first to have this problem. You'll need to use ADO or ADO.NET
> to at least retrieve it and pass it back to a stored procedure as an
> input parameter. Or, parse the XmlDocument client side and send
> the data back normally.
> This is sort of on target:
> http://www.eggheadcafe.com/articles/...r_bulkload.asp
> --
> 2005 Microsoft MVP C#
> Robbe Morris
> http://www.robbemorris.com
> http://www.learncsharp.net/home/listings.aspx
>
> "bob b" <bobb@.discussions.microsoft.com> wrote in message
> news:0194A7E4-952D-4D7D-9CF1-10A9ED508178@.microsoft.com...
>
order to quickly insert it into the database, I set up a table with an
identity column and an ntext column. This works great for queuing the
documents, but I need to retrieve the ntext column and use
sp_xml_preparedocument on it so that I can shred the document and process the
hierarchal data. I have not found a way to dynamically use the column from my
table since you can not declare an ntext column as a local variable. Any
ideas?
Bob b
And you won't. I understand your issue and you certainly aren't
the first to have this problem. You'll need to use ADO or ADO.NET
to at least retrieve it and pass it back to a stored procedure as an
input parameter. Or, parse the XmlDocument client side and send
the data back normally.
This is sort of on target:
http://www.eggheadcafe.com/articles/...r_bulkload.asp
2005 Microsoft MVP C#
Robbe Morris
http://www.robbemorris.com
http://www.learncsharp.net/home/listings.aspx
"bob b" <bobb@.discussions.microsoft.com> wrote in message
news:0194A7E4-952D-4D7D-9CF1-10A9ED508178@.microsoft.com...
>I have a large well formed XML document which I recieved from a program. In
> order to quickly insert it into the database, I set up a table with an
> identity column and an ntext column. This works great for queuing the
> documents, but I need to retrieve the ntext column and use
> sp_xml_preparedocument on it so that I can shred the document and process
> the
> hierarchal data. I have not found a way to dynamically use the column from
> my
> table since you can not declare an ntext column as a local variable. Any
> ideas?
> --
> Bob b
|||Besides very ugly workarounds such as using nested EXEC or sp_OA stored
procs, you better use the SQLXML 3.0 XML Bulkload object client-side.
Note that SQL Server 2005 has fortunately no such limits anymore.
Best regards
Michael
"Robbe Morris [C# MVP]" <info@.turnkeytools.com> wrote in message
news:%23MfyA2fHFHA.3352@.TK2MSFTNGP10.phx.gbl...
> And you won't. I understand your issue and you certainly aren't
> the first to have this problem. You'll need to use ADO or ADO.NET
> to at least retrieve it and pass it back to a stored procedure as an
> input parameter. Or, parse the XmlDocument client side and send
> the data back normally.
> This is sort of on target:
> http://www.eggheadcafe.com/articles/...r_bulkload.asp
> --
> 2005 Microsoft MVP C#
> Robbe Morris
> http://www.robbemorris.com
> http://www.learncsharp.net/home/listings.aspx
>
> "bob b" <bobb@.discussions.microsoft.com> wrote in message
> news:0194A7E4-952D-4D7D-9CF1-10A9ED508178@.microsoft.com...
>
reusing temp table across different procs
Is there any way I can put the following consolidated statement into a proc
or function for reuse'
I have to use dynamic exec statement because filename,extension, or linked
server could change
<consolidated>
create table #t1(
c1 int,
c2 int,
c3 int,
c4 int,
c5 int
)
insert into #t1
EXEC('SELECT
c1,
c2,
c3,
c4,
c5
FROM '+@.mylinked_server + '...['+@.myfile_name + '#' + @.myfile_extension + ']
')
select * from #t1
</consolidated>
And then I could call this consolidate code in different procs and do some
thing like this
inside proc1...
insert into dbo.mytbl1(c2,c3)
select c2,c3 from <consolidated #t1>
inside proc2...
insert into dbo.mytbl2(c4,c5)
select c4,c5 from <consolidated #t1>
Please let me know if this needs more clarification and TIA..what about global temp tables? prefixed with a double pound sign
http://sqlservercode.blogspot.com/|||If you create the #temp table in the "parent" proc, then execute other procs
from the parent, you can.
USE model;
GO
CREATE PROCEDURE dbo.foo2
AS
BEGIN
SET NOCOUNT ON;
INSERT #foo SELECT 1 UNION ALL SELECT 2;
END;
GO
CREATE PROCEDURE dbo.foo1
AS
BEGIN
SET NOCOUNT ON;
CREATE TABLE #foo(a INT);
EXEC dbo.foo2;
SELECT * FROM #foo;
DROP TABLE #foo;
END;
GO
EXEC dbo.foo1;
GO
DROP PROCEDURE dbo.foo1, dbo.foo2;
GO
I've read your requirements and <consolidated> but I still don't quite
understand the actual goal and whether the above meets your requirements.
If you need to have independent stored procedures called separately and
still have access to the temp table, I think you are up the wrong tree.
http://www.sommarskog.se/share_data.html
"sqlster" <nospam@.nospam.com> wrote in message
news:4E26B406-7C49-4EE3-8A4B-AD5C3C91D04E@.microsoft.com...
> Is there any way I can put the following consolidated statement into a
> proc
> or function for reuse'
> I have to use dynamic exec statement because filename,extension, or linked
> server could change
> <consolidated>
> create table #t1(
> c1 int,
> c2 int,
> c3 int,
> c4 int,
> c5 int
> )
> insert into #t1
> EXEC('SELECT
> c1,
> c2,
> c3,
> c4,
> c5
> FROM '+@.mylinked_server + '...['+@.myfile_name + '#' + @.myfile_extension +
> ']')
> select * from #t1
> </consolidated>
> And then I could call this consolidate code in different procs and do some
> thing like this
> inside proc1...
> insert into dbo.mytbl1(c2,c3)
> select c2,c3 from <consolidated #t1>
>
> inside proc2...
> insert into dbo.mytbl2(c4,c5)
> select c4,c5 from <consolidated #t1>
> Please let me know if this needs more clarification and TIA..
>|||Sorry, I missed the cross-server bit.
I don't think that will be possible because the #temp table will probably
belong to a different session. Again, I don't think you can use #temp
tables for this.
While I'll admit I haven't tried this extensively, I don't think global temp
tables will help either, because the session won't necessarily be maintained
across servers, and the calling proc's session dictates the life of the
global temp table.
A
"sqlster" <nospam@.nospam.com> wrote in message
news:4E26B406-7C49-4EE3-8A4B-AD5C3C91D04E@.microsoft.com...
> Is there any way I can put the following consolidated statement into a
> proc
> or function for reuse'
> I have to use dynamic exec statement because filename,extension, or linked
> server could change
> <consolidated>
> create table #t1(
> c1 int,
> c2 int,
> c3 int,
> c4 int,
> c5 int
> )
> insert into #t1
> EXEC('SELECT
> c1,
> c2,
> c3,
> c4,
> c5
> FROM '+@.mylinked_server + '...['+@.myfile_name + '#' + @.myfile_extension +
> ']')
> select * from #t1
> </consolidated>
> And then I could call this consolidate code in different procs and do some
> thing like this
> inside proc1...
> insert into dbo.mytbl1(c2,c3)
> select c2,c3 from <consolidated #t1>
>
> inside proc2...
> insert into dbo.mytbl2(c4,c5)
> select c4,c5 from <consolidated #t1>
> Please let me know if this needs more clarification and TIA..
>|||I would like to avoid global temp tables
"SQL" wrote:
> what about global temp tables? prefixed with a double pound sign
> http://sqlservercode.blogspot.com/
>|||Aaron,
I would like to import data from a csv file into separate tables. To pull
rows into a table format in query analyzer, I am using linked server.
Temp table gives me staging table functionality. I would like to pull all
the data into a central location and then reuse that central location in
different procs to populate or process that data.
Please let me know if this clarifies the overall approach and thanks again
in advance.
"Aaron Bertrand [SQL Server MVP]" wrote:
> If you create the #temp table in the "parent" proc, then execute other pro
cs
> from the parent, you can.
> USE model;
> GO
> CREATE PROCEDURE dbo.foo2
> AS
> BEGIN
> SET NOCOUNT ON;
> INSERT #foo SELECT 1 UNION ALL SELECT 2;
> END;
> GO
> CREATE PROCEDURE dbo.foo1
> AS
> BEGIN
> SET NOCOUNT ON;
> CREATE TABLE #foo(a INT);
> EXEC dbo.foo2;
> SELECT * FROM #foo;
> DROP TABLE #foo;
> END;
> GO
> EXEC dbo.foo1;
> GO
> DROP PROCEDURE dbo.foo1, dbo.foo2;
> GO
>
> I've read your requirements and <consolidated> but I still don't quite
> understand the actual goal and whether the above meets your requirements.
> If you need to have independent stored procedures called separately and
> still have access to the temp table, I think you are up the wrong tree.
> http://www.sommarskog.se/share_data.html
>
>
> "sqlster" <nospam@.nospam.com> wrote in message
> news:4E26B406-7C49-4EE3-8A4B-AD5C3C91D04E@.microsoft.com...
>
>|||I hate procs that reference temporary tables created by other procs. 8-[
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uKthgGKEGHA.3920@.tk2msftngp13.phx.gbl...
> Sorry, I missed the cross-server bit.
> I don't think that will be possible because the #temp table will probably
> belong to a different session. Again, I don't think you can use #temp
> tables for this.
> While I'll admit I haven't tried this extensively, I don't think global
> temp tables will help either, because the session won't necessarily be
> maintained across servers, and the calling proc's session dictates the
> life of the global temp table.
> A
>
>
> "sqlster" <nospam@.nospam.com> wrote in message
> news:4E26B406-7C49-4EE3-8A4B-AD5C3C91D04E@.microsoft.com...
>|||i a different proc references a "temp" table, it's not really a temp
table.
but for this problem you're having, you can use tempdb. just drop and
create table as you please. for cross server, you can link the servers
together or use opendatasource. try not to do too much cross server
queries.|||>I hate procs that reference temporary tables created by other procs. 8-[
Ok. They can be useful for others, in spite of your hatred for them.
http://www.aspfaq.com/2248
Yes, full of bad practices, but useful nonetheless
PS I hate slow drivers in the left lane, but they still exist, and I still
have to deal with them on my commute.
A|||> but for this problem you're having, you can use tempdb. just drop and
> create table as you please.
Except if the proc is called by two sessions at the same time, they will
both try CREATE TABLE dbo.MyTable, oops, one of them wins, one of them
loses.
A
or function for reuse'
I have to use dynamic exec statement because filename,extension, or linked
server could change
<consolidated>
create table #t1(
c1 int,
c2 int,
c3 int,
c4 int,
c5 int
)
insert into #t1
EXEC('SELECT
c1,
c2,
c3,
c4,
c5
FROM '+@.mylinked_server + '...['+@.myfile_name + '#' + @.myfile_extension + ']
')
select * from #t1
</consolidated>
And then I could call this consolidate code in different procs and do some
thing like this
inside proc1...
insert into dbo.mytbl1(c2,c3)
select c2,c3 from <consolidated #t1>
inside proc2...
insert into dbo.mytbl2(c4,c5)
select c4,c5 from <consolidated #t1>
Please let me know if this needs more clarification and TIA..what about global temp tables? prefixed with a double pound sign
http://sqlservercode.blogspot.com/|||If you create the #temp table in the "parent" proc, then execute other procs
from the parent, you can.
USE model;
GO
CREATE PROCEDURE dbo.foo2
AS
BEGIN
SET NOCOUNT ON;
INSERT #foo SELECT 1 UNION ALL SELECT 2;
END;
GO
CREATE PROCEDURE dbo.foo1
AS
BEGIN
SET NOCOUNT ON;
CREATE TABLE #foo(a INT);
EXEC dbo.foo2;
SELECT * FROM #foo;
DROP TABLE #foo;
END;
GO
EXEC dbo.foo1;
GO
DROP PROCEDURE dbo.foo1, dbo.foo2;
GO
I've read your requirements and <consolidated> but I still don't quite
understand the actual goal and whether the above meets your requirements.
If you need to have independent stored procedures called separately and
still have access to the temp table, I think you are up the wrong tree.
http://www.sommarskog.se/share_data.html
"sqlster" <nospam@.nospam.com> wrote in message
news:4E26B406-7C49-4EE3-8A4B-AD5C3C91D04E@.microsoft.com...
> Is there any way I can put the following consolidated statement into a
> proc
> or function for reuse'
> I have to use dynamic exec statement because filename,extension, or linked
> server could change
> <consolidated>
> create table #t1(
> c1 int,
> c2 int,
> c3 int,
> c4 int,
> c5 int
> )
> insert into #t1
> EXEC('SELECT
> c1,
> c2,
> c3,
> c4,
> c5
> FROM '+@.mylinked_server + '...['+@.myfile_name + '#' + @.myfile_extension +
> ']')
> select * from #t1
> </consolidated>
> And then I could call this consolidate code in different procs and do some
> thing like this
> inside proc1...
> insert into dbo.mytbl1(c2,c3)
> select c2,c3 from <consolidated #t1>
>
> inside proc2...
> insert into dbo.mytbl2(c4,c5)
> select c4,c5 from <consolidated #t1>
> Please let me know if this needs more clarification and TIA..
>|||Sorry, I missed the cross-server bit.
I don't think that will be possible because the #temp table will probably
belong to a different session. Again, I don't think you can use #temp
tables for this.
While I'll admit I haven't tried this extensively, I don't think global temp
tables will help either, because the session won't necessarily be maintained
across servers, and the calling proc's session dictates the life of the
global temp table.
A
"sqlster" <nospam@.nospam.com> wrote in message
news:4E26B406-7C49-4EE3-8A4B-AD5C3C91D04E@.microsoft.com...
> Is there any way I can put the following consolidated statement into a
> proc
> or function for reuse'
> I have to use dynamic exec statement because filename,extension, or linked
> server could change
> <consolidated>
> create table #t1(
> c1 int,
> c2 int,
> c3 int,
> c4 int,
> c5 int
> )
> insert into #t1
> EXEC('SELECT
> c1,
> c2,
> c3,
> c4,
> c5
> FROM '+@.mylinked_server + '...['+@.myfile_name + '#' + @.myfile_extension +
> ']')
> select * from #t1
> </consolidated>
> And then I could call this consolidate code in different procs and do some
> thing like this
> inside proc1...
> insert into dbo.mytbl1(c2,c3)
> select c2,c3 from <consolidated #t1>
>
> inside proc2...
> insert into dbo.mytbl2(c4,c5)
> select c4,c5 from <consolidated #t1>
> Please let me know if this needs more clarification and TIA..
>|||I would like to avoid global temp tables
"SQL" wrote:
> what about global temp tables? prefixed with a double pound sign
> http://sqlservercode.blogspot.com/
>|||Aaron,
I would like to import data from a csv file into separate tables. To pull
rows into a table format in query analyzer, I am using linked server.
Temp table gives me staging table functionality. I would like to pull all
the data into a central location and then reuse that central location in
different procs to populate or process that data.
Please let me know if this clarifies the overall approach and thanks again
in advance.
"Aaron Bertrand [SQL Server MVP]" wrote:
> If you create the #temp table in the "parent" proc, then execute other pro
cs
> from the parent, you can.
> USE model;
> GO
> CREATE PROCEDURE dbo.foo2
> AS
> BEGIN
> SET NOCOUNT ON;
> INSERT #foo SELECT 1 UNION ALL SELECT 2;
> END;
> GO
> CREATE PROCEDURE dbo.foo1
> AS
> BEGIN
> SET NOCOUNT ON;
> CREATE TABLE #foo(a INT);
> EXEC dbo.foo2;
> SELECT * FROM #foo;
> DROP TABLE #foo;
> END;
> GO
> EXEC dbo.foo1;
> GO
> DROP PROCEDURE dbo.foo1, dbo.foo2;
> GO
>
> I've read your requirements and <consolidated> but I still don't quite
> understand the actual goal and whether the above meets your requirements.
> If you need to have independent stored procedures called separately and
> still have access to the temp table, I think you are up the wrong tree.
> http://www.sommarskog.se/share_data.html
>
>
> "sqlster" <nospam@.nospam.com> wrote in message
> news:4E26B406-7C49-4EE3-8A4B-AD5C3C91D04E@.microsoft.com...
>
>|||I hate procs that reference temporary tables created by other procs. 8-[
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uKthgGKEGHA.3920@.tk2msftngp13.phx.gbl...
> Sorry, I missed the cross-server bit.
> I don't think that will be possible because the #temp table will probably
> belong to a different session. Again, I don't think you can use #temp
> tables for this.
> While I'll admit I haven't tried this extensively, I don't think global
> temp tables will help either, because the session won't necessarily be
> maintained across servers, and the calling proc's session dictates the
> life of the global temp table.
> A
>
>
> "sqlster" <nospam@.nospam.com> wrote in message
> news:4E26B406-7C49-4EE3-8A4B-AD5C3C91D04E@.microsoft.com...
>|||i a different proc references a "temp" table, it's not really a temp
table.
but for this problem you're having, you can use tempdb. just drop and
create table as you please. for cross server, you can link the servers
together or use opendatasource. try not to do too much cross server
queries.|||>I hate procs that reference temporary tables created by other procs. 8-[
Ok. They can be useful for others, in spite of your hatred for them.
http://www.aspfaq.com/2248
Yes, full of bad practices, but useful nonetheless
PS I hate slow drivers in the left lane, but they still exist, and I still
have to deal with them on my commute.
A|||> but for this problem you're having, you can use tempdb. just drop and
> create table as you please.
Except if the proc is called by two sessions at the same time, they will
both try CREATE TABLE dbo.MyTable, oops, one of them wins, one of them
loses.
A
Reusing SQL 2005 Maintenance Plans
Does anybody know of a way to move maintenance plans from one Yukon server to
another? I don't want to have to manually set them up on each server. I'd
like to be able to script it, like in SQL 2000, if possible. Thanks in
advance! ~ Lindsey
You can import and export maintenance plans. Connect to
Integration Services on the server where the maintenance
plans are located. Go to Stored Packages -> MSDB ->
Maintenance Plans. From there you can right click on
Maintenance Plans and select Import. Or if you right click
on one of your maintenance plans, you can select import or
export.
-Sue
On Tue, 31 Jan 2006 12:00:23 -0800, Resquegal
<Resquegal@.discussions.microsoft.com> wrote:
>Does anybody know of a way to move maintenance plans from one Yukon server to
>another? I don't want to have to manually set them up on each server. I'd
>like to be able to script it, like in SQL 2000, if possible. Thanks in
>advance! ~ Lindsey
another? I don't want to have to manually set them up on each server. I'd
like to be able to script it, like in SQL 2000, if possible. Thanks in
advance! ~ Lindsey
You can import and export maintenance plans. Connect to
Integration Services on the server where the maintenance
plans are located. Go to Stored Packages -> MSDB ->
Maintenance Plans. From there you can right click on
Maintenance Plans and select Import. Or if you right click
on one of your maintenance plans, you can select import or
export.
-Sue
On Tue, 31 Jan 2006 12:00:23 -0800, Resquegal
<Resquegal@.discussions.microsoft.com> wrote:
>Does anybody know of a way to move maintenance plans from one Yukon server to
>another? I don't want to have to manually set them up on each server. I'd
>like to be able to script it, like in SQL 2000, if possible. Thanks in
>advance! ~ Lindsey
Reusing SQL 2005 Maintenance Plans
Does anybody know of a way to move maintenance plans from one Yukon server t
o
another? I don't want to have to manually set them up on each server. I'd
like to be able to script it, like in SQL 2000, if possible. Thanks in
advance! ~ LindseyYou can import and export maintenance plans. Connect to
Integration Services on the server where the maintenance
plans are located. Go to Stored Packages -> MSDB ->
Maintenance Plans. From there you can right click on
Maintenance Plans and select Import. Or if you right click
on one of your maintenance plans, you can select import or
export.
-Sue
On Tue, 31 Jan 2006 12:00:23 -0800, Resquegal
<Resquegal@.discussions.microsoft.com> wrote:
>Does anybody know of a way to move maintenance plans from one Yukon server
to
>another? I don't want to have to manually set them up on each server. I'd
>like to be able to script it, like in SQL 2000, if possible. Thanks in
>advance! ~ Lindsey
o
another? I don't want to have to manually set them up on each server. I'd
like to be able to script it, like in SQL 2000, if possible. Thanks in
advance! ~ LindseyYou can import and export maintenance plans. Connect to
Integration Services on the server where the maintenance
plans are located. Go to Stored Packages -> MSDB ->
Maintenance Plans. From there you can right click on
Maintenance Plans and select Import. Or if you right click
on one of your maintenance plans, you can select import or
export.
-Sue
On Tue, 31 Jan 2006 12:00:23 -0800, Resquegal
<Resquegal@.discussions.microsoft.com> wrote:
>Does anybody know of a way to move maintenance plans from one Yukon server
to
>another? I don't want to have to manually set them up on each server. I'd
>like to be able to script it, like in SQL 2000, if possible. Thanks in
>advance! ~ Lindsey
Reusing SQL 2005 Maintenance Plans
Does anybody know of a way to move maintenance plans from one Yukon server to
another? I don't want to have to manually set them up on each server. I'd
like to be able to script it, like in SQL 2000, if possible. Thanks in
advance! ~ LindseyYou can import and export maintenance plans. Connect to
Integration Services on the server where the maintenance
plans are located. Go to Stored Packages -> MSDB ->
Maintenance Plans. From there you can right click on
Maintenance Plans and select Import. Or if you right click
on one of your maintenance plans, you can select import or
export.
-Sue
On Tue, 31 Jan 2006 12:00:23 -0800, Resquegal
<Resquegal@.discussions.microsoft.com> wrote:
>Does anybody know of a way to move maintenance plans from one Yukon server to
>another? I don't want to have to manually set them up on each server. I'd
>like to be able to script it, like in SQL 2000, if possible. Thanks in
>advance! ~ Lindsey
another? I don't want to have to manually set them up on each server. I'd
like to be able to script it, like in SQL 2000, if possible. Thanks in
advance! ~ LindseyYou can import and export maintenance plans. Connect to
Integration Services on the server where the maintenance
plans are located. Go to Stored Packages -> MSDB ->
Maintenance Plans. From there you can right click on
Maintenance Plans and select Import. Or if you right click
on one of your maintenance plans, you can select import or
export.
-Sue
On Tue, 31 Jan 2006 12:00:23 -0800, Resquegal
<Resquegal@.discussions.microsoft.com> wrote:
>Does anybody know of a way to move maintenance plans from one Yukon server to
>another? I don't want to have to manually set them up on each server. I'd
>like to be able to script it, like in SQL 2000, if possible. Thanks in
>advance! ~ Lindsey
Reusing Report Parameters
Let's say I have 10 reports in a project where each report requires a
"drop down list" type of filter. We'll call this filter (report
parameter) "Product Type" and is a populated from a stored proc.
In order to put this Product type parameter on each report, do I need
to define the dataset which references the stored proc in each report?
Or is there a way to define the dataset one time, in some sort of
shared area, where it can be simply referenced by each of the reports.
Any suggestions on how to avoid duplicating prodcedure calls?
TIA
RobI too wish for the same feature. I would like to be able to define a global
dataset just like having a global data source defined. It is not feature of
this version. At least by having a stored procedure you have put the logic
in one place.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Rob Hoeting" <rhoeting@.hotmail.com> wrote in message
news:5a283851.0410190435.21012bf3@.posting.google.com...
> Let's say I have 10 reports in a project where each report requires a
> "drop down list" type of filter. We'll call this filter (report
> parameter) "Product Type" and is a populated from a stored proc.
> In order to put this Product type parameter on each report, do I need
> to define the dataset which references the stored proc in each report?
> Or is there a way to define the dataset one time, in some sort of
> shared area, where it can be simply referenced by each of the reports.
> Any suggestions on how to avoid duplicating prodcedure calls?
> TIA
> Rob|||Hello Rob,
Kind of. There are a few techniques I can think of that might help you and
partially solve the problem:-
The first is to view the Report in Code View - which will show you the RDL
for the report - you can then cut, copy and paste sections to the Toolbox -
and then when you are in your next report you can copy the sections back in.
I often work directly with the RDL because it is a lot quicker that messing
around with the mouse... but then I edit my web pages in Visual Notepad too!
The second thing you can do, is that you can create a Report Template.
Chapter 9 of the Hitchhiker's Guide to SQL Server 2000 Reporting Services,
shows how to create Report Templates and the various behind the scenes steps
that you take to set-up configuration files.
Peter Blackburn
Hitchhiker's Guide to SQL Server 2000 Reporting Services
www.sqlreportingservices.net
"Rob Hoeting" <rhoeting@.hotmail.com> wrote in message
news:5a283851.0410190435.21012bf3@.posting.google.com...
> Let's say I have 10 reports in a project where each report requires a
> "drop down list" type of filter. We'll call this filter (report
> parameter) "Product Type" and is a populated from a stored proc.
> In order to put this Product type parameter on each report, do I need
> to define the dataset which references the stored proc in each report?
> Or is there a way to define the dataset one time, in some sort of
> shared area, where it can be simply referenced by each of the reports.
> Any suggestions on how to avoid duplicating prodcedure calls?
> TIA
> Rob
"drop down list" type of filter. We'll call this filter (report
parameter) "Product Type" and is a populated from a stored proc.
In order to put this Product type parameter on each report, do I need
to define the dataset which references the stored proc in each report?
Or is there a way to define the dataset one time, in some sort of
shared area, where it can be simply referenced by each of the reports.
Any suggestions on how to avoid duplicating prodcedure calls?
TIA
RobI too wish for the same feature. I would like to be able to define a global
dataset just like having a global data source defined. It is not feature of
this version. At least by having a stored procedure you have put the logic
in one place.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Rob Hoeting" <rhoeting@.hotmail.com> wrote in message
news:5a283851.0410190435.21012bf3@.posting.google.com...
> Let's say I have 10 reports in a project where each report requires a
> "drop down list" type of filter. We'll call this filter (report
> parameter) "Product Type" and is a populated from a stored proc.
> In order to put this Product type parameter on each report, do I need
> to define the dataset which references the stored proc in each report?
> Or is there a way to define the dataset one time, in some sort of
> shared area, where it can be simply referenced by each of the reports.
> Any suggestions on how to avoid duplicating prodcedure calls?
> TIA
> Rob|||Hello Rob,
Kind of. There are a few techniques I can think of that might help you and
partially solve the problem:-
The first is to view the Report in Code View - which will show you the RDL
for the report - you can then cut, copy and paste sections to the Toolbox -
and then when you are in your next report you can copy the sections back in.
I often work directly with the RDL because it is a lot quicker that messing
around with the mouse... but then I edit my web pages in Visual Notepad too!
The second thing you can do, is that you can create a Report Template.
Chapter 9 of the Hitchhiker's Guide to SQL Server 2000 Reporting Services,
shows how to create Report Templates and the various behind the scenes steps
that you take to set-up configuration files.
Peter Blackburn
Hitchhiker's Guide to SQL Server 2000 Reporting Services
www.sqlreportingservices.net
"Rob Hoeting" <rhoeting@.hotmail.com> wrote in message
news:5a283851.0410190435.21012bf3@.posting.google.com...
> Let's say I have 10 reports in a project where each report requires a
> "drop down list" type of filter. We'll call this filter (report
> parameter) "Product Type" and is a populated from a stored proc.
> In order to put this Product type parameter on each report, do I need
> to define the dataset which references the stored proc in each report?
> Or is there a way to define the dataset one time, in some sort of
> shared area, where it can be simply referenced by each of the reports.
> Any suggestions on how to avoid duplicating prodcedure calls?
> TIA
> Rob
Reusing part of an existing measure group
Hello,
I have a large cube called Sales and inside that a measure group called SalesDetail. I'd like to reuse only a small slice of this cube in another cube, just 1 product subcategory in fact to meet that specific departments needs. They only want a subset of dimensions and only their data. Would perspectives allow filtering of data, i don't think so...
Rather than creating another view over the fact, and building another cube is there a way of reusing a slice of a cube?
So in the AventureWorks example i'd just like to specifically reuse "[Product].[Product Categories].[Subcategory].&[1]" subcategory..
Regards,
Ben
How about setting up a security role, with just that 1 allowed member on the Product Subcategory atttribute - visual totals can be enabled, so that users in this role only see totals contributed by that member?
SQL Server 2005 Books Online
Granting Custom Access to Dimension Data
...
Understanding the AllowedSet Property
The AllowedSet property uses a Multidimensional Expressions (MDX) expression to determine which attribute members can be viewed by the database role (the allowed set). The allowed set can include no (default), all, or some attribute members.
...Understanding the VisualTotals Property
The VisualTotals property indicates whether the aggregated cell values that are displayed are calculated according to all cell values or only according to the cell values that are visible to the database role.
|||I thought about using security, but will dimension security mean that these users cannot see all categories in another cube?
The intention is that this is a targeted cube for these users, and they won't be distracted by the whole set of data.
|||"will dimension security mean that these users cannot see all categories in another cube?" - not necessarily, since you can define dimension security just on cube dimensions. This might not be obvious from BOL; but the "Dimension" drop-down tree in the "Dimension Data" tab of the "Role" designer in BIDS shows something like:
-- Database
- Dimensions in Database
-- Cube(s)
- Dimensions in Cube
Answer Re: Security on role-playing dimensions
When you define dimension security, you can set it either on database dimension or on cube dimension. You need to choose one of the cube's role-playing dimensions and set dimension security only on it.
Mosha - http://www.mosha.com/msolap
|||thanks.. this is exactly what i am after... will try it out today.
Reusing parameters in a subreport.
Is it possible to reuse parameters from a primary query to trigger the query in a subreport?
My primary report does totals sold grouped by salesman code, and now we need grand totals per salesmanin the report footer.
So what I did was create a subreport that gives me exactly what I need. Now for my problem. We use the visual basic ActiveX control for our users to query the database for the report.
It was fine before since all the user had to do was enter a from_date, to_date, and a state parameter and the report would do it's thing. Now with the subreport it asks for...
from_date
to_date
state
from_date (SalesManTotals)
to_date (SalesManTotals)
state (SalesManTotals)
So our users are forced to enter the same parameters twice. Once from the VB interface, and then a second time from a Crystal Reports box requesting the same parameters for the subreport.
How can I tell the report to use the first set of parameters for both reports without being requested to enter them a second time for the subreport?Try linking those parameter fields to the field in the subreport that contains that data
My primary report does totals sold grouped by salesman code, and now we need grand totals per salesmanin the report footer.
So what I did was create a subreport that gives me exactly what I need. Now for my problem. We use the visual basic ActiveX control for our users to query the database for the report.
It was fine before since all the user had to do was enter a from_date, to_date, and a state parameter and the report would do it's thing. Now with the subreport it asks for...
from_date
to_date
state
from_date (SalesManTotals)
to_date (SalesManTotals)
state (SalesManTotals)
So our users are forced to enter the same parameters twice. Once from the VB interface, and then a second time from a Crystal Reports box requesting the same parameters for the subreport.
How can I tell the report to use the first set of parameters for both reports without being requested to enter them a second time for the subreport?Try linking those parameter fields to the field in the subreport that contains that data
Reusing Parameters
I have a loop that is doing multiple Stored Procedure calls (same call but
data is changing) and I get an error saying I parameter is already added.
It makes sense, but I am not sure how I move data into the parameter I have
already added.
The routine is:
objCmd.CommandText = "exec AddNewApplicantScreen
@.ApplicantID,@.PositionID,@.Version,@.Quest
ionUnique,@.Answer,@.AnswerTime"
with objCmd.Parameters
.Add("@.ApplicantID",SqlDbType.Int).value = session("ApplicantID")
.Add("@.PositionID",SqlDbType.Int).value = session("PositionID")
.Add("@.Version",SqlDbType.Int).value = 0
.Add("@.QuestionUnique",SqlDbType.Int).value = oQuestionUnique.text
.Add("@.Answer",SqlDbType.Int).value = AnswerBits
.Add("@.AnswerTime",SqlDbType.Int).value = 0
end with
objConn.Open
objCmd.ExecuteNonQuery
objConn.Close()
next
I assume I would take the "objCmd.CommandText = " and .add statements
outside of the loop and them somehow do something like
objCmd.Parameters(...) = something
Also, can I just put the objConn.Open and objConn.Close outside of the loop
and reexecute the objCmd.ExecuteNonQuery multiple times with closing and
reopening or do I need to do it as above?
Thanks,
TomHi Tom,
There are a couple of ways to to handle this...
One is that you can reset the objCmd with objCmd = New
System.Data.SqlClient.SqlCommand() as the first line in the loop...this will
necessitate you resetting the Connection object for the command.
Or Two before you start adding parameters after you invoke the
objCmd.Parameters.Clear() method. The Clear Method does exactly what it says
it clears/deletes all parameters in the Parameters Collection.
And yes you can put the Connection.Open() and Connection.Close() method
outside the loop.
Just objConn.Open() before your loop and invoke objConn.Close() after the
loop.
Hope this helps,
Chris
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:e9O%23uynAFHA.3416@.TK2MSFTNGP09.phx.gbl...
>I have a loop that is doing multiple Stored Procedure calls (same call but
>data is changing) and I get an error saying I parameter is already added.
> It makes sense, but I am not sure how I move data into the parameter I
> have already added.
> The routine is:
> objCmd.CommandText = "exec AddNewApplicantScreen
> @.ApplicantID,@.PositionID,@.Version,@.Quest
ionUnique,@.Answer,@.AnswerTime"
> with objCmd.Parameters
> .Add("@.ApplicantID",SqlDbType.Int).value = session("ApplicantID")
> .Add("@.PositionID",SqlDbType.Int).value = session("PositionID")
> .Add("@.Version",SqlDbType.Int).value = 0
> .Add("@.QuestionUnique",SqlDbType.Int).value = oQuestionUnique.text
> .Add("@.Answer",SqlDbType.Int).value = AnswerBits
> .Add("@.AnswerTime",SqlDbType.Int).value = 0
> end with
> objConn.Open
> objCmd.ExecuteNonQuery
> objConn.Close()
> next
> I assume I would take the "objCmd.CommandText = " and .add statements
> outside of the loop and them somehow do something like
> objCmd.Parameters(...) = something
> Also, can I just put the objConn.Open and objConn.Close outside of the
> loop and reexecute the objCmd.ExecuteNonQuery multiple times with closing
> and reopening or do I need to do it as above?
> Thanks,
> Tom
>|||"Chris Hayes" <cp.hayesATsbcglobal.net@.nospam.nospam> wrote in message
news:%23w8uaDoAFHA.3988@.TK2MSFTNGP11.phx.gbl...
> Hi Tom,
> There are a couple of ways to to handle this...
> One is that you can reset the objCmd with objCmd = New
> System.Data.SqlClient.SqlCommand() as the first line in the loop...this
> will necessitate you resetting the Connection object for the command.
>
Will I need to close and reopen the connection here, since I am resetting
the connection?
> Or Two before you start adding parameters after you invoke the
> objCmd.Parameters.Clear() method. The Clear Method does exactly what it
> says it clears/deletes all parameters in the Parameters Collection.
> And yes you can put the Connection.Open() and Connection.Close() method
> outside the loop.
> Just objConn.Open() before your loop and invoke objConn.Close() after the
> loop.
>
So each time I can do multiple objCmd.ExecuteNonQuery commands on the same
connection, what about DataAdapters or ExecuteReaders which return data.
Will it clear the old results for each execution or append the results?
> Hope this helps,
It does.
Thanks,
Tom
> Chris
>
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:e9O%23uynAFHA.3416@.TK2MSFTNGP09.phx.gbl...
>|||I've included a VB.NET code sample to illustrate.
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:erXs%23MoAFHA.2624@.TK2MSFTNGP11.phx.gbl...
> "Chris Hayes" <cp.hayesATsbcglobal.net@.nospam.nospam> wrote in message
> news:%23w8uaDoAFHA.3988@.TK2MSFTNGP11.phx.gbl...
> Will I need to close and reopen the connection here, since I am resetting
> the connection?
>
You will need to assign the connection only:
objCommand.Connection = conn
As long as you instantiated the connection object as its own object, you
will not need to reopen the connection .
> So each time I can do multiple objCmd.ExecuteNonQuery commands on the same
> connection, what about DataAdapters or ExecuteReaders which return data.
> Will it clear the old results for each execution or append the results?
>
From my experiences:
If you are using the DataAdapter to "Fill" a DataTable or a DataSet, it will
append to the DataTable or DataSet. If you are doing .ExecuteReader it will
not append as only one DataReader can be assigned at a time from the
.ExecuteReader method.
I hope this helps,
Chris
CODE SAMPLE:
Private Sub btnAppend_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnAppend.Click
Dim da As SqlClient.SqlDataAdapter
Dim conn As SqlClient.SqlConnection
Dim cmd As SqlClient.SqlCommand
Dim i As Int32
Dim ds As DataSet
ds = New DataSet
da = New SqlClient.SqlDataAdapter
conn = New SqlClient.SqlConnection("Server=(local);Initial
Catalog=Test;Integrated Security=SSPI;")
conn.Open()
For i = 1 To 5
cmd = New SqlClient.SqlCommand("procAppend", conn)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add("@.ID", SqlDbType.Int).Value = i
da.SelectCommand = cmd
da.Fill(ds)
cmd.Dispose()
Next
conn.Close()
conn.Dispose()
datagrid2.DataSource = ds
End Sub|||"Chris Hayes" <cp.hayesATsbcglobal.net@.nospam.nospam> wrote in message
news:%23PdW25oAFHA.3616@.TK2MSFTNGP11.phx.gbl...
> I've included a VB.NET code sample to illustrate.
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:erXs%23MoAFHA.2624@.TK2MSFTNGP11.phx.gbl...
> You will need to assign the connection only:
> objCommand.Connection = conn
> As long as you instantiated the connection object as its own object, you
> will not need to reopen the connection .
When I close the the page, will it close the connection at this point if I
haven't alrea done it?
>
> From my experiences:
> If you are using the DataAdapter to "Fill" a DataTable or a DataSet, it
> will append to the DataTable or DataSet. If you are doing .ExecuteReader
> it will not append as only one DataReader can be assigned at a time from
> the .ExecuteReader method.
> I hope this helps,
> Chris
> CODE SAMPLE:
> Private Sub btnAppend_Click(ByVal sender As System.Object, ByVal e As
> System.EventArgs) Handles btnAppend.Click
> Dim da As SqlClient.SqlDataAdapter
> Dim conn As SqlClient.SqlConnection
> Dim cmd As SqlClient.SqlCommand
> Dim i As Int32
> Dim ds As DataSet
> ds = New DataSet
> da = New SqlClient.SqlDataAdapter
> conn = New SqlClient.SqlConnection("Server=(local);Initial
> Catalog=Test;Integrated Security=SSPI;")
> conn.Open()
> For i = 1 To 5
> cmd = New SqlClient.SqlCommand("procAppend", conn)
> cmd.CommandType = CommandType.StoredProcedure
> cmd.Parameters.Add("@.ID", SqlDbType.Int).Value = i
> da.SelectCommand = cmd
> da.Fill(ds)
> cmd.Dispose()
> Next
> conn.Close()
> conn.Dispose()
> datagrid2.DataSource = ds
> End Sub
Thanks,
Tom|||Hi Tom,
While the Garbage Collector is supposed to help clean up memory, I don't
fully trust it.
I always close my connections and dispose of my objects after I have used
them. My philosophy is to only instantiate an object when needed and keep it
in memory for only as long as it needed and then when it is not to Dispose
of it. Of course the Dispose method only marks an object for the Garbage
Collector to deal with, but at least it's marked and the Garbage Collector
doesn't have to figure it out.
I normally wrap my dataaccess code in a try catch finally statement and in
the finally I check my dataaccess objects, if they are instantiated (not
equal to null/nothing) then I check the connection state if it's a
connection object, if it's not closed, I close it then dispose of it.
Chris
Here's a simple example of some clean up code I use:
Try
'logic
Catch ex As Exception
'handle the error
Finally
If Not da Is Nothing Then da.Dispose()
If Not cmd Is Nothing Then cmd.Dispose()
If Not conn Is Nothing Then
If conn.State = ConnectionState.Open Then conn.Close()
conn.Dispose()
End If
da = Nothing
cmd = Nothing
conn = Nothing
End Try
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:ONRsawxAFHA.2196@.TK2MSFTNGP14.phx.gbl...
> "Chris Hayes" <cp.hayesATsbcglobal.net@.nospam.nospam> wrote in message
> news:%23PdW25oAFHA.3616@.TK2MSFTNGP11.phx.gbl...
> When I close the the page, will it close the connection at this point if I
> haven't alrea done it?
>
> Thanks,
> Tom
>
data is changing) and I get an error saying I parameter is already added.
It makes sense, but I am not sure how I move data into the parameter I have
already added.
The routine is:
objCmd.CommandText = "exec AddNewApplicantScreen
@.ApplicantID,@.PositionID,@.Version,@.Quest
ionUnique,@.Answer,@.AnswerTime"
with objCmd.Parameters
.Add("@.ApplicantID",SqlDbType.Int).value = session("ApplicantID")
.Add("@.PositionID",SqlDbType.Int).value = session("PositionID")
.Add("@.Version",SqlDbType.Int).value = 0
.Add("@.QuestionUnique",SqlDbType.Int).value = oQuestionUnique.text
.Add("@.Answer",SqlDbType.Int).value = AnswerBits
.Add("@.AnswerTime",SqlDbType.Int).value = 0
end with
objConn.Open
objCmd.ExecuteNonQuery
objConn.Close()
next
I assume I would take the "objCmd.CommandText = " and .add statements
outside of the loop and them somehow do something like
objCmd.Parameters(...) = something
Also, can I just put the objConn.Open and objConn.Close outside of the loop
and reexecute the objCmd.ExecuteNonQuery multiple times with closing and
reopening or do I need to do it as above?
Thanks,
TomHi Tom,
There are a couple of ways to to handle this...
One is that you can reset the objCmd with objCmd = New
System.Data.SqlClient.SqlCommand() as the first line in the loop...this will
necessitate you resetting the Connection object for the command.
Or Two before you start adding parameters after you invoke the
objCmd.Parameters.Clear() method. The Clear Method does exactly what it says
it clears/deletes all parameters in the Parameters Collection.
And yes you can put the Connection.Open() and Connection.Close() method
outside the loop.
Just objConn.Open() before your loop and invoke objConn.Close() after the
loop.
Hope this helps,
Chris
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:e9O%23uynAFHA.3416@.TK2MSFTNGP09.phx.gbl...
>I have a loop that is doing multiple Stored Procedure calls (same call but
>data is changing) and I get an error saying I parameter is already added.
> It makes sense, but I am not sure how I move data into the parameter I
> have already added.
> The routine is:
> objCmd.CommandText = "exec AddNewApplicantScreen
> @.ApplicantID,@.PositionID,@.Version,@.Quest
ionUnique,@.Answer,@.AnswerTime"
> with objCmd.Parameters
> .Add("@.ApplicantID",SqlDbType.Int).value = session("ApplicantID")
> .Add("@.PositionID",SqlDbType.Int).value = session("PositionID")
> .Add("@.Version",SqlDbType.Int).value = 0
> .Add("@.QuestionUnique",SqlDbType.Int).value = oQuestionUnique.text
> .Add("@.Answer",SqlDbType.Int).value = AnswerBits
> .Add("@.AnswerTime",SqlDbType.Int).value = 0
> end with
> objConn.Open
> objCmd.ExecuteNonQuery
> objConn.Close()
> next
> I assume I would take the "objCmd.CommandText = " and .add statements
> outside of the loop and them somehow do something like
> objCmd.Parameters(...) = something
> Also, can I just put the objConn.Open and objConn.Close outside of the
> loop and reexecute the objCmd.ExecuteNonQuery multiple times with closing
> and reopening or do I need to do it as above?
> Thanks,
> Tom
>|||"Chris Hayes" <cp.hayesATsbcglobal.net@.nospam.nospam> wrote in message
news:%23w8uaDoAFHA.3988@.TK2MSFTNGP11.phx.gbl...
> Hi Tom,
> There are a couple of ways to to handle this...
> One is that you can reset the objCmd with objCmd = New
> System.Data.SqlClient.SqlCommand() as the first line in the loop...this
> will necessitate you resetting the Connection object for the command.
>
Will I need to close and reopen the connection here, since I am resetting
the connection?
> Or Two before you start adding parameters after you invoke the
> objCmd.Parameters.Clear() method. The Clear Method does exactly what it
> says it clears/deletes all parameters in the Parameters Collection.
> And yes you can put the Connection.Open() and Connection.Close() method
> outside the loop.
> Just objConn.Open() before your loop and invoke objConn.Close() after the
> loop.
>
So each time I can do multiple objCmd.ExecuteNonQuery commands on the same
connection, what about DataAdapters or ExecuteReaders which return data.
Will it clear the old results for each execution or append the results?
> Hope this helps,
It does.
Thanks,
Tom
> Chris
>
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:e9O%23uynAFHA.3416@.TK2MSFTNGP09.phx.gbl...
>|||I've included a VB.NET code sample to illustrate.
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:erXs%23MoAFHA.2624@.TK2MSFTNGP11.phx.gbl...
> "Chris Hayes" <cp.hayesATsbcglobal.net@.nospam.nospam> wrote in message
> news:%23w8uaDoAFHA.3988@.TK2MSFTNGP11.phx.gbl...
> Will I need to close and reopen the connection here, since I am resetting
> the connection?
>
You will need to assign the connection only:
objCommand.Connection = conn
As long as you instantiated the connection object as its own object, you
will not need to reopen the connection .
> So each time I can do multiple objCmd.ExecuteNonQuery commands on the same
> connection, what about DataAdapters or ExecuteReaders which return data.
> Will it clear the old results for each execution or append the results?
>
From my experiences:
If you are using the DataAdapter to "Fill" a DataTable or a DataSet, it will
append to the DataTable or DataSet. If you are doing .ExecuteReader it will
not append as only one DataReader can be assigned at a time from the
.ExecuteReader method.
I hope this helps,
Chris
CODE SAMPLE:
Private Sub btnAppend_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnAppend.Click
Dim da As SqlClient.SqlDataAdapter
Dim conn As SqlClient.SqlConnection
Dim cmd As SqlClient.SqlCommand
Dim i As Int32
Dim ds As DataSet
ds = New DataSet
da = New SqlClient.SqlDataAdapter
conn = New SqlClient.SqlConnection("Server=(local);Initial
Catalog=Test;Integrated Security=SSPI;")
conn.Open()
For i = 1 To 5
cmd = New SqlClient.SqlCommand("procAppend", conn)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add("@.ID", SqlDbType.Int).Value = i
da.SelectCommand = cmd
da.Fill(ds)
cmd.Dispose()
Next
conn.Close()
conn.Dispose()
datagrid2.DataSource = ds
End Sub|||"Chris Hayes" <cp.hayesATsbcglobal.net@.nospam.nospam> wrote in message
news:%23PdW25oAFHA.3616@.TK2MSFTNGP11.phx.gbl...
> I've included a VB.NET code sample to illustrate.
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:erXs%23MoAFHA.2624@.TK2MSFTNGP11.phx.gbl...
> You will need to assign the connection only:
> objCommand.Connection = conn
> As long as you instantiated the connection object as its own object, you
> will not need to reopen the connection .
When I close the the page, will it close the connection at this point if I
haven't alrea done it?
>
> From my experiences:
> If you are using the DataAdapter to "Fill" a DataTable or a DataSet, it
> will append to the DataTable or DataSet. If you are doing .ExecuteReader
> it will not append as only one DataReader can be assigned at a time from
> the .ExecuteReader method.
> I hope this helps,
> Chris
> CODE SAMPLE:
> Private Sub btnAppend_Click(ByVal sender As System.Object, ByVal e As
> System.EventArgs) Handles btnAppend.Click
> Dim da As SqlClient.SqlDataAdapter
> Dim conn As SqlClient.SqlConnection
> Dim cmd As SqlClient.SqlCommand
> Dim i As Int32
> Dim ds As DataSet
> ds = New DataSet
> da = New SqlClient.SqlDataAdapter
> conn = New SqlClient.SqlConnection("Server=(local);Initial
> Catalog=Test;Integrated Security=SSPI;")
> conn.Open()
> For i = 1 To 5
> cmd = New SqlClient.SqlCommand("procAppend", conn)
> cmd.CommandType = CommandType.StoredProcedure
> cmd.Parameters.Add("@.ID", SqlDbType.Int).Value = i
> da.SelectCommand = cmd
> da.Fill(ds)
> cmd.Dispose()
> Next
> conn.Close()
> conn.Dispose()
> datagrid2.DataSource = ds
> End Sub
Thanks,
Tom|||Hi Tom,
While the Garbage Collector is supposed to help clean up memory, I don't
fully trust it.
I always close my connections and dispose of my objects after I have used
them. My philosophy is to only instantiate an object when needed and keep it
in memory for only as long as it needed and then when it is not to Dispose
of it. Of course the Dispose method only marks an object for the Garbage
Collector to deal with, but at least it's marked and the Garbage Collector
doesn't have to figure it out.
I normally wrap my dataaccess code in a try catch finally statement and in
the finally I check my dataaccess objects, if they are instantiated (not
equal to null/nothing) then I check the connection state if it's a
connection object, if it's not closed, I close it then dispose of it.
Chris
Here's a simple example of some clean up code I use:
Try
'logic
Catch ex As Exception
'handle the error
Finally
If Not da Is Nothing Then da.Dispose()
If Not cmd Is Nothing Then cmd.Dispose()
If Not conn Is Nothing Then
If conn.State = ConnectionState.Open Then conn.Close()
conn.Dispose()
End If
da = Nothing
cmd = Nothing
conn = Nothing
End Try
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:ONRsawxAFHA.2196@.TK2MSFTNGP14.phx.gbl...
> "Chris Hayes" <cp.hayesATsbcglobal.net@.nospam.nospam> wrote in message
> news:%23PdW25oAFHA.3616@.TK2MSFTNGP11.phx.gbl...
> When I close the the page, will it close the connection at this point if I
> haven't alrea done it?
>
> Thanks,
> Tom
>
Reusing package configuration in child packages
I currently have multiple (parent and child) packages using the same config file. The config file has entries for connections to a number of systems. All of them are not used from the child packages. Hence, my child package throws an error when it tries to configure using the same config file because it can't find the extra connections in my connection collection.
Does anyone have any ideas on the best way to go about resolving this? Is multiple config files (one for each connection) the only way?
Sachin
I have found that one file per connection is ultimately the best way to go. You may be able to have more than that, if you are sure you will always use the same configurations at the same time, but that seems to be more useful when you have some variables, perhaps a couple of file paths used for all of your packages to do logging or check pointing, but you need to be careful of not falling into the same trap again of over grouping. Keep it at the lowest level you can, it is just more flexible I have found, and copes better with change as well. (Deleted Post?)
|||Darren
Looks like that might very well be the way to go then. I wonder if there is any way to include other config files into a single file, but that's for another day.
Thanks for the prompt reply.
Sachin
|||Nice idea, support for XML Inclusions (XInclude) would do it (http://www.w3.org/TR/xinclude/).
One for MS Connect I think - http://connect.microsoft.com
Reusing package configuration file across all packages in a solution?
I have 5 packages in a solution. |||
For 1st package, I add a package configuration file (xml) named common.dtsConfig containing only Database Connection configurations.
For the same package, I add another package configuration file names first.dtsConfig containing configurations specific to 1st package.
Now for 2nd package, when I reuse from existing package configuration (common.dtsConfg) with same name, it allows me to do that. I also create a package specific configuration file for 2nd package.
And so on for all 5 packages.
This works fine for development. If my database user/password changes, I edit onyl one file i.e. common.dtsConfig.
But, when I want to create the deployment utility, it fails by throwing error that "cannot copy common.dtsConfig from <src_directory> to .\bin\Deployment because it already exists". Due to this failure, I do not get the DTSInstall.EXE.
Surprisingly, this was working with June CTP and has failed with September CTP.
What should I do to reuse the package configuration file across all packages for deployment with September CTP?
thanks,
Nitesh
This is a known problem.
We fail to create a deployment manifest if 2 packages in the project share the same config file
Problem Description:
Build of a SSIS project with duplicate package config files reports the following –
Error 1 System.ApplicationException: Could not copy file "C:\VITAL\Prosjekter\Test\SmallProject\connectionLOG.dtsConfig" to the deployment utility output directory "C:\VITAL\Prosjekter\Test\SmallProject\bin\Deployment". > System.IO.IOException: The file 'C:\VITAL\Prosjekter\Test\SmallProject\bin\Deployment\connectionLOG.dtsConfig' already exists.
and as a consequence the deployment manifest file is not created and some of the package config files may not be copied to the deployment folder.
Workaround:
Create Proj.SSISDeploymentManifest manually using below template. Additionally verify that all config files and miscellaneous files (if you have it in your project) are present in deployment folder and copy in those that are missing.
<?xml version="1.0" ?>
- <DTSDeploymentManifest GeneratedBy="REDMOND\usr" GeneratedFromProjectName="Integration Services Project15" GeneratedDate="2005-09-20T16:17:42.4195337-07:00" AllowConfigurationChanges="true">
<Package>Package1.dtsx</Package>
<Package>Package.dtsx</Package>
<ConfigurationFile>cp.xml</ConfigurationFile>
<ConfigurationFile>cc.xml</ConfigurationFile>
</DTSDeploymentManifest>
Resolution:
We will fix the problem in SP1.
For meantime we consider releasing a QFE
I have 2 packages within a project that each have their own config file. When I build them and have the deployment package created, it doesn't put a DTSInstall.exe file in the directory.
I haven't created a deployment package since the June CTP, so I'm wondering if this has changed? I see some posts out there referring to DTUtil, should I be using that instead, or can I just copy the packages, change the config file settings and I'll be set?
Thanks in advance for your help.
-Chris
|||Just downloaded SP1. I dont see this fixed.|||I am experiencing the same problem even after the installation of the Cumulative Hotfix 2153.
Regards,
Yitzhak Khabinsky
Jamie, what is your word on the subject?
I know that you are using shared config files quite extensively.
Microsoft’s article (Article ID: 910419) mistakenly claims that it is fixed:
http://support.microsoft.com/?kbid=910419.
FIX: You receive an error message when you try to build a project for deployment and the project contains multiple packages that are configured to use a shared configuration file in SQL Server 2005 Integration Services
Regards,
Yitzhak
|||Yitzhak,
Up to now I have never used the deployment wizard so have not come across the problem.
-Jamie
|||
The error actually is happening during the Build process
It is very easy to reproduce.
BIDS project should have a couple of SSIS packages. Shared *.dtsConfig file should be explicitly added to the project. After that the config file shows up under the Miscellaneous node on the project tree. Right click on the SSIS project node and select Build option. The error shows up in the Output window.
Regards,
Yitzhak
|||Hi has anyone checked out the hotfix to see if the issue is resolved?|||I'm still having this issue as well. I installed service pack 1 a while ago. Is MS going to fix this soon? I use SSIS a lot and have found source control options VERY lacking. My company paid a lot of money to get TFS, and I'm not seeing a lot of value where SSIS is concerned.|||
I checked it out today. It did NOT work for me. I still get the same error.
I don't know if this would help; but until know I always have used .dtsx package files and never got to use the deployment wizard; and hat is because copying the .dtx and config files to the target destination is just that easy that I have not bothered to learn about wizard. I guess my suggestion here is not to use the deployment wizard.
|||Rafael Salas wrote:
I don't know if this would help; but until know I always have used .dtsx package files and never got to use the deployment wizard; and hat is because copying the .dtx and config files to the target destination is just that easy that I have not bothered to learn about wizard. I guess my suggestion here is not to use the deployment wizard.
I concur with Rafael. We're a *little* bit more scientific than that because we use WiX to build our deployment MSIs. I never go near the deployment wizard.
The beauty of WiX is that it is MSBuild compliant which means it can run in conjunction with TFS so all our MSIs are getting built using continuous integration (that's in answer to the guy that mentioned TFS).
Sorry, I know that's not much use to the people on this thread that are experiencing grief. It may be worth posing on another thread the question "is this fixed in SP2". The guy on this thread that replied from Microsoft (Nick Berezansky) no longer works on the SSIS team.
-Jamie
|||
Not fixed in SP2 - just tested it.
I realize this may not be an optimal solution, but have you tried removing the *.dtsconfig from the project (that is, don't include it in the misc. files). When I do this, the project builds with no errors, and the deployment utility is created with the *.dtsconfig file in the Deployment folder. The file can still be under source control, through the source code explorer with TFS, or VSS, or whatever tool you are using.
In general, though, I'd agree with Jamie and Rafael on this. I haven't used the deployment wizard since the first iteration of my first SSIS project.
Reusing package configuration file across all packages in a solution?
I have 5 packages in a solution. |||
For 1st package, I add a package configuration file (xml) named common.dtsConfig containing only Database Connection configurations.
For the same package, I add another package configuration file names first.dtsConfig containing configurations specific to 1st package.
Now for 2nd package, when I reuse from existing package configuration (common.dtsConfg) with same name, it allows me to do that. I also create a package specific configuration file for 2nd package.
And so on for all 5 packages.
This works fine for development. If my database user/password changes, I edit onyl one file i.e. common.dtsConfig.
But, when I want to create the deployment utility, it fails by throwing error that "cannot copy common.dtsConfig from <src_directory> to .\bin\Deployment because it already exists". Due to this failure, I do not get the DTSInstall.EXE.
Surprisingly, this was working with June CTP and has failed with September CTP.
What should I do to reuse the package configuration file across all packages for deployment with September CTP?
thanks,
Nitesh
This is a known problem.
We fail to create a deployment manifest if 2 packages in the project share the same config file
Problem Description:
Build of a SSIS project with duplicate package config files reports the following –
Error 1 System.ApplicationException: Could not copy file "C:\VITAL\Prosjekter\Test\SmallProject\connectionLOG.dtsConfig" to the deployment utility output directory "C:\VITAL\Prosjekter\Test\SmallProject\bin\Deployment". > System.IO.IOException: The file 'C:\VITAL\Prosjekter\Test\SmallProject\bin\Deployment\connectionLOG.dtsConfig' already exists.
and as a consequence the deployment manifest file is not created and some of the package config files may not be copied to the deployment folder.
Workaround:
Create Proj.SSISDeploymentManifest manually using below template. Additionally verify that all config files and miscellaneous files (if you have it in your project) are present in deployment folder and copy in those that are missing.
<?xml version="1.0" ?>
- <DTSDeploymentManifest GeneratedBy="REDMOND\usr" GeneratedFromProjectName="Integration Services Project15" GeneratedDate="2005-09-20T16:17:42.4195337-07:00" AllowConfigurationChanges="true">
<Package>Package1.dtsx</Package>
<Package>Package.dtsx</Package>
<ConfigurationFile>cp.xml</ConfigurationFile>
<ConfigurationFile>cc.xml</ConfigurationFile>
</DTSDeploymentManifest>
Resolution:
We will fix the problem in SP1.
For meantime we consider releasing a QFE
I have 2 packages within a project that each have their own config file. When I build them and have the deployment package created, it doesn't put a DTSInstall.exe file in the directory.
I haven't created a deployment package since the June CTP, so I'm wondering if this has changed? I see some posts out there referring to DTUtil, should I be using that instead, or can I just copy the packages, change the config file settings and I'll be set?
Thanks in advance for your help.
-Chris
|||Just downloaded SP1. I dont see this fixed.|||I am experiencing the same problem even after the installation of the Cumulative Hotfix 2153.
Regards,
Yitzhak Khabinsky
Jamie, what is your word on the subject?
I know that you are using shared config files quite extensively.
Microsoft’s article (Article ID: 910419) mistakenly claims that it is fixed:
http://support.microsoft.com/?kbid=910419.
FIX: You receive an error message when you try to build a project for deployment and the project contains multiple packages that are configured to use a shared configuration file in SQL Server 2005 Integration Services
Regards,
Yitzhak
|||Yitzhak,
Up to now I have never used the deployment wizard so have not come across the problem.
-Jamie
|||The error actually is happening during the Build process
It is very easy to reproduce.
BIDS project should have a couple of SSIS packages. Shared *.dtsConfig file should be explicitly added to the project. After that the config file shows up under the Miscellaneous node on the project tree. Right click on the SSIS project node and select Build option. The error shows up in the Output window.
Regards,
Yitzhak
|||Hi has anyone checked out the hotfix to see if the issue is resolved?|||I'm still having this issue as well. I installed service pack 1 a while ago. Is MS going to fix this soon? I use SSIS a lot and have found source control options VERY lacking. My company paid a lot of money to get TFS, and I'm not seeing a lot of value where SSIS is concerned.|||
I checked it out today. It did NOT work for me. I still get the same error.
I don't know if this would help; but until know I always have used .dtsx package files and never got to use the deployment wizard; and hat is because copying the .dtx and config files to the target destination is just that easy that I have not bothered to learn about wizard. I guess my suggestion here is not to use the deployment wizard.
|||Rafael Salas wrote:
I don't know if this would help; but until know I always have used .dtsx package files and never got to use the deployment wizard; and hat is because copying the .dtx and config files to the target destination is just that easy that I have not bothered to learn about wizard. I guess my suggestion here is not to use the deployment wizard.
I concur with Rafael. We're a *little* bit more scientific than that because we use WiX to build our deployment MSIs. I never go near the deployment wizard.
The beauty of WiX is that it is MSBuild compliant which means it can run in conjunction with TFS so all our MSIs are getting built using continuous integration (that's in answer to the guy that mentioned TFS).
Sorry, I know that's not much use to the people on this thread that are experiencing grief. It may be worth posing on another thread the question "is this fixed in SP2". The guy on this thread that replied from Microsoft (Nick Berezansky) no longer works on the SSIS team.
-Jamie
|||Not fixed in SP2 - just tested it.
I realize this may not be an optimal solution, but have you tried removing the *.dtsconfig from the project (that is, don't include it in the misc. files). When I do this, the project builds with no errors, and the deployment utility is created with the *.dtsconfig file in the Deployment folder. The file can still be under source control, through the source code explorer with TFS, or VSS, or whatever tool you are using.
In general, though, I'd agree with Jamie and Rafael on this. I haven't used the deployment wizard since the first iteration of my first SSIS project.
Reusing package configuration file across all packages in a solution?
I have 5 packages in a solution. |||
For 1st package, I add a package configuration file (xml) named common.dtsConfig containing only Database Connection configurations.
For the same package, I add another package configuration file names first.dtsConfig containing configurations specific to 1st package.
Now for 2nd package, when I reuse from existing package configuration (common.dtsConfg) with same name, it allows me to do that. I also create a package specific configuration file for 2nd package.
And so on for all 5 packages.
This works fine for development. If my database user/password changes, I edit onyl one file i.e. common.dtsConfig.
But, when I want to create the deployment utility, it fails by throwing error that "cannot copy common.dtsConfig from <src_directory> to .\bin\Deployment because it already exists". Due to this failure, I do not get the DTSInstall.EXE.
Surprisingly, this was working with June CTP and has failed with September CTP.
What should I do to reuse the package configuration file across all packages for deployment with September CTP?
thanks,
Nitesh
This is a known problem.
We fail to create a deployment manifest if 2 packages in the project share the same config file
Problem Description:
Build of a SSIS project with duplicate package config files reports the following –
Error 1 System.ApplicationException: Could not copy file "C:\VITAL\Prosjekter\Test\SmallProject\connectionLOG.dtsConfig" to the deployment utility output directory "C:\VITAL\Prosjekter\Test\SmallProject\bin\Deployment". > System.IO.IOException: The file 'C:\VITAL\Prosjekter\Test\SmallProject\bin\Deployment\connectionLOG.dtsConfig' already exists.
and as a consequence the deployment manifest file is not created and some of the package config files may not be copied to the deployment folder.
Workaround:
Create Proj.SSISDeploymentManifest manually using below template. Additionally verify that all config files and miscellaneous files (if you have it in your project) are present in deployment folder and copy in those that are missing.
<?xml version="1.0" ?>
- <DTSDeploymentManifest GeneratedBy="REDMOND\usr" GeneratedFromProjectName="Integration Services Project15" GeneratedDate="2005-09-20T16:17:42.4195337-07:00" AllowConfigurationChanges="true">
<Package>Package1.dtsx</Package>
<Package>Package.dtsx</Package>
<ConfigurationFile>cp.xml</ConfigurationFile>
<ConfigurationFile>cc.xml</ConfigurationFile>
</DTSDeploymentManifest>
Resolution:
We will fix the problem in SP1.
For meantime we consider releasing a QFE
I have 2 packages within a project that each have their own config file. When I build them and have the deployment package created, it doesn't put a DTSInstall.exe file in the directory.
I haven't created a deployment package since the June CTP, so I'm wondering if this has changed? I see some posts out there referring to DTUtil, should I be using that instead, or can I just copy the packages, change the config file settings and I'll be set?
Thanks in advance for your help.
-Chris
|||Just downloaded SP1. I dont see this fixed.|||I am experiencing the same problem even after the installation of the Cumulative Hotfix 2153.
Regards,
Yitzhak Khabinsky
Jamie, what is your word on the subject?
I know that you are using shared config files quite extensively.
Microsoft’s article (Article ID: 910419) mistakenly claims that it is fixed:
http://support.microsoft.com/?kbid=910419.
FIX: You receive an error message when you try to build a project for deployment and the project contains multiple packages that are configured to use a shared configuration file in SQL Server 2005 Integration Services
Regards,
Yitzhak
|||Yitzhak,
Up to now I have never used the deployment wizard so have not come across the problem.
-Jamie
|||The error actually is happening during the Build process
It is very easy to reproduce.
BIDS project should have a couple of SSIS packages. Shared *.dtsConfig file should be explicitly added to the project. After that the config file shows up under the Miscellaneous node on the project tree. Right click on the SSIS project node and select Build option. The error shows up in the Output window.
Regards,
Yitzhak
|||Hi has anyone checked out the hotfix to see if the issue is resolved?|||I'm still having this issue as well. I installed service pack 1 a while ago. Is MS going to fix this soon? I use SSIS a lot and have found source control options VERY lacking. My company paid a lot of money to get TFS, and I'm not seeing a lot of value where SSIS is concerned.|||
I checked it out today. It did NOT work for me. I still get the same error.
I don't know if this would help; but until know I always have used .dtsx package files and never got to use the deployment wizard; and hat is because copying the .dtx and config files to the target destination is just that easy that I have not bothered to learn about wizard. I guess my suggestion here is not to use the deployment wizard.
|||I don't know if this would help; but until know I always have used .dtsx package files and never got to use the deployment wizard; and hat is because copying the .dtx and config files to the target destination is just that easy that I have not bothered to learn about wizard. I guess my suggestion here is not to use the deployment wizard.
I concur with Rafael. We're a *little* bit more scientific than that because we use WiX to build our deployment MSIs. I never go near the deployment wizard.
The beauty of WiX is that it is MSBuild compliant which means it can run in conjunction with TFS so all our MSIs are getting built using continuous integration (that's in answer to the guy that mentioned TFS).
Sorry, I know that's not much use to the people on this thread that are experiencing grief. It may be worth posing on another thread the question "is this fixed in SP2". The guy on this thread that replied from Microsoft (Nick Berezansky) no longer works on the SSIS team.
-Jamie
|||Not fixed in SP2 - just tested it.
I realize this may not be an optimal solution, but have you tried removing the *.dtsconfig from the project (that is, don't include it in the misc. files). When I do this, the project builds with no errors, and the deployment utility is created with the *.dtsconfig file in the Deployment folder. The file can still be under source control, through the source code explorer with TFS, or VSS, or whatever tool you are using.
In general, though, I'd agree with Jamie and Rafael on this. I haven't used the deployment wizard since the first iteration of my first SSIS project.
Reusing package configuration file across all packages in a solution?
I have 5 packages in a solution. |||
For 1st package, I add a package configuration file (xml) named common.dtsConfig containing only Database Connection configurations.
For the same package, I add another package configuration file names first.dtsConfig containing configurations specific to 1st package.
Now for 2nd package, when I reuse from existing package configuration (common.dtsConfg) with same name, it allows me to do that. I also create a package specific configuration file for 2nd package.
And so on for all 5 packages.
This works fine for development. If my database user/password changes, I edit onyl one file i.e. common.dtsConfig.
But, when I want to create the deployment utility, it fails by throwing error that "cannot copy common.dtsConfig from <src_directory> to .\bin\Deployment because it already exists". Due to this failure, I do not get the DTSInstall.EXE.
Surprisingly, this was working with June CTP and has failed with September CTP.
What should I do to reuse the package configuration file across all packages for deployment with September CTP?
thanks,
Nitesh
This is a known problem.
We fail to create a deployment manifest if 2 packages in the project share the same config file
Problem Description:
Build of a SSIS project with duplicate package config files reports the following –
Error 1 System.ApplicationException: Could not copy file "C:\VITAL\Prosjekter\Test\SmallProject\connectionLOG.dtsConfig" to the deployment utility output directory "C:\VITAL\Prosjekter\Test\SmallProject\bin\Deployment". > System.IO.IOException: The file 'C:\VITAL\Prosjekter\Test\SmallProject\bin\Deployment\connectionLOG.dtsConfig' already exists.
and as a consequence the deployment manifest file is not created and some of the package config files may not be copied to the deployment folder.
Workaround:
Create Proj.SSISDeploymentManifest manually using below template. Additionally verify that all config files and miscellaneous files (if you have it in your project) are present in deployment folder and copy in those that are missing.
<?xml version="1.0" ?>
- <DTSDeploymentManifest GeneratedBy="REDMOND\usr" GeneratedFromProjectName="Integration Services Project15" GeneratedDate="2005-09-20T16:17:42.4195337-07:00" AllowConfigurationChanges="true">
<Package>Package1.dtsx</Package>
<Package>Package.dtsx</Package>
<ConfigurationFile>cp.xml</ConfigurationFile>
<ConfigurationFile>cc.xml</ConfigurationFile>
</DTSDeploymentManifest>
Resolution:
We will fix the problem in SP1.
For meantime we consider releasing a QFE
I have 2 packages within a project that each have their own config file. When I build them and have the deployment package created, it doesn't put a DTSInstall.exe file in the directory.
I haven't created a deployment package since the June CTP, so I'm wondering if this has changed? I see some posts out there referring to DTUtil, should I be using that instead, or can I just copy the packages, change the config file settings and I'll be set?
Thanks in advance for your help.
-Chris
|||Just downloaded SP1. I dont see this fixed.|||I am experiencing the same problem even after the installation of the Cumulative Hotfix 2153.
Regards,
Yitzhak Khabinsky
Jamie, what is your word on the subject?
I know that you are using shared config files quite extensively.
Microsoft’s article (Article ID: 910419) mistakenly claims that it is fixed:
http://support.microsoft.com/?kbid=910419.
FIX: You receive an error message when you try to build a project for deployment and the project contains multiple packages that are configured to use a shared configuration file in SQL Server 2005 Integration Services
Regards,
Yitzhak
|||Yitzhak,
Up to now I have never used the deployment wizard so have not come across the problem.
-Jamie
|||The error actually is happening during the Build process
It is very easy to reproduce.
BIDS project should have a couple of SSIS packages. Shared *.dtsConfig file should be explicitly added to the project. After that the config file shows up under the Miscellaneous node on the project tree. Right click on the SSIS project node and select Build option. The error shows up in the Output window.
Regards,
Yitzhak
|||Hi has anyone checked out the hotfix to see if the issue is resolved?|||I'm still having this issue as well. I installed service pack 1 a while ago. Is MS going to fix this soon? I use SSIS a lot and have found source control options VERY lacking. My company paid a lot of money to get TFS, and I'm not seeing a lot of value where SSIS is concerned.|||
I checked it out today. It did NOT work for me. I still get the same error.
I don't know if this would help; but until know I always have used .dtsx package files and never got to use the deployment wizard; and hat is because copying the .dtx and config files to the target destination is just that easy that I have not bothered to learn about wizard. I guess my suggestion here is not to use the deployment wizard.
|||I don't know if this would help; but until know I always have used .dtsx package files and never got to use the deployment wizard; and hat is because copying the .dtx and config files to the target destination is just that easy that I have not bothered to learn about wizard. I guess my suggestion here is not to use the deployment wizard.
I concur with Rafael. We're a *little* bit more scientific than that because we use WiX to build our deployment MSIs. I never go near the deployment wizard.
The beauty of WiX is that it is MSBuild compliant which means it can run in conjunction with TFS so all our MSIs are getting built using continuous integration (that's in answer to the guy that mentioned TFS).
Sorry, I know that's not much use to the people on this thread that are experiencing grief. It may be worth posing on another thread the question "is this fixed in SP2". The guy on this thread that replied from Microsoft (Nick Berezansky) no longer works on the SSIS team.
-Jamie
|||Not fixed in SP2 - just tested it.
I realize this may not be an optimal solution, but have you tried removing the *.dtsconfig from the project (that is, don't include it in the misc. files). When I do this, the project builds with no errors, and the deployment utility is created with the *.dtsconfig file in the Deployment folder. The file can still be under source control, through the source code explorer with TFS, or VSS, or whatever tool you are using.
In general, though, I'd agree with Jamie and Rafael on this. I haven't used the deployment wizard since the first iteration of my first SSIS project.
Reusing package configuration file across all packages in a solution?
I have 5 packages in a solution. |||
For 1st package, I add a package configuration file (xml) named common.dtsConfig containing only Database Connection configurations.
For the same package, I add another package configuration file names first.dtsConfig containing configurations specific to 1st package.
Now for 2nd package, when I reuse from existing package configuration (common.dtsConfg) with same name, it allows me to do that. I also create a package specific configuration file for 2nd package.
And so on for all 5 packages.
This works fine for development. If my database user/password changes, I edit onyl one file i.e. common.dtsConfig.
But, when I want to create the deployment utility, it fails by throwing error that "cannot copy common.dtsConfig from <src_directory> to .\bin\Deployment because it already exists". Due to this failure, I do not get the DTSInstall.EXE.
Surprisingly, this was working with June CTP and has failed with September CTP.
What should I do to reuse the package configuration file across all packages for deployment with September CTP?
thanks,
Nitesh
This is a known problem.
We fail to create a deployment manifest if 2 packages in the project share the same config file
Problem Description:
Build of a SSIS project with duplicate package config files reports the following –
Error 1 System.ApplicationException: Could not copy file "C:\VITAL\Prosjekter\Test\SmallProject\connectionLOG.dtsConfig" to the deployment utility output directory "C:\VITAL\Prosjekter\Test\SmallProject\bin\Deployment". > System.IO.IOException: The file 'C:\VITAL\Prosjekter\Test\SmallProject\bin\Deployment\connectionLOG.dtsConfig' already exists.
and as a consequence the deployment manifest file is not created and some of the package config files may not be copied to the deployment folder.
Workaround:
Create Proj.SSISDeploymentManifest manually using below template. Additionally verify that all config files and miscellaneous files (if you have it in your project) are present in deployment folder and copy in those that are missing.
<?xml version="1.0" ?>
- <DTSDeploymentManifest GeneratedBy="REDMOND\usr" GeneratedFromProjectName="Integration Services Project15" GeneratedDate="2005-09-20T16:17:42.4195337-07:00" AllowConfigurationChanges="true">
<Package>Package1.dtsx</Package>
<Package>Package.dtsx</Package>
<ConfigurationFile>cp.xml</ConfigurationFile>
<ConfigurationFile>cc.xml</ConfigurationFile>
</DTSDeploymentManifest>
Resolution:
We will fix the problem in SP1.
For meantime we consider releasing a QFE
I have 2 packages within a project that each have their own config file. When I build them and have the deployment package created, it doesn't put a DTSInstall.exe file in the directory.
I haven't created a deployment package since the June CTP, so I'm wondering if this has changed? I see some posts out there referring to DTUtil, should I be using that instead, or can I just copy the packages, change the config file settings and I'll be set?
Thanks in advance for your help.
-Chris
|||Just downloaded SP1. I dont see this fixed.|||I am experiencing the same problem even after the installation of the Cumulative Hotfix 2153.
Regards,
Yitzhak Khabinsky
Jamie, what is your word on the subject?
I know that you are using shared config files quite extensively.
Microsoft’s article (Article ID: 910419) mistakenly claims that it is fixed:
http://support.microsoft.com/?kbid=910419.
FIX: You receive an error message when you try to build a project for deployment and the project contains multiple packages that are configured to use a shared configuration file in SQL Server 2005 Integration Services
Regards,
Yitzhak
|||Yitzhak,
Up to now I have never used the deployment wizard so have not come across the problem.
-Jamie
|||
The error actually is happening during the Build process
It is very easy to reproduce.
BIDS project should have a couple of SSIS packages. Shared *.dtsConfig file should be explicitly added to the project. After that the config file shows up under the Miscellaneous node on the project tree. Right click on the SSIS project node and select Build option. The error shows up in the Output window.
Regards,
Yitzhak
|||Hi has anyone checked out the hotfix to see if the issue is resolved?|||I'm still having this issue as well. I installed service pack 1 a while ago. Is MS going to fix this soon? I use SSIS a lot and have found source control options VERY lacking. My company paid a lot of money to get TFS, and I'm not seeing a lot of value where SSIS is concerned.|||
I checked it out today. It did NOT work for me. I still get the same error.
I don't know if this would help; but until know I always have used .dtsx package files and never got to use the deployment wizard; and hat is because copying the .dtx and config files to the target destination is just that easy that I have not bothered to learn about wizard. I guess my suggestion here is not to use the deployment wizard.
|||I don't know if this would help; but until know I always have used .dtsx package files and never got to use the deployment wizard; and hat is because copying the .dtx and config files to the target destination is just that easy that I have not bothered to learn about wizard. I guess my suggestion here is not to use the deployment wizard.
I concur with Rafael. We're a *little* bit more scientific than that because we use WiX to build our deployment MSIs. I never go near the deployment wizard.
The beauty of WiX is that it is MSBuild compliant which means it can run in conjunction with TFS so all our MSIs are getting built using continuous integration (that's in answer to the guy that mentioned TFS).
Sorry, I know that's not much use to the people on this thread that are experiencing grief. It may be worth posing on another thread the question "is this fixed in SP2". The guy on this thread that replied from Microsoft (Nick Berezansky) no longer works on the SSIS team.
-Jamie
|||
Not fixed in SP2 - just tested it.
I realize this may not be an optimal solution, but have you tried removing the *.dtsconfig from the project (that is, don't include it in the misc. files). When I do this, the project builds with no errors, and the deployment utility is created with the *.dtsconfig file in the Deployment folder. The file can still be under source control, through the source code explorer with TFS, or VSS, or whatever tool you are using.
In general, though, I'd agree with Jamie and Rafael on this. I haven't used the deployment wizard since the first iteration of my first SSIS project.
Reusing Execution Plan
I've created a stored procedure and tried to compare the
execution plans by passing different parameters to it. The
execution plan in all the cases is displayed as same. But
how can i make sure that SQL Server is using the same
execution plan from memory each time i execute my stored
procedure. I want to make sure its not recreating the
execution plan each time i call the stored procedure.
Appreciate if anyone can help me to trace that.
Lots of thanks in advance.Look at masters syscacheobjects for your proc
select * from master.syscacheobjects where objid = object_id('myproc')
If you are plan sharing, you should see 2 entries, 1 executable plan and
1compiled plan...
"Sudhakar Koolla" <anonymous@.discussions.microsoft.com> wrote in message
news:093801c3b81d$492771b0$a301280a@.phx.gbl...
> I've created a stored procedure and tried to compare the
> execution plans by passing different parameters to it. The
> execution plan in all the cases is displayed as same. But
> how can i make sure that SQL Server is using the same
> execution plan from memory each time i execute my stored
> procedure. I want to make sure its not recreating the
> execution plan each time i call the stored procedure.
> Appreciate if anyone can help me to trace that.
> Lots of thanks in advance.
execution plans by passing different parameters to it. The
execution plan in all the cases is displayed as same. But
how can i make sure that SQL Server is using the same
execution plan from memory each time i execute my stored
procedure. I want to make sure its not recreating the
execution plan each time i call the stored procedure.
Appreciate if anyone can help me to trace that.
Lots of thanks in advance.Look at masters syscacheobjects for your proc
select * from master.syscacheobjects where objid = object_id('myproc')
If you are plan sharing, you should see 2 entries, 1 executable plan and
1compiled plan...
"Sudhakar Koolla" <anonymous@.discussions.microsoft.com> wrote in message
news:093801c3b81d$492771b0$a301280a@.phx.gbl...
> I've created a stored procedure and tried to compare the
> execution plans by passing different parameters to it. The
> execution plan in all the cases is displayed as same. But
> how can i make sure that SQL Server is using the same
> execution plan from memory each time i execute my stored
> procedure. I want to make sure its not recreating the
> execution plan each time i call the stored procedure.
> Appreciate if anyone can help me to trace that.
> Lots of thanks in advance.
Friday, March 9, 2012
Reusing Execution Plan
I've created a stored procedure and tried to compare the execution plans by passing different parameters to it. The execution plan in all the cases is displayed as same. But how can i make sure that SQL Server is using the same execution plan from memory each time i execute my stored procedure. I want to make sure its not recreating the execution plan each time i call the stored procedure
Appreciate if anyone can help me to trace that
Lots of thanks in advance.See my other reply. Please don't multipost.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Sudhakar Koolla" <sudhakar.koolla@.wipro.com> wrote in message
news:465AB931-50FE-4F2D-9A7F-143A69B7CEE5@.microsoft.com...
> I've created a stored procedure and tried to compare the execution plans by passing different
parameters to it. The execution plan in all the cases is displayed as same. But how can i make sure
that SQL Server is using the same execution plan from memory each time i execute my stored
procedure. I want to make sure its not recreating the execution plan each time i call the stored
procedure.
> Appreciate if anyone can help me to trace that.
> Lots of thanks in advance.
Appreciate if anyone can help me to trace that
Lots of thanks in advance.See my other reply. Please don't multipost.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Sudhakar Koolla" <sudhakar.koolla@.wipro.com> wrote in message
news:465AB931-50FE-4F2D-9A7F-143A69B7CEE5@.microsoft.com...
> I've created a stored procedure and tried to compare the execution plans by passing different
parameters to it. The execution plan in all the cases is displayed as same. But how can i make sure
that SQL Server is using the same execution plan from memory each time i execute my stored
procedure. I want to make sure its not recreating the execution plan each time i call the stored
procedure.
> Appreciate if anyone can help me to trace that.
> Lots of thanks in advance.
Reusing dialogs causes blocking
I was looking at a means of reusing dialogs.
The attempt I tried was looking up an existing dialog in the conversation_endpoints.
However on doing a scale test I would that the non blocking I was hoping wasn't happening. Even through I was giving each spid a new dialog by using a conversation_group_id related to the spid. I found that the following SQL was blocked by a transaction that contains a begin dialog. This suggests the locking on conversation_endpoints is too excessive.
select top 1 conversation_handle
from sys.conversation_endpoints ce
join sys.services s on s.service_id = ce.service_id
join sys.service_contracts c on c.service_contract_id = ce.service_contract_id
where s.name = 'jobStats'
and ce.far_service = 'jobStats'
and (ce.far_broker_instance = @.targetBroker OR @.targetBroker = 'CURRENT DATABASE')
and ce.state IN ('SO','CO')
and ce.is_initiator = 1
and (ce.conversation_group_id = @.conversation_group_id )--or @.conversation_group_id is null)
and c.name = @.contractName
Even a R/O scan will block behind an X lock in read-commited mode. Use the READPAST hint to skip locked records.
HTH,
~ Remus
Reusing connections
Every time my asp.net app needs to open a connection, it tries to establish a new connection with the mssql server. I′ve already set the max pool size property in the connection string. After that, my app raises an "time out"error saying it couldn′t obtain a connection from the pool. The problem is that I have a lot of iddle connections. With the Enterprise Manager I can see the status of the connections. They′re all the same "awaiting command". How can I reuse this connections? I know that the connection string must be the same for all connections and it is. I′ve set it in the web.config file. If I remove the max pool size property from the connection string I get a lot, I mean A LOT of connections with the sql server. Any ideas?
You should be ensuring that all connections get closed using a try/catch/finally block. Also, take a look at the following articles:
Tuning Up ADO.NET Connection Pooling With ASP.NET Applications
ASP.NET and SQL Server Performance Tips
The .NET Connection Pool Lifeguard
HTH
Subscribe to:
Posts (Atom)