Best way to copy database: structure

Showing posts with label structure. Show all posts

Tuesday, March 20, 2012

Big query on different servers

Hi.
From my dektop PC I started Query Analyser on 12 servers and used it to
execute a Stored Procedure (same database structure on all servers). On eight
it worked, on four it did not, giving the message:
ODBC: Msg 0, Level 19,. State 1
SqlDumpExceptionHandler: Process nnn generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
Connection broken
Can someone tell me what is going on and how to get round this? The Stored
Procedure is like this:
CREATE TABLE... (no problem here)
INSERT INTO... several thousand rows generated by reading a million plus
rows
from a different database on the same server
(this sometimes works, sometimes it fails at
this point)
UPDATE... all the rows from the INSERT, again with data generated by
reading a
million plus rows from a different database on the same
server
(if it gets beyond this point it works
correctly)
UPDATE... as the first update
UPDATE... as the first update
similar UPDATE... INSERT... UPDATE... UPDATE... UPDATE... UPDATE
A valid run takes about 10-15 minutes. The last one I tried failed during
the first UPDATE after 26 seconds.
TIA,
Peter.Hi
SQL version and service pack level (select @.@.version)?
Check that you are on the latest SP's and possible hotfixes.
Regards
Mike
"PeterHyssett" wrote:
> Hi.
> From my dektop PC I started Query Analyser on 12 servers and used it to
> execute a Stored Procedure (same database structure on all servers). On eight
> it worked, on four it did not, giving the message:
> ODBC: Msg 0, Level 19,. State 1
> SqlDumpExceptionHandler: Process nnn generated fatal exception c0000005
> EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
> Connection broken
> Can someone tell me what is going on and how to get round this? The Stored
> Procedure is like this:
> CREATE TABLE... (no problem here)
> INSERT INTO... several thousand rows generated by reading a million plus
> rows
> from a different database on the same server
> (this sometimes works, sometimes it fails at
> this point)
> UPDATE... all the rows from the INSERT, again with data generated by
> reading a
> million plus rows from a different database on the same
> server
> (if it gets beyond this point it works
> correctly)
> UPDATE... as the first update
> UPDATE... as the first update
> similar UPDATE... INSERT... UPDATE... UPDATE... UPDATE... UPDATE
> A valid run takes about 10-15 minutes. The last one I tried failed during
> the first UPDATE after 26 seconds.
> TIA,
> Peter.|||Thanks - the servers which gave trouble had no service packs applied - the
ones that worked were mostly at SP3.
Regards,
Peter.
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> SQL version and service pack level (select @.@.version)?
> Check that you are on the latest SP's and possible hotfixes.
> Regards
> Mike
> "PeterHyssett" wrote:
> > Hi.
> > From my dektop PC I started Query Analyser on 12 servers and used it to
> > execute a Stored Procedure (same database structure on all servers). On eight
> > it worked, on four it did not, giving the message:
> >
> > ODBC: Msg 0, Level 19,. State 1
> > SqlDumpExceptionHandler: Process nnn generated fatal exception c0000005
> > EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
> >
> > Connection broken
> >
> > Can someone tell me what is going on and how to get round this? The Stored
> > Procedure is like this:
> >
> > CREATE TABLE... (no problem here)
> > INSERT INTO... several thousand rows generated by reading a million plus
> > rows
> > from a different database on the same server
> > (this sometimes works, sometimes it fails at
> > this point)
> > UPDATE... all the rows from the INSERT, again with data generated by
> > reading a
> > million plus rows from a different database on the same
> > server
> > (if it gets beyond this point it works
> > correctly)
> > UPDATE... as the first update
> > UPDATE... as the first update
> > similar UPDATE... INSERT... UPDATE... UPDATE... UPDATE... UPDATE
> >
> > A valid run takes about 10-15 minutes. The last one I tried failed during
> > the first UPDATE after 26 seconds.
> >
> > TIA,
> >
> > Peter.|||On the problem server, step through or simplify the code to find where it
breaks.
Jeff
"PeterHyssett" <PeterHyssett@.discussions.microsoft.com> wrote in message
news:A0CF95FA-3109-4A96-95D4-5ACFD7B4305F@.microsoft.com...
> Thanks - the servers which gave trouble had no service packs applied - the
> ones that worked were mostly at SP3.
> Regards,
> Peter.
> "Mike Epprecht (SQL MVP)" wrote:
> > Hi
> >
> > SQL version and service pack level (select @.@.version)?
> >
> > Check that you are on the latest SP's and possible hotfixes.
> >
> > Regards
> > Mike
> >
> > "PeterHyssett" wrote:
> >
> > > Hi.
> > > From my dektop PC I started Query Analyser on 12 servers and used it
to
> > > execute a Stored Procedure (same database structure on all servers).
On eight
> > > it worked, on four it did not, giving the message:
> > >
> > > ODBC: Msg 0, Level 19,. State 1
> > > SqlDumpExceptionHandler: Process nnn generated fatal exception
c0000005
> > > EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
> > >
> > > Connection broken
> > >
> > > Can someone tell me what is going on and how to get round this? The
Stored
> > > Procedure is like this:
> > >
> > > CREATE TABLE... (no problem here)
> > > INSERT INTO... several thousand rows generated by reading a million
plus
> > > rows
> > > from a different database on the same server
> > > (this sometimes works, sometimes it
fails at
> > > this point)
> > > UPDATE... all the rows from the INSERT, again with data generated
by
> > > reading a
> > > million plus rows from a different database on the
same
> > > server
> > > (if it gets beyond this point it
works
> > > correctly)
> > > UPDATE... as the first update
> > > UPDATE... as the first update
> > > similar UPDATE... INSERT... UPDATE... UPDATE... UPDATE... UPDATE
> > >
> > > A valid run takes about 10-15 minutes. The last one I tried failed
during
> > > the first UPDATE after 26 seconds.
> > >
> > > TIA,
> > >
> > > Peter.|||> Hi.
> From my dektop PC I started Query Analyser on 12 servers and used it to
> execute a Stored Procedure (same database structure on all servers). On eight
> it worked, on four it did not, giving the message:
> ODBC: Msg 0, Level 19,. State 1
> SqlDumpExceptionHandler: Process nnn generated fatal exception c0000005
> EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
> Connection broken
> Can someone tell me what is going on and how to get round this? The Stored
> Procedure is like this:
> CREATE TABLE... (no problem here)
> INSERT INTO... several thousand rows generated by reading a million plus
> rows
> from a different database on the same server
> (this sometimes works, sometimes it fails at
> this point)
> UPDATE... all the rows from the INSERT, again with data generated by
> reading a
> million plus rows from a different database on the same
> server
> (if it gets beyond this point it works
> correctly)
> UPDATE... as the first update
> UPDATE... as the first update
> similar UPDATE... INSERT... UPDATE... UPDATE... UPDATE... UPDATE
> A valid run takes about 10-15 minutes. The last one I tried failed during
> the first UPDATE after 26 seconds.
> TIA,
> Peter.
User submitted from AEWNET (http://www.aewnet.com/)

Sunday, March 11, 2012

BI studio

hi,

I have to connect to use a cube that is residing on different machine in order to develop a mining structure using MS associations rules algorithm.

Can anybody tell me the connection type (for Data Source) I should use so that I can use the same to create Data Source View OR Mining Structure?

I tried using OLAP, Analysis Service 9.0, OLAP 8.0, Data Mining - connection types. I could see only data source created using Data Mining connection while i was creating data source view.

Please help me as to which method I should use to connect cube on remote server to create a mining structure.

Thank you.

Aashutosh

SQL Server Data Mining does not allow you to create mining structure on cubes residing on remote servers or in another database on the same server - the source cube for an OLAP mining structure needs to be in the same database as the mining structure.

|||The only option would be to use DMX statements to create and train a mining structure (CREATE MINING STRUCTURE and INSERT INTO statements)|||

Hi,

thank you for your help.

I will try using DMX. One more question:

If I connect to remote server (which has the cube) using SQL server Management Studio to create a mining structure, will it modify the database on the remote server?

Aashutosh

|||

Creating a mining structure on the remote server is modifying the database as you are creating an object.

If you don't want to permanently change the database, you can use CREATE SESSION MINING STRUCTURE.... but you first have to enable session objects on that server (available in server properties).

If you are creating a mining structure on a server and populating from another analysis services database through OPENQUERY, then you do not modify the source database.

|||

Thank you for your help.

Will the same thing happen in case of relational database, i.e.

If I use relational database on remote server and create mining structure using BI development studio installed on local machine, will it modify the database on the remote server?

thank you once again.

Aashutosh Magdum

BI studio

hi,

I have to connect to use a cube that is residing on different machine in order to develop a mining structure using MS associations rules algorithm.

Can anybody tell me the connection type (for Data Source) I should use so that I can use the same to create Data Source View OR Mining Structure?

I tried using OLAP, Analysis Service 9.0, OLAP 8.0, Data Mining - connection types. I could see only data source created using Data Mining connection while i was creating data source view.

Please help me as to which method I should use to connect cube on remote server to create a mining structure.

Thank you.

Aashutosh

|||The only option would be to use DMX statements to create and train a mining structure (CREATE MINING STRUCTURE and INSERT INTO statements)|||

Hi,

thank you for your help.

I will try using DMX. One more question:

If I connect to remote server (which has the cube) using SQL server Management Studio to create a mining structure, will it modify the database on the remote server?

Aashutosh

|||

Creating a mining structure on the remote server is modifying the database as you are creating an object.

If you don't want to permanently change the database, you can use CREATE SESSION MINING STRUCTURE.... but you first have to enable session objects on that server (available in server properties).

If you are creating a mining structure on a server and populating from another analysis services database through OPENQUERY, then you do not modify the source database.

|||

Thank you for your help.

Will the same thing happen in case of relational database, i.e.

If I use relational database on remote server and create mining structure using BI development studio installed on local machine, will it modify the database on the remote server?

thank you once again.

Aashutosh Magdum

Saturday, February 25, 2012

Better Way To Handle The Code?

Hi experts,
I have a table which stores data in multi-rows basis for a particular
record. The structure of the table is as exhibit:
CREATE TABLE [dbo].[Table_Header] (
[Rec_Id] [numeric](18, 0) NOT NULL ,
[Param_Name] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Param_Value] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
) ON [PRIMARY]
GO
Example data is shown as follows:
Rec_Id Param_Name Param_Value
1 Product TV
1 Category Electrical
2 Product Telephone
2 Category Electrical
3 Product TV
3 Category Electrical
In case I would like to filter out those records (Rec_Id) where Product = TV
and Category = Electrical, one way I can think of using T-SQL is:
Select A.Rec_Id From Table_Header A, Table_Header B
Where A.Rec_Id = B.Rec_Id And
A.Param_Name = 'Product' And
A.Param_Value = 'TV' And
B.Param_Name = 'Category' And
B.Param_Value = 'Electrical'
The Select statement above returns me results: 1 and 3. But it will grow to
be quite complicated if I have many parameters to filter in my select
statement. Any better way to handle this coding?
ThanksLBT
What is a PK on your table?
Do you have any indexes defined on the table?
"LBT" <LBT@.discussions.microsoft.com> wrote in message
news:A1E1B426-7986-4535-BF4B-BDD35532F273@.microsoft.com...
> Hi experts,
> I have a table which stores data in multi-rows basis for a particular
> record. The structure of the table is as exhibit:
> CREATE TABLE [dbo].[Table_Header] (
> [Rec_Id] [numeric](18, 0) NOT NULL ,
> [Param_Name] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
> [Param_Value] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> ) ON [PRIMARY]
> GO
> Example data is shown as follows:
> Rec_Id Param_Name Param_Value
> 1 Product TV
> 1 Category Electrical
> 2 Product Telephone
> 2 Category Electrical
> 3 Product TV
> 3 Category Electrical
> In case I would like to filter out those records (Rec_Id) where Product =
TV
> and Category = Electrical, one way I can think of using T-SQL is:
> Select A.Rec_Id From Table_Header A, Table_Header B
> Where A.Rec_Id = B.Rec_Id And
> A.Param_Name = 'Product' And
> A.Param_Value = 'TV' And
> B.Param_Name = 'Category' And
> B.Param_Value = 'Electrical'
> The Select statement above returns me results: 1 and 3. But it will grow
to
> be quite complicated if I have many parameters to filter in my select
> statement. Any better way to handle this coding?
> Thanks
>
>
>
>|||Hi
You can normalize your table.
You can have 2 tables here Product and Category
Product:
RecID | Product_Name
1 | TV
2 | Telephone
3 | TV
Category:
RecID | Category_Name
1 | Electrical
2 | Electrical
3 | Electrical
Based on the req. u can query as
Select Category.Rec_id
FROM Category
INNER JOIN Product ON Product.Rec_id = Category.Rec_id
AND Product_Name IN ('TV')
WHERE Category_Name IN ('Electrical')
This will solve the purpose
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"LBT" wrote:

