Having trouble Creating Linked Server to Access database

I been having an issue trying to connect an Access database through SQL
Server as a Linked Server.
The crux of the problem is the Access Database (mdb file) has a Database
Password and this cannot be removed due to the database still being used by a
VB application. SO can someone Assist in a detailed outline that works, for
configuring a Linked Server connecting to a mdb with a Database Password set.
If the password is temperarily removed from the database I am able to create
a Linked Server using the "MS Jet 4.0 OLD DB Provider" However to complete
my objective I cannot remove the Database Password on our clients DB.
When the password is put back in, then once again I am unable to access the
DB. I have tried different configuration options and recieved different
errors. for instance on one try SQL Server was erroring and saying it needed
a mdw file that we do not have becaue that is for User Level security which
out MDB is not using.
I tried using a OpenROWSET command and providing a Provider Connection
String setting the Database Password in the String, However this Also errors
with a Could not find Installable ISAM.
Thanks
Tony
Hi Tony,
I understand that you would like to know how to open your
password-protected access database (.mdb) in SQL Server via linked server
or OPENROWSET/OPENDATASOURCE.
If I have misunderstood, please let me know.
I reproduced your issue at my side. It seems not possible to use linked
server or OPENROWSET to connect to a password-protected mdb file. However
per my test, you can use OPENDATASOURCE to query your MDB file with
specifying the parameter "Jet OLEDB:Database Password=******". For example:
SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Data
Source="C:\Documents and Settings\charles\My Documents\db1.mdb";User
ID=Admin;Password=;Jet OLEDB:Database Password=myPassword!')...tblDT;
I am not sure why the parameter does not work for OPENROWSET and linked
server and I will try to consult the product team regarding the issue. If
there is any response, I will let you know. Anyway as a temporary
workaround, I recommend that you use OPENDATASOURCE to see if it helps.
If you have any other questions or concerns, please feel free to let me
know. It is my pleasure to be of assistance.
Best regards,
Charles Wang
Microsoft Online Community Support
================================================== ====
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====

Having trouble Creating Linked Server to Access database

I been having an issue trying to connect an Access database through SQL
Server as a Linked Server.
The crux of the problem is the Access Database (mdb file) has a Database
Password and this cannot be removed due to the database still being used by
a
VB application. SO can someone Assist in a detailed outline that works, fo
r
configuring a Linked Server connecting to a mdb with a Database Password set
.
If the password is temperarily removed from the database I am able to create
a Linked Server using the "MS Jet 4.0 OLD DB Provider" However to complete
my objective I cannot remove the Database Password on our clients DB.
When the password is put back in, then once again I am unable to access the
DB. I have tried different configuration options and recieved different
errors. for instance on one try SQL Server was erroring and saying it neede
d
a mdw file that we do not have becaue that is for User Level security which
out MDB is not using.
I tried using a OpenROWSET command and providing a Provider Connection
String setting the Database Password in the String, However this Also errors
with a Could not find Installable ISAM.
Thanks
TonyHi Tony,
I understand that you would like to know how to open your
password-protected access database (.mdb) in SQL Server via linked server
or OPENROWSET/OPENDATASOURCE.
If I have misunderstood, please let me know.
I reproduced your issue at my side. It seems not possible to use linked
server or OPENROWSET to connect to a password-protected mdb file. However
per my test, you can use OPENDATASOURCE to query your MDB file with
specifying the parameter "Jet OLEDB:Database Password=******". For example:
SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Data
Source="C:\Documents and Settings\charles\My Documents\db1.mdb";User
ID=Admin;Password=;Jet OLEDB:Database Password=myPassword!')...tblDT;
I am not sure why the parameter does not work for OPENROWSET and linked
server and I will try to consult the product team regarding the issue. If
there is any response, I will let you know. Anyway as a temporary
workaround, I recommend that you use OPENDATASOURCE to see if it helps.
If you have any other questions or concerns, please feel free to let me
know. It is my pleasure to be of assistance.
Best regards,
Charles Wang
Microsoft Online Community Support
========================================
==============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============|||Hi Tony,
Just notify that I have not got the product team's confirmation now. I will
try to follow up them. Since the process may need a long time, could you
please just leave me (changliw_at_microsoft_dot_com) an email response so
that I can timely update you when I got the confirmation?
Also could you please let me know whether or not the OPENDATASOURCE method
worked for you?
If you have any questions or concerns, please feel free to let me know. It
is my pleasure to be of your assistance.
Best regards,
Charles Wang
Microsoft Online Community Support
========================================
=============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============sql

Having trouble converting to datetime in SQL import

Our database gets updated each week with text files spat out by a mainframe. Previously, the database was in Access; we copied and pasted the text files into Excel, ran macros on them to convert the data, then pasted the results directly into Access and this worked fine for us.

Now that we've moved the tables to SQL Server 2000, we're having problems with the data. We wanted to set up DTS packages for each file to just put them directly into SQL Server. This works for pretty much everything except for the dates.

The way the files are set up, they're comma delimited files with quotes around the text and nothing around the dates. The dates don't have any delimiters; they're just listed like 13012006. Every time we try to import these files into SQL, it gripes about the datatypes; we're trying to put the dates into datetime fields but SQL thinks they're strings. Eek! If we put date delimiters (like 13/01/2006) SQL pulls them in fine, but apparently the mainframe lacks the ability to put these delimiters in by itself and still run everything else OK. The person who writes the extracts has to do it in a language called 'Focus' which I've never heard of and don't know anything about, and he says what I'm asking for can't be done. OK...so now what?

