Data throughput

I am trying to understand what things affect the
throughput of data when queries are executed to SQL
Server. I'm trying to determine if there are
settings,pramiters or times that can improve the speed of
a recordset being returned to the application.
What I have noticed in our environment is that a
recordset of 1,000 rows is returned very quickly where as
a recordset of a 1,000,000,000 rows can take over an hour.
I understand that the bandwidth will have a large effect
on the time but there are times when the bandwidth is
wide open and it still takes a significant amount of
time. What things can I do/set to optimixe this as much
as possible.
What can you do? Don't return 1 billion row resultsets, quite simple. No
human is able to digest that amount of information, so you should filter or
summarize it at the server using WHERE, SUM etc, and then return a more
limited number of rows to the client.
Jacco Schalkwijk
SQL Server MVP
"Jim Abel" <jim.abel@.lmco> wrote in message
news:5c7301c42d7f$a97fd480$a101280a@.phx.gbl...
> I am trying to understand what things affect the
> throughput of data when queries are executed to SQL
> Server. I'm trying to determine if there are
> settings,pramiters or times that can improve the speed of
> a recordset being returned to the application.
> What I have noticed in our environment is that a
> recordset of 1,000 rows is returned very quickly where as
> a recordset of a 1,000,000,000 rows can take over an hour.
> I understand that the bandwidth will have a large effect
> on the time but there are times when the bandwidth is
> wide open and it still takes a significant amount of
> time. What things can I do/set to optimixe this as much
> as possible.
|||The query is used to fill a CSV file that a third party
application uses to generate charts driven by the
ultimate Users. The reason I'm asking the question is to
see if there are options to speed the flow of data from
the Database to the CSV file to shorten the time it
currently takes. I can't modify the 3rd parties software
and I can't query less data so the ODBC seems like the
place to look. Your answer is logical for different
circumstances but doesn't get to the original question,
ARE there items that can be tweaked in ODBC that can
increase throughput?

>--Original Message--
>What can you do? Don't return 1 billion row resultsets,
quite simple. No
>human is able to digest that amount of information, so
you should filter or
>summarize it at the server using WHERE, SUM etc, and
then return a more[vbcol=seagreen]
>limited number of rows to the client.
>--
>Jacco Schalkwijk
>SQL Server MVP
>
>"Jim Abel" <jim.abel@.lmco> wrote in message
>news:5c7301c42d7f$a97fd480$a101280a@.phx.gbl...
of[vbcol=seagreen]
as[vbcol=seagreen]
hour.[vbcol=seagreen]
effect[vbcol=seagreen]
much
>
>.
>
|||What do you use to generate the csv file? bcp will be the quickest.
I--
Jacco Schalkwijk
SQL Server MVP
<anonymous@.discussions.microsoft.com> wrote in message
news:70ca01c42fae$ac9de360$a101280a@.phx.gbl...[vbcol=seagreen]
> The query is used to fill a CSV file that a third party
> application uses to generate charts driven by the
> ultimate Users. The reason I'm asking the question is to
> see if there are options to speed the flow of data from
> the Database to the CSV file to shorten the time it
> currently takes. I can't modify the 3rd parties software
> and I can't query less data so the ODBC seems like the
> place to look. Your answer is logical for different
> circumstances but doesn't get to the original question,
> ARE there items that can be tweaked in ODBC that can
> increase throughput?
>
> quite simple. No
> you should filter or
> then return a more
> of
> as
> hour.
> effect
> much

Data throughput

I am trying to understand what things affect the
throughput of data when queries are executed to SQL
Server. I'm trying to determine if there are
settings,pramiters or times that can improve the speed of
a recordset being returned to the application.
What I have noticed in our environment is that a
recordset of 1,000 rows is returned very quickly where as
a recordset of a 1,000,000,000 rows can take over an hour.
I understand that the bandwidth will have a large effect
on the time but there are times when the bandwidth is
wide open and it still takes a significant amount of
time. What things can I do/set to optimixe this as much
as possible.What can you do? Don't return 1 billion row resultsets, quite simple. No
human is able to digest that amount of information, so you should filter or
summarize it at the server using WHERE, SUM etc, and then return a more
limited number of rows to the client.
Jacco Schalkwijk
SQL Server MVP
"Jim Abel" <jim.abel@.lmco> wrote in message
news:5c7301c42d7f$a97fd480$a101280a@.phx.gbl...
> I am trying to understand what things affect the
> throughput of data when queries are executed to SQL
> Server. I'm trying to determine if there are
> settings,pramiters or times that can improve the speed of
> a recordset being returned to the application.
> What I have noticed in our environment is that a
> recordset of 1,000 rows is returned very quickly where as
> a recordset of a 1,000,000,000 rows can take over an hour.
> I understand that the bandwidth will have a large effect
> on the time but there are times when the bandwidth is
> wide open and it still takes a significant amount of
> time. What things can I do/set to optimixe this as much
> as possible.|||The query is used to fill a CSV file that a third party
application uses to generate charts driven by the
ultimate Users. The reason I'm asking the question is to
see if there are options to speed the flow of data from
the Database to the CSV file to shorten the time it
currently takes. I can't modify the 3rd parties software
and I can't query less data so the ODBC seems like the
place to look. Your answer is logical for different
circumstances but doesn't get to the original question,
ARE there items that can be tweaked in ODBC that can
increase throughput?

>--Original Message--
>What can you do? Don't return 1 billion row resultsets,
quite simple. No
>human is able to digest that amount of information, so
you should filter or
>summarize it at the server using WHERE, SUM etc, and
then return a more
>limited number of rows to the client.
>--
>Jacco Schalkwijk
>SQL Server MVP
>
>"Jim Abel" <jim.abel@.lmco> wrote in message
>news:5c7301c42d7f$a97fd480$a101280a@.phx.gbl...
of[vbcol=seagreen]
as[vbcol=seagreen]
hour.[vbcol=seagreen]
effect[vbcol=seagreen]
much[vbcol=seagreen]
>
>.
>|||What do you use to generate the csv file? bcp will be the quickest.
I--
Jacco Schalkwijk
SQL Server MVP
<anonymous@.discussions.microsoft.com> wrote in message
news:70ca01c42fae$ac9de360$a101280a@.phx.gbl...[vbcol=seagreen]
> The query is used to fill a CSV file that a third party
> application uses to generate charts driven by the
> ultimate Users. The reason I'm asking the question is to
> see if there are options to speed the flow of data from
> the Database to the CSV file to shorten the time it
> currently takes. I can't modify the 3rd parties software
> and I can't query less data so the ODBC seems like the
> place to look. Your answer is logical for different
> circumstances but doesn't get to the original question,
> ARE there items that can be tweaked in ODBC that can
> increase throughput?
>
>
> quite simple. No
> you should filter or
> then return a more
> of
> as
> hour.
> effect
> much

Data that is absent