> Hi experts,
> I have a table which stores data in multi-rows basis for a particular
> record. The structure of the table is as exhibit:
> CREATE TABLE [dbo].[Table_Header] (
> [Rec_Id] [numeric](18, 0) NOT NULL ,
> [Param_Name] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [Param_Value] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> ) ON [PRIMARY]
> GO
> Example data is shown as follows:
> Rec_Id Param_Name Param_Value
> 1 Product TV
> 1 Category Electrical
> 2 Product Telephone
> 2 Category Electrical
> 3 Product TV
> 3 Category Electrical
> In case I would like to filter out those records (Rec_Id) where Product =
TV
> and Category = Electrical, one way I can think of using T-SQL is:
> Select A.Rec_Id From Table_Header A, Table_Header B
> Where A.Rec_Id = B.Rec_Id And
> A.Param_Name = 'Product' And
> A.Param_Value = 'TV' And
> B.Param_Name = 'Category' And
> B.Param_Value = 'Electrical'
> The Select statement above returns me results: 1 and 3. But it will grow t
o
> be quite complicated if I have many parameters to filter in my select
> statement. Any better way to handle this coding?
> Thanks
>
>
>
>|||> I have a table which stores data in multi-rows basis for a particular

> record
Why? That's usually a really poor design. There are better ways to
represent types and subtypes. Example:
CREATE TABLE Products (sku INTEGER PRIMARY KEY, product_type_code
CHAR(2) NOT NULL CHECK (product_type_code IN ('EL','EN','SP')),
productname VARCHAR(40) NOT NULL UNIQUE, UNIQUE (sku,product_type_code)
/* Columns common to all products */)
CREATE TABLE EntertainmentProducts (sku INTEGER PRIMARY KEY,
product_type_code CHAR(2) DEFAULT 'EN' NOT NULL CHECK
(product_type_code='EN'), medium CHAR(3) NOT NULL CHECK (medium IN
('DVD','VHS','CD')), FOREIGN KEY (sku, product_type_code) REFERENCES
Products (sku, product_type_code) /* Columns specific to this
subtype... */)
CREATE TABLE ElectronicProducts (sku INTEGER PRIMARY KEY,
product_type_code CHAR(2) DEFAULT 'EL' NOT NULL CHECK
(product_type_code='EL'), FOREIGN KEY (sku, product_type_code)
REFERENCES Products (sku, product_type_code) /* Columns specific to
this subtype */)
CREATE TABLE SportsProducts (sku INTEGER PRIMARY KEY, product_type_code
CHAR(2) DEFAULT 'SP' NOT NULL CHECK (product_type_code='SP'), FOREIGN
KEY (sku, product_type_code) REFERENCES Products (sku,
product_type_code) /* Columns specific to this subtype */)

