SQL Server Management Studio: 2012

Friday, March 30, 2012

Migrating from MDF File to SQL Server 2005?

I created a web site using the "personal web site" starter kit with Visual Studio 2005. It created an ASPNET MDF file as well as a Personal.MDF in my App_Data directory.

I want to migrate this site to a production server that does not have SQL Express, but does have a full version of SQL Server 2005.

How do I migrate both the database format (tables and SPs) as well as the data to the SQL Server?

I can't find anything that looks like Enterprise Manager or Query Analyzer on my system, and installing the "native client tools" doesn't do anything except install configuration utilities (no clients).

Inside of Visual Studio 2005, I can bring up both databases (one from the MDF file(s) and one using a connection string to the server), but there doesn't appear to be any way to copy from one to the other.

There must be an easy way to do this, right?

Many thanks in advance!

Hello cambler,

You can simply do a straight-forward Attach Database function in SQL Server 2005 Management Studio. You can simply copy the MDF and LDF files from your App_Data folder from your PC to your production server running SQL Server 2005. Then, attach the database pointing to the copy of the MDF file. You need to detach the database files first on your development PC before you can copy the MDF and LDF files as the service is making use of these files.

For more information, visit http://support.microsoft.com/default.aspx?scid=kb;en-us;224071

|||

I am trying to utilize the SQL Server Management Studio to open an MDF file, so I can perform tasks such as importing CSV files. I guess that I need to attach to a an MDF file, but I don't see the that option available.

If there is a nother approach, please advise.

Thanks,

|||From SQL Server Management Studio, you can simply right-click on the Databases folder and select Attach. Then point to the location of your MDF file. That should do the trick.sql

Migrating from Intebrase to SQL Server

Hi.
We've been having many problems with our current Interbase database
(v6.0.2.0). These are mainly corruption issues with the constant need to
backup and restore, and the fact that it is almost impossible to reliably
change metadata in a live environment.
We need to change servers, and our two options are either upgrading to
Interbase 7.1, or migrating to SQL Server 2000.
These are our requirements:
- the ability to reliaby and efficiently cope with huge amounts of data
- the server must be able to cope seemlessly with clients not disconnecting
cleanly and transactions left open (IB seems to have trouble with this), or
at least provide some mechanism to view such transactions and kill them
- the ability to change metada in a live environment with many connected
users (create/drop tables, foreign keys, etc - is this possible with any DB
at all?)
- it is critical that the database is up 24/7 - we can afford no downtime -
is this something SQL Server can provide?
I have also read a lot about how Interbase is unique in that it provides a
multi-generational architecture - presumably this means SQL Server does not,
but what are the implications of this? Could someone explain how SQL's
architecture differs from IB's with respect to this?
Has anyone else had any experience (good or bad) migrating from IB to SQL?
Perhaps our problems would not be solved by this and therefore it would be a
waste of time and money?
We basically need as much info on the subject as possible so we can make an
informed decision.
MikeAs you haven't gotten any replies yet, I'll only chime in with my little comment (I haven't worked
with Interbase):
> I have also read a lot about how Interbase is unique in that it provides a
> multi-generational architecture - presumably this means SQL Server does not,
> but what are the implications of this? Could someone explain how SQL's
> architecture differs from IB's with respect to this?
The multi-generation stuff means that when you start reading, you get a "personal snapshot" of the
data. You work with that over time, and other modifying the real data will not block you (but you
have the be extremely careful if you then have to do modifications based on this old data). SQL
Server doesn't have this, so you have blocking. If you try to read something that someone else has
modified but not committed, you will be blocked until the modifier ends the transaction. So, in SQL
Server, you want to use short transactions.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Mike Tremendous" <noone@.hotmail.com> wrote in message news:ONxqCMmrDHA.2432@.TK2MSFTNGP10.phx.gbl...
> Hi.
> We've been having many problems with our current Interbase database
> (v6.0.2.0). These are mainly corruption issues with the constant need to
> backup and restore, and the fact that it is almost impossible to reliably
> change metadata in a live environment.
> We need to change servers, and our two options are either upgrading to
> Interbase 7.1, or migrating to SQL Server 2000.
> These are our requirements:
> - the ability to reliaby and efficiently cope with huge amounts of data
> - the server must be able to cope seemlessly with clients not disconnecting
> cleanly and transactions left open (IB seems to have trouble with this), or
> at least provide some mechanism to view such transactions and kill them
> - the ability to change metada in a live environment with many connected
> users (create/drop tables, foreign keys, etc - is this possible with any DB
> at all?)
> - it is critical that the database is up 24/7 - we can afford no downtime -
> is this something SQL Server can provide?
> I have also read a lot about how Interbase is unique in that it provides a
> multi-generational architecture - presumably this means SQL Server does not,
> but what are the implications of this? Could someone explain how SQL's
> architecture differs from IB's with respect to this?
> Has anyone else had any experience (good or bad) migrating from IB to SQL?
> Perhaps our problems would not be solved by this and therefore it would be a
> waste of time and money?
> We basically need as much info on the subject as possible so we can make an
> informed decision.
> Mike
>

Migrating from Excel to sql-server

Hello,
I have got the job to migrate data from an Excel-sheet to a
sql-server-database. I have a lot of experience with Access but
Sql-Server is not in my line.
So who can give me some advice about how to do this job?

thanx for your help, HelmutEasiest way is to use the Data Transformation Services (DTS) of SQL Server.
It has a nice UI.

Start > Programs > Microsoft SQL Server > Import and Export Data

HTH,
Dave

"Helmut Blass" <helmut_blass@.tweb.de> wrote in message
news:bplp9f$olf$07$1@.news.t-online.com...
> Hello,
> I have got the job to migrate data from an Excel-sheet to a
> sql-server-database. I have a lot of experience with Access but
> Sql-Server is not in my line.
> So who can give me some advice about how to do this job?
> thanx for your help, Helmut

Migrating from Enterprise to SQL Server 2000 Standard

We are migrating a browser based application developed
with the Enterprise version to a server running the
standard edition. There will be some development on the
standard version before a new vresion of the application
is deployed. Can anyone help me to understand what
issues/problems we should look out for?Since most of the difference between Standard Edition and Enterprise Edition
involves scalability and availability features (>4 processors, large memory
support, clusters) there generally will be no problems migrating
applications from Enterprise to Standard. The few small gotchas include
Transparent Materialized Views and Distributed Partitioned Views, both of
which are only included in Enterprise Edition. (For completeness, Analysis
Services has some similar differences between Standard and Enterprise). If
you have not used these few scalability-related features then you will not
notice application level differences between these editions.
See "Features Supported by the Editions of SQL Server 2000" in Books Online.
--
Hal Berenson, SQL Server MVP
True Mountain Group LLC
"rez" <rezam@.go.com> wrote in message
news:039601c37bc5$bda02a30$a401280a@.phx.gbl...
> We are migrating a browser based application developed
> with the Enterprise version to a server running the
> standard edition. There will be some development on the
> standard version before a new vresion of the application
> is deployed. Can anyone help me to understand what
> issues/problems we should look out for?

Migrating From Development to Production Environment in MSAS 2005

Hi,

I have built a project in Ananlysis Services 2005 and would like to migrate the contents of the Project ( Dimensions, Measures, Cubes Etc) to a New server having analysis Services 2005 , what is the best approach. Does microsoft provide any best practices document. I searched the forum and the topics on migration seem to be from Analysis Services 2000 to Analysis Services 2005, can some one guide me on migration between Analysis Services 2005 and 2005.

Thansks

A backup and restore (like in AS2000) is certainly possible. This is the only method if you need to get the data cube moved in a processed state (with data). Except you could use the new synchronization feature, but that would probably be using this feature for something it was not quite intended.

The recommended method would be using the new AS Deployment Wizard. Make a "build" in BI Development Studio of your database and fire up the AS Deployment Wizard. This will allow you to specify different settings for your cube database when it is deployed to the production server. For more info on how to use the Deployment Wizard, see http://msdn2.microsoft.com/en-US/library/ms176121.aspx

Migrating from Developer Edition to Standard Edition

