Creating an application on SQL server 2005 Express that will be migrated to SQL Server

I have several general questions about this but I thought I would describe what I am doing first. My senior project team is developing a web application that will wind up using SQL Server for the database. We are writing it in ASP.net and developing the database on SQL Server Express. My job is to develop and deploy the database. I have an ERD designed and am getting ready to start coding the tables, constraints and stored proceedures but am unsure of a couple of things.

1) How do I create a new schema?

2) I can see how to create tables in the GUI but what I am trying to do is create scripts that can be used to create the tables and objects on the actual server that our client will be using. How do I create scripts in 2005 Express? Do I just go click on the new query button?

3) Are scripts that work for Express going to have any problem executing on SQL Server?

Asp.net 2.0 comes what application services database built for you by the Asp.net team at Microsoft it uses the DBO schema, that is the Asp.net runtime uses the DBO schema. It helps you create users with a few lines of code, you have the option to use that database separately or add those tables to your database. Go to the location below in your hard drive and use the aspnet_regsql utility to create the database, then add connection string and you can add your users with the Web site admin tool. Try the links below for code sample and how to videos, the second link first section video 9 and 7 and 11 in the second section. Hope this helps.

C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727

http://weblogs.asp.net/scottgu/archive/2005/08/25/423703.aspx

http://www.asp.net/learn/videos/

Creating Access tables with SQL

I'm currently writing a web application in Coldfusion which uses a Access 2000 db. I can create tables in SQL ok but am having problems with the Autonumber type. Any ideas?uuuuuummmm...

maybe a little more details?

What error, what syntax...|||I think you are looking for the IDENTITY property. It's not a separate data type like in Access, but a property available for certain data types (principally INT) in SQL.

Do a search on Autonumber on this forum. I recently answered a similar question...

Regards,

hmscott|||Originally posted by Brett Kaiser
uuuuuummmm...

maybe a little more details?

What error, what syntax...

Good point, it was a bit vague.

Trying to create a table with field SID as an Autonumber and the primary key.

I'm using the following query but to be honest I haven't got a clue how to set the first field (SID) to Autonumber

<cfquery name="creattable_users" datasource="#attributes.dsn#">
Create Table #tbl.code#_Users
(
SID INT NOT NULL
PRIMARY KEY
DEFAULT 1,
Firstname VARCHAR(50) NOT NULL,
Surname VARCHAR(50) NOT NULL,
Address VARCHAR(150) NOT NULL,
Town VARCHAR(50) NOT NULL,
County VARCHAR(50) NOT NULL,
Postcode VARCHAR(50) NOT NULL,
email VARCHAR(50),
phone VARCHAR(50) NOT NULL,
mobile VARCHAR(50)
)
</cfquery>

The code in my SQL book is designed purely for SQL DBs and is not having any of it.

The error is -

Error Diagnostic Information
ODBC Error Code = 37000 (Syntax error or access violation)

[Microsoft][ODBC Microsoft Access Driver] Syntax error in CREATE TABLE statement.

SQL = "Create Table test1_Users ( SID INT NOT NULL PRIMARY KEY DEFAULT 1, Firstname VARCHAR(50) NOT NULL, Surname VARCHAR(50) NOT NULL, Address VARCHAR(150) NOT NULL, Town VARCHAR(50) NOT NULL, County VARCHAR(50) NOT NULL, Postcode VARCHAR(50) NOT NULL, email VARCHAR(50), phone VARCHAR(50) NOT NULL, mobile VARCHAR(50) )"


Which is really helpful as you can see.

Thanks in advance for any help.|||Try SID INT IDENTITY (1,1)

Regards,

hmscott|||Originally posted by hmscott
Try SID INT IDENTITY (1,1)

Regards,

hmscott

Tried it but no joy.|||Did you try it this way?

Create Table #tbl.code#_Users
(
SID INT IDENTITY(1,1) NOT NULL,
Firstname VARCHAR(50) NOT NULL,
Surname VARCHAR(50) NOT NULL,
Address VARCHAR(150) NOT NULL,
Town VARCHAR(50) NOT NULL,
County VARCHAR(50) NOT NULL,
Postcode VARCHAR(50) NOT NULL,
email VARCHAR(50),
phone VARCHAR(50) NOT NULL,
mobile VARCHAR(50)
)