> it will grow to
> be quite complicated if I have many parameters to filter in my select

> statement.
Correct! That's why it's better to go for a more normalized approach.
David Portas
SQL Server MVP
--|||The PKs are Rec_Id and Param_Name
"Uri Dimant" wrote:

> LBT
> What is a PK on your table?
> Do you have any indexes defined on the table?
> "LBT" <LBT@.discussions.microsoft.com> wrote in message
> news:A1E1B426-7986-4535-BF4B-BDD35532F273@.microsoft.com...
> ,
> TV
> to
>
>|||Thanks for the suggestion. In fact, there will be a lot of parameters in my
actual application where I think normalization is not my exact solution
(sorry that not to make my question clear in previous post). And because of
the norm that the parameters list will change from time to time, I need to
store data in the mentioned manner (or I will have to change table structure
everytime parameters change).
"Chandra" wrote:
> Hi
> You can normalize your table.
> You can have 2 tables here Product and Category
> Product:
> RecID | Product_Name
> 1 | TV
> 2 | Telephone
> 3 | TV
> Category:
> RecID | Category_Name
> 1 | Electrical
> 2 | Electrical
> 3 | Electrical
> Based on the req. u can query as
> Select Category.Rec_id
> FROM Category
> INNER JOIN Product ON Product.Rec_id = Category.Rec_id
> AND Product_Name IN ('TV')
> WHERE Category_Name IN ('Electrical')
> This will solve the purpose
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://groups.msn.com/SQLResource/
> ---
>
> "LBT" wrote:
>|||1. What objects are being stored in this table? It looks like a generic
System table - holding attribute bnames and values, for a database. I would
recommend you get a good book on Relational Data modeling, and take a quick
look at it. You might consider normalizing the data structure a bit..
Instead of having a Param_Name column in a table, you might want to create a
Products Table and a Categorys Table, but I don;t know what exactly you are
doing so it's hard to tell..
"LBT" wrote:

> Hi experts,
> I have a table which stores data in multi-rows basis for a particular
> record. The structure of the table is as exhibit:
> CREATE TABLE [dbo].[Table_Header] (
> [Rec_Id] [numeric](18, 0) NOT NULL ,
> [Param_Name] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [Param_Value] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> ) ON [PRIMARY]
> GO
> Example data is shown as follows:
> Rec_Id Param_Name Param_Value
> 1 Product TV
> 1 Category Electrical
> 2 Product Telephone
> 2 Category Electrical
> 3 Product TV
> 3 Category Electrical
> In case I would like to filter out those records (Rec_Id) where Product =
TV
> and Category = Electrical, one way I can think of using T-SQL is:
> Select A.Rec_Id From Table_Header A, Table_Header B
> Where A.Rec_Id = B.Rec_Id And
> A.Param_Name = 'Product' And
> A.Param_Value = 'TV' And
> B.Param_Name = 'Category' And
> B.Param_Value = 'Electrical'
> The Select statement above returns me results: 1 and 3. But it will grow t
o
> be quite complicated if I have many parameters to filter in my select
> statement. Any better way to handle this coding?
> Thanks
>
>
>
>|||I think I have provided a confusing example. My mistake :)
Actually the table structure is designed in such a way to store variety of
data structure sent in by user. Say for example, user might send in "Rec_Id
[1], Product [TV], Category [Electrical]", "Rec_Id [2], Product [Telephone],
Category [Electrical]", etc.
At later time, user can send in data structure with new add-in parameter(s)
- "Rec_Id [3], Product [TV], Category [Electrical], Power [1000kW], Color
[Black]". The challenge is I cannot change the table structure (this is the
dynamic solution requested by customer) everytime new parameter is being
introduced. With the table design, I can simply store "Power" into Param_Nam
e
and "1000kW" into Param_Value (no adding of column to table structure is
allowed).
So with this table design, the only way I can perform records filtering (the
filtering parameters however will be fixed which are Product and Category in
this case) is as stated in my post? I just wonder whether there is any bette
r
way to perform the query.
In fact, I have to create and configure few reference/definition tables so
that my application will be dynamic enough to cater for this requirement. Bu
t
it will be lengthy to explain the entire situation.
Thanks
"CBretana" wrote:
> 1. What objects are being stored in this table? It looks like a generic
> System table - holding attribute bnames and values, for a database. I wou
ld
> recommend you get a good book on Relational Data modeling, and take a qui
ck
> look at it. You might consider normalizing the data structure a bit..
> Instead of having a Param_Name column in a table, you might want to create
a
> Products Table and a Categorys Table, but I don;t know what exactly you a
re
> doing so it's hard to tell..
> "LBT" wrote:
>|||In a way, you are in the dilemma which often results from trying to achieve
conflicting objectives...
The entire concept of Relational Databases is, in one sense (I need to be
careful here) designed to allow you to "Structure" your data so that it
closely matches the real world data objects or abstractions which represent
those objects, in order to allow the kind of data "processing" (like
arbitrary filtering, sorting, etc. that you are trying to accomplish. Old
style (pre-Relational) Database systems were comparitively inadeguate at
these sorts of things, *because* the data was not stored in a structured
relational way.
But you are *Trying* to store your data in an unstructured way to allow as
much flexibility in what kind of data is allowed in to the database. These
two objectives are fundamentally inconsistent, and that is one reason why yo
u
are experiencing difficulties
"LBT" wrote:
> I think I have provided a confusing example. My mistake :)
> Actually the table structure is designed in such a way to store variety of
> data structure sent in by user. Say for example, user might send in "Rec_I
d
> [1], Product [TV], Category [Electrical]", "Rec_Id [2], Product [Telephone],
> Category [Electrical]", etc.
> At later time, user can send in data structure with new add-in parameter(s
)
> - "Rec_Id [3], Product [TV], Category [Electrical], Power [1000kW], Color
> [Black]". The challenge is I cannot change the table structure (this is the
> dynamic solution requested by customer) everytime new parameter is being
> introduced. With the table design, I can simply store "Power" into Param_N
ame
> and "1000kW" into Param_Value (no adding of column to table structure is
> allowed).
> So with this table design, the only way I can perform records filtering (t
he
> filtering parameters however will be fixed which are Product and Category
in
> this case) is as stated in my post? I just wonder whether there is any bet
ter
> way to perform the query.
> In fact, I have to create and configure few reference/definition tables so
> that my application will be dynamic enough to cater for this requirement.
But
> it will be lengthy to explain the entire situation.
> Thanks
> "CBretana" wrote:
>|||If the customer DEMANDS a design like this then they should understand
and accept its weaknesses with regard to data integrity, performance
and increased complexity. However, I would always discuss the
alternatives with them first. Change control for adding new attributes
should be their friend not their enemy. If they are s