Hello,
I have Reporting Sevices Developer Edition running on a Windows XP Pro
workstation. I want to migrate it to Reporting Services/SQL Server Standard
Edition on a Windows 2003 server. Can this be done? Since the edition of RS
changes, and the edition of SQL Server changes, and the hardware changes, I
need to know if this is feasible, or if it would be better to redeploy the
reports to the new server. BOL isn't very helpful in this area, and I don't
have time to learn C# to use the RS utility.
Thanks in advance.Given that it is so easy to redeploy and the fact that everything is
changing, my advice is to install new and then redeploy.
Bruce L-C
"Cable Guy" <Cable Guy@.discussions.microsoft.com> wrote in message
news:DCD83C56-B311-45EB-90A6-1A5F45B5A87B@.microsoft.com...
> Hello,
> I have Reporting Sevices Developer Edition running on a Windows XP Pro
> workstation. I want to migrate it to Reporting Services/SQL Server
Standard
> Edition on a Windows 2003 server. Can this be done? Since the edition of
RS
> changes, and the edition of SQL Server changes, and the hardware changes,
I
> need to know if this is feasible, or if it would be better to redeploy the
> reports to the new server. BOL isn't very helpful in this area, and I
don't
> have time to learn C# to use the RS utility.
> Thanks in advance.|||Others would need to jump in for your answer. I can't really help you with
that.
Bruce L-C
"Cable Guy" <CableGuy@.discussions.microsoft.com> wrote in message
news:40C1D674-60C1-49AB-B8B6-5483EE8BDE2F@.microsoft.com...
> Hi Bruce,
> Thanks for the reply. That is what I was initially persuaded to do, but
> redeploying introduces the possibility of something being overlooked, and
> that is what we want to avoid. Is it possible to use the RS utility to do
> what we need it to do (i.e. copy the logical contents of the database to a
> new database/server/edition) and have it work properly? Do you know of
> any
> good documentation on the RS utility?
> "Bruce Loehle-Conger" wrote:
>> Given that it is so easy to redeploy and the fact that everything is
>> changing, my advice is to install new and then redeploy.
>> Bruce L-C
>> "Cable Guy" <Cable Guy@.discussions.microsoft.com> wrote in message
>> news:DCD83C56-B311-45EB-90A6-1A5F45B5A87B@.microsoft.com...
>> > Hello,
>> >
>> > I have Reporting Sevices Developer Edition running on a Windows XP Pro
>> > workstation. I want to migrate it to Reporting Services/SQL Server
>> Standard
>> > Edition on a Windows 2003 server. Can this be done? Since the edition
>> > of
>> RS
>> > changes, and the edition of SQL Server changes, and the hardware
>> > changes,
>> I
>> > need to know if this is feasible, or if it would be better to redeploy
>> > the
>> > reports to the new server. BOL isn't very helpful in this area, and I
>> don't
>> > have time to learn C# to use the RS utility.
>> >
>> > Thanks in advance.
>>|||Hi Bruce,
Thanks for the reply. That is what I was initially persuaded to do, but
redeploying introduces the possibility of something being overlooked, and
that is what we want to avoid. Is it possible to use the RS utility to do
what we need it to do (i.e. copy the logical contents of the database to a
new database/server/edition) and have it work properly? Do you know of any
good documentation on the RS utility?
"Bruce Loehle-Conger" wrote:
> Given that it is so easy to redeploy and the fact that everything is
> changing, my advice is to install new and then redeploy.
> Bruce L-C
> "Cable Guy" <Cable Guy@.discussions.microsoft.com> wrote in message
> news:DCD83C56-B311-45EB-90A6-1A5F45B5A87B@.microsoft.com...
> > Hello,
> >
> > I have Reporting Sevices Developer Edition running on a Windows XP Pro
> > workstation. I want to migrate it to Reporting Services/SQL Server
> Standard
> > Edition on a Windows 2003 server. Can this be done? Since the edition of
> RS
> > changes, and the edition of SQL Server changes, and the hardware changes,
> I
> > need to know if this is feasible, or if it would be better to redeploy the
> > reports to the new server. BOL isn't very helpful in this area, and I
> don't
> > have time to learn C# to use the RS utility.
> >
> > Thanks in advance.
>
>sql

Migrating from Crystal to SSRS questions

Hi all,
Our company is considering converting all our reports from Crystal to SSRS.
We have done a fair bit of research. I have a number of questions that I
can't find answers to. If anyone with experience using the software could
help me out with some answers, I'd be most grateful.
1. Can you save data with a report? or do you have to connect to data every
time you run a report?
2. I've heard when scrolling a matrix report that the row heading overlaps
values when scrolling and looks very messy. Is this true and is there a way
to stop it happening as a lot of our reports are Matrix based?
3. Is there an equivalent to Crystal's Group Explorer, for just viewing a
certain group?
4. With regards date formatting we have heard it defaults to US date. Is it
possible to permanently overide this or do you have to do it manually for
each report?
Many thanks
JHJH,
I'm having some difficulty getting this feature to work accross the
entire row header. But, what I've found out so far is RS2005 does
offer the feature, but it's not very intuitive to use. The only way
I've found it to work (somewhat) is
1. Add a group to your Matrix/Table.
2. Edit the group by right-clicking the group text-box.
3. Check the box at the bottom of the General tab for "Group header
should remain visible while scrolling".
I haven't figured out yet how to select the entire row (which in my
case contains about 15 measures) and set the row to freeze like in
excel. I even tried to add subsequent groups as separate
measures...can't figure that one out either.
Corey
JH wrote:
> Hi all,
> Our company is considering converting all our reports from Crystal to SSRS.
> We have done a fair bit of research. I have a number of questions that I
> can't find answers to. If anyone with experience using the software could
> help me out with some answers, I'd be most grateful.
> 1. Can you save data with a report? or do you have to connect to data every
> time you run a report?
> 2. I've heard when scrolling a matrix report that the row heading overlaps
> values when scrolling and looks very messy. Is this true and is there a way
> to stop it happening as a lot of our reports are Matrix based?
> 3. Is there an equivalent to Crystal's Group Explorer, for just viewing a
> certain group?
> 4. With regards date formatting we have heard it defaults to US date. Is it
> possible to permanently overide this or do you have to do it manually for
> each report?
> Many thanks
> JH

Migrating from Crystal Reports Charts using TopN and "Other" to Reporting Services

I am currently migrating several Crystal Reports that have charts. The chart series is set to use the TopN values in a count, and combine the rest of the series into one series titled "Other". I see how to filter the chart using TopN, however I need to still show the rest of the data grouped as "Other". I think the solution I need is to create a group expression that uses some sort of IIF function to create the "Other" group, but I haven't found a function that I can use to get the results I need. Any ideas?

Scott

I found the answer to my question in another forum.

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

I used option 1.

Migrating from Crystal Reports Charts using TopN and "Other" to Reporting Services

Scott

I found the answer to my question in another forum.

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

I used option 1.

migrating from crystal report 8.5 to 10.0 using vb .

I am working in migrating my cr application to 10th ver from the existing 8.5 where we have to explicitly make a query in query builder and then attach it. I was using dsn in ms odbc for oracle driver to connect the DB. Now in 10.0 i have removed the query file created using 8.5 query builder and trying to write directly to command editor. the report as stand alone is working fine. but when i try to generate the pdf using my vb application then on .export option it is giving error as "Logon failed.
Details: 01S00:[Microsoft][ODBC driver for Oracle]Invalid connection string attribute". i think the problem might be with cddb_oracle.dll or crdb_oracle.dll file as i am using a evaluation version of crystal report 10. as input to dsn i am providing
1.dsn
2.usid
3.database
4.password
what else is missing . plz help....The next is code I use in VB6 to display in CRViewer10 a CR10 report that read data thru ODBC

Set CRReport = CRApplication.OpenReport("Report.rpt", 1)
For MEnt2 = 1 To CRReport.Database.Tables.Count
Set CRTabla = CRReport.Database.Tables(MEnt2)
Set CRConProp = CRTabla.ConnectionProperties("Provider")
CRConProp.value = "MSDASQL"
Set CRConProp = CRTabla.ConnectionProperties("Data Source")
CRConProp.value = "DSNname"
Set CRConProp = CRTabla.ConnectionProperties("Initial Catalog")
CRConProp.value = "Datapath"
Set CRConProp = CRTabla.ConnectionProperties("User ID")
CRConProp.value = ""
Set CRConProp = CRTabla.ConnectionProperties("Locale Identifier")
CRConProp.value = "2058"
Set CRConProp = CRTabla.ConnectionProperties("OLE DB Services")
CRConProp.value = "-5"
Next MEnt2

frmReport.Show vbModal

Migrating From CR 9 to VS Reporting Services

