How to exclude read-only connections from DTC transactions?

Hi
I'm importing from an Access db into SQL 2005 using SSIS, and I want to execute the package inside a transaction so that I can rollback the import if I get an error. However, it seems the Access connection doesn't support DTC as I get the following error:
[Connection manager "MyConnection"] Error: The connection does not support enlisting in distributed transaction. The error goes away if I set the data flow task's TransactionOption to Supported rather than Required, but obviously I don't then get transactions.
Now, all I want to do is to read from the Access connection so I don't require it to be involved in the transaction. Is there any way I can tell SSIS this, so that it doesn't fall over? Or is there another way to achieve what I want?
On a side note - the read-only "SupportsDTCTransactions" property for the Access connection is set to True even though this is clearly not the case - would this be a bug?
[Also posted to microsoft.private.sqlserver2005.dts - I don't know which is the preferred forum - apologies]
Hi,
I got the same error when I tried to exclude from the distributed transaction a connection to an Excel source. So it seems to be a problem of Microsoft.Jet.OLEDB provider. I also inserted "OLE DB Services = -7;" in the connection string, but it didn't work.|||

Were I in your shoes, I think I'd load into a temporary table (outside of the transaction), then, inside a transaction, load the real table with an "insert into select from" Execute SQL task.

|||Yeah, I can see that would work, but it'd be fairly inefficient once the data volumes get high. I suppose with Access as a source there's not much danger of that - but I wonder how many other data sources are affected by this?
|||

There is same error in my projects. I import data from some dbf files to SQL Server 2005. And I must delete the data from Table A first, then import the data to that table. These two steps shoule be included in a transaction. But SSIS give the error like below:

Error: 0xC0014034 at Package, Connection manager "foxpro": The connection does not support enlisting in distributed transaction.

Error: 0xC0202009 at Package, Connection manager "foxpro": An OLE DB error has occurred. Error code: 0x8000FFFF.

Error: 0xC020801C at 导入CDWArea, OLE DB Source [1]: The AcquireConnection method call to the connection manager "foxpro" failed with error code 0xC0202009.

Error: 0xC004701A at 导入CDWArea, DTS.Pipeline: component "OLE DB Source" (1) failed the pre-execute phase and returned error code 0xC020801C

At the beginning, I use the conncetion driver of "Microsoft Jet 4.0", then I change it to "Visual Foxpro 9.0 ole db". But they are useless.

Anyone? Thansk!

How to exclude read-only connections from DTC transactions?

Hi
I'm importing from an Access db into SQL 2005 using SSIS, and I want to execute the package inside a transaction so that I can rollback the import if I get an error. However, it seems the Access connection doesn't support DTC as I get the following error:
[Connection manager "MyConnection"] Error: The connection does not support enlisting in distributed transaction. The error goes away if I set the data flow task's TransactionOption to Supported rather than Required, but obviously I don't then get transactions.
Now, all I want to do is to read from the Access connection so I don't require it to be involved in the transaction. Is there any way I can tell SSIS this, so that it doesn't fall over? Or is there another way to achieve what I want?
On a side note - the read-only "SupportsDTCTransactions" property for the Access connection is set to True even though this is clearly not the case - would this be a bug?
[Also posted to microsoft.private.sqlserver2005.dts - I don't know which is the preferred forum - apologies]
Hi,
I got the same error when I tried to exclude from the distributed transaction a connection to an Excel source. So it seems to be a problem of Microsoft.Jet.OLEDB provider. I also inserted "OLE DB Services = -7;" in the connection string, but it didn't work.|||

Were I in your shoes, I think I'd load into a temporary table (outside of the transaction), then, inside a transaction, load the real table with an "insert into select from" Execute SQL task.

|||Yeah, I can see that would work, but it'd be fairly inefficient once the data volumes get high. I suppose with Access as a source there's not much danger of that - but I wonder how many other data sources are affected by this?
|||

There is same error in my projects. I import data from some dbf files to SQL Server 2005. And I must delete the data from Table A first, then import the data to that table. These two steps shoule be included in a transaction. But SSIS give the error like below:

Error: 0xC0014034 at Package, Connection manager "foxpro": The connection does not support enlisting in distributed transaction.

Error: 0xC0202009 at Package, Connection manager "foxpro": An OLE DB error has occurred. Error code: 0x8000FFFF.

Error: 0xC020801C at 导入CDWArea, OLE DB Source [1]: The AcquireConnection method call to the connection manager "foxpro" failed with error code 0xC0202009.

Error: 0xC004701A at 导入CDWArea, DTS.Pipeline: component "OLE DB Source" (1) failed the pre-execute phase and returned error code 0xC020801C

At the beginning, I use the conncetion driver of "Microsoft Jet 4.0", then I change it to "Visual Foxpro 9.0 ole db". But they are useless.

Anyone? Thansk!

How to exclude read-only connections from DTC transactions?

Hi
I'm importing from an Access db into SQL 2005 using SSIS, and I want to execute the package inside a transaction so that I can rollback the import if I get an error. However, it seems the Access connection doesn't support DTC as I get the following error:
[Connection manager "MyConnection"] Error: The connection does not support enlisting in distributed transaction. The error goes away if I set the data flow task's TransactionOption to Supported rather than Required, but obviously I don't then get transactions.
Now, all I want to do is to read from the Access connection so I don't require it to be involved in the transaction. Is there any way I can tell SSIS this, so that it doesn't fall over? Or is there another way to achieve what I want?
On a side note - the read-only "SupportsDTCTransactions" property for the Access connection is set to True even though this is clearly not the case - would this be a bug?
[Also posted to microsoft.private.sqlserver2005.dts - I don't know which is the preferred forum - apologies]
Hi,
I got the same error when I tried to exclude from the distributed transaction a connection to an Excel source. So it seems to be a problem of Microsoft.Jet.OLEDB provider. I also inserted "OLE DB Services = -7;" in the connection string, but it didn't work.|||

Were I in your shoes, I think I'd load into a temporary table (outside of the transaction), then, inside a transaction, load the real table with an "insert into select from" Execute SQL task.

|||Yeah, I can see that would work, but it'd be fairly inefficient once the data volumes get high. I suppose with Access as a source there's not much danger of that - but I wonder how many other data sources are affected by this?
|||

There is same error in my projects. I import data from some dbf files to SQL Server 2005. And I must delete the data from Table A first, then import the data to that table. These two steps shoule be included in a transaction. But SSIS give the error like below:

Error: 0xC0014034 at Package, Connection manager "foxpro": The connection does not support enlisting in distributed transaction.

Error: 0xC0202009 at Package, Connection manager "foxpro": An OLE DB error has occurred. Error code: 0x8000FFFF.

Error: 0xC020801C at 导入CDWArea, OLE DB Source [1]: The AcquireConnection method call to the connection manager "foxpro" failed with error code 0xC0202009.

Error: 0xC004701A at 导入CDWArea, DTS.Pipeline: component "OLE DB Source" (1) failed the pre-execute phase and returned error code 0xC020801C

At the beginning, I use the conncetion driver of "Microsoft Jet 4.0", then I change it to "Visual Foxpro 9.0 ole db". But they are useless.

Anyone? Thansk!

sql

How to encrypt data - SQL Server 2000

I have a linked table (SQL Server) in a MS Access 2000 mdb
I have a table called tbl_users which contains a pwd field that I would like
to see encrypted when I open the table. I don't want to use the Input mask
property because everyone can remove it.
How can I encrypt that field so it will be in an unreadable format to users?Steph
Don't do it on database side. Encrypt it on the client site
I suggest you to search(Google) for Steve Kass's recomendations for such
problems.
"Steph" <microsoft.public.access.formscoding> wrote in message
news:%23JQLk3TCFHA.3688@.TK2MSFTNGP14.phx.gbl...
> I have a linked table (SQL Server) in a MS Access 2000 mdb
> I have a table called tbl_users which contains a pwd field that I would
like
> to see encrypted when I open the table. I don't want to use the Input
mask
> property because everyone can remove it.
> How can I encrypt that field so it will be in an unreadable format to
users?
>

How to enable Remote Erros on Report Server 2005?

hi, all

When i tried to access a report via URL and I got an error page like:

Reporting Services Error

---

For more information about this error navigate to the report server on the local server machine, or enable remote errors

---

SQL Server Reporting Services

So, How to enable the remote errors and then I was able to see what is wrong.

Thanks.

You could set the "EnableRemoteErrors" configuration value to True in the ReportServer.ConfigurationInfo database table.

Alternatively, you can use the SetSystemProperties SOAP call:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsprog/htm/rsp_ref_soapapi_service_lz_63le.asp

-- Robert

|||Hi, Sorry from the newbie question :)

I saw the answer:
"You could set the "EnableRemoteErrors" configuration value to True in the ReportServer.ConfigurationInfo database table."

How can I get to the ReportServer.ConfigurationInfo database table?

I am on the reportservice server and I run "reporting services configuration manager" and the "sql server managment". I didn't saw that option :(

Any help?
Thanks.
|||

Hi,

Go To SQL Server Management Studion , Select ReportServer Database and run below Statement

Select * From ConfigurationInfo

Where Name = 'EnableRemoteErrors'

Change the Value to True

Thanks

Nilesh

How to enable Remote Erros on Report Server 2005?

hi, all

When i tried to access a report via URL and I got an error page like:

Reporting Services Error

---

For more information about this error navigate to the report server on the local server machine, or enable remote errors

---

SQL Server Reporting Services

So, How to enable the remote errors and then I was able to see what is wrong.

Thanks.

You could set the "EnableRemoteErrors" configuration value to True in the ReportServer.ConfigurationInfo database table.

Alternatively, you can use the SetSystemProperties SOAP call:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsprog/htm/rsp_ref_soapapi_service_lz_63le.asp

-- Robert

|||Hi, Sorry from the newbie question :)

I saw the answer:
"You could set the "EnableRemoteErrors" configuration value to True in the ReportServer.ConfigurationInfo database table."

How can I get to the ReportServer.ConfigurationInfo database table?

I am on the reportservice server and I run "reporting services configuration manager" and the "sql server managment". I didn't saw that option :(

Any help?
Thanks.

How to enable Remote Erros on Report Server 2005?

hi, all

When i tried to access a report via URL and I got an error page like:

Reporting Services Error

---

For more information about this error navigate to the report server on the local server machine, or enable remote errors

---

SQL Server Reporting Services

So, How to enable the remote errors and then I was able to see what is wrong.

Thanks.

You could set the "EnableRemoteErrors" configuration value to True in the ReportServer.ConfigurationInfo database table.

Alternatively, you can use the SetSystemProperties SOAP call:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsprog/htm/rsp_ref_soapapi_service_lz_63le.asp

-- Robert

|||Hi, Sorry from the newbie question :)

