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 SOurce View and Missing Diagrams

Guys, I am a newbie working through the SSAS tutorial. I am at lesson 5 and something has gone wrong with my Data Source View. Although I have diagrams in the Diagram Source pane I can not get them to show in the actual diagram pane. That pane just shows a grey background rather than the cream canvas. New diagrams in that data source view show the same problem.

If I create a new Data Sorce View, I can create diagrams and see them.

I do not want to create a new Data Sorce View and abandon the old one, because it will break the tutorial. I do not have a record of everything that was in the old one. Neither do I know how to repoint my cube at the new Data Sorce View.

Can someone please tell me how to restore the view of the diagrams.

Thanks

Cliff

How about re-installing the tutorial files? It is hard to tell what was wrong, but you might want to send the broken solution to andrewgaATnetzeroDOTcom and i will take a look.

In order to change the data source view for the cube you can invoke View | Code menu item when the cube designer is open, locate the XML code like below and replace the ID of the DSV.

<Source dwd:design-time-name="80d8b583-e073-4437-83cc-f17c461bfa13">
<DataSourceViewID>Simple Sales</DataSourceViewID>
</Source>

Note, that you will probably have to change bindings for existing measures.sql

Data Parameter Question

Hi All:
Reporting Services newbie here. I have a query that has a fieldcalled Date. I have created two parameters in my report namedStart Date and End Date.
I need to know the proper syntax to build an expression that willreturn a recordset that contains all records with dates between mystart and end dates.
How do I accomplish this? And also, does anyone know of aresource anywhere that lays out examples of the syntax for variousexpressions used in Reporting Services. The help documentationisn't very clear.
Thanks
Brennan

What you need involve Time Interval and it is not implemented in SQL Server now but I found a UDF(User Defined Function) that can do it. Use it and you will get what you need, I would also look at the stored procs in the Time Tracker starter kit it will have time interval cals. Try the link below for the code. Hope this helps.

http://www.novicksoftware.com/UDFofWeek/Vol1/T-SQL-UDF-Volume-1-Number-38-udf_DT_AddTime.htm

|||you can either call a stored proc that takes the startdate and enddate as parameters and gives you the result set or you can get the entire result set from the table and use filters to filter out the data between the dates. I'd recommend using a stored proc.|||

I am not sure if this is what you need but like most suggest you should use a stored proceedure but the syntax for the query would look like:

SELECT column_name FROM table_nameWHERE column_nameBETWEEN @.start_dateAND @.end_date

You can just try this directly in the report designer before making a stored procedure for it.

Note: @.start_date and @.end_date will be the value the user put in the parameter box.

|||Hi,
I have a similar question as you do, in fact I'm tryning to filter my report results between two dates, and I'm trying to do this directly in the Report designer using two report parameters, for the begin and for the end date. My problem is that the data type available for date parameters is "Date Time", and I want my parameter formated as a Date ( I don′t want to do it on the query because the parameter mus be shown as a date in the report) .Is there a way of doing it? Or I must set the parameters as strings, and cast them in the query? If so, how can I send an error message if the dates are not well fomated?
Thanks in advance for any help!
|||I didnt understand your question. Can you rephrase ?|||

Yes, what I wrote before was a little bit confuse,

In fact I have 2 different questions:

The first one is, can I have a report parameter configured as a Date instead of Date Time? (the data types available for the report parameters don't include the type Date).
The second question is, how can I send a pop-up with an error message in my report ? For example, if the parameter value inserted by the user is not valid for my report.

Thanks,
Cat.

|||

Hello:
Yes, just CAST the datetime value provided by the parameter within RS within SQL to the date - but thre is more to it than that!
The date passed as a parameter could be in any number of many formats - YYYY.DD/DD or YYYY-MM-DD, etc.
The dates based upon the culture or the dates stored within the data base!
This "date" thing drives me nuts at times in that I have to support Thai (which their year is 543 years ahead of the western calendar) but this is what I do and this may or may not be of help.
In my SQL data base I have dates that are in YYYY-MM-DD Hour:Minute:Sec, YYYYMMDD, MM/DD/YY, MM/DD/YYYY, DD/MM/YYYY, DD/MM/YY, etc. I did not create this mess but inherited it from programmers who developed the system in Thailand and Korea which I used the data within this database for another application -- I get really frustrated at times.
So, I define all date parameters in RS as strings. When an RS report is launched and the date parameter is displayed I provide a drop down list of available parameters which are:
NONE, Today, Yesterday, Beginning of Week, BI -Weekly, Beginning of Month, Monthly, Bi-Monthly, Quarterly, Yearly not in this particular order but nevertheless.
If they select NONE - I provide and additional two(2) parameters which allow them to enter the date in YYYYMMDD format.
I have also created a table in SQL called a Calendar Time Dimension (which is mandatory for Analysis Services).
The Calendar Time Dimension has a day for each day of the year and I have populated for 1995 through 2015.
The Calendar Time Dimension file has for each date within the year(s) that I populate the following:
Time Key which has a value for (eg. todays date 20050904) in the following formats:
YYYYMMDD, YYMMDD, MMDDYY, MMDDYYYY, DDMMYY, DDMMYYYY, MM/DD/YYYY, DD/MM/YYYY, including the Thai date which ifor today i s 20050904 + 543 (years) = 25480904, etc. I have all of the weirdo formats defined.
Also in the Calendar Time Dimension is the Calendar Week, Calendar Quarter, Fiscal Quarter, Month of Year, Week within Month, etc.
So when a user enters their own date via the date parameter (and elects not to select the any other parameter such as Today) within RS (a call to a stored procedure) I validate against the Calendar Time Dimension and if the date is invalid I provide back an error message via RS that indicates the date is invalid.
So my point (even though LOOONG WINDED) is that you should consider to manipulate the dates within a stored procedure based upon anything entered by the user within the date parameter within RS and validate against a similar Calendar Time Dimension table within the data base. So when a date is valid you use a JOIN to the Calendar Time Dimension to filter out the records you want within SQL. Another added benefit is that this really improves performance in that you are not filtering or comparing dates within each record within the DB.
If the user selects TODAY, Yesterday, Weekly etc. I have UDF's that generate the date ranges I use against the Calendar Time Dimension to process only the records I need against the table I go after in the Stored Procedure for the respective RS program.
If you want I can give you a stored procedure to create the Calendar TIme Dimension and then you can go from there.
Best Regards,

|||Thanks a lot for your post!
Cat.

Data Import from Multiple Tables

Hi all,
I am a newbie to .NET and would appreciate all your valublesuggestions. I have and issue were I am trying to import data from afew selected columns MS Access and a couple of columns in SQLServer Table Y and trying to populate another table X . Bothtables X and Y are in the same Database . I am wondering if I coulddesign a custom package for this task.
Ananth
There might be better ways to do this, but...
I would first create a temporary table, and take and dump all of the relevant Access data into that temporary table (via DTS). From there, joining and inserting into Table Y should be pretty easy. When you're done, delete the temporary table.
(And I say temporary, but not TEMP).
I'm sure there are more efficient ways of doing this, and of course it depends on how much data is in the access table. But probably the easiest way to create the dataset you will need for Table Y will be by using T-SQL. So having everything within the same database will make that easier.|||Thanks a lot for the suggestion. I appreciate it.