ing a zero
maintenance solution then tell them to forget it - of course no such
thing exists - or at least when a legacy application reaches that stage
of maturity then it's probably already in need of replacement.
The reason that proper change control is required is that users
generally make very poor database designers. If you allow users to
decide the format of business data you will lose any integrity and
future value therein. Do you really expect users to comprehenend and
analyze functional dependencies and standardise and conform attributes
in the system? If they were capable of that then they must be database
pros in which case they wouldn't need this kind of kludge. Corporate
data is too important to be left to users to manage.
David Portas
SQL Server MVP
--

Friday, February 24, 2012

Best XML structure for import into SQL 2005

Hi,

I am writing a contingency plan for incase our SQL Server (2005) goes down. Basically when our applicatino is about to write data to the SQL Server it checks the connection. If the connection passes it outputs to SQL, if it fails it generates an XML document detailing the data so that the data can be imported later.

Each XML document needs to define which database and which table needs to be updated/inserted.

So the SQL needs to be able to read a number of XML documents and update the relevant tables according dependent on the data held within the XML; some examples:

<Output>
<Record>
<Database>DW</Database>
<Table>dbo.tblActivity</Table>
<DateTime>11-05-2007 10:03:33.099</DateTime>
<Data>
<strActivityPK>9353|HCR008</strActivityPK>
<strActivityCode>HCR008</strActivityCode>
<strActivityDescription>HCR Complete</strActivityDescription>
<datTarget>2007-05-18</datTarget>
<datActual />
<intMatterIntCode>9353</intMatterIntCode>
<intRecurringActivityFlag>0</intRecurringActivityFlag>
<intETLFlag>0</intETLFlag>
</Data>
</Record>
</Output>

I need SSIS to open this XML, read the Table element and insert or update dbo.tblActivity (in this case) with the data defined in the Data element. If updating, the update should only take place if the DateTime is more recent than a field (datLastUpdate) on the corresponding row in the database.

Can anyone offer some guidance (for example is the XML optimally structured for import by SQL) and advice on how (which tasks to use) to go about setting this SSIS package up.

Ps. thanks to Darren I already have the file selection and open process sorted using a Foreach and some enumeration to select each XML file. It is what to do with the XML afterwards I need get working...

Thanks,
Drammy
bump|||

Drammy wrote:
Hi,
I am writing a contingency plan for incase our SQL Server (2005) goes down. Basically when our applicatino is about to write data to the SQL Server it checks the connection. If the connection passes it outputs to SQL, if it fails it generates an XML document detailing the data so that the data can be imported later.
Each XML document needs to define which database and which table needs to be updated/inserted.
So the SQL needs to be able to read a number of XML documents and update the relevant tables according dependent on the data held within the XML; some examples:
<Output>
<Record>
<Database>DW</Database>
<Table>dbo.tblActivity</Table>
<DateTime>11-05-2007 10:03:33.099</DateTime>
<Data>
<strActivityPK>9353|HCR008</strActivityPK>
<strActivityCode>HCR008</strActivityCode>
<strActivityDescription>HCR Complete</strActivityDescription>
<datTarget>2007-05-18</datTarget>
<datActual />
<intMatterIntCode>9353</intMatterIntCode>
<intRecurringActivityFlag>0</intRecurringActivityFlag>
<intETLFlag>0</intETLFlag>
</Data>
</Record>
</Output>
I need SSIS to open this XML, read the Table element and insert or update dbo.tblActivity (in this case) with the data defined in the Data element. If updating, the update should only take place if the DateTime is more recent than a field (datLastUpdate) on the corresponding row in the database.
Can anyone offer some guidance (for example is the XML optimally structured for import by SQL) and advice on how (which tasks to use) to go about setting this SSIS package up.
Ps. thanks to Darren I already have the file selection and open process sorted using a Foreach and some enumeration to select each XML file. It is what to do with the XML afterwards I need get working...
Thanks,
Drammy

If you are intending to update different tables with this, you won't be able to use a data flow. You could use the XML Task to read the XML and the Execute SQL to run a statement using the OPENXML function in SQL Server.

|||Thanks jwelch,

I actually ended up using a DataFlow pulling the XML data into a temp table and then run a SQL task to update the corresponding target tables.

Drammy
|||That's a good approach too

Best XML structure for import into SQL 2005

Hi,

Each XML document needs to define which database and which table needs to be updated/inserted.