I saw the answer:
"You could set the "EnableRemoteErrors" configuration value to True in the ReportServer.ConfigurationInfo database table."

How can I get to the ReportServer.ConfigurationInfo database table?

I am on the reportservice server and I run "reporting services configuration manager" and the "sql server managment". I didn't saw that option :(

Any help?
Thanks.

How to enable remote erros for report server?

hi, all
When i tried to access a report via URL and I got an error page like:
Reporting Services Error
----
For more information about this error navigate to the report server on
the local server machine, or enable remote errors
----
SQL Server Reporting Services
So, How to enable the remote errors and then I was able to see what is
wrong.
Thanks.I'm getting the same error. We can get around the problem for now by logging
onto the server to see exactly what the error is, but I have yet to find out
how to turn on remote errors.
Any one have any ideas how to do this?
"Nick" wrote:
> hi, all
> When i tried to access a report via URL and I got an error page like:
> Reporting Services Error
> ----
> For more information about this error navigate to the report server on
> the local server machine, or enable remote errors
> ----
> SQL Server Reporting Services
> So, How to enable the remote errors and then I was able to see what is
> wrong.
> Thanks.
>|||I have done this with RS 2000.
It is in the machine.config file for .NET Framework v1.1 in the
<windows>\Microsoft.NET\Framework\<Version>\ folder.
There is a XML Tag <System.Web>/<customErrors>
I think you set the value to "RemoteOnly"
It looks like there is a similar tag for .NET 2.0 but the config file looks
a little different. Hopefully, this can can point you in the right direction
-Sean
"Nick" wrote:
> hi, all
> When i tried to access a report via URL and I got an error page like:
> Reporting Services Error
> ----
> For more information about this error navigate to the report server on
> the local server machine, or enable remote errors
> ----
> SQL Server Reporting Services
> So, How to enable the remote errors and then I was able to see what is
> wrong.
> Thanks.
>|||Sean, I found on another posting that this setting is within the
ConfigurationInfo table in the ReportServer database. Setting the value to
True makes it work.
Steve
"Sean P." wrote:
> I have done this with RS 2000.
> It is in the machine.config file for .NET Framework v1.1 in the
> <windows>\Microsoft.NET\Framework\<Version>\ folder.
> There is a XML Tag <System.Web>/<customErrors>
> I think you set the value to "RemoteOnly"
> It looks like there is a similar tag for .NET 2.0 but the config file looks
> a little different. Hopefully, this can can point you in the right direction
> -Sean
>
> "Nick" wrote:
> > hi, all
> >
> > When i tried to access a report via URL and I got an error page like:
> >
> > Reporting Services Error
> > ----
> >
> > For more information about this error navigate to the report server on
> > the local server machine, or enable remote errors
> > ----
> > SQL Server Reporting Services
> > So, How to enable the remote errors and then I was able to see what is
> > wrong.
> >
> > Thanks.
> >
> >|||setting *what* value exactly? my ConfigurationInfo table has 9 rows in
it and none of these contain anything that looks like it may be the right
setting to change.
heres the contents of my ConfigurationInfo table.
EnableExecutionLogging True
EnableMyReports False
ExecutionLogDaysKept 60
MyReportsRole My Reports
SessionTimeout 600
SiteName SQL Server Reporting Services
SystemReportTimeout 1800
SystemSnapshotLimit -1
UseSessionCookies true
"SteveIrwin" wrote:
> Sean, I found on another posting that this setting is within the
> ConfigurationInfo table in the ReportServer database. Setting the value to
> True makes it work.
> Steve
> "Sean P." wrote:
> > I have done this with RS 2000.
> > It is in the machine.config file for .NET Framework v1.1 in the
> > <windows>\Microsoft.NET\Framework\<Version>\ folder.
> >
> > There is a XML Tag <System.Web>/<customErrors>
> > I think you set the value to "RemoteOnly"
> >
> > It looks like there is a similar tag for .NET 2.0 but the config file looks
> > a little different. Hopefully, this can can point you in the right direction
> >
> > -Sean
> >
> >
> > "Nick" wrote:
> >
> > > hi, all
> > >
> > > When i tried to access a report via URL and I got an error page like:
> > >
> > > Reporting Services Error
> > > ----
> > >
> > > For more information about this error navigate to the report server on
> > > the local server machine, or enable remote errors
> > > ----
> > > SQL Server Reporting Services
> > > So, How to enable the remote errors and then I was able to see what is
> > > wrong.
> > >
> > > Thanks.
> > >
> > >|||My ConfigurationInfo table has the following info
EnableExecutionLogging True
EnableIntegratedSecurity true
EnableMyReports False
ExecutionLogDaysKept 60
ExternalImagesTimeout 600
MyReportsRole My Reports
SessionTimeout 600
SiteName SQL Server Reporting Services
SnapshotCompression SQL
SystemReportTimeout 1800
SystemSnapshotLimit -1
UseSessionCookies true
Do we need to add a record for customErrors?