Hi,
I'm not that familiar with VS RS and I have to convert reports from Crystal
Report 9 to VS RS.
Is it possible ? If not, is there any equivalent of the "unbound fields"
from CR9 in VS RS ?
Thanks in Advance.
wadjaHello Wadja,
I apologize for contacting you on an unsolicited basis, but I read a
few of your postings on Google's Crystal Developers users group and
hoped that you'd be willing to help me.
I'm a journalist with Application Development Trends magazine (ADT,
http://www.adtmag.com), and I'm working on a story on reporting tools
from a developer's perspective. As you no doubt know, Crystal has
long been pretty much the only game in town in this respect, as the
former Crystal Decisions seeded the market with this tool (it's shipped
with Visual Studio for more than a decade, for example, and it ships
with a host of other applications and tools, too).
Now, however, developers have a choice of which reporting tool to use
when they need to incorporate reporting functionality into their apps.
Microsoft announced its SQL Server 2000 Reporting Services last year,
and there's even an open source alternative called BIRT (that's short
for BI Reporting Tool). I'd like to take a look at the positives and
negatives of each of these solutions -- Crystal still seems hard to
beat for straight-up reporting, but both Reporting Services and BIRT
promise better (that is, cheaper) report lifecycle management
capabilities, which could make them better choices for many BI-related
reporting applications.
Would you mind having a go at a few of the questions I've appended
below? I'd love to hear your thoughts on this issue. If your employer
prohibits you from speaking on the record to journalists, I don't
have a mind abstracting its identity (e.g., "a major pharmaceutical
company based in the Northeast," or "a large financial services
firm based in the American South"). So don't let that stop you.
Thanks very much for your time. I appreciate it, and I apologize, once
again, for contacting you on an unsolicited basis.
Best,
Steve
1. Is your organization invested in Crystal, or have you transitioned
over to Reporting Services? If the former, for what purposes or in
support of which applications do you use Crystal? (I'm trying to get
a sense for whether you use Crystal mostly as a default reporting tool
for app dev purposes, whether it's tied to the use of any specific
application, or whether you're using it mostly as a reporting
front-end for data warehousing and BI.)
2. Did your organization opt for Crystal as a matter of choice -
e.g., you evaluated CR in addition to a host of other tools - or did
it go with Crystal largely because it was bundled with your default
development environment, or core applications that you use?
3. In comparison with Reporting Services and other reporting solutions,
what features about Crystal do you most like? What features about it
would you most like to change? How well do you feel Crystal
addresses/addressed your core use requirements?
4. Re: Reporting Services, are you currently using it? If not, have you
looked into using it? What features, if any, do you most like? What
features, if any, are deal-breakers for you? How do you feel that
Reporting Services compares with Crystal and other established
reporting solutions?
5. Do you have any interest in an open source reporting tool based on
the Eclipse development framework? If so, have you looked at the BIRT
project?|||Hello Steve,
As I said before, I'm new with reporting tools and with my company.
Therefore I can hardly make objective answers to you.
All I can tell is that they (developers before I step in) used Crystal
Report (CR) to test it. Now, we currently use Reporting Services(RS). The
reasons that made us change :
- We don't have to invoice our clients for using CR, it is included in
Microsoft Licences (we have a partnership with this company).
- Several developers complained about the technical support given with CR.
They hardly find professional help they needed.
- I was said designing on RS was much more easier than on CR.
For the moment, we are not interested in other report tools, as RS meets our
satisfaction.
I hope I gave any relevant information for you.
Best,
wadja
"daedalus" <stephen.swoyer@.gmail.com> a écrit dans le message de news:
1117634907.794866.228080@.f14g2000cwb.googlegroups.com...
> Hello Wadja,
> I apologize for contacting you on an unsolicited basis, but I read a
> few of your postings on Google's Crystal Developers users group and
> hoped that you'd be willing to help me.
> I'm a journalist with Application Development Trends magazine (ADT,
> http://www.adtmag.com), and I'm working on a story on reporting tools
> from a developer's perspective. As you no doubt know, Crystal has
> long been pretty much the only game in town in this respect, as the
> former Crystal Decisions seeded the market with this tool (it's shipped
> with Visual Studio for more than a decade, for example, and it ships
> with a host of other applications and tools, too).
> Now, however, developers have a choice of which reporting tool to use
> when they need to incorporate reporting functionality into their apps.
> Microsoft announced its SQL Server 2000 Reporting Services last year,
> and there's even an open source alternative called BIRT (that's short
> for BI Reporting Tool). I'd like to take a look at the positives and
> negatives of each of these solutions -- Crystal still seems hard to
> beat for straight-up reporting, but both Reporting Services and BIRT
> promise better (that is, cheaper) report lifecycle management
> capabilities, which could make them better choices for many BI-related
> reporting applications.
> Would you mind having a go at a few of the questions I've appended
> below? I'd love to hear your thoughts on this issue. If your employer
> prohibits you from speaking on the record to journalists, I don't
> have a mind abstracting its identity (e.g., "a major pharmaceutical
> company based in the Northeast," or "a large financial services
> firm based in the American South"). So don't let that stop you.
> Thanks very much for your time. I appreciate it, and I apologize, once
> again, for contacting you on an unsolicited basis.
> Best,
> Steve
> 1. Is your organization invested in Crystal, or have you transitioned
> over to Reporting Services? If the former, for what purposes or in
> support of which applications do you use Crystal? (I'm trying to get
> a sense for whether you use Crystal mostly as a default reporting tool
> for app dev purposes, whether it's tied to the use of any specific
> application, or whether you're using it mostly as a reporting
> front-end for data warehousing and BI.)
> 2. Did your organization opt for Crystal as a matter of choice -
> e.g., you evaluated CR in addition to a host of other tools - or did
> it go with Crystal largely because it was bundled with your default
> development environment, or core applications that you use?
> 3. In comparison with Reporting Services and other reporting solutions,
> what features about Crystal do you most like? What features about it
> would you most like to change? How well do you feel Crystal
> addresses/addressed your core use requirements?
> 4. Re: Reporting Services, are you currently using it? If not, have you
> looked into using it? What features, if any, do you most like? What
> features, if any, are deal-breakers for you? How do you feel that
> Reporting Services compares with Crystal and other established
> reporting solutions?
> 5. Do you have any interest in an open source reporting tool based on
> the Eclipse development framework? If so, have you looked at the BIRT
> project?
>sql

Migrating from ASP

I have a bunch of contracts built in asp and I need them to be

published on report server. Is there any easier way than rebuilding

them in report designer?

There is not migration path from ASP to Reporting Services reports. You can store the asp contracts in reporting services as resources, but they will not be executed as reports.

Migrating from Acess 2003 to Sql server - Function Val()

Hi folks

I do a lot of work with address data. I have been using Acess 2003 as my database but now need to upgrade to SQL Server.

My queries have been making extensive use of the Val function to order my queries on address columns. A typical address column might be:

12 The Avenue .......

by using Order By Val(AddressCol) I can sort these rows in ascending house number.

I can't find anything in SQL Server that will acomplish this. Does anybody have any ideas.

Thanks

Paul

why do you need to reference the val() function? is there something in your column that you want to exclude?

Does: "order by AddressCol" in TSQL not accomplish what you want?

Migrating from Access, boolean field problem

Hallo,
I am migrating complex database from Access 2000 to MSSQL server 8. This
database is used by web applications. Some time I need to be able swicth
between the old Access version and new SQL server driven one. I have
different connection strings, and connecting works well.
However, some sql query incompatibility has appeared...
Number of Access tables have boolean (Yes/No) fields. In Access, I could run
a query like this:
SELECT id FROM myTable WHERE myBooleanField
it returns all records where myBooleanField value is true. Now, when I
migrated to MSSQL, these boolean fields are of "bit" type, and carry "1" for
every Access "true" value, and "0" for "false" values. With this new field
type, I can no longer run the above mentioned query. I must change it to
this:
SELECT id FROM myTable WHERE myBooleanField=1
Now, this would not be a crazy problem, however I need to be able to run it
for some time on both database solutions, Access and MSSQL, so I really need
to have compatible queries.
One solution I see now would be converting the field type in access from
boolean to byte, and then run the "=1" queries. But that could involve range
of incompatibilities within the application itself, that assumes that these
fields are "true" or "false" instead of "1" and "0". Browsing through the
application to find these places is not really the best thing I can imagine.
So, maybe there is a real "boolean" field in MSSQL? Maybe other approaches
to solve my problem?
Thanks,
PavilsOne issue is that Access/Jet booleans are not true booleans, but can
also contain null values. Change the Jet columns to Not Null and
provide a default value (same with SQLS). This lets you avoid the
three-valued logic trap. In your SQL statements and code, perform all
comparisons to 0, not to -1 or 1 or True or False. 0 is always false,
everywhere, so both engines are going to interpret "<>0" the same way.
-- Mary
MCW Technologies
http://www.mcwtech.com
On Thu, 19 Feb 2004 16:25:28 +0200, "Pavils Jurjans"
<pavils@.mailbox.riga.lv> wrote:
>Hallo,
>I am migrating complex database from Access 2000 to MSSQL server 8. This
>database is used by web applications. Some time I need to be able swicth
>between the old Access version and new SQL server driven one. I have
>different connection strings, and connecting works well.
>However, some sql query incompatibility has appeared...
>Number of Access tables have boolean (Yes/No) fields. In Access, I could run
>a query like this:
>SELECT id FROM myTable WHERE myBooleanField
>it returns all records where myBooleanField value is true. Now, when I
>migrated to MSSQL, these boolean fields are of "bit" type, and carry "1" for
>every Access "true" value, and "0" for "false" values. With this new field
>type, I can no longer run the above mentioned query. I must change it to
>this:
>SELECT id FROM myTable WHERE myBooleanField=1
>Now, this would not be a crazy problem, however I need to be able to run it
>for some time on both database solutions, Access and MSSQL, so I really need
>to have compatible queries.
>One solution I see now would be converting the field type in access from
>boolean to byte, and then run the "=1" queries. But that could involve range
>of incompatibilities within the application itself, that assumes that these
>fields are "true" or "false" instead of "1" and "0". Browsing through the
>application to find these places is not really the best thing I can imagine.
>So, maybe there is a real "boolean" field in MSSQL? Maybe other approaches
>to solve my problem?
>Thanks,
>Pavils
>|||Thanks Mary,
This is a good suggestion, it helps me to move further...
Now another problem has arised:
Access runs this query without complaints:
UPDATE myTable SET myBool = NOT myBool WHERE id=100
What could be a syntax working on both Jet and MSSQL engines?
I tried this one...
UPDATE myTable SET myBool = IIf(myBool, 0, 1) WHERE id=100
but I get error -
'IIf' is not a recognized function name.
Maybe some other smart syntax?
Thanks,
-- Pavils
"Mary Chipman" <mchip@.nomail.please> wrote in message
news:rij9301d0sjh4b3ghkva5vmjh3b3pn7cpp@.4ax.com...
> One issue is that Access/Jet booleans are not true booleans, but can
> also contain null values. Change the Jet columns to Not Null and
> provide a default value (same with SQLS). This lets you avoid the
> three-valued logic trap. In your SQL statements and code, perform all
> comparisons to 0, not to -1 or 1 or True or False. 0 is always false,
> everywhere, so both engines are going to interpret "<>0" the same way.
> -- Mary
> MCW Technologies
> http://www.mcwtech.com
> On Thu, 19 Feb 2004 16:25:28 +0200, "Pavils Jurjans"
> <pavils@.mailbox.riga.lv> wrote:
> >Hallo,
> >
> >I am migrating complex database from Access 2000 to MSSQL server 8. This
> >database is used by web applications. Some time I need to be able swicth
> >between the old Access version and new SQL server driven one. I have
> >different connection strings, and connecting works well.
> >
> >However, some sql query incompatibility has appeared...
> >
> >Number of Access tables have boolean (Yes/No) fields. In Access, I could
run
> >a query like this:
> >
> >SELECT id FROM myTable WHERE myBooleanField
> >
> >it returns all records where myBooleanField value is true. Now, when I
> >migrated to MSSQL, these boolean fields are of "bit" type, and carry "1"
for
> >every Access "true" value, and "0" for "false" values. With this new
field
> >type, I can no longer run the above mentioned query. I must change it to
> >this:
> >
> >SELECT id FROM myTable WHERE myBooleanField=1
> >
> >Now, this would not be a crazy problem, however I need to be able to run
it
> >for some time on both database solutions, Access and MSSQL, so I really
need
> >to have compatible queries.
> >
> >One solution I see now would be converting the field type in access from
> >boolean to byte, and then run the "=1" queries. But that could involve
range
> >of incompatibilities within the application itself, that assumes that
these
> >fields are "true" or "false" instead of "1" and "0". Browsing through the
> >application to find these places is not really the best thing I can
imagine.
> >
> >So, maybe there is a real "boolean" field in MSSQL? Maybe other
approaches
> >to solve my problem?
> >
> >Thanks,
> >
> >Pavils
> >
>|||One thing that's going to continue to trip you up in writing your
queries is not understanding the fundamental differences between
Access SQL and T-SQL, the two main points of which are:
-- Access SQL relies heavily on the expression service and VBA to
compensate for the fact that it has no programming language features
on its own. You can execute VBA functions like Iif inside of a query
only against the Jet engine with Access as a FE. .
-- T-SQL contains many programming language features, like variables.
It does not rely on an external programming language to provide this
functionality the way Access does. When you try VBA syntax, it fails
because SQLS has no knowledge of VBA.
For the most part, you'll want to stay away from language elements
that are specific to either Jet SQL or T-SQL. I'd start by writing the
queries using the Query Analyzer, then copying/pasting them into an
Access query window to see if the syntax works in both. SQL Books
Online is a good reference--Access SQL documentation is practically
non-existant. Besides the language elements, be aware that you're also
going to have problems with delimiters when working with datetime
values -- Jet uses # and SQLS uses '.
-- Mary
MCW Technologies
http://www.mcwtech.com
On Thu, 19 Feb 2004 18:09:50 +0200, "Pavils Jurjans"
<pavils@.mailbox.riga.lv> wrote:
>Thanks Mary,
>This is a good suggestion, it helps me to move further...
>Now another problem has arised:
>Access runs this query without complaints:
>UPDATE myTable SET myBool = NOT myBool WHERE id=100
>What could be a syntax working on both Jet and MSSQL engines?
>I tried this one...
>UPDATE myTable SET myBool = IIf(myBool, 0, 1) WHERE id=100
>but I get error -
>'IIf' is not a recognized function name.
>Maybe some other smart syntax?
>Thanks,
>-- Pavils
>"Mary Chipman" <mchip@.nomail.please> wrote in message
>news:rij9301d0sjh4b3ghkva5vmjh3b3pn7cpp@.4ax.com...
>> One issue is that Access/Jet booleans are not true booleans, but can
>> also contain null values. Change the Jet columns to Not Null and
>> provide a default value (same with SQLS). This lets you avoid the
>> three-valued logic trap. In your SQL statements and code, perform all
>> comparisons to 0, not to -1 or 1 or True or False. 0 is always false,
>> everywhere, so both engines are going to interpret "<>0" the same way.
>> -- Mary
>> MCW Technologies
>> http://www.mcwtech.com
>> On Thu, 19 Feb 2004 16:25:28 +0200, "Pavils Jurjans"
>> <pavils@.mailbox.riga.lv> wrote:
>> >Hallo,
>> >
>> >I am migrating complex database from Access 2000 to MSSQL server 8. This
>> >database is used by web applications. Some time I need to be able swicth
>> >between the old Access version and new SQL server driven one. I have
>> >different connection strings, and connecting works well.
>> >
>> >However, some sql query incompatibility has appeared...
>> >
>> >Number of Access tables have boolean (Yes/No) fields. In Access, I could
>run
>> >a query like this:
>> >
>> >SELECT id FROM myTable WHERE myBooleanField
>> >
>> >it returns all records where myBooleanField value is true. Now, when I
>> >migrated to MSSQL, these boolean fields are of "bit" type, and carry "1"
>for
>> >every Access "true" value, and "0" for "false" values. With this new
>field
>> >type, I can no longer run the above mentioned query. I must change it to
>> >this:
>> >
>> >SELECT id FROM myTable WHERE myBooleanField=1
>> >
>> >Now, this would not be a crazy problem, however I need to be able to run
>it
>> >for some time on both database solutions, Access and MSSQL, so I really
>need
>> >to have compatible queries.
>> >
>> >One solution I see now would be converting the field type in access from
>> >boolean to byte, and then run the "=1" queries. But that could involve
>range
>> >of incompatibilities within the application itself, that assumes that
>these
>> >fields are "true" or "false" instead of "1" and "0". Browsing through the
>> >application to find these places is not really the best thing I can
>imagine.
>> >
>> >So, maybe there is a real "boolean" field in MSSQL? Maybe other
>approaches
>> >to solve my problem?
>> >
>> >Thanks,
>> >
>> >Pavils
>> >
>|||Thanks, Mary for your help
I've been doing databasing in Access, mySQL, and some generic work in
interBase, MSSQL, and Oracle... but in this case I have to port fairly
complex application to MSSQL and I really see all those blanks I miss in the
knowledge about MSSQL. Well, but that's the nature of learning curve, being
very steep :) I'd be happy if this project would be originally developed in
MSSQL, but unfortunately this is not the case.
On my previous query, where I was looking for both Jet and MSSQL engine
compatible syntax for NOT-ting the boolean value, I solved it like this:
UPDATE myTable SET myBool = 1-ABS(myBool) WHERE id=123
That works on both Access and MSSQL.
The date delimiter thing I fortunately have abstracted away in my database
library, so it's easily fixable thing. Some issues about text/BLOB fields
are luring in now, but nothing very crucial though..
Thanks,
Pavils Jurjans
"Mary Chipman" <mchip@.nomail.please> wrote in message
news:7roe30topfasals1m2avf2u2fj3dqol9dn@.4ax.com...
> One thing that's going to continue to trip you up in writing your
> queries is not understanding the fundamental differences between
> Access SQL and T-SQL, the two main points of which are:
> -- Access SQL relies heavily on the expression service and VBA to
> compensate for the fact that it has no programming language features
> on its own. You can execute VBA functions like Iif inside of a query
> only against the Jet engine with Access as a FE. .
> -- T-SQL contains many programming language features, like variables.
> It does not rely on an external programming language to provide this
> functionality the way Access does. When you try VBA syntax, it fails
> because SQLS has no knowledge of VBA.
> For the most part, you'll want to stay away from language elements
> that are specific to either Jet SQL or T-SQL. I'd start by writing the
> queries using the Query Analyzer, then copying/pasting them into an
> Access query window to see if the syntax works in both. SQL Books
> Online is a good reference--Access SQL documentation is practically
> non-existant. Besides the language elements, be aware that you're also
> going to have problems with delimiters when working with datetime
> values -- Jet uses # and SQLS uses '.
> -- Mary
> MCW Technologies
> http://www.mcwtech.com