regards,

hmscott|||Did a copy and paste on your code, still throws up the same error.
Might have to convince the client to stop using Access.|||Managed to get the answer in another forum, in case anybody is interested the solution is:

SID COUNTER PRIMARY KEY

Thanks for all the help.

Creating a search engine

I'm not sure if this is the appropriate forum to ask this question; however, here goes.

I'm being asked to create/modify a search engine for our Web site. Beyond trying to enhance the speed, I've been asked to add spelling and punctuation checks into the search text.

Right now, all that's being done is a simple like statement.

What's the best method to query a database?

For example, if someone typed in "A and W" or "A/Ws" to get the value"A & W's", how would I make the link? Is there a database table that exists that I could reference to replace & with and (or vice versa) , etc... Is there a good place to start?

I suggest that you look into using the SQL Server full text indexing.

|||

Delayed response (I'm finally getting back to this issue).

So I've tried to create a stored procedure that does both a ContainsTable search and a FreeTextTable search. This seems like it's going to be a pig of a procedure. (Plus I get duplicate records that I need to filter out). The results are pretty good, but I'm worried about performance. Anyone have any experience in using these options? Any good best practices I should be looking out for?

Creating a Report Model

I am primarily a web developer as of late utilizing Visual Web Developer 2005 Express with SQL 2005. I'd like to generate some reports from the data stored in SQL and like the options that the Reporting Services provides. However, I need to create a report model which I understand requires the full Visual Studio product.

Do, or will any of the Express editions of Visual Studio 2005 allow me to create a report model? I really don't have a need for Visual Studio 2005 - except it appears, to create a report model.

Thanks for any insight!

Visual Studio 2005 is not required. Model Designer is part of SQL Server 2005.

Creating a Report Model

I am primarily a web developer as of late utilizing Visual Web Developer 2005 Express with SQL 2005. I'd like to generate some reports from the data stored in SQL and like the options that the Reporting Services provides. However, I need to create a report model which I understand requires the full Visual Studio product.

Do, or will any of the Express editions of Visual Studio 2005 allow me to create a report model? I really don't have a need for Visual Studio 2005 - except it appears, to create a report model.

Thanks for any insight!

Visual Studio 2005 is not required. Model Designer is part of SQL Server 2005.

Creating a Query for Notification

We want to be able to cache the data for our propertydetails web page (a real estate web site)

Right now the stored procedure for that page is not a good candidate for query notification.

Is it possible to have a trigger on a Property table and when the lastupdated field is changed then go and update the web cache data for that propertydetails page?

(i believe that this is Broker Services).

You'll probably receive more responses from another newsgroup or forum. This one is dedicated to SQL Server Notification Services - not the same technology as Query Notifications.

Cheers!

Joe

Creating a Query for Notification

We want to be able to cache the data for our propertydetails web page (a real estate web site)

Right now the stored procedure for that page is not a good candidate for query notification.

Is it possible to have a trigger on a Property table and when the lastupdated field is changed then go and update the web cache data for that propertydetails page?

(i believe that this is Broker Services).

You'll probably receive more responses from another newsgroup or forum. This one is dedicated to SQL Server Notification Services - not the same technology as Query Notifications.

Cheers!

Joe

Creating a database from a form.

I've made a form and now all I want to do is save everything in the form into a new database file but I don't know how. I'm using Visual Web Developer 2005. Somehow I'm guessing that I have to set something somewhere so when the submit button is hit, it knows to save it to a database file which doesn't exist yet so it would have to create it. It should be easy but I can't figure it out.

This is an excellent article to review to fully answer your question:

http://aspnet.4guysfromrolla.com/articles/110905-1.aspx

Hope this helps.

Creating a database backup

Hi,

I have my MSSQL database developed in Visual Web Developer 2005 Express Edition. I am trying to create a backup file of this databse (.bak). Is SQL Server 2005 Studio Express the program that will let me do this?

Also in SQL Server, how can I attach to my database that I have made in Web Developer 2005? if I go to attach it doesn't list anything.

Thanks for any help.

I have fixed the problem now.

CreateReport method delivers empty response

Hello,
I use the web service for the reporting services with Delphi 7.
I call the CreateReport method and it works fine (the RDL is uploaded to the
server), but the SOAP response raises in exception because the answer is
empty:
'<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<soap:Header>
<ServerInfoHeader
xmlns="http://schemas.microsoft.com/sqlserver/2003/12/reporting/reportingservices">
<ReportServerVersionNumber>Microsoft SQL Server Reporting Services, Version
8.00.1038.00</ReportServerVersionNumber>
<ReportServerEdition>Enterprise</ReportServerEdition></ServerInfoHeader>
</soap:Header>
<soap:Body>
<CreateReportResponse
xmlns="http://schemas.microsoft.com/sqlserver/2003/12/reporting/reportingservices" />
</soap:Body></soap:Envelope>'
What can I do to circumvent this? I mean, CreateReport returns an array of
warnings if there are some. But when there are no warnings this error occurs.
What can I do?Hello,
I solved the problem. I wrote a function which edits the SOAP-Packet after
receiving it in Delphi.
I killed the empty response node and now it is working fine, because the
returned value is nil.
"Sandra Geisler" <SandraGeisler@.discussions.microsoft.com> schrieb im
Newsbeitrag news:2BBFAC0E-4DA3-4A3B-9E68-9A73D76D47E5@.microsoft.com...
> Hello,
> I use the web service for the reporting services with Delphi 7.
> I call the CreateReport method and it works fine (the RDL is uploaded to
> the
> server), but the SOAP response raises in exception because the answer is
> empty:
> '<?xml version="1.0" encoding="utf-8"?>
> <soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"
> xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
> xmlns:xsd="">http://www.w3.org/2001/XMLSchema">
> <soap:Header>
> <ServerInfoHeader
> xmlns="">http://schemas.microsoft.com/sqlserver/2003/12/reporting/reportingservices">
> <ReportServerVersionNumber>Microsoft SQL Server Reporting Services,
> Version
> 8.00.1038.00</ReportServerVersionNumber>
> <ReportServerEdition>Enterprise</ReportServerEdition></ServerInfoHeader>
> </soap:Header>
> <soap:Body>
> <CreateReportResponse
> xmlns="http://schemas.microsoft.com/sqlserver/2003/12/reporting/reportingservices"
> />
> </soap:Body></soap:Envelope>'
> What can I do to circumvent this? I mean, CreateReport returns an array of
> warnings if there are some. But when there are no warnings this error
> occurs.
> What can I do?
>

CREATE_ENDPOINT / HTTPRequest

I am trying to figure out how to communicate to some web clients when a
SQL Server table has changed. I am connecting to the database through
ADO embedded on the clients in web pages. I want to be able to refresh
data on the client only when needed. I have researched all sorts of
methodologies and it seams like the CREATE_ENDPOINT will allow for me
to create a connection where I can call some functions like;
getDataStatus and return to the client what date time the data last
changed and then allow my client to requery the database or, something
like, setAccessTime where the Client sends its last date time stamp
when It last queried for data and allow the server to send back the
requested dataset to the client.Synchronize clock, sync up the clocks
of the clients to server.
Any help would be greatly appreciated. DanHello Dan,
I don't think there's a great way to do this since Web Services is essential
ly
a pull rather than push approach. If you were working with traditional ASMX
functions, I'd suggest looking at SqlCacheDependency since that could "subsc
ribe"
to changes on a query without having to invest a lot in the infrastructure
of the solution. Your ASMX would cache the dataset (saving SQL Server genera
ting
it fresh or from cache) and would, more or less, automatically get updated
data whenever your query changed. You could then add your bits for tracking
when the last change was received.
There really shouldn't need to be a have the client do more periodically
"call home" and ask "have you got a new version of this data?" Use some seri
al
number approach instead of time so that you don't have to worry about timezo
ne
changes between the client and the server.
This probably works "better" in that it minimizes the load on SQL Server
and gives you a better programability model than just a native web service
does today. Not saying that these services aren't useful, but they are harde
r
to use concepts like SqlDepedency on.
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||Thanks, I think I understand what you are getting at. There are no real
provisions to push to clients any data without request from the client
unless you take advantage of new .net technology which will allow for
caching of data on querys, tables etc... and automatically update the
data every ' as set up within the SQL or is it web config. Anyway. I
think I may have my clients poll for a serial number from an END_POINT
and then get the data when the serial number is different from the
clients. Do you think this will work. I wasn't sure if you were saying
it would or would not work? Thanks.|||Hello Dan,

> Thanks, I think I understand what you are getting at. There are no
> real provisions to push to clients any data without request from the
> client unless you take advantage of new .net technology which will
> allow for caching of data on querys, tables etc... and automatically
> update the data every '
There a new set of features in SQL Server 2005, .NET 2.0 and ASP.NET 2.0
allow a client to subscriber to notifications of changes on the data underly
ing
a query. In .NET, its common to use SqlDependency for this. In ASP.NET 2.0,
its common to use SqlCacheDependency. These subscribe to server-side events
and allow processing of them on the client side. However, they currently
require an active TDS connection to SQL Server.
When you get the notification, you can then decide what you want to do. A
common choice is to recall your loading code and restart the dependency.

> as set up within the SQL or is it web config.
Some of both. Google on SqlCacheDependency for more information.

> Anyway. I think I may have my clients poll for a serial number from an
> END_POINT and then get the data when the serial number is different
> from the clients. Do you think this will work. I wasn't sure if you
> were saying it would or would not work? Thanks.
No, because the endpoint doesn't have a good way to keep track of a serial
number. I'm basically suggesting that if you want an optimized solution (in
this case), don't use endpoints, use ASMX instead.
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||Thank You, Ill look into it. Dan