I've tried and tried to convert these strings into dates using both CAST() and CONVERT() and just can't manage to do it. I know I'm missing something really obvious here; does anyone have any tips or advice? Thanks in advance.It's a bit of a pain to find in the DTS Designer, but within the Data Pump task, if you individually define the column mappings, there is an option to select DateTime strings.

One way to do this is to:
1. Select your source and destination connections
2. Add the Data Pump task
3. Specify your source table/query
4. Specify the destination query
5. Click on the 'Transformations' tab (usually the column mappings will auto-populate)
6. Click on the column mapping where the source is the date formatted as a string.
7. Delete this column mapping (click on delete or hit the delete key)
8. Select the source column and the destination column and click on 'New'
9. Choose the third item from the pop-up list (DateTime string)
10. You will then have to define the properties of the source and destination formats.

I've worked with this before and it worked pretty well.

Alternatively, you could proceed through item 8 and then (in item 9) choose ActiveX script. This will allow you to define more custom formats and insert additional error checking and/or handling.

Regards,

hmscott|||It's a bit of a pain to find in the DTS Designer, but within the Data Pump task, if you individually define the column mappings, there is an option to select DateTime strings.

One way to do this is to:
1. Select your source and destination connections
2. Add the Data Pump task
3. Specify your source table/query
4. Specify the destination query
5. Click on the 'Transformations' tab (usually the column mappings will auto-populate)
6. Click on the column mapping where the source is the date formatted as a string.
7. Delete this column mapping (click on delete or hit the delete key)
8. Select the source column and the destination column and click on 'New'
9. Choose the third item from the pop-up list (DateTime string)
10. You will then have to define the properties of the source and destination formats.

I've worked with this before and it worked pretty well.

Alternatively, you could proceed through item 8 and then (in item 9) choose ActiveX script. This will allow you to define more custom formats and insert additional error checking and/or handling.

Regards,

hmscott

Thanks for the advice; I'll try your first suggestion, since I can't write ActiveX script. I can usually manage SQL and I can almost always figure it out on my own, but this one stumped me. I'm not a certified DBA or anything, I'm still learning. :)|||It's a bit of a pain to find in the DTS Designer, but within the Data Pump task, if you individually define the column mappings, there is an option to select DateTime strings.

One way to do this is to:
1. Select your source and destination connections
2. Add the Data Pump task
3. Specify your source table/query
4. Specify the destination query
5. Click on the 'Transformations' tab (usually the column mappings will auto-populate)
6. Click on the column mapping where the source is the date formatted as a string.
7. Delete this column mapping (click on delete or hit the delete key)
8. Select the source column and the destination column and click on 'New'
9. Choose the third item from the pop-up list (DateTime string)
10. You will then have to define the properties of the source and destination formats.

I've worked with this before and it worked pretty well.

Alternatively, you could proceed through item 8 and then (in item 9) choose ActiveX script. This will allow you to define more custom formats and insert additional error checking and/or handling.

Regards,

hmscott

We tried using datetime strings and it just refused to work; we had to write ActiveX scripts n the end using DateSerial in VBScript, which was tough for us because neither one of us really knows VBScript, I'm a VB noob and my colleague knows VBA but not VBScript; I know they're basically the same but he doesn't. :) But we did eventually get it to work! So yay!

Having 1 user in 2 NT groups causing problems

Hopefully this isnt by design...
I have a user who is a member of 2 NT groups. Both these
groups have access to one of our SQL 2k boxes.
Group A only has access to DB1 as dbo.
Group B only has access to DB2 as dbo.
When this user connects and logs onto DB2 it doesnt work
as it says he doesnt have enough permissions. I'm guessing
its because its only checked Group A?
Short of rejigging our NT groups is there a way round
this? Or have I done something stupid?
SQL Server 2000 sp3a, NT4. User connecting from NT4 client.
Thanks in advance.
Mike.Has Group A been specifically denied any permissions in DB2?
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Mike Watson" <mike@.REMOVE.prog99.com> wrote in message
news:ac7b01c43696$7e46f3f0$a501280a@.phx.gbl...
Hopefully this isnt by design...
I have a user who is a member of 2 NT groups. Both these
groups have access to one of our SQL 2k boxes.
Group A only has access to DB1 as dbo.
Group B only has access to DB2 as dbo.
When this user connects and logs onto DB2 it doesnt work
as it says he doesnt have enough permissions. I'm guessing
its because its only checked Group A?
Short of rejigging our NT groups is there a way round
this? Or have I done something stupid?
SQL Server 2000 sp3a, NT4. User connecting from NT4 client.
Thanks in advance.
Mike.|||>--Original Message--
>Has Group A been specifically denied any permissions in
DB2?
>--
>Tom
Thanks for the swift response Tom. No group A has been
neither denied or granted any access to DB2.
I'm beginning to wonder if group2 has had something
revoked further up the chain somewhere in the domain.|||Does this particular user have any denied permissions in DB2?
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Mike Watson" <mike@.remove.prog99.som> wrote in message
news:a9a001c43699$b28eb690$a401280a@.phx.gbl...
>--Original Message--
>Has Group A been specifically denied any permissions in
DB2?
>--
>Tom
Thanks for the swift response Tom. No group A has been
neither denied or granted any access to DB2.
I'm beginning to wonder if group2 has had something
revoked further up the chain somewhere in the domain.|||
>--Original Message--
>Does this particular user have any denied permissions in
DB2?
>--
>Tom
Nothing immediately jumps out at me. As far as I can see
they are just dbo with no other extra bits.|||What sort of message are you getting?
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
<anonymous@.discussions.microsoft.com> wrote in message
news:ada101c4369c$64a21b40$a001280a@.phx.gbl...

