Access Database in other server

Hi.

I have a stored procedurein my database and I want to access table in otherserver. can I do that in Stored procedure?

kojoh:

I have a stored procedurein my database and I want to access table in otherserver. can I do that in Stored procedure?

as long as you SQL Server that has the stored procedured has the link server configured, it would work.

|||

Thank's for replay , I will give you an example to explane what I want.

CREATE PROCEDURE [dbo].[MySP]ASinsert into memberselect *from member1

the table member in the current server and the table member1 in other server.

Regards.

|||

As long as you have configured the link server to the member1 table then it would work as you need linked servers to run distributed queries.

Here are some articles on linked servers and distributed queries

http://msdn2.microsoft.com/en-us/library/aa213778(SQL.80).aspx

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

http://www.databasejournal.com/features/mssql/article.php/3085211

|||

Thank's alotjimmy

I try to implement the setpes but I can't get it .

CREATE PROCEDURE [dbo].[link]ASexecsp_addlinkedserver @.server ='Srv' , @.srvproduct ='' , @.provider='SQLOLEDB' ,@.datasrc ='Srv'select invcompfrom [Srv].[DB].glcomGO

when I run the script it give me

(1 row(s) affected)

(1 row(s) affected)

Server: Msg 208, Level 16, State 1, Procedure link, Line 4
Invalid object name 'Srv.DB.glcom'.

I use sql server 2000.

Access Database Datatypes, ADOX and VS2005 Question

I'm using ADOX 2.8 for table creation: The following is an example of a column defintion:

If CreateNewTable Then CreateNewTable = a.CreateColumn("ReferenceCount", ADOX.DataTypeEnum.adInteger)

If CreateNewTable Then CreateNewTable = a.CreateColumn("Document", ADOX.DataTypeEnum.adLongVarBinary) 'Oleobject

If CreateNewTable Then CreateNewTable = a.CreateColumn("EntityID", ADOX.DataTypeEnum.adWChar, 18) 'text

Where CreateColumn looks like this:

Public Function CreateColumn(ByVal ColumnName As String, ByVal Datatype As ADOX.DataTypeEnum, Optional ByVal Size As Integer = 0) As Boolean

'ADOX.CreateColumn- Called by Common.CreateNewTable

'CreateColumn creates a column described in the Table object so it assumes it is set.

'One method of setting it is to call Select Table after opening the database

If Not Me.ConnectionIsOpen Then

MsgBox("CreateColumn - Failed to Create Column : " _

& ColumnName, MsgBoxStyle.Exclamation, cNoConn)

Return False

End If

Dim col As New ADOX.Column

col.Name = ColumnName

Try

col.Type = Datatype

Catch e As Exception

MsgBox("CreateColumb - Failed to Create Column : " _

& ColumnName, MsgBoxStyle.Exclamation, e.Message)

col = Nothing

Return False

End Try

If Size <> 0 Then col.DefinedSize = Size

Try

Table.Columns.Append(ColumnName, Datatype)

Catch e As Exception

If Err.Number() <> 0 Then

MsgBox(Err.Source & "-->" & Err.Description, , "Error")

End If

MsgBox("CreateColumb - Failed to Append Column : " _

& ColumnName, MsgBoxStyle.Exclamation, e.Message)

Return False

End Try

col = Nothing

Return True

End Function

in CreateColumn("EntityID", ADOX.DataTypeEnum.adWChar, 18)

the 18 specifies the field width in the database. Yet no matter whether I use adWChar or

adVarWChar, Access always shows the field size to be 255.

Does anyone know why or how to fix that?

I can see that you created col variable, and assigned values to the fields of it. But, unless I'm missing something, I do not see how it is used in Table.Columns.Append call.

I guess that you should either use col instead of ColumnName, or add Size as a third parameter, and do not use col at all.

Access data update problem