Migrating from Access, boolean field problem

I'd be happy if this project would be originally developed in
MSSQL, but unfortunately this is not the case.
On my previous query, where I was looking for both Jet and MSSQL engine
compatible syntax for NOT-ting the boolean value, I solved it like this:
UPDATE myTable SET myBool = 1-ABS(myBool) WHERE id=123
That works on both Access and MSSQL.
The date delimiter thing I fortunately have abstracted away in my database
library, so it's easily fixable thing. Some issues about text/BLOB fields
are luring in now, but nothing very crucial though..
Thanks,
Pavils Jurjans
"Mary Chipman" <mchip@.nomail.please> wrote in message
news:7roe30topfasals1m2avf2u2fj3dqol9dn@.
4ax.com...
> One thing that's going to continue to trip you up in writing your
> queries is not understanding the fundamental differences between
> Access SQL and T-SQL, the two main points of which are:
> -- Access SQL relies heavily on the expression service and VBA to
> compensate for the fact that it has no programming language features
> on its own. You can execute VBA functions like Iif inside of a query
> only against the Jet engine with Access as a FE. .
> -- T-SQL contains many programming language features, like variables.
> It does not rely on an external programming language to provide this
> functionality the way Access does. When you try VBA syntax, it fails
> because SQLS has no knowledge of VBA.
> For the most part, you'll want to stay away from language elements
> that are specific to either Jet SQL or T-SQL. I'd start by writing the
> queries using the Query Analyzer, then copying/pasting them into an
> Access query window to see if the syntax works in both. SQL Books
> Online is a good reference--Access SQL documentation is practically
> non-existant. Besides the language elements, be aware that you're also
> going to have problems with delimiters when working with datetime
> values -- Jet uses # and SQLS uses '.
> -- Mary
> MCW Technologies
> http://www.mcwtech.com|||Pavil,
I've been in this situation many times. As has been mentioned already, Acces
s'
YES/NO field is really a three-state field: YES/NO/NULL, where in SQL SERVER
the BIT type is 2-state.
The other problem is that Access treats TRUE as -1 where SQL Serrver actuall
y
treats TRUE as any non-zero value.
Armed with that information, you have two choices:
1) Use INT field in SQL Server, and you'll see good ol' -1 and 0 in that tab
le
anytime Access gets its ODBC/JET mitts on it.
2) When creating WHERE clauses in Access, get in the habit of ALWAYS using t
he
following test for TRUE:
Fld <> 0
Do that in VBA too, just to be consistent.

Migrating from Access to SQL Server with a web front end

Afternoon all,

Apologies for cross-posting but as my query covers both Access and SQL
Server I thought I'd send it both!

I have inherited a project to migrate a fairly complex series of
Access databases into a single proper SQL database with a web front
end.

Its quite a nasty job as people are working on a variety of data sets
at several Universities around the world and the data has got very
messy; hence the requirement to put it all on one live web enabled
database server and provide a web-based front end (particularly as
some users insist on using Macs so can't run Access as a front end
anyway).

If anyone could give me hints on how to perform such a migration or if
anyone knows of any good books or other documents on this I'd be
grateful for assistance.

Many thanks

Rich May
Museum of LondonFirst off, congratulations - this is a great move to make.

As far as converting the tables, use DTS (Data Transformation Services) in
sql server. It is pretty much wizard driven and works really well. As for
the queries, you would do best to forget you even have them in Access and
rewrite everything using Stored Procedures. My favorite book for "stepping"
between access and sql server is "Microsoft Access Developer's Guide to SQL
Server" by Chipman and Baron. It's wonderful.

As for your web front end... that is where the headache starts. You can
pretty much choose two paths if you want to stay on the Microsoft path. You
can do it using ASP or ASP.NET. Both will run on IIS (.net needs framework
installed).

If you are serving to Macs, you may deliver your app better using ASP
because you will be able to better control the rendered output (very good
for Netscape). I use Visual Basic to build "ActiveX DLL" components to
alleviate the complexity of the ASP pages so you would want to be proficient
in Visual Basic and instantiating COM components. Next, you need to take
ADO (ActiveX Data Objects) head on and learn it well, that is what you will
use to connect to sql, execute queries, and work with data.