Create virtual directory to remote SQL server on a web server without SQL

Is it possible to create a virtual directory to access a remote SQL
server on a web server that does not have SQL installed on it? I have
previously used the Configure SQL XML Support in IIS tool, and I was
hoping that this could still be installed on a server that has IIS but
not SQL on it. If it can, I would be very grateful to anyone who could
tell me how to do it. If it cannot, I would be equally grateful for
any other solutions to my problem.
Many thanks
You access remote SQL Server, typically, via TCP at certain port (1433 as
default) at certain location (computer name or IP address), plus appropriate
authentication. It has nothing to do with virtual directory on the web
server.
<clockemail@.alltel.net> wrote in message
news:1181750810.617111.24060@.i38g2000prf.googlegro ups.com...
> Is it possible to create a virtual directory to access a remote SQL
> server on a web server that does not have SQL installed on it? I have
> previously used the Configure SQL XML Support in IIS tool, and I was
> hoping that this could still be installed on a server that has IIS but
> not SQL on it. If it can, I would be very grateful to anyone who could
> tell me how to do it. If it cannot, I would be equally grateful for
> any other solutions to my problem.
> Many thanks
>
|||On Jun 13, 12:04 pm, "Norman Yuan" <NotR...@.NotReal.not> wrote:
> You access remote SQL Server, typically, via TCP at certain port (1433 as
> default) at certain location (computer name or IP address), plus appropriate
> authentication. It has nothing to do with virtual directory on the web
> server.
> <clockem...@.alltel.net> wrote in message
> news:1181750810.617111.24060@.i38g2000prf.googlegro ups.com...
>
>
> - Show quoted text -
Sorry, I should have been more specific. I need to make JavaScript
XMLHttpRequests to an SQL Server on a remote server, which I can
currently do by using virtual directories as both the database and
website are on the same server. However, both are moving to different
servers, and the web server does not have SQL installed on it.

