Creating an installer for SQL-side components of an app

Hello.
I'm researching about the ways I can create an installer of the SQL (2000)
objects for an application.
At the present time we're using a set of calls to osql utility from a VB
application. But the maintenance of the scripts is becoming cumbersome.
Does MS provides utilities for this? Where can I look up for further info?
Thanks.-
| Thread-Topic: Creating an installer for SQL-side components of an app
| thread-index: AcThIuI4MycuAx2CSYGbOG6c0kGPaQ==
| X-WBNR-Posting-Host: 200.44.173.82
| From: =?Utf-8?B?cnBhbGxhcmVz?= <rpallares@.discussions.microsoft.com>
| Subject: Creating an installer for SQL-side components of an app
| Date: Mon, 13 Dec 2004 06:49:01 -0800
| Lines: 12
| Message-ID: <CF1D14A9-81F6-4891-BCCD-4D8299366B7D@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.clients
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
| Path: cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.clients:29256
| X-Tomcat-NG: microsoft.public.sqlserver.clients
|
| Hello.
|
| I'm researching about the ways I can create an installer of the SQL
(2000)
| objects for an application.
|
| At the present time we're using a set of calls to osql utility from a VB
| application. But the maintenance of the scripts is becoming cumbersome.
|
| Does MS provides utilities for this? Where can I look up for further info?
|
| Thanks.-
|
|
<><><><><><><><><><><><><><><><><><><><><><><><><> <><><>
Hi,
If you still need assistance and you're using MSDE then I believe you will
find this link useful:
http://msdn.microsoft.com/library/de...us/dnmsde/html
/msdedepl.asp
Regards,
Yasemin Gunduz
Support Engineer
This posting is provided "AS IS" with no warranties, and confers no rights.
|||What u mean with the SQL server Objects ?
"rpallares" <rpallares@.discussions.microsoft.com> wrote in message
news:CF1D14A9-81F6-4891-BCCD-4D8299366B7D@.microsoft.com...
> Hello.
> I'm researching about the ways I can create an installer of the SQL (2000)
> objects for an application.
> At the present time we're using a set of calls to osql utility from a VB
> application. But the maintenance of the scripts is becoming cumbersome.
> Does MS provides utilities for this? Where can I look up for further info?
> Thanks.-
>

Creating an custom application simulating an SQL Server Profiler.

Hi,
I have an requirement for doing some custom action in my
application when any SQL server table is modified. One of the ways,
which I think it is achievable is to write an custom application which
listens to SQL queries fired on the particular Database. This will
enable me to do the custom action, when any DML statement is trapped
through my custom SQL profiler. So, more or less, it boils down to
writing/using something similar to the SQL Profiler tool. Any help
would be appreciated.
Thanks in Advance,
Nimesh
Upgrade to SQL Server 2005 and you will have all those features build-in
<nimeshn@.gmail.com> wrote in message
news:1178447651.122809.119940@.q75g2000hsh.googlegr oups.com...
> Hi,
> I have an requirement for doing some custom action in my
> application when any SQL server table is modified. One of the ways,
> which I think it is achievable is to write an custom application which
> listens to SQL queries fired on the particular Database. This will
> enable me to do the custom action, when any DML statement is trapped
> through my custom SQL profiler. So, more or less, it boils down to
> writing/using something similar to the SQL Profiler tool. Any help
> would be appreciated.
> Thanks in Advance,
> Nimesh
>
|||Thanks for the suggestion, Uri. I think you are referring to query
notifications feature in SQL server 2005. But, I got to have this
custom application to work with SQL server 2000 as well.
On May 6, 3:53 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Upgrade toSQLServer2005 and you will have all those features build-in
> <nime...@.gmail.com> wrote in message
> news:1178447651.122809.119940@.q75g2000hsh.googlegr oups.com...
>
>
> - Show quoted text -

Creating an custom application simulating an SQL Server Profiler.

Hi,
I have an requirement for doing some custom action in my
application when any SQL server table is modified. One of the ways,
which I think it is achievable is to write an custom application which
listens to SQL queries fired on the particular Database. This will
enable me to do the custom action, when any DML statement is trapped
through my custom SQL profiler. So, more or less, it boils down to
writing/using something similar to the SQL Profiler tool. Any help
would be appreciated.
Thanks in Advance,
NimeshUpgrade to SQL Server 2005 and you will have all those features build-in
<nimeshn@.gmail.com> wrote in message
news:1178447651.122809.119940@.q75g2000hsh.googlegroups.com...
> Hi,
> I have an requirement for doing some custom action in my
> application when any SQL server table is modified. One of the ways,
> which I think it is achievable is to write an custom application which
> listens to SQL queries fired on the particular Database. This will
> enable me to do the custom action, when any DML statement is trapped
> through my custom SQL profiler. So, more or less, it boils down to
> writing/using something similar to the SQL Profiler tool. Any help
> would be appreciated.
> Thanks in Advance,
> Nimesh
>|||Thanks for the suggestion, Uri. I think you are referring to query
notifications feature in SQL server 2005. But, I got to have this
custom application to work with SQL server 2000 as well.
On May 6, 3:53 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Upgrade toSQLServer2005 and you will have all those features build-in
> <nime...@.gmail.com> wrote in message
> news:1178447651.122809.119940@.q75g2000hsh.googlegroups.com...
>
> > Hi,
> > I have an requirement for doing somecustomaction in my
> >applicationwhen anySQLservertable is modified. One of the ways,
> > which I think it is achievable is to write ancustomapplicationwhich
> > listens toSQLqueries fired on the particular Database. This will
> > enable me to do thecustomaction, when any DML statement is trapped
> > through mycustomSQLprofiler. So, more or less, it boils down to
> > writing/using something similar to theSQLProfilertool. Any help
> > would be appreciated.
> > Thanks in Advance,
> > Nimesh- Hide quoted text -
> - Show quoted text -

Creating an custom application simulating an SQL Server Profiler.

Hi,
I have an requirement for doing some custom action in my
application when any SQL server table is modified. One of the ways,
which I think it is achievable is to write an custom application which
listens to SQL queries fired on the particular Database. This will
enable me to do the custom action, when any DML statement is trapped
through my custom SQL profiler. So, more or less, it boils down to
writing/using something similar to the SQL Profiler tool. Any help
would be appreciated.
Thanks in Advance,
NimeshUpgrade to SQL Server 2005 and you will have all those features build-in
<nimeshn@.gmail.com> wrote in message
news:1178447651.122809.119940@.q75g2000hsh.googlegroups.com...
> Hi,
> I have an requirement for doing some custom action in my
> application when any SQL server table is modified. One of the ways,
> which I think it is achievable is to write an custom application which
> listens to SQL queries fired on the particular Database. This will
> enable me to do the custom action, when any DML statement is trapped
> through my custom SQL profiler. So, more or less, it boils down to
> writing/using something similar to the SQL Profiler tool. Any help
> would be appreciated.
> Thanks in Advance,
> Nimesh
>|||Thanks for the suggestion, Uri. I think you are referring to query
notifications feature in SQL server 2005. But, I got to have this
custom application to work with SQL server 2000 as well.
On May 6, 3:53 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Upgrade toSQLServer2005 and you will have all those features build-in
> <nime...@.gmail.com> wrote in message
> news:1178447651.122809.119940@.q75g2000hsh.googlegroups.com...
>
>
>
> - Show quoted text -sql

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 an "in memory" database

