Adding a leading character to a string

I'm looking for a string function (or any other quick way) for adding a leading zero to make a string two characters long. For example, if the input is '12' the result will be '12' but if the input is '1', the result will be '01'.

It's actually about making a month number two characters long but I've understood there is no way to make Datediff() to fix it for me.

I thought there might be a string function for this, like there is in many programming languages, but I can't find anything in BOL. I want to keep it simple, because it will be used in a Select and a Group By for aggregating data based on time intervals (year + month).

And, I assume I can't make Datepart() to return year + month directly, only one of the parts at a time.SELECT Convert(CHAR(7), GetDate(), 121)
Use the date expresion of your choice instead of GetDate()

-PatP|||I use this ;)

create function dbo.FN_INT_TOSTRING(@.codigo int, @.length int) returns varchar(10)
begin
declare @.res varchar(10)
set @.res = cast(@.codigo as varchar)
while len(@.res) < @.length
begin
set @.res = '0' + @.res
end
return @.res
end|||What about a generic function like:

Right('0' + cast(@.MyInt as varchar(2)), 2)

But Pat really has the best answer here.

Regards,

hmscott|||Thanks everyone. Simpler than I first thought! I'll play with it at work on Monday morning (9 PM over here now).

PS. Sorry for replying from another alias - I now realize I'm logged on as Coolberg from my work and as Nabucco from my home computer. I'll correct that. ;-)
DS.|||So finding an answer at 21:00 on Friday night doesn't inspire you to run right down to the office to try it ? Well, what kind of geek are you anyway ? Next thing you know, you'll be telling us that you're going to enjoy a cold beer and a warm bed!

-PatP|||select convert(varchar(2),getdate(),101)|||> Next thing you know, you'll be telling us that you're going to enjoy a cold beer and a warm bed!

I missed the beer; the beer shop here closes at 6 PM ;-)|||select convert(varchar(2),getdate(),101)

I'll test this one tomorrow.

By the way, SELECT Convert(CHAR(7), GetDate(), 121) didn't work.

I tried Right('0' + cast(@.MyInt as varchar(2)), 2) today, it worked fine.|||waddyamean it didn't work...of course it worked...read the hint sticky at the top of the forum|||The problem is, I'm doing a (for example)

select convert(varchar(2),datepart(mm,'2006-01-30'),101)

where you'll still get "1" since datepart() returns "1" , not "01".|||select right(convert(char(7),yourdate,120),2)|||And, I assume I can't make Datepart() to return year + month directly, only one of the parts at a time.select convert(char(7),yourdate,120)|||Select convert(varchar(2),convert(datetime,'2006-01-30'),101)|||Thanks everybody!

Add-in for SQL Server Mnagement Studio 2005

Hi,
I notice that a few third party SQL Server tool developers (RedGate
for example) have started to sell utilities that appear as add-ins
to
SQL Server Management Studio 2005 (with additional menu's and
dialogs).
I cant find any documentation on how you do this. Is it an
unsupported hack ? If so, can anyone point me in the right direction
on how I would work out how to do this.
(Sorry for the cross post, I posted the same message in
microsoft.public.sqlserver.server by mistake).
Thanks in advance,
Matt
Asked and answered in .server. Please refrain from multiposting.

Adam Machanic
SQL Server MVP - http://sqlblog.com
Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220
<matt_randle@.yahoo.com> wrote in message
news:1183902073.740953.145580@.22g2000hsm.googlegro ups.com...
> Hi,
> I notice that a few third party SQL Server tool developers (RedGate
> for example) have started to sell utilities that appear as add-ins
> to
> SQL Server Management Studio 2005 (with additional menu's and
> dialogs).
>
> I cant find any documentation on how you do this. Is it an
> unsupported hack ? If so, can anyone point me in the right direction
> on how I would work out how to do this.
> (Sorry for the cross post, I posted the same message in
> microsoft.public.sqlserver.server by mistake).
> Thanks in advance,
>
> Matt
>
|||Adam Machanic (amachanic@.IHATESPAMgmail.com) writes:
> Asked and answered in .server. Please refrain from multiposting.
Hey, he did after all say:
[vbcol=seagreen]
And this groups is certainly the appropriate one for the topic.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||Ok. Thanks for your reply (in the other group). If I can work out
how to do it, ill put up a blog post explaining it. I notice that it
will be a supported feature in the next release of SSMS though so may
just wait for that.
Thanks.
On 8 Jul, 16:09, "Adam Machanic" <amacha...@.IHATESPAMgmail.com> wrote:
> Asked and answered in .server. Please refrain from multiposting.
> --
> Adam Machanic
> SQL Server MVP -http://sqlblog.com
> Author, "Expert SQL Server 2005 Development"http://www.apress.com/book/bookDisplay.html?bID=10220
> <matt_ran...@.yahoo.com> wrote in message
> news:1183902073.740953.145580@.22g2000hsm.googlegro ups.com...
>