How to enable full text search for symbols?

scenario:
I have a user interface done in Coldfusion that access SQL server 2K store
procedure on server 2003 that perform full text search on a field of a table.
I have remove all content of noise.enu as user can use anything as a search
index.
Why is it that SQL server or SQL Analyser still return err/msg when I use
symbol as serach index (e.g. {/}/?/>/</./,/@./!/$):
"Server: Msg 7619, Level 16, State 1, Line 1
Execution of a full-text operation failed. A clause of the query contained
only ignored words." - error msg from SQL Analyser
At minimun SQL server should return no recors. Instead it return err/msg,
that hangs my SQL service when run from Coldfusion and i had to manual
restart my service. Any ideas ?
These are invalid characters which cannot be searched upon. This should not
be hanging your SQL Server however. You should be trapping for such
characters on the client and removing them if the user is trying to search
on them.
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
"eslim" <eslim@.discussions.microsoft.com> wrote in message
news:95366FB3-4621-421E-9F5B-DD3118798A5F@.microsoft.com...
> scenario:
> I have a user interface done in Coldfusion that access SQL server 2K
> store
> procedure on server 2003 that perform full text search on a field of a
> table.
> I have remove all content of noise.enu as user can use anything as a
> search
> index.
> Why is it that SQL server or SQL Analyser still return err/msg when I use
> symbol as serach index (e.g. {/}/?/>/</./,/@./!/$):
> "Server: Msg 7619, Level 16, State 1, Line 1
> Execution of a full-text operation failed. A clause of the query contained
> only ignored words." - error msg from SQL Analyser
> At minimun SQL server should return no recors. Instead it return err/msg,
> that hangs my SQL service when run from Coldfusion and i had to manual
> restart my service. Any ideas ?
|||well, i can code a filter at the coldfusion side. But i have to test all
possible invalid chars for SQL server 2K Full text search.
Is there any easy way out whereby I can configure SQL server 2K to return at
least no records or trap the err/msg return in SQL server as a result of
invalid chars in full text search.
xxx
"Hilary Cotter" wrote:

> These are invalid characters which cannot be searched upon. This should not
> be hanging your SQL Server however. You should be trapping for such
> characters on the client and removing them if the user is trying to search
> on them.
> --
> 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
> "eslim" <eslim@.discussions.microsoft.com> wrote in message
> news:95366FB3-4621-421E-9F5B-DD3118798A5F@.microsoft.com...
>
>

How to enable clients to access the same DB via SQL Express

Hi to all!

My problem comes from this situation: I have two PC connected via a network calble; on the first I have SQL Server 2005 Express, an MDF data file in a shared directory under the "Document and Settings" path, and my program written in VB .NET 2005. On the second PC I have only my program, and I would like to have it connect to and work on the MDF on the first PC.

The program on the first PC works well and uses a connection string like the following:

Data Source=.\SQLEXPRESS;AttachDbFilename=<path>\<name>.mdf;;Integrated Security=True;User Instance=True

I am able to access to SQL Express from the second PC (I have enabled the Client to use the right TCP ports, told the firewall to let them work and enabled the Guest user on the first PC to use SQL Express), but from Visual Studio Server Explorer I cannot reach my data file: any attempt fails telling me that I cannot open it because is in a shared location... I can only open the DBs located in the "Data Directory" of SQL Express...

In other words, what should I do to have two or more clients work on the same DB under SQL Express? Is it mandatory that the DB .MDF file resides in the "C:\Programs\Microsoft SQL Server\MSSQL.1\MSSQL\Data" directory so that it can be permanently attached to SQL Express?