Hi everyone,
I am writing an application for which there are several large internal
collections whose contents have to be filtered and sorted in various ways.
To really take the cake, the contents of the collections may change during
the process (objects coming from other threads).
The number of objects in each collection is fairly high (up to several
thousand) but not so high that I don't anticipate being able to have the
whole model in memory at one time. But it is large enough that I'm going t
o
have to use some kind of indexed access to avoid having to re-sort the
collections all the time.
So a database seems to be warranted here, but there isn't really a
requirement to persist the collections to disk (at least not in this part of
the app) and I don't want to incur the overhead of reading and writing
records to disk. So my questions...
Is there a way to tell SQL Server to create an "in memory" database? I've
looked at "temporary" files, but these look like regular SQL Server database
files that just are automatically dropped at the end of the session. I want
tables/indexes/views that are only contained in memory and get dropped at th
e
end of the session.
Do I really need to worry about this? It's my impression that SQL Server
only writes records to disk when it really needs to anyway, so is my
perception that disk read/writes would slow down the app unfounded?
Thanks for your help.
BBMyuo cant create "in memory" database...
but, you can create "in memory" tempdb
or you can use "in memory" tables - variables
or... optimize your query and use regular db
"BBM" wrote:

> Hi everyone,
> I am writing an application for which there are several large internal
> collections whose contents have to be filtered and sorted in various ways.
> To really take the cake, the contents of the collections may change during
> the process (objects coming from other threads).
> The number of objects in each collection is fairly high (up to several
> thousand) but not so high that I don't anticipate being able to have the
> whole model in memory at one time. But it is large enough that I'm going
to
> have to use some kind of indexed access to avoid having to re-sort the
> collections all the time.
> So a database seems to be warranted here, but there isn't really a
> requirement to persist the collections to disk (at least not in this part
of
> the app) and I don't want to incur the overhead of reading and writing
> records to disk. So my questions...
> Is there a way to tell SQL Server to create an "in memory" database? I've
> looked at "temporary" files, but these look like regular SQL Server databa
se
> files that just are automatically dropped at the end of the session. I wa
nt
> tables/indexes/views that are only contained in memory and get dropped at
the
> end of the session.
> Do I really need to worry about this? It's my impression that SQL Server
> only writes records to disk when it really needs to anyway, so is my
> perception that disk read/writes would slow down the app unfounded?
> Thanks for your help.
> BBM|||SQL Server doesn't support memory-only databases but that isn't really
a problem in terms of performance as SQL Server makes extensive use of
cacheing. If your data is small enough and created only during a single
session then most reads will probably be from cache anyway.
For optimum performance it doesn't make much sense to create and drop
databases and tables at runtime. Create the empty tables you need at
installation and then populate them a runtime. If required you can
always delete the data afterwards but there's no real reason to drop
the tables since you will presumably only have to create them again
later.
As you only need a small-footprint DB, you could consider using MSDE
for this.
http://www.microsoft.com/sql/msde/
David Portas
SQL Server MVP
--|||If these records are to be updated often, then your biggest concern will not
be time it takes to query but rather record locking and other concurrency
issues. Just start off with the idea of an ordinary table, and implement
minimal indexing, becuase several thousand records is actually not a lot, it
depends on the total length of the record, and updating indexes could cause
more concurrency issues. Also, read up on options for transaction isolation
level in BOL. Using "set transaction level read uncommitted" will result in
the least record locking.
What will be the maximum number of records in this table assuming growth
over the next year? When a process queries the table, is it important that
they pull the absolute most recent updates from other processes? Perhaps one
process will not have a need to query across another processes updates?
"BBM" <bbm@.bbmcompany.com> wrote in message
news:FC96FA9F-0ED6-4D26-9B66-0B02A576EB2A@.microsoft.com...
> Hi everyone,
> I am writing an application for which there are several large internal
> collections whose contents have to be filtered and sorted in various ways.
> To really take the cake, the contents of the collections may change during
> the process (objects coming from other threads).
> The number of objects in each collection is fairly high (up to several
> thousand) but not so high that I don't anticipate being able to have the
> whole model in memory at one time. But it is large enough that I'm going
to
> have to use some kind of indexed access to avoid having to re-sort the
> collections all the time.
> So a database seems to be warranted here, but there isn't really a
> requirement to persist the collections to disk (at least not in this part
of
> the app) and I don't want to incur the overhead of reading and writing
> records to disk. So my questions...
> Is there a way to tell SQL Server to create an "in memory" database? I've
> looked at "temporary" files, but these look like regular SQL Server
database
> files that just are automatically dropped at the end of the session. I
want
> tables/indexes/views that are only contained in memory and get dropped at
the
> end of the session.
> Do I really need to worry about this? It's my impression that SQL Server
> only writes records to disk when it really needs to anyway, so is my
> perception that disk read/writes would slow down the app unfounded?
> Thanks for your help.
> BBM|||Thanks to all the responders. You all had good input. Right now I'm going
to proceed just using regular SQL Server Tables/Indexes until I prove to
myself that performance is an issue. I was hoping that there was some way t
o
tell SQL Server to keep a table in memory, but I guess there's not.
Is there a way to tell SQL Server to keep it's cache at a certain size? I'm
familiar with DB2 and in DB2 you can do that by table. Essentially you can
set the cache size for a table so large that the entire table becomes memory
resident.
I am intrigued by some of Aleksandar's responses. Could you elaborate on
what you had in mind with "in memory" temporary tables?
Thanks again for your responses.
BBM
"JT" wrote:

> If these records are to be updated often, then your biggest concern will n
ot
> be time it takes to query but rather record locking and other concurrency
> issues. Just start off with the idea of an ordinary table, and implement
> minimal indexing, becuase several thousand records is actually not a lot,
it
> depends on the total length of the record, and updating indexes could caus
e
> more concurrency issues. Also, read up on options for transaction isolatio
n
> level in BOL. Using "set transaction level read uncommitted" will result i
n
> the least record locking.
> What will be the maximum number of records in this table assuming growth
> over the next year? When a process queries the table, is it important that
> they pull the absolute most recent updates from other processes? Perhaps o
ne
> process will not have a need to query across another processes updates?
> "BBM" <bbm@.bbmcompany.com> wrote in message
> news:FC96FA9F-0ED6-4D26-9B66-0B02A576EB2A@.microsoft.com...
> to
> of
> database
> want
> the
>
>|||Please view my response to JT below... Thanks.
"Aleksandar Grbic" wrote:
> yuo cant create "in memory" database...
> but, you can create "in memory" tempdb
> or you can use "in memory" tables - variables
> or... optimize your query and use regular db
> "BBM" wrote:
>|||Please see my reply to JT below...
Thanks.
"David Portas" wrote:

> SQL Server doesn't support memory-only databases but that isn't really
> a problem in terms of performance as SQL Server makes extensive use of
> cacheing. If your data is small enough and created only during a single
> session then most reads will probably be from cache anyway.
> For optimum performance it doesn't make much sense to create and drop
> databases and tables at runtime. Create the empty tables you need at
> installation and then populate them a runtime. If required you can
> always delete the data afterwards but there's no real reason to drop
> the tables since you will presumably only have to create them again
> later.
> As you only need a small-footprint DB, you could consider using MSDE
> for this.
> http://www.microsoft.com/sql/msde/
> --
> David Portas
> SQL Server MVP
> --
>|||You can set minimum and maximum values for the RAM used by SQL Server
(sp_configure or change it in Enterprise Manager). Data will still be
written to disk however - you cannot avoid this. Even creating
temporary tables will cause data to be written to the tempdb log file.
The point is that with adequate RAM you shouldn't have to spend much
time waiting for disk reads and writes.
As JT indicated, however, there are issues that you should consider
much more important than disk usage. Good database design and
well-written code are far more important factors in determining overall
performance. A bad design or poorly written code can kill even a small
database.
David Portas
SQL Server MVP
--|||"BBM" <bbm@.bbmcompany.com> wrote in message
news:8C8DC765-3E23-403B-A206-E4ED44CB5117@.microsoft.com...
> Thanks to all the responders. You all had good input. Right now I'm
going
> to proceed just using regular SQL Server Tables/Indexes until I prove to
> myself that performance is an issue. I was hoping that there was some way
to
> tell SQL Server to keep a table in memory, but I guess there's not.
> Is there a way to tell SQL Server to keep it's cache at a certain size?
I'm
> familiar with DB2 and in DB2 you can do that by table. Essentially you
can
> set the cache size for a table so large that the entire table becomes
memory
> resident.
> I am intrigued by some of Aleksandar's responses. Could you elaborate on
> what you had in mind with "in memory" temporary tables?
> Thanks again for your responses.
>
There are several methods that you can use.
1. Create the tempdb in memory and then use it. (Not necessarily a
preferred solution.)
2. Use table level variables in your stored procedures. (Not necessarily a
preferred solution.)
3. Use DBCC PINTABLE and UNPINTABLE for tables to live in memory once read
from disk. (Not necessarily a preferred solution.)
4. Preferred solution -- If the entire database is has a small footprint,
then just use regular SQL Server tables and indexes to create and work with
everything. Once data and index pages are read in to memory, unless SQL
Server needs more RAM for something, they will not be flushed back to disk.
Ensure that SQL Server has enough memory to keep everything in memory.
Testing will tell, but I have a feeling that all of the extra work for
PINTABLE, and/or table level variables etc. will probably NOT outperform
letting SQL Server manage itself.
Rick Sawtell
MCT, MCSD, MCDBA|||David and Rick,
Thanks again for the clarifications.
I have already taken JT's concerns into account. My original description of
my problem was inaccurate and you guys are justified in worrying about
concurrency. In actuality, records would be added to the database only by
my central process. Other processes notify my process that they have an ite
m
that needs to be inserted, but they that do it by raising an event that is
handled by my running process and it does the update. As it performs the
update, my main process decides whether the insert affects what it has been
doing and possibly starts over.
Lots of interesting stuff in your responses, but I think I'll take your
advice and make sure I have a problem before I start jumping through hoops.
Thanks again.
BBM
"Rick Sawtell" wrote:

> "BBM" <bbm@.bbmcompany.com> wrote in message
> news:8C8DC765-3E23-403B-A206-E4ED44CB5117@.microsoft.com...
> going
> to
> I'm
> can
> memory
> There are several methods that you can use.
> 1. Create the tempdb in memory and then use it. (Not necessarily a
> preferred solution.)
> 2. Use table level variables in your stored procedures. (Not necessarily
a
> preferred solution.)
> 3. Use DBCC PINTABLE and UNPINTABLE for tables to live in memory once rea
d
> from disk. (Not necessarily a preferred solution.)
> 4. Preferred solution -- If the entire database is has a small footprint
,
> then just use regular SQL Server tables and indexes to create and work wit
h
> everything. Once data and index pages are read in to memory, unless SQL
> Server needs more RAM for something, they will not be flushed back to disk
.
> Ensure that SQL Server has enough memory to keep everything in memory.
> Testing will tell, but I have a feeling that all of the extra work for
> PINTABLE, and/or table level variables etc. will probably NOT outperform
> letting SQL Server manage itself.
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>
>

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 VB.net event handler for a Stored Procedure

Is it possible to create an event handler in a VB.net application to run whenever a Stored Procedure is run.

My application has a scheduled task which is created and scheduled by users of the application, whenever this scheduled task is run I would like it to contact the application to kick off a sequence of tasks. I would appreciate if anybody could point me in the right direction.You can script a custom trace that captures the execution of this task that can instantiate a COM object which in turn can do whatever you want it to do.|||I'm new to developement is there anywhere I can learn to do this.|||I don't know of any way to call code in a VB app running on another machine, but you can create a simple COM object and then use sp_OACreate (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_oa-oz_9k2t.asp) and the related procedures to launch your COM object on the server.

Will this do what you want?

-PatP|||There both running on the same machine so with a bit of luck I'll be able to get it working.

Thanks

Creating a user in SQLExpress with SQL Server Management Studio Express

I have created a user in SQL Server Management Studio Express. However,
whenever my ASP.NET application tries to connect I get the error
Cannot open database "ABC" requested by the login. The login failed. Login
failed for user 'Fred'.

If I change my login string to Integrated Security=SSPI it works. Also, I
was able to add a user successfully in SQL 2000 but SQLExpress is differnt.

Can anyone tell me how to add a user and give him permissions for a
particular database? Or refer me to a URL that does.

Many thanks.

In SQL Server Management Studio Express, when you login to the SQL Express Server, you'll see "Databases" as an item in the Object Explorer. If you expand that, you'll see all of your databases listed. Select the database you want and expand that, then you'll see Security as one of the items. Expand that one more time and you'll see Users. You can create a new user there, as well as right click on a user and select Properties to fine tune the permissions of that user.

You can also create global users (Logins) by going to the Security item at the top level list in the Object Explorer. Once you've created a Login, you can map it to certain users within a database.

Thanks< MJ

Creating a user in SQLExpress with SQL Server Management Studio Express