>--Original Message--
>Does this particular user have any denied permissions in
DB2?
>--
>Tom
Nothing immediately jumps out at me. As far as I can see
they are just dbo with no other extra bits.|||
>--Original Message--
>What sort of message are you getting?
>--
>Tom
Unfortunately its going home time here in Scotland and the
weather is surprisingly good so they have gone home. I'll
liase with them tomorrow and see exactly what message they
are getting.|||Glad it worked out. In that case, Group B would not have been able to
access its own database either.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
.
"Mike Watson" <mike@.remove.prog99.com> wrote in message
news:5F75D6D5-76F5-47B3-8858-83FAC6FABFF5@.microsoft.com...
Sorted!
NT group B hadnt been granted access to the NT server. Did that and it all
worked smoothly.
thanks for your assistance.

Have Suggestions for a Front End Tool?

I am currently using Microsoft Excel pivot tables to access data cubes from SQL Server 2002 as a front end tool for users to access sales data. I have run into issues when I am asking the pivot to filter too much. Does anyone have any suggestions on another frontend tool for cubes that does not run into limitations like Excel does? A 3rd party tool? I know Access could be used but I would like to purchase something not write a program?

Thank you,

Carla C. HertelCheck out the following link:

link (http://www.hungrydogsoftware.com/)|||Here is a bigger list:

list (http://www.dwinfocenter.org/olap.html)

But the hungry dog intellibrowser is pretty good.|||Have you checked out Microsoft's pivot table Office Web Component? If you haven't it might be worth a look.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odeopg/html/deovrworkingwithofficewebcomponents.asp

Good luck|||Panorama Novaview http://www.pansw.com/ OLAP front-end is made by the same company that licensed Microsoft the OLAP server technology for Analysis Services. It has write-back capabilities unlike Bus Obj, Cognos, Crystal et al and some nice features like bubble-up exceptions, wizards for query creation and good thin-client support.

SQLmag published a review of the leading OLAP front-ends for Microsoft Analysis Services:

http://www.sqlmag.com/Articles/Index.cfm?ArticleID=26486

There are many tools on the market, but this is the short list.|||i can recommend BusinessObjects - although some dum people (like our MIS dept) use universes only, BusinessObjects can work with MS Analytical Servicers just fine. Business Objects is more reporting tool. They have also special add-in into Excel called BusinessQuery.

microsoft has data analyzer (it's 100% analytical tool) http://www.microsoft.com/office/dataanalyzer/default.asp

jiri|||Hi,

as a client tool we use Q4bisAnalysis.
A quit nice little tool which could be learned within minutes.

More infos:
www.q4bis.com

regards

Michael

have sql file and want to run it on an ftp connection

i have a db set up that i access using an ftp connection, and an sql script
file that i wish to run, how do i go about doing so?

tiastrawberry (1@.2.com) writes:
> i have a db set up that i access using an ftp connection, and an sql
> script file that i wish to run, how do i go about doing so?

You have an SQL Server database that you access through FTP? Maybe I'm
dense, but I don't understand how you could do that?

Of course, if your FTP client permits you to run remote scripts, you
could upload your script, and then run it with OSQL.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi

Why can you not get access to connect to the remote server, possibly through
a VPN?

John

"strawberry" <1@.2.com> wrote in message
news:exJ8f.19119$6i4.3749@.newsfe7-gui.ntli.net...
>i have a db set up that i access using an ftp connection, and an sql script
>file that i wish to run, how do i go about doing so?
> tia|||this is half the problem, I'm not sure how or where to execute the file I've
tried all sorts but suspect i don't have the correct privileges ( its a
university server)

thanks for your reply.

"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:436396ac$0$361$da0feed9@.news.zen.co.uk...
> Hi
> Why can you not get access to connect to the remote server, possibly
> through a VPN?
> John
> "strawberry" <1@.2.com> wrote in message
> news:exJ8f.19119$6i4.3749@.newsfe7-gui.ntli.net...
>>i have a db set up that i access using an ftp connection, and an sql
>>script file that i wish to run, how do i go about doing so?
>>
>> tia
>>|||its hosted on a linux server which has ftp access to it. what is osql?, and
where is the best place to upload it too ?

thanks for your reply

"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns96FE92704E4B5Yazorman@.127.0.0.1...
> strawberry (1@.2.com) writes:
>> i have a db set up that i access using an ftp connection, and an sql
>> script file that i wish to run, how do i go about doing so?
> You have an SQL Server database that you access through FTP? Maybe I'm
> dense, but I don't understand how you could do that?
> Of course, if your FTP client permits you to run remote scripts, you
> could upload your script, and then run it with OSQL.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||strawberry (1@.2.com) writes:
> its hosted on a linux server which has ftp access to it. what is osql?,
> and where is the best place to upload it too ?

The database is on a Linux server? OK, then you are in the wrong newsgroup.
This newsgroup is about MS SQL Server, which runs only on Windows. (And
OSQL is a command-line tool to access MS SQL Server. On Windows.)

I recommend that you find out which DB engine you are using, and then
find the a forum that engine. Or even better ask the staff at the
university where the DB engine is.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||ahh, oppsy! . hadn't thought of that!

many thanks.

"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns96FF6A8A99F2Yazorman@.127.0.0.1...
> strawberry (1@.2.com) writes:
>> its hosted on a linux server which has ftp access to it. what is osql?,
>> and where is the best place to upload it too ?
> The database is on a Linux server? OK, then you are in the wrong
> newsgroup.
> This newsgroup is about MS SQL Server, which runs only on Windows. (And
> OSQL is a command-line tool to access MS SQL Server. On Windows.)
> I recommend that you find out which DB engine you are using, and then
> find the a forum that engine. Or even better ask the staff at the
> university where the DB engine is.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp

HAS_DBACCESS for any user name

Hi All,
I need function looks like HAS_DBACCESS for any user name.
How can I check has user access to db? As a user or as a member of group.
Thanks.See whether this NG thread helps.
http://tinyurl.com/88jl2
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Oleg Cherkasenko" <oleg@.opel.com.ua> wrote in message
news:OMIZzsHrFHA.528@.TK2MSFTNGP09.phx.gbl...
> Hi All,
> I need function looks like HAS_DBACCESS for any user name.
> How can I check has user access to db? As a user or as a member of group.
> Thanks.
>
>|||Thanksm its useful.
The hard case is:
I have a windows user 'user1' which is a member of windows group 'group1'.
In SQL Server I added this group 'group1' only but not 'user1'.
'user1' has a db access via memebership in 'group1', but I cannot find this
user name in sql server tables and procedures.
Its a question.
Regards.
"Roji. P. Thomas" <thomasroji@.gmail.com> wrote in message
news:evwZvzHrFHA.908@.tk2msftngp13.phx.gbl...
> See whether this NG thread helps.
> http://tinyurl.com/88jl2
> --
> Roji. P. Thomas

> Net Asset Management
> http://toponewithties.blogspot.com
>
> "Oleg Cherkasenko" <oleg@.opel.com.ua> wrote in message
> news:OMIZzsHrFHA.528@.TK2MSFTNGP09.phx.gbl...
>sql

Has anyone tried "Using SQL Stored procedure in MS ACCESS"?

I am developing a program using ms access with an SQL 2000 back end. I am using an odbc connection to access my data. Using this connection, i can only access the tables and views of my database. I am planning to create "Stored procedure" in SQL and connect it to my MS Access program but have no idea how to do it.

Please help! Thanks in Advance!Might I suggest an Access newsgroup as its an Access specific problem? SQL Server SPs should just be called like a SQL statement is executed, ie "exec usp_foo" instead of "select * from foo".|||if you want to view stored procedure from Access I suggest you to not use a Access Project (file .adp) instead of the classic Access database (.mdb);
in this way in the Query tab of you Access Project you will see views and stored procedure of the Sql server and then you can manage these directly by Access.
bye

Has anyone tried "Using SQL Stored procedure in MS ACCESS"?

I am developing a program using ms access with an SQL 2000 back end. I am using an odbc connection to access my data. Using this connection, i can only access the tables and views of my database. I am planning to create "Stored procedure" in SQL and connect it to my MS Access program but have no idea how to do it.

Please help! Thanks in Advance!Might I suggest an Access newsgroup as its an Access specific problem? SQL Server SPs should just be called like a SQL statement is executed, ie "exec usp_foo" instead of "select * from foo".|||if you want to view stored procedure from Access I suggest you to not use a Access Project (file .adp) instead of the classic Access database (.mdb);
in this way in the Query tab of you Access Project you will see views and stored procedure of the Sql server and then you can manage these directly by Access.
bye

HardDisk very busy while access SQL db

Dear all:
I'm using IIS6 + ASP + SQL2000sp3a. And between these month, when I connect
to my website, the harddisk will very busy, so my website become very slow.
But after HD read finished, the website will work fine.
I'm using virutal server, and another website work fine because it not
access sql server. I think its DB's problem.
Is there any method to check my db? Should I defrag my db or fix it'
Thank you very much for your read and reply!Fragmentation might be contributing to the performance problem, but there
are also many other problems as well. Performance tuning is a big topic & it
would be hard to suggest any one thing as being the most likely fix for your
problem given the limited info you've given here.
You need to follow a basic performance tuning approach - check high level
system metrics such as cpu, memory, disk queues etc. Then you make further
assumptions based on your observations & perhaps perform deeper system
analysis or go to the sql server performance tuning tools - such as SQL
Profiler. You might determine that large tables are being scanned or there
are concurrency / blocking issues.
The SQL Profiler also provides an out of the box performance tuning template
which is worth looking at. Long running query filtering from Profiler can
often reveal as single or small group of problem queries.
Ultimately, you may even have a hardware resource issue.
You might find some interesting material on
http://www.sql-server-performance.com
HTH
Regards,
Greg Linwood
SQL Server MVP
"Come2" <come2@.ms76.url.com.tw> wrote in message
news:#NWpd4ExDHA.2308@.TK2MSFTNGP11.phx.gbl...
> Dear all:
> I'm using IIS6 + ASP + SQL2000sp3a. And between these month, when I
connect
> to my website, the harddisk will very busy, so my website become very
slow.
> But after HD read finished, the website will work fine.
> I'm using virutal server, and another website work fine because it not
> access sql server. I think its DB's problem.
> Is there any method to check my db? Should I defrag my db or fix it'
> Thank you very much for your read and reply!
>

Handling Errors...

Hello to all,

On my webPage I have Used one SQLDataSource to access DataBase. Now whenever some error occures it shows error page by default. I am not able to catch Errors and tackle in my way...

Furthermore in this new structure of accessing DataBase even I do not know where to write Try... Catch...

Hey mistry,
this is the structure for Try ... Catch ... in SQL

Begin Try
-- e.g.
INSERT INTO a SELECT * FROM b
-- your actual code
End Try
Begin Catch
-- code for error handling
End Catch

handling columns with multiple values

I am writing a stored procedure that needs a access individual entries in a column with multiple entries delimited by a comma(yeah i know, not 1st NF) . Like this:

Key

NotANormalizedCol

1

1324, 5124, 5435,5467

2

423, 23, 5345

3

52334, 53443, 1224

4

12, 4, 1243,66

is there a function that returns a substring given a delimiter character? the only substring returning function that i found are the LEFT and RIGHT that returns fixed length substring.

I am pretty new to this, so I apologize if this is a trivial questions

Look at http://www.sommarskog.se/arrays-in-sql.html|||Hi,

I once wrote a function for that which can be found here in this post: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=320221&SiteID=1

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

halt execution and wait for parameters

Hello,
does anybody know of a way to to have RS wait to execute (on access) until
the parameter fields have been filled?
thanks,
gregI've noticed this was a problem when I had default values for every
parameter. Try to remove default values.
"greg" <greg@.discussions.microsoft.com> wrote in message
news:DDF49D51-17BC-4D9B-ACD5-B240C559AC08@.microsoft.com...
> Hello,
> does anybody know of a way to to have RS wait to execute (on access) until
> the parameter fields have been filled?
> thanks,
> greg|||Thanks for the response. That is the same problem I'm having. I have to leave
defaults (8 fields) but the users change certian fields. thanks again good
to see others are having the same issues.
Greg
"Jason" wrote:
> I've noticed this was a problem when I had default values for every
> parameter. Try to remove default values.
> "greg" <greg@.discussions.microsoft.com> wrote in message
> news:DDF49D51-17BC-4D9B-ACD5-B240C559AC08@.microsoft.com...
> > Hello,
> > does anybody know of a way to to have RS wait to execute (on access) until
> > the parameter fields have been filled?
> >
> > thanks,
> > greg
>
>

half baked conversion - speed issue - any low hanging fruit ?

Continuing question about my MS Access to SQL conversion project :
0)Many thanks for previous assistance - we now have the batch process
running successfully on the clients network
1) It's an MS Access batch process using approximately 150 tables, 300
queries - sounds like a mess but it is a reasonably disciplined and
structured application - dealing with real world, very noisy data from
3 sources, massaging them in to a unified set of data, recording data
over a 25 year period.
2) The client does not want to pay for a full conversion to SQL - ie
convert all of MS Access code to stored procedures - just wants the
back end across to SQL for use with other tools (such as Cognos)
3) The batch process takes 12 hours on my little development
environment and 24 hours plus on their corporate network
(the original pure MS Access batch process take 45 minutes on my
network and 6 hours on theirs)
4) The client is now starting to understand the need to move some of
the processing in to the SQL server. For example - I have experimented
and found that a delete query on an intermediate work table will take
10 minutes via the Access front end, but 5 seconds as a pass thru
query. Also, some queries are taking 100 minutes to run across the
network, and if I focus attention on turning these in to pass thru
queries - I am sure I can drastically speed them up.
QUESTION
Before I put the client to the expense of additional development - are
there any other steps I should follow first - ie are there any
settings I should check on the SQL server.
For example - I don't need transaction logging at all - its really a
single user, batch application - if it crashes half way thru we can
just restart it and it is built in such a way that it will sort itself
out.
I have to confess that I only know enough about SQL server to be
dangerous - so if anyone could point me at some topics - I will go off
and do some research - but at the moment I don't know where to start.
Many thanks
TonyYou can't turn off logging completely but you can under the right conditions
do a "minimally logged load" Look in BooksOnLine under that topic for more
details. But this will only help with loading of tables and not
manipulating the data once there. Are you using SET NOCOUNT ON in all your
batches? I have no clue as to what they are really doing but if you are
trying to issue Deletes etc. via a gui when you can simply pass the query in
you are definitely going to slow things down. I suspect there are lots of
things you can do to speed things up but without knowing more about exactly
what you are doing and how it's pretty hard to say.
--
Andrew J. Kelly SQL MVP
<ace join_to ware@.iinet.net.au (Tony Epton)> wrote in message
news:41cb69e9.7622515@.news.m.iinet.net.au...
> Continuing question about my MS Access to SQL conversion project :
> 0)Many thanks for previous assistance - we now have the batch process
> running successfully on the clients network
> 1) It's an MS Access batch process using approximately 150 tables, 300
> queries - sounds like a mess but it is a reasonably disciplined and
> structured application - dealing with real world, very noisy data from
> 3 sources, massaging them in to a unified set of data, recording data
> over a 25 year period.
> 2) The client does not want to pay for a full conversion to SQL - ie
> convert all of MS Access code to stored procedures - just wants the
> back end across to SQL for use with other tools (such as Cognos)
> 3) The batch process takes 12 hours on my little development
> environment and 24 hours plus on their corporate network
> (the original pure MS Access batch process take 45 minutes on my
> network and 6 hours on theirs)
> 4) The client is now starting to understand the need to move some of
> the processing in to the SQL server. For example - I have experimented
> and found that a delete query on an intermediate work table will take
> 10 minutes via the Access front end, but 5 seconds as a pass thru
> query. Also, some queries are taking 100 minutes to run across the
> network, and if I focus attention on turning these in to pass thru
> queries - I am sure I can drastically speed them up.
> QUESTION
> Before I put the client to the expense of additional development - are
> there any other steps I should follow first - ie are there any
> settings I should check on the SQL server.
> For example - I don't need transaction logging at all - its really a
> single user, batch application - if it crashes half way thru we can
> just restart it and it is built in such a way that it will sort itself
> out.
> I have to confess that I only know enough about SQL server to be
> dangerous - so if anyone could point me at some topics - I will go off
> and do some research - but at the moment I don't know where to start.
> Many thanks
> Tony|||<ace join_to ware@.iinet.net.au (Tony Epton)> wrote in message
news:41cb69e9.7622515@.news.m.iinet.net.au...
> Continuing question about my MS Access to SQL conversion project :
> 0)Many thanks for previous assistance - we now have the batch process
> running successfully on the clients network
>
Good to hear.
> 1) It's an MS Access batch process using approximately 150 tables, 300
> queries - sounds like a mess but it is a reasonably disciplined and
> structured application - dealing with real world, very noisy data from
> 3 sources, massaging them in to a unified set of data, recording data
> over a 25 year period.
>
Yeah... there's "ideal" and "reality" :-)
> 2) The client does not want to pay for a full conversion to SQL - ie
> convert all of MS Access code to stored procedures - just wants the
> back end across to SQL for use with other tools (such as Cognos)
> 3) The batch process takes 12 hours on my little development
> environment and 24 hours plus on their corporate network
> (the original pure MS Access batch process take 45 minutes on my
> network and 6 hours on theirs)
Any idea why so much longer? More data or what?
> 4) The client is now starting to understand the need to move some of
> the processing in to the SQL server. For example - I have experimented
> and found that a delete query on an intermediate work table will take
> 10 minutes via the Access front end, but 5 seconds as a pass thru
> query. Also, some queries are taking 100 minutes to run across the
> network, and if I focus attention on turning these in to pass thru
> queries - I am sure I can drastically speed them up.
Yes. Generally in cases like this, as much as can be done on the server
should be. As you note, the speed improvements can be dramatic.
Ultimately this will probably sell them on moving more to SQL. Also, I'll
bet their network admins will notice the lower load as more is moved to the
DB and will thank you for it.
> QUESTION
> Before I put the client to the expense of additional development - are
> there any other steps I should follow first - ie are there any
> settings I should check on the SQL server.
"Maybe". There are some best practices, such as splitting log traffic to a
separate RAID 1 or RAID 10.
But, generally I'd look at code first. If you're already going from 600
seconds to 5 seconds, tweaking the server most likely won't get you another
120x improvement.
> For example - I don't need transaction logging at all - its really a
> single user, batch application - if it crashes half way thru we can
> just restart it and it is built in such a way that it will sort itself
> out.
Simple logging will help.
> I have to confess that I only know enough about SQL server to be
> dangerous - so if anyone could point me at some topics - I will go off
> and do some research - but at the moment I don't know where to start.
>
Sounds like you're off to a good start already.
> Many thanks
> Tony