add words to FTS dictionary

we are medical company I need to a some words to the dictionary that SQL
containstable uses how can I do it? These are not noise words example the 2
letters SP is a word we use over and over also HiCNR
--
cindyYou don't add words to the SQL FTS "dictionary". You might want to replace
your noise word list with an empty space and then rebuild your catalog. SQL
FTS builds its own "dictionary" based on your content.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"cindy" <cmello@.nospam.nospam> wrote in message
news:20D3663B-C12B-4282-9A9A-BFCFD5B6ABC3@.microsoft.com...
> we are medical company I need to a some words to the dictionary that SQL
> containstable uses how can I do it? These are not noise words example the
> 2
> letters SP is a word we use over and over also HiCNR
> --
> cindy|||for example SP or HiCNR are words in this industry. We have customized sql
to do a boolean search with containstable in stored procedure. A search for
"SP" and "HiCNR" returns correctly matching words in document content. Thes
e
documents are actually rows in a table where Sharepoint stores the content o
f
its document libraries. So will "Magnet" and "heat" for example return as
expected. The problem is with the use of AND NOT. For example "Magnet" and
not "heat" will not bring back content with magnet and without heat whereas
"Magnet" and not "SP" seems to have no effect in eliminating the "SP" word.
I am thinking if I could use a "custom" dictionary have SQL see "SP" as a
word. But then maybe I am not understand the technology. When FTS does a
return is it doing like a split string or REGEX word matching, just saying o
k
here is a word as defined by the word breaker ie there is a space on either
side of "SP" and it is not in the noise word list?
--
cindy
"Hilary Cotter" wrote:

> You don't add words to the SQL FTS "dictionary". You might want to replace
> your noise word list with an empty space and then rebuild your catalog. SQ
L
> FTS builds its own "dictionary" based on your content.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> 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
>
> "cindy" <cmello@.nospam.nospam> wrote in message
> news:20D3663B-C12B-4282-9A9A-BFCFD5B6ABC3@.microsoft.com...
>
>|||Hello Cindy,
Based on my scope, SP shall be the same as other word and there is no
internal dictionary and it is built from word breaker.
I was not able to reproduce the issue. The following is the statements
===================================
create table documentTable(ftkey int not null, document ntext)
create unique index DTftkey_idx on documentTable(ftKey)
exec sp_fulltext_column 'documentTable', 'document', 'add'
exec sp_fulltext_table 'documentTable', 'start_change_tracking'
exec sp_fulltext_table 'documentTable', 'start_background_updateindex'
exec sp_fulltext_catalog 'myfulltext','start_full'
insert documenttable values (10, 'words letters SP Magnet')
insert documenttable values (11, 'HiCNR Magnet')
SELECT * FROM documenttable WHERE CONTAINS(document, 'Magnet AND NOT SP')
SELECT * FROM documenttable WHERE CONTAINS(document, 'Magnet')
====================================
I got only 1 record for the first select query.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.

Add sum column to query

What's the best way to include an amount sum in a query if I also need the individual amounts? For example, I need the following columns:

order number

order amount

total amount

I tried using "with cube ", but the total number of columns in the query exceeds the allowable limit of 10.

select a.ordernum, a.orderamt, b.totalamt
from orders a, (select ordernum, sum(orderamt) from orders group by ordernum) b
where a.ordernum = b.ordernum