Create virtual directory to remote SQL server on a web server without SQL

Is it possible to create a virtual directory to access a remote SQL
server on a web server that does not have SQL installed on it? I have
previously used the Configure SQL XML Support in IIS tool, and I was
hoping that this could still be installed on a server that has IIS but
not SQL on it. If it can, I would be very grateful to anyone who could
tell me how to do it. If it cannot, I would be equally grateful for
any other solutions to my problem.
Many thanksYou access remote SQL Server, typically, via TCP at certain port (1433 as
default) at certain location (computer name or IP address), plus appropriate
authentication. It has nothing to do with virtual directory on the web
server.
<clockemail@.alltel.net> wrote in message
news:1181750810.617111.24060@.i38g2000prf.googlegroups.com...
> Is it possible to create a virtual directory to access a remote SQL
> server on a web server that does not have SQL installed on it? I have
> previously used the Configure SQL XML Support in IIS tool, and I was
> hoping that this could still be installed on a server that has IIS but
> not SQL on it. If it can, I would be very grateful to anyone who could
> tell me how to do it. If it cannot, I would be equally grateful for
> any other solutions to my problem.
> Many thanks
>|||On Jun 13, 12:04 pm, "Norman Yuan" <NotR...@.NotReal.not> wrote:
> You access remote SQL Server, typically, via TCP at certain port (1433 as
> default) at certain location (computer name or IP address), plus appropria
te
> authentication. It has nothing to do with virtual directory on the web
> server.
> <clockem...@.alltel.net> wrote in message
> news:1181750810.617111.24060@.i38g2000prf.googlegroups.com...
>
>
>
> - Show quoted text -
Sorry, I should have been more specific. I need to make JavaScript
XMLHttpRequests to an SQL Server on a remote server, which I can
currently do by using virtual directories as both the database and
website are on the same server. However, both are moving to different
servers, and the web server does not have SQL installed on it.