If you are using IE on all clients and have control over them, you will find
that Visual Studio.NET does a great job at deploying web applications in
ease and speed, once you pass the high learning curve. I VERY highly
recommend "Programming Microsoft Visual Basic .Net" by Francesco Balena
(1600 pages).

If it all sounds complex, don't get me wrong... it is. If you need help,
let me know, we do this for a living.

--
Jerry Boone
Analytical Technologies, Inc.
http://www.antech.biz

"Rich May" <rmay@.museumoflondon.org.uk> wrote in message
news:e12c70e0.0311140943.722ed0ba@.posting.google.c om...
> Afternoon all,
> Apologies for cross-posting but as my query covers both Access and SQL
> Server I thought I'd send it both!
> I have inherited a project to migrate a fairly complex series of
> Access databases into a single proper SQL database with a web front
> end.
> Its quite a nasty job as people are working on a variety of data sets
> at several Universities around the world and the data has got very
> messy; hence the requirement to put it all on one live web enabled
> database server and provide a web-based front end (particularly as
> some users insist on using Macs so can't run Access as a front end
> anyway).
> If anyone could give me hints on how to perform such a migration or if
> anyone knows of any good books or other documents on this I'd be
> grateful for assistance.
> Many thanks
> Rich May
> Museum of London|||several choices, depending on what you're comfy with ..
1) sql server, IIS & ASP
2) mySQL, apache, php

here's a RAD tool that I found the other day to create web pages for
either option (I haven't tried it yet)
http://www.citybusinesslogic.com/apwab.php

and somewhere I've got vba code to convert ms-access tables to mysql
format

rmay@.museumoflondon.org.uk (Rich May) wrote in message news:<e12c70e0.0311140943.722ed0ba@.posting.google.com>...
> Afternoon all,
> Apologies for cross-posting but as my query covers both Access and SQL
> Server I thought I'd send it both!
> I have inherited a project to migrate a fairly complex series of
> Access databases into a single proper SQL database with a web front
> end.
> Its quite a nasty job as people are working on a variety of data sets
> at several Universities around the world and the data has got very
> messy; hence the requirement to put it all on one live web enabled
> database server and provide a web-based front end (particularly as
> some users insist on using Macs so can't run Access as a front end
> anyway).
> If anyone could give me hints on how to perform such a migration or if
> anyone knows of any good books or other documents on this I'd be
> grateful for assistance.
> Many thanks
> Rich May
> Museum of London|||Hi Rich,

I think there are 3 options. a) Access' Upsizing Migration Wizard
b)Sql Server's DTS and c) setting Access as a linked server and
programmatically importing the data. I believe the main problem
you'll run into is that Access' datatypes are not 100% compatible with
Sql server's. - Louis|||louisducnguyen@.hotmail.com (louis nguyen) wrote in message news:<b0e9d53.0311142220.41dff853@.posting.google.com>...
> Hi Rich,
> I think there are 3 options. a) Access' Upsizing Migration Wizard
> b)Sql Server's DTS and c) setting Access as a linked server and
> programmatically importing the data. I believe the main problem
> you'll run into is that Access' datatypes are not 100% compatible with
> Sql server's. - Louis

Another problem I have seen with the upsize wizard is it makes all
columns with the type nvarchar i.e. unicode which doubles the space
requirements.

Duncan|||"duncan" wrote

> Another problem I have seen with
> the upsize wizard is it makes all
> columns with the type nvarchar i.e.
> unicode which doubles the space
> requirements.

First, I don't think that is true -- all _text_ columns, perhaps, but
certainly not numeric and date/time columns.

Secondly, were you under the impression that Access does NOT use unicode? It
has since Access 2000. There is an option for "unicode compression" to
reduce the impact; I don't know if there is such an option in SQL Server,
but would think that it would have a similar feature.

Larry Linson
Microsoft Access MVP|||Is Cold Fusion a viable alternative to ASP in this kind of situation?

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||In Ms SQL...

Char is fixed length and pads with spaces
NChar is fixed length and pads with spaces, plus is unicode
VarChar is variable character (with max length spec)
NVarChar is variable (maxlength), plus is unicode

Since I don't deploy to any other language and don't plan to, I never use
the N' datatypes.

Also in SQL, the "SmallDateTime" field type is 4 bytes and the "DateTime" is
8 bytes). If it's 4 bytes in Access, then the equiv is "SmallDateTime".

In SQL Numeric DT's, there is tinyint, smallint, int, real, bigint, float,
money, smallmoney, and decimal. All with their mix of capacity.

--
Jerry Boone
Analytical Technologies, Inc.
http://www.antech.biz

"Larry Linson" <bouncer@.localhost.not> wrote in message
news:bpytb.58064$p9.53071@.nwrddc02.gnilink.net...
> "duncan" wrote
> > Another problem I have seen with
> > the upsize wizard is it makes all
> > columns with the type nvarchar i.e.
> > unicode which doubles the space
> > requirements.
> First, I don't think that is true -- all _text_ columns, perhaps, but
> certainly not numeric and date/time columns.
> Secondly, were you under the impression that Access does NOT use unicode?
It
> has since Access 2000. There is an option for "unicode compression" to
> reduce the impact; I don't know if there is such an option in SQL Server,
> but would think that it would have a similar feature.
> Larry Linson
> Microsoft Access MVP|||Sure, I looked into it some time ago when I was a beginner, but then I
discovered the unimaginable amount of documentation and books from multiple
authors in Microsoft products and I didn't even give it a second thought.
To me, no product is satisfactory if I have to spend days looking for
answers, and especially when they aren't there to be found.

--
Jerry Boone
Analytical Technologies, Inc.
http://www.antech.biz

"John Callaghan" <jackit2@.yahoo.com> wrote in message
news:3fb88be3$0$194$75868355@.news.frii.net...
> Is Cold Fusion a viable alternative to ASP in this kind of situation?
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!|||In my experience, the data conversion part of a project like this is almost
trivial compared to the front-end application challenge.

Access forms and reports can be incredibly complex, with subforms, simple
data binding, no-code dynamic behavior (record navigation, filtering,
sorting, finding, column-reordering, all those are built in to Access, not
at all in asp), and the list goes on and on. If your Access front-end
application is complex, it could be impossibly time-consuming to do in asp,
slightly less so in asp.net.

You would need some other tool for reporting (Crystal or others), I've made
crude html reports in asp and it is nearly impossible to do even the easy
stuff Access reports can do.

As a example, I wrote a full maintenance application for a 30 table database
(with lots of RI between tables), with multiple subforms, dropdown
navigation, and lots of other features, all in one day of work.. ASP would
have taken weeks to do even close to the same functionality.

So overall, my suggestion is if the Access forms are complex, plan for 1000
to 2000% more development time over Access forms. You will definitely need a
better reporting tool than html/asp. Hope this helps,

Steve Nyberg

migrating from Access to SQL Server