This will return data like:

ORDERNUM ORDERAMT TOTALAMT
1234 12.33 56.23
1234 11.25 56.23
1234 32.65 56.23
2345 ...ETC...|||

Hello,

Can you list the schema and an example of required output briefly as I'm not 100% sure what you want to do: do you want to select an order amount and sum what amount?

Sounds as if you should be using a subquery/join.

Cheers,

Rob

|||

to complete Phil's answer, I think this should be a little more efficient to do this

SELECT a.ordernum, a.orderamt, b.totalamt, c.totalamt...
FROM orders a
inner join (select ordernum, sum(orderamt) from orders group by ordernum) b on a.ordernum= b.ordernum
inner join (select ordernum, sum(orderamt) from orders group by ordernum) c on a.ordernum= c.ordernum

|||So you've just added a duplicate total column. Why?|||

Could it look something like this?

create table #x( ordernum int not null, amt int not null )
insert #x
select 1, 20 union all
select 1, 15 union all
select 2, 35 union all
select 2, 10 union all
select 2, 70
go

select ordernum, amt, sum(amt) as total
from #x
group by ordernum, amt
with rollup
go

ordernum amt total
-- -- --
1 15 15
1 20 20
1 NULL 35
2 10 10
2 35 35
2 70 70
2 NULL 115
NULL NULL 150

(8 row(s) affected)

/Kenneth

|||I tried using With Cube and With rollup, but they have a limit of 10 on the number of columns returned. The actual report I'm writing has over 20.|||So what are you trying to achieve?

Give us an example output of what you'd like the data to look like.|||I think your first post will solve the problem. Haven't had time to try it yet.|||

sorry, i made a mistake...

SELECT a.ordernum, a.orderamt, b.totalamt
FROM orders a
inner join (select sum(orderamt) from orders) b on a.ordernum= b.ordernum

I thought inner join could be more efficient than using where statement...

|||

stephane - Montpellier wrote:

sorry, i made a mistake...

SELECT a.ordernum, a.orderamt, b.totalamt
FROM orders a
inner join (select sum(orderamt) from orders) b on a.ordernum= b.ordernum

I thought inner join could be more efficient than using where statement...

Your statement won't work there either. You didn't include ordernum in your subquery.

Never-the-less, inner join and the join method I used are identical.

add sql logins from multiple domains

Hi, SQL gurus.
How is it possible to add to sql logins users from more that just one
domain? For example I have my pc connected to domain
department0.mydomain.net. Our worldwide company has another few departments
like department1.mydomain.net and department2.mydomain.net. How can I put
some guy from department2.mydomain.net to my sql server attached to
department0.mydomain.net domain? And is it the same if my machine will be
attached to mydomain.net? I know that it is more about windows
administration, then just about MS SQL Server, but I hope you will point me
to the right way.
PS: We're using Active Directory.
PPS: If you need more details, I'm ready to explain whatever you want.
--
---
There are only 10 types of people in this world: those who understand binary
and those who don't.If the domain in which your SQL Server exists and the other domains are
'trusted', then you could simply add those other domain's logins using
sp_grantlogin.
You could also create a windows group into which you add all the logins you
wanted, and grant access to this windows group.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
What hardware is your SQL Server running on?
http://vyaskn.tripod.com/poll.htm
"German" <german.koninin@.crm-worldwideDOTnet> wrote in message
news:eGYi2lIaDHA.2464@.TK2MSFTNGP09.phx.gbl...
Hi, SQL gurus.
How is it possible to add to sql logins users from more that just one
domain? For example I have my pc connected to domain
department0.mydomain.net. Our worldwide company has another few departments
like department1.mydomain.net and department2.mydomain.net. How can I put
some guy from department2.mydomain.net to my sql server attached to
department0.mydomain.net domain? And is it the same if my machine will be
attached to mydomain.net? I know that it is more about windows
administration, then just about MS SQL Server, but I hope you will point me
to the right way.
PS: We're using Active Directory.
PPS: If you need more details, I'm ready to explain whatever you want.
--
---
There are only 10 types of people in this world: those who understand binary
and those who don't.

