How to develop database locally and post to web host?

I have MSSQL 2005 Express installed locally. I've developed a database and
would like to copy it to my web host to be accessed using some ASP code.
Everything appears to be OK, except for one issue that I can't figure out.
HOW do I take my local database and upload it to my host?
I do have the MS SQL Server 2005 Express Manager installed and can connect
to both my local SQL 2005 server and my hosts SQL 2003 server.
How are databases normally developed for web applications?
Hi,
"SQL 2003 server." There is no SQL 2003 Server. If your hoster doesn=B4t
offer you to restore backups you ade on your test system. You can
either use scripts changing your database which can be applied on the
server, or you can transfer objects to your hosters db. I am alqys
doing a whole *backup* (with transfering the database objects to my
local machine) doing changes and reapply them on the *productional*
server.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
|||You can backup local database and restore on the host database.
If you developed database file using Visual Studio, you can attach database
on the host.
And can modifiy connection string.
"Noozer"?? ??? ??:

> I have MSSQL 2005 Express installed locally. I've developed a database and
> would like to copy it to my web host to be accessed using some ASP code.
> Everything appears to be OK, except for one issue that I can't figure out.
> HOW do I take my local database and upload it to my host?
> I do have the MS SQL Server 2005 Express Manager installed and can connect
> to both my local SQL 2005 server and my hosts SQL 2003 server.
> How are databases normally developed for web applications?
>
>

How to determine which Service Pack level your SQL Server is running and column name spaci

I have visual studio 2005 with XP home. I'm trying to find out which service pack my SQL server is running as I've been told that Service pack 2 has a few bugs.For example ADO Applications Report a Syntax Error When Column Names Contain Spaces. This appears to be an error i am recieving. I just wanted to confirm that it is a microsoft bug rather then something Im doing wrong.Microsoft meantions the bug here:http://support.microsoft.com/kb/264687I've been looking everywhere. I do not have the "query analyzer window" where you can find this out as I have VS 2005. How would I find this out.Thank you

Query Analyzer is an application that came with SQL 2000.

You can get the same results in SQL Management Studio, by right clicking on a table, choose 'Script Table As', then 'Select To' and lastly, 'New Query Window'

It gives you a select statement based on that table, but you can adjust the query any way you'd like, and then run it to see the results

|||

universaltruth:

I'm trying to find out which service pack my SQL server is running ....

Here is an example that will get you the answer, (Copy & Paste it in your SQL Server)... then Run... we are Done!

My Input:

1SELECTCONVERT(char(20), SERVERPROPERTY('productlevel'));2GO

My Output:

SP2

Done!

----------------

Now we are done for the Service Pack, here is a bonus "Tip"to get know more about the server.

Try this:

execxp_msver

Output:

1 ProductName NULL Microsoft SQL Server
2 ProductVersion 589824 9.00.3054.00
3 Language 1033 English (United States)
4 Platform NULL NT INTEL X86
5 Comments NULL NT INTEL X86
6 CompanyName NULL Microsoft Corporation
7 FileDescription NULL SQL Server Windows NT
8 FileVersion NULL 2005.090.3054.00
9 InternalName NULL SQLSERVR
10 LegalCopyright NULL ? Microsoft Corp. All rights reserved.
11 LegalTrademarks NULL Microsoft? is a registered trademark of Microsoft Corporation. Windows(TM) is a trademark of Microsoft Corporation
12 OriginalFilename NULL SQLSERVR.EXE
13 PrivateBuild NULL NULL
14 SpecialBuild 200146944 NULL
15 WindowsVersion 393216006 6.0 (6000)
16 ProcessorCount 2 2
17 ProcessorActiveMask 3 00000003
18 ProcessorType 586 PROCESSOR_INTEL_PENTIUM
19 PhysicalMemory 2046 2046 (2145202176)
20 Product ID NULL NULL

|||

CS4ever,

This issue of determining service pack level has been bugging me for ages - thanks for that reply. I've worked with early releases of SQL Server, and I had wondered why "select @.@.version" was no longer giving me the proper release info. Once again, thanks!

|||Thank you very much for your answers. Unfortunatley I Know very little about SQL.You said "Here is an example that will get you the answer, (Copy & Paste it in your SQL Server)... then Run... we are Done!"Im not sure where to copy that code. Do i just copy it into the regular aspx.vb page or somewhere else|||

Copy it into a SQL Editor that runs queries against your database. You're using Visual Studio or Visual Web Developer Express? Open the Database Explorer, right-click on the database and choose "new query". You'll be able to copy the SQL into the window provided and hit the "execute" button.

If you have Enterprise Manager of SQL Server Management Studio - which you probably don't - these have better SQL Editors, but the Database Explorer should do fine.

|||

One more thing - if you're using Visual Web Developer Express then the Database Explorer query window doesn't let you run stored procedures. That means the CS4ever's second example that starts with "exec" will throw an error - you'll need Management Studio instead.

But you'll be able to run his first example "Select.." fine. Good luck!

how to deploy SQL Server Database Project w/o Visual Studio.