So the SQL needs to be able to read a number of XML documents and update the relevant tables according dependent on the data held within the XML; some examples:

Can anyone offer some guidance (for example is the XML optimally structured for import by SQL) and advice on how (which tasks to use) to go about setting this SSIS package up.

Thanks,
Drammy
bump|||

Drammy wrote:
Hi,
I am writing a contingency plan for incase our SQL Server (2005) goes down. Basically when our applicatino is about to write data to the SQL Server it checks the connection. If the connection passes it outputs to SQL, if it fails it generates an XML document detailing the data so that the data can be imported later.
Each XML document needs to define which database and which table needs to be updated/inserted.
So the SQL needs to be able to read a number of XML documents and update the relevant tables according dependent on the data held within the XML; some examples:
<Output>
<Record>
<Database>DW</Database>
<Table>dbo.tblActivity</Table>
<DateTime>11-05-2007 10:03:33.099</DateTime>
<Data>
<strActivityPK>9353|HCR008</strActivityPK>
<strActivityCode>HCR008</strActivityCode>
<strActivityDescription>HCR Complete</strActivityDescription>
<datTarget>2007-05-18</datTarget>
<datActual />
<intMatterIntCode>9353</intMatterIntCode>
<intRecurringActivityFlag>0</intRecurringActivityFlag>
<intETLFlag>0</intETLFlag>
</Data>
</Record>
</Output>
I need SSIS to open this XML, read the Table element and insert or update dbo.tblActivity (in this case) with the data defined in the Data element. If updating, the update should only take place if the DateTime is more recent than a field (datLastUpdate) on the corresponding row in the database.
Can anyone offer some guidance (for example is the XML optimally structured for import by SQL) and advice on how (which tasks to use) to go about setting this SSIS package up.
Ps. thanks to Darren I already have the file selection and open process sorted using a Foreach and some enumeration to select each XML file. It is what to do with the XML afterwards I need get working...
Thanks,
Drammy

|||Thanks jwelch,

I actually ended up using a DataFlow pulling the XML data into a temp table and then run a SQL task to update the corresponding target tables.

Drammy
|||That's a good approach too

Sunday, February 19, 2012

Best way to setup data structure

I have an online application that works and functions like an accounting,
payroll, scheduliing and billing system. In the past, each center that
signed up would be setup in thier own database on SQL 2000. As this grows
more and more multisite organizations are using this service. It will
possible to have one organization with 2,000 centers using this service. My
question is: Should I continue setting up individual databases for each
center or should I combine the 2,000 centers for the one company into one
database and have a center key to distinguse the data. The only issue with
the mulit sites are reporting across the 2,000 or so centers. 95% of the
application is center specific the only time the data needs to be combined is
for some reports that are company wide.
Thanks,
MarkHi
I would do one database, but it depends on how well normalised the Db is and
what a stress test would show up.
Managing 2000 DB's becomes a nightmare.
How big ar the DB's and how big do you expect them to grow?
Regards
Mike
"Mark" wrote:
> I have an online application that works and functions like an accounting,
> payroll, scheduliing and billing system. In the past, each center that
> signed up would be setup in thier own database on SQL 2000. As this grows
> more and more multisite organizations are using this service. It will
> possible to have one organization with 2,000 centers using this service. My
> question is: Should I continue setting up individual databases for each
> center or should I combine the 2,000 centers for the one company into one
> database and have a center key to distinguse the data. The only issue with
> the mulit sites are reporting across the 2,000 or so centers. 95% of the
> application is center specific the only time the data needs to be combined is
> for some reports that are company wide.
> Thanks,
> Mark|||The databse is normalized and where it is not, we are in the process of
fixing that now. The database size after 2 years of use is about 3 gigs - I
would imagine each database could grow to 10 gigs a piece at which time we
will get rid of some of the data. The size of the database is really
dependant on the activity of the centers.
Thanks,
Mark
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> I would do one database, but it depends on how well normalised the Db is and
> what a stress test would show up.
> Managing 2000 DB's becomes a nightmare.
> How big ar the DB's and how big do you expect them to grow?
> Regards
> Mike
> "Mark" wrote:
> > I have an online application that works and functions like an accounting,
> > payroll, scheduliing and billing system. In the past, each center that
> > signed up would be setup in thier own database on SQL 2000. As this grows
> > more and more multisite organizations are using this service. It will
> > possible to have one organization with 2,000 centers using this service. My
> > question is: Should I continue setting up individual databases for each
> > center or should I combine the 2,000 centers for the one company into one
> > database and have a center key to distinguse the data. The only issue with
> > the mulit sites are reporting across the 2,000 or so centers. 95% of the
> > application is center specific the only time the data needs to be combined is
> > for some reports that are company wide.
> >
> > Thanks,
> > Mark|||I would put everything in one DB. one of our companies has around 400
lawfirms and everything is in one database. The main thing that you have to
think about is security. In our case each lawfirm has a role and many tables
contain a field that specifies to which law firm the row belongs (row level
security). This is basically the idea but it gets more complex when you have
multiple groups with each law firm. This is based on the requirements and
something that you have to analyze). The
process might be painful but you have a lot to win as a DBA:
-amount of work for you and the developers ($$$)
-more simplified disaster recovery
-For reporting you can replicate your database on a different server and let
your clients hit the your reporting box instead of your prod.
-You will not need as many connection strings!
-Avoiding an administrative nightmare
--
Sasan Saidi, MSc in CS
Senior DBA
Brascan Business Services
"I saw it work in a cartoon once so I am pretty sure I can do it."
"Mark" wrote:
> The databse is normalized and where it is not, we are in the process of
> fixing that now. The database size after 2 years of use is about 3 gigs - I
> would imagine each database could grow to 10 gigs a piece at which time we
> will get rid of some of the data. The size of the database is really
> dependant on the activity of the centers.
> Thanks,
> Mark
> "Mike Epprecht (SQL MVP)" wrote:
> > Hi
> >
> > I would do one database, but it depends on how well normalised the Db is and
> > what a stress test would show up.
> >
> > Managing 2000 DB's becomes a nightmare.
> >
> > How big ar the DB's and how big do you expect them to grow?
> >
> > Regards
> > Mike
> >
> > "Mark" wrote:
> >
> > > I have an online application that works and functions like an accounting,
> > > payroll, scheduliing and billing system. In the past, each center that
> > > signed up would be setup in thier own database on SQL 2000. As this grows
> > > more and more multisite organizations are using this service. It will
> > > possible to have one organization with 2,000 centers using this service. My
> > > question is: Should I continue setting up individual databases for each
> > > center or should I combine the 2,000 centers for the one company into one
> > > database and have a center key to distinguse the data. The only issue with
> > > the mulit sites are reporting across the 2,000 or so centers. 95% of the
> > > application is center specific the only time the data needs to be combined is
> > > for some reports that are company wide.
> > >
> > > Thanks,
> > > Mark