I have a single table which we use to keep track of data from different
physical machines. The columns are: TheDate, TheName, & TheNotes. The Date
column is datetime and the rest are char. Every day each machine we collect
data on gets a row of information: The date of the collection, the machine
name, & any notes on the machine.
Since everyday there is a new row for each machine, what I would like to do
is in Query Analyzer be able to tell if there are any dates missing. Then we
could manually check the machine and see why it is not reporting.
I read an article that said to use the EXISTS command to select data based
on the preence or absence of values. I tried a couple different code samples
,
none showed signs of progress. If I were coding in VB I would use an array
and compare the distinct values. Is there a way in SQL to show values absent
from a known sequence.
Thanks
vmI assume you have a table with one row for each machine you want to manage.
You can do something like this.
tableA = Machine Names
tableB = Holds daily rows
SELECT a.Name FROM tableA AS a
WHERE NOT EXISTS (SELECT * FROM tableB AS b WHERE a.Name = b.Name
AND b.TheDate BETWEEN @.FromDate AND @.ToDate)
Andrew J. Kelly SQL MVP
"vm" <vm@.discussions.microsoft.com> wrote in message
news:513AB937-20C7-4893-8F8A-3E0363404703@.microsoft.com...
>I have a single table which we use to keep track of data from different
> physical machines. The columns are: TheDate, TheName, & TheNotes. The Date
> column is datetime and the rest are char. Every day each machine we
> collect
> data on gets a row of information: The date of the collection, the machine
> name, & any notes on the machine.
> Since everyday there is a new row for each machine, what I would like to
> do
> is in Query Analyzer be able to tell if there are any dates missing. Then
> we
> could manually check the machine and see why it is not reporting.
> I read an article that said to use the EXISTS command to select data based
> on the preence or absence of values. I tried a couple different code
> samples,
> none showed signs of progress. If I were coding in VB I would use an array
> and compare the distinct values. Is there a way in SQL to show values
> absent
> from a known sequence.
> Thanks
> vm|||Actually all of the data is in a single table. Each row in the table contain
s
the date, machine name, and any notes for the machine in different columns.
The database is simple and probably inefficient because I am fairly new to
SQL and the data I need to store and access is fairly straight forward. The
main reason I went with SQL db over Access, Excel, or even text files is
because the number of machines I have to work with multiplied by the number
of days would have blown everything else away. The data is simple enough tha
t
I could have went with any of the other storage options except for the sheer
number of records.
Is there any way your code will work with a single table. What I have read
on EXISTS mentions multiple tables.
Thanks
vm
vm
"Andrew J. Kelly" wrote:

> I assume you have a table with one row for each machine you want to manage
.
> You can do something like this.
> tableA = Machine Names
> tableB = Holds daily rows
> SELECT a.Name FROM tableA AS a
> WHERE NOT EXISTS (SELECT * FROM tableB AS b WHERE a.Name = b.Name
> AND b.TheDate BETWEEN @.FromDate AND @.ToDate)
> --
> Andrew J. Kelly SQL MVP
>
> "vm" <vm@.discussions.microsoft.com> wrote in message
> news:513AB937-20C7-4893-8F8A-3E0363404703@.microsoft.com...
>
>|||"vm" <vm@.discussions.microsoft.com> wrote in message
news:513AB937-20C7-4893-8F8A-3E0363404703@.microsoft.com...
> I have a single table which we use to keep track of data from
different
> physical machines. The columns are: TheDate, TheName, & TheNotes.
The Date
> column is datetime and the rest are char. Every day each machine
we collect
> data on gets a row of information: The date of the collection, the
machine
> name, & any notes on the machine.
> Since everyday there is a new row for each machine, what I would
like to do
> is in Query Analyzer be able to tell if there are any dates
missing. Then we
> could manually check the machine and see why it is not reporting.
> I read an article that said to use the EXISTS command to select
data based
> on the preence or absence of values. I tried a couple different
code samples,
> none showed signs of progress. If I were coding in VB I would use
an array
> and compare the distinct values. Is there a way in SQL to show
values absent
> from a known sequence.
> Thanks
> vm
vm,
Basically, Andrew Kelly was right.
In order to have a known sequence of dates, you would build a
calendar table with your dates.
This way, you can run a NOT EXISTS or Frustrated Outer Join query
against the calendar table, and that will show you the missing
dates.
Sincerely,
Chris O.|||Excellent, that seems the simpelest solution. I am away from work for the
wend, but will try on Monday.
Thanks to both!
vm
"Chris2" wrote:

> "vm" <vm@.discussions.microsoft.com> wrote in message
> news:513AB937-20C7-4893-8F8A-3E0363404703@.microsoft.com...
> different
> The Date
> we collect
> machine
> like to do
> missing. Then we
> data based
> code samples,
> an array
> values absent
> vm,
> Basically, Andrew Kelly was right.
> In order to have a known sequence of dates, you would build a
> calendar table with your dates.
> This way, you can run a NOT EXISTS or Frustrated Outer Join query
> against the calendar table, and that will show you the missing
> dates.
>
> Sincerely,
> Chris O.
>
>

data that in one table column and not in the other table column

dear all

i have 2 tables, lets say table A and Table B

both tables has column ID

i wonder how can i find records that appears in B.ID and not appear in A.ID

what is the SQL command in this case?

Thnks alot

Please post T-SQL question in the Transact-SQL forum at:

http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=85&SiteID=1

You can use the following query:

SELECT B.ID from B where B.ID NOT IN

(SELECT A.ID FROM A)

And this will return records from B which are not in A

Data tarsfer from one table to another with different schema on sqlserver 2000

Hi all
I am working on SqlServer 2000,
When row will be inserted/updated in one table that data neds to be
inserted/updated into the another DataBase on the same machine. Both
table has different schema, what is the Better solution? I know we can
do it with Trigger, But Is there any better way to do it?
Thanks
ABCL
> do it with Trigger, But Is there any better way to do it?
How do you insert/update the data ? By using SP? If so insert/update into
another table within the same SP.
I hope you don't have permmisions problems when you update/insert the
data into differnt schem/user owner object
"ABCL" <ohmp05@.hotmail.com> wrote in message
news:64f2096c-1e83-4e6a-8cec-2f0f818284bd@.n77g2000hse.googlegroups.com...
> Hi all
> I am working on SqlServer 2000,
> When row will be inserted/updated in one table that data neds to be
> inserted/updated into the another DataBase on the same machine. Both
> table has different schema, what is the Better solution? I know we can
> do it with Trigger, But Is there any better way to do it?
> Thanks
|||On Mar 16, 8:01Xam, ABCL <ohm...@.hotmail.com> wrote:
> Hi all
> X I am working on SqlServer 2000,
> XWhen row will be inserted/updated Xin one table that data neds to be
> inserted/updated into the another DataBase on the same machine. Both
> table has different schema, what is the Better solution? I know we can
> do it with Trigger, But Is there any better way to do it?
> Thanks
Actually we want to SynchroniZe the Data from one DataBase to another
and vise versa on Insert/update and delete.
How Can we Perform Data Synchronization on both the DataBase with
different schema.
If I write trigger, I think There will be endless loop If both Tables
hava trigger on inster/update and Delete.
Do u have any solution for that? I am new to perform this kind of task
Is it possible with DTS( scheduled job)?
Is there any Performance Issue?
sql

Data tarsfer from one table to another with different schema on sql

