Attach Access Database to SQLServer

Attach Access Database to SQLServer?

It seems it is necessary if I want to put it on the internet through IIS.

I tried add data source through tools and tried most combinations, but nothing led in that direction.

I also did a search.

If not, the alternative is importing the data into sqlserver 2005. What worries me about this is incrementally importing new tables, views, etc., and new rows. Is this later possible?

dennist685

> It seems it is necessary if I want to put it on the internet through IIS.

No. My guess is that you might security issues. The account of which runs your statement (IIS account or ASP account, I guess) to have the rights to read your mdb file. Should be mentioned in numerous threads.

> If not, the alternative is importing the data into sqlserver 2005. What worries me about this is incrementally importing new tables, views, etc., and new rows. Is this later possible?

This would be my soulution. I would use SQL Server (maybe Express) to serve data.

You can access Access tables from within SQL Server queries. I would keep away from doing this for normal processing. For import of data, it is OK, I guess.

-- Sample to SELECT against object in access db:

EXEC sp_configure 'show advanced options', 1

GO

RECONFIGURE

GO

EXEC sp_configure 'Ad Hoc Distributed Queries', 1

GO

RECONFIGURE

GO

SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'x:\dir\mymdb.mdb';'admin';'',objectname)

GO

The sp_configure statments are neccessary because distributed queries are turned off by default for security reasons

Hope this helps

sql

assigning a data source

Is there any other way to a assign a data source to a report other than
through the Report Manager. The reason I ask is because when I try to
browse for data sources in Report Manager, I get "Object reference not set
to an instance of an object". Microsoft tech support seems unable to help
with this error message so I'm wondering if assigning a data source can be
done outside of the Manager.
--Will you send the Report Manager log file that contains the object reference
exception? The file name should be ReportServerWebApp_<current date>.log
--
This posting is provided "AS IS" with no warranties, and confers no rights
"Richard Cranium" <mgreco40@.hotmail.com> wrote in message
news:%23lOzEIpmEHA.2096@.TK2MSFTNGP15.phx.gbl...
> Is there any other way to a assign a data source to a report other than
> through the Report Manager. The reason I ask is because when I try to
> browse for data sources in Report Manager, I get "Object reference not set
> to an instance of an object". Microsoft tech support seems unable to help
> with this error message so I'm wondering if assigning a data source can be
> done outside of the Manager.
> --
>
>|||I received the strack trace from the PSS engineer you were working with. I
believe you have view state disabled on your machine. In the web.config
file in the Report Manager directory, add the attribute
enableViewState="true" to the system.web/pages tag:
<system.web>
<pages enableViewState="true" />
</system.web>
--
This posting is provided "AS IS" with no warranties, and confers no rights
"Richard Cranium" <mgreco40@.hotmail.com> wrote in message
news:%23lOzEIpmEHA.2096@.TK2MSFTNGP15.phx.gbl...
> Is there any other way to a assign a data source to a report other than
> through the Report Manager. The reason I ask is because when I try to
> browse for data sources in Report Manager, I get "Object reference not set
> to an instance of an object". Microsoft tech support seems unable to help
> with this error message so I'm wondering if assigning a data source can be
> done outside of the Manager.
> --
>
>

Assign Variable for FileName of Source File

I am wanting to capture the file name I am using to load data from and use it in a SQL statement to insert it along with the data that I load.

I am using a ForEach container to load all my .txt files but cannot figure out how to capture the name of each source file as it loops through the files and then add it to my insert statement that is populating my history table. I would think that the ForEach container has that information, but I do not see how to access it and assign it to a variable that I could use in my SQL statement.

Any ideas or suggestions?

The ForEach loop container can store the name of eac enumerated file into a variable. This article explains how to do that: http://blogs.conchango.com/jamiethomson/archive/2005/05/30/1489.aspx

Once its in a variable you can do pretty much anything you want with it and it seems you already know how to do that. If not, just reply here.

-Jamie

|||

Thanks for the link, but I have was able to setup a variable @.[User::FN] to the ConnectionString property associated with the flat file connection, and the looping and loading effort is working fine.

This may seem like a stupid question, and I apologize, but I cannot figure out how to reference that variable and use it in my execute SQL task that follows later and does my insert of the data into my table. What I want to do is something like this:

Insert into Table1 Select a, b, c, @.FN From Table2

The parser requires that I declare the variable and does not seem to recognize the @.[User::FN] variable.

Any suggestions?

|||

Yes, use an expression. This (sort of) shows you how:

http://blogs.conchango.com/jamiethomson/archive/2005/12/09/2480.aspx

-Jamie

asp.net report (rdlc) file cannot create dataSource

With asp.net 2005 - after I create a report (rdlc) file, I try to create a
Data Source (Report menu, then click "Data Sources", I get error:
The root element of a WC3 XML Schema should be <schema> and its namespace
should be 'http://www.w3.org/2001/XMLSchema'.
Can I fix this?
Thanks
--
JayYou can create the data source later in the ReportingServe Manager.
Dont use shared data sources in this case..
I dont know why you are getting that error but that is an alternative.