Thanks for your help!!!

Roberto

Permanently attaching the DB would be the easiest way to go. You do not have to have the mdf in SQL server data directory. You can put it on any local drive and attach it from there.

As connection string use Data Source=yourDBServer\SQLEXPRESS; Integrated Security=True

Do you have any concerns attaching the mdf permanently.

--
SvenC

|||Is it possible to put the mdf on a network drive? Using SQL Server Management Studio I can't: it lets me attach only mdf located in SQL Server data directory...|||

It would not be advised to have the file on a network share... I have not tested it, but I would think that the SQL Engine will not be able to connect to it (Using the T-SQL or GUI).

|||

It might work if you change the SQL service account, so that SQL Server runs in an account which has access rights to that share. Local System can only access local resources.

But you add a dependency, that the file server has to be always online otherwise SQL is down.

--
SvenC

|||

http://cyberblox.spaces.live.com/blog/cns!D0847CCE32E9987C!185.entry

How to eliminate the NULL field values

I am importing an Access .mdb file into MS SQL server, and empty fields where the default value is "", change into NULL. This is a problem when I re-export a result set and have to apply a procedure to clean these values. Is there a way to eliminate this? . . . . and what have I missed?Check 'allow nulls' and 'default value' settings in the access tables.|||and to solve for the export problem...