I have developed an Access 2002 fornt end for an SQL
database. When an form is run, it opens a limited set of
data that is updatable - the sql table is updated directly
from the access table, query or form. This works fine on
our intranet. When accessed from the internet, however, I
am only able to to update data in the table; when I run
the query or the form, the keyboard beeps when I try to
update any value. I have run the analyzer on the query,
and it shows that the dataupdatable value is false (inside
our intranet, the value shows true), but I cannot figure
out how to change this.
I have simplified the query to only draw information from
one table, but cannot update the data in the query
datasheet view or on a resulting form, only on the
original data table.Access is not designed for or intended to be used as the front end for
an Internet application. I'd look at using ASP.NET or some other more
suitable technology for the Internet.
-- Mary
MCW Technologies
http://www.mcwtech.com
On Fri, 6 Feb 2004 14:40:42 -0800, "Don" <don@.ameritest.net> wrote:

>I have developed an Access 2002 fornt end for an SQL
>database. When an form is run, it opens a limited set of
>data that is updatable - the sql table is updated directly
>from the access table, query or form. This works fine on
>our intranet. When accessed from the internet, however, I
>am only able to to update data in the table; when I run
>the query or the form, the keyboard beeps when I try to
>update any value. I have run the analyzer on the query,
>and it shows that the dataupdatable value is false (inside
>our intranet, the value shows true), but I cannot figure
>out how to change this.
>I have simplified the query to only draw information from
>one table, but cannot update the data in the query
>datasheet view or on a resulting form, only on the
>original data table.|||Hi Don,
Thank you for using the newsgroup and it is my pleasure to help you with
your issue.
From the information you provided, you have an Access 2002 front end and a
SQL Server backend. I assume it is a ADP. You found every thing is fine on
intranet but when you run it in a internet environment, you found you could
only able to update data in the table but cannot update from query or form,
right?
Well, could you please provide some informaiton, which is helpful for our
analysis:
As you mentioned that you cannot change the value from the query and the
form, beside the keyboard beep, is there any message you got? You mentioned
that you could update the the record in table, you could please provide the
information of how you modified the record and could you confirm that the
modification is made on the SQL Server side? As a ADP, could provide the
information of the connection of it? You could get the information by
choose from the menu, 'File'->'Connection', then get the information of
Server Name, the authentication mode and related logins you use and the the
database you connect to? This information is much helpful to us.
I am standing by to you reply and thanks.
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.

Access Data Projects Uniqueidentifier problem

Hi,

I am designing a SQL server backend, MS Access (ADP) frontend
application and I came across an interesting problem.
Lets say I have a table T with the following fields(columns)
[a] (int Not Null, Primary key, Identity auto increment)
[b] (uniqueidentifier, Allow Nulls)
[c] (text)

When I open the table in datasheet view using Access and type a value
in [c], [a] and [b] get populated automatically. [a] getting filled
with incremental integers is understandable. But, I have not set the
ISRowGUID or a default value for [b] and still it gets popullated with
a random GUID. If I use SQL Server enterprise manager to enter data
into the table T, [b] does not get filled and stays Null. I forgot to
mention that there are no relationships between the tables yet.
Is it MS access that is responsible or is it me? In any case can
something be dont to prevent this? Also should there be something else
in relation to this that I have to look for in the future.

My best
Ibrahim MMSSQL itself won't put in a value unless you have a column default or a
trigger on the table, so it seems likely that Access is doing this. You
could use Profiler to see exactly what commands Access sends to the
server when you use the datasheet, but if you want to know why it does
that, then I guess you'll get a better answer in an Access group.

Simon|||Thanks Simon,

The "problem" in fact does lie in MS Access.

I found a fix here
http://www.developersdex.com/sql/me...p=581&r=3822184

Thanks a lot for all the help !
My best
Ibrahim M

Access Crashes Filtering a Linked SQL Table on a date field

I have an MS Access 2002 application that is distributed to a number of PCs around our office. The data for this application is stored on a central SQL Server that is linked in through ODBC.

This application has been in place for two years and working fine. We recently formatted and restored a PC, and now that particular PC has issues with the Access application.

Every time it tries to filter one of the linked SQL tables on a date field, Access goes unresponsive and GPFs out. If it's in a query that is behind a report, I get the old standard 'Catastrophic Failure'. If I open the table and right-click filter or run a query manually, Access GPFs.