Best way to setup data structure

Sunday, February 12, 2012

Best way to generate script

Hi!

I have to make a program that copy a database structure. Right now i'm using SMO but it take a lot of time just to copy all tables. Here is what i'm doing:

ServeurLocal.SetDefaultInitFields(GetType(Table), "IsSystemObject")

For Each uneTable In BDConfig.Tables

If Not uneTable.IsSystemObject Then

NouvelleBD.ExecuteNonQuery(uneTable.Script)

End If

We have over 700 tables and it take me more than 5 min to copy all the tables and i haven't copy all the view and stored proc yet. The problem seems that i execute one by one each script. So i was wondering is there a way to stock all the script in some kind of object and then when the FOR is done execute this big script. Is it possible to create an object script which we can add all the script for each table ?

Thank and sorry about my bad English ^_^

I found how do to it. I have to use an object Transfer. Then i generate a script for all my object and execute this script.|||

I know you've found another way to address the problem, but here's a bit of code that can create the script you were actually attempting to create, and will put the tables first, the views second and the stored procedures last in the script:

Dim scrDBScript As Scripter
Dim objSMOObjects(1) As SqlSmoObject
Dim intObjCount As Integer
intObjCount = 0

Dim srv As Server
srv = New Server("MyServer")
'Define a Database object variable by supplying the server and the database name arguments in the constructor.
Dim db As Database
Dim tbColl As TableCollection
Dim tb As Table
Dim vwColl As ViewCollection
Dim vw As View
Dim spColl As StoredProcedureCollection
Dim sp As StoredProcedure

db = New Database(srv, "AdventureWorks")
tbColl = db.Tables
For Each tb In tbColl
objSMOObjects(intObjCount) = tb 'Script each table
intObjCount += 1
Next
vwColl = db.Views
For Each vw In vwColl
objSMOObjects(intObjCount) = vw 'Script each view
intObjCount += 1
Next
spColl = db.StoredProcedures
For Each sp In spColl
objSMOObjects(intObjCount) = sp 'Script each stored procedure
intObjCount += 1
Next

scrDBScript = New Scripter(srv)
scrDBScript.Options.FileName = "c:\DBScript.sql"
scrDBScript.Options.IncludeHeaders = True
scrDBScript.Options.AppendToFile = True

ReDim Preserve objSMOObjects(intObjCount - 1)
scrDBScript.Script(objSMOObjects)

This may be something you would want to try another time, but shouldn't perform as badly as you're currently experiencing.

|||

Thanks for your answer. Actually i have some problem trying to copy my DB. I have tried using a Tranfer Object and also like your method. My problem is that we have a lot of views where the query is using others views. I have the same problem with stored proc that call other stored proc. The problem is when i'm trying to execute the script for example for the views. I've got error because some views can't be created because some views are missing. In fact they are not missing they are just not created yet.

Maybe i'm not using the best way to do what i need to do. I have web application and in some page the user can create a copy of the DB that he is using. So all i want i to copy all the DB in a new one. Maybe i shouldn't use SMO to do that.....i don't really know what is the best way to do it.

|||

I just played with some code and got a successful copy of AdventureWorks using the Transfer object. Here's my demo code - see if it accomplishes what you're trying to do.

Dim srv As Server
srv = New Server("TestServer")
'Define a Database object variable by supplying the server and the database name arguments in the constructor.
Dim db As Database

db = srv.Databases("AdventureWorks")
Dim strDBName As String
strDBName = "TestDatabase"

Dim dbCopy As Database
Dim dbFG As FileGroup
Dim dbFile As DataFile
Dim dbLogFile As LogFile