Create virtual directory to remote SQL server on a web server without SQL

Is it possible to create a virtual directory to access a remote SQL
server on a web server that does not have SQL installed on it? I have
previously used the Configure SQL XML Support in IIS tool, and I was
hoping that this could still be installed on a server that has IIS but
not SQL on it. If it can, I would be very grateful to anyone who could
tell me how to do it. If it cannot, I would be equally grateful for
any other solutions to my problem.
Many thanksYou access remote SQL Server, typically, via TCP at certain port (1433 as
default) at certain location (computer name or IP address), plus appropriate
authentication. It has nothing to do with virtual directory on the web
server.
<clockemail@.alltel.net> wrote in message
news:1181750810.617111.24060@.i38g2000prf.googlegroups.com...
> Is it possible to create a virtual directory to access a remote SQL
> server on a web server that does not have SQL installed on it? I have
> previously used the Configure SQL XML Support in IIS tool, and I was
> hoping that this could still be installed on a server that has IIS but
> not SQL on it. If it can, I would be very grateful to anyone who could
> tell me how to do it. If it cannot, I would be equally grateful for
> any other solutions to my problem.
> Many thanks
>|||On Jun 13, 12:04 pm, "Norman Yuan" <NotR...@.NotReal.not> wrote:
> You access remote SQL Server, typically, via TCP at certain port (1433 as
> default) at certain location (computer name or IP address), plus appropriate
> authentication. It has nothing to do with virtual directory on the web
> server.
> <clockem...@.alltel.net> wrote in message
> news:1181750810.617111.24060@.i38g2000prf.googlegroups.com...
>
> > Is it possible to create a virtual directory to access a remote SQL
> > server on a web server that does not have SQL installed on it? I have
> > previously used the Configure SQL XML Support in IIS tool, and I was
> > hoping that this could still be installed on a server that has IIS but
> > not SQL on it. If it can, I would be very grateful to anyone who could
> > tell me how to do it. If it cannot, I would be equally grateful for
> > any other solutions to my problem.
> > Many thanks- Hide quoted text -
> - Show quoted text -
Sorry, I should have been more specific. I need to make JavaScript
XMLHttpRequests to an SQL Server on a remote server, which I can
currently do by using virtual directories as both the database and
website are on the same server. However, both are moving to different
servers, and the web server does not have SQL installed on it.