I am creating a application in Visual C++ 2005 that has database connections in it. I have gone through the process of setting up the data source and it will work great on my computer. However, the second I move either the project or the created executable to another computer the application keeps crashing. I know that it is crashing due to the database connection string. The string is setup to work only on my computer, I am wondering how do I make, or edit, the string to work on any computer? Thanks for the help.
Have you gone into SQL Server Surface Area Configuration to enable remote connections to the database?
Showing posts with label application. Show all posts
Showing posts with label application. Show all posts
Friday, March 30, 2012
Wednesday, March 28, 2012
Relationships and constraints across databases
(I wasn't sure which forum to pos this in. Let me know if you think there is a better one.)
I have an application that breaks down into two sets of data, Set A and Set B, for our purposes. Both Sets currently reside in the same database. Set A is a fairly static set of data, changing maybe once a week, if not less often. Set B is empty at first but it has a lot of dependencies to Set A. Items are chosen from Set A to fill Set B but never the otherway around.
Here is the problem. We continue to copy the "master" database whenever a new customer signs up. The problem is, anytime we have to update data on Set A we have to do it across multiple databases. Set B is never copied as its customer owned.
What I would like to do is create a separate database that contains Set A data only. Whenever we have a new customer, I create a new database that contains Set B only. I have done so, but the relationships and constraints cannot be done unless I use triggers and procedures.
Is there anyway to achieve what I am trying to do without using triggers and procedures?
Thanks in advance.
Hi, actually no. Cross database integrity has to be achieved through triggers.HTH, Jens Suessmeyer.
http://www.sqlserver2005.de|||
Another alternative in 2005 would be to use schemas instead of different databases. Then all of your data would be in the same database, which would be better for your data security, easier to program etc. You could break the schemas out into filegroups if you wanted for backup purposes too.
Then you could use DRI and not have to resort to triggers (which can be really problematic to manage and keep the data in sync (though not by any means impossible.) The problem being that you need a trigger on the parent AND child table, to protect on inserts/updates of children, then for deletes/updates of parents.
|||These circumstances are the sole purpose of triggers (but not always sp_'s).
There is only one alternative but has more tradeoff's than it's worth. Dump everything into one table but you've already discovered this.
Adamus
Monday, March 26, 2012
relation & query
Hello,
I'm relative new to sql and databases and the last few weeks I learned
myself a lot. I'm trying to make a hotel reservation application.
I have a database with a table Booking, a table Room, a table
RoomsPerBooking. So a booking contains date/time etc and a field
RoomsPerBookingID. The table RoomsPerBooking contains number of
persons, unitprice etc. and a field ID and a field RoomID. The table
Room contains data like name, notes etc.
now i have two questions:
First about relations:
The table Booking has relationship: PK table RoomsPerBooking - ID <-->
FK table Booking - RoomsPerBookingID.
The table RoomsPerBookingID has relationship: PK table Room - ID <-->
FK table RoomsPerBooking - RoomID
Is this relationset good for my purpose? I think it is, but I am not
sure.
The second question is:
How do I get available rooms per night
I came this far... what are the "some statements"?
CREATE PROCEDURE dbo.GetAvailableRooms
(
@.BeginDate DATETIME,
@.EndDate DATETIME
)
AS
SELECT Room.*
FROM Room
WHERE Room.ID NOT IN (
SELECT DISTINCT room.ID
FROM Room room JOIN RoomsPerBooking roomsPerBooking
ON room.ID = roomsPerBooking.RoomID
--Some statements--
WHERE booking.FromDate <= @.EndDate
AND booking.ToDate >= @.BeginDate)
GOPlease post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications.
Why do you have multiple names for the same data element? Why do you
use a singular name for a set of Rooms? Why did you use id and
room_id when the standard way of referencing a room is a "room number"?
Why do you use aliases that are the same as the base table names?
After you clean up the schema a bit, look at using a Calendar table.
Friday, March 23, 2012
Related Records Inport from single CSV
Hello,
I am working on an application that interfaces with an AS400. The AS400
programmers I am working with at the client, are not very flexible. They are
providing me with data for the initial database load for the application to
go live. After that my app will take care of all data manipulation. My
problem is, for the initial import, I have a single row in a CSV file that
holds a customer record, it is then followed by three fields for product
information that repeat based on the products that that customer has. I know
the number of fields leading up to the repeating portion and I know that
from that point on, every three fields goes to one record. In my database
the customer record is in a seperate table from the products in a one to
many relationship.
I need to write a script or DTS package that will manipulate the data
and create the related records in both tables. Can this be done? Is there a
simple way to do it? Any hints as to what to try or where to look would be
greatly appreciated. Thanks.
Andrew,
I don't fully understand whether the "single row" follwed by "fields"
is one long single row, or one row followed by more rows, but if you
have just one row per customer, you may be able to import your data into
a staging table with columns for the customer data and one additional
long column for all that customer's product information, which you can
then break up once it's in SQL Server.
Can you post a few customer's worth of what the data looks like? In
particular, what I can't tell from your description is how you know
where the product information ends and the next customer's information
begins.
If the customer and product info is on separate lines, you could
preprocess the data before importing it, by adding line numbers first,
then splitting it into two files, one for customers, one for products,
which the line numbers would help you link back together after you
import each into SQL Server.
Steve Kass
Drew University
Andrew L. Van Slaars wrote:
>Hello,
> I am working on an application that interfaces with an AS400. The AS400
>programmers I am working with at the client, are not very flexible. They are
>providing me with data for the initial database load for the application to
>go live. After that my app will take care of all data manipulation. My
>problem is, for the initial import, I have a single row in a CSV file that
>holds a customer record, it is then followed by three fields for product
>information that repeat based on the products that that customer has. I know
>the number of fields leading up to the repeating portion and I know that
>from that point on, every three fields goes to one record. In my database
>the customer record is in a seperate table from the products in a one to
>many relationship.
> I need to write a script or DTS package that will manipulate the data
>and create the related records in both tables. Can this be done? Is there a
>simple way to do it? Any hints as to what to try or where to look would be
>greatly appreciated. Thanks.
>
>
|||"Andrew L. Van Slaars" <andrew@.vanslaars.com> wrote in message
news:%23TxETPo8EHA.824@.TK2MSFTNGP11.phx.gbl...
> Hello,
> I am working on an application that interfaces with an AS400. The AS400
> programmers I am working with at the client, are not very flexible. They
> are
> providing me with data for the initial database load for the application
> to
> go live. After that my app will take care of all data manipulation. My
> problem is, for the initial import, I have a single row in a CSV file that
> holds a customer record, it is then followed by three fields for product
> information that repeat based on the products that that customer has. I
> know
> the number of fields leading up to the repeating portion and I know that
> from that point on, every three fields goes to one record. In my database
> the customer record is in a seperate table from the products in a one to
> many relationship.
> I need to write a script or DTS package that will manipulate the data
> and create the related records in both tables. Can this be done? Is there
> a
> simple way to do it? Any hints as to what to try or where to look would be
> greatly appreciated. Thanks.
>
Andrew,
You CAN do this. In a nutshell, as long as the data is in some type of
consistent structured format, you will be able to manipulate it. I think
the easiest solution for you would be to use a DTS package and ActiveX
scripting within that package. You can instantiate ADO recordsets and then
manipulate the data in any fashion that you wish.
You should be able to read the CSV rows in and then based on what the data
values are in the various "fields", you can then instantiate additional
recordsets. Your choices from there are up to you. You could then create
ADO recordset instances that connect to your SQL Server tables and do
individual INSERTS (slow), or you can parse your data into a large chunk of
INSERT statements (store them in a string variable) and then use the
ADO.Connection object's Execute method to bulk those inserts statements into
SQL Server (faster).
Example:
Dim cn as ADODB.Connection
cn.ConnectionString = "Provider = OLEDB.1; ....."
cn.Execute(SQLString)
Hope this gets you off on the right foot.
Rick Sawtell
MCT, MCSD, MCDBA
sql
I am working on an application that interfaces with an AS400. The AS400
programmers I am working with at the client, are not very flexible. They are
providing me with data for the initial database load for the application to
go live. After that my app will take care of all data manipulation. My
problem is, for the initial import, I have a single row in a CSV file that
holds a customer record, it is then followed by three fields for product
information that repeat based on the products that that customer has. I know
the number of fields leading up to the repeating portion and I know that
from that point on, every three fields goes to one record. In my database
the customer record is in a seperate table from the products in a one to
many relationship.
I need to write a script or DTS package that will manipulate the data
and create the related records in both tables. Can this be done? Is there a
simple way to do it? Any hints as to what to try or where to look would be
greatly appreciated. Thanks.
Andrew,
I don't fully understand whether the "single row" follwed by "fields"
is one long single row, or one row followed by more rows, but if you
have just one row per customer, you may be able to import your data into
a staging table with columns for the customer data and one additional
long column for all that customer's product information, which you can
then break up once it's in SQL Server.
Can you post a few customer's worth of what the data looks like? In
particular, what I can't tell from your description is how you know
where the product information ends and the next customer's information
begins.
If the customer and product info is on separate lines, you could
preprocess the data before importing it, by adding line numbers first,
then splitting it into two files, one for customers, one for products,
which the line numbers would help you link back together after you
import each into SQL Server.
Steve Kass
Drew University
Andrew L. Van Slaars wrote:
>Hello,
> I am working on an application that interfaces with an AS400. The AS400
>programmers I am working with at the client, are not very flexible. They are
>providing me with data for the initial database load for the application to
>go live. After that my app will take care of all data manipulation. My
>problem is, for the initial import, I have a single row in a CSV file that
>holds a customer record, it is then followed by three fields for product
>information that repeat based on the products that that customer has. I know
>the number of fields leading up to the repeating portion and I know that
>from that point on, every three fields goes to one record. In my database
>the customer record is in a seperate table from the products in a one to
>many relationship.
> I need to write a script or DTS package that will manipulate the data
>and create the related records in both tables. Can this be done? Is there a
>simple way to do it? Any hints as to what to try or where to look would be
>greatly appreciated. Thanks.
>
>
|||"Andrew L. Van Slaars" <andrew@.vanslaars.com> wrote in message
news:%23TxETPo8EHA.824@.TK2MSFTNGP11.phx.gbl...
> Hello,
> I am working on an application that interfaces with an AS400. The AS400
> programmers I am working with at the client, are not very flexible. They
> are
> providing me with data for the initial database load for the application
> to
> go live. After that my app will take care of all data manipulation. My
> problem is, for the initial import, I have a single row in a CSV file that
> holds a customer record, it is then followed by three fields for product
> information that repeat based on the products that that customer has. I
> know
> the number of fields leading up to the repeating portion and I know that
> from that point on, every three fields goes to one record. In my database
> the customer record is in a seperate table from the products in a one to
> many relationship.
> I need to write a script or DTS package that will manipulate the data
> and create the related records in both tables. Can this be done? Is there
> a
> simple way to do it? Any hints as to what to try or where to look would be
> greatly appreciated. Thanks.
>
Andrew,
You CAN do this. In a nutshell, as long as the data is in some type of
consistent structured format, you will be able to manipulate it. I think
the easiest solution for you would be to use a DTS package and ActiveX
scripting within that package. You can instantiate ADO recordsets and then
manipulate the data in any fashion that you wish.
You should be able to read the CSV rows in and then based on what the data
values are in the various "fields", you can then instantiate additional
recordsets. Your choices from there are up to you. You could then create
ADO recordset instances that connect to your SQL Server tables and do
individual INSERTS (slow), or you can parse your data into a large chunk of
INSERT statements (store them in a string variable) and then use the
ADO.Connection object's Execute method to bulk those inserts statements into
SQL Server (faster).
Example:
Dim cn as ADODB.Connection
cn.ConnectionString = "Provider = OLEDB.1; ....."
cn.Execute(SQLString)
Hope this gets you off on the right foot.
Rick Sawtell
MCT, MCSD, MCDBA
sql
Related Records Inport from single CSV
Hello,
I am working on an application that interfaces with an AS400. The AS400
programmers I am working with at the client, are not very flexible. They are
providing me with data for the initial database load for the application to
go live. After that my app will take care of all data manipulation. My
problem is, for the initial import, I have a single row in a CSV file that
holds a customer record, it is then followed by three fields for product
information that repeat based on the products that that customer has. I know
the number of fields leading up to the repeating portion and I know that
from that point on, every three fields goes to one record. In my database
the customer record is in a seperate table from the products in a one to
many relationship.
I need to write a script or DTS package that will manipulate the data
and create the related records in both tables. Can this be done? Is there a
simple way to do it? Any hints as to what to try or where to look would be
greatly appreciated. Thanks.Andrew,
I don't fully understand whether the "single row" follwed by "fields"
is one long single row, or one row followed by more rows, but if you
have just one row per customer, you may be able to import your data into
a staging table with columns for the customer data and one additional
long column for all that customer's product information, which you can
then break up once it's in SQL Server.
Can you post a few customer's worth of what the data looks like? In
particular, what I can't tell from your description is how you know
where the product information ends and the next customer's information
begins.
If the customer and product info is on separate lines, you could
preprocess the data before importing it, by adding line numbers first,
then splitting it into two files, one for customers, one for products,
which the line numbers would help you link back together after you
import each into SQL Server.
Steve Kass
Drew University
Andrew L. Van Slaars wrote:
>Hello,
> I am working on an application that interfaces with an AS400. The AS400
>programmers I am working with at the client, are not very flexible. They are
>providing me with data for the initial database load for the application to
>go live. After that my app will take care of all data manipulation. My
>problem is, for the initial import, I have a single row in a CSV file that
>holds a customer record, it is then followed by three fields for product
>information that repeat based on the products that that customer has. I know
>the number of fields leading up to the repeating portion and I know that
>from that point on, every three fields goes to one record. In my database
>the customer record is in a seperate table from the products in a one to
>many relationship.
> I need to write a script or DTS package that will manipulate the data
>and create the related records in both tables. Can this be done? Is there a
>simple way to do it? Any hints as to what to try or where to look would be
>greatly appreciated. Thanks.
>
>|||"Andrew L. Van Slaars" <andrew@.vanslaars.com> wrote in message
news:%23TxETPo8EHA.824@.TK2MSFTNGP11.phx.gbl...
> Hello,
> I am working on an application that interfaces with an AS400. The AS400
> programmers I am working with at the client, are not very flexible. They
> are
> providing me with data for the initial database load for the application
> to
> go live. After that my app will take care of all data manipulation. My
> problem is, for the initial import, I have a single row in a CSV file that
> holds a customer record, it is then followed by three fields for product
> information that repeat based on the products that that customer has. I
> know
> the number of fields leading up to the repeating portion and I know that
> from that point on, every three fields goes to one record. In my database
> the customer record is in a seperate table from the products in a one to
> many relationship.
> I need to write a script or DTS package that will manipulate the data
> and create the related records in both tables. Can this be done? Is there
> a
> simple way to do it? Any hints as to what to try or where to look would be
> greatly appreciated. Thanks.
>
Andrew,
You CAN do this. In a nutshell, as long as the data is in some type of
consistent structured format, you will be able to manipulate it. I think
the easiest solution for you would be to use a DTS package and ActiveX
scripting within that package. You can instantiate ADO recordsets and then
manipulate the data in any fashion that you wish.
You should be able to read the CSV rows in and then based on what the data
values are in the various "fields", you can then instantiate additional
recordsets. Your choices from there are up to you. You could then create
ADO recordset instances that connect to your SQL Server tables and do
individual INSERTS (slow), or you can parse your data into a large chunk of
INSERT statements (store them in a string variable) and then use the
ADO.Connection object's Execute method to bulk those inserts statements into
SQL Server (faster).
Example:
Dim cn as ADODB.Connection
cn.ConnectionString = "Provider = OLEDB.1; ....."
cn.Execute(SQLString)
Hope this gets you off on the right foot.
Rick Sawtell
MCT, MCSD, MCDBA
I am working on an application that interfaces with an AS400. The AS400
programmers I am working with at the client, are not very flexible. They are
providing me with data for the initial database load for the application to
go live. After that my app will take care of all data manipulation. My
problem is, for the initial import, I have a single row in a CSV file that
holds a customer record, it is then followed by three fields for product
information that repeat based on the products that that customer has. I know
the number of fields leading up to the repeating portion and I know that
from that point on, every three fields goes to one record. In my database
the customer record is in a seperate table from the products in a one to
many relationship.
I need to write a script or DTS package that will manipulate the data
and create the related records in both tables. Can this be done? Is there a
simple way to do it? Any hints as to what to try or where to look would be
greatly appreciated. Thanks.Andrew,
I don't fully understand whether the "single row" follwed by "fields"
is one long single row, or one row followed by more rows, but if you
have just one row per customer, you may be able to import your data into
a staging table with columns for the customer data and one additional
long column for all that customer's product information, which you can
then break up once it's in SQL Server.
Can you post a few customer's worth of what the data looks like? In
particular, what I can't tell from your description is how you know
where the product information ends and the next customer's information
begins.
If the customer and product info is on separate lines, you could
preprocess the data before importing it, by adding line numbers first,
then splitting it into two files, one for customers, one for products,
which the line numbers would help you link back together after you
import each into SQL Server.
Steve Kass
Drew University
Andrew L. Van Slaars wrote:
>Hello,
> I am working on an application that interfaces with an AS400. The AS400
>programmers I am working with at the client, are not very flexible. They are
>providing me with data for the initial database load for the application to
>go live. After that my app will take care of all data manipulation. My
>problem is, for the initial import, I have a single row in a CSV file that
>holds a customer record, it is then followed by three fields for product
>information that repeat based on the products that that customer has. I know
>the number of fields leading up to the repeating portion and I know that
>from that point on, every three fields goes to one record. In my database
>the customer record is in a seperate table from the products in a one to
>many relationship.
> I need to write a script or DTS package that will manipulate the data
>and create the related records in both tables. Can this be done? Is there a
>simple way to do it? Any hints as to what to try or where to look would be
>greatly appreciated. Thanks.
>
>|||"Andrew L. Van Slaars" <andrew@.vanslaars.com> wrote in message
news:%23TxETPo8EHA.824@.TK2MSFTNGP11.phx.gbl...
> Hello,
> I am working on an application that interfaces with an AS400. The AS400
> programmers I am working with at the client, are not very flexible. They
> are
> providing me with data for the initial database load for the application
> to
> go live. After that my app will take care of all data manipulation. My
> problem is, for the initial import, I have a single row in a CSV file that
> holds a customer record, it is then followed by three fields for product
> information that repeat based on the products that that customer has. I
> know
> the number of fields leading up to the repeating portion and I know that
> from that point on, every three fields goes to one record. In my database
> the customer record is in a seperate table from the products in a one to
> many relationship.
> I need to write a script or DTS package that will manipulate the data
> and create the related records in both tables. Can this be done? Is there
> a
> simple way to do it? Any hints as to what to try or where to look would be
> greatly appreciated. Thanks.
>
Andrew,
You CAN do this. In a nutshell, as long as the data is in some type of
consistent structured format, you will be able to manipulate it. I think
the easiest solution for you would be to use a DTS package and ActiveX
scripting within that package. You can instantiate ADO recordsets and then
manipulate the data in any fashion that you wish.
You should be able to read the CSV rows in and then based on what the data
values are in the various "fields", you can then instantiate additional
recordsets. Your choices from there are up to you. You could then create
ADO recordset instances that connect to your SQL Server tables and do
individual INSERTS (slow), or you can parse your data into a large chunk of
INSERT statements (store them in a string variable) and then use the
ADO.Connection object's Execute method to bulk those inserts statements into
SQL Server (faster).
Example:
Dim cn as ADODB.Connection
cn.ConnectionString = "Provider = OLEDB.1; ....."
cn.Execute(SQLString)
Hope this gets you off on the right foot.
Rick Sawtell
MCT, MCSD, MCDBA
Related Records Inport from single CSV
Hello,
I am working on an application that interfaces with an AS400. The AS400
programmers I am working with at the client, are not very flexible. They are
providing me with data for the initial database load for the application to
go live. After that my app will take care of all data manipulation. My
problem is, for the initial import, I have a single row in a CSV file that
holds a customer record, it is then followed by three fields for product
information that repeat based on the products that that customer has. I know
the number of fields leading up to the repeating portion and I know that
from that point on, every three fields goes to one record. In my database
the customer record is in a seperate table from the products in a one to
many relationship.
I need to write a script or DTS package that will manipulate the data
and create the related records in both tables. Can this be done? Is there a
simple way to do it? Any hints as to what to try or where to look would be
greatly appreciated. Thanks.Andrew,
I don't fully understand whether the "single row" follwed by "fields"
is one long single row, or one row followed by more rows, but if you
have just one row per customer, you may be able to import your data into
a staging table with columns for the customer data and one additional
long column for all that customer's product information, which you can
then break up once it's in SQL Server.
Can you post a few customer's worth of what the data looks like? In
particular, what I can't tell from your description is how you know
where the product information ends and the next customer's information
begins.
If the customer and product info is on separate lines, you could
preprocess the data before importing it, by adding line numbers first,
then splitting it into two files, one for customers, one for products,
which the line numbers would help you link back together after you
import each into SQL Server.
Steve Kass
Drew University
Andrew L. Van Slaars wrote:
>Hello,
> I am working on an application that interfaces with an AS400. The AS400
>programmers I am working with at the client, are not very flexible. They ar
e
>providing me with data for the initial database load for the application to
>go live. After that my app will take care of all data manipulation. My
>problem is, for the initial import, I have a single row in a CSV file that
>holds a customer record, it is then followed by three fields for product
>information that repeat based on the products that that customer has. I kno
w
>the number of fields leading up to the repeating portion and I know that
>from that point on, every three fields goes to one record. In my database
>the customer record is in a seperate table from the products in a one to
>many relationship.
> I need to write a script or DTS package that will manipulate the data
>and create the related records in both tables. Can this be done? Is there a
>simple way to do it? Any hints as to what to try or where to look would be
>greatly appreciated. Thanks.
>
>|||"Andrew L. Van Slaars" <andrew@.vanslaars.com> wrote in message
news:%23TxETPo8EHA.824@.TK2MSFTNGP11.phx.gbl...
> Hello,
> I am working on an application that interfaces with an AS400. The AS400
> programmers I am working with at the client, are not very flexible. They
> are
> providing me with data for the initial database load for the application
> to
> go live. After that my app will take care of all data manipulation. My
> problem is, for the initial import, I have a single row in a CSV file that
> holds a customer record, it is then followed by three fields for product
> information that repeat based on the products that that customer has. I
> know
> the number of fields leading up to the repeating portion and I know that
> from that point on, every three fields goes to one record. In my database
> the customer record is in a seperate table from the products in a one to
> many relationship.
> I need to write a script or DTS package that will manipulate the data
> and create the related records in both tables. Can this be done? Is there
> a
> simple way to do it? Any hints as to what to try or where to look would be
> greatly appreciated. Thanks.
>
Andrew,
You CAN do this. In a nutshell, as long as the data is in some type of
consistent structured format, you will be able to manipulate it. I think
the easiest solution for you would be to use a DTS package and ActiveX
scripting within that package. You can instantiate ADO recordsets and then
manipulate the data in any fashion that you wish.
You should be able to read the CSV rows in and then based on what the data
values are in the various "fields", you can then instantiate additional
recordsets. Your choices from there are up to you. You could then create
ADO recordset instances that connect to your SQL Server tables and do
individual INSERTS (slow), or you can parse your data into a large chunk of
INSERT statements (store them in a string variable) and then use the
ADO.Connection object's Execute method to bulk those inserts statements into
SQL Server (faster).
Example:
Dim cn as ADODB.Connection
cn.ConnectionString = "Provider = OLEDB.1; ....."
cn.Execute(SQLString)
Hope this gets you off on the right foot.
Rick Sawtell
MCT, MCSD, MCDBA
I am working on an application that interfaces with an AS400. The AS400
programmers I am working with at the client, are not very flexible. They are
providing me with data for the initial database load for the application to
go live. After that my app will take care of all data manipulation. My
problem is, for the initial import, I have a single row in a CSV file that
holds a customer record, it is then followed by three fields for product
information that repeat based on the products that that customer has. I know
the number of fields leading up to the repeating portion and I know that
from that point on, every three fields goes to one record. In my database
the customer record is in a seperate table from the products in a one to
many relationship.
I need to write a script or DTS package that will manipulate the data
and create the related records in both tables. Can this be done? Is there a
simple way to do it? Any hints as to what to try or where to look would be
greatly appreciated. Thanks.Andrew,
I don't fully understand whether the "single row" follwed by "fields"
is one long single row, or one row followed by more rows, but if you
have just one row per customer, you may be able to import your data into
a staging table with columns for the customer data and one additional
long column for all that customer's product information, which you can
then break up once it's in SQL Server.
Can you post a few customer's worth of what the data looks like? In
particular, what I can't tell from your description is how you know
where the product information ends and the next customer's information
begins.
If the customer and product info is on separate lines, you could
preprocess the data before importing it, by adding line numbers first,
then splitting it into two files, one for customers, one for products,
which the line numbers would help you link back together after you
import each into SQL Server.
Steve Kass
Drew University
Andrew L. Van Slaars wrote:
>Hello,
> I am working on an application that interfaces with an AS400. The AS400
>programmers I am working with at the client, are not very flexible. They ar
e
>providing me with data for the initial database load for the application to
>go live. After that my app will take care of all data manipulation. My
>problem is, for the initial import, I have a single row in a CSV file that
>holds a customer record, it is then followed by three fields for product
>information that repeat based on the products that that customer has. I kno
w
>the number of fields leading up to the repeating portion and I know that
>from that point on, every three fields goes to one record. In my database
>the customer record is in a seperate table from the products in a one to
>many relationship.
> I need to write a script or DTS package that will manipulate the data
>and create the related records in both tables. Can this be done? Is there a
>simple way to do it? Any hints as to what to try or where to look would be
>greatly appreciated. Thanks.
>
>|||"Andrew L. Van Slaars" <andrew@.vanslaars.com> wrote in message
news:%23TxETPo8EHA.824@.TK2MSFTNGP11.phx.gbl...
> Hello,
> I am working on an application that interfaces with an AS400. The AS400
> programmers I am working with at the client, are not very flexible. They
> are
> providing me with data for the initial database load for the application
> to
> go live. After that my app will take care of all data manipulation. My
> problem is, for the initial import, I have a single row in a CSV file that
> holds a customer record, it is then followed by three fields for product
> information that repeat based on the products that that customer has. I
> know
> the number of fields leading up to the repeating portion and I know that
> from that point on, every three fields goes to one record. In my database
> the customer record is in a seperate table from the products in a one to
> many relationship.
> I need to write a script or DTS package that will manipulate the data
> and create the related records in both tables. Can this be done? Is there
> a
> simple way to do it? Any hints as to what to try or where to look would be
> greatly appreciated. Thanks.
>
Andrew,
You CAN do this. In a nutshell, as long as the data is in some type of
consistent structured format, you will be able to manipulate it. I think
the easiest solution for you would be to use a DTS package and ActiveX
scripting within that package. You can instantiate ADO recordsets and then
manipulate the data in any fashion that you wish.
You should be able to read the CSV rows in and then based on what the data
values are in the various "fields", you can then instantiate additional
recordsets. Your choices from there are up to you. You could then create
ADO recordset instances that connect to your SQL Server tables and do
individual INSERTS (slow), or you can parse your data into a large chunk of
INSERT statements (store them in a string variable) and then use the
ADO.Connection object's Execute method to bulk those inserts statements into
SQL Server (faster).
Example:
Dim cn as ADODB.Connection
cn.ConnectionString = "Provider = OLEDB.1; ....."
cn.Execute(SQLString)
Hope this gets you off on the right foot.
Rick Sawtell
MCT, MCSD, MCDBA
Relate two databases
I am developing an application which consists of two separate products
that both use SQL Server 2000 to store data (database A and database
B). In the application logic we define relations between objects in
these products.
Is it possible to ensure that when restoring a database from a backup,
the other database is automatically restored at the same time to keep
data consistent between the two databases?
What I want to avoid is having a DBA restoring one database and
forgetting the other, thereby creating inconsistencies.
Kind regards,
Martin
Hi,
You can write a script to restore both the databases in order. So instead of
doing a manual restore you could just run the
restore script which loads both the database.
Some thing like:-
Alter database <db1> set single_user with rollback immediate
go
Restore database <db1> from disk='c:\backup\file_name.bak'
go
Alter database <db1> set Multi_user
go
Alter database <db2> set single_user with rollback immediate
go
Restore database <db2> from disk='c:\backup\file_name.bak'
go
Alter database <db2> set Multi_user
Thanks
Hari
MCDBA
"Martin Perfelian" <zonk99@.hotmail.com> wrote in message
news:b9c2729.0408110037.738ea8c2@.posting.google.co m...
> I am developing an application which consists of two separate products
> that both use SQL Server 2000 to store data (database A and database
> B). In the application logic we define relations between objects in
> these products.
> Is it possible to ensure that when restoring a database from a backup,
> the other database is automatically restored at the same time to keep
> data consistent between the two databases?
> What I want to avoid is having a DBA restoring one database and
> forgetting the other, thereby creating inconsistencies.
> Kind regards,
> Martin
|||Even tho Hari describes the correct method, you may still have problems...
Databases are backed up independently of one another. You must use the logs
and probably, roll both forward to the same point in time, otherwise you may
still get mis-matches
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Martin Perfelian" <zonk99@.hotmail.com> wrote in message
news:b9c2729.0408110037.738ea8c2@.posting.google.co m...
> I am developing an application which consists of two separate products
> that both use SQL Server 2000 to store data (database A and database
> B). In the application logic we define relations between objects in
> these products.
> Is it possible to ensure that when restoring a database from a backup,
> the other database is automatically restored at the same time to keep
> data consistent between the two databases?
> What I want to avoid is having a DBA restoring one database and
> forgetting the other, thereby creating inconsistencies.
> Kind regards,
> Martin
that both use SQL Server 2000 to store data (database A and database
B). In the application logic we define relations between objects in
these products.
Is it possible to ensure that when restoring a database from a backup,
the other database is automatically restored at the same time to keep
data consistent between the two databases?
What I want to avoid is having a DBA restoring one database and
forgetting the other, thereby creating inconsistencies.
Kind regards,
Martin
Hi,
You can write a script to restore both the databases in order. So instead of
doing a manual restore you could just run the
restore script which loads both the database.
Some thing like:-
Alter database <db1> set single_user with rollback immediate
go
Restore database <db1> from disk='c:\backup\file_name.bak'
go
Alter database <db1> set Multi_user
go
Alter database <db2> set single_user with rollback immediate
go
Restore database <db2> from disk='c:\backup\file_name.bak'
go
Alter database <db2> set Multi_user
Thanks
Hari
MCDBA
"Martin Perfelian" <zonk99@.hotmail.com> wrote in message
news:b9c2729.0408110037.738ea8c2@.posting.google.co m...
> I am developing an application which consists of two separate products
> that both use SQL Server 2000 to store data (database A and database
> B). In the application logic we define relations between objects in
> these products.
> Is it possible to ensure that when restoring a database from a backup,
> the other database is automatically restored at the same time to keep
> data consistent between the two databases?
> What I want to avoid is having a DBA restoring one database and
> forgetting the other, thereby creating inconsistencies.
> Kind regards,
> Martin
|||Even tho Hari describes the correct method, you may still have problems...
Databases are backed up independently of one another. You must use the logs
and probably, roll both forward to the same point in time, otherwise you may
still get mis-matches
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Martin Perfelian" <zonk99@.hotmail.com> wrote in message
news:b9c2729.0408110037.738ea8c2@.posting.google.co m...
> I am developing an application which consists of two separate products
> that both use SQL Server 2000 to store data (database A and database
> B). In the application logic we define relations between objects in
> these products.
> Is it possible to ensure that when restoring a database from a backup,
> the other database is automatically restored at the same time to keep
> data consistent between the two databases?
> What I want to avoid is having a DBA restoring one database and
> forgetting the other, thereby creating inconsistencies.
> Kind regards,
> Martin
Relate two databases
I am developing an application which consists of two separate products
that both use SQL Server 2000 to store data (database A and database
B). In the application logic we define relations between objects in
these products.
Is it possible to ensure that when restoring a database from a backup,
the other database is automatically restored at the same time to keep
data consistent between the two databases?
What I want to avoid is having a DBA restoring one database and
forgetting the other, thereby creating inconsistencies.
Kind regards,
MartinHi,
You can write a script to restore both the databases in order. So instead of
doing a manual restore you could just run the
restore script which loads both the database.
Some thing like:-
Alter database <db1> set single_user with rollback immediate
go
Restore database <db1> from disk='c:\backup\file_name.bak'
go
Alter database <db1> set Multi_user
go
Alter database <db2> set single_user with rollback immediate
go
Restore database <db2> from disk='c:\backup\file_name.bak'
go
Alter database <db2> set Multi_user
Thanks
Hari
MCDBA
"Martin Perfelian" <zonk99@.hotmail.com> wrote in message
news:b9c2729.0408110037.738ea8c2@.posting.google.com...
> I am developing an application which consists of two separate products
> that both use SQL Server 2000 to store data (database A and database
> B). In the application logic we define relations between objects in
> these products.
> Is it possible to ensure that when restoring a database from a backup,
> the other database is automatically restored at the same time to keep
> data consistent between the two databases?
> What I want to avoid is having a DBA restoring one database and
> forgetting the other, thereby creating inconsistencies.
> Kind regards,
> Martin|||Even tho Hari describes the correct method, you may still have problems...
Databases are backed up independently of one another. You must use the logs
and probably, roll both forward to the same point in time, otherwise you may
still get mis-matches
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Martin Perfelian" <zonk99@.hotmail.com> wrote in message
news:b9c2729.0408110037.738ea8c2@.posting.google.com...
> I am developing an application which consists of two separate products
> that both use SQL Server 2000 to store data (database A and database
> B). In the application logic we define relations between objects in
> these products.
> Is it possible to ensure that when restoring a database from a backup,
> the other database is automatically restored at the same time to keep
> data consistent between the two databases?
> What I want to avoid is having a DBA restoring one database and
> forgetting the other, thereby creating inconsistencies.
> Kind regards,
> Martinsql
that both use SQL Server 2000 to store data (database A and database
B). In the application logic we define relations between objects in
these products.
Is it possible to ensure that when restoring a database from a backup,
the other database is automatically restored at the same time to keep
data consistent between the two databases?
What I want to avoid is having a DBA restoring one database and
forgetting the other, thereby creating inconsistencies.
Kind regards,
MartinHi,
You can write a script to restore both the databases in order. So instead of
doing a manual restore you could just run the
restore script which loads both the database.
Some thing like:-
Alter database <db1> set single_user with rollback immediate
go
Restore database <db1> from disk='c:\backup\file_name.bak'
go
Alter database <db1> set Multi_user
go
Alter database <db2> set single_user with rollback immediate
go
Restore database <db2> from disk='c:\backup\file_name.bak'
go
Alter database <db2> set Multi_user
Thanks
Hari
MCDBA
"Martin Perfelian" <zonk99@.hotmail.com> wrote in message
news:b9c2729.0408110037.738ea8c2@.posting.google.com...
> I am developing an application which consists of two separate products
> that both use SQL Server 2000 to store data (database A and database
> B). In the application logic we define relations between objects in
> these products.
> Is it possible to ensure that when restoring a database from a backup,
> the other database is automatically restored at the same time to keep
> data consistent between the two databases?
> What I want to avoid is having a DBA restoring one database and
> forgetting the other, thereby creating inconsistencies.
> Kind regards,
> Martin|||Even tho Hari describes the correct method, you may still have problems...
Databases are backed up independently of one another. You must use the logs
and probably, roll both forward to the same point in time, otherwise you may
still get mis-matches
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Martin Perfelian" <zonk99@.hotmail.com> wrote in message
news:b9c2729.0408110037.738ea8c2@.posting.google.com...
> I am developing an application which consists of two separate products
> that both use SQL Server 2000 to store data (database A and database
> B). In the application logic we define relations between objects in
> these products.
> Is it possible to ensure that when restoring a database from a backup,
> the other database is automatically restored at the same time to keep
> data consistent between the two databases?
> What I want to avoid is having a DBA restoring one database and
> forgetting the other, thereby creating inconsistencies.
> Kind regards,
> Martinsql
Relate two databases
I am developing an application which consists of two separate products
that both use SQL Server 2000 to store data (database A and database
B). In the application logic we define relations between objects in
these products.
Is it possible to ensure that when restoring a database from a backup,
the other database is automatically restored at the same time to keep
data consistent between the two databases?
What I want to avoid is having a DBA restoring one database and
forgetting the other, thereby creating inconsistencies.
Kind regards,
MartinHi,
You can write a script to restore both the databases in order. So instead of
doing a manual restore you could just run the
restore script which loads both the database.
Some thing like:-
Alter database <db1> set single_user with rollback immediate
go
Restore database <db1> from disk='c:\backup\file_name.bak'
go
Alter database <db1> set Multi_user
go
Alter database <db2> set single_user with rollback immediate
go
Restore database <db2> from disk='c:\backup\file_name.bak'
go
Alter database <db2> set Multi_user
Thanks
Hari
MCDBA
"Martin Perfelian" <zonk99@.hotmail.com> wrote in message
news:b9c2729.0408110037.738ea8c2@.posting.google.com...
> I am developing an application which consists of two separate products
> that both use SQL Server 2000 to store data (database A and database
> B). In the application logic we define relations between objects in
> these products.
> Is it possible to ensure that when restoring a database from a backup,
> the other database is automatically restored at the same time to keep
> data consistent between the two databases?
> What I want to avoid is having a DBA restoring one database and
> forgetting the other, thereby creating inconsistencies.
> Kind regards,
> Martin|||Even tho Hari describes the correct method, you may still have problems...
Databases are backed up independently of one another. You must use the logs
and probably, roll both forward to the same point in time, otherwise you may
still get mis-matches
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Martin Perfelian" <zonk99@.hotmail.com> wrote in message
news:b9c2729.0408110037.738ea8c2@.posting.google.com...
> I am developing an application which consists of two separate products
> that both use SQL Server 2000 to store data (database A and database
> B). In the application logic we define relations between objects in
> these products.
> Is it possible to ensure that when restoring a database from a backup,
> the other database is automatically restored at the same time to keep
> data consistent between the two databases?
> What I want to avoid is having a DBA restoring one database and
> forgetting the other, thereby creating inconsistencies.
> Kind regards,
> Martin
that both use SQL Server 2000 to store data (database A and database
B). In the application logic we define relations between objects in
these products.
Is it possible to ensure that when restoring a database from a backup,
the other database is automatically restored at the same time to keep
data consistent between the two databases?
What I want to avoid is having a DBA restoring one database and
forgetting the other, thereby creating inconsistencies.
Kind regards,
MartinHi,
You can write a script to restore both the databases in order. So instead of
doing a manual restore you could just run the
restore script which loads both the database.
Some thing like:-
Alter database <db1> set single_user with rollback immediate
go
Restore database <db1> from disk='c:\backup\file_name.bak'
go
Alter database <db1> set Multi_user
go
Alter database <db2> set single_user with rollback immediate
go
Restore database <db2> from disk='c:\backup\file_name.bak'
go
Alter database <db2> set Multi_user
Thanks
Hari
MCDBA
"Martin Perfelian" <zonk99@.hotmail.com> wrote in message
news:b9c2729.0408110037.738ea8c2@.posting.google.com...
> I am developing an application which consists of two separate products
> that both use SQL Server 2000 to store data (database A and database
> B). In the application logic we define relations between objects in
> these products.
> Is it possible to ensure that when restoring a database from a backup,
> the other database is automatically restored at the same time to keep
> data consistent between the two databases?
> What I want to avoid is having a DBA restoring one database and
> forgetting the other, thereby creating inconsistencies.
> Kind regards,
> Martin|||Even tho Hari describes the correct method, you may still have problems...
Databases are backed up independently of one another. You must use the logs
and probably, roll both forward to the same point in time, otherwise you may
still get mis-matches
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Martin Perfelian" <zonk99@.hotmail.com> wrote in message
news:b9c2729.0408110037.738ea8c2@.posting.google.com...
> I am developing an application which consists of two separate products
> that both use SQL Server 2000 to store data (database A and database
> B). In the application logic we define relations between objects in
> these products.
> Is it possible to ensure that when restoring a database from a backup,
> the other database is automatically restored at the same time to keep
> data consistent between the two databases?
> What I want to avoid is having a DBA restoring one database and
> forgetting the other, thereby creating inconsistencies.
> Kind regards,
> Martin
Tuesday, March 20, 2012
Reinitializing Subscriptions
I am new to SQL Server Replication so I still have a cloud over my
head.
Our target system for a new application is to have a central Sql
Server 2000 SP3 with at least 50 laptops running MSDE 2000 SP3. Mobile
workforce will be updating data offline that will then need to synch
to the master and back down throughout the other laptops (some of it
anyway, dynamic filtering is next on my list). We want to use
anonymous subscriptions.
For testing, I have the Northwind database setup on Windows 2000
running Sql Server 2000 and an XP machine running MSDE. I am using
Visual Studio .NET on the XP machine to go against the ActiveX
Replication objects.
I have successfully setup merge replication. The XP machine uses .NET
code against the SQLMerge ActiveX object to use an anonymous
subscription to pull down data from the publisher. I was able to see
changes made on both databases get merged correctly. I was also able
to see conflicts and see them get resolved correctly.
One area I still fail to understand (and there are many) is
Reinitializing Subscriptions. The scenario I am trying to test is how
we roll out a new release of the existing application -- and in this
case -- changes to the database. I have read and re-read the
Reinitializing Subscriptions in Books Online but I just don't get it.
I changed my publication on the Northwind database to exclude sending
the Picture column in the Categories table. EM told me that all
subscriptions would be marked for reinitialization and that subscriber
data not synchronized first would be lost. So it works as EM and Books
Online say as I lost my data I changed on the laptop (I changed a
description field in the Categories table).
One thing that did not seem to jive with the documentation was that I
tried to synchronize the laptop database before creating the new
snapshot but the SQLMerge.Initialize method received an error saying
the snapshot was not up to date. So I did not have any way of getting
my changed data from the laptop to the server once I had made the
change to the publication.
In our production environment, the laptops will trickle in over time
complicating matters.
But basically, how do I make a change to the database that needs to
get down to the laptops AND preserve the data that has been collecting
on the laptops for weeks?
Crossing my fingers for something built-in,
Gar
There is an option called upload changes to publisher before
reinitialization.
Have a look at this property
Sub ReinitializeSubscription([bUploadBeforeReinit As Boolean = False])
I think you want to set this to True.
What will happen is changes which are pooled on the subscriber are uploaded
to the publisher before the schema changes and the merged data are sent to
the subscriber.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Now available on Amazon.com
http://www.amazon.com/gp/product/off...?condition=all
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"post messages" <post.messages@.gmail.com> wrote in message
news:c45b744b.0502031406.5dbd12bc@.posting.google.c om...
> I am new to SQL Server Replication so I still have a cloud over my
> head.
> Our target system for a new application is to have a central Sql
> Server 2000 SP3 with at least 50 laptops running MSDE 2000 SP3. Mobile
> workforce will be updating data offline that will then need to synch
> to the master and back down throughout the other laptops (some of it
> anyway, dynamic filtering is next on my list). We want to use
> anonymous subscriptions.
> For testing, I have the Northwind database setup on Windows 2000
> running Sql Server 2000 and an XP machine running MSDE. I am using
> Visual Studio .NET on the XP machine to go against the ActiveX
> Replication objects.
> I have successfully setup merge replication. The XP machine uses .NET
> code against the SQLMerge ActiveX object to use an anonymous
> subscription to pull down data from the publisher. I was able to see
> changes made on both databases get merged correctly. I was also able
> to see conflicts and see them get resolved correctly.
> One area I still fail to understand (and there are many) is
> Reinitializing Subscriptions. The scenario I am trying to test is how
> we roll out a new release of the existing application -- and in this
> case -- changes to the database. I have read and re-read the
> Reinitializing Subscriptions in Books Online but I just don't get it.
> I changed my publication on the Northwind database to exclude sending
> the Picture column in the Categories table. EM told me that all
> subscriptions would be marked for reinitialization and that subscriber
> data not synchronized first would be lost. So it works as EM and Books
> Online say as I lost my data I changed on the laptop (I changed a
> description field in the Categories table).
> One thing that did not seem to jive with the documentation was that I
> tried to synchronize the laptop database before creating the new
> snapshot but the SQLMerge.Initialize method received an error saying
> the snapshot was not up to date. So I did not have any way of getting
> my changed data from the laptop to the server once I had made the
> change to the publication.
> In our production environment, the laptops will trickle in over time
> complicating matters.
> But basically, how do I make a change to the database that needs to
> get down to the laptops AND preserve the data that has been collecting
> on the laptops for weeks?
> Crossing my fingers for something built-in,
> Gar
|||Hi Hilary,
Thanks for the reply. Based on what you said I did more searching in
the newsgroup. I found a post that is a little dated, didn't get
promising answers and is still important to the success of trying to
send out database schema upgrades through replication.
Detect Reinitialization is required.
http://groups-beta.google.com/group/...public.sqlser
ver.replication%26q%3DReinitializeSubscription%26q t_g%3D1%26searchnow%3DSearch+this+group%26&_doneTi tle=Back+to+Search&&d#62baf38ab39170d6
Since the laptops are going to have compiled .NET code calling the
merge object, how should they detect that they need to call
ReinitializeSubscription(true) before calling the usual Initialize,
Run, Terminate methods? My understanding is it is wrong to always call
ReinitializeSubscription(true) -- it has to be called only when
necessary.
Thanks,
Gary
head.
Our target system for a new application is to have a central Sql
Server 2000 SP3 with at least 50 laptops running MSDE 2000 SP3. Mobile
workforce will be updating data offline that will then need to synch
to the master and back down throughout the other laptops (some of it
anyway, dynamic filtering is next on my list). We want to use
anonymous subscriptions.
For testing, I have the Northwind database setup on Windows 2000
running Sql Server 2000 and an XP machine running MSDE. I am using
Visual Studio .NET on the XP machine to go against the ActiveX
Replication objects.
I have successfully setup merge replication. The XP machine uses .NET
code against the SQLMerge ActiveX object to use an anonymous
subscription to pull down data from the publisher. I was able to see
changes made on both databases get merged correctly. I was also able
to see conflicts and see them get resolved correctly.
One area I still fail to understand (and there are many) is
Reinitializing Subscriptions. The scenario I am trying to test is how
we roll out a new release of the existing application -- and in this
case -- changes to the database. I have read and re-read the
Reinitializing Subscriptions in Books Online but I just don't get it.
I changed my publication on the Northwind database to exclude sending
the Picture column in the Categories table. EM told me that all
subscriptions would be marked for reinitialization and that subscriber
data not synchronized first would be lost. So it works as EM and Books
Online say as I lost my data I changed on the laptop (I changed a
description field in the Categories table).
One thing that did not seem to jive with the documentation was that I
tried to synchronize the laptop database before creating the new
snapshot but the SQLMerge.Initialize method received an error saying
the snapshot was not up to date. So I did not have any way of getting
my changed data from the laptop to the server once I had made the
change to the publication.
In our production environment, the laptops will trickle in over time
complicating matters.
But basically, how do I make a change to the database that needs to
get down to the laptops AND preserve the data that has been collecting
on the laptops for weeks?
Crossing my fingers for something built-in,
Gar
There is an option called upload changes to publisher before
reinitialization.
Have a look at this property
Sub ReinitializeSubscription([bUploadBeforeReinit As Boolean = False])
I think you want to set this to True.
What will happen is changes which are pooled on the subscriber are uploaded
to the publisher before the schema changes and the merged data are sent to
the subscriber.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Now available on Amazon.com
http://www.amazon.com/gp/product/off...?condition=all
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"post messages" <post.messages@.gmail.com> wrote in message
news:c45b744b.0502031406.5dbd12bc@.posting.google.c om...
> I am new to SQL Server Replication so I still have a cloud over my
> head.
> Our target system for a new application is to have a central Sql
> Server 2000 SP3 with at least 50 laptops running MSDE 2000 SP3. Mobile
> workforce will be updating data offline that will then need to synch
> to the master and back down throughout the other laptops (some of it
> anyway, dynamic filtering is next on my list). We want to use
> anonymous subscriptions.
> For testing, I have the Northwind database setup on Windows 2000
> running Sql Server 2000 and an XP machine running MSDE. I am using
> Visual Studio .NET on the XP machine to go against the ActiveX
> Replication objects.
> I have successfully setup merge replication. The XP machine uses .NET
> code against the SQLMerge ActiveX object to use an anonymous
> subscription to pull down data from the publisher. I was able to see
> changes made on both databases get merged correctly. I was also able
> to see conflicts and see them get resolved correctly.
> One area I still fail to understand (and there are many) is
> Reinitializing Subscriptions. The scenario I am trying to test is how
> we roll out a new release of the existing application -- and in this
> case -- changes to the database. I have read and re-read the
> Reinitializing Subscriptions in Books Online but I just don't get it.
> I changed my publication on the Northwind database to exclude sending
> the Picture column in the Categories table. EM told me that all
> subscriptions would be marked for reinitialization and that subscriber
> data not synchronized first would be lost. So it works as EM and Books
> Online say as I lost my data I changed on the laptop (I changed a
> description field in the Categories table).
> One thing that did not seem to jive with the documentation was that I
> tried to synchronize the laptop database before creating the new
> snapshot but the SQLMerge.Initialize method received an error saying
> the snapshot was not up to date. So I did not have any way of getting
> my changed data from the laptop to the server once I had made the
> change to the publication.
> In our production environment, the laptops will trickle in over time
> complicating matters.
> But basically, how do I make a change to the database that needs to
> get down to the laptops AND preserve the data that has been collecting
> on the laptops for weeks?
> Crossing my fingers for something built-in,
> Gar
|||Hi Hilary,
Thanks for the reply. Based on what you said I did more searching in
the newsgroup. I found a post that is a little dated, didn't get
promising answers and is still important to the success of trying to
send out database schema upgrades through replication.
Detect Reinitialization is required.
http://groups-beta.google.com/group/...public.sqlser
ver.replication%26q%3DReinitializeSubscription%26q t_g%3D1%26searchnow%3DSearch+this+group%26&_doneTi tle=Back+to+Search&&d#62baf38ab39170d6
Since the laptops are going to have compiled .NET code calling the
merge object, how should they detect that they need to call
ReinitializeSubscription(true) before calling the usual Initialize,
Run, Terminate methods? My understanding is it is wrong to always call
ReinitializeSubscription(true) -- it has to be called only when
necessary.
Thanks,
Gary
Monday, March 12, 2012
Re-indexing required?
Hi, I'm developing a database driven application that, besides everything
else, it keeps a log file of all the actions a user has taken during his use
of the app.
This log file is stored in a database table that has a primary key of type
"bigint" that auto increments (1, 1).
If ~100 to ~500 actions (insertions, deletions) are made to this table per
day, how long before I need to re-index the table? Do I need to re-index it
at all?
Thanks in advance,
Peter
pnp,
When are your maintenance windows? Do you have ANY maintenance windows? If you get a chance it would be good to recreate your indexes using the CREATE INDEX statement and the DROP_EXISTING clause - however test this for performance against DBCC DBREINDEX.
Remember that these are OFFLINE operations and will lock tables.
If you don't have a maintenance window, then measure your defragmentation using DBCC SHOWCONTIG. Based on a value acceptable to you, you can rebuild your index with DBCC INDEXDEFRAG - this is an ONLINE operation and will not lock tables, however it is not
as thorough as the other methods.
My advice would be to run DBCC SHOWCONTIG first before doing a rebuild, and then decide when to do it based on your maintenance windows. From the activity you describe it sounds like you may need to monitor it daily with DBCC SHOWCONTIG.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
|||Hi,
Execute the below command with in the database to identify the
fragmentation,
DBCC SHOWCONTIG ('table_name') WITH FAST
DBCC SHOWCONTIG determines whether the table is heavily fragmented. Table
fragmentation occurs through the process of data modifications (INSERT,
UPDATE, and DELETE statements) made against the table. This
will cause additional page reads results in slow performance.
How to over come the Fragmentation:
1. Drop and re-create a clustered index.
2. DBCC INDEXDEFRAG (Refer books online)
Have a look into DBCC SHOWCONTIG in books online for more information.
Thanks
Hari
MCDBA
"pnp" <pnp.at.softlab.ece.ntua.gr> wrote in message
news:eoKusYJGEHA.3880@.TK2MSFTNGP10.phx.gbl...
> Hi, I'm developing a database driven application that, besides everything
> else, it keeps a log file of all the actions a user has taken during his
use
> of the app.
> This log file is stored in a database table that has a primary key of type
> "bigint" that auto increments (1, 1).
> If ~100 to ~500 actions (insertions, deletions) are made to this table per
> day, how long before I need to re-index the table? Do I need to re-index
it
> at all?
> Thanks in advance,
> Peter
>
|||On a slighly different thread.
I'd be curious to know how SQL Server indexes deal with incremental keys.
Other RDBMS implemented hash indexes as btrees can become lopsided with
these keys.
Paul Cahill
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:eoOuuoJGEHA.1180@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Execute the below command with in the database to identify the
> fragmentation,
> DBCC SHOWCONTIG ('table_name') WITH FAST
> DBCC SHOWCONTIG determines whether the table is heavily fragmented. Table
> fragmentation occurs through the process of data modifications (INSERT,
> UPDATE, and DELETE statements) made against the table. This
> will cause additional page reads results in slow performance.
> How to over come the Fragmentation:
> 1. Drop and re-create a clustered index.
> 2. DBCC INDEXDEFRAG (Refer books online)
> Have a look into DBCC SHOWCONTIG in books online for more information.
> Thanks
> Hari
> MCDBA
>
> "pnp" <pnp.at.softlab.ece.ntua.gr> wrote in message
> news:eoKusYJGEHA.3880@.TK2MSFTNGP10.phx.gbl...
everything
> use
type
per
> it
>
|||To add to all the other (sound) advice, please checkout the excellent
whitepaper at
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
It gives extensive details on how to diagnose and cope with fragmentation,
including working out which indexes to focus on and even whether you need to
bother, based on your workload.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"pnp" <pnp.at.softlab.ece.ntua.gr> wrote in message
news:eoKusYJGEHA.3880@.TK2MSFTNGP10.phx.gbl...
> Hi, I'm developing a database driven application that, besides everything
> else, it keeps a log file of all the actions a user has taken during his
use
> of the app.
> This log file is stored in a database table that has a primary key of type
> "bigint" that auto increments (1, 1).
> If ~100 to ~500 actions (insertions, deletions) are made to this table per
> day, how long before I need to re-index the table? Do I need to re-index
it
> at all?
> Thanks in advance,
> Peter
>
else, it keeps a log file of all the actions a user has taken during his use
of the app.
This log file is stored in a database table that has a primary key of type
"bigint" that auto increments (1, 1).
If ~100 to ~500 actions (insertions, deletions) are made to this table per
day, how long before I need to re-index the table? Do I need to re-index it
at all?
Thanks in advance,
Peter
pnp,
When are your maintenance windows? Do you have ANY maintenance windows? If you get a chance it would be good to recreate your indexes using the CREATE INDEX statement and the DROP_EXISTING clause - however test this for performance against DBCC DBREINDEX.
Remember that these are OFFLINE operations and will lock tables.
If you don't have a maintenance window, then measure your defragmentation using DBCC SHOWCONTIG. Based on a value acceptable to you, you can rebuild your index with DBCC INDEXDEFRAG - this is an ONLINE operation and will not lock tables, however it is not
as thorough as the other methods.
My advice would be to run DBCC SHOWCONTIG first before doing a rebuild, and then decide when to do it based on your maintenance windows. From the activity you describe it sounds like you may need to monitor it daily with DBCC SHOWCONTIG.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
|||Hi,
Execute the below command with in the database to identify the
fragmentation,
DBCC SHOWCONTIG ('table_name') WITH FAST
DBCC SHOWCONTIG determines whether the table is heavily fragmented. Table
fragmentation occurs through the process of data modifications (INSERT,
UPDATE, and DELETE statements) made against the table. This
will cause additional page reads results in slow performance.
How to over come the Fragmentation:
1. Drop and re-create a clustered index.
2. DBCC INDEXDEFRAG (Refer books online)
Have a look into DBCC SHOWCONTIG in books online for more information.
Thanks
Hari
MCDBA
"pnp" <pnp.at.softlab.ece.ntua.gr> wrote in message
news:eoKusYJGEHA.3880@.TK2MSFTNGP10.phx.gbl...
> Hi, I'm developing a database driven application that, besides everything
> else, it keeps a log file of all the actions a user has taken during his
use
> of the app.
> This log file is stored in a database table that has a primary key of type
> "bigint" that auto increments (1, 1).
> If ~100 to ~500 actions (insertions, deletions) are made to this table per
> day, how long before I need to re-index the table? Do I need to re-index
it
> at all?
> Thanks in advance,
> Peter
>
|||On a slighly different thread.
I'd be curious to know how SQL Server indexes deal with incremental keys.
Other RDBMS implemented hash indexes as btrees can become lopsided with
these keys.
Paul Cahill
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:eoOuuoJGEHA.1180@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Execute the below command with in the database to identify the
> fragmentation,
> DBCC SHOWCONTIG ('table_name') WITH FAST
> DBCC SHOWCONTIG determines whether the table is heavily fragmented. Table
> fragmentation occurs through the process of data modifications (INSERT,
> UPDATE, and DELETE statements) made against the table. This
> will cause additional page reads results in slow performance.
> How to over come the Fragmentation:
> 1. Drop and re-create a clustered index.
> 2. DBCC INDEXDEFRAG (Refer books online)
> Have a look into DBCC SHOWCONTIG in books online for more information.
> Thanks
> Hari
> MCDBA
>
> "pnp" <pnp.at.softlab.ece.ntua.gr> wrote in message
> news:eoKusYJGEHA.3880@.TK2MSFTNGP10.phx.gbl...
everything
> use
type
per
> it
>
|||To add to all the other (sound) advice, please checkout the excellent
whitepaper at
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
It gives extensive details on how to diagnose and cope with fragmentation,
including working out which indexes to focus on and even whether you need to
bother, based on your workload.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"pnp" <pnp.at.softlab.ece.ntua.gr> wrote in message
news:eoKusYJGEHA.3880@.TK2MSFTNGP10.phx.gbl...
> Hi, I'm developing a database driven application that, besides everything
> else, it keeps a log file of all the actions a user has taken during his
use
> of the app.
> This log file is stored in a database table that has a primary key of type
> "bigint" that auto increments (1, 1).
> If ~100 to ~500 actions (insertions, deletions) are made to this table per
> day, how long before I need to re-index the table? Do I need to re-index
it
> at all?
> Thanks in advance,
> Peter
>
Re-indexing required?
Hi, I'm developing a database driven application that, besides everything
else, it keeps a log file of all the actions a user has taken during his use
of the app.
This log file is stored in a database table that has a primary key of type
"bigint" that auto increments (1, 1).
If ~100 to ~500 actions (insertions, deletions) are made to this table per
day, how long before I need to re-index the table? Do I need to re-index it
at all?
Thanks in advance,
Peterpnp,
When are your maintenance windows? Do you have ANY maintenance windows? If y
ou get a chance it would be good to recreate your indexes using the CREATE I
NDEX statement and the DROP_EXISTING clause - however test this for performa
nce against DBCC DBREINDEX.
Remember that these are OFFLINE operations and will lock tables.
If you don't have a maintenance window, then measure your defragmentation us
ing DBCC SHOWCONTIG. Based on a value acceptable to you, you can rebuild you
r index with DBCC INDEXDEFRAG - this is an ONLINE operation and will not loc
k tables, however it is not
as thorough as the other methods.
My advice would be to run DBCC SHOWCONTIG first before doing a rebuild, and
then decide when to do it based on your maintenance windows. From the activi
ty you describe it sounds like you may need to monitor it daily with DBCC SH
OWCONTIG.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk|||Hi,
Execute the below command with in the database to identify the
fragmentation,
DBCC SHOWCONTIG ('table_name') WITH FAST
DBCC SHOWCONTIG determines whether the table is heavily fragmented. Table
fragmentation occurs through the process of data modifications (INSERT,
UPDATE, and DELETE statements) made against the table. This
will cause additional page reads results in slow performance.
How to over come the Fragmentation:
1. Drop and re-create a clustered index.
2. DBCC INDEXDEFRAG (Refer books online)
Have a look into DBCC SHOWCONTIG in books online for more information.
Thanks
Hari
MCDBA
"pnp" <pnp.at.softlab.ece.ntua.gr> wrote in message
news:eoKusYJGEHA.3880@.TK2MSFTNGP10.phx.gbl...
> Hi, I'm developing a database driven application that, besides everything
> else, it keeps a log file of all the actions a user has taken during his
use
> of the app.
> This log file is stored in a database table that has a primary key of type
> "bigint" that auto increments (1, 1).
> If ~100 to ~500 actions (insertions, deletions) are made to this table per
> day, how long before I need to re-index the table? Do I need to re-index
it
> at all?
> Thanks in advance,
> Peter
>|||On a slighly different thread.
I'd be curious to know how SQL Server indexes deal with incremental keys.
Other RDBMS implemented hash indexes as btrees can become lopsided with
these keys.
Paul Cahill
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:eoOuuoJGEHA.1180@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Execute the below command with in the database to identify the
> fragmentation,
> DBCC SHOWCONTIG ('table_name') WITH FAST
> DBCC SHOWCONTIG determines whether the table is heavily fragmented. Table
> fragmentation occurs through the process of data modifications (INSERT,
> UPDATE, and DELETE statements) made against the table. This
> will cause additional page reads results in slow performance.
> How to over come the Fragmentation:
> 1. Drop and re-create a clustered index.
> 2. DBCC INDEXDEFRAG (Refer books online)
> Have a look into DBCC SHOWCONTIG in books online for more information.
> Thanks
> Hari
> MCDBA
>
> "pnp" <pnp.at.softlab.ece.ntua.gr> wrote in message
> news:eoKusYJGEHA.3880@.TK2MSFTNGP10.phx.gbl...
everything
> use
type
per
> it
>|||To add to all the other (sound) advice, please checkout the excellent
whitepaper at
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
It gives extensive details on how to diagnose and cope with fragmentation,
including working out which indexes to focus on and even whether you need to
bother, based on your workload.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"pnp" <pnp.at.softlab.ece.ntua.gr> wrote in message
news:eoKusYJGEHA.3880@.TK2MSFTNGP10.phx.gbl...
> Hi, I'm developing a database driven application that, besides everything
> else, it keeps a log file of all the actions a user has taken during his
use
> of the app.
> This log file is stored in a database table that has a primary key of type
> "bigint" that auto increments (1, 1).
> If ~100 to ~500 actions (insertions, deletions) are made to this table per
> day, how long before I need to re-index the table? Do I need to re-index
it
> at all?
> Thanks in advance,
> Peter
>
else, it keeps a log file of all the actions a user has taken during his use
of the app.
This log file is stored in a database table that has a primary key of type
"bigint" that auto increments (1, 1).
If ~100 to ~500 actions (insertions, deletions) are made to this table per
day, how long before I need to re-index the table? Do I need to re-index it
at all?
Thanks in advance,
Peterpnp,
When are your maintenance windows? Do you have ANY maintenance windows? If y
ou get a chance it would be good to recreate your indexes using the CREATE I
NDEX statement and the DROP_EXISTING clause - however test this for performa
nce against DBCC DBREINDEX.
Remember that these are OFFLINE operations and will lock tables.
If you don't have a maintenance window, then measure your defragmentation us
ing DBCC SHOWCONTIG. Based on a value acceptable to you, you can rebuild you
r index with DBCC INDEXDEFRAG - this is an ONLINE operation and will not loc
k tables, however it is not
as thorough as the other methods.
My advice would be to run DBCC SHOWCONTIG first before doing a rebuild, and
then decide when to do it based on your maintenance windows. From the activi
ty you describe it sounds like you may need to monitor it daily with DBCC SH
OWCONTIG.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk|||Hi,
Execute the below command with in the database to identify the
fragmentation,
DBCC SHOWCONTIG ('table_name') WITH FAST
DBCC SHOWCONTIG determines whether the table is heavily fragmented. Table
fragmentation occurs through the process of data modifications (INSERT,
UPDATE, and DELETE statements) made against the table. This
will cause additional page reads results in slow performance.
How to over come the Fragmentation:
1. Drop and re-create a clustered index.
2. DBCC INDEXDEFRAG (Refer books online)
Have a look into DBCC SHOWCONTIG in books online for more information.
Thanks
Hari
MCDBA
"pnp" <pnp.at.softlab.ece.ntua.gr> wrote in message
news:eoKusYJGEHA.3880@.TK2MSFTNGP10.phx.gbl...
> Hi, I'm developing a database driven application that, besides everything
> else, it keeps a log file of all the actions a user has taken during his
use
> of the app.
> This log file is stored in a database table that has a primary key of type
> "bigint" that auto increments (1, 1).
> If ~100 to ~500 actions (insertions, deletions) are made to this table per
> day, how long before I need to re-index the table? Do I need to re-index
it
> at all?
> Thanks in advance,
> Peter
>|||On a slighly different thread.
I'd be curious to know how SQL Server indexes deal with incremental keys.
Other RDBMS implemented hash indexes as btrees can become lopsided with
these keys.
Paul Cahill
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:eoOuuoJGEHA.1180@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Execute the below command with in the database to identify the
> fragmentation,
> DBCC SHOWCONTIG ('table_name') WITH FAST
> DBCC SHOWCONTIG determines whether the table is heavily fragmented. Table
> fragmentation occurs through the process of data modifications (INSERT,
> UPDATE, and DELETE statements) made against the table. This
> will cause additional page reads results in slow performance.
> How to over come the Fragmentation:
> 1. Drop and re-create a clustered index.
> 2. DBCC INDEXDEFRAG (Refer books online)
> Have a look into DBCC SHOWCONTIG in books online for more information.
> Thanks
> Hari
> MCDBA
>
> "pnp" <pnp.at.softlab.ece.ntua.gr> wrote in message
> news:eoKusYJGEHA.3880@.TK2MSFTNGP10.phx.gbl...
everything
> use
type
per
> it
>|||To add to all the other (sound) advice, please checkout the excellent
whitepaper at
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
It gives extensive details on how to diagnose and cope with fragmentation,
including working out which indexes to focus on and even whether you need to
bother, based on your workload.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"pnp" <pnp.at.softlab.ece.ntua.gr> wrote in message
news:eoKusYJGEHA.3880@.TK2MSFTNGP10.phx.gbl...
> Hi, I'm developing a database driven application that, besides everything
> else, it keeps a log file of all the actions a user has taken during his
use
> of the app.
> This log file is stored in a database table that has a primary key of type
> "bigint" that auto increments (1, 1).
> If ~100 to ~500 actions (insertions, deletions) are made to this table per
> day, how long before I need to re-index the table? Do I need to re-index
it
> at all?
> Thanks in advance,
> Peter
>
Friday, March 9, 2012
ReHigh memory usage-SQLServer 7
Hi to all,
we are using MSSQLServer 7 for both Win2K and Win95/98 OS.
My problem is, that as we run our application overnight,
inserting records in the table, we notice that the memory
usage of sqlserver.exe in the Task Bar Manager keep on
increasing, thus "eats-up" the available physical memory.
How can we limit the memory usage of the sqlserver.exe?
coz in a low physical memory we could not open any
application unless we will stop/restart SQLServer to free-
up mem. we are just using 260MB of RAM.
thank you and more power.Hi Alvin.
SQL Server is designed to use up the available memory on a
computer. This is because it is configured (by default) to
behave like the only software running on the computer.
When installed on a dedicated (so often the case) this is
usually true - you want SQL Server to make use of the
available memory to run queries as quickly as possible.
If you're installing SQL Server on a computer where it is
NOT the only software running (ie not a dedicated server -
such as a desktop), you can configure SQL Server to use a
maximum (and / or a minimum) amount of memory.
Easiest way to do this is from the SQL Enterprise Manager.
As long as you've registered your EM connection to connect
as a systadmin, you should be able to simply right click
on the SQL Server in the EM, select properties, go to the
memory tab & set your memory settings.
HTH
Regards,
Greg Linwood
SQL Server MVP
>--Original Message--
>Hi to all,
>we are using MSSQLServer 7 for both Win2K and Win95/98
OS.
>My problem is, that as we run our application overnight,
>inserting records in the table, we notice that the memory
>usage of sqlserver.exe in the Task Bar Manager keep on
>increasing, thus "eats-up" the available physical memory.
>How can we limit the memory usage of the sqlserver.exe?
>coz in a low physical memory we could not open any
>application unless we will stop/restart SQLServer to free-
>up mem. we are just using 260MB of RAM.
>thank you and more power.
>.
>
we are using MSSQLServer 7 for both Win2K and Win95/98 OS.
My problem is, that as we run our application overnight,
inserting records in the table, we notice that the memory
usage of sqlserver.exe in the Task Bar Manager keep on
increasing, thus "eats-up" the available physical memory.
How can we limit the memory usage of the sqlserver.exe?
coz in a low physical memory we could not open any
application unless we will stop/restart SQLServer to free-
up mem. we are just using 260MB of RAM.
thank you and more power.Hi Alvin.
SQL Server is designed to use up the available memory on a
computer. This is because it is configured (by default) to
behave like the only software running on the computer.
When installed on a dedicated (so often the case) this is
usually true - you want SQL Server to make use of the
available memory to run queries as quickly as possible.
If you're installing SQL Server on a computer where it is
NOT the only software running (ie not a dedicated server -
such as a desktop), you can configure SQL Server to use a
maximum (and / or a minimum) amount of memory.
Easiest way to do this is from the SQL Enterprise Manager.
As long as you've registered your EM connection to connect
as a systadmin, you should be able to simply right click
on the SQL Server in the EM, select properties, go to the
memory tab & set your memory settings.
HTH
Regards,
Greg Linwood
SQL Server MVP
>--Original Message--
>Hi to all,
>we are using MSSQLServer 7 for both Win2K and Win95/98
OS.
>My problem is, that as we run our application overnight,
>inserting records in the table, we notice that the memory
>usage of sqlserver.exe in the Task Bar Manager keep on
>increasing, thus "eats-up" the available physical memory.
>How can we limit the memory usage of the sqlserver.exe?
>coz in a low physical memory we could not open any
>application unless we will stop/restart SQLServer to free-
>up mem. we are just using 260MB of RAM.
>thank you and more power.
>.
>
Regular expression in SQL 2000
Hi All,
We are migrating a web application from oracle to SQL Server 2000.We have
used regular expression extensively in Oracle.
Is it possible to use Regular expressions in SQL 2000 queries'
GuhanCheck out the LIKE operator and the PATINDEX function. It isn't the full
regex syntax but basic wildcards and search patterns are supported.
David Portas
SQL Server MVP
--|||Can you give more details on what you are using. As David said in SQL 2K you
always have PATINDEX to help. Yet not complete in the capability. Just
wanted to add that in SQL Server 2005 you can use the capability of CLR to
perform
regex work.
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
"Guhanath" <Guhanath@.discussions.microsoft.com> wrote in message
news:D2295C71-1483-4D89-B24C-5550FA00D7CE@.microsoft.com...
> Hi All,
> We are migrating a web application from oracle to SQL Server 2000.We
have
> used regular expression extensively in Oracle.
> Is it possible to use Regular expressions in SQL 2000 queries'
>
> --
> Guhan|||Have a look at
http://blogs.msdn.com/khen1234/arch.../11/416392.aspx
http://blogs.msdn.com/khen1234/arch.../19/420209.aspx
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Guhanath" <Guhanath@.discussions.microsoft.com> wrote in message
news:D2295C71-1483-4D89-B24C-5550FA00D7CE@.microsoft.com...
> Hi All,
> We are migrating a web application from oracle to SQL Server 2000.We
> have
> used regular expression extensively in Oracle.
> Is it possible to use Regular expressions in SQL 2000 queries'
>
> --
> Guhan
We are migrating a web application from oracle to SQL Server 2000.We have
used regular expression extensively in Oracle.
Is it possible to use Regular expressions in SQL 2000 queries'
GuhanCheck out the LIKE operator and the PATINDEX function. It isn't the full
regex syntax but basic wildcards and search patterns are supported.
David Portas
SQL Server MVP
--|||Can you give more details on what you are using. As David said in SQL 2K you
always have PATINDEX to help. Yet not complete in the capability. Just
wanted to add that in SQL Server 2005 you can use the capability of CLR to
perform
regex work.HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
"Guhanath" <Guhanath@.discussions.microsoft.com> wrote in message
news:D2295C71-1483-4D89-B24C-5550FA00D7CE@.microsoft.com...
> Hi All,
> We are migrating a web application from oracle to SQL Server 2000.We
have
> used regular expression extensively in Oracle.
> Is it possible to use Regular expressions in SQL 2000 queries'
>
> --
> Guhan|||Have a look at
http://blogs.msdn.com/khen1234/arch.../11/416392.aspx
http://blogs.msdn.com/khen1234/arch.../19/420209.aspx
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Guhanath" <Guhanath@.discussions.microsoft.com> wrote in message
news:D2295C71-1483-4D89-B24C-5550FA00D7CE@.microsoft.com...
> Hi All,
> We are migrating a web application from oracle to SQL Server 2000.We
> have
> used regular expression extensively in Oracle.
> Is it possible to use Regular expressions in SQL 2000 queries'
>
> --
> Guhan
Wednesday, March 7, 2012
registry replication consistency in MSCS
Hi,
I'm using MSCS registry checkpointing to make sure my application data
written in registry will be available to other nodes when they become online.
I was wondering if the checkpoint operation is synchronous to the registry
write or is it possible that the registry write would be acknowledged but the
checkpoint would be delayed?
If the second option is possible than how can I guarentee that if a power
failure occurs right after the registry write was acknowledged that the
checkpoint was indeed written to quorum and would be available for the second
node when it becomes online?
Hi
Well, if your SAN and servers are not on UPS, and you have a power failure,
the OS might be in the middle of a write, resulting in a partial write. This
may lead to corruption in the registry or quorum drive.
If you have a cluster for high availability, you should have a reliable
power system, or at least a way to shut it down in an orderly fashion once
the UPS goes onto low power.
Unless you have an orderly server shutdown, don't expect anything to be 100%
once it comes up.
Why are you using the registry to persist data? Databases or XML files do
the job better.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"smadar" <smadar@.discussions.microsoft.com> wrote in message
news:4BB152CC-4455-4CDF-91D2-B39EEF2D1D6D@.microsoft.com...
> Hi,
> I'm using MSCS registry checkpointing to make sure my application data
> written in registry will be available to other nodes when they become
> online.
> I was wondering if the checkpoint operation is synchronous to the registry
> write or is it possible that the registry write would be acknowledged but
> the
> checkpoint would be delayed?
> If the second option is possible than how can I guarentee that if a power
> failure occurs right after the registry write was acknowledged that the
> checkpoint was indeed written to quorum and would be available for the
> second
> node when it becomes online?
|||Hi,
Let me clarify my question:
My worries are not on the shutdown scenario, but on the behavior of the
checkpoint mechanism.
I'm using the win32 reg function: ZwSetValueKey (from my driver) to set a
key registered with a cluster resource.
If this call returns success can I assume that the data was already written
to a checkpoint on the quorum device? Is it synchronous to the registry write
or the quorum may be updated in a later time?
Thanks.
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> Well, if your SAN and servers are not on UPS, and you have a power failure,
> the OS might be in the middle of a write, resulting in a partial write. This
> may lead to corruption in the registry or quorum drive.
> If you have a cluster for high availability, you should have a reliable
> power system, or at least a way to shut it down in an orderly fashion once
> the UPS goes onto low power.
> Unless you have an orderly server shutdown, don't expect anything to be 100%
> once it comes up.
> Why are you using the registry to persist data? Databases or XML files do
> the job better.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "smadar" <smadar@.discussions.microsoft.com> wrote in message
> news:4BB152CC-4455-4CDF-91D2-B39EEF2D1D6D@.microsoft.com...
>
>
I'm using MSCS registry checkpointing to make sure my application data
written in registry will be available to other nodes when they become online.
I was wondering if the checkpoint operation is synchronous to the registry
write or is it possible that the registry write would be acknowledged but the
checkpoint would be delayed?
If the second option is possible than how can I guarentee that if a power
failure occurs right after the registry write was acknowledged that the
checkpoint was indeed written to quorum and would be available for the second
node when it becomes online?
Hi
Well, if your SAN and servers are not on UPS, and you have a power failure,
the OS might be in the middle of a write, resulting in a partial write. This
may lead to corruption in the registry or quorum drive.
If you have a cluster for high availability, you should have a reliable
power system, or at least a way to shut it down in an orderly fashion once
the UPS goes onto low power.
Unless you have an orderly server shutdown, don't expect anything to be 100%
once it comes up.
Why are you using the registry to persist data? Databases or XML files do
the job better.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"smadar" <smadar@.discussions.microsoft.com> wrote in message
news:4BB152CC-4455-4CDF-91D2-B39EEF2D1D6D@.microsoft.com...
> Hi,
> I'm using MSCS registry checkpointing to make sure my application data
> written in registry will be available to other nodes when they become
> online.
> I was wondering if the checkpoint operation is synchronous to the registry
> write or is it possible that the registry write would be acknowledged but
> the
> checkpoint would be delayed?
> If the second option is possible than how can I guarentee that if a power
> failure occurs right after the registry write was acknowledged that the
> checkpoint was indeed written to quorum and would be available for the
> second
> node when it becomes online?
|||Hi,
Let me clarify my question:
My worries are not on the shutdown scenario, but on the behavior of the
checkpoint mechanism.
I'm using the win32 reg function: ZwSetValueKey (from my driver) to set a
key registered with a cluster resource.
If this call returns success can I assume that the data was already written
to a checkpoint on the quorum device? Is it synchronous to the registry write
or the quorum may be updated in a later time?
Thanks.
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> Well, if your SAN and servers are not on UPS, and you have a power failure,
> the OS might be in the middle of a write, resulting in a partial write. This
> may lead to corruption in the registry or quorum drive.
> If you have a cluster for high availability, you should have a reliable
> power system, or at least a way to shut it down in an orderly fashion once
> the UPS goes onto low power.
> Unless you have an orderly server shutdown, don't expect anything to be 100%
> once it comes up.
> Why are you using the registry to persist data? Databases or XML files do
> the job better.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "smadar" <smadar@.discussions.microsoft.com> wrote in message
> news:4BB152CC-4455-4CDF-91D2-B39EEF2D1D6D@.microsoft.com...
>
>
Registry key setting for MS SQL Server
I am installing my application thru Installshield Professional 5. How
to know whether MS SQL Server is installed or not on the machine on
which i am installing my application. What is the registry key thru
which we can find this."harish" <harishksh@.yahoo.com> wrote in message
news:ff46df6e.0401090401.732c0ecb@.posting.google.c om...
> I am installing my application thru Installshield Professional 5. How
> to know whether MS SQL Server is installed or not on the machine on
> which i am installing my application. What is the registry key thru
> which we can find this.
to know whether MS SQL Server is installed or not on the machine on
which i am installing my application. What is the registry key thru
which we can find this."harish" <harishksh@.yahoo.com> wrote in message
news:ff46df6e.0401090401.732c0ecb@.posting.google.c om...
> I am installing my application thru Installshield Professional 5. How
> to know whether MS SQL Server is installed or not on the machine on
> which i am installing my application. What is the registry key thru
> which we can find this.
Have a look at points 12 and 13 in this KB article:
http://support.microsoft.com/defaul...6&Product=sql2k
Simon
Registry entries not found
I have a Notification Services instance/application running on the same machine that runs the website that manages the Subscribers. The instance is registered and it is running perfectly fine as a Windows service.
However, when running the subscription-management website i get the following error:
"The registry entries for the specified instance of Notification Services could not be found."
Please note, that the website is running on the same machine as the instance (and the SQL Server as well).
Any suggestions on how to solve this problem will be greatly appreciated.
Thanks.
Double check the string you are using to specify the instance name. It should be what you've defined in the following node of the ICF. <!--Instance name-->
<InstanceName>MyInstance</InstanceName>
It's not the name of the service.
HTH...
Joe|||
Thanks for your reply.
Yes I know that the service name and the instance name is not the same. My instance name is PWNotifications and the name of the service (which is running fine by the way - chronicle tables are updated as scheduled) is NS$PWNotifications.
The error I get is in the ASP.NET code when this statement gets executed:
new Instance("PWNotifications");
It is very likely that there are some security-restrictions of the process running ASP.NET, I haven't looked into that yet, but the research I done so far tells my that this error is not security related - it is simply a matter of the service not being found.
I have also checked the registry - there is plenty of NS$PWNotifications-related nodes in there.
Another thing that might be relevant: The subscription-management part (the website) was originally written for an application running in .NET 1.x and Notification Services running on MS SQL 2000. It has now been converted to .NET 2.0, but otherwise it has not been changed. Is the [new Instance("PWNotifications")] syntax obsolete in the NS 2005 API?
/Henrik
|||The problem is solved.
I had not updated the Microsoft.SQLServer.NotificationServices.dll used by the webproject from version 2.x to 9.x.
/Henrik
Registry entries for application to connect to remote SQL Server DB
I am trying to switch a PowerBuilder application from using a local database (SQL Anywhere) to a remote SQL Server database. I am getting IM002 after defining the data source and pointing the application to the datasource.
Can someone please send me some examples of Registry entries where an application is pointed to a remote SQL Server database?
IM002 is not a SQL Server error message. This seems to be a power builder issue, and I think you have a batter
chance to get good advice in a PowerBuilder newsgroup...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rajeev" <rlpd@.hotmail.com> wrote in message news:72091455-61D9-475E-B9AB-5DAF5A1DE604@.microsoft.com...
> I am trying to switch a PowerBuilder application from using a local database (SQL Anywhere) to a remote SQL
Server database. I am getting IM002 after defining the data source and pointing the application to the
datasource.
> Can someone please send me some examples of Registry entries where an application is pointed to a remote SQL
Server database?
Can someone please send me some examples of Registry entries where an application is pointed to a remote SQL Server database?
IM002 is not a SQL Server error message. This seems to be a power builder issue, and I think you have a batter
chance to get good advice in a PowerBuilder newsgroup...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rajeev" <rlpd@.hotmail.com> wrote in message news:72091455-61D9-475E-B9AB-5DAF5A1DE604@.microsoft.com...
> I am trying to switch a PowerBuilder application from using a local database (SQL Anywhere) to a remote SQL
Server database. I am getting IM002 after defining the data source and pointing the application to the
datasource.
> Can someone please send me some examples of Registry entries where an application is pointed to a remote SQL
Server database?
Registry entries for application to connect to remote SQL Server DB
I am trying to switch a PowerBuilder application from using a local database
(SQL Anywhere) to a remote SQL Server database. I am getting IM002 after d
efining the data source and pointing the application to the datasource.
Can someone please send me some examples of Registry entries where an applic
ation is pointed to a remote SQL Server database?IM002 is not a SQL Server error message. This seems to be a power builder is
sue, and I think you have a batter
chance to get good advice in a PowerBuilder newsgroup...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rajeev" <rlpd@.hotmail.com> wrote in message news:72091455-61D9-475E-B9AB-5DAF5A1DE604@.micro
soft.com...
> I am trying to switch a PowerBuilder application from using a local database (SQL
Anywhere) to a remote SQL
Server database. I am getting IM002 after defining the data source and poin
ting the application to the
datasource.
> Can someone please send me some examples of Registry entries where an application
is pointed to a remote SQL
Server database?
(SQL Anywhere) to a remote SQL Server database. I am getting IM002 after d
efining the data source and pointing the application to the datasource.
Can someone please send me some examples of Registry entries where an applic
ation is pointed to a remote SQL Server database?IM002 is not a SQL Server error message. This seems to be a power builder is
sue, and I think you have a batter
chance to get good advice in a PowerBuilder newsgroup...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rajeev" <rlpd@.hotmail.com> wrote in message news:72091455-61D9-475E-B9AB-5DAF5A1DE604@.micro
soft.com...
> I am trying to switch a PowerBuilder application from using a local database (SQL
Anywhere) to a remote SQL
Server database. I am getting IM002 after defining the data source and poin
ting the application to the
datasource.
> Can someone please send me some examples of Registry entries where an application
is pointed to a remote SQL
Server database?
Registry entries for application to connect to remote SQL Server DB
I am trying to switch a PowerBuilder application from using a local database (SQL Anywhere) to a remote SQL Server database. I am getting IM002 after defining the data source and pointing the application to the datasource
Can someone please send me some examples of Registry entries where an application is pointed to a remote SQL Server database?IM002 is not a SQL Server error message. This seems to be a power builder issue, and I think you have a batter
chance to get good advice in a PowerBuilder newsgroup...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rajeev" <rlpd@.hotmail.com> wrote in message news:72091455-61D9-475E-B9AB-5DAF5A1DE604@.microsoft.com...
> I am trying to switch a PowerBuilder application from using a local database (SQL Anywhere) to a remote SQL
Server database. I am getting IM002 after defining the data source and pointing the application to the
datasource.
> Can someone please send me some examples of Registry entries where an application is pointed to a remote SQL
Server database?
Can someone please send me some examples of Registry entries where an application is pointed to a remote SQL Server database?IM002 is not a SQL Server error message. This seems to be a power builder issue, and I think you have a batter
chance to get good advice in a PowerBuilder newsgroup...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rajeev" <rlpd@.hotmail.com> wrote in message news:72091455-61D9-475E-B9AB-5DAF5A1DE604@.microsoft.com...
> I am trying to switch a PowerBuilder application from using a local database (SQL Anywhere) to a remote SQL
Server database. I am getting IM002 after defining the data source and pointing the application to the
datasource.
> Can someone please send me some examples of Registry entries where an application is pointed to a remote SQL
Server database?
Subscribe to:
Posts (Atom)