Our organization has about 10 multiuser Access databases that I'm
considering migrating to SQL Server. Currently, these split databases
perform reasonably well in Access over our LANs, but slow over the T1 lines.
However, we occasionally run into corruption problems, and we would
eventually like to develop some browser-based applications to work with our
data.
Most of these databases have dozens of queries and form controls also based
on queries, so the most realistic strategy for us in the short term would
probably be to just migrate the data, and link the remaining Access front
ends to the tables in SQL Server.
However, I'm wondering if we will accomplish much by taking such a step,
since all the queries will still be in the front end. I understand there
would be a performance improvement if we had the resources (which we
currently don't) to convert those queries to views and stored procedures in
SQL Server. But if we only move the data tables to the back end, do we
really obtain enough of a benefit to justify taking even that step?
Thanks in advance,
Paul
Hi,
Please do not misunderstand moving data from Acess to SQL Server would
increase the performance, it is still based on the design of the databases
and all the indexes. I would recommend to move from Access to SQL Server
since SQL Server can support more concurrent users and it provides better
security and data import/export functions.
Ed
"Paul Ponzelli" wrote:

> Our organization has about 10 multiuser Access databases that I'm
> considering migrating to SQL Server. Currently, these split databases
> perform reasonably well in Access over our LANs, but slow over the T1 lines.
> However, we occasionally run into corruption problems, and we would
> eventually like to develop some browser-based applications to work with our
> data.
> Most of these databases have dozens of queries and form controls also based
> on queries, so the most realistic strategy for us in the short term would
> probably be to just migrate the data, and link the remaining Access front
> ends to the tables in SQL Server.
> However, I'm wondering if we will accomplish much by taking such a step,
> since all the queries will still be in the front end. I understand there
> would be a performance improvement if we had the resources (which we
> currently don't) to convert those queries to views and stored procedures in
> SQL Server. But if we only move the data tables to the back end, do we
> really obtain enough of a benefit to justify taking even that step?
> Thanks in advance,
> Paul
>
>
|||Good points. Thanks, Ed.
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:1F01575D-FA7D-4C73-ADC6-F205B7B035DD@.microsoft.com...[vbcol=seagreen]
> Hi,
> Please do not misunderstand moving data from Acess to SQL Server would
> increase the performance, it is still based on the design of the databases
> and all the indexes. I would recommend to move from Access to SQL Server
> since SQL Server can support more concurrent users and it provides better
> security and data import/export functions.
> Ed
> "Paul Ponzelli" wrote:
|||"Paul Ponzelli" <begone@.spam.forever> wrote in message
news:enIRb3vPFHA.3596@.TK2MSFTNGP15.phx.gbl...
> Our organization has about 10 multiuser Access databases that I'm
> considering migrating to SQL Server. Currently, these split databases
> perform reasonably well in Access over our LANs, but slow over the T1
lines.
> However, we occasionally run into corruption problems, and we would
> eventually like to develop some browser-based applications to work with
our
> data.
In addition to the valid points that Ed made, SQL Server is far more
resilient to data corruption than Access.
Steve
|||Another reason to make the move.
Thanks, Steve.

Migrating from Access to SQL server

We have some data and a large number of queries in an Access database.
Can we migrate the queries over to SQL aong with the data? The data
should be easy, but rebuilding these queries would be a pain.
Lorenzo,
The Access query upsizing is handled variously - they are created on SQl
Server as either views, stored procedures, or user-defined function, though
a few (like DISTINCTROW queries) aren't upsizeable. The wizard saves a
comprehensive snap shot report (.snp) to your My Documents directory of
everything it was and wasn't able to accomplish.
HTH,
Paul Ibison
|||In article <e7jLNoPLEHA.1340@.TK2MSFTNGP12.phx.gbl>,
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote:

> Lorenzo,
> The Access query upsizing is handled variously - they are created on SQl
> Server as either views, stored procedures, or user-defined function, though
> a few (like DISTINCTROW queries) aren't upsizeable. The wizard saves a
> comprehensive snap shot report (.snp) to your My Documents directory of
> everything it was and wasn't able to accomplish.
> HTH,
> Paul Ibison
>
Thanks, I'll have to look at that again. I found some documentation on
MS's website. It sounded like SQL would not bring the queries in
directly, but the docs are rather ambiguous.

Migrating from Access to SQL server

We have some data and a large number of queries in an Access database.
Can we migrate the queries over to SQL aong with the data? The data
should be easy, but rebuilding these queries would be a pain.Lorenzo,
The Access query upsizing is handled variously - they are created on SQl
Server as either views, stored procedures, or user-defined function, though
a few (like DISTINCTROW queries) aren't upsizeable. The wizard saves a
comprehensive snap shot report (.snp) to your My Documents directory of
everything it was and wasn't able to accomplish.
HTH,
Paul Ibison|||In article <e7jLNoPLEHA.1340@.TK2MSFTNGP12.phx.gbl>,
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote:
> Lorenzo,
> The Access query upsizing is handled variously - they are created on SQl
> Server as either views, stored procedures, or user-defined function, though
> a few (like DISTINCTROW queries) aren't upsizeable. The wizard saves a
> comprehensive snap shot report (.snp) to your My Documents directory of
> everything it was and wasn't able to accomplish.
> HTH,
> Paul Ibison
>
Thanks, I'll have to look at that again. I found some documentation on
MS's website. It sounded like SQL would not bring the queries in
directly, but the docs are rather ambiguous.

Migrating from Access to SQL Server

I recently migrated some user tables from Access to SQL Server while leaving
the forms, reports, and queries in Access with Linked tables to the SQL
instance. Some of the users queries/forms are not working correctly? It
seems that Access Queries return a single empty row when no matches are foun
d
while SQL Server returns an empty result set. In Access, the users use a
query to search for a customer number, if no matching number is found the
customer form is displayed with one record containing no values and the user
s
can then input new customer information. When the query/form is pointed to
SQL Server, when no matching customer is found a blank page is displayed. I
s
there a way to force SQL Server to return an empty row if none are found?Hi,
probably the attached script would help to you (I'm not sure how to
implement it within Access enviroment):
create table t (col1 int null, col2 varchar(10) null)
--without variables
select col1,col2 from t
union all
select null, null
--with variables
declare @.r as int
select * from t
set @.r=@.@.rowcount
if @.r>0
return
else
select null as col1, null as col2
drop table t
Denis
"DT" wrote:

> I recently migrated some user tables from Access to SQL Server while leavi
ng
> the forms, reports, and queries in Access with Linked tables to the SQL
> instance. Some of the users queries/forms are not working correctly? It
> seems that Access Queries return a single empty row when no matches are fo
und
> while SQL Server returns an empty result set. In Access, the users use a
> query to search for a customer number, if no matching number is found the
> customer form is displayed with one record containing no values and the us
ers
> can then input new customer information. When the query/form is pointed t
o
> SQL Server, when no matching customer is found a blank page is displayed.
Is
> there a way to force SQL Server to return an empty row if none are found?
>|||Although your solution does cause the form to be displayed (progress!) I can
not enter data on the form? Am I missing something?
"Denis" wrote:
> Hi,
> probably the attached script would help to you (I'm not sure how to
> implement it within Access enviroment):
> create table t (col1 int null, col2 varchar(10) null)
> --without variables
> select col1,col2 from t
> union all
> select null, null
> --with variables
> declare @.r as int
> select * from t
> set @.r=@.@.rowcount
> if @.r>0
> return
> else
> select null as col1, null as col2
>
> drop table t
> Denis
> "DT" wrote:
>|||There is no such thing as an "empty" row in SQL Server. The functionality
you are wanting would probably be a property of the recordset or data grid.
"DT" <DT@.discussions.microsoft.com> wrote in message
news:0D425E66-49D7-4C46-97E2-7813DB70A6D0@.microsoft.com...
>I recently migrated some user tables from Access to SQL Server while
>leaving
> the forms, reports, and queries in Access with Linked tables to the SQL
> instance. Some of the users queries/forms are not working correctly? It
> seems that Access Queries return a single empty row when no matches are
> found
> while SQL Server returns an empty result set. In Access, the users use a
> query to search for a customer number, if no matching number is found the
> customer form is displayed with one record containing no values and the
> users
> can then input new customer information. When the query/form is pointed
> to
> SQL Server, when no matching customer is found a blank page is displayed.
> Is
> there a way to force SQL Server to return an empty row if none are found?
>

Migrating from Access to SQL server

We have some data and a large number of queries in an Access database.
Can we migrate the queries over to SQL aong with the data? The data
should be easy, but rebuilding these queries would be a pain.Lorenzo,
The Access query upsizing is handled variously - they are created on SQl
Server as either views, stored procedures, or user-defined function, though
a few (like DISTINCTROW queries) aren't upsizeable. The wizard saves a
comprehensive snap shot report (.snp) to your My Documents directory of
everything it was and wasn't able to accomplish.
HTH,
Paul Ibison|||In article <e7jLNoPLEHA.1340@.TK2MSFTNGP12.phx.gbl>,
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote:

> Lorenzo,
> The Access query upsizing is handled variously - they are created on SQl
> Server as either views, stored procedures, or user-defined function, thoug
h
> a few (like DISTINCTROW queries) aren't upsizeable. The wizard saves a
> comprehensive snap shot report (.snp) to your My Documents directory of
> everything it was and wasn't able to accomplish.
> HTH,
> Paul Ibison
>
Thanks, I'll have to look at that again. I found some documentation on
MS's website. It sounded like SQL would not bring the queries in
directly, but the docs are rather ambiguous.sql

Migrating from Access to SQL

Hi guys, its been awhile. I inherited a database thats an MDB and I need to get it on to sql server but I am having problem with this query, can anyone help please. Its suppose to give you the month and year when you click on it, you just put that in and up come the results on a query or report, now I have something similar to this but the code is very different.

SELECT Format$([Main Table].Date,'mmmm yyyy') AS [Date By Month], [Main Table].[Action Type], Sum([Main Table].[Action Type]) AS [Main Table_Action Type]
FROM [Action Type] INNER JOIN [Main Table] ON [Action Type].ID = [Main Table].[Action Type]
GROUP BY Format$([Main Table].Date,'mmmm yyyy'), [Main Table].[Action Type]
HAVING (((Format$([Main Table].[Date],'mmmm yyyy'))=[Enter the month and the Year]));Which bit are you struggling with? How far have you got?|||The format$ is the part thats giving me fits sQL server doesnt recognize that function|||try this --SELECT DATEPART(year,[Main Table].[Date]) AS [Date By Year]
, DATEPART(month,[Main Table].[Date]) AS [Date By Month]
, [Main Table].[Action Type]
, Sum([Main Table].[Action Type]) AS [Main Table_Action Type]
FROM [Action Type]
INNER
JOIN [Main Table]
ON [Action Type].ID = [Main Table].[Action Type]
WHERE [Main Table].[Date] >= '2007-03-01' /* first day of this month */
AND [Main Table].[Date] < '2007-03-01' /* first day of next month */
GROUP
BY DATEPART(year,[Main Table].[Date])
, DATEPART(month,[Main Table].[Date])
, [Main Table].[Action Type]note you could concatenate those two datepart expressions if you need to

also note the index-friendly method of extracting rows for a certain month (anything else will involve table scans)

and of course access doesn't know the difference between HAVING and WHERE, and in this case, you need WHERE, not HAVING|||and of course access doesn't know the difference between HAVING and WHERE, and in this case, you need WHERE, not HAVINGUh? How can it not know the difference? Are you confusing access and access developers?|||no, access itself generates that crap|||Only if you don't add a second column into your query designer and make that a "Where".

But yes - Access does not encourage thoughtful & efficient query design.|||Thanks guys its just that the query he created gives you a dialog box "like the edit parameters" box and you can put the month and year in yourself and it generates a report. Now I have one for searching my subforms last name field ALTER FUNCTION dbo.SearchLName
(@.Enter_SubjLastName nvarchar(100))
RETURNS TABLE
AS
RETURN ( SELECT dbo.Report.[Incident Report No], dbo.Subjects.SubjFirstName, dbo.Subjects.SubjLastName, dbo.Report.TypeOfIncident
FROM dbo.Report INNER JOIN
dbo.Subjects ON dbo.Report.[Incident Report No] = dbo.Subjects.[Incident number]
WHERE (dbo.Subjects.SubjLastName = @.Enter_SubjLastName) )

This is a function and you enter in the last name and it generate a report for you, now from what I see hes doing the same thing but with Month and Year, does that make sense??|||Here is what I have so far but I need to do this by month too they get a pretty detailed report

SELECT DATEPART(year, dbo.[Main Table].Date) AS [Date By Year], DATEPART(month, dbo.[Main Table].Date) AS [Date By Month],
dbo.[Main Table].[Action Type], SUM(dbo.[Main Table].[Action Type]) AS [Main Table_Action Type]
FROM dbo.[Action Type] INNER JOIN
dbo.[Main Table] ON dbo.[Action Type].ID = dbo.[Main Table].[Action Type]
GROUP BY DATEPART(year, dbo.[Main Table].Date), DATEPART(month, dbo.[Main Table].Date), dbo.[Main Table].[Action Type]
HAVING (DATEPART(year, dbo.[Main Table].Date) = @.Enter_Date)|||Create an MS-Access form with two date controls, an Ok and a Cancel button on it When the user clicks Ok, validate the data in the controls, then use them to call the stored procedure.

-PatP|||The results would have to show in a report, I'm so frustrated, this is what I got so far but its spitting out an error message "Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 'Main Table'."

SELECT Year(3/01/2001)([Main Table].[Date],'yyyy') AS [Date By Year], [Main Table].[Violation Type], Sum([Main Table].[Loss])
AS [Sum Of Loss], Count([Main Table].[Violation Type])
AS [CountOfViolation Type]
FROM [Main Table]
GROUP BY Year(3/01/2001)([Main Table].Date,'yyyy'), [Main Table].[Violation Type], Year([Main Table].[Date])
HAVING ((Year()([Main Table].Date)=[Please Enter the Year]))
GO|||You'll have to excuse me, but can we go back a few posts to that first query. Why are we doing an inner join??? I can see no reason for it because we're not bringing back any results in our resultset from the join table...|||SELECT
DATEPART(year, dbo.[Main Table].Date) AS [Date By Year],
DATEPART(month, dbo.[Main Table].Date) AS [Date By Month],
SUM(dbo.[Main Table].[Action Type]) AS [Main Table_Action Type]
FROM dbo.[Action Type]
INNER JOIN
dbo.[Main Table] ON dbo.[Action Type].ID = dbo.[Main Table].[Action Type]
WHERE
(DATEPART(year, dbo.[Main Table].Date) = @.Enter_Date)
GROUP BY
DATEPART(year, dbo.[Main Table].Date), DATEPART(month, dbo.[Main Table].Date), dbo.[Main Table].[Action Type]

Incidently i'm not seeing a need for the inner join above...|||Quick guess :

SELECT
([Main Table].[Date],'yyyy') AS [Date By Year],
[Main Table].[Violation Type], Sum([Main Table].[Loss]) AS [Sum Of Loss],
Count([Main Table].[Violation Type]) AS [CountOfViolation Type]
FROM [Main Table]
WHERE DATEPART(year,[Main Table].Date)=@.YEAR
GROUP BY DATEPART(year,[Main Table].Date), [Main Table].[Violation Type]|||I inherited this database it was an MDB and I'm trying to convert it to an ADP I ran that select statement in the query analyzer and this is the error message i'm gettting

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near ','.|||Hold on, i need some clarity here for piece of mind, to make sure that i'm working with the right thing :

Are you running these queries in MSSQL or MSAccess ?|||Found my error :

SELECT
DATEPART(year,[Main Table].[Date]) AS [Date By Year],
[Main Table].[Violation Type], Sum([Main Table].[Loss]) AS [Sum Of Loss],
Count([Main Table].[Violation Type]) AS [CountOfViolation Type]
FROM [Main Table]
WHERE DATEPART(year,[Main Table].Date)=@.YEAR
GROUP BY DATEPART(year,[Main Table].Date), [Main Table].[Violation Type]|||I am running them in MSSQL, I imported the tables over from Access 2003 onto SQL server. The user set up his reports a certain way and since Jet SQL and SQL Server dont exactly speak the same language I have to redo allot of the queries. Does that make sense??|||Why are we doing an inner join??? I can see no reason for it because we're not bringing back any results in our resultset from the join table...the necessity for doing an inner join is not always predicated on returning something from the joined table

sometimes it is just those rows which have a match that you are interested in

example: say you are the HR department and you have an employees table

suppose you needed to store the email address of their parole officer (let's say to email them confirmation of the employee's attendance)

not every employee has a parole officer, check? so you don't keep the parole officer's email in the employees table (it would need to be NULL for most employees), you keep it in a separate table (incidentally, this is another example of proper normalization, it's 4nf i believe)

now you want to produce a list of employee names and departments for all employees who have a parole officer, but you don't actually need the parole officer's email address

what does your query look like?

that's right, an inner join, selecting only from the left table

:)|||See this the query he set up in Access, if you go into the [Main table] down where the field properties are, he has made one of the fields a combo box and the rowsource is that table [Action Type], and Violation Type is another table that also linked to it. Does that make sense??
SELECT DISTINCTROW Format$([Main Table].Date,'mmmm yyyy') AS [Date By Month], [Main Table].[Violation Type], Sum([Main Table].Loss) AS [Sum Of Loss], Count([Main Table].[Violation Type]) AS [CountOfViolation Type], [Action Type].[Action Type]
FROM [Action Type] INNER JOIN [Main Table] ON [Action Type].ID = [Main Table].[Action Type]
GROUP BY Format$([Main Table].Date,'mmmm yyyy'), [Main Table].[Violation Type], [Action Type].[Action Type], Year([Main Table].Date)*12+DatePart('m',[Main Table].Date)-1
HAVING (((Format$([Main Table].[Date],'mmmm yyyy'))=[Enter the month and year]));

Migrating from Access 2003 to SQL CE

I've a database in Access 2003 that I want to migrate to SQL CE since Pocket Access is no longer supported.

I've researched extensively this issue to no avail.

Even tried to use the ADS but it's not feasible,

Please, any help is welcomed!

I have written a set of commercial tools that may help you - see my company's site.

|||

I downloaded your DataPort Wizard, however I always got the same error "Connection to device failed"

I was trying to migrate a local database. From Access 2003 to SDF

Please can you give some hints on this?

thanks!

|||Can you please use the site's forum or the support email to continue this conversation?

Migrating from Access 2003 to SQL CE

I've a database in Access 2003 that I want to migrate to SQL CE since Pocket Access is no longer supported.

I've researched extensively this issue to no avail.

Even tried to use the ADS but it's not feasible,

Please, any help is welcomed!

I have written a set of commercial tools that may help you - see my company's site.

|||

I downloaded your DataPort Wizard, however I always got the same error "Connection to device failed"

I was trying to migrate a local database. From Access 2003 to SDF

Please can you give some hints on this?

thanks!

|||Can you please use the site's forum or the support email to continue this conversation?

Migrating From Access 2000 To SQl Server 2000

Hi,

I have developed an application using VB 6 (SP 5), MS Access 2000 and
Crystal Reports 9. I migrated from from access to SQl Server 2000.
This wasn't a problem as i could import all the tables to SQL Server.
Also, i could make VB6 talk to SQl Server.

The problem arsies when i run my application. The sql syntax for
access seems to be different than that for SQL Server. A simple
example being: In access boolean datatype is true/false ,whereas in
SQL Server the boolean equivalent is bit (numerical 1 or 0). These
kind of issues are causing problems and most queries don't run.

Would i need to go and change all the queries in accordance with SQl
Server syntax ,which would be very time consuming or is there any
function which will convert the access datatype into its equivalent
SQl Server datatype??

Any input/thoughts/suggestions would be appreciated.

Thanks
JatinThere is a big problem with ODBC JET Linked tables when it comes to boolean
fields in Access that get turned into BIT fields in SQL Server.

Basically, JET thinks of boolean TRUE as (-1) where SQL thinks of it as (1).
The ODBC driver switches the values for you in Access, so that when you look at
the table it shows (-1)...but if you look at the table in SQL Server it says
(1).

The bottom line is that the ODBC driver does NOT understand what TRUE or FALSE
means, so the short answer is YES, you have to change all of those refrences to
(-1) or (0) respectively.

The standard, quick answer to this problem is to use a find/replace tool (in
NOTEPAD) to change all

= TRUE

= -1

and = FALSE

= 0

WARNING: This is only true for JET-based ODBC queries. If you create a view on
SQL Server, you'll have to use = 1 for TRUE!!!!

For that reason, I normally use =0 for FALSE, and <>0 for TRUE!!! That way, it
always works.

Another option is to change the BIT datatype on SQL Server to INT
(integer)...that way a (-1) WILL BE STORED by the ODBC driver, and it'll be
consistent.

Hope this helps!

Migrating from Access 2000 to SQL Server

Hey All,
Migrating from Access 2000 to SQL Server 6.5 and am experiencing some difficulty...Using the database utilities -> wizard, use existing database, have File DSN created on Server, select it and the login appears. Enter the login and get error message:

"Reserved error (-1038); there is no message for this error"

I cannot locate any info on. If someone knows anything I would really appreciate your input.

Thanks,
soniaI thought I would add some info for you...

Now, normally when you specify the DSN (which is on a network) it prompts you for the user login but when I am in the wizard I didn't get the prompt, I had to actually use windows explorer, go to the correct server and when I selected it there I get the prompt, and I enter the correct user name/password and then the share folders appear. After I did that, the shared folders appear for me when using the wizard.

I located the error meaning in the reserved error refernces: JET_errBufferTooSmall -1038 Buffer is too small. I don't know what this means I should do... If someone knows anything I would really appreciate your input.

Thanks, Desperately hoping someone knows something
Ssql

Migrating from 2005 to 2005 Mobile

Hello All,

I have a database created on SQL Server 2005 and now I would like to migrate the table structure to a SQL Server 2005 Mobile database. Generating the scripts and running them against the mobile database generates a lot of error that I suppose are caused by not supported features or keywords on the mobile engine.

I would like to know what are the best procedure for doing such migration. Note that this has nothing to do with replicating, since the SQL Server 2005 database will be "discontinued" after the migration.

Thanks in Advance,

Igor Kondrasovas

you have a variety of options

1) you can create a new SQL Mobile database, add a subscription to it that subscribes to a publication which consists of the entire SS2005 database, and then merge replicate

2) you can script the DDL of the SS2005 database and run each statement a line at a time from a simple CF application on device to recreate the skeletal database