Add search variables to report header

Hi,
I'd like to make the Search variables in my reports part of the Header, so
for example if I'm searching for dates and have @.StartDate and @.EndDate in
the SQL statement, I'd like to add something like 'Report Represents Data
From 1993-01-01 To 1995-01-01' or something like that. How can I do this?
Thanks -
AlexAlex:
Place a textbox either in the Page Header or he body of your report.
Set the value of the textbox equal to:
="Report Represents Data From: " & Parameters!StartDate.Value & " to " &
Parameters!EndDate.Value
Michael C
"Alex" wrote:
> Hi,
> I'd like to make the Search variables in my reports part of the Header, so
> for example if I'm searching for dates and have @.StartDate and @.EndDate in
> the SQL statement, I'd like to add something like 'Report Represents Data
> From 1993-01-01 To 1995-01-01' or something like that. How can I do this?
> Thanks -
> Alex
>
>

Add new column in the matrix

Hi!

Is it possible add a column in the end of a matrix, for example:

id

Last Name

Salary

Notes

employee

Can I add a column "Notes" in the a matrix?

Thanks in advance!!!

the only way to have setup you've suggested in a matrix is if there is a static group on the columns. With the static group in place, just right click the group and select add column, then just leave the field empty.

Add dynamically series in Dundas chart

Hi!
Does anybody know how to add dynamically at run-time a new series in
Dundas chart and bind it to the data in dataset?
For example, I have the following dataset:
LineType PoinNo PointX PointY
1 1 10 20
1 2 15 40
...
1 20 100 30
2 1 5 15
...
2 40 30 20
and so on.
I want Dundas to draw me one line (Series) per LineType value. Of
couse, I can filter out points by LineType for each Series (specifying
its values), but at design time I dont know how many LineTypes there
will bee in the dataset.
One solution is to define a lot of series at design type and show/hide
them at run-time, but it is nogood enough.
Does anybody have any idea?
P.S. Of course it is Dundas chart .NET for Reporting Services version
Thank you in advance!Drop the value you wish to have as the Y Value in the Data Values drop
zone (top). For the series groupings, drop the LineType field in the
Series Grouping field (right) -- this will create a new series defined
by the Y Value field for each new LineType.
Open the chart Values editor associated with the YValue to change the
chart type from the default Column Type to a Line or another series
type. You may also define the XValue for the data or drag the XValue
field in the Category Grouping landing zone (bottom).
If you have further questions, please send an email to
support@.dundas.com.
Thanks
Andrew Bryan
Dundas Software
vladsm@.rambler.ru wrote:
> Hi!
> Does anybody know how to add dynamically at run-time a new series in
> Dundas chart and bind it to the data in dataset?
> For example, I have the following dataset:
> LineType PoinNo PointX PointY
> 1 1 10 20
> 1 2 15 40
> ...
> 1 20 100 30
> 2 1 5 15
> ...
> 2 40 30 20
> and so on.
> I want Dundas to draw me one line (Series) per LineType value. Of
> couse, I can filter out points by LineType for each Series (specifying
> its values), but at design time I dont know how many LineTypes there
> will bee in the dataset.
> One solution is to define a lot of series at design type and show/hide
> them at run-time, but it is nogood enough.
> Does anybody have any idea?
> P.S. Of course it is Dundas chart .NET for Reporting Services version
> Thank you in advance!

add Date and Time In Non english format(Farsi or arabic)

Please Help me
I want Store date and time in sql server tables but in non english format.
For example Arabic or farsi
How can i Do?
Help meeeeeeeee
ThanksI am not sure which format you want - but there are 2 Kuwaiti formats:

Style Format
130 dd mon yyyy hh:mi:ss:mmmAM
131 dd/mon/yy hh:mi:ss:mmmAM

You can alter your datetime data using the convert function. Look at convert in bol.|||Date and time are not stored in any "format" in the database. They are stored as numeric values indicating the days elapsed since a fixed point in time. What you are looking for is a way to display the datetime value in a particular format. You can use the convert function given by rnealejr when outputing your results, but generally issues of data-formatting and display should be handled by your user interface or reporting tool.

