sql server db: access

Showing posts with label access. Show all posts

Thursday, March 29, 2012

correct syntax for this select in SQL Server?

This (demo) statement is fine in Access, and so far as I can see, should
be OK in SQL Server.

But Enterprise Manager barfs at the final bracket. Can anyone help
please?

select sum(field1) as sum1, sum(field2) as sum2 from
(SELECT * from test where id < 3
union
SELECT * from test where id 2)

In fact, I can reduce it to :-

select * from
(SELECT * from test)

with the same effect - clearly I just need telling :-)

cheers,
Jim
--
Jim
a Yorkshire polymothJim Lawton (usenet1@.jimlawton.TAKEOUTinfo) writes:

Quote:

Originally Posted by

This (demo) statement is fine in Access, and so far as I can see, should
be OK in SQL Server.
>
But Enterprise Manager barfs at the final bracket. Can anyone help
please?
>
select sum(field1) as sum1, sum(field2) as sum2 from
(SELECT * from test where id < 3
union
SELECT * from test where id 2)
>
In fact, I can reduce it to :-
>
select * from
(SELECT * from test)
>
with the same effect - clearly I just need telling :-)

In SQL Server, you need to provide an alias for the derived table:

SELECT * FROM (SELECT *FROM test) AS x

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Derived tables must be assigned an alias.

select * from
(SELECT * from test) as X

Roy Harvey
Beacon Falls, CT

On Fri, 22 Jun 2007 21:19:31 GMT, Jim Lawton
<usenet1@.jimlawton.TAKEOUTinfowrote:

Quote:

Originally Posted by

>This (demo) statement is fine in Access, and so far as I can see, should
>be OK in SQL Server.
>
>But Enterprise Manager barfs at the final bracket. Can anyone help
>please?
>
>select sum(field1) as sum1, sum(field2) as sum2 from
>(SELECT * from test where id < 3
>union
>SELECT * from test where id 2)
>
>In fact, I can reduce it to :-
>
>select * from
>(SELECT * from test)
>
>with the same effect - clearly I just need telling :-)
>
>
>cheers,
>Jim

|||On Fri, 22 Jun 2007 21:30:17 +0000 (UTC), Erland Sommarskog
<esquel@.sommarskog.sewrote:

Quote:

Originally Posted by

>Jim Lawton (usenet1@.jimlawton.TAKEOUTinfo) writes:

Quote:

Originally Posted by

>This (demo) statement is fine in Access, and so far as I can see, should
>be OK in SQL Server.
>>
>But Enterprise Manager barfs at the final bracket. Can anyone help
>please?
>>
>select sum(field1) as sum1, sum(field2) as sum2 from
>(SELECT * from test where id < 3
>union
>SELECT * from test where id 2)
>>
>In fact, I can reduce it to :-
>>
>select * from
>(SELECT * from test)
>>
>with the same effect - clearly I just need telling :-)

>
>In SQL Server, you need to provide an alias for the derived table:
>
SELECT * FROM (SELECT *FROM test) AS x

Thanks to both you and Roy.
--
Jim
a Yorkshire polymoth|||On Sat, 23 Jun 2007 06:32:00 GMT, Jim Lawton
<usenet1@.jimlawton.TAKEOUTinfowrote:

Quote:

Originally Posted by

>On Fri, 22 Jun 2007 21:30:17 +0000 (UTC), Erland Sommarskog
><esquel@.sommarskog.sewrote:
>

Quote:

Originally Posted by

>>Jim Lawton (usenet1@.jimlawton.TAKEOUTinfo) writes:

Quote:

Originally Posted by

>>This (demo) statement is fine in Access, and so far as I can see, should
>>be OK in SQL Server.
>>>
>>But Enterprise Manager barfs at the final bracket. Can anyone help
>>please?
>>>
>>select sum(field1) as sum1, sum(field2) as sum2 from
>>(SELECT * from test where id < 3
>>union
>>SELECT * from test where id 2)
>>>
>>In fact, I can reduce it to :-
>>>
>>select * from
>>(SELECT * from test)
>>>
>>with the same effect - clearly I just need telling :-)

>>
>>In SQL Server, you need to provide an alias for the derived table:
>>
> SELECT * FROM (SELECT *FROM test) AS x

And, actually, and for the record, let's just add, that for this to work
on Oracle we have to omit the "as" - which is OK for SQL Server as well.
--
Jim
a Yorkshire polymoth

Tuesday, March 27, 2012

correct practices with SQL Server

I am hoping someone can give some advice on the following things:

I have read a few times about a data access layer in an n-tier application. I am assuming that this should be done

using sprocs. Is there an advantage of using sprocs instead of views ( in situations where the same thing could

be accomplished using either)? Will a sproc run faster than a view? Can any share any info?

Are sprocs best suited for data access and to enforce business rules?

I know SQL Server has reserved words that shouldn't be used. I am wondering what the best thing to do is

in the following situation? What is the best way to handle storing a customer or clients address? I am working from a book that shows the name of a column as "Address". I have found that with SQL Server 2005

Express that this is a reserved word(it is shown in blue in the query window). I want to keep my names short. I am trying to avoid a name like "StreetAddress". Is my book teaching bad habits?

...........................................thanks...........................................................

Views are generally better than stored procedures where they could be used interchangeably, especially considering that you can create indexes on views.