I have created a user in SQL Server Management Studio Express. However,
whenever my ASP.NET application tries to connect I get the error
Cannot open database "ABC" requested by the login. The login failed. Login
failed for user 'Fred'.
If I change my login string to Integrated Security=SSPI it works. Also, I
was able to add a user successfully in SQL 2000 but SQLExpress is differnt.
Can anyone tell me how to add a user and give him permissions for a
particular database? Or refer me to a URL that does.
Many thanks.
hi Andrew,
Andrew Chalk wrote:
> I have created a user in SQL Server Management Studio Express.
> However, whenever my ASP.NET application tries to connect I get the
> error Cannot open database "ABC" requested by the login. The login failed.
> Login failed for user 'Fred'.
> If I change my login string to Integrated Security=SSPI it works.
> Also, I was able to add a user successfully in SQL 2000 but
> SQLExpress is differnt.
> Can anyone tell me how to add a user and give him permissions for a
> particular database? Or refer me to a URL that does.
> Many thanks.
please verify your SQLExpress instance accepts standard SQL Server logins,
enabling mixed security.. your connection string changed to ...Integrated
Security=SSPI implies a trusted connection...
SQL Server uses a so called "2 phases" authentication policy:
first an SQL Server Login or a Windows login must be created of granted
access to the SQL Server instance... at the server level a login can be made
member of none, 1 or all of the fixed server roles, which include "sysadmin"
role and so on...
you can choose between 2 authentication modes:
WinNT (trusted) connections or SQL Server authenticated connections... the
latter always requires full user's credential such as "User
Id=sa;Password=pwd", the password can be NULL so it must not be specified,
but I strongly advise you always to ensure strong passwords are present...
WindowsNT authentication, on the contrary, does not requires user's
credential becouse it's directly provided by Windows via the logins'ID
(sid), which authenticate user's login at the windows login step... SQL
Server only needs to verify that the corresponding login and/or group is
granted to log on the instance...
the second authentication phase is at database level, where each login will
be granted database access mapping to a database user... here access
permissions are set, as granting user/role SELECT/DELETE/EXECUTE (and so on)
privileges at an object level (or column level for tables and views)..
so, the second phase regards a database security implementation... in order
to access a specified database the simple login existance does not provide
database access, but a (database) user must be mapped to the corresponding
login.. and is about verifying that at each object level (including
database, tables, views, columns, procedures and so on) the Login/User
association is permitted access to...
CREATE LOGIN http://msdn2.microsoft.com/en-us/library/ms189751.aspx
CREATE USER http://msdn2.microsoft.com/en-us/library/ms173463.aspx
GRANT {database permission}
http://msdn2.microsoft.com/en-us/library/ms178569.aspx
GRANT {permission} http://msdn2.microsoft.com/en-us/library/ms187965.aspx
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.16.0 - DbaMgr ver 0.61.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Andrea: Thanks you for your detailed reply.
I understand the concptual difference between Windows and SQL authentication
and thought I had done all the necessary steps, which is why I am puzzled.
As I mentioned, with SQL 2000 I used SQL Enterprise manager and have the
login working. I have tried to map the steps I took in SQL2000's SQL EM to
those in the new SQL Server Mgt. Studio Express.
1) I created a login under the server->security folder.
I did not give this Login any Roles. It is just a plain ol' user (public).
2) I went to the server instance name at the top and GRANTed "Connect SQL"
permission to the new Login just created;
3) I went to the database and granted "Connect" permission to the Login.
After these steps I get the error reported in my earlier post.
I should add that I am not an SQL Server expert and first looked at the SQL
Server Mgt. Studio Express BOL.They are absolutely useless.
Hope the above hels.
Andrew
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:40acn2F19erhvU1@.individual.net...
> hi Andrew,
> Andrew Chalk wrote:
> please verify your SQLExpress instance accepts standard SQL Server logins,
> enabling mixed security.. your connection string changed to ...Integrated
> Security=SSPI implies a trusted connection...
> SQL Server uses a so called "2 phases" authentication policy:
> first an SQL Server Login or a Windows login must be created of granted
> access to the SQL Server instance... at the server level a login can be
> made
> member of none, 1 or all of the fixed server roles, which include
> "sysadmin"
> role and so on...
> you can choose between 2 authentication modes:
> WinNT (trusted) connections or SQL Server authenticated connections... the
> latter always requires full user's credential such as "User
> Id=sa;Password=pwd", the password can be NULL so it must not be specified,
> but I strongly advise you always to ensure strong passwords are
> present...
> WindowsNT authentication, on the contrary, does not requires user's
> credential becouse it's directly provided by Windows via the logins'ID
> (sid), which authenticate user's login at the windows login step... SQL
> Server only needs to verify that the corresponding login and/or group is
> granted to log on the instance...
> the second authentication phase is at database level, where each login
> will
> be granted database access mapping to a database user... here access
> permissions are set, as granting user/role SELECT/DELETE/EXECUTE (and so
> on)
> privileges at an object level (or column level for tables and views)..
> so, the second phase regards a database security implementation... in
> order
> to access a specified database the simple login existance does not provide
> database access, but a (database) user must be mapped to the corresponding
> login.. and is about verifying that at each object level (including
> database, tables, views, columns, procedures and so on) the Login/User
> association is permitted access to...
> CREATE LOGIN http://msdn2.microsoft.com/en-us/library/ms189751.aspx
> CREATE USER http://msdn2.microsoft.com/en-us/library/ms173463.aspx
> GRANT {database permission}
> http://msdn2.microsoft.com/en-us/library/ms178569.aspx
> GRANT {permission} http://msdn2.microsoft.com/en-us/library/ms187965.aspx
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.16.0 - DbaMgr ver 0.61.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
|||I don't understand the following stement:
> database access, but a (database) user must be mapped to the corresponding
> login..
what is the difference between a Login and a User?
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:40acn2F19erhvU1@.individual.net...
> the second authentication phase is at database level, where each login
> will
> be granted database access mapping to a database user... here access
> permissions are set, as granting user/role SELECT/DELETE/EXECUTE (and so
> on)
> privileges at an object level (or column level for tables and views)..
> so, the second phase regards a database security implementation... in
> order
> to access a specified database the simple login existance does not provide
> database access, but a (database) user must be mapped to the corresponding
> login.. and is about verifying that at each object level (including
> database, tables, views, columns, procedures and so on) the Login/User
> association is permitted access to...
> CREATE LOGIN http://msdn2.microsoft.com/en-us/library/ms189751.aspx
> CREATE USER http://msdn2.microsoft.com/en-us/library/ms173463.aspx
> GRANT {database permission}
> http://msdn2.microsoft.com/en-us/library/ms178569.aspx
> GRANT {permission} http://msdn2.microsoft.com/en-us/library/ms187965.aspx
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.16.0 - DbaMgr ver 0.61.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
|||hi Andrew,
Andrew Chalk wrote:
> Andrea: Thanks you for your detailed reply.
> 1) I created a login under the server->security folder.
> I did not give this Login any Roles. It is just a plain ol' user
> (public).
it's not a user, it's a login and there's no "public" server role... it's
just a login which is not member of server roles...

> 2) I went to the server instance name at the top and GRANTed "Connect
> SQL" permission to the new Login just created;
yep... the login has been created, enabled, and granted access to the
instance..

> 3) I went to the database and granted "Connect" permission to the
> Login.
ok, you granted db access to the login, which is now member of th public
database role..

> After these steps I get the error reported in my earlier post.
I reproduced your steps and I can log on to SQLExpress as a standard SQL
Server Login, and access a db the login has been mapped to a corresponding
user.. I did it via SSMSExpress and not via ASP..

> I should add that I am not an SQL Server expert and first looked at
> the SQL Server Mgt. Studio Express BOL.They are absolutely useless.
:D
download the full BOL, december released..