half baked conversion - speed issue - any low hanging fruit ?

Continuing question about my MS Access to SQL conversion project :
0)Many thanks for previous assistance - we now have the batch process
running successfully on the clients network
1) It's an MS Access batch process using approximately 150 tables, 300
queries - sounds like a mess but it is a reasonably disciplined and
structured application - dealing with real world, very noisy data from
3 sources, massaging them in to a unified set of data, recording data
over a 25 year period.
2) The client does not want to pay for a full conversion to SQL - ie
convert all of MS Access code to stored procedures - just wants the
back end across to SQL for use with other tools (such as Cognos)
3) The batch process takes 12 hours on my little development
environment and 24 hours plus on their corporate network
(the original pure MS Access batch process take 45 minutes on my
network and 6 hours on theirs)
4) The client is now starting to understand the need to move some of
the processing in to the SQL server. For example - I have experimented
and found that a delete query on an intermediate work table will take
10 minutes via the Access front end, but 5 seconds as a pass thru
query. Also, some queries are taking 100 minutes to run across the
network, and if I focus attention on turning these in to pass thru
queries - I am sure I can drastically speed them up.
QUESTION
Before I put the client to the expense of additional development - are
there any other steps I should follow first - ie are there any
settings I should check on the SQL server.
For example - I don't need transaction logging at all - its really a
single user, batch application - if it crashes half way thru we can
just restart it and it is built in such a way that it will sort itself
out.
I have to confess that I only know enough about SQL server to be
dangerous - so if anyone could point me at some topics - I will go off
and do some research - but at the moment I don't know where to start.
Many thanks
Tony
You can't turn off logging completely but you can under the right conditions
do a "minimally logged load" Look in BooksOnLine under that topic for more
details. But this will only help with loading of tables and not
manipulating the data once there. Are you using SET NOCOUNT ON in all your
batches? I have no clue as to what they are really doing but if you are
trying to issue Deletes etc. via a gui when you can simply pass the query in
you are definitely going to slow things down. I suspect there are lots of
things you can do to speed things up but without knowing more about exactly
what you are doing and how it's pretty hard to say.
Andrew J. Kelly SQL MVP
<ace join_to ware@.iinet.net.au (Tony Epton)> wrote in message
news:41cb69e9.7622515@.news.m.iinet.net.au...
> Continuing question about my MS Access to SQL conversion project :
> 0)Many thanks for previous assistance - we now have the batch process
> running successfully on the clients network
> 1) It's an MS Access batch process using approximately 150 tables, 300
> queries - sounds like a mess but it is a reasonably disciplined and
> structured application - dealing with real world, very noisy data from
> 3 sources, massaging them in to a unified set of data, recording data
> over a 25 year period.
> 2) The client does not want to pay for a full conversion to SQL - ie
> convert all of MS Access code to stored procedures - just wants the
> back end across to SQL for use with other tools (such as Cognos)
> 3) The batch process takes 12 hours on my little development
> environment and 24 hours plus on their corporate network
> (the original pure MS Access batch process take 45 minutes on my
> network and 6 hours on theirs)
> 4) The client is now starting to understand the need to move some of
> the processing in to the SQL server. For example - I have experimented
> and found that a delete query on an intermediate work table will take
> 10 minutes via the Access front end, but 5 seconds as a pass thru
> query. Also, some queries are taking 100 minutes to run across the
> network, and if I focus attention on turning these in to pass thru
> queries - I am sure I can drastically speed them up.
> QUESTION
> Before I put the client to the expense of additional development - are
> there any other steps I should follow first - ie are there any
> settings I should check on the SQL server.
> For example - I don't need transaction logging at all - its really a
> single user, batch application - if it crashes half way thru we can
> just restart it and it is built in such a way that it will sort itself
> out.
> I have to confess that I only know enough about SQL server to be
> dangerous - so if anyone could point me at some topics - I will go off
> and do some research - but at the moment I don't know where to start.
> Many thanks
> Tony
|||<ace join_to ware@.iinet.net.au (Tony Epton)> wrote in message
news:41cb69e9.7622515@.news.m.iinet.net.au...
> Continuing question about my MS Access to SQL conversion project :
> 0)Many thanks for previous assistance - we now have the batch process
> running successfully on the clients network
>
Good to hear.