I walk you through how to do option 1 in the MSDN webcast I gave in Nov 2005.

http://msreadiness.com/WS_abstract.asp?eid=15003229

Darren

|||

Hello Darren,

Thanks for the help.

I was thinking that there I could just do that using the Management Studio.

I found out a tool called DataPort Wizard that is capable of doing such think, including table data very easily. I hope Microsoft will provide a way for doing that the same way this tool does (very easy by the way).

The other thing is that if you decide to create you own Mobile Database you cannot easily define contrainsts (as foreign keys) using the "Table Creation GUI". Only defining Primary Keys is Allowed. Besides, if you have foreign keys defined you cannot see them on the Colums definition on Management Studio, as you can see on the SQL Server 2005 database. Please correct me if I'm wrong.

Best Regards,

Igor Kondrasovas

|||

You are correct - you cannot do much in terms of defining constraints or altering tables visually in VS2005 or SS2005, but to be honest, the SQL to do this from code (or in SS2005 Mgmt Studio Query Window) is not difficult.

Darren

Migrating from 2000 to 2005

Hello guys, I have vry basic question. I'm migrating from 2000 to 2005. MY question is do we have to recode all packages or just migrating of old 2000 DTS is fine. If so, then I can see only small icon of DTS package in 2005, whihc is not showing the details of DTS. Kindly, explain me what do i really do, migrating old one or re-creating new in 2005. Thanks in Advance