While it makes sense to want to use address for a customer's address, the meaning (in a computer) of the word is too general. It could mean a customer's home address, delivery address, the data address of some memory block on the client side, the IP address of another server, really almost anything. Ideally, you would want to follow naming conventions that enable other developers to immediately draw as much information as they can out of a table (e.g. customer name, customer address, customer ID. This will make it much easier to join tables (e.g. joining customer table with business table might have 2 address fields that need to be resolved). One possible naming convention is to consider the table name (e.g. customers) as an abstract object or schema for a data set and name everything by "<schema> <object name>" you could use underscores if you were more comfortable with unspaced column names. Ideally, this will make it much easier to read complex joins as the project grows and will make maintenance much less of a headache -- since there will be fewer questions like "which address do you mean: the one in customer data, business data, the address of the pointer, or the shipping address of the order in question?".

In general, all books teach both good and bad habits, unfortunately. Most SQL books published over the past 10 years have SQL Injection attacks available against all their examples, for instance. Like grade school -> high school -> college, we have to accept that some percentage of the information we learn in books or from teachers is just wrong. That doesn't mean it isn't practical to use as a learning tool, it is just not perfect in practice. This is sort of the scientific method, work with what you know until you learn something better, then paradigm shift and continue. In the long run, there is no perfect book for any given domain space. The only way to grow is to continuously seek out information, as you are doing now.

Hope that helps,

John

|||

So, it seems like the advantage of using a view with an index would be to increase performance. Should stored procedures be used only when there is a need to write to the database? Are these deductions correct?

I ended up doing what you suggested and changed it to CustAddress and EmpAddress.

........................................Thanks for the advice

Correct Edition of Reporting Services

Hi,

We are in the process of replacing our Microsoft Access(.adp)/SQL2000 based reporting tool to ReportingServices/SQL2000 one. Our development team would be in charge of converting the existing Access based reports to Reporting Service format and and any new reports thereafter. Keeping that in mind which of the existing version(Standard, Enterprise, Developer) of Reporting Services would best suit our environment? The final picture would be something like all the reports will be residing on the Reporting server and it will be accessed by clients with the help of a custom reporting application(made in .net) that calls these reports based on user level permissions to these reports.

Will this be possible by using a Standard edition of RS?

Looking forward to your replies..

Do you mean SQL 2005? I've never used SQL 2000 RS so I couldn't really comment.

|||

No, i meant SQL2000. I know that RS is bundled into the SQL2005 installation media but as far as SQL2000 is concerned, it was only released quite some time after SQL Server was released. I have read in the help file that the Standard edition of SQL2000 does support the standard as well as enterprise edition of RS2000. Therefore i was looking for the correct edition that best suits our requirement which i have mentioned in my earlier post...

|||

Here is the feature mix information for SQL Server 2005 - it is applicable to SQL 2000 also with the exception of features that were new to 2005 (Report Builder, for example).

http://www.microsoft.com/sql/technologies/reporting/rsfeatures.mspx

|||

Thanks Carolyn for the link. So can i safely presume that i could deploy my reports created with Standard Reporting Services for my my users to access in a client-server setup?

Last But not least, I had downloaded the 120 days Eval copy of RS for SQL2000. Since my SQL2000 server is already licensed, should i need extra licensing to use Reporting services?

|||

You should be able to use the standard edition of SRS 2000 to accomplish what you require. The version that you are licensed for matches the version of SQL Server that you have. Developer edition is never licensed for production work.

If you are developing using VS 2005, you have access to the ReportViewer control even if you don't have SSRS 2005 in your environment. You could also embed your reports into your application using it. From your post I don't know if you are doing a WinForm or WebForm, but the ReportViewer control works well in either case. You could source data from your SQL Server 2000 instance, but I don't believe you will be able to display SRS 2000 reports from it directly.

You will need the Visual Studio 2003 environment for anyone you want to be able to create reports. That yet another big difference between SRS 2000 and SSRS 2005. SSRS 2005 does not require VS to be installed, it is capable of installing the pieces it requires.

RE the licensing, short answer: SRS is a component of SQL Server. Anywhere you are licensed to have SQL Server installed, you can also have SRS installed. Here is a page that describes some of the different scenarios:

http://www.microsoft.com/sql/prodinfo/previousversions/rs/howtolicensers.mspx

Regarding your eval copy, if you do not want an interruption of services, go to the link below and order your copy of SRS for S/H cost. Do it now, before the eval expires.

http://www.microsoft.com/sql/prodinfo/previousversions/rs/retailfulfillment.mspx

Make sure to get SP2 for SRS 2000 (http://www.microsoft.com/downloads/details.aspx?FamilyId=502C0D89-1308-4662-8F58-CEC55EF1235B&displaylang=en) as it expands the feature set (adding client printing) as well as fixes some bugs.

Good luck with your migration!

Larry Smithmier

|||

Thanks for your input Larry.

So once i get the licensed copy of SRS2000, i would have to un-install the existing eval copy and install the licensed one?

If thats the case i would want to warn my developers against deploying anything on the server untill a licensed copy is avalable...

Would i have to re-install the client components too on the developers machines? And finally, would i have to apply the SRS2000 SP2 to the developers machines too?

|||

I believe that installing the full version over the evaluation should upgrade the instance in-place. You should not have to uninstall the eval copy.

Let your developers deploy at will. I believe that the installation will upgrade your version. The worst case scenario would be that your developers would need to re-deploy the reports after you uninstalled and reinstalled.

Yes, you would want to apply SP2 to development boxes also.

Larry Smithmier

sql

Correct Edition of Reporting Services

Hi,

Will this be possible by using a Standard edition of RS?

Looking forward to your replies..

Do you mean SQL 2005? I've never used SQL 2000 RS so I couldn't really comment.

|||

Here is the feature mix information for SQL Server 2005 - it is applicable to SQL 2000 also with the exception of features that were new to 2005 (Report Builder, for example).

http://www.microsoft.com/sql/technologies/reporting/rsfeatures.mspx

|||

Thanks Carolyn for the link. So can i safely presume that i could deploy my reports created with Standard Reporting Services for my my users to access in a client-server setup?

Last But not least, I had downloaded the 120 days Eval copy of RS for SQL2000. Since my SQL2000 server is already licensed, should i need extra licensing to use Reporting services?

|||

http://www.microsoft.com/sql/prodinfo/previousversions/rs/howtolicensers.mspx

Regarding your eval copy, if you do not want an interruption of services, go to the link below and order your copy of SRS for S/H cost. Do it now, before the eval expires.

http://www.microsoft.com/sql/prodinfo/previousversions/rs/retailfulfillment.mspx

Good luck with your migration!

Larry Smithmier

|||

Thanks for your input Larry.

So once i get the licensed copy of SRS2000, i would have to un-install the existing eval copy and install the licensed one?

If thats the case i would want to warn my developers against deploying anything on the server untill a licensed copy is avalable...

Would i have to re-install the client components too on the developers machines? And finally, would i have to apply the SRS2000 SP2 to the developers machines too?

|||

I believe that installing the full version over the evaluation should upgrade the instance in-place. You should not have to uninstall the eval copy.

Yes, you would want to apply SP2 to development boxes also.

Larry Smithmier

Correct Edition of Reporting Services

Hi,

Will this be possible by using a Standard edition of RS?

Looking forward to your replies..

Do you mean SQL 2005? I've never used SQL 2000 RS so I couldn't really comment.

|||

Here is the feature mix information for SQL Server 2005 - it is applicable to SQL 2000 also with the exception of features that were new to 2005 (Report Builder, for example).

http://www.microsoft.com/sql/technologies/reporting/rsfeatures.mspx

|||

Thanks Carolyn for the link. So can i safely presume that i could deploy my reports created with Standard Reporting Services for my my users to access in a client-server setup?

Last But not least, I had downloaded the 120 days Eval copy of RS for SQL2000. Since my SQL2000 server is already licensed, should i need extra licensing to use Reporting services?

|||

http://www.microsoft.com/sql/prodinfo/previousversions/rs/howtolicensers.mspx

Regarding your eval copy, if you do not want an interruption of services, go to the link below and order your copy of SRS for S/H cost. Do it now, before the eval expires.

http://www.microsoft.com/sql/prodinfo/previousversions/rs/retailfulfillment.mspx

Good luck with your migration!

Larry Smithmier

|||

Thanks for your input Larry.

So once i get the licensed copy of SRS2000, i would have to un-install the existing eval copy and install the licensed one?

If thats the case i would want to warn my developers against deploying anything on the server untill a licensed copy is avalable...

Would i have to re-install the client components too on the developers machines? And finally, would i have to apply the SRS2000 SP2 to the developers machines too?

|||

I believe that installing the full version over the evaluation should upgrade the instance in-place. You should not have to uninstall the eval copy.

Yes, you would want to apply SP2 to development boxes also.

Larry Smithmier

Correct access to TempDB?

uff... I've another problem...
In this loop I've same ADO 2.7 error (number: -2147217865,
description: Invalid object name '#tabella_temp') at STEP 3:

'--STEP 1--
sSql = "IF OBJECT_ID('tempdb..#tabella') IS NOT NULL DROP TABLE
#tabella"
m_cn.Execute sSql, RowAff, adExecuteNoRecords

'--STEP 2--
sSql = "SELECT top 0 * INTO #tabella_temp FROM tabella"
m_cn.Execute sSql, RowAff, adExecuteNoRecords

'STEP --READ DATA--
sSql = "SELECT IdRow from TabellaIn"
rs.Open sSql, m_cn, adOpenForwardOnly, adLockReadOnly

Do While Not rs.EOF
'--STEP 3--
sSql = "insert into #tabella_temp (row) values (" & rs("IdRow") &
")"
m_cn.Execute sSql, RowAff, adExecuteNoRecords
rs.MoveNext
Loop
rs.CLose

why , why, why?zMatteo (origma@.edpsistem.it) writes:
> uff... I've another problem...
> In this loop I've same ADO 2.7 error (number: -2147217865,
> description: Invalid object name '#tabella_temp') at STEP 3:
> '--STEP 1--
> sSql = "IF OBJECT_ID('tempdb..#tabella') IS NOT NULL DROP TABLE
> #tabella"
> m_cn.Execute sSql, RowAff, adExecuteNoRecords
> '--STEP 2--
> sSql = "SELECT top 0 * INTO #tabella_temp FROM tabella"
> m_cn.Execute sSql, RowAff, adExecuteNoRecords
> 'STEP --READ DATA--
> sSql = "SELECT IdRow from TabellaIn"
> rs.Open sSql, m_cn, adOpenForwardOnly, adLockReadOnly
> Do While Not rs.EOF
> '--STEP 3--
> sSql = "insert into #tabella_temp (row) values (" & rs("IdRow") &
> ")"
> m_cn.Execute sSql, RowAff, adExecuteNoRecords
> rs.MoveNext
> Loop
> rs.CLose
>
> why , why, why?