Hi all
I am working on SqlServer 2000,
When row will be inserted/updated in one table that data neds to be
inserted/updated into the another DataBase on the same machine. Both
table has different schema, what is the Better solution? I know we can
do it with Trigger, But Is there any better way to do it?
ThanksABCL
> do it with Trigger, But Is there any better way to do it?
How do you insert/update the data ? By using SP? If so insert/update into
another table within the same SP.
I hope you don't have permmisions problems when you update/insert the
data into differnt schem/user owner object
"ABCL" <ohmp05@.hotmail.com> wrote in message
news:64f2096c-1e83-4e6a-8cec-2f0f818284bd@.n77g2000hse.googlegroups.com...
> Hi all
> I am working on SqlServer 2000,
> When row will be inserted/updated in one table that data neds to be
> inserted/updated into the another DataBase on the same machine. Both
> table has different schema, what is the Better solution? I know we can
> do it with Trigger, But Is there any better way to do it?
> Thanks|||On Mar 16, 8:01=A0am, ABCL <ohm...@.hotmail.com> wrote:
> Hi all
> =A0 I am working on SqlServer 2000,
> =A0When row will be inserted/updated =A0in one table that data neds to be
> inserted/updated into the another DataBase on the same machine. Both
> table has different schema, what is the Better solution? I know we can
> do it with Trigger, But Is there any better way to do it?
> Thanks
Actually we want to SynchroniZe the Data from one DataBase to another
and vise versa on Insert/update and delete.
How Can we Perform Data Synchronization on both the DataBase with
different schema.
If I write trigger, I think There will be endless loop If both Tables
hava trigger on inster/update and Delete.
Do u have any solution for that? I am new to perform this kind of task
Is it possible with DTS( scheduled job)?
Is there any Performance Issue?

data table fragmentation

we've got some large _data_ tables that could be fragmented (indexes
are already being defragged using dbcc indexdefrag). so,
1. how can i confirm this?
2. if they are, what's the best way to unfragment them?
i've been looking through old threads and see a lot of references to
dbcc shrinkfile, but that command doesn't seem to address the data
contiguity issue. the other option i've seen is to bcp the data out and
back in, but that seems a bit cumbersome.
thanks,
arthur
1)
DBCC SHOWCONTIG (For more details please refer to the BOL)
2)
DBCC INDEXDEFRAG
DBCC DBREINDEX
"arthur" <alangham@.gmail.com> wrote in message
news:1107784896.734177.96160@.l41g2000cwc.googlegro ups.com...
> we've got some large _data_ tables that could be fragmented (indexes
> are already being defragged using dbcc indexdefrag). so,
> 1. how can i confirm this?
> 2. if they are, what's the best way to unfragment them?
> i've been looking through old threads and see a lot of references to
> dbcc shrinkfile, but that command doesn't seem to address the data
> contiguity issue. the other option i've seen is to bcp the data out and
> back in, but that seems a bit cumbersome.
> thanks,
> arthur
>
|||No don't use shrinkfile as that will fragment them even more. You defrag
indexes with DBCC DBREINDEX or DBCC INDEXDEFRAG. The table itself should
have a clustered index on it so if you rebuild the clustered index you
rebuild the table itself. Have a look here:
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
Andrew J. Kelly SQL MVP
"arthur" <alangham@.gmail.com> wrote in message
news:1107784896.734177.96160@.l41g2000cwc.googlegro ups.com...
> we've got some large _data_ tables that could be fragmented (indexes
> are already being defragged using dbcc indexdefrag). so,
> 1. how can i confirm this?
> 2. if they are, what's the best way to unfragment them?
> i've been looking through old threads and see a lot of references to
> dbcc shrinkfile, but that command doesn't seem to address the data
> contiguity issue. the other option i've seen is to bcp the data out and
> back in, but that seems a bit cumbersome.
> thanks,
> arthur
>
|||use dbcc showcontig
check books on line for lots of examples and explanation of what the output
means.
fix via indexdefrag or dbReindex.
keep in mind that if a table does not have a clustered index, you will not
be able to defrag it.
cheers,
Greg Jackson
Portland, Oregon
|||as always, thanks!
arthur

data table fragmentation

we've got some large _data_ tables that could be fragmented (indexes
are already being defragged using dbcc indexdefrag). so,
1. how can i confirm this?
2. if they are, what's the best way to unfragment them?
i've been looking through old threads and see a lot of references to
dbcc shrinkfile, but that command doesn't seem to address the data
contiguity issue. the other option i've seen is to bcp the data out and
back in, but that seems a bit cumbersome.
thanks,
arthur1)
DBCC SHOWCONTIG (For more details please refer to the BOL)
2)
DBCC INDEXDEFRAG
DBCC DBREINDEX
"arthur" <alangham@.gmail.com> wrote in message
news:1107784896.734177.96160@.l41g2000cwc.googlegroups.com...
> we've got some large _data_ tables that could be fragmented (indexes
> are already being defragged using dbcc indexdefrag). so,
> 1. how can i confirm this?
> 2. if they are, what's the best way to unfragment them?
> i've been looking through old threads and see a lot of references to
> dbcc shrinkfile, but that command doesn't seem to address the data
> contiguity issue. the other option i've seen is to bcp the data out and
> back in, but that seems a bit cumbersome.
> thanks,
> arthur
>|||No don't use shrinkfile as that will fragment them even more. You defrag
indexes with DBCC DBREINDEX or DBCC INDEXDEFRAG. The table itself should
have a clustered index on it so if you rebuild the clustered index you
rebuild the table itself. Have a look here:
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
Andrew J. Kelly SQL MVP
"arthur" <alangham@.gmail.com> wrote in message
news:1107784896.734177.96160@.l41g2000cwc.googlegroups.com...
> we've got some large _data_ tables that could be fragmented (indexes
> are already being defragged using dbcc indexdefrag). so,
> 1. how can i confirm this?
> 2. if they are, what's the best way to unfragment them?
> i've been looking through old threads and see a lot of references to
> dbcc shrinkfile, but that command doesn't seem to address the data
> contiguity issue. the other option i've seen is to bcp the data out and
> back in, but that seems a bit cumbersome.
> thanks,
> arthur
>|||use dbcc showcontig
check books on line for lots of examples and explanation of what the output
means.
fix via indexdefrag or dbReindex.
keep in mind that if a table does not have a clustered index, you will not
be able to defrag it.
cheers,
Greg Jackson
Portland, Oregon|||as always, thanks!
arthur

data table fragmentation

we've got some large _data_ tables that could be fragmented (indexes
are already being defragged using dbcc indexdefrag). so,
1. how can i confirm this?
2. if they are, what's the best way to unfragment them?
i've been looking through old threads and see a lot of references to
dbcc shrinkfile, but that command doesn't seem to address the data
contiguity issue. the other option i've seen is to bcp the data out and
back in, but that seems a bit cumbersome.
thanks,
arthur1)
DBCC SHOWCONTIG (For more details please refer to the BOL)
2)
DBCC INDEXDEFRAG
DBCC DBREINDEX
"arthur" <alangham@.gmail.com> wrote in message
news:1107784896.734177.96160@.l41g2000cwc.googlegroups.com...
> we've got some large _data_ tables that could be fragmented (indexes
> are already being defragged using dbcc indexdefrag). so,
> 1. how can i confirm this?
> 2. if they are, what's the best way to unfragment them?
> i've been looking through old threads and see a lot of references to
> dbcc shrinkfile, but that command doesn't seem to address the data
> contiguity issue. the other option i've seen is to bcp the data out and
> back in, but that seems a bit cumbersome.
> thanks,
> arthur
>|||No don't use shrinkfile as that will fragment them even more. You defrag
indexes with DBCC DBREINDEX or DBCC INDEXDEFRAG. The table itself should
have a clustered index on it so if you rebuild the clustered index you
rebuild the table itself. Have a look here:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
--
Andrew J. Kelly SQL MVP
"arthur" <alangham@.gmail.com> wrote in message
news:1107784896.734177.96160@.l41g2000cwc.googlegroups.com...
> we've got some large _data_ tables that could be fragmented (indexes
> are already being defragged using dbcc indexdefrag). so,
> 1. how can i confirm this?
> 2. if they are, what's the best way to unfragment them?
> i've been looking through old threads and see a lot of references to
> dbcc shrinkfile, but that command doesn't seem to address the data
> contiguity issue. the other option i've seen is to bcp the data out and
> back in, but that seems a bit cumbersome.
> thanks,
> arthur
>|||use dbcc showcontig
check books on line for lots of examples and explanation of what the output
means.
fix via indexdefrag or dbReindex.
keep in mind that if a table does not have a clustered index, you will not
be able to defrag it.
cheers,
Greg Jackson
Portland, Oregon|||as always, thanks!
arthur