blindman

add column in the middle column of the table

Dear All

I need add column in the middle column of the table.
For example,
original have columns a1, b2, c3
now, I wanna add column d4 between b2 and c3

Please give me some suggestions.

My platform have two. One is windows 98 plus access 97. Another one is

windows 2000 server plus SQL Server 2000 (service pack 3)

Alex

Alex

If you go into enterprise manager, select the table right click then design table.

Select the column where you need to insert the new column, right click > insert column. Type the column name, column type and length and if nulls are allowed. Then save

Regard

Richard

|||There was a post on this a few weeks ago. Note that you can only do this from Enterprise manager, not in T-SQL. The only way to do this in T-SQl would be to create a temp table copy of the table with the new column, copy over the data, drop the table, then rename the temp table to the old table name.|||

2alex121: never use * in you queries, specify field list in the insert statements and you will never bothered by such sort of problems.

|||...gone should be the days that we had to take care of the ordinal positions in the entitites.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

Add blank rows to table

Hello,
I need to create a report with a fixed number of table rows. For example:
the table is fixed at 20 rows per page regardless of data retrieved. If say
10 rows are filled with data then the rest are blanks. If 25 rows of data is
retrieved then the first page is filled and next page has 5 rows filled with
the rest blank. I also need a running row number in the first column. I've
managed that with "RowNumber(Nothing)" but I am not sure if it will work with
blank rows too. Is there any way to accomplish this? Through report
designer or custom code?
Thanks,
RodneyI would add rows to the source dataset. If the data is coming from a stored
procedure, append a dummy column to the end of the dataset and put a 1 in it
if the row contained data and a 2 if it did not (this way you have something
to sort on so that the blank rows are at the end of the report. Use a
count() in the stored procedure to determine how many rows to append to the
end of the dataset.
"Rodney" wrote:
> Hello,
> I need to create a report with a fixed number of table rows. For example:
> the table is fixed at 20 rows per page regardless of data retrieved. If say
> 10 rows are filled with data then the rest are blanks. If 25 rows of data is
> retrieved then the first page is filled and next page has 5 rows filled with
> the rest blank. I also need a running row number in the first column. I've
> managed that with "RowNumber(Nothing)" but I am not sure if it will work with
> blank rows too. Is there any way to accomplish this? Through report
> designer or custom code?
> Thanks,
> Rodney|||How would I append blank rows to a select query? Are you suggesting a
temporary table where the data and blank rows are dumped into? Would this be
a problem in a multiuser app?
"Kebler" wrote:
> I would add rows to the source dataset. If the data is coming from a stored
> procedure, append a dummy column to the end of the dataset and put a 1 in it
> if the row contained data and a 2 if it did not (this way you have something
> to sort on so that the blank rows are at the end of the report. Use a
> count() in the stored procedure to determine how many rows to append to the
> end of the dataset.
> "Rodney" wrote:
> > Hello,
> >
> > I need to create a report with a fixed number of table rows. For example:
> > the table is fixed at 20 rows per page regardless of data retrieved. If say
> > 10 rows are filled with data then the rest are blanks. If 25 rows of data is
> > retrieved then the first page is filled and next page has 5 rows filled with
> > the rest blank. I also need a running row number in the first column. I've
> > managed that with "RowNumber(Nothing)" but I am not sure if it will work with
> > blank rows too. Is there any way to accomplish this? Through report
> > designer or custom code?
> >
> > Thanks,
> >
> > Rodney

Add an alert/warning via report Builder

Hi !
Is it possible to add an alert/warning via reportBuilder ?
An alert is for example a value which is superior of a nominal value for example...
I saw in a demo, it is possible to have it by creating a function, but this way is for the developper, if the user wants to add an alert himself ? how can he do ?
and maybe how can the developper help him ?

thank you for your answerthank you for your answer.

Excuse me, I think I didn't good explain my need.
I want to display a grid with duration values in the last colum (for example), if one of my duration is superior of 1 second, I want the cell (where the value is ) become red (background color).
I saw in a demo, (webCast in french) it is possible to add a function in the code tab or the report to select the color of the cell is the value is superior, but I want to know if somethings similaire exist for a simple user via report Builder ?
I think no, but I want to be sure....

|||Don′t think that this is possible, never saw such an option.

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

Alineo wrote:

thank you for your answer.

Excuse me, I think I didn't good explain my need.
I want to display a grid with duration values in the last colum (for example), if one of my duration is superior of 1 second, I want the cell (where the value is ) become red (background color).
I saw in a demo, (webCast in french) it is possible to add a function in the code tab or the report to select the color of the cell is the value is superior, but I want to know if somethings similaire exist for a simple user via report Builder ?
I think no, but I want to be sure....

Hi I never did the duration before, but if you want to change the color of the cell, you can use an IIF statement in the background color property.

=IIF("Condition", "Red", "White")

Hope this helps

Bernard

|||Thank you for your help, I know what you explain but I ask to know, if it possible for a simple user via report Builder and not report designer...
But apparement it is not possible
|||

Alineo wrote:

Hi !
Is it possible to add an alert/warning via reportBuilder ?
An alert is for example a value which is superior of a nominal value for example...
I saw in a demo, it is possible to have it by creating a function, but this way is for the developper, if the user wants to add an alert himself ? how can he do ?
and maybe how can the developper help him ?

thank you for your answer

HI alineo,

It sounds like you are having the same problem or concept as I do. I am not sure whether this is the same problem as I am having, but here is the post that started this discussion. hope it might shed some light or else maybe someone can provide some enlightenment to us.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1450478&SiteID=1

Is there a way where you can show where this is working ? the demo you were speaking of?

thanks

Hope this helps.

Bernard

|||No, there is no way to place custom error messages in the user interface.

Jens K. Suessmeyer.

http://www.sqlserver2005.de

add a pramater.value to the jump to url link

I need to open a aspx page from a report jump to ulr action. but I need to pass in a prameter value. see example
http://ktec_inet/gco/productionreports/prod_asp/pcba_station_detail.asp?processname=335ICT×tampw=999999999&userid=WeeklyYieldPCBA&queryflag= Parameters!prod_cls.Value
--
Message posted via http://www.sqlmonster.comUse the expression
="http://ktec_inet/gco/productionreports/prod_asp/pcba_station_detail.asp?
processname=335ICT×tampw=999999999&userid=WeeklyYieldPCBA&queryflag=" &
Parameters!prod_cls.Value
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Lee Hopkins via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:4b989eb2a43e487a8a731afd19e27357@.SQLMonster.com...
>I need to open a aspx page from a report jump to ulr action. but I need to
>pass in a prameter value. see example
> http://ktec_inet/gco/productionreports/prod_asp/pcba_station_detail.asp?processname=335ICT×tampw=999999999&userid=WeeklyYieldPCBA&queryflag=
> Parameters!prod_cls.Value
> --
> Message posted via http://www.sqlmonster.com

Ad Hoc Distributed Query Against MS Access

Hello,
I have been looking for an example of how to do an ad hoc distributed query to an MS Access database. I've tried this;

select * from OpenRowSet('Microsoft.Jet.OLEDB.4.0',
'\\Server1\Data\Corporate\CPSDaily\Tools\Test.mdb' ;'admin';'',Names)

I recieve an OLEDB provider error when I try it this way. The version of Access is 2003. The database 'Test.mdb' is on a network share, and the table is called 'Names'. The share is located on the machine that hosts SQL Server 2000.

Is there a setting in sql server that can be set to allow/disallow ad hoc distributed queries?

What am I missing?

Thank you for your help!

cdun2

Are you doing something in SSIS, or shall I move this to the Transact-SQL forum?|||I was attempting to do this within an Execute SQL Task of an SSIS package.|||Why don't you create an OLE connection manager object that points to the Access database and then in the Execute SQL Task you reference that OLE connection manager and then simply use a "select * from table" query?|||

Ultimately, I was attempting to incorporate the use of CASE in the query. Anyway, I found a better way to do was I was trying to do, but I have another problem with a Precedence Constraint.

Thank you for your response, I'll post again later.