Seems to be the same problem again. Your connection is busy with getting
data from TabellaIn, so ADO opens second connection for you, and then
the temp table is not there.

Two ways to address this:

o Use a client-side cursor. (Connection.CursorLocation = adUseClient)
o Explicitly use two connection, ond for data in and one for
data out.

(Actually I am not entirely sure that using a client-side cursor is
enough. But it's a good thing anyway.)

And of course, if all you do is copy data, it is much more effective
to do it down in SQL Server and not get the data forth and back over
the network.

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

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

Sunday, March 25, 2012

Copying tables from access to sql server express

I am trying to export a databse from access into sql server express. The access database is on a network and the sql server express is on my local machine.

Could someone give me setp by step instructions please as to how to export the data from the tables into my sql server express.

Thank you very much for your time

Access includes a wizard called the Upsize Wizard that can help you do this. Depending on which version of Access you have, it may be on different menus, but last time I saw it it was under Tools | Database Utilities.

You may have to install the Upsize Wizard if you don't already have the Advanced Wizards installed. Search the Access help file for information about using this tool.

Mike

Thursday, March 22, 2012

copying stored procedures to C drive on local machine

I have a project with around 50 stored procedures, can not seem to see the
dbase when using explorer but have access to it with enterprize manager and
visual studio.net server explorer. Just wondering if there is an easy way to
copy the stored procedures onto the workstation possibly using enterprize
manager or .net server explorer? thanks.
--
Paul G
Software engineer.The stored procedures don't really have any meaning outside of the database,
and you should only see MDF/NDF/LDF files in windows explorer (they are not
separate files). What exactly are you trying to accomplish?
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:E3D99B30-FBA9-41E8-9F87-413D02810F43@.microsoft.com...
> I have a project with around 50 stored procedures, can not seem to see the
> dbase when using explorer but have access to it with enterprize manager
and
> visual studio.net server explorer. Just wondering if there is an easy way
to
> copy the stored procedures onto the workstation possibly using enterprize
> manager or .net server explorer? thanks.
> --
> Paul G
> Software engineer.|||You can generate a script of the stored procedures. In SQL Enterprise
manager, right click your database name and go to SQL Scripting... It will
walk you through the process..
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:E3D99B30-FBA9-41E8-9F87-413D02810F43@.microsoft.com...
> I have a project with around 50 stored procedures, can not seem to see the
> dbase when using explorer but have access to it with enterprize manager
and
> visual studio.net server explorer. Just wondering if there is an easy way
to
> copy the stored procedures onto the workstation possibly using enterprize
> manager or .net server explorer? thanks.
> --
> Paul G
> Software engineer.|||Hi thanks for the response. Just wanted to put the stored procedure code
onto a laptop for reference, so copying out to a client machine is sufficient.
"Aaron [SQL Server MVP]" wrote:
> The stored procedures don't really have any meaning outside of the database,
> and you should only see MDF/NDF/LDF files in windows explorer (they are not
> separate files). What exactly are you trying to accomplish?
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:E3D99B30-FBA9-41E8-9F87-413D02810F43@.microsoft.com...
> > I have a project with around 50 stored procedures, can not seem to see the
> > dbase when using explorer but have access to it with enterprize manager
> and
> > visual studio.net server explorer. Just wondering if there is an easy way
> to
> > copy the stored procedures onto the workstation possibly using enterprize
> > manager or .net server explorer? thanks.
> > --
> > Paul G
> > Software engineer.
>
>|||ok thanks will give it a try. Hopefully it allows you to select all the
stored procedures in some fashion as it would take a bit of time to do each
one individually.
"Wayne Snyder" wrote:
> You can generate a script of the stored procedures. In SQL Enterprise
> manager, right click your database name and go to SQL Scripting... It will
> walk you through the process..
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:E3D99B30-FBA9-41E8-9F87-413D02810F43@.microsoft.com...
> > I have a project with around 50 stored procedures, can not seem to see the
> > dbase when using explorer but have access to it with enterprize manager
> and
> > visual studio.net server explorer. Just wondering if there is an easy way
> to
> > copy the stored procedures onto the workstation possibly using enterprize
> > manager or .net server explorer? thanks.
> > --
> > Paul G
> > Software engineer.
>
>|||Yes, it does, try it out...
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:ABF8BA22-734A-472A-9019-91B0D16BD502@.microsoft.com...
> ok thanks will give it a try. Hopefully it allows you to select all the
> stored procedures in some fashion as it would take a bit of time to do
each
> one individually.
> "Wayne Snyder" wrote:
> > You can generate a script of the stored procedures. In SQL Enterprise
> > manager, right click your database name and go to SQL Scripting... It
will
> > walk you through the process..
> >
> > --
> > Wayne Snyder, MCDBA, SQL Server MVP
> > Mariner, Charlotte, NC
> > www.mariner-usa.com
> > (Please respond only to the newsgroups.)
> >
> > I support the Professional Association of SQL Server (PASS) and it's
> > community of SQL Server professionals.
> > www.sqlpass.org
> >
> > "Paul" <Paul@.discussions.microsoft.com> wrote in message
> > news:E3D99B30-FBA9-41E8-9F87-413D02810F43@.microsoft.com...
> > > I have a project with around 50 stored procedures, can not seem to see
the
> > > dbase when using explorer but have access to it with enterprize
manager
> > and
> > > visual studio.net server explorer. Just wondering if there is an easy
way
> > to
> > > copy the stored procedures onto the workstation possibly using
enterprize
> > > manager or .net server explorer? thanks.
> > > --
> > > Paul G
> > > Software engineer.
> >
> >
> >

copying stored procedures to C drive on local machine

Tuesday, March 20, 2012

Copying MSDE database

I have a MSDE database on a laptop and need to copy it to another machine in order to use Access 2000 to inspect the tables and view the data using an Access project adp file.
Could someone please tell me how to do this and whether there are any relevant issues/problems.
thanksBACKUP and RESTORE work fine. There's just no GUI

You can do interactive tsql by Navigating to

C:\Program Files\Microsoft SQL Server\80\Tools\Binn

THEN type osql -E -r1

------------------------
Handy Instructions

Type in your tsql commands (BACKUP \RESTORE\Whatever)
For the TSQL reference, go to
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_00_519s.asp

To execute these commands, on a line by itself type "GO"

to QUIT, on a line by itself type "QUIT"sql

Sunday, March 11, 2012

Copying database files

Hi all,
I need to give remote access to a SQL Server 2000 (MSDE) dabatase, meaning
that a developer must be able to upload/dowload via FTP the mdf and ldf
files.
This isn't a production database, so there is no active connection to it
except for the said developer.
The system doesn't even allow us to copy the files, saying that they are in
use.
I've fiddled with the "Auto Close" parameter for the db, but to no avail.
The only way I've found is to stop the SQL Server, but obviously, this isn't
a viable solution.
How can this be done?
TIA
Paul Dussault, MCP
Hi Paul,
The programmer mentioned below could detach the database remotely then copy
the files and last but not least; attach the database. You can do this
without stopping the service.
Yours sincerely,
Jo Segers.
"Paul Dussault" <paulduss@.hotmail.com> schreef in bericht
news:%23Z4mQU7IEHA.2596@.TK2MSFTNGP10.phx.gbl...
> Hi all,
> I need to give remote access to a SQL Server 2000 (MSDE) dabatase, meaning
> that a developer must be able to upload/dowload via FTP the mdf and ldf
> files.
> This isn't a production database, so there is no active connection to it
> except for the said developer.
> The system doesn't even allow us to copy the files, saying that they are
in
> use.
> I've fiddled with the "Auto Close" parameter for the db, but to no avail.
> The only way I've found is to stop the SQL Server, but obviously, this
isn't
> a viable solution.
> How can this be done?
> TIA
> Paul Dussault, MCP
>
|||1. detach the database - sp_detach_db
2. copy the files
3. re-attach the database - sp_attach_db
If you do this on a regular basis, you can create a script to detach your
database, copy the files to an alternate location, then re-attach the
database on a regular basis.
Regards
Shane Brodie
"Paul Dussault" <paulduss@.hotmail.com> wrote in message
news:%23Z4mQU7IEHA.2596@.TK2MSFTNGP10.phx.gbl...
> Hi all,
> I need to give remote access to a SQL Server 2000 (MSDE) dabatase, meaning
> that a developer must be able to upload/dowload via FTP the mdf and ldf
> files.
> This isn't a production database, so there is no active connection to it
> except for the said developer.
> The system doesn't even allow us to copy the files, saying that they are
in
> use.
> I've fiddled with the "Auto Close" parameter for the db, but to no avail.
> The only way I've found is to stop the SQL Server, but obviously, this
isn't
> a viable solution.
> How can this be done?
> TIA
> Paul Dussault, MCP
>
|||Thank you both for your quick replies.
I was aware of the sprocs, but the developper won't have any other access
than HTTP and FTP (ports 80 and 21).
Should I create an ASP script to execute these sprocs and then let the
developper execute to script as needed?
Thanks again!
Paul Dussault, MCP
"Shane Brodie" <sbrodie@.decorkit.com> wrote in message
news:uqrV5b7IEHA.2556@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> 1. detach the database - sp_detach_db
> 2. copy the files
> 3. re-attach the database - sp_attach_db
> If you do this on a regular basis, you can create a script to detach your
> database, copy the files to an alternate location, then re-attach the
> database on a regular basis.
> Regards
> Shane Brodie
> "Paul Dussault" <paulduss@.hotmail.com> wrote in message
> news:%23Z4mQU7IEHA.2596@.TK2MSFTNGP10.phx.gbl...
meaning[vbcol=seagreen]
> in
avail.
> isn't
>
|||A much better option is to use the BACKUP command to backup your =
database(s) to a file. You can use these files to restore on your =
machine. The benefit with this approach is that you do not have to take =
the database offline at any point.
More information about Backup and Restore can be found within Books =
Online
http://www.microsoft.com/sql/techinf...2000/books.asp
--=20
Keith
"Shane Brodie" <sbrodie@.decorkit.com> wrote in message =
news:uqrV5b7IEHA.2556@.TK2MSFTNGP12.phx.gbl...
> 1. detach the database - sp_detach_db
> 2. copy the files
> 3. re-attach the database - sp_attach_db
>=20
> If you do this on a regular basis, you can create a script to detach =
your[vbcol=seagreen]
> database, copy the files to an alternate location, then re-attach the
> database on a regular basis.
>=20
> Regards
>=20
> Shane Brodie
>=20
> "Paul Dussault" <paulduss@.hotmail.com> wrote in message
> news:%23Z4mQU7IEHA.2596@.TK2MSFTNGP10.phx.gbl...
meaning[vbcol=seagreen]
ldf[vbcol=seagreen]
to it[vbcol=seagreen]
are[vbcol=seagreen]
> in
avail.[vbcol=seagreen]
this
> isn't
>=20
>
|||In 6 years of SQL server experience, I've never found a case where using
backup/restore was better than using detach/attach when wholesale
replacement of the database is needed. Additionally, in order to restore
from a backup, the restorer still needs to have access to the SQL server
enterprise manager and the database has to be void of any users or pending
transactions.
Paul's situation indicates only HTTP and FTP access is available. So, his
idea of exposing a script to attach and detach the relevant database files
should work OK. My question is, "What is the developer doing that requires
him/her to make regular wholesale replacements of the entire database?"
-Steve
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:eqZb2V9IEHA.700@.TK2MSFTNGP09.phx.gbl...
A much better option is to use the BACKUP command to backup your database(s)
to a file. You can use these files to restore on your machine. The benefit
with this approach is that you do not have to take the database offline at
any point.
More information about Backup and Restore can be found within Books Online
http://www.microsoft.com/sql/techinf...2000/books.asp
Keith
"Shane Brodie" <sbrodie@.decorkit.com> wrote in message
news:uqrV5b7IEHA.2556@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> 1. detach the database - sp_detach_db
> 2. copy the files
> 3. re-attach the database - sp_attach_db
> If you do this on a regular basis, you can create a script to detach your
> database, copy the files to an alternate location, then re-attach the
> database on a regular basis.
> Regards
> Shane Brodie
> "Paul Dussault" <paulduss@.hotmail.com> wrote in message
> news:%23Z4mQU7IEHA.2596@.TK2MSFTNGP10.phx.gbl...
meaning[vbcol=seagreen]
> in
avail.
> isn't
>
|||sp_detach_db takes the database offline.=20
This is unacceptable in a production environment.
BACKUP does not bring the database offline.
The developer/DBA has many options when restoring the database (WITH =
MOVE, for example)
SQL Server Enterprise Manager is not required to issue the RESTORE =
command. Anything that can execute the appropriate Transact-SQL RESTORE =
command will do. This includes osql.exe (a command line utility), Query =
Analyzer, or even a query window within a web page.
I agree with your question "What is the developer doing that requires =
him/her to make regular wholesale replacements of the entire database?"
Perhaps they are not tracking their table, data, stored procedure =
changes and it is simply "easier" to replace the whole database when =
they want to move their code to production. Most of us would agree that =
this is not the best method of code promotion. It is better to apply =
table changes as necessary, insert/update/delete any data that needs to =
be modified, and create the stored procedures that have changed since =
the latest build and promote to production.
--=20
Keith
"Steve Lupton" <nospam@.nowhere.com> wrote in message =
news:XqZfc.18538$_I3.13377@.twister.socal.rr.com...
> In 6 years of SQL server experience, I've never found a case where =
using
> backup/restore was better than using detach/attach when wholesale
> replacement of the database is needed. Additionally, in order to =
restore
> from a backup, the restorer still needs to have access to the SQL =
server
> enterprise manager and the database has to be void of any users or =
pending
> transactions.
>=20
> Paul's situation indicates only HTTP and FTP access is available. So, =
his
> idea of exposing a script to attach and detach the relevant database =
files
> should work OK. My question is, "What is the developer doing that =
requires
> him/her to make regular wholesale replacements of the entire =
database?"
>=20
> -Steve
>=20
> "Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
> news:eqZb2V9IEHA.700@.TK2MSFTNGP09.phx.gbl...
> A much better option is to use the BACKUP command to backup your =
database(s)
> to a file. You can use these files to restore on your machine. The =
benefit
> with this approach is that you do not have to take the database =
offline at
> any point.
>=20
> More information about Backup and Restore can be found within Books =
Online[vbcol=seagreen]
> http://www.microsoft.com/sql/techinf...2000/books.asp
>=20
> --=20
> Keith
>=20
>=20
> "Shane Brodie" <sbrodie@.decorkit.com> wrote in message
> news:uqrV5b7IEHA.2556@.TK2MSFTNGP12.phx.gbl...
your[vbcol=seagreen]
the[vbcol=seagreen]

Copying database files

Thursday, March 8, 2012

copying data from Microsoft Access to SQL Server

Hello

I am developing a web application that will allow users to upload a .mdb file and from that file I need to populate an SQL database. I know the table name of the .mdb file, but I am unclear how to structure my data access layer correctly. How do I pull data from the .mdb file and once I have it how do i populate the SQL database?

Any advice would be greatly appreciated.

thanks!

There is one problem with moving Access data on the fly Access let people to just point and click what they wish, if that is your situation you need DTS because in SQL Server your data must be clean and relational. So if you have clean table definitions and clean inserts you could just use OpenRowset or OpenQuery. If not then DTS is your friend. So check your setup and post specifics so I can give you the resources as needed. Hope this helps.

Wednesday, March 7, 2012

Copying columns to table with different design

I am working with a SQL database that was migrated from MS Access and adapted for full-text search which involved creating a new table with a different design.I would like to copy three columns (YearGiven, MonthGiven, DayGiven) from the Documents table to the newly-created FullDocuments table so that I can delete the Documents table and four other tables with redundant data.Here are the two tables involved in the column copy:

Documents Table

DocID (Primary Key)

DocNo

SequenceNo

YearGiven

MonthGiven

DayGiven

FullDocuments Table

FullDocID (Primary Key)

DocNo

SequenceNo

SectionText

YearGiven (empty – no data)

MonthGiven (empty – no data)

DayGiven (empty – no data)

After the copy column procedure I want the FullDocuments table structure to look like this:

FullDocuments Table

FullDocID (Primary Key)

DocNo

SequenceNo

SectionText

YearGiven (full of transferred data)

MonthGiven (full of transferred data)

DayGiven (full of transferred data)

The problem is that the FullDocuments table contains approximately 4x as many rows as the Documents table.This is because each document has four types – the primary Text document and three supplemental documents (Background, Report, and Index).Whereas the Documents table has one row for each Document, the Full Documents table has four rows per document (the four document types).Instead of simply doing a copy and paste of columns based on a primary key and foreign key relationship (as described in the MSDN online books), I need to copy and paste based on the DocNo and SequenceNo columns.Thus the same date data (Year, Month, Day) will need to occupy four consecutive rows for each document in the FullDocuments table (as is now the case with the DocNo and SequenceNo).

I will end up with one table that meets the requirements for the full-text search (primary key and all the text to be searched) and eliminate five tables (Documents and the four document type text tables).This is a static database consisting of historical records so I am not concerned about input errors that can be associated with denormalization.

I tried exporting the data using the Export Wizard using the “Copy data from one or more tables” option.The transfer failed.The most relevant lines of the Error Report are:

·Error 0xc0202009: Data Flow Task: An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available.Source: "Microsoft SQL Native Client"Hresult: 0x80004005Description: "The statement has been terminated.".
An OLE DB record is available.Source: "Microsoft SQL Native Client"Hresult: 0x80004005Description: "Cannot insert the value NULL into column 'FullDocumentID', table 'Documents.dbo.FullDocuments'; column does not allow nulls. INSERT fails.".
(SQL Server Import and Export Wizard)

·Error 0xc0047022: Data Flow Task: The ProcessInput method on component "Destination - FullDocuments" (61) failed with error code 0xC0202009. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.
(SQL Server Import and Export Wizard)

·Error 0xc0047021: Data Flow Task: Thread "WorkThread0" has exited with error code 0xC0202009.
(SQL Server Import and Export Wizard)

I then tried the “Write a query to specify the data to transfer” option focusing on just the YearGiven column using various FETCH, INSERT AND UPDATE commands without any luck.Questions:

1.Is the query approach the best way to achieve my objective, or did I miss something in the “Copy data” option in the Export Wizard?

2.If the query approach is the best way, any suggestions on what the query will look like?

Thanks for any help you can provide.

It sounds as though you have a pretty complicated requirement here and therefore I would suggest you try to build a package using BIDS rather than using the import/export wizard.

-Jamie

|||Jamie: Thanks for the suggestion. It would never have occurred to me to use the Business Intelligence Development Studio for this purpose since I think of BIDS as a report generator instead of a tool for moving data around in a database. I'll have to look into this option.|||

BIDS is the tool for building Reporting Services reports, Analysis Services cubes, and Integration Services packages. Hence its a tool for building BI components - hence its name.

-Jamie

Copying all data from table in one DB to another db

I have to SQL Databases.
The first one is used with MS Access to transfer/convert data from clients
existing database (could be any type) to a database (SQL Database A) in SQL
server. During the process data is modified to meet my applications
requirements
SQL database A - Has no constraints etc just the data fields.
SQL database B - has all the necessary constraints etc.
I have many tables that need to be coppied in a certain order. Is there a
way without DTS to create a script to copy all rows from the tables in SQL
Database A to SQL Database B so i can process the import in table order I
require.
Regards
Jeff
--
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.525 / Virus Database: 322 - Release Date: 9/10/2003As long as the user has the correct permissions to the objects in both
databases, you could issue a series of INSERT INTO/SELECT statements to
move the data from the tables in Database A to Database B.
Rand
This posting is provided "as is" with no warranties and confers no rights.|||... and those statements can be created with a cursor fairly easily. Look
at the sysobjects table with type U. Those objects are user tables.
hth
Quentin
"Rand Boyd [MS]" <rboyd@.onlinemicrosoft.com> wrote in message
news:ycqzEXFlDHA.576@.cpmsftngxa06.phx.gbl...
> As long as the user has the correct permissions to the objects in both
> databases, you could issue a series of INSERT INTO/SELECT statements to
> move the data from the tables in Database A to Database B.
> Rand
> This posting is provided "as is" with no warranties and confers no rights.
>

copying Access data to SQL. Cropping occurs.

I am copying data from MS access 2003 which is in a Memo field to ...
an SQL Server 7 sp4 varchar(8000)...
but only 255 characters copy over.
Any ideas! Your help is appreciated!It's most likely the setting in Query Analyzer that is limiting your
results. Go to options dialog in QA and change the # of chars in the
results tab to 8000. YOu can use DATALENGTH() function to see how large
they are.
Andrew J. Kelly SQL MVP
"BostonSQL" <BostonSQL@.discussions.microsoft.com> wrote in message
news:9565D2F8-64D8-4160-B235-C604690CD34D@.microsoft.com...
>I am copying data from MS access 2003 which is in a Memo field to ...
> an SQL Server 7 sp4 varchar(8000)...
> but only 255 characters copy over.
> Any ideas! Your help is appreciated!|||I am using Access to copy records.
"Andrew J. Kelly" wrote:
> It's most likely the setting in Query Analyzer that is limiting your
> results. Go to options dialog in QA and change the # of chars in the
> results tab to 8000. YOu can use DATALENGTH() function to see how large
> they are.
>
> --
> Andrew J. Kelly SQL MVP
>
> "BostonSQL" <BostonSQL@.discussions.microsoft.com> wrote in message
> news:9565D2F8-64D8-4160-B235-C604690CD34D@.microsoft.com...
> >I am copying data from MS access 2003 which is in a Memo field to ...
> > an SQL Server 7 sp4 varchar(8000)...
> > but only 255 characters copy over.
> > Any ideas! Your help is appreciated!
>
>|||But how are you determining that the data is only 255 chars long?
--
Andrew J. Kelly SQL MVP
"BostonSQL" <BostonSQL@.discussions.microsoft.com> wrote in message
news:4A87A3C1-D16A-44F5-A55A-530AB7BD5278@.microsoft.com...
>I am using Access to copy records.
> "Andrew J. Kelly" wrote:
>> It's most likely the setting in Query Analyzer that is limiting your
>> results. Go to options dialog in QA and change the # of chars in the
>> results tab to 8000. YOu can use DATALENGTH() function to see how large
>> they are.
>>
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "BostonSQL" <BostonSQL@.discussions.microsoft.com> wrote in message
>> news:9565D2F8-64D8-4160-B235-C604690CD34D@.microsoft.com...
>> >I am copying data from MS access 2003 which is in a Memo field to ...
>> > an SQL Server 7 sp4 varchar(8000)...
>> > but only 255 characters copy over.
>> > Any ideas! Your help is appreciated!
>>

Saturday, February 25, 2012

Copying a database between servers

Is backup and restore the best way to simply copy a database from one
SQL Server 7.0 database with 'select' access to another SQL Server 7.0
database on another machine with 'all' access ? Or is there another
easier way with the SQL Server 7.0 tools ?
Edward Diener wrote:
> Is backup and restore the best way to simply copy a database from one
> SQL Server 7.0 database with 'select' access to another SQL Server 7.0
> database on another machine with 'all' access ? Or is there another
> easier way with the SQL Server 7.0 tools ?
You could try detaching and reattaching the database using sp_detach_db
and sp_attach_db / sp_attach_single_file_db. You would need to stop the
server and copy the data and log files and attach the copy. You wouldn't
need to detach in this case. When you attach the copy, you'll likely get
an error related to the log file since the data file points to a log
file in use by the original database. SQL Server 2000 will create a new
log file and attach. I'm not sure if SQL 7 will do the same, but it
likely will.
David Gugick
Imceda Software
www.imceda.com
|||David Gugick wrote:
> Edward Diener wrote:
>
> You could try detaching and reattaching the database using sp_detach_db
> and sp_attach_db / sp_attach_single_file_db. You would need to stop the
> server and copy the data and log files and attach the copy. You wouldn't
> need to detach in this case. When you attach the copy, you'll likely get
> an error related to the log file since the data file points to a log
> file in use by the original database. SQL Server 2000 will create a new
> log file and attach. I'm not sure if SQL 7 will do the same, but it
> likely will.
Can this detach/attach be done with Enterprise Manager and, if not, how
do I do it ?
I tried to backup and restore but SQL Server 7 would only allow me to
backup on the machine where the server resides in which is the database
I want to backup, and would only allow me to restore from the machine
where is the server to which I wanted to restore the database. Now that
is what I call flexibility ! Why I can not backup and restore to and
from any machine to which I am connected and have directory rights I do
not know.
|||Edward Diener wrote:
> Can this detach/attach be done with Enterprise Manager and, if not,
> how do I do it ?
No. You have to run the commands I mentioned.
- Use the database you want to copy in query analyzer
- Run sp_helpfile and note the locations of all data and log files
- Stop the SQL Server
- Open Explorer and make a _copy_ of all data and log files from
sp_helpfile
- Start SQL Server
- Run either sp_attach_db or sp_attach_single_file_db with the new
database name and data file location. For example, for
sp_attach_single_file_db:
Exec sp_attach_single_file_db 'NewDBName', 'C:\Data\NewDataFile.mdf'
-- You'll likely see an error on the log file and a message indicating
the new log file name
-- you can then delete the copied log file since it won't be used any
longer
David Gugick
Imceda Software
www.imceda.com
|||I'm not sure about SQL7.0, but SQL2000 will without any problems backup and
restore databases from other servers. In 2000 you can backup to an UNC path
or a local drive and the same goes for the restore. Attaching and Detaching
the files as DAvid explains will work, but you have to remember that it's an
offline operation where your source database will be unavailable while you
are copying the files. Also there're more steps to be done than if you just
backup the database and then restore it on the new server.
I'm not an expert in doing this from EM, but maybe others can help you with
that. I'd suggest that you look up the Backup and Restore command in Books
On Line and then do it from Query Analyzer - that will give you more options
and flexibility.
Regards
Steen
Edward Diener wrote:
> David Gugick wrote:
> Can this detach/attach be done with Enterprise Manager and, if not,
> how do I do it ?
> I tried to backup and restore but SQL Server 7 would only allow me to
> backup on the machine where the server resides in which is the
> database I want to backup, and would only allow me to restore from
> the machine where is the server to which I wanted to restore the
> database. Now that is what I call flexibility ! Why I can not backup
> and restore to and from any machine to which I am connected and have
> directory rights I do not know.

Thursday, March 29, 2012

correct syntax for this select in SQL Server?

Tuesday, March 27, 2012

correct practices with SQL Server

Correct Edition of Reporting Services

Correct Edition of Reporting Services

Correct Edition of Reporting Services

Correct access to TempDB?

Sunday, March 25, 2012

Copying tables from access to sql server express

Thursday, March 22, 2012

copying stored procedures to C drive on local machine

copying stored procedures to C drive on local machine

copying stored procedures to C drive on local machine

Tuesday, March 20, 2012

Copying MSDE database

Sunday, March 11, 2012

Copying database files

Copying database files

Thursday, March 8, 2012

copying data from Microsoft Access to SQL Server

Wednesday, March 7, 2012

Copying columns to table with different design

Copying all data from table in one DB to another db

copying Access data to SQL. Cropping occurs.

copying Access data to SQL. Cropping occurs.

copying Access data to SQL. Cropping occurs.

Saturday, February 25, 2012

Copying a database between servers

sql server db

Blog Archive

About Me