Interfacing SQL Server with Access...

I have a web based SQL Server application that I need to modify to include data that is currently stored in an Access mdb. The mdb file and SQL Server are running on the same box. The data that is on the Access system cannot be migrated to SQL Server since it is a commercial package. But in our web application we want to query this data (and that in the SQL Server database) and present both current information to the users. We don't need to edit the Access data. Just view it and combine it with data we now have in SQL Server.

I was thinking I would build views or stored procedures that would pull info from both the SQL Server tables and the Access tables. But I'm not sure how efficient this is. One issue is that the users are remote, meaning many will not be in the same building that the SQL Server box is located at.

What is the best way to accomplish this? Any ideas? I've thought about using DTS but maybe it is overkill.

Thanks for the help.I would not query the access data from a web app. It will choke and die once you get about 20 connectins to it at a time. If you can live with the access data not being real time I would setup a job that fires daily or hourly that pulls the data into sql server daily or hourly or every 15 minutes. Or if you have the development manpower, redevelop the Access app to use sql as the datasource with an Access Data Project. Access as a backend to a web app is a recipe for disaster. Seen it before.|||Thanks for the advice Sean. Seeing as we cannot redevelop the backend for SQL Server, our only option appears to be batch updating of data into our system from Access on a scheduled basis. Would we use something like DTS for this? What can we do in SQL Server to facilitate the transfer of data from Access to SQL Server?

Thanks Sean.|||Use a SQL Server Agent job that fires a DTS job.|||Use a SQL Server Agent job that fires a DTS job.

Thanks. Will do.sql

Interesting question

hi,
I am thinking if Transactional backup is based on the data in the
Transaction Log. what if i set up a the Log Reader to be run every 1 hour,
but within that 1 hour, i do a transactional backup and issue the command
"CheckPoint"!!! Can the log reader still be able to read what i have changed
before the backup?
Thanks
Ed
Ed,
transactions are only removed from the transaction log once sp_repldone has
been issued. This is called by the log reader agent, so backing up the log,
using simple recovery mode, issuing checkpoints etc won't make any
difference.
HTH,
Paul Ibison, SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||transactions are never removed from the log otherwise doing any form of
recovery using the log would be useless. Rather they are marked as
replicated.
Hilary Cotter
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
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23X7hZXnKFHA.3512@.TK2MSFTNGP15.phx.gbl...
> Ed,
> transactions are only removed from the transaction log once sp_repldone
has
> been issued. This is called by the log reader agent, so backing up the
log,
> using simple recovery mode, issuing checkpoints etc won't make any
> difference.
> HTH,
> Paul Ibison, SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Thanks for the correction! What I meant is that they can
be removed on backup of the log or truncation, although
my Englissh let me down
Rgds,
Paul
|||I thought that was what you meant, but it wasn't clear And you know me -
I am very easily confused.
Hilary Cotter
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
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:142101c52afc$b2e69bf0$a601280a@.phx.gbl...
> Thanks for the correction! What I meant is that they can
> be removed on backup of the log or truncation, although
> my Englissh let me down
> Rgds,
> Paul
>

Interactive column sort in Reporting services

Hi,

I have a report with fiive columns, I have implemented interactive column sorting on the report. I have added a group to the report based on Column 2 and there is a page break by group. Now if I am on the second page ( page break by column 2 ) and sort on column 3(there is no grouping on column 3), the sorting happens but after the sort, the first page is displayed.IS there any way to remain on the same page while sorting?

Thanks in Advance.

I do not think that is possible. Once you click on the sort it will sort all the pages in the report.|||

It is ok if it sorts all the pages. I wanted to know if there is any way i can stick to the same page even after sorting. ie. If I am on page 5 and I click on sort, it sorts that records and takes me back to Page 1. Is there any way I can remain on page 5 after sorting?

|||I agree... I don't think that is possible (not without some nifty trickery), but do you really want that anyways? What good does it do to remain on the same page if the data is sorted differently? The data being referenced on that page will not be the same so you might as well start from the beginning.

Interactive column sort in Reporting services

Hi,

I have a report with fiive columns, I have implemented interactive column sorting on the report. I have added a group to the report based on Column 2 and there is a page break by group. Now if I am on the second page ( page break by column 2 ) and sort on column 3(there is no grouping on column 3), the sorting happens but after the sort, the first page is displayed.IS there any way to remain on the same page while sorting?

Thanks in Advance.

I do not think that is possible. Once you click on the sort it will sort all the pages in the report.|||

It is ok if it sorts all the pages. I wanted to know if there is any way i can stick to the same page even after sorting. ie. If I am on page 5 and I click on sort, it sorts that records and takes me back to Page 1. Is there any way I can remain on page 5 after sorting?

|||I agree... I don't think that is possible (not without some nifty trickery), but do you really want that anyways? What good does it do to remain on the same page if the data is sorted differently? The data being referenced on that page will not be the same so you might as well start from the beginning.

Integration with Team Foundation Server

Hi Everyone.
Does Business Intelligence Development Studio (BIDS) integrate with the Team Foundation Server? I've read that BIDS is based on VS.NET, but it's a different client than standard VS.NET.
Can we setup a Team Foundation Project and integrate the work item lists, check in policies, etc, with BIDS?
Thanks,
RobBIDS is actually just a copy of the VS IDE so in theory it should work. But it does not come with the Team Foundation Project, only the BI projects. YOu can upgrade BIDS to a full copy of VS by just installing over the top, this will keep the BI projects. Or you can just install the BI projects into a VS installation.