I've tried recreating the ODBC, linking the tables through TCP/IP as well as Named Pipes. Nothing fixes it. All Windows and Office updates have been applied. This is not the first time we've reformatted a PC in the office, but we've never had this issue.

Has anyone run across this before?

Thanks!

-BenWhat is the operating system on the new PC?
Is is connecting directly to the network?
What is the SQL Server ODBC driver version #?

The other thing I would do is find the developer that designed the application in Access and smack him in the back of the head for trying to develop a multi-user application in Access.

There are many many other alternatives that would work better and be much much faster.

Access a table

Another question about SQL Server tables and access grants.
I've created an user 'user1' as Access account, ad also as db_owner of a
selected database.
Then, I've created a table as user1.table1.
When I try to access the data of the table, with the account name of user1,
I must specify the owner ('select * from user1.table1') even if I've logged
as user1 (I need to access the data just with 'select * from table1').
What's the problem?

Thank you
Federica"Federica T" <fedina_chicca@.N_O_Spam_libero.it> wrote in message
news:ckgq2d$a46$1@.atlantis.cu.mi.it...
> Another question about SQL Server tables and access grants.
> I've created an user 'user1' as Access account, ad also as db_owner of a
> selected database.
> Then, I've created a table as user1.table1.
> When I try to access the data of the table, with the account name of
> user1,
> I must specify the owner ('select * from user1.table1') even if I've
> logged
> as user1 (I need to access the data just with 'select * from table1').
> What's the problem?
> Thank you
> Federica

It sounds as if it should work as you expect - for users not in the sysadmin
role, MSSQL always looks for an object owned by the current user first, then
dbo (see "Database Object Owner" in Books Online), but for sysadmins it will
always look for dbo first.

Do you get error 208 (invalid object name), or does it select from
dbo.table1 instead? If you get error 208, then you can use USER_NAME() to
check that you are user1, DB_NAME() to check you're in the correct database
(you might be in master by default, for example), and OBJECT_ID() to make
sure the object exists. If it selects from dbo.table1 instead, then user1's
login is probably also in the sysadmin role on the server.

If the problem is still unclear, what do you get when you run the following
as user1?

create table user1.t1 (col varchar(100))
insert into user1.t1 select 'Owned by user1'

create table dbo.t1 (col varchar(100))
insert into dbo.t1 select 'Owned by dbo'

select
'Database: '+ db_name() + ' ' +
'Login: ' + suser_sname() + ' ' +
'User: '+ user_name() + ' ' +
'Object owner: ' + col + ' ' +
'db_owner: ' + case is_member('db_owner') when 1 then 'Y' else 'N' end + '
' +
'sysadmin: ' + case is_srvrolemember('sysadmin') when 1 then 'Y' else 'N'
end
from t1

Simon|||"Simon Hayes" <sql@.hayes.ch
> It sounds as if it should work as you expect - for users not in the
sysadmin
> role, MSSQL always looks for an object owned by the current user first,
then
> dbo (see "Database Object Owner" in Books Online), but for sysadmins it
will
> always look for dbo first.

Thank you for the answer! The problem was caused by the user that was also a
sysadmin!
Fedesql

Access a spaced column name

I have a table column like "First Name". How do I select that from the table.
It gives me an error if I write query:
select "First Name" from table1.
Any clues?
Thanks
VinnieI use brackets, likeSELECT [First Name]
FROM table1-PatP|||Thanks for the info

Access 2007 + SQL Server 2005 + Vista x64

Hello,

I am looking for a solution to the problem named Table was skipped, or export failed. Here's the story:

I have a desktop with Vista Ultimate x64 and Office 2007. I want to export a database from Access MDB (about 2Gb, 110 tables) to SQL Server Express Express (Advanced). The simple solution > upsize wizard.

- create new database

- server name, used trusted connection, some database name
- I select all tables
- I unselect table's attributes

- create new Access server/client application

and every time I have Table was skipped, or export failed. The same thing happens when I create new MDB with one table (some random values)!