dbCopy = New Database(srv, strDBName) 'Instantiate the new database
dbFG = New FileGroup(dbCopy, "PRIMARY") 'Instantiate the PRIMARY filegroup
dbCopy.FileGroups.Add(dbFG) 'Add the FileGroup
dbFile = New DataFile(dbFG, strDBName + "_Data") 'Instantiate the data file within the filegroup
dbFG.Files.Add(dbFile) 'Add the File
dbFile.FileName = "D:\MSSQL.1\MSSQL\Data\" + strDBName + "_Data.mdf" 'Define the actual file system name
dbFile.Size = 25.0 * 1024.0 'Define the size of the file
dbFile.GrowthType = FileGrowthType.Percent 'Define the Growth Type
dbFile.Growth = 25.0 'Define the Growth Percent
dbFile.MaxSize = 100.0 * 1024.0 'Define the Max database size

dbLogFile = New LogFile(dbCopy, strDBName + "_Log") 'Instantiate the log file (no filegroup for log files)
dbCopy.LogFiles.Add(dbLogFile) 'Add the log file
dbLogFile.FileName = "D:\MSSQL.1\MSSQL\Data\" + strDBName + "_Log.ldf" 'Define the log file system name
dbLogFile.Size = 10.0 * 1024.0 'Define the size of the log file
dbLogFile.GrowthType = FileGrowthType.Percent 'Define the Growth Type
dbLogFile.Growth = 25.0 'Define the Growth Percent

dbCopy.Create() 'Create the database

'Define a Transfer object and set the required options and properties.
Dim xfr As Transfer
xfr = New Transfer(db)
xfr.CopyAllObjects = True
xfr.CopyAllUsers = True
xfr.Options.WithDependencies = True
xfr.Options.ContinueScriptingOnError = True
xfr.DestinationDatabase = strDBName
xfr.DestinationServer = srv.Name
xfr.DestinationLoginSecure = True
xfr.CopySchema = True
'Script the transfer. Alternatively perform immediate data transfer with TransferData method.
xfr.TransferData()

|||Thanks but i have the same problem. When i tried to copy all objects it failed sometime (not all the time). I've got a message saying that it cannot created a view because the view use in the SELECT do not existe. In fact the problem is beacause it's trying to create the "child" view befor the "parent" view.

I've found another way to make my copy and it's much faster than using the Transfer object. Using the Transfert Object it took me 30sec to copy all the tables (715 tables). Now it took me 15sec to copy all the BD(715 tables + 315 view, 236 stored proc, 32 function). Insteand of doing a copy i've created a Backup. Using the backup Object i save the backup on my drive. Then i create a the New DB and then i restore the backup on this new DB. It's a lot faster and i have no error when i'm using this method.|||

Backup/restore is the most trouble-free way to get a copy of an existing database because nothing is recompiled or verified by SQL Server.

For example, if a view or stored procedure references a column that no longer exists, you cannot generate and execute a script because it will fail. The object(s) in question is invalid in the source database and will continue to be invalid in the new database. This method provides an exact copy of what you have.

I don't have experience with the scripting that you are referring to but it would seem to me that if the scripting method supports dependencies, then your dependency information is missing or corrupt in the database. For example, if MyProc1 executes MyProc2, SQL Server will only have dependency information IF MyProc2 was created first followed by MyProc1. If that is true, when you generate a SQL Script (at least through Enterprise Manager's Generate SQL Script wizard, dependency information will be used to properly sequence the objects in the script.

Best way to generate script

Hi!

I have to make a program that copy a database structure. Right now i'm using SMO but it take a lot of time just to copy all tables. Here is what i'm doing:

ServeurLocal.SetDefaultInitFields(GetType(Table), "IsSystemObject")

For Each uneTable In BDConfig.Tables

If Not uneTable.IsSystemObject Then

NouvelleBD.ExecuteNonQuery(uneTable.Script)

End If

Thank and sorry about my bad English ^_^

I found how do to it. I have to use an object Transfer. Then i generate a script for all my object and execute this script.|||

Dim scrDBScript As Scripter
Dim objSMOObjects(1) As SqlSmoObject
Dim intObjCount As Integer
intObjCount = 0

scrDBScript = New Scripter(srv)
scrDBScript.Options.FileName = "c:\DBScript.sql"
scrDBScript.Options.IncludeHeaders = True
scrDBScript.Options.AppendToFile = True

ReDim Preserve objSMOObjects(intObjCount - 1)
scrDBScript.Script(objSMOObjects)

This may be something you would want to try another time, but shouldn't perform as badly as you're currently experiencing.

|||

I just played with some code and got a successful copy of AdventureWorks using the Transfer object. Here's my demo code - see if it accomplishes what you're trying to do.

Dim srv As Server
srv = New Server("TestServer")
'Define a Database object variable by supplying the server and the database name arguments in the constructor.
Dim db As Database

db = srv.Databases("AdventureWorks")
Dim strDBName As String
strDBName = "TestDatabase"

Dim dbCopy As Database
Dim dbFG As FileGroup
Dim dbFile As DataFile
Dim dbLogFile As LogFile

dbCopy.Create() 'Create the database

Backup/restore is the most trouble-free way to get a copy of an existing database because nothing is recompiled or verified by SQL Server.

Tuesday, March 20, 2012

Big query on different servers

Big query on different servers

Sunday, March 11, 2012

BI studio

BI studio

Saturday, February 25, 2012

Better Way To Handle The Code?

Friday, February 24, 2012

Best XML structure for import into SQL 2005

Best XML structure for import into SQL 2005

Sunday, February 19, 2012

Best way to setup data structure

Best way to setup data structure

Best way to setup data structure

Sunday, February 12, 2012

Best way to generate script

Best way to generate script

Best way to copy database

Blog Archive

About Me