Management Studio uses VS technology and so will not automatically work with TS.|||I have this working without problem, though my understanding was that the latest patch set for TFS included support for the full set of database projects in VSTS.

Integration with Team Foundation Server

Hi Everyone.
Does Business Intelligence Development Studio (BIDS) integrate with the Team Foundation Server? I've read that BIDS is based on VS.NET, but it's a different client than standard VS.NET.
Can we setup a Team Foundation Project and integrate the work item lists, check in policies, etc, with BIDS?
Thanks,
RobBIDS is actually just a copy of the VS IDE so in theory it should work. But it does not come with the Team Foundation Project, only the BI projects. YOu can upgrade BIDS to a full copy of VS by just installing over the top, this will keep the BI projects. Or you can just install the BI projects into a VS installation.

Management Studio uses VS technology and so will not automatically work with TS.|||I have this working without problem, though my understanding was that the latest patch set for TFS included support for the full set of database projects in VSTS.

Integration Services Data Types Maximum Length

Hi,

Is there a way in-code to determine the maximum length of a Integration Services Data Type.

I need to determine based on the data type what the maximum length of a column is IN-CODE.

However, the column.Length property only gives me a length for DT_WSTR and DT_STR values. This is the only property that would seem to remotely give me the right answer.

I need to know the maximum lengths in columns for DT_BOOL, DT_CY, DT_I2, DT_I4, DT_I8, DT_NUMERIC, and DT_UI1. I can always hard-code these values into my program, but that makes no sense. There has to be some sort of way to determine what the maximum possible length of these values are.

For numeric values I could use the column.Precision value but that still leaves with with a lot of data types without a maximum length.
It might help if you explain why you want to know this information.

A DT_I4 is 4 bytes long, for instance, but it can hold -2,147,483,648 to 2,147,483,647. So which value do you want? Do you see the problem?

Data types are fixed in what they can handle, so why not just hard code them into your "IN-CODE"?|||In your example, for DT_I4, -2,147,483,648 is a length of 11 (excluding commas, but including the sign). Thats the kind of value I need to know. I apologize if I wasn't clear on that.

Well, yes, I could hard-code them. But I was wondering whether that type of information is actually stored (which really if you think about it, shouldn't be that difficult, and also worthwhile so a person does not have the compute what each one would be).

Hard-coding is something you also try to avoid if there is a way to avoid it (and hence my question as to whether I can avoid this or not).

Thanks

|||But that's just it, the length of a DT_I4 is 4, not 11.

The "length" of 11 is not stored anywhere. It's a limitation of the storage allocated to the data type. There are probably internal bounds checks, but I'm guessing they are not exposed. You could always try one of the many programming forums in MSDN to see if someone has an idea, as this really isn't an SSIS issue. SSIS data types are simply mapped to structures in .Net.

And the max length of a DT_STR is 8000 bytes. DT_WSTR is 4000 bytes. But more importantly, there is no such thing as a DT_STR, DT_I4, etc... in code. So what SSIS has as limitations may not be the same as the structures available in your code. (A DT_I4 equates to the Integer (Int32) structure.)|||

theddern wrote:

Hi,

Is there a way in-code to determine the maximum length of a Integration Services Data Type.

I need to determine based on the data type what the maximum length of a column is IN-CODE.

However, the column.Length property only gives me a length for DT_WSTR and DT_STR values. This is the only property that would seem to remotely give me the right answer.

I need to know the maximum lengths in columns for DT_BOOL, DT_CY, DT_I2, DT_I4, DT_I8, DT_NUMERIC, and DT_UI1. I can always hard-code these values into my program, but that makes no sense. There has to be some sort of way to determine what the maximum possible length of these values are.

For numeric values I could use the column.Precision value but that still leaves with with a lot of data types without a maximum length.

There is no maximum/minimum length for those data types. They are just what they are - they never change. The length simply isn't relevant.

-Jamie

|||

theddern wrote:

In your example, for DT_I4, -2,147,483,648 is a length of 11 (excluding commas, but including the sign).

No its not. The length of it is 4. 4 bytes that is.

If the value that you want is 11 then just cast it as a DT_STR and get the length of that.

-Jamie

|||So what you are saying is that I need to hard-code the lengths if I need to know those particular type of values?

|||

theddern wrote:

So what you are saying is that I need to hard-code the lengths if I need to know those particular type of values?

Yes! I just can't see the value in knowing the "lengths" of data types.|||And to clarify, I need to know what the maximum length (in characters) a particular column can be if that particular data was transfered to a command delimited flat file.

I was trying to figure that out based on IDTSColumn90.DataType because that would be the most logical place to start.
|||Are you wanting the display length for each data type?|||

theddern wrote:

And to clarify, I need to know what the maximum length (in characters) a particular column can be if that particular data was transfered to a command (sic) delimited flat file.

Why? If it is a delimited file, who cares?|||If he wanted to document specs for a fixed-length output file, he would find these useful. |||

theddern wrote:

So what you are saying is that I need to hard-code the lengths if I need to know those particular type of values?

No. I'm saying you don't need to care.

|||

Phil Brammer wrote:

theddern wrote:

And to clarify, I need to know what the maximum length (in characters) a particular column can be if that particular data was transfered to a command (sic) delimited flat file.

Why? If it is a delimited file, who cares?

Very very very good point.

This seems a very strange thing to want to do.

|||Yes