data table foriegn key problem

I am just learning how to program in SQL, so please be patient with me

ok, here it is, i am getting the following errors with my script when i try to execute, and although i realize that this is not the best style to write my script in, i am trying to learn why this is erroring. the errors are:

Msg 1769, Level 16, State 1, Line 9

Foreign key 'Employee2Job_Title' references invalid column 'Title' in referencing table 'Employee'.

Msg 1750, Level 16, State 0, Line 9

Could not create constraint. See previous errors.

now i realize that the second error is because of the first, and that if i can fix the first, the second will go away. Thanks for the help.

Here is my script

use inventory

go

CREATE TABLE Job_Title

(Job_Title_Title char(25) NOT NULL ,

Job_Title_EEO1_Classification char(25) ,

Job_Title_Job_Description Varchar(45) ,

Job_Title_Exempt_Status Varchar(15)

, PRIMARY KEY (Job_Title_Title)

);

CREATE TABLE Employee

(Employee_Emp_ID integer NOT NULL ,

Employee_last_name varchar(15) ,

Employee_first_name varchar(15) ,

Employee_address varchar(30) ,

Employee_city varchar(15) ,

Employee_state char(2) ,

Employee_Telephone_area_code char(3) ,

Employee_Telephone_number char(8) ,

Employee_EEO1_Classification char(25) ,

Employee_Hire_Date char(8) ,

Employee_Salary char(6) ,

Employee_Gender Varchar(1) ,

Employee_Age char(2) ,

Employee_Title char(25)

, PRIMARY KEY (Employee_Emp_ID)

, constraint Employee2Job_Title FOREIGN KEY (Title

) REFERENCES Job_Title

);

Insert INTO Employee (Emp_ID, Last_name, First_name, Address, City, State, Telephone_area_code, Telephone_number, EEO1_Classification, Hire_date, Salary, Gender, Age, Title) Values

(1, 'Edelman', 'Glenn', '175 Bishop Lane', 'La Jolla', 'CA', 619, '555-0199', 'Sales Workers', 10/7/2003, 21500.00, 'M', 64, 'Cashier')

Insert INTO Employee (Emp_ID, Last_name, First_name, Address, City, State, Telephone_area_code, Telephone_number, EEO1_Classification, Hire_date, Salary, Gender, Age, Title) Values

(2, 'McMullen', 'Eric', '762 Church Street', 'Lemon Grove', 'CA', 619, '555-0133', 'Sales Workers', 11/1/2002, 13500.00, 'M', 20, 'Bagger')

Insert INTO Employee (Emp_ID, Last_name, First_name, Address, City, State, Telephone_area_code, Telephone_number, EEO1_Classification, Hire_date, Salary, Gender, Age, Title) Values

(3, 'Slenj', 'Raj', '123 Torrey Drive', 'North Clairmont', 'CA', 619, '555-0123', 'Officials & Managers', 6/1/2000, 48000.00, 'M', 34, 'Assistant Manager')

Insert INTO Employee (Emp_ID, Last_name, First_name, Address, City, State, Telephone_area_code, Telephone_number, EEO1_Classification, Hire_date, Salary, Gender, Age, Title) Values

(4, 'Broun', 'Erin', '2045 Parkway Apt 2b', 'Encinitas', 'CA', 760, '555-0100', 'Sales Workers', 3/12/2003, 10530.00, 'F', 24, 'Bagger - 30 hours/wk')

Insert INTO Employee (Emp_ID, Last_name, First_name, Address, City, State, Telephone_area_code, Telephone_number, EEO1_Classification, Hire_date, Salary, Gender, Age, Title) Values

(5, 'Carpenter', 'Donald', '927 Second Street', 'Encinitas', 'CA', 619, '555-0154', 'Office/Clerical', 11/1/2003, 15000.00, 'M', 18, 'Stocker')

Insert INTO Employee (Emp_ID, Last_name, First_name, Address, City, State, Telephone_area_code, Telephone_number, EEO1_Classification, Hire_date, Salary, Gender, Age, Title) Values

(6, 'Esquivez', 'David', '10983 North Coast Highway Apt 902', 'Encinitas', 'CA', 760, '555-0108', 'Operatives (Semi skilled)', 7/25/2003, 18500.00, 'M',25, 'Asst. - Butchers & Seafood Specialists')

Insert INTO Employee (Emp_ID, Last_name, First_name, Address, City, State, Telephone_area_code, Telephone_number, EEO1_Classification, Hire_date, Salary, Gender, Age, Title) Values

(7, 'Sharp', 'Nancy', '10793 Montecino Road', 'Ramona', 'CA', 858, '555-0135', 'Sales Workers', 7/12/2003, 21000.00, 'F', 24, 'Cashier')

Insert into Job_Title (Title, EEO1_Classification, Job_Description, Exempt_Status) Values

('Accounting Clerk', 'Office/Clerical', 'Computes, Classifies, records, and verifies numerical data for use in maintaining accounting records.', 'Non-Exempt')

Insert into Job_Title (Title, EEO1_Classification, Job_Description, Exempt_Status) Values

('Assistant store manager', 'Officials & Mangers', 'Supervises and coordintes activities of workers in department of food store. Assist store manager in daily operations of store.', 'Exempt')

Insert into Job_Title (Title, EEO1_Classification, Job_Description, Exempt_Status) Values

('Bagger', 'Sales Worker', 'Places customer orders in bags. Performs carry out duties for customers.', 'Non-Exempt')

Insert into Job_Title (Title, EEO1_Classification, Job_Description, Exempt_Status) Values

('Cashier', 'Sales Worker', 'Operates Cash register to itemize and total customers purchases in grocercy store', 'Non-Exempt')

Insert into Job_Title (Title, EEO1_Classification, Job_Description, Exempt_Status) Values

('Computer Support Specialist', 'Technician', 'Installs, Modifies, and makes minor repairs to personal computer hardware and software systems and provides technical assistance and training to system users.', 'Non-Exempt')

Insert into Job_Title (Title, EEO1_Classification, Job_Description, Exempt_Status) Values

('Director of Finance & Accounting', 'Officials & Mangers', 'Plans and directs finance and accounting activites for Kudlser Fine Foods.', 'Non-Exempt')

Insert into Job_Title (Title, EEO1_Classification, Job_Description, Exempt_Status) Values

('Retail Asst. Bakery & Pastry', 'Craft Workers (Skilled)', 'Obtains or prepares Bakery and Pastry items requested by customers in retail food store.', 'Non-Exempt')