> 1) It's an MS Access batch process using approximately 150 tables, 300
> queries - sounds like a mess but it is a reasonably disciplined and
> structured application - dealing with real world, very noisy data from
> 3 sources, massaging them in to a unified set of data, recording data
> over a 25 year period.
>
Yeah... there's "ideal" and "reality" :-)

> 2) The client does not want to pay for a full conversion to SQL - ie
> convert all of MS Access code to stored procedures - just wants the
> back end across to SQL for use with other tools (such as Cognos)
> 3) The batch process takes 12 hours on my little development
> environment and 24 hours plus on their corporate network
> (the original pure MS Access batch process take 45 minutes on my
> network and 6 hours on theirs)
Any idea why so much longer? More data or what?

> 4) The client is now starting to understand the need to move some of
> the processing in to the SQL server. For example - I have experimented
> and found that a delete query on an intermediate work table will take
> 10 minutes via the Access front end, but 5 seconds as a pass thru
> query. Also, some queries are taking 100 minutes to run across the
> network, and if I focus attention on turning these in to pass thru
> queries - I am sure I can drastically speed them up.
Yes. Generally in cases like this, as much as can be done on the server
should be. As you note, the speed improvements can be dramatic.
Ultimately this will probably sell them on moving more to SQL. Also, I'll
bet their network admins will notice the lower load as more is moved to the
DB and will thank you for it.