The strange thing that on laptop with XP + SQL Sever 2005 Express (Simple) and Access XP upsizing the same database works fine!'

one other issues of a rookie:

I wanted to access a database (randomly created) on Vista with the Access on my laptop. Both PCs are in the same network I can ping both, but when I try to connect to IP_desktop\SQLEXPRESS Access posts connection error. Why?

P.S.

When installing SQL Server on Vista I have only a problem with IIS and ASP.NET, but it shouldn't be a problem.

For the first issue, plase post the complete error message, for the second quesstion, you should enable the SQL Server Browser service and make sure that remote connections are enabled to the SQL Server machines (for more information see the screencast on my site)

Jens K. Suessmeyer

http://www.sqlserver2005.de

Access 2003 with SQL Express 2005

I am a fresher in using SQL with access.

I am using a code in Ms access to link a table that is in Sql express 2005.

I use this linked table name to download records to the local Access table.

The link is then dropped in Access.

Is there any way to write back records into the linked Sql table from Access.

It does not allow me to delete / add / modify records of a linked table.

How can I do this.

Thanks

Sjohn

YOU will need to specify the primary key on the linked server table that exists in SQL Server. This information is automatically created by default.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de
sql

Access 2003 Project Connecting to SQL 2000 & 2005 Servers

We have an Access 2003 Project that it's primary server is a SQL 2000 server. When I link a table to the 2000 server and execute the view, I get data with no problem. I link a table to the 2005 server and execute the view...once again, no problem. However, if I try to write a query to pull data from both views......times out. I have changed the timeout value to "0" and never returns the data.

So then I created a new Access 2003 Project to use the 2005 as the primary server. Once again I create a linked table and execute the view on the SQL 2000 server and it works just fine. Then create a linked table to the 2005 server and the view works just fine. However, here's the difference. When I try to create a query to pull data from both views, I get:

1. This version of Microsoft Office Access doesn’t support design changes with the version of Microsoft SQL Server your Access project is connected to. See the Microsoft Office Update Web site for the latest information and downloads. Your design changes will not be saved.

2. You have connected to a version of SQL Server later than SQL Server 2000. The version of Visual Studio or Access that you are using was released before the version of SQL Server to which you are connected. For this reason, you might encounter problems. Please check with Microsoft to see if there is a service pack that you should apply to Visual Studio or Office in order to get support for the version of SQL Server to which you are connected. You can continue but any new object types might not be enumerated, and it will not be possible to save any objects or database diagrams that you create using the Visual Database Tools.

Any assistance would be GREATLY appreciated. I'm stuck and wish I could make this happen.

PS:....

The version of Access 2003 specifically is: Microsoft ? Office Access 2003 (11.6566.6568) SP2

I've even made two linked tables to the 2005 server and executed each view by themselves and it worked fine. However, if I try to create a query off of those linked tables, same errors just posted above.

I'm afraid that Access ADPs aren't going to work all that smoothly with SQL Server 2005--they are created for SQL Server 2000 and earlier. If you're creating a new app, it's recommended that you use an mdb with linked tables because it gives you more flexibility. ADPs were designed around the scenario that you would connect to *one* SQL Server database, not multiple databases. One option if you must use ADPs is to create views to access data in another database. You can then use those views in your Access FE.

--Mary

sql

Access 2000, SQL Server 2000, Bulk Insert

I am pulling information from a DB2 table through a pass-through query
in Access 2000. I am going to create a comma delimited file that I want
to use Bulk Insert to put into SQL Server 2000.
I have the query to pull the data, all is well. How do I program in VBA
(Acess 2000) to do a bulk insert into a SQL Server table. I have 100000
records that I do this to every day. Bulk Insert seems to be the fastest
way. I will bulk insert the comma delimited file.
I am on a PC, not the SQL Server for the bulk insert. I cannot access
the mainframe DB2 tables from the SQL Server. My PC is the middle man
right now. How do I effectively do the bulk insert.
Does anybody have any experience in doing this, if so, can you post some
code to assist.
Thanks, Lyners
--
Posted via http://dbforums.comOne more thing, I have the code written so that it pulls the data and
loads the records 1 by 1. It takes to long and I keep blowing up the
transaction file. That is why I want to do the bulk insert. 1 record for
the transaction file. (AT least that is what I have read).
Posted via http://dbforums.com