Insert into Job_Title (Title, EEO1_Classification, Job_Description, Exempt_Status) Values

('Retail Asst. Butchers and Seafood Specialists', 'Operatives (Semi skilled)', 'Obtains or prepares Meat and Seafood items requested by customers in retail food store.', 'Non-Exempt')

Insert into Job_Title (Title, EEO1_Classification, Job_Description, Exempt_Status) Values

('Stocker', 'Office/Clerical', 'Stores, prices, and restocks merchandise displays in store.', 'Non-Exempt')

In your foreign key that you are creating with:

constraint Employee2Job_Title FOREIGN KEY (Title) REFERENCES Job_Title

The column named Title needs to exist in the Employee table on which you are creating the constraint. Then for the column it points to with the REFERENCES part, the needs to be in the format of ReferenceTable(ReferenceTableColumnName)

If I'm guessing right at your tables, I think what you are looking for is more along the lines of

constraint Employee2Job_Title FOREIGN KEY (Employee_Title) REFERENCES Job_Title(Job_Title_Title)

-Sue

|||ok, here is the code i ended up with that cured that problem

CREATE TABLE Job_Title
(Job_Title_Title char(25) NOT NULL ,
Job_Title_EEO1_Classification char(25) ,
Job_Title_Job_Description Varchar(45) ,
Job_Title_Exempt_Status Varchar(15)
, PRIMARY KEY (Job_Title_Title)
);

CREATE TABLE Employee
(Employee_Emp_ID integer NOT NULL ,
Employee_last_name varchar(15) ,
Employee_first_name varchar(15) ,
Employee_address varchar(30) ,
Employee_city varchar(15) ,
Employee_state char(2) ,
Employee_Telephone_area_code char(3) ,
Employee_Telephone_number char(8) ,
Employee_EEO1_Classification char(25) ,
Employee_Hire_Date char(8) ,
Employee_Salary char(6) ,
Employee_Gender Varchar(1) ,
Employee_Age char(2) ,
Employee_Title char(25)
, PRIMARY KEY (Employee_Emp_ID)
, constraint Employee2Job_Title FOREIGN KEY (Employee_Title
) REFERENCES Job_Title
);

but now i am on to new and even more frustrating errors, and i will do some work on them myself, on the morrow, then if i am still having problems, i will again avail myself of this resource.

thanks much sue, your assistance has been invaluable!sql

Data tab works great but i can't get it work in Preview tab in reporting services