Use a query to export the table, and use
Select ISNULL(yourfield,"") AS FIELD1...sql

How to eliminate login screen when i try to access report via URL

I am trying to access a report via url

http://69.23.3.112/reportserver?/rptProject/rptStatus&eStatus=All&eUser=2

it always asks for username and password.

All my users login to my project which is asp.net 1.1(vs2003) based project, now from inside the project, if they try to access any report from (which is on framework 2.0), they have to go through a autentication screen which is related to sql server reporting services.

can you please help, how to override this login screen.

Thank you very much for the information.

If It is for login to the datasource then you ned to pass datasource credentials. (reportviewer.setdatasourcecredentials())And if it ask you to login to the report server than you need to set the report server to allow access of the report to your website.

and if you want you can set report server credentials.. and give that credentials access to the reports using report manager.

|||

Hi, Thanks.

I gave permissions to report manager folder for aspnet and also iusr_machine name both.

and also checked anonymous login for reports virtual directory under intepub.

still i get the login screen if somone trying to access reports via reporting services reports folder.

i am calling the reports via url from vs 2003 , and my reports are on vs 2005.

And microsoft did'nt release no report viewer with framework 1.1., that is causing the problem.

please help guys.,

Thank you all.

|||

I can give you one option.

You can crete one project with a page (may be 1 default.aspx) containing report viewer and if you want parameter promp controls of your own.

And redirect your users from original website to new website when ever they select menuitem( or whatever you have used) for report.

Now for login screen.

I need to know where it pop us..

when anyone trying to go to report folder or when any one trying to run report.

If it is asking when anyone trying to go to report folder.. I thing you need to pass login for the user in URL( Not sure how)

If it is asking when they run report just do one thing. go to each report.. than properties than datasource and save credentials for datasource.

How to eliminate login screen when i try to access report via URL

I am trying to access a report via url

http://69.23.3.112/reportserver?/rptProject/rptStatus&eStatus=All&eUser=2

it always asks for username and password.

All my users login to my project which is asp.net 1.1(vs2003) based project, now from inside the project, if they try to access any report from (which is on framework 2.0), they have to go through a autentication screen which is related to sql server reporting services.

can you please help, how to override this login screen.

Thank you very much for the information.

If It is for login to the datasource then you ned to pass datasource credentials. (reportviewer.setdatasourcecredentials())And if it ask you to login to the report server than you need to set the report server to allow access of the report to your website.

and if you want you can set report server credentials.. and give that credentials access to the reports using report manager.

|||

Hi, Thanks.

I gave permissions to report manager folder for aspnet and also iusr_machine name both.

and also checked anonymous login for reports virtual directory under intepub.

still i get the login screen if somone trying to access reports via reporting services reports folder.

i am calling the reports via url from vs 2003 , and my reports are on vs 2005.

And microsoft did'nt release no report viewer with framework 1.1., that is causing the problem.

please help guys.,

Thank you all.

|||

I can give you one option.

You can crete one project with a page (may be 1 default.aspx) containing report viewer and if you want parameter promp controls of your own.

And redirect your users from original website to new website when ever they select menuitem( or whatever you have used) for report.

Now for login screen.

I need to know where it pop us..

when anyone trying to go to report folder or when any one trying to run report.

If it is asking when anyone trying to go to report folder.. I thing you need to pass login for the user in URL( Not sure how)

If it is asking when they run report just do one thing. go to each report.. than properties than datasource and save credentials for datasource.

sql

How to edit a SQL Server table from MS Access?

Hi guys,

I need provide some of my users an easy way of accessing a SQL Server table through MS Access so that

they can edit the table.

Please let me know which of the following is more appropriate to accomplish this task.

1. Creating a Data Access Page

2. Creating a Link Table

I thought I could it do it though a Data Access Page but I was only able to see the data but not edit it. But, I

know there is a way of doing this. Please throw some light on this topic.

Thanks a lot for your time.

Regards,

-RL.

Tables must have a key to be editable in access|||

Hi TiJaLeII,

Thanks a lot. Now I could be able to edit the data.