There is some information in this forum and on the web:

http://forums.microsoft.com/MSDN/Search/Search.aspx?words=DTS+migration&localechoice=9&SiteID=1&searchscope=forumscope&ForumID=80

http://www.google.com/search?hl=en&q=migration+ssis+to+dts

Migrating from 2 Instances to 1 Instance (SQL 2005)

Hello, someone please help!!!!
I am trying to simplify life by combining 2 sql instances that are on the
same box into 1. Basically there is only 1 database per instance that is
being used for any real time production activity. My main problem is, that
there identical user logins (name and pw) setup on the two instances, and
when I try to combine them and call stored procs it can't find them in some
cases. In my research I found that this is because for the user, SQL really
only looks in the user's default database for the SPs. My problem with thi
s
is that I have different applications using the same login and it will be a
lot of trouble to change the user that the apps are using.
Also, it doesn't appear that the initial catalog param I am using in my
connection string is actually doing anything. Actually, I can completely
remove it and it makes no difference in how the application acts (mainly
because the connection just gets established to the default DB no matter wha
t
I pass in), any ideas on this? I mean, what is the point of having the
option if it doesn't do anything.
So, I guess my main question is, is there any way to make SQL look at more
than just the default DB to connect to, or is there a setting in SQL 2005
that allows me to connect to a different DB other than the default if I want
to by looking at the connection string.
Any help will be greatly appreciated...At the very least, have you looked at sp_change_users_login? That should
help you to consolidate servers.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
"Jeff Gibson" <Jeff Gibson@.discussions.microsoft.com> wrote in message
news:7BBD384B-A311-493B-B7F9-D66985374B2B@.microsoft.com...
Hello, someone please help!!!!
I am trying to simplify life by combining 2 sql instances that are on the
same box into 1. Basically there is only 1 database per instance that is
being used for any real time production activity. My main problem is, that
there identical user logins (name and pw) setup on the two instances, and
when I try to combine them and call stored procs it can't find them in some
cases. In my research I found that this is because for the user, SQL really
only looks in the user's default database for the SPs. My problem with
this
is that I have different applications using the same login and it will be a
lot of trouble to change the user that the apps are using.
Also, it doesn't appear that the initial catalog param I am using in my
connection string is actually doing anything. Actually, I can completely
remove it and it makes no difference in how the application acts (mainly
because the connection just gets established to the default DB no matter
what
I pass in), any ideas on this? I mean, what is the point of having the
option if it doesn't do anything.
So, I guess my main question is, is there any way to make SQL look at more
than just the default DB to connect to, or is there a setting in SQL 2005
that allows me to connect to a different DB other than the default if I want
to by looking at the connection string.
Any help will be greatly appreciated...|||Thanks for replying Tom,
As far as I know, our DBA has successfully migrated all the users, in fact,
I am having no login issues for the users themselves (since the logins are
instance based), only when the user tries to access resources that are not
located in its' default database. Basically, I have a "LoginUser" user on
two databases; say DB1, and DB2 of the same instance. If the stored
procedure is on DB2, but LoginUser's default database is DB1, I get a messag
e
from SQL saying that it couldn't find the SP. If I change the default db
using sp_defaultdb to DB2 then the call works, but this is not an option
since I do have stored procedures also on DB1 that the LoginUser is accessin
g
(well, needs to) on DB1 also. I can also use a three part naming convention
[DB2].[dbo].[StoredProcedureName], but for that to work I will n
eed to change
and re-compile all my applications - which in that case I could just add a
new user, say LoginUserDB2, and change only the applications' connection
strings that access resources on DB2 - but the problem with that is too much
up front work since I have many applications, some of which I am not even
sure where the source code is to actually change the string. Basically, I a
m
trying to find a quick and dirty way for a singe login user to access
resources on 2 databases of the same instance without changing any
application code to do so, if that is my only option I will probably just
keep the 2 instances and move on to other things.
The other weird thing is, since I am passing in the database name in my
connection string, why doesn't this signify the database to connect to. Wha
t
is the point if I can only access the default database resources?
Thanks again.
"Tom Moreau" wrote:

> At the very least, have you looked at sp_change_users_login? That should
> help you to consolidate servers.
> --
> Tom
>|||Hmm. I can't understand why it would ignore the database in the connect
string - unless somewhere in the app it's hard-coded to issue a USE
<database> command. Another explanation is that the connect string is
simply incorrect. It may not throw an error; rather it simply doesn't
switch to the specified DB and goes with the default. Can you post the
connect string (sans password)?
What method was used to migrate the logins, BTW? Have you looked at:
http://support.microsoft.com/defaul...kb;en-us;246133
http://forums.microsoft.com/MSDN/Sh...309521&SiteID=1
The scripts preserve the SID's. I'm not sure if that's what happened here.
That said, you may be able to assign default DB's to the new DB, based on
the old system, using SQL-DMO. Just step through the Logins collection of
the source server and look at the Database property. For the same Login on
the destination, set the Database property to be that of the source server.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Jeff Gibson" <JeffGibson@.discussions.microsoft.com> wrote in message
news:A1DCE3CB-AF90-47DB-A28D-AD209B3C157C@.microsoft.com...
Thanks for replying Tom,
As far as I know, our DBA has successfully migrated all the users, in fact,
I am having no login issues for the users themselves (since the logins are
instance based), only when the user tries to access resources that are not
located in its' default database. Basically, I have a "LoginUser" user on
two databases; say DB1, and DB2 of the same instance. If the stored
procedure is on DB2, but LoginUser's default database is DB1, I get a
message
from SQL saying that it couldn't find the SP. If I change the default db
using sp_defaultdb to DB2 then the call works, but this is not an option
since I do have stored procedures also on DB1 that the LoginUser is
accessing
(well, needs to) on DB1 also. I can also use a three part naming convention
[DB2].[dbo].[StoredProcedureName], but for that to work I will n
eed to
change
and re-compile all my applications - which in that case I could just add a
new user, say LoginUserDB2, and change only the applications' connection
strings that access resources on DB2 - but the problem with that is too much
up front work since I have many applications, some of which I am not even
sure where the source code is to actually change the string. Basically, I
am
trying to find a quick and dirty way for a singe login user to access
resources on 2 databases of the same instance without changing any
application code to do so, if that is my only option I will probably just
keep the 2 instances and move on to other things.
The other weird thing is, since I am passing in the database name in my
connection string, why doesn't this signify the database to connect to.
What
is the point if I can only access the default database resources?
Thanks again.
"Tom Moreau" wrote:

> At the very least, have you looked at sp_change_users_login? That should
> help you to consolidate servers.
> --
> Tom
>|||I'll reiterate - check out the sp_change_users_login. Your "identical"
user ids really aren't. They are from two different instances,
therefore have two different SIDS. unless your DBA removed all the
users and completely recreated them, they won't have access. Just
recently ran in to this, and that stored procedure saved the day.|||Hi
Tell your DBA to read this article
http://blogs.msdn.com/lcris/archive.../03/567680.aspx
"PSPDBA" <DissendiumDBA@.gmail.com> wrote in message
news:1148583484.249905.46360@.u72g2000cwu.googlegroups.com...
> I'll reiterate - check out the sp_change_users_login. Your "identical"
> user ids really aren't. They are from two different instances,
> therefore have two different SIDS. unless your DBA removed all the
> users and completely recreated them, they won't have access. Just
> recently ran in to this, and that stored procedure saved the day.
>

Friday, March 30, 2012

Migrating from MDF File to SQL Server 2005?

Migrating from Intebrase to SQL Server

Migrating from Excel to sql-server

Migrating from Enterprise to SQL Server 2000 Standard

Migrating From Development to Production Environment in MSAS 2005

Migrating from Developer Edition to Standard Edition

Migrating from Crystal to SSRS questions

Migrating from Crystal Reports Charts using TopN and "Other" to Reporting Services

Migrating from Crystal Reports Charts using TopN and "Other" to Reporting Services

migrating from crystal report 8.5 to 10.0 using vb .

Migrating From CR 9 to VS Reporting Services

Migrating from ASP

Migrating from Acess 2003 to Sql server - Function Val()

Migrating from Access, boolean field problem

Migrating from Access, boolean field problem

Migrating from Access to SQL Server with a web front end

migrating from Access to SQL Server

Migrating from Access to SQL server

Migrating from Access to SQL server

Migrating from Access to SQL Server

Migrating from Access to SQL server

Migrating from Access to SQL

Migrating from Access 2003 to SQL CE

Migrating from Access 2003 to SQL CE

Migrating From Access 2000 To SQl Server 2000

Migrating from Access 2000 to SQL Server

Migrating from 2005 to 2005 Mobile

Migrating from 2000 to 2005

Migrating from 2 Instances to 1 Instance (SQL 2005)

SQL Server Management Studio

Blog Archive

About Me