This code works great in data tab but when i go to preview it i get a len function error.
I am passing possible BLANK values in all the strings not NULL. Do you think its an parameter expression issue? If so, how do I write a expression for each parameter that will reflect this logic. NOTE: As you can see states and program are multi param, and I don't know ahead of time what values will be entered. So if some could please help.
SELECT DISTINCT A.RECIPIENT_STATE, B.FISCAL_YEAR, B.FEDERAL_SHARE, B.NON_FEDERAL_SHARE
FROM Tab_Awards AS A JOIN TAB_Amendments AS B ON A.AWARD_NUMBER = B.AWARD_NUMBER JOIN Tab_Program AS C
ON A.FPO_ID = C.FPO_ID
WHERE (LEN(@.keywords)<1 OR (FREETEXT(A.*,@.keywords)))
AND (LEN(@.states)<1 OR A.RECIPIENT_STATE IN (@.states))
AND (LEN(@.program) <1 OR C.PROGRAM IN (@.program))
AND (LEN(@.fromYear)<1 OR B.FISCAL_YEAR >= @.fromYear)
AND (LEN(@.toYear)<1 OR B.FISCAL_YEAR <= @.toYear)
AND (LEN(@.year)<1 OR LEN(@.year)>=1)
AND (LEN(@.organization)<1 OR LEN(@.organization)>=1
From http://www.developmentnow.com/g/115_0_0_0_0_0/sql-server-reporting-services.ht
Posted via DevelopmentNow.com Group
http://www.developmentnow.comWhat exactly is the error ? Why I am asking is that len must be evaluating
some numeric value e.g year parameter must be having numeric and Len will
take string expression only. So Just have a look at all the parameters again
and see their data type.
Amarnath
"Dre" wrote:
> This code works great in data tab but when i go to preview it i get a len function error.
> I am passing possible BLANK values in all the strings not NULL. Do you think its an parameter expression issue? If so, how do I write a expression for each parameter that will reflect this logic. NOTE: As you can see states and program are multi param, and I don't know ahead of time what values will be entered. So if some could please help.
> SELECT DISTINCT A.RECIPIENT_STATE, B.FISCAL_YEAR, B.FEDERAL_SHARE, B.NON_FEDERAL_SHARE
> FROM Tab_Awards AS A JOIN TAB_Amendments AS B ON A.AWARD_NUMBER = B.AWARD_NUMBER JOIN Tab_Program AS C
> ON A.FPO_ID = C.FPO_ID
> WHERE (LEN(@.keywords)<1 OR (FREETEXT(A.*,@.keywords)))
> AND (LEN(@.states)<1 OR A.RECIPIENT_STATE IN (@.states))
> AND (LEN(@.program) <1 OR C.PROGRAM IN (@.program))
> AND (LEN(@.fromYear)<1 OR B.FISCAL_YEAR >= @.fromYear)
> AND (LEN(@.toYear)<1 OR B.FISCAL_YEAR <= @.toYear)
> AND (LEN(@.year)<1 OR LEN(@.year)>=1)
> AND (LEN(@.organization)<1 OR LEN(@.organization)>=1)
> From http://www.developmentnow.com/g/115_0_0_0_0_0/sql-server-reporting-services.htm
> Posted via DevelopmentNow.com Groups
> http://www.developmentnow.com
>

data synchronization accross multiple servers running replication

Hello,

We have an environment with 7 servers that are running replication with one another and I'm wondering if there are any tools or experiences that any of you might have that may assist in the auditing of these servers. The data should be in synch accross the boards for all tables, but sometimes problems can arise such as replication not being set up properly, stored procedure's being out of synch, or data gliches etc.

In dealing with these issues we have an in-house written program which analises each table on each server and takes a snapshot and does column by column compare. We also have another program that will synch the data up (basically a delete/insert statement on the publisher). This process can take up to 3 weeks for our quarterly update of every table. I'm wondering if anybody has used any tools such as in SSIS or a third party tool and has done or is doing something similar to what we are doing now. If so, are there any tips you wouldn't mind sharing on how our process might be sped up?

Thanks,

Phil

In SQL Server 2005 there is a tablediff tool which you can use to compare the tables. You can also use the validation options built in replication to verify if the data are in sync.

Here are the validation options:

In transactional replication: sp_publication_validation

In merge replication: use the -validate parameter for merge agent

|||

Li,

Thanks for the reply. I believe we can use this. Do you know how well it does as far as speed is concerned? I think that's our main concern right now. Also, I'm reading a bit on the uitlity and I'm wondering how does it take a "snapshot" of a table on multiple servers and compare that. Also you wouldn't happen to have any syntax examples would you?

Thanks,

Phil

|||

Also, does anyone know if there is a way to get the utility to use windows authentication instead of coding the sa password in the utility statement?

Thanks,

Phil

data synchronization

I have one Informix in OpenVMS machine and another MS SQL in Windows machine. I cannot touch the Informix server at all.

How to do the extract/update the data periodically from Informix to SQL? I was thinking of writing a service. But I don't know how to do the incremental update.

I was thinking of doing a pull subscription from MS SQL. Is it feasible? Is it a replication or integration service? I dunno. Please help.

Thank you.You don't need to write a service. There already is a service. Use DTS.

I do a nightly tranfer of data from Informix to SQL Server for one of my steady clients.

data synchronization

Hello! anybody know some tool to synchronizate data between databases (two
databases has the same structure) . I can not use replicate becouse I have
not a permanet connection between servers. Now, somebody can send me a
backup peridocally using email. And I can restore in a second database in
local and work in local with principal and second database, then send back
the backup sinchronized.
Thank you in advance and sorry for my english.
Guillermo
You might be able to use tools like RedGate software's data compare -
however they too will require a network connection.
You can replicate using merge replication or transactional replication to a
local database and then hack the msmerge_contents or msmerge_tombstone
tables for merge replication, or use sp_browsereplcmds for transactional
replication to get a list of the changes and then apply these on either
side.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Guillermo Villanueva" <guillermovil@.nospam.com> wrote in message
news:OTu6RLM4GHA.5000@.TK2MSFTNGP02.phx.gbl...
> Hello! anybody know some tool to synchronizate data between databases (two
> databases has the same structure) . I can not use replicate becouse I have
> not a permanet connection between servers. Now, somebody can send me a
> backup peridocally using email. And I can restore in a second database in
> local and work in local with principal and second database, then send back
> the backup sinchronized.
> Thank you in advance and sorry for my english.
> Guillermo
>
|||"Hilary Cotter" wrote:

> You might be able to use tools like RedGate software's data compare -
> however they too will require a network connection.
> You can replicate using merge replication or transactional replication to a
> local database and then hack the msmerge_contents or msmerge_tombstone
> tables for merge replication, or use sp_browsereplcmds for transactional
> replication to get a list of the changes and then apply these on either
> side.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Guillermo Villanueva" <guillermovil@.nospam.com> wrote in message
> news:OTu6RLM4GHA.5000@.TK2MSFTNGP02.phx.gbl...
>
>

data synchronization

Dear All,
I have 3 databases in sql , one db have a master data and I want to
synchronize to other 2 databases. is any sync. sw is available for that. or
is any thing in SQL also. databases may be in same place or remortly also
please help
thanking you
Regards
K R LalYou could write a script to keep the databases updated and run the script at
specific intervals. You may also consider replication. Take a look at
Replication in Books Online.
"Lal" <kr.lal@.luxoroffice.com> wrote in message
news:O8kSCgaCFHA.904@.TK2MSFTNGP12.phx.gbl...
> Dear All,
> I have 3 databases in sql , one db have a master data and I want to
> synchronize to other 2 databases. is any sync. sw is available for that.
> or
> is any thing in SQL also. databases may be in same place or remortly also
> please help
> thanking you
> Regards
>
> K R Lal
>|||Lal
The question is how often do you want to get a fresh data from the master?
Replication, especially transaction replication could suite you.
Also . I'd look at BACKUP/RESTORE commands altogether with BACKUP LOG file
as well.
"Lal" <kr.lal@.luxoroffice.com> wrote in message
news:O8kSCgaCFHA.904@.TK2MSFTNGP12.phx.gbl...
> Dear All,
> I have 3 databases in sql , one db have a master data and I want to
> synchronize to other 2 databases. is any sync. sw is available for that.
or
> is any thing in SQL also. databases may be in same place or remortly also
> please help
> thanking you
> Regards
>
> K R Lal
>|||Hello Lal.
Replication would be the defacto response although it may not suit your
environment or needs. You could also check out DB Ghost which can synchroniz
e
both data or schema or both which can be scheduled to run when you like. I
created a SQL Server job to synchronize my database with the hosting company
so I could have a reporting and disaster recovery database in case anything
went wrong with my hosting company. You can download the example here:
http://www.innovartis.co.uk/downloa...J
ob.zip
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Database change management for SQL Server
"Lal" wrote:

> Dear All,
> I have 3 databases in sql , one db have a master data and I want to
> synchronize to other 2 databases. is any sync. sw is available for that. o
r
> is any thing in SQL also. databases may be in same place or remortly also
> please help
> thanking you
> Regards
>
> K R Lal
>
>sql

data synchronization

Hello! anybody know some tool to synchronizate data between databases (two
databases has the same structure) . I can not use replicate becouse I have
not a permanet connection between servers. Now, somebody can send me a
backup peridocally using email. And I can restore in a second database in
local and work in local with principal and second database, then send back
the backup sinchronized.
Thank you in advance and sorry for my english.
Guillermo
You might be able to use tools like RedGate software's data compare -
however they too will require a network connection.
You can replicate using merge replication or transactional replication to a
local database and then hack the msmerge_contents or msmerge_tombstone
tables for merge replication, or use sp_browsereplcmds for transactional
replication to get a list of the changes and then apply these on either
side.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Guillermo Villanueva" <guillermovil@.nospam.com> wrote in message
news:OTu6RLM4GHA.5000@.TK2MSFTNGP02.phx.gbl...
> Hello! anybody know some tool to synchronizate data between databases (two
> databases has the same structure) . I can not use replicate becouse I have
> not a permanet connection between servers. Now, somebody can send me a
> backup peridocally using email. And I can restore in a second database in
> local and work in local with principal and second database, then send back
> the backup sinchronized.
> Thank you in advance and sorry for my english.
> Guillermo
>
|||"Hilary Cotter" wrote:

> You might be able to use tools like RedGate software's data compare -
> however they too will require a network connection.
> You can replicate using merge replication or transactional replication to a
> local database and then hack the msmerge_contents or msmerge_tombstone
> tables for merge replication, or use sp_browsereplcmds for transactional
> replication to get a list of the changes and then apply these on either
> side.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Guillermo Villanueva" <guillermovil@.nospam.com> wrote in message
> news:OTu6RLM4GHA.5000@.TK2MSFTNGP02.phx.gbl...
>
>

Data Synchronisation Issue

I am not quite a newbie in the area of SQL server - more "knows just
enough to be dangerous"
- so please be gentle with me.
Cross posted to several groups - apologies if too far off topic
I have an application that does a lot of massaging of data (insurance
claims) from 3 different data sources to present one set of nice
homogeneous output tables. Uses about 200 various tables and about 300
queries to do the work ( sounds like a mess - but trust me - it is
quite disciplined)
We are using 4 * 1Gb mdb backends and the client agrees it is time to
move
the backend to SQL server. They have made it clear that they do not
wish to convert any of the queries to pass thru queries - ie - I am
just replacing my attachments to mdb tables with attachments to SQL
tables. They accept that it will probably run even slower due to the
extra SQL overheads.
I have dealt with most issues in the conversion but the 3 show
stoppers are:
1) Half way through the process I get a "record is deleted" message
when one of the queries attempts to run. I am guessing that there is a
synchronisation problem between any earlier query that empties a table
and an "append" query that refills it, and maybe a subsequent select
query that uses the refilled table.
Can anyone give me some code snippets in access to force a query to
flush all its results to SQL before I embark on the next query.
2) Initially I used the data transformation services to load all the
access backend tables across to SQL server.
I then retweaked my homebrew attachment routines to handle attaching
to an SQL table - everything worked fine.
Of course the tables were not updateable due to a lack of primary keys
in the SQL tables.
No problem - I worked my way through the SQL tables building
constraints and / or primary keys.
Then I found that my attachment routines would fail for some of the
tables - message being (paraphrasing) - "I can't find that table or
the table name is too long"
If I went back in to SQL server and shortened the table names down to
about 20 characters - then the problem went away.
I even adjusted my Access attachment routines so that I could still
keep the desired attached table names.
The problem is more for the client - when they go to point Cognos at
the SQL tables - they will need to do some reworking (or maybe Cognos
has an alias facility)
Can anyone shed any light on this situation.
3) When I set up DSN's on my (Win 98) machine - they appear to store
the user password quite happily.
On the client's (XP) machine - when my attachment routine runs - it
appears that the DSN is not holding the password anymore - and we get
prompted for the password, for every table that is being attached.
Can anyone explain why ?
Many thanks in advance
Tony
See inline
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
<ace join_to ware@.iinet.net.au (Tony Epton)> wrote in message
news:41abdc2f.3023390@.news.m.iinet.net.au...
> I am not quite a newbie in the area of SQL server - more "knows just
> enough to be dangerous"
> - so please be gentle with me.
> Cross posted to several groups - apologies if too far off topic
> I have an application that does a lot of massaging of data (insurance
> claims) from 3 different data sources to present one set of nice
> homogeneous output tables. Uses about 200 various tables and about 300
> queries to do the work ( sounds like a mess - but trust me - it is
> quite disciplined)
> We are using 4 * 1Gb mdb backends and the client agrees it is time to
> move
> the backend to SQL server. They have made it clear that they do not
> wish to convert any of the queries to pass thru queries - ie - I am
> just replacing my attachments to mdb tables with attachments to SQL
> tables. They accept that it will probably run even slower due to the
> extra SQL overheads.
> I have dealt with most issues in the conversion but the 3 show
> stoppers are:
> 1) Half way through the process I get a "record is deleted" message
> when one of the queries attempts to run. I am guessing that there is a
> synchronisation problem between any earlier query that empties a table
> and an "append" query that refills it, and maybe a subsequent select
> query that uses the refilled table.
> Can anyone give me some code snippets in access to force a query to
> flush all its results to SQL before I embark on the next query.
>
If you are running a single process, then each query is complete before the
next one begins.