Access / SQL Server currency datatypes

When linking a SQL server 2000 table to Access 2003 using the standard MS
ODBC driver (2000.85.1117.00) fields with a datatype money and smallmoney
are blank in Access.
If I use a DataDirect ODBC driver the money fields are displayed correctly.
Any Idea if there is a MS ODBC driver that displays money correctly?
You might want to ask this question in one of the Access newsgroups
since it isn't an ODBC or a SQL Server issue. Someone there may have
the answer you seek.
--Mary
On Tue, 14 Mar 2006 06:02:26 -0600, "Simon
Lampert"<ureader@.byteaccess.com> wrote:

>When linking a SQL server 2000 table to Access 2003 using the standard MS
>ODBC driver (2000.85.1117.00) fields with a datatype money and smallmoney
>are blank in Access.
>If I use a DataDirect ODBC driver the money fields are displayed correctly.
>Any Idea if there is a MS ODBC driver that displays money correctly?

Access / SQL Server currency datatypes

When linking a SQL server 2000 table to Access 2003 using the standard MS
ODBC driver (2000.85.1117.00) fields with a datatype money and smallmoney
are blank in Access.
If I use a DataDirect ODBC driver the money fields are displayed correctly.
Any Idea if there is a MS ODBC driver that displays money correctly?You might want to ask this question in one of the Access newsgroups
since it isn't an ODBC or a SQL Server issue. Someone there may have
the answer you seek.
--Mary
On Tue, 14 Mar 2006 06:02:26 -0600, "Simon
Lampert"<ureader@.byteaccess.com> wrote:

>When linking a SQL server 2000 table to Access 2003 using the standard MS
>ODBC driver (2000.85.1117.00) fields with a datatype money and smallmoney
>are blank in Access.
>If I use a DataDirect ODBC driver the money fields are displayed correctly.
>Any Idea if there is a MS ODBC driver that displays money correctly?

Access .adp :How to INSERT all but KEY violations

I am trying to append records from one table to another in a db running on
MSDE, knowing fullwell that some of the data in the source will be
duplicates of that in the destination table's pk.
What I would like to happen is to have the stored procedure plunk in all
records that don't violate the constraint
and silently let the duplicate info fall by the wayside. The trouble is SQL
server seems to abort the whole procedure if
even a single record violates the constraint.

In a regular Access mdb, an INSERT statement (append query) would do just
that. Of course it warns you of the violation but a DoCmd.SetWarnings FALSE
takes care of that.

Any ideas as to what I need to do to achieve that same thing?For example:

INSERT INTO TargetTable (key_col, col1, col2, ...)
SELECT S.key_col, S.col1, S.col2, ...
FROM SourceTable AS S
LEFT JOIN TargetTable AS T
ON S.key_col = T.key_col
WHERE T.key_col IS NULL

(where key_col is the primary key).

--
David Portas
SQL Server MVP
--

Access

I have an Access file with many tables and each table has got many constraints.I want to remove each and every constraints(including primary key,foreign relationship) for every table.How can I do it first?Delete the file

Accesing package Variable in Conditional Component

Hello Experts,

I am trying to load data to a sql table from flat file..I have a script component which will validate the data. I am also firing the errros when data validation fails. Once I encounter an error I want the whole package to be aborted. What I have done is declared a variable called errorcount (Scope :Package) and I am incrmenting this variable everytime an error is raised and next I have a Conditional Split which will check if this variable count is greater than 0 if yes I will not update the table.This is the only way I think this will work.

error I am getting is :The collection of variables locked for read and write access is not available outside of PostExecute.

I understand they r in the same data flow thats the reason I get this error but how can I by pass it ?

MY dataflow-> Flat File Source-Script Comp-Condtional Split-(If errorCount==0)-SQL Table

How can I redo the dataflow so that this err is not raised.