> QUESTION
> Before I put the client to the expense of additional development - are
> there any other steps I should follow first - ie are there any
> settings I should check on the SQL server.
"Maybe". There are some best practices, such as splitting log traffic to a
separate RAID 1 or RAID 10.
But, generally I'd look at code first. If you're already going from 600
seconds to 5 seconds, tweaking the server most likely won't get you another
120x improvement.

> For example - I don't need transaction logging at all - its really a
> single user, batch application - if it crashes half way thru we can
> just restart it and it is built in such a way that it will sort itself
> out.
Simple logging will help.

> I have to confess that I only know enough about SQL server to be
> dangerous - so if anyone could point me at some topics - I will go off
> and do some research - but at the moment I don't know where to start.
>
Sounds like you're off to a good start already.

> Many thanks
> Tony

half baked conversion - speed issue - any low hanging fruit ?

Continuing question about my MS Access to SQL conversion project :
0)Many thanks for previous assistance - we now have the batch process
running successfully on the clients network
1) It's an MS Access batch process using approximately 150 tables, 300
queries - sounds like a mess but it is a reasonably disciplined and
structured application - dealing with real world, very noisy data from
3 sources, massaging them in to a unified set of data, recording data
over a 25 year period.
2) The client does not want to pay for a full conversion to SQL - ie
convert all of MS Access code to stored procedures - just wants the
back end across to SQL for use with other tools (such as Cognos)
3) The batch process takes 12 hours on my little development
environment and 24 hours plus on their corporate network
(the original pure MS Access batch process take 45 minutes on my
network and 6 hours on theirs)
4) The client is now starting to understand the need to move some of
the processing in to the SQL server. For example - I have experimented
and found that a delete query on an intermediate work table will take
10 minutes via the Access front end, but 5 seconds as a pass thru
query. Also, some queries are taking 100 minutes to run across the
network, and if I focus attention on turning these in to pass thru
queries - I am sure I can drastically speed them up.
QUESTION
Before I put the client to the expense of additional development - are
there any other steps I should follow first - ie are there any
settings I should check on the SQL server.
For example - I don't need transaction logging at all - its really a
single user, batch application - if it crashes half way thru we can
just restart it and it is built in such a way that it will sort itself
out.
I have to confess that I only know enough about SQL server to be
dangerous - so if anyone could point me at some topics - I will go off
and do some research - but at the moment I don't know where to start.
Many thanks
TonyYou can't turn off logging completely but you can under the right conditions
do a "minimally logged load" Look in BooksOnLine under that topic for more
details. But this will only help with loading of tables and not
manipulating the data once there. Are you using SET NOCOUNT ON in all your
batches? I have no clue as to what they are really doing but if you are
trying to issue Deletes etc. via a gui when you can simply pass the query in
you are definitely going to slow things down. I suspect there are lots of
things you can do to speed things up but without knowing more about exactly
what you are doing and how it's pretty hard to say.
Andrew J. Kelly SQL MVP
<ace join_to ware@.iinet.net.au (Tony Epton)> wrote in message
news:41cb69e9.7622515@.news.m.iinet.net.au...
> Continuing question about my MS Access to SQL conversion project :
> 0)Many thanks for previous assistance - we now have the batch process
> running successfully on the clients network
> 1) It's an MS Access batch process using approximately 150 tables, 300
> queries - sounds like a mess but it is a reasonably disciplined and
> structured application - dealing with real world, very noisy data from
> 3 sources, massaging them in to a unified set of data, recording data
> over a 25 year period.
> 2) The client does not want to pay for a full conversion to SQL - ie
> convert all of MS Access code to stored procedures - just wants the
> back end across to SQL for use with other tools (such as Cognos)
> 3) The batch process takes 12 hours on my little development
> environment and 24 hours plus on their corporate network
> (the original pure MS Access batch process take 45 minutes on my
> network and 6 hours on theirs)
> 4) The client is now starting to understand the need to move some of
> the processing in to the SQL server. For example - I have experimented
> and found that a delete query on an intermediate work table will take
> 10 minutes via the Access front end, but 5 seconds as a pass thru
> query. Also, some queries are taking 100 minutes to run across the
> network, and if I focus attention on turning these in to pass thru
> queries - I am sure I can drastically speed them up.
> QUESTION
> Before I put the client to the expense of additional development - are
> there any other steps I should follow first - ie are there any
> settings I should check on the SQL server.
> For example - I don't need transaction logging at all - its really a
> single user, batch application - if it crashes half way thru we can
> just restart it and it is built in such a way that it will sort itself
> out.
> I have to confess that I only know enough about SQL server to be
> dangerous - so if anyone could point me at some topics - I will go off
> and do some research - but at the moment I don't know where to start.
> Many thanks
> Tony|||<ace join_to ware@.iinet.net.au (Tony Epton)> wrote in message
news:41cb69e9.7622515@.news.m.iinet.net.au...
> Continuing question about my MS Access to SQL conversion project :
> 0)Many thanks for previous assistance - we now have the batch process
> running successfully on the clients network
>
Good to hear.