> 2) Initially I used the data transformation services to load all the
> access backend tables across to SQL server.
> I then retweaked my homebrew attachment routines to handle attaching
> to an SQL table - everything worked fine.
> Of course the tables were not updateable due to a lack of primary keys
> in the SQL tables.
> No problem - I worked my way through the SQL tables building
> constraints and / or primary keys.
> Then I found that my attachment routines would fail for some of the
> tables - message being (paraphrasing) - "I can't find that table or
> the table name is too long"
> If I went back in to SQL server and shortened the table names down to
> about 20 characters - then the problem went away.
> I even adjusted my Access attachment routines so that I could still
> keep the desired attached table names.
> The problem is more for the client - when they go to point Cognos at
> the SQL tables - they will need to do some reworking (or maybe Cognos
> has an alias facility)
> Can anyone shed any light on this situation.
SQL table names can be up to 128 characters long... I suspect you are seeing
some Access restriction, not a sql restriction.

> 3) When I set up DSN's on my (Win 98) machine - they appear to store
> the user password quite happily.
> On the client's (XP) machine - when my attachment routine runs - it
> appears that the DSN is not holding the password anymore - and we get
> prompted for the password, for every table that is being attached.
> Can anyone explain why ?
XP DSNs hold login/password if you choose NOT to use trusted
authentication... The create DSN walks you through this... Also, make sure
you are using a system DSN, so it will be valid for any user on the
machine...
> Many thanks in advance
> Tony

Data Synchronisation Issue

I am not quite a newbie in the area of SQL server - more "knows just
enough to be dangerous"
- so please be gentle with me.
Cross posted to several groups - apologies if too far off topic
I have an application that does a lot of massaging of data (insurance
claims) from 3 different data sources to present one set of nice
homogeneous output tables. Uses about 200 various tables and about 300
queries to do the work ( sounds like a mess - but trust me - it is
quite disciplined)
We are using 4 * 1Gb mdb backends and the client agrees it is time to
move
the backend to SQL server. They have made it clear that they do not
wish to convert any of the queries to pass thru queries - ie - I am
just replacing my attachments to mdb tables with attachments to SQL
tables. They accept that it will probably run even slower due to the
extra SQL overheads.
I have dealt with most issues in the conversion but the 3 show
stoppers are:
1) Half way through the process I get a "record is deleted" message
when one of the queries attempts to run. I am guessing that there is a
synchronisation problem between any earlier query that empties a table
and an "append" query that refills it, and maybe a subsequent select
query that uses the refilled table.
Can anyone give me some code snippets in access to force a query to
flush all its results to SQL before I embark on the next query.
2) Initially I used the data transformation services to load all the
access backend tables across to SQL server.
I then retweaked my homebrew attachment routines to handle attaching
to an SQL table - everything worked fine.
Of course the tables were not updateable due to a lack of primary keys
in the SQL tables.
No problem - I worked my way through the SQL tables building
constraints and / or primary keys.
Then I found that my attachment routines would fail for some of the
tables - message being (paraphrasing) - "I can't find that table or
the table name is too long"
If I went back in to SQL server and shortened the table names down to
about 20 characters - then the problem went away.
I even adjusted my Access attachment routines so that I could still
keep the desired attached table names.
The problem is more for the client - when they go to point Cognos at
the SQL tables - they will need to do some reworking (or maybe Cognos
has an alias facility)
Can anyone shed any light on this situation.
3) When I set up DSN's on my (Win 98) machine - they appear to store
the user password quite happily.
On the client's (XP) machine - when my attachment routine runs - it
appears that the DSN is not holding the password anymore - and we get
prompted for the password, for every table that is being attached.
Can anyone explain why ?
Many thanks in advance
TonySee inline
--
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
<ace join_to ware@.iinet.net.au (Tony Epton)> wrote in message
news:41abdc2f.3023390@.news.m.iinet.net.au...
> I am not quite a newbie in the area of SQL server - more "knows just
> enough to be dangerous"
> - so please be gentle with me.
> Cross posted to several groups - apologies if too far off topic
> I have an application that does a lot of massaging of data (insurance
> claims) from 3 different data sources to present one set of nice
> homogeneous output tables. Uses about 200 various tables and about 300
> queries to do the work ( sounds like a mess - but trust me - it is
> quite disciplined)
> We are using 4 * 1Gb mdb backends and the client agrees it is time to
> move
> the backend to SQL server. They have made it clear that they do not
> wish to convert any of the queries to pass thru queries - ie - I am
> just replacing my attachments to mdb tables with attachments to SQL
> tables. They accept that it will probably run even slower due to the
> extra SQL overheads.
> I have dealt with most issues in the conversion but the 3 show
> stoppers are:
> 1) Half way through the process I get a "record is deleted" message
> when one of the queries attempts to run. I am guessing that there is a
> synchronisation problem between any earlier query that empties a table
> and an "append" query that refills it, and maybe a subsequent select
> query that uses the refilled table.
> Can anyone give me some code snippets in access to force a query to
> flush all its results to SQL before I embark on the next query.
>
If you are running a single process, then each query is complete before the
next one begins.
> 2) Initially I used the data transformation services to load all the
> access backend tables across to SQL server.
> I then retweaked my homebrew attachment routines to handle attaching
> to an SQL table - everything worked fine.
> Of course the tables were not updateable due to a lack of primary keys
> in the SQL tables.
> No problem - I worked my way through the SQL tables building
> constraints and / or primary keys.
> Then I found that my attachment routines would fail for some of the
> tables - message being (paraphrasing) - "I can't find that table or
> the table name is too long"
> If I went back in to SQL server and shortened the table names down to
> about 20 characters - then the problem went away.
> I even adjusted my Access attachment routines so that I could still
> keep the desired attached table names.
> The problem is more for the client - when they go to point Cognos at
> the SQL tables - they will need to do some reworking (or maybe Cognos
> has an alias facility)
> Can anyone shed any light on this situation.
SQL table names can be up to 128 characters long... I suspect you are seeing
some Access restriction, not a sql restriction.
> 3) When I set up DSN's on my (Win 98) machine - they appear to store
> the user password quite happily.
> On the client's (XP) machine - when my attachment routine runs - it
> appears that the DSN is not holding the password anymore - and we get
> prompted for the password, for every table that is being attached.
> Can anyone explain why ?
XP DSNs hold login/password if you choose NOT to use trusted
authentication... The create DSN walks you through this... Also, make sure
you are using a system DSN, so it will be valid for any user on the
machine...
> Many thanks in advance
> Tony