i need a regex support in SQL 2005 server.

i've downloaded SQL regex support project fromhttp://msdn.microsoft.com/msdnmag/issues/07/02/SQLRegex/default.aspx?loc=en.

but in order to enable regex support i needed to open VS 2005. compile the project and deploy it to SQL server. now i can use Regexp-s in SQL queries.

However this is not elegant way to deploy everytime SQL Database project via Visual Studio to every MS SQL server i want.

my question is : how can i deploy without using Visual Studio?

As long as the assembly is compiled into a DLL, you can use the CREATE ASSEMBLY and then the CREATE FUNCTION commands to install the SQLCLR assembly.

If it isn't compiled, you can download the free .NET Framework 2.0 distribution from Microsoft and use the command line compilers to create the DLL.

This will take a little work and will require learning a few things about SQLCLR code. Are you familiar with any of this?

Don

|||

no, i'm not familiar with CLR. :(

|||

Okay, let's take this a step at a time. Do you have a DLL file for the library you want to use?

Don

|||

i have a dll.

i can create an SQL assembly:

USE [myDB];

EXECsp_configure @.configname='Show Advanced Options', @.configvalue= 1;

RECONFIGUREWITHOVERRIDE;

EXECsp_configure;

CREATEASSEMBLY RegexFROM'C:\Msdn.SqlRegex.dll'WITHPERMISSION_SET=SAFE ;

what is next?

PS: thanks :)

|||

Cool. You're most of the way there. The last step is to use CREATE FUNCTION to create the function you can call from T-SQL. Unlike when you create T-SQL user-defined functions, this time you're going to create a function name linked to a function in the regex assembly. The syntax will be something like this:

CREATE FUNCTION dbo.MyRegExFunc (@.myString nvarchar(4000))
RETURNS nvarchar(4000)
AS EXTERNAL NAME Regex.MyRegExFunc
GO

That's the basic syntax, but you'll have to adjust it for your actual code. A couple of things to note: The name of the function in T-SQL doesn't have to match the name of the function in your assembly. Here I called them both MyRegExFunc, but you can make the T-SQL name any legal name. It's is convenient and easiest to make them the same, however. If you use namespaces in your .NET code, the EXTERNAL NAME might be a bit different. And you have to match the input parameter and return data types, more or less.

Make sense?

Don

|||

thanks alot!!!

i've found the full syntax for creating the functions (just calling the MODIFY in SQL on already imported function from assembly) :

CREATEFUNCTION [dbo].[exportedFunctionName](@.input [nvarchar](max), @.pattern [nvarchar](4000))

RETURNS [bit]WITHEXECUTEASCALLER

AS

EXTERNALNAME [assemblyName].[classInDLL].[functionToExport];

again , thanks alot for helping

How to delete (drop) a database with OSQL

Hi,
I imported DBF files into a new SQL server database and I've been
developing a VB.Net application.
Now I would like to delete the current database and re-import a more
current set of DBF files into SQL.
Only problem, I can't seem to find a way to delete the database from
MSDE. I tried the OSQL 'drop database <name>' but it complains the
'database is in currently in use'.
I must be missing something simple.
Thanks
Richard
Hi ,
You might be in the same database while you are trying to execute the
command.
connect to the server and change the context to the master database and
also make sure that you dint have any sessions currently accessing the
database either remotely or from the same machine. This time the command
should go ahead fine
girish sundaram
This posting is provided "AS IS" with no warranties, and confers no rights.
|||It is possible that there are still connections to the database. If you are
on MSDE 2000 the following is a sure way to drop a database:
-- Kick everyone out of the database
ALTER DATABASE <database name> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
-- Go somewhere else yourself as well
USE master
-- Now drop it
DROP DATABASE <database name>
Jacco Schalkwijk
SQL Server MVP
"Richard Fagen" <no_spam@.my_isp.com> wrote in message
news:eBGaP5K3EHA.2676@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I imported DBF files into a new SQL server database and I've been
> developing a VB.Net application.
> Now I would like to delete the current database and re-import a more
> current set of DBF files into SQL.
> Only problem, I can't seem to find a way to delete the database from MSDE.
> I tried the OSQL 'drop database <name>' but it complains the 'database is
> in currently in use'.
> I must be missing something simple.
> Thanks
> Richard
|||Thanks guys, you were right, there was a connection to the database.
At first I tried a 'use master' in qsql but got the same results. Then
it dawned on me that Visual Studio itself had a connection to the
database. Once I closed it, I could delete the database.
To be sure I understood it, I restored the database, checked in VS.Net
to see it, then I deleted it in osql while VS.Net was open (but no
connection), this also worked.
Thanks for you help!
Richard
Jacco Schalkwijk wrote:
> It is possible that there are still connections to the database. If you are
> on MSDE 2000 the following is a sure way to drop a database:
> -- Kick everyone out of the database
> ALTER DATABASE <database name> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
> -- Go somewhere else yourself as well
> USE master
> -- Now drop it
> DROP DATABASE <database name>
>