How to edit a SQL Server table from Microsoft ACCESS?

Hi guys,

I need to provide some of my users an easy way of accessing a SQL Server table through MS Access so

that they can edit the data in the table.

Please let me know which of the following is more appropriate to accomplish this task.

1. Creating a Data Access Page (OR)

2. Creating a Link Table

I tried to create a Data Access Page but I could only be able to see but not edit the data in the table. But, I

guess there is a way to do that.

Please throw some light on this topic.

Thanks a lot for your time.

-RL

Hi RL,

If I understood you correctly, you have 2 questions:

(1) Use Data Access Page or a Linked Table? Well, these are 2 separate technologies - one is a web form, the other requires opening a mdb database in Access and accessing data through a normal Access form. It's up to you and your users to select what's easier - having a centralized web page seems simpler, but requires IIS.

(2) Your data is read-only in the Data Access Page? Have you changed the DataEntry property to True? Check all the properties to make sure everything is setup correctly.

HTH,
Jivko Dobrev - MSFT
--
This posting is provided "AS IS" with no warranties, and confers no rights.

How to dynamically assign database name in query or store procedure?

Hello,

I am not sure if this possible, but I have store procedures that access to multiple databases, therefore I currently have to hardcode my database name in the queries. The problem start when I move my store procedures into the production, and the database name in production is different. I have to go through all my store procedures and rename the DBname. I am just wonder if there is way that I could define my database name as a global variable and then use that variable as my DB name instead of hardcode them?

something like

Declare @.MyDatabaseName varchar(30)

set @.MyDatabaseName = "MyDB"

SELECT * from MyDatabaseName.dbo.MyTable

Any suggestion? Please.

Thanks in advance

declare @.cmd nvarchar (2000)

declare @.MyDatabaseName nvarchar(30)

select @.MyDatabaseName = 'northwind'
select @.cmd = 'SELECT * from '+ @.MyDatabaseName +'.dbo.employees'

exec (@.cmd)

|||

It is much easier and cleaner if you simply deploy your stored procedure in each database. Using dynamic SQL has lot of security implications and you don't really simplify your code.

For a future version of SQL Server, we are looking at features that will enable you to parameterize identifiers without using dynamic SQL. And also the ability to deploy SPs in one module & resolve objects in the execution context database.

|||

Thanks Uma and Joeydj

I can't use dynamic query like Joeydj, simply because my stps are huge; but I am curious about Uma's statement about the security implication in Dynamic query. Could you elaborate a little Uma? thanks

|||

For one, you need to grant more permissions to users on your SPs if you use dynamic SQL. So you increasing the attack surface area of your database by using dynamic SQL. Additionally, if you don't protect against malicious inputs then you are vulnerable to SQL injection attacks which can compromise the database, entire server or network. Lastly, there is also the performance and maintainence aspect of dynamic SQL depending on the usage. So there are many risks in using dynamic SQL. However, for some problems in SQL Server there is no way other than using dynamic SQL (like parameterizing DDL statements, running DDLs against multiple dbs etc). But you can do these carefully by protecting your code against SQL injection attacks, using QUOTENAME for values that can be used as identifiers etc.

Below is a good link on the issues of using dynamic SQL in SQL Server. Also, search the WWW for "SQL Injection" and you will find lot of articles.

http://www.sommarskog.se/dynamic_sql.html

|||

Thank you Uma for your explaination and thanks for the link about dynamic sql. It's a great article!

appreciated.

How to do something like: SqlDataSource[nRow][nCol].Value ??

How do you get a record-set or something like it. I do not want to bind to any grids or display the data. I just need access to SQL Server tables.

What is the best way and where can I get some documentation and example code?

Thank's for any help

Then you do not need SqlDataSource. You can use SqlCommand/SqlDataReader/SqlDataAdapter etc. You can find lots of example from MSDN:

http://msdn2.microsoft.com/en-us/library/ms254937.aspx

how to do replication from access 2000 to sql server 2000

My requirement i want to create publisher in access 2000 and subscribe the
data from sql server by using pull replication
Thanks in advance for any reply
Try creating a merge publication in sql server and set up the access
database as a heterogenous subscriber.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)