Data Synchronisation Issue

I am not quite a newbie in the area of SQL server - more "knows just
enough to be dangerous"
- so please be gentle with me.
Cross posted to several groups - apologies if too far off topic
I have an application that does a lot of massaging of data (insurance
claims) from 3 different data sources to present one set of nice
homogeneous output tables. Uses about 200 various tables and about 300
queries to do the work ( sounds like a mess - but trust me - it is
quite disciplined)
We are using 4 * 1Gb mdb backends and the client agrees it is time to
move
the backend to SQL server. They have made it clear that they do not
wish to convert any of the queries to pass thru queries - ie - I am
just replacing my attachments to mdb tables with attachments to SQL
tables. They accept that it will probably run even slower due to the
extra SQL overheads.
I have dealt with most issues in the conversion but the 3 show
stoppers are:
1) Half way through the process I get a "record is deleted" message
when one of the queries attempts to run. I am guessing that there is a
synchronisation problem between any earlier query that empties a table
and an "append" query that refills it, and maybe a subsequent select
query that uses the refilled table.
Can anyone give me some code snippets in access to force a query to
flush all its results to SQL before I embark on the next query.
2) Initially I used the data transformation services to load all the
access backend tables across to SQL server.
I then retweaked my homebrew attachment routines to handle attaching
to an SQL table - everything worked fine.
Of course the tables were not updateable due to a lack of primary keys
in the SQL tables.
No problem - I worked my way through the SQL tables building
constraints and / or primary keys.
Then I found that my attachment routines would fail for some of the
tables - message being (paraphrasing) - "I can't find that table or
the table name is too long"
If I went back in to SQL server and shortened the table names down to
about 20 characters - then the problem went away.
I even adjusted my Access attachment routines so that I could still
keep the desired attached table names.
The problem is more for the client - when they go to point Cognos at
the SQL tables - they will need to do some reworking (or maybe Cognos
has an alias facility)
Can anyone shed any light on this situation.
3) When I set up DSN's on my (Win 98) machine - they appear to store
the user password quite happily.
On the client's (XP) machine - when my attachment routine runs - it
appears that the DSN is not holding the password anymore - and we get
prompted for the password, for every table that is being attached.
Can anyone explain why ?
Many thanks in advance
TonySee inline
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
<ace join_to ware@.iinet.net.au (Tony Epton)> wrote in message
news:41abdc2f.3023390@.news.m.iinet.net.au...
> I am not quite a newbie in the area of SQL server - more "knows just
> enough to be dangerous"
> - so please be gentle with me.
> Cross posted to several groups - apologies if too far off topic
> I have an application that does a lot of massaging of data (insurance
> claims) from 3 different data sources to present one set of nice
> homogeneous output tables. Uses about 200 various tables and about 300
> queries to do the work ( sounds like a mess - but trust me - it is
> quite disciplined)
> We are using 4 * 1Gb mdb backends and the client agrees it is time to
> move
> the backend to SQL server. They have made it clear that they do not
> wish to convert any of the queries to pass thru queries - ie - I am
> just replacing my attachments to mdb tables with attachments to SQL
> tables. They accept that it will probably run even slower due to the
> extra SQL overheads.
> I have dealt with most issues in the conversion but the 3 show
> stoppers are:
> 1) Half way through the process I get a "record is deleted" message
> when one of the queries attempts to run. I am guessing that there is a
> synchronisation problem between any earlier query that empties a table
> and an "append" query that refills it, and maybe a subsequent select
> query that uses the refilled table.
> Can anyone give me some code snippets in access to force a query to
> flush all its results to SQL before I embark on the next query.
>
If you are running a single process, then each query is complete before the
next one begins.

> 2) Initially I used the data transformation services to load all the
> access backend tables across to SQL server.
> I then retweaked my homebrew attachment routines to handle attaching
> to an SQL table - everything worked fine.
> Of course the tables were not updateable due to a lack of primary keys
> in the SQL tables.
> No problem - I worked my way through the SQL tables building
> constraints and / or primary keys.
> Then I found that my attachment routines would fail for some of the
> tables - message being (paraphrasing) - "I can't find that table or
> the table name is too long"
> If I went back in to SQL server and shortened the table names down to
> about 20 characters - then the problem went away.
> I even adjusted my Access attachment routines so that I could still
> keep the desired attached table names.
> The problem is more for the client - when they go to point Cognos at
> the SQL tables - they will need to do some reworking (or maybe Cognos
> has an alias facility)
> Can anyone shed any light on this situation.
SQL table names can be up to 128 characters long... I suspect you are seeing
some Access restriction, not a sql restriction.

> 3) When I set up DSN's on my (Win 98) machine - they appear to store
> the user password quite happily.
> On the client's (XP) machine - when my attachment routine runs - it
> appears that the DSN is not holding the password anymore - and we get
> prompted for the password, for every table that is being attached.
> Can anyone explain why ?
XP DSNs hold login/password if you choose NOT to use trusted
authentication... The create DSN walks you through this... Also, make sure
you are using a system DSN, so it will be valid for any user on the
machine...
> Many thanks in advance
> Tony

Data synchronisation across the Firewall

Narendra,
These articles should help:
http://support.microsoft.com/?id=321822
http://support.microsoft.com/default.aspx?scid=kb;en-
us;287932&Product=sql
Rgds,
Paul Ibison
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Hello Paul,
thanks for the reply. But in our case, opening any special ports like
1433 for SQL server is out of question. I guess there must be another way
out.
Nor can we afford to have VPN connectivity.
regards
Narendra
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:35e701c4a47c$bc6a7590$a401280a@.phx.gbl...
> Narendra,
> These articles should help:
> http://support.microsoft.com/?id=321822
> http://support.microsoft.com/default.aspx?scid=kb;en->
us;287932&Product=sql
> Rgds,
> Paul Ibison
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Narendra,
you'll need the SQL Server TCP/IP port to be open.
In SQL Server 2005 you can do merge replication over
HTTPS and it is quite simple to set up, but there is no
such thing in SQL Server 2000 (unless you are using CE),
and no file-based solution.
Rgds,
Paul Ibison
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
sql