> 1) It's an MS Access batch process using approximately 150 tables, 300
> queries - sounds like a mess but it is a reasonably disciplined and
> structured application - dealing with real world, very noisy data from
> 3 sources, massaging them in to a unified set of data, recording data
> over a 25 year period.
>
Yeah... there's "ideal" and "reality" :-)

> 2) The client does not want to pay for a full conversion to SQL - ie
> convert all of MS Access code to stored procedures - just wants the
> back end across to SQL for use with other tools (such as Cognos)
> 3) The batch process takes 12 hours on my little development
> environment and 24 hours plus on their corporate network
> (the original pure MS Access batch process take 45 minutes on my
> network and 6 hours on theirs)
Any idea why so much longer? More data or what?

> 4) The client is now starting to understand the need to move some of
> the processing in to the SQL server. For example - I have experimented
> and found that a delete query on an intermediate work table will take
> 10 minutes via the Access front end, but 5 seconds as a pass thru
> query. Also, some queries are taking 100 minutes to run across the
> network, and if I focus attention on turning these in to pass thru
> queries - I am sure I can drastically speed them up.
Yes. Generally in cases like this, as much as can be done on the server
should be. As you note, the speed improvements can be dramatic.
Ultimately this will probably sell them on moving more to SQL. Also, I'll
bet their network admins will notice the lower load as more is moved to the
DB and will thank you for it.

> QUESTION
> Before I put the client to the expense of additional development - are
> there any other steps I should follow first - ie are there any
> settings I should check on the SQL server.
"Maybe". There are some best practices, such as splitting log traffic to a
separate RAID 1 or RAID 10.
But, generally I'd look at code first. If you're already going from 600
seconds to 5 seconds, tweaking the server most likely won't get you another
120x improvement.

> For example - I don't need transaction logging at all - its really a
> single user, batch application - if it crashes half way thru we can
> just restart it and it is built in such a way that it will sort itself
> out.
Simple logging will help.

> I have to confess that I only know enough about SQL server to be
> dangerous - so if anyone could point me at some topics - I will go off
> and do some research - but at the moment I don't know where to start.
>
Sounds like you're off to a good start already.

> Many thanks
> Tony