> what is the difference between a Login and a User?
a Login is the very first part of the 2 phases security policy enforcement
of SQL Server... I repeat what I already wrote..
SQL Server uses a so called "2 phases" authentication policy:
first an SQL Server Login or a Windows login must be created in order to
grant access to the SQL Server instance... at the server level a login can
be made member of none, 1 or all of the fixed server roles, which include
"sysadmin" role and so on...
you can choose between 2 authentication modes:
WinNT (trusted) connections or SQL Server authenticated connections... the
latter always requires full user's credential such as "User
Id=sa;Password=pwd", the password can be NULL so it must not be specified,
but I strongly advise you always to ensure strong passwords are present...
WindowsNT authentication, on the contrary, does not requires credential
becouse it's directly provided by Windows via the logins' ID (sid), which
authenticate (Windows) user's login at the windows login step... SQL Server
only needs to verify that the corresponding login and/or group is granted to
log on the instance (checking the granted Login's existence in the SQL
Server instance)...
the second authentication phase is at database level, where each login will
be granted database access mapping to a database user... here access
permissions are set, as granting user/role SELECT/DELETE/EXECUTE (and so on)
privileges at an object level (or column level for tables and views)..
so, the second phase regards a database security implementation... in order
to access a specified database the simple login existance does not provide
database access, but a (database) user must be mapped to the corresponding
login.. and is about verifying that at each object level (including
database, tables, views, columns, procedures and so on) the Login/User
association is permitted access to...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.16.0 - DbaMgr ver 0.61.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:40d46vF19tm2gU1@.individual.net...
> I reproduced your steps and I can log on to SQLExpress as a standard SQL
> Server Login, and access a db the login has been mapped to a corresponding
> user.. I did it via SSMSExpress and not via ASP..
The phrase:
> the login has been mapped to a corresponding user..
Can you explain that.
How did it happen?
Who is the user?
Is it correct to say that the user is an instance of utilizing a login? I.e.
"users" have logins. Some may use the same logins as others (for example
several instances of an application). In which case each application trying
to use SQL server is a user and their name/password pair correspond to their
login?
Thanks.
|||I now have a role named 'fred' and a user named 'fred' of the database that
I want the application to access. Is the use of the same name the problem?
Thanks
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:40d46vF19tm2gU1@.individual.net...
> hi Andrew,
> Andrew Chalk wrote:
> it's not a user, it's a login and there's no "public" server role... it's
> just a login which is not member of server roles...
>
> yep... the login has been created, enabled, and granted access to the
> instance..
>
> ok, you granted db access to the login, which is now member of th public
> database role..
>
> I reproduced your steps and I can log on to SQLExpress as a standard SQL
> Server Login, and access a db the login has been mapped to a corresponding
> user.. I did it via SSMSExpress and not via ASP..
>
> :D
> download the full BOL, december released..
>
> a Login is the very first part of the 2 phases security policy enforcement
> of SQL Server... I repeat what I already wrote..
> SQL Server uses a so called "2 phases" authentication policy:
> first an SQL Server Login or a Windows login must be created in order to
> grant access to the SQL Server instance... at the server level a login can
> be made member of none, 1 or all of the fixed server roles, which include
> "sysadmin" role and so on...
> you can choose between 2 authentication modes:
> WinNT (trusted) connections or SQL Server authenticated connections... the
> latter always requires full user's credential such as "User
> Id=sa;Password=pwd", the password can be NULL so it must not be specified,
> but I strongly advise you always to ensure strong passwords are
> present...
> WindowsNT authentication, on the contrary, does not requires credential
> becouse it's directly provided by Windows via the logins' ID (sid), which
> authenticate (Windows) user's login at the windows login step... SQL
> Server only needs to verify that the corresponding login and/or group is
> granted to log on the instance (checking the granted Login's existence in
> the SQL Server instance)...
> the second authentication phase is at database level, where each login
> will be granted database access mapping to a database user... here access
> permissions are set, as granting user/role SELECT/DELETE/EXECUTE (and so
> on) privileges at an object level (or column level for tables and views)..
> so, the second phase regards a database security implementation... in
> order to access a specified database the simple login existance does not
> provide database access, but a (database) user must be mapped to the
> corresponding login.. and is about verifying that at each object level
> (including database, tables, views, columns, procedures and so on) the
> Login/User
> association is permitted access to...
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.16.0 - DbaMgr ver 0.61.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
|||OK, fixed it, though mainly through luck than knowhow.
Deleted user "fred" and login "fred".
Recreated login Fred but made sure that I went to 'User Mapping' and checked
the database that I wanted this Login to be able to access;
Login was created and, like magic, a user appeared, also named 'fred', under
the database.
I still don't understand this.
Nonetheless, thanks for your help,
Andrew
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:40d46vF19tm2gU1@.individual.net...
> hi Andrew,
> Andrew Chalk wrote:
> it's not a user, it's a login and there's no "public" server role... it's
> just a login which is not member of server roles...
>
> yep... the login has been created, enabled, and granted access to the
> instance..
>
> ok, you granted db access to the login, which is now member of th public
> database role..
>
> I reproduced your steps and I can log on to SQLExpress as a standard SQL
> Server Login, and access a db the login has been mapped to a corresponding
> user.. I did it via SSMSExpress and not via ASP..
>
> :D
> download the full BOL, december released..
>
> a Login is the very first part of the 2 phases security policy enforcement
> of SQL Server... I repeat what I already wrote..
> SQL Server uses a so called "2 phases" authentication policy:
> first an SQL Server Login or a Windows login must be created in order to
> grant access to the SQL Server instance... at the server level a login can
> be made member of none, 1 or all of the fixed server roles, which include
> "sysadmin" role and so on...
> you can choose between 2 authentication modes:
> WinNT (trusted) connections or SQL Server authenticated connections... the
> latter always requires full user's credential such as "User
> Id=sa;Password=pwd", the password can be NULL so it must not be specified,
> but I strongly advise you always to ensure strong passwords are
> present...
> WindowsNT authentication, on the contrary, does not requires credential
> becouse it's directly provided by Windows via the logins' ID (sid), which
> authenticate (Windows) user's login at the windows login step... SQL
> Server only needs to verify that the corresponding login and/or group is
> granted to log on the instance (checking the granted Login's existence in
> the SQL Server instance)...
> the second authentication phase is at database level, where each login
> will be granted database access mapping to a database user... here access
> permissions are set, as granting user/role SELECT/DELETE/EXECUTE (and so
> on) privileges at an object level (or column level for tables and views)..
> so, the second phase regards a database security implementation... in
> order to access a specified database the simple login existance does not
> provide database access, but a (database) user must be mapped to the
> corresponding login.. and is about verifying that at each object level
> (including database, tables, views, columns, procedures and so on) the
> Login/User
> association is permitted access to...
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.16.0 - DbaMgr ver 0.61.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
|||hi Andrew,
Andrew Chalk wrote:
> The phrase:
> Can you explain that.
> How did it happen?
> Who is the user?
perhaps my english is not as good as I thought.. :D
perhaps here,
http://msdn.microsoft.com/library/de...rity_4fol.asp,
the architectural design is more clear..
a SQL Server Login (both standard SQL Server login or a WinNT login) is just
the very first brick in the wall in order to access a SQL Server instance...
without that you can not log in and connect to a specified instance... it
has a server wide range...
a User is just a database user (can have the same name as the corresponding
Login [Login is now somehaw obsolete, to be replaced by principals] but this
is not mandatory even if it's recommended in order to minimize troubles :D)
that, at creation time, is mapped via it's sid column
(dbname.sys.sysusers.sid) to an existing login
(master.sys.server_principals.sid)
SELECT u.name AS [Name in DB], u.hasdbaccess ,
p.name AS [Login Name]
FROM master.sys.server_principals p JOIN sys.sysusers u
ON p.sid = u.sid
Principals
http://msdn2.microsoft.com/en-us/library/ms181127(en-US,SQL.90).aspx
DB Users
http://msdn2.microsoft.com/en-us/library/ms190928(en-US,SQL.90).aspx

> Is it correct to say that the user is an instance of utilizing a
> login? I.e. "users" have logins.
Logins can be granted db access as database users, but they can even not..

>Some may use the same logins as
> others (for example several instances of an application). In which
> case each application trying to use SQL server is a user and their
> name/password pair correspond to their login?
an application is not a user or a login... in certain case an application
role can represent this scenario
(http://msdn2.microsoft.com/en-us/library/ms190998.aspx)

> Deleted user "fred" and login "fred".
> Recreated login Fred but made sure that I went to 'User Mapping' and
> checked the database that I wanted this Login to be able to access;
> Login was created and, like magic, a user appeared, also named
> 'fred', under the database.
it's correct... you created a login... then you granted that login db access
via a database user (in the defined database) mapping it to the original
login ...
if you only create a login without grating him/her access to your desired
database(s), that login will only access it if the predefined "guest"
database user is available (and usually that db user is not available for
security reason)...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.16.0 - DbaMgr ver 0.61.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||That URL does not use the work 'Login', only 'User'.
What is the difference between a login and a user?
Thanks,
Andrew
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:40dk3lF198jigU1@.individual.net...
> hi Andrew,
> Andrew Chalk wrote:
> perhaps my english is not as good as I thought.. :D
> perhaps here,
> http://msdn.microsoft.com/library/de...rity_4fol.asp,
> the architectural design is more clear..
> a SQL Server Login (both standard SQL Server login or a WinNT login) is
> just the very first brick in the wall in order to access a SQL Server
> instance... without that you can not log in and connect to a specified
> instance... it has a server wide range...
> a User is just a database user (can have the same name as the
> corresponding Login [Login is now somehaw obsolete, to be replaced by
> principals] but this is not mandatory even if it's recommended in order to
> minimize troubles :D) that, at creation time, is mapped via it's sid
> column (dbname.sys.sysusers.sid) to an existing login
> (master.sys.server_principals.sid)
> SELECT u.name AS [Name in DB], u.hasdbaccess ,
> p.name AS [Login Name]
> FROM master.sys.server_principals p JOIN sys.sysusers u
> ON p.sid = u.sid
> Principals
> http://msdn2.microsoft.com/en-us/library/ms181127(en-US,SQL.90).aspx
> DB Users
> http://msdn2.microsoft.com/en-us/library/ms190928(en-US,SQL.90).aspx
>
> Logins can be granted db access as database users, but they can even not..
>
> an application is not a user or a login... in certain case an application
> role can represent this scenario
> (http://msdn2.microsoft.com/en-us/library/ms190998.aspx)
>
> it's correct... you created a login... then you granted that login db
> access via a database user (in the defined database) mapping it to the
> original login ...
> if you only create a login without grating him/her access to your desired
> database(s), that login will only access it if the predefined "guest"
> database user is available (and usually that db user is not available for
> security reason)...
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.16.0 - DbaMgr ver 0.61.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
sql

creating a subscription via an application

Hello,

I am wondering if there is some sample code out there that shows how to create a subscription for a report on reporting services via a win app or if anyone has a better suggestion. We are wanting to have a report that resides on reporting services server be sent to a client via email subscription, but do not want the client to goto the actual website that host reporting services. Thanks in advance.

John

There are SOAP APIs that lets you create subscriptions programmatically. Is that what you are looking for? See the following link for details.

http://msdn2.microsoft.com/en-us/library/microsoft.wssux.reportingserviceswebservice.rsmanagementservice2005.reportingservice2005.createsubscription.aspx

Thanks,

Sharmila

|||This was it. Thank you!!

Creating a shared ODBC dsn for Access to SQL Server connection

I have a Microsoft Access application which uses linked SQL Server tables. I would like to create an ODBC DSN which would be available to all users so that I don't have to create a DSN on each machine. Can this be done? The Access application resides on a shared drive (Windows). Thanks for your help.

Don,

Will a File DSN work for what you are trying to do? I don't have much experience with those, so I can't help you more in that area.

I too had a similar problem as you do, and I didn't want to go through manually creating a DSN on everybody's machine. I found this script that will make your life slightly easier:

http://www.enterpriseitplanet.com/resources/scripts_win/article.php/3089341

With a little modification to the example included in that web page, I had all my users that needed a DSN ready to rock within a mouse-click!

Thanks,

Chuck

|||Thanks, Chuck. The script will certainly make the process easier if there isn't a way to create a global ODBC DSN. Appreciate the help.

Creating a Script out of Sample Data

SQL Server 2000

Is there any way to take sample data in my database and create an INSERT INTO script?

I have a commercial application that I would like to include sample data, and instead of restoring a backup like I am doing now, I would like to first run a script that creates the database, stored procedures, etc, then run a script that inserts sample data if the customer so chooses.

I know I can do this manually, but is there any way to create the script based on exisiting data?

TIA

--
Tim Morrison

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

Vehicle Web Studio - The easiest way to create and maintain your vehicle related website.
http://www.vehiclewebstudio.comTim,

You can use our free tool, QALite or ObjectScriptr to generate insert
statements. You can download it from site.

--
-oj
http://www.rac4sql.net

"Tim Morrison" <sales@.kjmsoftware.com> wrote in message
news:J%1Lb.761723$HS4.6022647@.attbi_s01...
SQL Server 2000

Is there any way to take sample data in my database and create an INSERT INTO
script?

I have a commercial application that I would like to include sample data, and
instead of restoring a backup like I am doing now, I would like to first run a
script that creates the database, stored procedures, etc, then run a script that
inserts sample data if the customer so chooses.

I know I can do this manually, but is there any way to create the script based
on exisiting data?

TIA

--
Tim Morrison

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

Vehicle Web Studio - The easiest way to create and maintain your vehicle related
website.
http://www.vehiclewebstudio.com|||"Tim Morrison" <sales@.kjmsoftware.com> wrote in message news:<J%1Lb.761723$HS4.6022647@.attbi_s01>...
> SQL Server 2000
> Is there any way to take sample data in my database and create an INSERT
> INTO script?
> I have a commercial application that I would like to include sample
> data, and instead of restoring a backup like I am doing now, I would
> like to first run a script that creates the database, stored procedures,
> etc, then run a script that inserts sample data if the customer so
> chooses.
> I know I can do this manually, but is there any way to create the script
> based on exisiting data?
> TIA
> --
> Tim Morrison
> ----------------------
> ---
> Vehicle Web Studio - The easiest way to create and maintain your vehicle
> related website.
> http://www.vehiclewebstudio.com
> --

You could use BCP/DTS to load the sample data from external files, but
if you want to use pure TSQL, then this is one possibility:

http://vyaskn.tripod.com/code.htm#inserts

Simon|||Looks like ObjectScriptr is just what im looking for.

Thanks,

Tim Morrison

"oj" <nospam_ojngo@.home.com> wrote in message
news:JN7Lb.781998$Fm2.760879@.attbi_s04...
> Tim,
> You can use our free tool, QALite or ObjectScriptr to generate insert
> statements. You can download it from site.
> --
> -oj
> http://www.rac4sql.net
>
> "Tim Morrison" <sales@.kjmsoftware.com> wrote in message
> news:J%1Lb.761723$HS4.6022647@.attbi_s01...
> SQL Server 2000
> Is there any way to take sample data in my database and create an INSERT
INTO
> script?
> I have a commercial application that I would like to include sample data,
and
> instead of restoring a backup like I am doing now, I would like to first
run a
> script that creates the database, stored procedures, etc, then run a
script that
> inserts sample data if the customer so chooses.
> I know I can do this manually, but is there any way to create the script
based
> on exisiting data?
> TIA
> --
> Tim Morrison
> -----------------------
--
> Vehicle Web Studio - The easiest way to create and maintain your vehicle
related
> website.
> http://www.vehiclewebstudio.com|||Hi !
Try http://www.sqlscripter.com

This tool is able to create INSERT, UPDATE and DELETE commands.

Nicksql

Creating a publication

hi
I was following the walkthrough "Creating a Mobile Application with SQL Server Mobile" and when I got to the point where you create a "local publication" I couldn't find the link "Local Publication" in my Object Explorer.

I read all the help in books online however id did not tell me how to bring that link there.

I did install the replication component using the CD installation. I have SQL Server 2005 Standard Edition and Visual Studio 2005

I also found the help "Using the Publication Wizard to Create a Publication" but did not know where to locate or start the wizard.

any help will be appreciated.

1. Run sqlwb.exe to launch SQL Management Studio.

2. Make a connection to the SQL server you have installed. So it is shown as the root node of the tree view in the Object Explorer.

3. Look for Replication\Local Publications alone the tree view hierarchy.

4. Right click on the "Local Publications" to show the context menu - you can select "New Publication ...".

Thanks.

This posting is provided "AS IS" with no warranties, and confers no rights.

|||hi
thanks for your reply.
the problem is there is no "Local Publication" folder. The only thing is "Local Subscription"

how can I add that folder?
|||

I suspect you can add that folder. In general SQL 2005 Standard SKU should be able to do publication. Please check one more time that you do have Standard SKU not SQL Express SKU. You can get this information as "select @.@.version".

Thanks.

Creating a new Integration Service Component

Good morning everybody,

I made a Java application to pre-process portuguese texts (stopwords, stemming, BOW creating, etc.)

I want to transform this application on a Integration Service component. I understand I will have to code this new component from zero. But I have no idea on how to start.

I'm reading and testing several tutorials on Integration Services that came with the SQL Server install package but none of them has clues on developing new components. These tutorials seams more focused on demostrate the (awesome) capabilities of Integration Services.

Is there any tutorials on how to implement new components to Integration Services ?

Any help will be very appreciated.

Thanks a lot,

-Renan Souza

Hello, Renan

A few Integration Services Samples are available at codeplex : http://www.codeplex.com/MSFTISProdSamples

you might also want to check this out: "SQL Server Integration Services (SSIS) Hands on Training - Creating Custom Components" at http://www.microsoft.com/downloads/details.aspx?familyid=1c2a7dd2-3ec3-4641-9407-a5a337bea7d3&displaylang=en

Hope this helps

|||

Thanks a lot for your prompt reply.

It was very helpfull, as usual.

Creating a new instance of SQL

Hi, We have a SQL server and I have installed a new instance for some
specific application database.
The installation go sucessfully coped all files and I can see the 2
instances on my Program Files folder and the 2 services running.
My question is : How is suppose I swich or see the 2 instances ? I have
open Entrerprise and I can't see the new instance?
I need to install from the CD others componentes like : Entreprise for the
new instances?
Thanks
Andrea
As far as the new instance is a "new server" ou have to register it within
the EM. Register the server with that naming convention
[NameoftheServer]\[InstanceName]. To list the available server you can issue
the following command at the command prompt: OSQL -L
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"Colores" <Colores@.discussions.microsoft.com> schrieb im Newsbeitrag
news:6BC321C2-4DF0-4B34-B018-5683536148C7@.microsoft.com...
> Hi, We have a SQL server and I have installed a new instance for some
> specific application database.
> The installation go sucessfully coped all files and I can see the 2
> instances on my Program Files folder and the 2 services running.
> My question is : How is suppose I swich or see the 2 instances ? I have
> open Entrerprise and I can't see the new instance?
> I need to install from the CD others componentes like : Entreprise for the
> new instances?
> Thanks
> Andrea
|||Hi,
No need to install the client components again. You could register the new
server name inside enterprise manager or in query analyzer you
could type the Hostname\sql servername (See the new sql server error logs
for the exact server name).
Thanks
Hari
SQL Server Mvp
"Colores" <Colores@.discussions.microsoft.com> wrote in message
news:6BC321C2-4DF0-4B34-B018-5683536148C7@.microsoft.com...
> Hi, We have a SQL server and I have installed a new instance for some
> specific application database.
> The installation go sucessfully coped all files and I can see the 2
> instances on my Program Files folder and the 2 services running.
> My question is : How is suppose I swich or see the 2 instances ? I have
> open Entrerprise and I can't see the new instance?
> I need to install from the CD others componentes like : Entreprise for the
> new instances?
> Thanks
> Andrea

Creating a new database beside 1 that currently exists

I am developing an application that will use msde or SQL sevrer depending on
if they are in the office or workign on the road. If they are on the road
they currently have MSDE installed by a different program. I also want to
use the msde service to run my database.
is it possible to run two databases on the same PC?
if it is do I need to know the sa password for msde? if i do is there a way
around it, I doubt that the company whos program runs on msde would want me
knowing the sa password
I have my database scripted in 3 sql files and I know that I will have to
use oSQL to execute them
If any one can point me how to do it or provide sample code I would be very
grateful
cheers
Comments inline.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"steven scaife" <stevenscaife@.discussions.microsoft.com> schrieb im
Newsbeitrag news:399E4B40-6312-423F-99B1-D3566EA9BEA6@.microsoft.com...
>I am developing an application that will use msde or SQL sevrer depending
>on
> if they are in the office or workign on the road. If they are on the road
> they currently have MSDE installed by a different program. I also want to
> use the msde service to run my database.
> is it possible to run two databases on the same PC?
Yeah, you are only stuck in to one instance.
> if it is do I need to know the sa password for msde? if i do is there a
> way
> around it, I doubt that the company whos program runs on msde would want
> me
> knowing the sa password
if you got windows auth. activated you can easily log on to the msde
with an administrive account, they are usally members of the system
administrator group, if they disabled it, you can reenable that via:
HKEY_LOCAL_MACHINE\SOFTWARE\MiXcrosoft\
MicrosoftSQLServer\<instance_nXame>\MSSQLServer\Lo ginMode
auf "1" (Windows Auth)
http://www.microsoft.com/sql/tXechin...tion/MaXy3.asp

> I have my database scripted in 3 sql files and I know that I will have to
> use oSQL to execute them
> If any one can point me how to do it or provide sample code I would be
> very
> grateful
You can call external scripts via the switch -i
OSQL -iC:\Test.sql

> cheers
|||thanks for the reply
Am I right in assuming that I just re-run the msde2000 setup but name an
instance
eg. C:\MSDERelA\setup.exe sapwd="<password>" INSTANCENAME="Kaisen"
TARGETDIR="C:\Program files\KaisenDB\"
then I can play around with it using my SA password and its totally seperate
but just using the sql service
sorry if i sound dumb but I dont have much experience with administering
msde, just running from the bits i picked up
"Jens Sü?meyer" wrote:

> Comments inline.
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "steven scaife" <stevenscaife@.discussions.microsoft.com> schrieb im
> Newsbeitrag news:399E4B40-6312-423F-99B1-D3566EA9BEA6@.microsoft.com...
> Yeah, you are only stuck in to one instance.
> if you got windows auth. activated you can easily log on to the msde
> with an administrive account, they are usally members of the system
> administrator group, if they disabled it, you can reenable that via:
> HKEY_LOCAL_MACHINE\SOFTWARE\MiXcrosoft\
> MicrosoftSQLServer\<instance_nXame>\MSSQLServer\L oginMode
> auf "1" (Windows Auth)
> http://www.microsoft.com/sql/tXechi...ion/MaXy3.asp
>
> You can call external scripts via the switch -i
> OSQL -iC:\Test.sql
>
>
|||hi Jens,
Jens Smeyer wrote:
> if you got windows auth. activated you can easily log on to the
> msde with an administrive account, they are usally members of the
> system administrator group, if they disabled it, you can reenable
> that via:
> HKEY_LOCAL_MACHINE\SOFTWARE\MiXcrosoft\
> MicrosoftSQLServer\<instance_nXame>\MSSQLServer\Lo ginMode
> auf "1" (Windows Auth)
Windows authentication can not be disabled even in the value of "0" is
reported as SQLDMOSecurity_Normal (Allow SQL Server Authentication only)..
anyway, you'd require administrative WinNT privileges to modify that
registry settings...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.12.0 - DbaMgr ver 0.58.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||hi Steven,
steven scaife wrote:
> thanks for the reply
> Am I right in assuming that I just re-run the msde2000 setup but name
> an instance
> eg. C:\MSDERelA\setup.exe sapwd="<password>" INSTANCENAME="Kaisen"
> TARGETDIR="C:\Program files\KaisenDB\"
> then I can play around with it using my SA password and its totally
> seperate but just using the sql service
MSDE installs by default disable standard SQL Server connection and only
allowing trusted ones...
to modify this behaviour at install time you have to provide the
SECURITYMODE=SQL paramenter to the setup.exe boostrap installer... or,
after install, as already discussed, you can modifiy a registry key..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.12.0 - DbaMgr ver 0.58.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

Creating a Mobile Application with SQL Server Mobile - FIX

This is a great tutorial and it's a shame one of the more important steps was missed.

In the “Create the snapshot user” section you you find the steps to create the snapshot_agent account. Then in the “Create the snapshot folder” section you find the share and folder permissions. However, at no point do the instructions advise you about adding the snapshot_agent to the SQL Server Logins. The result is that agent cannot perform the initial snapshot but you won't find this out until 50 steps later after Step 10 in the section “Create a new subscription".

To get back on track, openthe Object Explorer's Security section and add the snapshot_agent to your logins. Then using the "User Mappings", set an appropriate level for the SQLMobile database role. Once completed you then need to run the agent.

Right-click the SQLMobile publication you created and select "View Snapshot Agent status". From that dialog you can select "Start" to run the agent. When it completes, you can return to the tutorial section "Create a new subscription" and continue with the tutorial.

that's one way to do it and thanks for pointing out the omission. a more common approach is to make sure the account that the snapshot agent runs as is granted permissions on the publication, the database engine, and the database.

Darren

|||

Darren,

In order to grant permissions on the database engine you need to create the login. And just granting permission on the database engine, database and the publication doesn't seem do it. You still need to asign a "Server Role" to the login. My first guess was "processadmin"; however, I tried a number of different combinations without success. Only when I set the snaphot-agent to the role of "sysadmin" was I able to get the agent to complete the process of creating the initial snapshot.

|||

that's true if the account you chose to run SQL Agent as isn't already recognized in the sysadmin role as a SQL Server login. for the average developer trying to get merge repl working the first time, SQL Server and IIS are both running on the machine that the device is connected to via ActiveSync. what I was trying to say in my last post is, whatever account you log on to you machine with, as long as it is an Administrator account, this is a good account to run the SQL Agent under. That makes the permissions issues easier to configure on SQL Server as you only have to grant that login appropriate permissions on the pub and the db. Of course you also grant permissions to the IUSR_{your machine name} account if using anonymous auth.

in a production environment, some other account should be used and as you correctly noted, this account needs to either be sysadmin on SQL Server or be granted db_reader and db_writer on the pub and published database. typically, IIS and SQL Server are on separate boxes in this scenario and a domain account is used that both machines can recognize.

Darren

|||

It is less than practical to use the "sysadmin" role in this instance. Review of the documentation shows that the minimum permissions for a "pull subscription" require the login to be associated with a user in the distribution database. At minimum be a member of the db_owner fixed database role in the distribution database.

So I have now removed the sysadmin role from the computername\snapshot_agent and added it as a user in the distribution database with the db_owner role. These permissions allow the agent to run the replication snapshot job.

Now, I have no indication that others are able to run this tutorial without specifically setting the above permission. If they are, then perhaps some other settings related to the wizards or replication itself are necessary.

The real point here is that given a tutorial which identifies each step in setting up replication (including specific names and permisions) should work according to the names/permissions identified. So lets fix the omission and move on.

Note: specific infornation is available at http://msdn2.microsoft.com/en-us/library/ms151868(d=ide).aspx

Creating a Mobile Application with SQL Server Compact Edition

Hello!!

I completed that example that I pasted in the subject part and when I try to synchronize my mobile database, the data from the server appear in my pocket pc; but when i refresh the data on my pocket pc they do not show on the server.

Can anyone give me a hand?

thanks

This sample is a download only scenario. Are you still running this code in form_Load ?:

Code Snippet

private void Form1_Load(object sender, EventArgs e)
{
DeleteDB();
Sync();

// TODO: Delete this line of code.
this.flightDataTableAdapter.Fill(this.sqlmobileDataSet.FlightData);
// TODO: Delete this line of code.
this.membershipDataTableAdapter.Fill(this.sqlmobileDataSet.MembershipData);
}

This sample allows two way sync: http://technet.microsoft.com/en-us/library/ms346580.aspx

and so does this Hands On Lab: http://msdn2.microsoft.com/en-us/library/aa454892.aspx

|||

I will try to make those example.

Thank you very much.

Creating a Mobile Application with SQL Server Compact Edition

Hello!!

I completed that example that I pasted in the subject part and when I try to synchronize my mobile database, the data from the server appear in my pocket pc; but when i refresh the data on my pocket pc they do not show on the server.

Can anyone give me a hand?

thanks

This sample is a download only scenario. Are you still running this code in form_Load ?:

Code Snippet

private void Form1_Load(object sender, EventArgs e)
{
DeleteDB();
Sync();

// TODO: Delete this line of code.
this.flightDataTableAdapter.Fill(this.sqlmobileDataSet.FlightData);
// TODO: Delete this line of code.
this.membershipDataTableAdapter.Fill(this.sqlmobileDataSet.MembershipData);
}

This sample allows two way sync: http://technet.microsoft.com/en-us/library/ms346580.aspx

and so does this Hands On Lab: http://msdn2.microsoft.com/en-us/library/aa454892.aspx

|||

I will try to make those example.

Thank you very much.