I have tried to add an output column (blnContinue) and use that in conditional stmt but as this is stored at row level values which have blnContinue as true are getting processed, I dont want this I just want to stop the package execution even if a single validation fails

Pardon my long question

Appreciate your help in advance

You idea will not work for two reasons. 1 the limitation of accessing variables, which is really about performance. 2 if your error is the last row, and you have multiple buffers, the first buffer could have already moved downstream and been written to SQL, as at that point the split would have still been passing.

A better way may be to just throw and exception in the component when you encounter an error. Whilst you still have an issue with data moving downstream before all rows have been checked, but you could workaround this by using a transaction or a commit size of 0 to ensure all or nothing at the SQL end. You could do a similar method by using basic SQL transactions, avoiding DTC, and that way you get to issue the BEGIN TRAN and COMMIT TRAN, or of course when the variable says you have bad data, the ROLLBACK. SSIS Nugget: RetainSameConnection property of the OLE DB Connection Manager - (http://blogs.conchango.com/jamiethomson/archive/2005/08/20/2048.aspx)

Another method would be to stage the data between the validation/cleansing and load. Then check the variable and decide if you want to go on a load SQL afterwards. Raw files are the cheapest phstical persistance, they have very little overhead, just the IO cost really.

|||

Appreciate your response

How do I specify Commit size of 0 to a SQL Destination?

|||For the SQL Server Destination it is called MaxInsertCommitSize, look in the properties grid, I could not see it in the component editor UI.|||

Hmmm...yeah but what if all the files get validated right and there will be some existing records which need to be updates and the otehrs need to be inserted...

If no errors found this is how process should end if found completly abort the process...

Struck ova here....arghhhhh

|||Now you're changing the requirements, but still the options can work. So options now available as I see it are the staging idea, validate everything first before you load into SQL in a second Data Flow task. The other option is to use a transaction. Can be quite expensive, but ultimately that is what they are there for, either a package transaction or the retain same connection method as linked above.

Accesing package Variable in Conditional Component

Hello Experts,

I am trying to load data to a sql table from flat file..I have a script component which will validate the data. I am also firing the errros when data validation fails. Once I encounter an error I want the whole package to be aborted. What I have done is declared a variable called errorcount (Scope :Package) and I am incrmenting this variable everytime an error is raised and next I have a Conditional Split which will check if this variable count is greater than 0 if yes I will not update the table.This is the only way I think this will work.

error I am getting is :The collection of variables locked for read and write access is not available outside of PostExecute.

I understand they r in the same data flow thats the reason I get this error but how can I by pass it ?

MY dataflow-> Flat File Source-Script Comp-Condtional Split-(If errorCount==0)-SQL Table

How can I redo the dataflow so that this err is not raised.

I have tried to add an output column (blnContinue) and use that in conditional stmt but as this is stored at row level values which have blnContinue as true are getting processed, I dont want this I just want to stop the package execution even if a single validation fails

Pardon my long question

Appreciate your help in advance

You idea will not work for two reasons. 1 the limitation of accessing variables, which is really about performance. 2 if your error is the last row, and you have multiple buffers, the first buffer could have already moved downstream and been written to SQL, as at that point the split would have still been passing.

A better way may be to just throw and exception in the component when you encounter an error. Whilst you still have an issue with data moving downstream before all rows have been checked, but you could workaround this by using a transaction or a commit size of 0 to ensure all or nothing at the SQL end. You could do a similar method by using basic SQL transactions, avoiding DTC, and that way you get to issue the BEGIN TRAN and COMMIT TRAN, or of course when the variable says you have bad data, the ROLLBACK. SSIS Nugget: RetainSameConnection property of the OLE DB Connection Manager - (http://blogs.conchango.com/jamiethomson/archive/2005/08/20/2048.aspx)

Another method would be to stage the data between the validation/cleansing and load. Then check the variable and decide if you want to go on a load SQL afterwards. Raw files are the cheapest phstical persistance, they have very little overhead, just the IO cost really.

|||

Appreciate your response

How do I specify Commit size of 0 to a SQL Destination?

|||For the SQL Server Destination it is called MaxInsertCommitSize, look in the properties grid, I could not see it in the component editor UI.|||

Hmmm...yeah but what if all the files get validated right and there will be some existing records which need to be updates and the otehrs need to be inserted...

If no errors found this is how process should end if found completly abort the process...

Struck ova here....arghhhhh

|||Now you're changing the requirements, but still the options can work. So options now available as I see it are the staging idea, validate everything first before you load into SQL in a second Data Flow task. The other option is to use a transaction. Can be quite expensive, but ultimately that is what they are there for, either a package transaction or the retain same connection method as linked above.

acces multiple servers

Hi,

I need to access a table from another server in my procedure...I am now connected to say SERVERA...I need to access another table SERVERB.TABLE2 in the same procedure...

Is there a way to do that...

I need to connect to two servers from the same procedure to get data..

Please get back

Thanks,

Hi Swapna,

Yes, there are a few methods, however the most straightforward would be to create a linked server.

Have a look at BOL or http://www.databasejournal.com/features/mssql/article.php/3085211 for more info.

Cheers,

Rob

|||Thanks a lot Rob...It was very helpful

accepting multiple values in data-driven subscription

I have set up a data-driven subscription based off an SQL table. It works in
all cases except when I am trying to select all or multiple values for a
parameter. Does anyone know how this needs to be set up in the SQL table so
that the report will accept multiple values?
ThanksHi Marie,
I encountered the same problem. Did you get any solution yet from other
forums?
Thanks,
Rares
"Marie M." wrote:
> I have set up a data-driven subscription based off an SQL table. It works in
> all cases except when I am trying to select all or multiple values for a
> parameter. Does anyone know how this needs to be set up in the SQL table so
> that the report will accept multiple values?
> Thanks

accents in full-text searching

If I have accented characters in my table which is full-text indexed then I
cannot search for the corresponding non-accented character. e.g. I have the
character "é" in my column (which is nvarchar), so searching for "é" comes up
with results because it is actually entered as "é" in the database, but
searching for "e" does not come up with any results. I have tried changing
the collation on this column to SQL Collation "SQL_Latin1_General_CP1_CI_AI"
and then I tried Windows Collation "Latin1_General_CI_AI". My database
collation name is "Latin1_General_CI_AI". I've set the default full-text
language to 0 (neutral).
Does anyone know how to solve the problem of accent insensitivity for
full-text searching?
my select @.@.version is:
Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation Personal Edition on Windows NT
5.1 (Build 2600: Service Pack 2)
Thanks in advance
Enoch
This is handled correctly in SQL 2005. However in the meantime you should
trap for these accented versions of the words and expand your search for
both the accented and unaccented versions of the work.
So a search on bebe would be expanded to "bb" or "bebe".
There will be some correct stemming when you do a FreeText query is you are
using the French word breaker. For instance if you have removed noise words
for all verb forms of the verb to be (etre) from the French noise word list,
you will get search results to the accented versions of this word when doing
a FreeText search.
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"Enoch" <Enoch@.discussions.microsoft.com> wrote in message
news:E10365FE-F791-4973-84F1-DC4C9596B1AD@.microsoft.com...
> If I have accented characters in my table which is full-text indexed then
> I
> cannot search for the corresponding non-accented character. e.g. I have
> the
> character "" in my column (which is nvarchar), so searching for "" comes
> up
> with results because it is actually entered as "" in the database, but
> searching for "e" does not come up with any results. I have tried
> changing
> the collation on this column to SQL Collation
> "SQL_Latin1_General_CP1_CI_AI"
> and then I tried Windows Collation "Latin1_General_CI_AI". My database
> collation name is "Latin1_General_CI_AI". I've set the default full-text
> language to 0 (neutral).
> Does anyone know how to solve the problem of accent insensitivity for
> full-text searching?
> my select @.@.version is:
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05
> Copyright (c) 1988-2003 Microsoft Corporation Personal Edition on Windows
> NT
> 5.1 (Build 2600: Service Pack 2)
> Thanks in advance
> Enoch
>