Create table script without drop

Hello all!
I'm currently working on a project where we have several customers with
the same application. The database is constantly being changed and it's
hard to keep track of all the changes from all the versions in the
customers' systems.
Usually I create the changes script every time I alter any of the tables
but there is always a risk of loosing them. I wonder if there is anyway
of creating a script that updates all the tables instead of dropping and
creating them all, so our customers won't loose the database records.
Thanks in advance,
Hugo MadureiraHugo,
You can use the ALTER TABLE Statement instead of DROP TABLE & CREATE
TABLE.
eg.
Alter Table MyTable
Add MyColumn varchar(10)
HTH
Barry|||You can get rid of a lot of headaches by using SQL Compare.
www.red-gate.com
"Hugo Madureira" <hugomadureira@.hotmail.com> wrote in message
news:%232216JpJGHA.3696@.TK2MSFTNGP15.phx.gbl...
> Hello all!
> I'm currently working on a project where we have several customers with
> the same application. The database is constantly being changed and it's
> hard to keep track of all the changes from all the versions in the
> customers' systems.
> Usually I create the changes script every time I alter any of the tables
> but there is always a risk of loosing them. I wonder if there is anyway of
> creating a script that updates all the tables instead of dropping and
> creating them all, so our customers won't loose the database records.
>
> Thanks in advance,
> Hugo Madureira|||Of course, this gets more complex than just adding columns. Such as
adding/removing columns with check constraints, foreign key constraints,
primary key constraints, unique constraints, computed columns, changing
datatypes/scale/precision, etc. Not all table changes are adding columns.
"Barry" <barry.oconnor@.singers.co.im> wrote in message
news:1138731694.928227.324210@.z14g2000cwz.googlegroups.com...
> Hugo,
> You can use the ALTER TABLE Statement instead of DROP TABLE & CREATE
> TABLE.
> eg.
> Alter Table MyTable
> Add MyColumn varchar(10)
>
> HTH
> Barry
>|||Ahh now I understand what he *actually* wanted... oops!
Barry|||I was looking for a possible way of doing that with Enterprise Manager
manager, in a way that it could be done automatically.
When I use Enterprise Manager to create a table script, it drops the
table and re-creates it. That causes data loss in the database.
If there is no way of doing that, is it possible to easily edit the
script generated by Enterprise Manager to do that?
Barry wrote:
> Hugo,
> You can use the ALTER TABLE Statement instead of DROP TABLE & CREATE
> TABLE.
> eg.
> Alter Table MyTable
> Add MyColumn varchar(10)
>
> HTH
> Barry
>

create table permission denied

Hi,

i run an asp.net application which uses sql server express.
i defined a login 'aspnet' (IIS 5.0) and for the specific database, an user
'aspnet' with following roles:
db_datareader and db_datawriter.

Now, any user who uses that application must also be able to create
programmatically tables in that database. My question is: which role do i
have to give to user 'aspnet'?

I use Studio Management express.

Thanks
Tartuffe

The answer depends on which version of SQL Server you are using, since SQL Server 2005 dramatically enhanced permissions in the database. Which version?

Also, must the user be able to create ANY table in the database, or only a specific table? I think the former, but please confirm.

Don

|||

Hi, thanks for replying.

I use sql server 2005 express with Studio Management express.

Each windows-account in the domain in our organization who starts the application creates automatically (in code-behnd) a table with his personal (unique) number in the organization (e.g. L0564).

This happens only the first time he starts the application. So each member has his own table.

We use IIS 5.1, so the account which runs under asp.net is ASPNET. For another application, i defined a login in Studio Management and then for the database of that application, i defined a user 'aspnet' with following roles: db_reader and db_writer (i didn't use schema's because it's not very clear to me ..). This works, but there was no need to cerate a table.

I did the same for this new application +I db_owner and then it works. But i think it's probably too many privileges ... So: my question is: which privileges to give to 'aspnet' and how to do that in Studio Management?

Thanks

|||

You need the dbowner... only then you will be able to do the specific operations like creating tables and other things. DBWrite and Read will allow you to do simple updates insert and deletes.

|||

Thanks. I'll try.

If you don't mind, .. what if the ASPNET account must also be able to create databases? Is it suffisant with db_owner only?

|||

Yes i think so .. it doesn't matter which account it is .. till the time you map correct roles

|||

i tried with db_owner and Aspnet can create tables programmatically.

But Aspnet cannot create a new database with only db_owner.

|||

you need to be sysadmin for that

|||

Thanks

sql

Create table from web application

i am creating a model in which the control panel i am building for a web application, i can write sql statements in a textbox and the query will be done in my application. But i am have a problem creating a table.

this error of "Create table permission not granted in the sql server" pops up whenever i want to execute the create table sql statements from my web page.

How can i permit my sql server to allow creation of tables from my web application.

Thanks

It all depends on the type of authentication that you are using for the app. The users of your web app will have to have an account capable of creating tables or whatever else you want them to do. What ever it is make an user account just for them. Please do not give the the sa account or you may be sorry later.|||

i want to know how i can authenticate the user to create the table from the server. I know i have to authenticate the user but what it is procedure to do that. I want to know how i can achieve that in Sql 2000

Thank you

|||Ok, What you can do now is in your connection string use the user and pass that the end user supplies. This will need to be a username and pass that you have supplied to them that is valid for the database. When the user logs in just add their user and pass to session so that you can call it back whenever you need it. Then when you create a connection for that user in your connection string use (string)Session["UserName"] and (string)Session["Password"] for the user and pass. To add their user and pass to the Session just use Session.Add("UserName",txtThetextboxthattheyentertheirusernamein.Text); and Session.Add("Password",txtThetextboxthattheyenteredtheirpasswordin.Text); I would put my connection properties in a class so that you can call them whenever and make your method so that it will accept the user and pass that you need to through it. Like this:
[pre]Public bool DBConnection(string Username,String Password)
{
bool connected = false;
Try
{
your connection params using the strings UserName and Password
connected = true;
}
Catch
{
connected = false;
}
Return connected;
}[/pre]
Then you can check the return value to make sure that they connected and use the connection for whatever you want to process. To call it up just type DBConnection((string)Session["UserName"],(string)Session["Password"])
if you need to check it set the return value to a bool and check for true or false
[pre]
bool didconnect = DBConnection((string)Session["UserName"],(string)Session["Password"])
if (didconnect != false)
{
Whatever stuff you need to run against the db;
}
else
{
Errormessage to the user;
}
[/pre]sql

CREATE SYMMETRIC KEY

Hi,
I am in the process of switching an application from SQL Server 2000 to SQL
Server 2005, with the main purpose to use the encryption capabilities of SQL
Server 2005.
To test it out using encryption, I created a database, TestEncrypt, using
all the defaults.
I then worked with the script from the help file in encryption[SQL Serve
r] /
columns / Simple Symmetric Encryption.
When I run
CREATE SYMMETRIC KEY SSN_Key_01
WITH ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE HumanResources037;
GO
from the script, I get the following error:
Msg 15314, Level 16, State 1, Line 2
Either no algorithm has been specified or the bitlength and the algorithm
specified for the key are not available in this installation of Windows.
When I change this to
CREATE SYMMETRIC KEY SSN_Key_01
WITH ALGORITHM = DES
ENCRYPTION BY CERTIFICATE HumanResources037;
GO
it completes successfully.
However, the Decrypted ID (here is the output):
NationalIDNumber: 002020002
Decrypted ID Number: 2
This does not make sense (the decrypted value should be the same as the
original value).
Full script is below.
Can you tell me why the AES_256 doesn't work (I'm on an XP Pro machine) and
why the decrypted value is different from the original value?
Thanks.
Bob
/* To prevent any potential data loss issues, you should review this script
in detail before running it outside the context of the database designer.*/
BEGIN TRANSACTION
Use TestEncrypt
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
CREATE TABLE dbo.Employee
(
NationalIDNumber varchar(50) NULL
) ON [PRIMARY]
GO
COMMIT
Use TestEncrypt
GO
INSERT INTO dbo.Employee (NationalIDNumber) SELECT '002020002'
GO
SELECT * FROM dbo.Employee
GO
--If there is no master key, create one now
IF NOT EXISTS
(SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)
CREATE MASTER KEY ENCRYPTION BY
PASSWORD =
'23987hxJKL95QYV4369#ghf0%94467GRdkjuw54
ie5y01478dDkjdahflkujaslekjg5k3fd117
r$$#1946kcj$n44ncjhdlj'
GO
CREATE CERTIFICATE HumanResources037
WITH SUBJECT = 'Employee Social Security Numbers';
GO
CREATE SYMMETRIC KEY SSN_Key_01
WITH ALGORITHM = DES
ENCRYPTION BY CERTIFICATE HumanResources037;
GO
USE [TestEncrypt];
GO
-- Create a column in which to store the encrypted data
ALTER TABLE Employee
ADD EncryptedNationalIDNumber varbinary(128);
GO
-- Open the symmetric key with which to encrypt the data
OPEN SYMMETRIC KEY SSN_Key_01
DECRYPTION BY CERTIFICATE HumanResources037;
-- Encrypt the value in column NationalIDNumber with symmetric
-- key SSN_Key_01. Save the result in column EncryptedNationalIDNumber.
UPDATE Employee
SET EncryptedNationalIDNumber = EncryptByKey(Key_GUID('SSN_Key_01'),
NationalIDNumber);
GO
-- Verify the encryption.
-- First, open the symmetric key with which to decrypt the data
OPEN SYMMETRIC KEY SSN_Key_01
DECRYPTION BY CERTIFICATE HumanResources037;
GO
-- Now list the original ID, the encrypted ID, and the
-- decrypted ciphertext. If the decryption worked, the original
-- and the decrypted ID will match.
SELECT NationalIDNumber, EncryptedNationalIDNumber
AS "Encrypted ID Number",
CONVERT(nvarchar, DecryptByKey(EncryptedNationalIDNumber))
AS "Decrypted ID Number"
FROM Employee;
GOI found the problem on the on the encryption inconsistency, still would like
to know about the AES_256. Thanks.
"Gerhard" wrote:

> Hi,
> I am in the process of switching an application from SQL Server 2000 to SQ
L
> Server 2005, with the main purpose to use the encryption capabilities of S
QL
> Server 2005.
> To test it out using encryption, I created a database, TestEncrypt, using
> all the defaults.
> I then worked with the script from the help file in encryption[SQL Ser
ver] /
> columns / Simple Symmetric Encryption.
> When I run
> CREATE SYMMETRIC KEY SSN_Key_01
> WITH ALGORITHM = AES_256
> ENCRYPTION BY CERTIFICATE HumanResources037;
> GO
> from the script, I get the following error:
> Msg 15314, Level 16, State 1, Line 2
> Either no algorithm has been specified or the bitlength and the algorithm
> specified for the key are not available in this installation of Windows.
> When I change this to
> CREATE SYMMETRIC KEY SSN_Key_01
> WITH ALGORITHM = DES
> ENCRYPTION BY CERTIFICATE HumanResources037;
> GO
> it completes successfully.
> However, the Decrypted ID (here is the output):
> NationalIDNumber: 002020002
> Decrypted ID Number: 2
> This does not make sense (the decrypted value should be the same as the
> original value).
> Full script is below.
> Can you tell me why the AES_256 doesn't work (I'm on an XP Pro machine) an
d
> why the decrypted value is different from the original value?
> Thanks.
> Bob
>
> /* To prevent any potential data loss issues, you should review this scrip
t
> in detail before running it outside the context of the database designer.*
/
> BEGIN TRANSACTION
> Use TestEncrypt
> SET QUOTED_IDENTIFIER ON
> SET ARITHABORT ON
> SET NUMERIC_ROUNDABORT OFF
> SET CONCAT_NULL_YIELDS_NULL ON
> SET ANSI_NULLS ON
> SET ANSI_PADDING ON
> SET ANSI_WARNINGS ON
> COMMIT
> BEGIN TRANSACTION
> GO
> CREATE TABLE dbo.Employee
> (
> NationalIDNumber varchar(50) NULL
> ) ON [PRIMARY]
> GO
> COMMIT
> Use TestEncrypt
> GO
> INSERT INTO dbo.Employee (NationalIDNumber) SELECT '002020002'
> GO
> SELECT * FROM dbo.Employee
> GO
> --If there is no master key, create one now
> IF NOT EXISTS
> (SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)
> CREATE MASTER KEY ENCRYPTION BY
> PASSWORD =
> '23987hxJKL95QYV4369#ghf0%94467GRdkjuw54
ie5y01478dDkjdahflkujaslekjg5k3fd1
17r$$#1946kcj$n44ncjhdlj'
> GO
> CREATE CERTIFICATE HumanResources037
> WITH SUBJECT = 'Employee Social Security Numbers';
> GO
> CREATE SYMMETRIC KEY SSN_Key_01
> WITH ALGORITHM = DES
> ENCRYPTION BY CERTIFICATE HumanResources037;
> GO
> USE [TestEncrypt];
> GO
> -- Create a column in which to store the encrypted data
> ALTER TABLE Employee
> ADD EncryptedNationalIDNumber varbinary(128);
> GO
> -- Open the symmetric key with which to encrypt the data
> OPEN SYMMETRIC KEY SSN_Key_01
> DECRYPTION BY CERTIFICATE HumanResources037;
> -- Encrypt the value in column NationalIDNumber with symmetric
> -- key SSN_Key_01. Save the result in column EncryptedNationalIDNumber.
> UPDATE Employee
> SET EncryptedNationalIDNumber = EncryptByKey(Key_GUID('SSN_Key_01'),
> NationalIDNumber);
> GO
> -- Verify the encryption.
> -- First, open the symmetric key with which to decrypt the data
> OPEN SYMMETRIC KEY SSN_Key_01
> DECRYPTION BY CERTIFICATE HumanResources037;
> GO
> -- Now list the original ID, the encrypted ID, and the
> -- decrypted ciphertext. If the decryption worked, the original
> -- and the decrypted ID will match.
> SELECT NationalIDNumber, EncryptedNationalIDNumber
> AS "Encrypted ID Number",
> CONVERT(nvarchar, DecryptByKey(EncryptedNationalIDNumber))
> AS "Decrypted ID Number"
> FROM Employee;
> GO
>
>
>
>
>|||Depends on the version of Windows you're running. Different versions have
different variations of CryptoAPI. I believe all versions of CryptoAPI have
some basic algorithms available (RC2, DES), but AES is not available on all
platforms.
"Gerhard" <acsla@.community.nospam> wrote in message
news:C771AB35-74D3-40D5-A94C-33C9F08A40FB@.microsoft.com...[vbcol=seagreen]
>I found the problem on the on the encryption inconsistency, still would
>like
> to know about the AES_256. Thanks.
> "Gerhard" wrote:
>|||AES is only supported by SQL Server on Windows 2003.
Laurentiu Cristofor [MSFT]
Software Design Engineer
SQL Server Engine
http://blogs.msdn.com/lcris/
This posting is provided "AS IS" with no warranties, and confers no rights.
"Mike C#" <xyz@.xyz.com> wrote in message
news:uYZddkbhGHA.4892@.TK2MSFTNGP02.phx.gbl...
> Depends on the version of Windows you're running. Different versions have
> different variations of CryptoAPI. I believe all versions of CryptoAPI
> have some basic algorithms available (RC2, DES), but AES is not available
> on all platforms.
> "Gerhard" <acsla@.community.nospam> wrote in message
> news:C771AB35-74D3-40D5-A94C-33C9F08A40FB@.microsoft.com...
>sql

Create Subscription Without Administrator Priveleges

Hi,
I have an application that attempts to create a subscription through the
Reporting Services API. I'm currently getting an error that states the
"'System.Web.Services.Protocols.SoapException: A subscription delivery error
has occurred. --> A subscription delivery error has occurred. --> The value
of parameter ''extensionSettings'' is not valid. Check the documentation for
information about valid values. --> The account you are using does not have
administrator privileges. A subscription cannot be created for
testuser@.company.com"
I did a bit more poking around and found that if I added TestUser, the
currently logged in account, to the administrator group of the machine that
has the Reporting Services webserver, I no longer had this problem and I was
able to create the subscription through the API. My domain users are all set
to Browsers through the Report Manager web interface, but I doubt this
matters since it is separate from the API.
I'm devising a work around for this problem now that doesn't use the default
credentials for the Reporting Service web service, but uses credentials of a
user who is an administrator of that machine. I believe this will work, but
I was wondering how else I might give users the privelege of creating
subscriptions without being administrators of the machine. Also, what other
methods (i.e. delete subscription) are priveleged to only administrators of
the machine?
Thanks, JoelTurns out they just can't create subscriptions for anybody else but
themselves.
Bugger! And I built a great little address book tool too!
-Joel
"Joel Rumerman" <JRumerman@.prometheuslabs.com> wrote in message
news:e$OO684xEHA.2876@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I have an application that attempts to create a subscription through the
> Reporting Services API. I'm currently getting an error that states the
> "'System.Web.Services.Protocols.SoapException: A subscription delivery
> error has occurred. --> A subscription delivery error has occurred. -->
> The value of parameter ''extensionSettings'' is not valid. Check the
> documentation for information about valid values. --> The account you are
> using does not have administrator privileges. A subscription cannot be
> created for testuser@.company.com"
> I did a bit more poking around and found that if I added TestUser, the
> currently logged in account, to the administrator group of the machine
> that has the Reporting Services webserver, I no longer had this problem
> and I was able to create the subscription through the API. My domain users
> are all set to Browsers through the Report Manager web interface, but I
> doubt this matters since it is separate from the API.
> I'm devising a work around for this problem now that doesn't use the
> default credentials for the Reporting Service web service, but uses
> credentials of a user who is an administrator of that machine. I believe
> this will work, but I was wondering how else I might give users the
> privelege of creating subscriptions without being administrators of the
> machine. Also, what other methods (i.e. delete subscription) are
> priveleged to only administrators of the machine?
> Thanks, Joel
>|||Hi Joel,
I am looking into this issue and will update you as soon as possible when I
find any valueable things to add.
Thank you for your patience and corporation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||All,
Following a lead, I checked the output of a GetPermissions call and this is
what I, an adminstrator of the webserver and a content manager Reporting
Services received for output.
Create Folder
Delete
Read Properties
Update Properties
Create Report
Create Resource
Create data source
Read Security Policies
Update Security Policies
The example in RS BOL states that I should see more permissions :
Delete
Execute and View
Read Properties
Update Properties
Update Parameters
Read Data Sources
Update Data Sources
Read Report Definition
Update Report Definition
Create Subscription
Delete Subscription
Read Subscription
Delete Report History
Update Subscription
Create Any Subscription
Delete Any Subscription
Read Any Subscription
Read Security Policies
Update Security Policies
Update Any Subscription
Read Policy
Update Policy
List Report History
Create Report History
Execute
Create Link
I ran it again with a user that had Browser permissions only and who wasn't
an administrator and it returned
Read Properties
My main concern is where are the
Create Subscription
Delete Subscription
Read Subscription
permissions'? If only for me, but also for TestUser.
Thx, Joel
""Michael Cheng [MSFT]"" <v-mingqc@.online.microsoft.com> wrote in message
news:puNmpb$xEHA.3956@.cpmsftngxa10.phx.gbl...
> Hi Joel,
> I am looking into this issue and will update you as soon as possible when
> I
> find any valueable things to add.
>
> Thank you for your patience and corporation. If you have any questions or
> concerns, don't hesitate to let me know. We are always here to be of
> assistance!
>
> Sincerely yours,
> Michael Cheng
> Online Partner Support Specialist
> Partner Support Group
> Microsoft Global Technical Support Center
> ---
> Get Secure! - http://www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>|||So it turns out that I had my path set to the root directory in the sample
application
Dim permissions As [String]() = rs.GetPermissions("/")
If change it to point directly to a report I get back the expected
permissions.
Dim permissions As [String]() = rs.GetPermissions("/Billing/Billing Group
Activity Detail")
Joel
"Joel Rumerman" <JRumerman@.prometheuslabs.com> wrote in message
news:eq0fb2AyEHA.3120@.TK2MSFTNGP12.phx.gbl...
> All,
> Following a lead, I checked the output of a GetPermissions call and this
> is what I, an adminstrator of the webserver and a content manager
> Reporting Services received for output.
> Create Folder
> Delete
> Read Properties
> Update Properties
> Create Report
> Create Resource
> Create data source
> Read Security Policies
> Update Security Policies
> The example in RS BOL states that I should see more permissions :
> Delete
> Execute and View
> Read Properties
> Update Properties
> Update Parameters
> Read Data Sources
> Update Data Sources
> Read Report Definition
> Update Report Definition
> Create Subscription
> Delete Subscription
> Read Subscription
> Delete Report History
> Update Subscription
> Create Any Subscription
> Delete Any Subscription
> Read Any Subscription
> Read Security Policies
> Update Security Policies
> Update Any Subscription
> Read Policy
> Update Policy
> List Report History
> Create Report History
> Execute
> Create Link
> I ran it again with a user that had Browser permissions only and who
> wasn't an administrator and it returned
> Read Properties
> My main concern is where are the
> Create Subscription
> Delete Subscription
> Read Subscription
>
> permissions'? If only for me, but also for TestUser.
> Thx, Joel
>
> ""Michael Cheng [MSFT]"" <v-mingqc@.online.microsoft.com> wrote in message
> news:puNmpb$xEHA.3956@.cpmsftngxa10.phx.gbl...
>> Hi Joel,
>> I am looking into this issue and will update you as soon as possible when
>> I
>> find any valueable things to add.
>>
>> Thank you for your patience and corporation. If you have any questions or
>> concerns, don't hesitate to let me know. We are always here to be of
>> assistance!
>>
>> Sincerely yours,
>> Michael Cheng
>> Online Partner Support Specialist
>> Partner Support Group
>> Microsoft Global Technical Support Center
>> ---
>> Get Secure! - http://www.microsoft.com/security
>> This posting is provided "as is" with no warranties and confers no
>> rights.
>> Please reply to newsgroups only, many thanks!
>|||For all to learn from ...
The permissions on subscriptions don't make sense. It seems the user is
blocked from creating a subscription for anybody only at the Report Manager
web site level, not at the API level if SendEmailToUserAlias=False. This is
evident by the CC and BCC text boxes not being visible when the user is not
an administrator of the machine RS is running on, and by RS NOT throwing an
error when the CC and BCC fields are used in the extensionParameters array
through the API call to CreateSubscription. However, the comments field is
not allowed through an API call or shown on the website and throws an error
if its use is attempted. It seems MSFT has implemented most security only at
the presentation tier (the web site), not the middle-tier. (However,
documentation on the all of the security is lacking.)
Thx, Joel
"Joel Rumerman" <JRumerman@.prometheuslabs.com> wrote in message
news:eZRrC7AyEHA.2348@.TK2MSFTNGP12.phx.gbl...
> So it turns out that I had my path set to the root directory in the sample
> application
> Dim permissions As [String]() = rs.GetPermissions("/")
> If change it to point directly to a report I get back the expected
> permissions.
> Dim permissions As [String]() = rs.GetPermissions("/Billing/Billing Group
> Activity Detail")
> Joel
> "Joel Rumerman" <JRumerman@.prometheuslabs.com> wrote in message
> news:eq0fb2AyEHA.3120@.TK2MSFTNGP12.phx.gbl...
>> All,
>> Following a lead, I checked the output of a GetPermissions call and this
>> is what I, an adminstrator of the webserver and a content manager
>> Reporting Services received for output.
>> Create Folder
>> Delete
>> Read Properties
>> Update Properties
>> Create Report
>> Create Resource
>> Create data source
>> Read Security Policies
>> Update Security Policies
>> The example in RS BOL states that I should see more permissions :
>> Delete
>> Execute and View
>> Read Properties
>> Update Properties
>> Update Parameters
>> Read Data Sources
>> Update Data Sources
>> Read Report Definition
>> Update Report Definition
>> Create Subscription
>> Delete Subscription
>> Read Subscription
>> Delete Report History
>> Update Subscription
>> Create Any Subscription
>> Delete Any Subscription
>> Read Any Subscription
>> Read Security Policies
>> Update Security Policies
>> Update Any Subscription
>> Read Policy
>> Update Policy
>> List Report History
>> Create Report History
>> Execute
>> Create Link
>> I ran it again with a user that had Browser permissions only and who
>> wasn't an administrator and it returned
>> Read Properties
>> My main concern is where are the
>> Create Subscription
>> Delete Subscription
>> Read Subscription
>>
>> permissions'? If only for me, but also for TestUser.
>> Thx, Joel
>>
>> ""Michael Cheng [MSFT]"" <v-mingqc@.online.microsoft.com> wrote in message
>> news:puNmpb$xEHA.3956@.cpmsftngxa10.phx.gbl...
>> Hi Joel,
>> I am looking into this issue and will update you as soon as possible
>> when I
>> find any valueable things to add.
>>
>> Thank you for your patience and corporation. If you have any questions
>> or
>> concerns, don't hesitate to let me know. We are always here to be of
>> assistance!
>>
>> Sincerely yours,
>> Michael Cheng
>> Online Partner Support Specialist
>> Partner Support Group
>> Microsoft Global Technical Support Center
>> ---
>> Get Secure! - http://www.microsoft.com/security
>> This posting is provided "as is" with no warranties and confers no
>> rights.
>> Please reply to newsgroups only, many thanks!
>>
>

Create stored procs in new DB from within a stored proc?

In our application, we dynamically create new databases using a stored
procedure. Each new database must have a few required stored procedures
created in it. Since SQL Server does not allow the specification of a
different database context for creation of procedures or functions, the
current workaround is to define a system stored procedure in Master that
creates the stored procs. We call this in the context of the new DB after it
is created. This works fine, but an approach that does not require the use o
f
any system databases would be preferred.
Is there a better way to accomplish this without Master or Model (in
pseudocode):
create procedure usp_NewDB
@.DBName
as
begin
create database @.DBName
create procedure @.DBName.dbo.SP1 as ...
create procedure @.DBName.dbo.SP2 as ...
endYou cannot do this with a variable in that way...
http://www.sommarskog.se/dynamic_sql.html
"ScottL" <ScottL@.community.nospam> wrote in message
news:C1C4D5F3-F2C8-4970-9368-742D1D55FEBE@.microsoft.com...
> In our application, we dynamically create new databases using a stored
> procedure. Each new database must have a few required stored procedures
> created in it. Since SQL Server does not allow the specification of a
> different database context for creation of procedures or functions, the
> current workaround is to define a system stored procedure in Master that
> creates the stored procs. We call this in the context of the new DB after
> it
> is created. This works fine, but an approach that does not require the use
> of
> any system databases would be preferred.
> Is there a better way to accomplish this without Master or Model (in
> pseudocode):
> create procedure usp_NewDB
> @.DBName
> as
> begin
> create database @.DBName
> create procedure @.DBName.dbo.SP1 as ...
> create procedure @.DBName.dbo.SP2 as ...
> end
>|||Yes, I know. That's why I said it was pseudocode. The issue is not one of
dynamic SQL, it is of creating a stored procedure in a different database
context. Regardless of dynamic SQL, the syntax CREATE PROCEDURE
<DBName>.dbo.<SPName> is not valid, since you cannot specify the database
name with CREATE PROCEDURE. Let me rephrase it for you more simply:
How can I create a stored procedure in Database_B from a stored procedure
running in Database_A?
"Aaron Bertrand [SQL Server MVP]" wrote:

> You cannot do this with a variable in that way...
> http://www.sommarskog.se/dynamic_sql.html
>
> "ScottL" <ScottL@.community.nospam> wrote in message
> news:C1C4D5F3-F2C8-4970-9368-742D1D55FEBE@.microsoft.com...
>
>|||> Let me rephrase it for you more simply:
> How can I create a stored procedure in Database_B from a stored procedure
> running in Database_A?
Let me answer for you "more simply":
EXEC('USE '+@.DBName+'; CREATE PROCEDURE ... ');|||Aaron Bertrand [SQL Server MVP] wrote:
> Let me answer for you "more simply":
>
Aaron, you're not getting annoyed are you :)
Remember people should EXPECT to get their questions answered promptly
and in a way they feel is appropriate, we have to make an effort
keeping them happy ;)
/impslayer, aka Birger Johansson|||Strange response, but thanks anyway. This syntax will not work and actually
executing it would result in:
'CREATE/ALTER PROCEDURE' must be the first statement in a query batch.
"Aaron Bertrand [SQL Server MVP]" wrote:

> Let me answer for you "more simply":
> EXEC('USE '+@.DBName+'; CREATE PROCEDURE ... ');
>
>|||> Aaron, you're not getting annoyed are you :)
The implication I got was, here idiot, since the original question was too
complex for you, let me dumb it down.|||Yes, if you can dynamic sql.
Context switch not change in stored procedure.
You can refer below my example
-- S2K SP3
DECLARE @.I_DB_NAME NVARCHAR(200)
SET @.I_DB_NAME='Demo'
DECLARE @.proc NVARCHAR(4000)
SELECT @.proc =QUOTENAME(@.I_DB_NAME) + '.dbo.sp_execresultset'
EXEC @.proc 'CREATE VIEW t2 AS SELECT GETDATE() D'
"ScottL"?? ??? ??:

> In our application, we dynamically create new databases using a stored
> procedure. Each new database must have a few required stored procedures
> created in it. Since SQL Server does not allow the specification of a
> different database context for creation of procedures or functions, the
> current workaround is to define a system stored procedure in Master that
> creates the stored procs. We call this in the context of the new DB after
it
> is created. This works fine, but an approach that does not require the use
of
> any system databases would be preferred.
> Is there a better way to accomplish this without Master or Model (in
> pseudocode):
> create procedure usp_NewDB
> @.DBName
> as
> begin
> create database @.DBName
> create procedure @.DBName.dbo.SP1 as ...
> create procedure @.DBName.dbo.SP2 as ...
> end
>|||Here's a way to bypass the parser.
DECLARE @.sql VARCHAR(255);
SET @.sql = 'USE tempdb; EXEC(''CREATE PROCEDURE dbo.foo AS SELECT bar =
1'');';
EXEC(@.sql);
GO
EXEC tempdb.dbo.foo;
GO
USE tempdb;
GO
DROP PROCEDURE dbo.foo;
GO|||Aaron Bertrand [SQL Server MVP] skrev:

> The implication I got was, here idiot, since the original question was too
> complex for you, let me dumb it down.
Yeah, I interpreted it the same was as you, and my reply was intended
to support you, in a somewhat humorously way. Not sure I succeeded
though :)
/impslayer, aka Birger Johanssonsql

CREATE RULE for a Default Type

Hi all,
Using: SQL Server 2000 SP3A Enterprise Edition
I have setup a table that holds application information. One of the fields
holds the Applications Version Information.
I have created a default type called Version of nvarchar and length 43.
Version information is made up of 2, 3 or 4 parts, Major, Minor, Build and
Revision (Major.Minor[.Build[.Revision]]). Each part can hold up to 10 digits
up to 2,147,483,647 (int without comas). That makes 4 blocks of 10 plus up t
o
3 seperators (being the .) makes 43 the max length.
What I want to do is create a rule that will only allow a valid version
number to be stored in the field. I had something like this:
@.value LIKE '[0-9].[0-9]' OR @.value LIKE '[0-9].[0-9].[0-9]' OR @.value LIKE
'[0-9].[0-9].[0-9].[0-9]'
This will not allow 1.10.8903.56 as [0-9] specifies single characters only.
Is there quick way to do the validation as a rule without having to type
loads of LIKE statements for every possibility?Use a CHECK constraint rather than a RULE. Rules and user-defined types
are supported for backwards compatibility. Constraints are more much
easier to maintain and code.
In this case I think you'll find it easier to exclude the values you
don't want:
CREATE TABLE YourTable
(... , version VARCHAR(43) NOT NULL
CHECK (version NOT LIKE '[^.0-9]'
AND (version LIKE '%.%'
OR version LIKE '%.%.%'
OR version LIKE '%.%.%.%')))
David Portas
SQL Server MVP
--|||Oops. That should be:
CREATE TABLE YourTable
(version NVARCHAR(43) NOT NULL
CHECK (version NOT LIKE '%[^.0-9]%'
AND (version LIKE '%.%'
OR version LIKE '%.%.%'
OR version LIKE '%.%.%.%')))
David Portas
SQL Server MVP
--|||Hi,
Thanks for quick response to my question!
All working okay now!
Just 1 other question! Why use a Check instead of a Rule? I was using the
rule on the default type to save me typing the Check for every field as I
have many tables that contain this Version type field. Your code works both
as a Check and Rule.
Cheers
Paul|||Yes it will work as a Check and a Rule. User-defined types, defaults
and rules are designated as backwards compatibility features so they
won't necessarily be fully supported in future versions of SQL Server.
Books Online recommends using the ANSI/ISO standard alternatives, CHECK
and DEFAULT constraints, instead.
User-defined types are difficult to maintain because of the convoluted
syntax and binding - you have to remove all references and unbind
before you can make a change - a big problem if your type is used in
many columns. Constraints are declarative, unbound and much more
flexible.
CHECK constraints can also be used by the optimizer (although that's
unlikely to be useful with the constraint used here). I don't think the
optimizer can take advantage of Rules, although I confess I don't
recall where I've seen that documented so someone may correct me on
that point.
Finally, I suspect fewer SQL Server professionals will continue to use
and remember the old syntax in future so those who inherit your code
will probably be more productive if they don't have to cope with the
legacy stuff.
I think those are enough reasons not to use User-defined Types and
Rules. You want to save yourself some typing? Just cut-and-Paste the
CHECK constraint in Query Analyzer - that's no more work than pasting
the name of a user-defined type.
David Portas
SQL Server MVP
--|||Hi,
Thanks again for your information, very useful.
I have updated to use Check instead of Rule, was just trying to do the easy
way but as you pointed out sometimes the easy way can become problamatic in
the future.
Cheers again for your help.
Paul|||If you use a datamodeling tool (I use ERwin) you probably can do much the
same thing in the model, but generating them out as CHECK constraints. They
have domains that you can use in the model but only generate them as CHECKS.
Not sure if other tools have this, but it is a really feature of ERwin.
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Dr. Paul Caesar - CoullByte (UK) Limited"
< DrPaulCaesarCoullByteUKLimited@.discussio
ns.microsoft.com> wrote in message
news:667A4E1E-64D2-4030-A25E-399B07E3C9B8@.microsoft.com...
> Hi,
> Thanks again for your information, very useful.
> I have updated to use Check instead of Rule, was just trying to do the
> easy
> way but as you pointed out sometimes the easy way can become problamatic
> in
> the future.
> Cheers again for your help.
>
> Paul

Create PDF File

I have an application in Power Builder 6.5 and I use SQL Server 2000.
How Can I create pdf files?
I want to create pdf files using Power or SQL.
Is it possible?
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
I guess you cannot create PDF files using SQL Server 2000. I don't know about
Powerbuilder. Adobe has a PDF file creation services. Please take a look at
http://www.adobe.com/products/server/docgen.html
Did you mean storing PDF files in SQL Server 2000?
"Karla Gomez" wrote:

> I have an application in Power Builder 6.5 and I use SQL Server 2000.
> How Can I create pdf files?
> I want to create pdf files using Power or SQL.
> Is it possible?
>
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
>
|||Vikas,
While you may not be able to directly create Adobe PDF files directly with
T-SQL (and I'm still researching this), you can use a 3rd party tool and a
T-SQL query and generate a PDF file as the output. Specificly, see
ceTesoftware at http://www.dynamicpdf.com/ and their product DynamicPDF
ReportWriter and code example at:
http://www.dynamicpdf.com/Products/R...r/Examples.csp
The basic program flow for a script using DynamicPDF? ReportWriter is as
follows:
Create a report object.
Define the database connection and query.
Add report elements (i.e. RecordBox) to the header, section header, body,
section footer and/or footer.
Generate (Draw) the PDF report.
ASP Example Script: <!-- METADATA TYPE="typelib"
UUID="{567E47DC-8220-403F-B348-EB9A035648BB}" -->
<%
Dim objReport
Set objReport = Server.CreateObject("DPDF_RptWtr.Report")
With objReport.Command
'An ADO Connection String (Northwind DB in this example)
.ActiveConnection = "File Name = " & Server.MapPath("Northwind.udl")
'A SQL Query
.CommandText = "SELECT CompanyName FROM Customers"
End With
With objReport.Header
.Height = 24
.AddLabel "Header goes here", 0, 0, 504, 12
End With
With objReport.Body
.Height = 12
.AddRecordBox "CompanyName", 0, 0, 504, 12
End With
objReport.DrawToASP
Set objReport = Nothing
%>
And the following link demostrates a dynamiclly built PDF file:
http://www.dynamicpdf.com/Products/R...t_Verdana.aspx
I'm sure there are other such products as SQL Server 2000 Reporting Services
supports writing the output to the PDF format.
Regards,
John
"Vikas Ahuja [MSFT]" wrote:
[vbcol=seagreen]
> I guess you cannot create PDF files using SQL Server 2000. I don't know about
> Powerbuilder. Adobe has a PDF file creation services. Please take a look at
> http://www.adobe.com/products/server/docgen.html
> Did you mean storing PDF files in SQL Server 2000?
> "Karla Gomez" wrote:
|||Karla & Vikas,
I knew I had seen how to do this somewhere...
Creating a PDF from a Stored Procedure
http://www.sqlservercentral.com/colu...dprocedure.asp
Regards,
John
"John Kane" <JohnKane@.discussions.microsoft.com> wrote in message
news:DC2A1EB6-DEF1-47DA-919C-22A7336C5638@.microsoft.com...
> Vikas,
> While you may not be able to directly create Adobe PDF files directly with
> T-SQL (and I'm still researching this), you can use a 3rd party tool and a
> T-SQL query and generate a PDF file as the output. Specificly, see
> ceTesoftware at http://www.dynamicpdf.com/ and their product DynamicPDF
> ReportWriter and code example at:
> http://www.dynamicpdf.com/Products/R...r/Examples.csp
> The basic program flow for a script using DynamicPDFT ReportWriter is as
> follows:
> Create a report object.
> Define the database connection and query.
> Add report elements (i.e. RecordBox) to the header, section header, body,
> section footer and/or footer.
> Generate (Draw) the PDF report.
> ASP Example Script: <!-- METADATA TYPE="typelib"
> UUID="{567E47DC-8220-403F-B348-EB9A035648BB}" -->
> <%
> Dim objReport
> Set objReport = Server.CreateObject("DPDF_RptWtr.Report")
> With objReport.Command
> 'An ADO Connection String (Northwind DB in this example)
> .ActiveConnection = "File Name = " & Server.MapPath("Northwind.udl")
> 'A SQL Query
> .CommandText = "SELECT CompanyName FROM Customers"
> End With
> With objReport.Header
> .Height = 24
> .AddLabel "Header goes here", 0, 0, 504, 12
> End With
> With objReport.Body
> .Height = 12
> .AddRecordBox "CompanyName", 0, 0, 504, 12
> End With
> objReport.DrawToASP
> Set objReport = Nothing
> %>
> And the following link demostrates a dynamiclly built PDF file:
>
http://www.dynamicpdf.com/Products/R...t_Verdana.aspx
> I'm sure there are other such products as SQL Server 2000 Reporting
Services[vbcol=seagreen]
> supports writing the output to the PDF format.
> Regards,
> John
>
>
>
> "Vikas Ahuja [MSFT]" wrote:
about[vbcol=seagreen]
at[vbcol=seagreen]
|||hello Karla,
did you find a solution to this problem? Did you use the acrobat writer or stored procedure? how difficult is it to create pdf's on the fly?
bev
************************************************** ********************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...
|||Hi Bev,
Yes, I did find a solution to this problem. You can use T-SQL and therefore
stored procedures via the methods documented at:
Creating a PDF from a Stored Procedure
http://www.sqlservercentral.com/colu...dprocedure.asp
Regards,
John
"bev harris" wrote:

> hello Karla,
> did you find a solution to this problem? Did you use the acrobat writer or stored procedure? how difficult is it to create pdf's on the fly?
> bev
> ************************************************** ********************
> Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
> Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...
>
|||Karla
This company provide COM and .NET components.
http://websupergoo.com/products.htm
I have used version 3 which was a COM component with .NET 1.0 and it worked
very well. Tickets generated on www.easyCinema.com use this technology.
I have never used this product...
http://www.tallcomponents.com/
"Karla Gomez" wrote:

> I have an application in Power Builder 6.5 and I use SQL Server 2000.
> How Can I create pdf files?
> I want to create pdf files using Power or SQL.
> Is it possible?
>
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
>

Create new database based on a template using SMO

Hi All,

I'm working on a web application where the user needs to be able to create and name new databases that are identical in structure to other existing databases (that is, all tables, stored procedures, functions, indexes, etc.). This is so that they can create a new database for each client and need to be able to do this through the web application. Having hunted around a fair bit, I've established that SMO is capable of doing pretty much everything that I want. The only problem is that everything I do seems to be based on the actual SQL Server and associated databases rather than the ones I have created in the App_Data folder.

The relevant code (so far) is:

Dim sqlServerAs New Server()With sqlServer.ConnectionContext .ServerInstance ="(local)" .Connect() .Disconnect()End WithFor Each dbAs DatabaseIn sqlServer.Databases ListView1.Items.Add(db.Name)NextDim newDatabaseAs New Database(sqlServer, DbName.Text.ToString)newDatabase.Create()

This does actaully create a new database, just not where I want it! Can anyone point me in the right direction as to how I can create a copy of a database in the App_Data folder?

Thanks & regards,

Paul

One general question first, will the server be running nothing but these databases? If so you may well be able to simplify the process by creating a template database within the model database. When a new database is created, it will be populated using objects in model.

The second issue is one of security as effectively sa permissions are required to create a new database. Your security concerns may be insufficient for this to be an issue, however you would be well advised to employ a level of indrection. Instead of letting the users directly trigger the create process, set up a queue table in a suitable location and have a windows service monitor this queue and create a database as required.

To find out what is required in the way of TSQL, just generate a database create script for an existing database inside (Enterprise Manager for SQL2000 and SQL Server Management Studio for SQL2005).

|||

Hi,

Thank you for your reply. I appreciate any help as I've struggled on this whole problem for a couple of days and making very little progress...

Anyway, at the moment the SQL Server is only being used for the client databases in this application, but I don't know how long that will continue to be the case.

As for the security issue, only Administrators on the Active Directory account will ultimately be able to access the page for creating databases. At the moment I'm just using site security, but will be changing this later to Active Directory.

I had already created a script file, but as this was several thousand lines, I'm rather hoping for a more manageable solution!

Thanks again,

Paul

|||Are you able to use multiple SQL Instances on that server? If so create an instance just for this application and you could use the model approach. I am glad that you have already considered security - for many applications, secuirity is an afterthought if it is thought of at all.|||

Hi,

I know that I should know, but I have no idea if I can create multiple instances of the server or not. Assuming that I can, what exactly is the model approach? How do I make fresh copies of the amended 'Model' database?

Thanks again,

Paul

Create N rows from 1 Row?

Hello...

I have a small question about how to realize something in SSIS.

We are rewriting an Application and we will be normalizing a table. The current Table has Data in the Format:

ID - Name - Type500 - Type1000 - Type2000

1 - Test - 2 - 1 - 0

2 - Test 2 - 0 - 2 - 1

The Targets would be:

ID - Name

1 - Test

2 - Test

TargetID , Type_ID , Date (+ ID Field omitted here)

1 - 1 - 1.1.1900

1 - 1 - 1.1.1900

1 - 2 - 1.1.1900

2 - 2 - 1.1.1900

2 - 2 - 1.1.1900

2 - 3 - 1.1.1900

So basically we need to generate N type rows for each "Count" in the Type Fields. So whats the best aproach to convert this data in an SSIS Package? Currently I am thinking about calling a SP to split the rows, but I dont "like" this aproach since it would place import/migration logic into the database and I would have to "clean up" later.

I can see two choices-

1 - Use a script component.

2 - Write a custom component.

For information, this would be an asynchronous component, as discussed in Books Online.

There is a similar problem covered here http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=511608&SiteID=1

|||

Thanks...

The link is exactly what I was looking for...

create mobile database from xls, csv, text or mdb

Hello,

Since my experience in VS is extremely limited, i'd like to be exused if questions sound silly.

My problem is that, at my device application project, i want to build a database that will retrieve data from .xls, or .csv, or .txt, or .mbd files. If i've noticed well, there is no support for OLEDB or ODBC, since by the time that i add tableadapter to my database.xsd and use these, after insertion i get multiple errors informing that system.data.oledb. .... or .odbc "type is not defined".

If it cannot be done, and since i still try to figure out how smart devices function, transact with databases, e.c.t. is there any suggestion?

Thank you in advance

Kostas

you'll likely need to use sql server mobile.|||

I'am trying to do so with sql mobile.

Going-> right click on "deviceapplication1" -> add -> new item -> sql mobile database

This gives me an empty database. In order to fill it i go at server explorer, right click on "AppDatabase2.sdf" (new created database), -> modify connection -> change (data source field) -> i choose microsoft ODBC data source -> user or system data source names: ms access database -> OK. Then browsing for mdb file (my access databace), and it's in server explorer; no problem yet. Then going to solution explorer, opening "AppDatabase2DataSet.xsd", drug & drop tables from server explorer to "AppDatabase2DataSet.xsd" (making table adapters(?)). Then i get this huge error list that "system.data.odbc...." Type is not defined.

Either i need to add some reference (as i did at VBA) since even autofill doesn't work (I try at the top of code to insert "imports system.data.odbc), or it's just not supported in sql mobile database

|||you can't use access as your datasource. appdatabase2.sdf IS your sql server mobile database, and that needs to remain your datasource. no odbc, no ole db in NETCF. your only mobile database option for .net development is a sql server mobile DB.|||

That is something that i have already understood after some experiment.

The question is how sql mobile works and if i can update the tables from an external (server or just a given path) sourse. I have created an sdb, with the tables required, but this tables are empty, and need to be updated each day from source files of the above mentioned format.

|||I'm not a drag and drop type of guy, but from what you've said, you're trying to create a set of adapters between Access and SQL Server Mobile -- you can't do this, there's no ODBC implementation within NETCF for the CF to talk to Access. I learned yesterday from a posting by Ilya Tumanov that there's a util called AccessSynchronizer for Access<-->SQL Server Mobile synchronization, you may want to look into it. Personally I'd just dump the stuff from Access into XML or CSV, and import using my own code, but that may not be for you. Another option is to wrap your datasource in a web service and proxy the sync operation that way.|||

Thank you Andrew, i'll take a look at accessSynchronizer. If my knowledge and experience wasn't so poor i could ask about the code for importing CSV since this is the format that interest me more, but i guess that it will be rather difficult for me fix.

Thank you very much

|||

I believe opennetcf has a csv importer, you may want to consider that ...

this post is a bit old, but may still be relevant:
http://groups.google.com/group/microsoft.public.dotnet.framework.compactframework/browse_thread/thread/671b1480a962e8e0/92d0ba873b07398d%2392d0ba873b07398d

Create Mining Structure and Mining Model with code

Dear friends,

I encounter a serious problem.

I would like to develop an application that can create Data Mining structures and a mining model in SQL Server 2005 with VB.NET. I tried the code from book Data Mining with SQL server 2005 in chapter 14 but did not work. Any good idea?

Please help me.

Best regards,

Manolis

Can you post the errors you're seeing?

Some of the AMO objects/methods changed slightly in the final release (this book was based on pre-release APIs). The C# sample here should help you diagnose the errors: http://www.sqlserverdatamining.com/DMCommunity/Downloads/Links_LinkRedirector.aspx?id=78.

|||

The errata for chapter 14 (and others, ironically, including how to get to this site) is at http://www.wiley.com/WileyCDA/WileyAncillary/productCd-0471462616.html

|||

Thank you very much for your help.

The errors that I can see in the code that you gave in your answer are the following and they are more or less the same as I had previously

I tried the code but initially I have encounter the following problems.

1. In any line that have the declaration As Server, As Database like in

Public Function CreateDatabase(ByVal srv As Server, ByVal databaseName As String) As Database gives me the problem that type Database is not declared the same type Server is not declared and it does not give me any option.

2. In addition to that for As DataSource, As RelationalDataSource, As RelationalDataSourceView, As ScalarMiningStructureColumn, As DataSourceViewBinding, gives me the problem that type is not declared.

3. Finally in mc = New MiningModelColumn("Yearly income", Utils.GetSyntacticallyValidID("Yearly income", Type.GetType(MiningModelColumn))) is not accesible in this context because it is 'Private'.

I have some more problems but I thing that by solving the above that I referred I will solve the rest.

Thank you any way.

Best regards,

Manolis

Create mdf file?

Scenario:
I have a fully licensed SQL server 2005 database that my production
application uses. I have several contract sales people who take my web
application, visit prospective clients and perform a demonstration of our
product (by connecting to the internet to access the database).
I would like to have the application run completely on the laptops my sales
team uses, so I am wondering how to move my existing SQL 2005 database to a
.mdf file that can be places in the app_data folder so the application will
not have to access the Internet for the demonstration).
I don't know what the steps are to do this, or how to create the conection
string to point to a local database in the app_data folder.
Thanks in advance!
BrianBrian,
Of course, the laptop will need to have SQL Server installed on it. If your
database is not too big (<4 GB) you can use SQL Server Express. BACKUP your
database and RESTORE the backup to the laptops. (This will place an MDF and
an LDF on the laptop. You need both.)
Since logins will not come over with the restore, you will also need to set
up a login for the person demonstrating your product. But that should not
be a problem to do.
I would suggest that you do this once on a single laptop. Once you have it
just the way you want it, take backups of all the databases on the laptop.
Then for the other laptops, install SQL Server Express and then restore all
the backups to each traveling laptop.
RLF
.
"AutoTrackerPlus" <brian.cesafsky@.autotrackerplus.com> wrote in message
news:Oz5QIkTzHHA.4652@.TK2MSFTNGP05.phx.gbl...
> Scenario:
> I have a fully licensed SQL server 2005 database that my production
> application uses. I have several contract sales people who take my web
> application, visit prospective clients and perform a demonstration of our
> product (by connecting to the internet to access the database).
> I would like to have the application run completely on the laptops my
> sales team uses, so I am wondering how to move my existing SQL 2005
> database to a .mdf file that can be places in the app_data folder so the
> application will not have to access the Internet for the demonstration).
> I don't know what the steps are to do this, or how to create the conection
> string to point to a local database in the app_data folder.
> Thanks in advance!
>
> Brian
>|||It is not as simple as including the data files. Your application
does not touch the files, and would not know what to do with them if
it did. Your application talks to the SQL Server service running on
the server, and SQL Server deals with the database files.
To run the application on the laptops you will need SQL Server
installed and running on the laptops. In this case you can probably
get away with SQL Express, which is free. That assumes the database
is no more than 4GB, the SQL Express limit. You will probably want
reasonably robust laptops, particularly when it comes to memory where
1GB is probably a minimum. I suggest thorough testing to confirm that
performance is good enough to show customers
Roy Harvey
Beacon Falls, CT
On Mon, 23 Jul 2007 09:51:57 -0500, "AutoTrackerPlus"
<brian.cesafsky@.autotrackerplus.com> wrote:
>Scenario:
>I have a fully licensed SQL server 2005 database that my production
>application uses. I have several contract sales people who take my web
>application, visit prospective clients and perform a demonstration of our
>product (by connecting to the internet to access the database).
>I would like to have the application run completely on the laptops my sales
>team uses, so I am wondering how to move my existing SQL 2005 database to a
>.mdf file that can be places in the app_data folder so the application will
>not have to access the Internet for the demonstration).
>I don't know what the steps are to do this, or how to create the conection
>string to point to a local database in the app_data folder.
>Thanks in advance!
>
>Brian
>

Create Login form(authenticate with sql)

Hi all.

M trying to create a logon form
I had something in mind but i can work it out yet

When the user wants to login into the application the value of textbox must be compared with the datafield in the sql server.
And then i want a messagebox to show up.

I created a table in sql server with 2 fields in it , User and Password.

I hope anyone could help me tnx already

You will need a login which is able to access the table or a stored procedure which does the check of the login for you. Then its up to you to either raise an error from your stored procedure or return a specific result fromthe stored procedure which is transformed into a user friendly message like "Password wrong" or "Username / password combination wrong".

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

Create indexes for Query Optimization

I am using ASP.net application on SQL server 2005.
I am getting data from the DB using this SQL statement from 2 tables:
Select Table1.a,Table1.b,Table2.C from Table1 INNER JOIN Table1 ON
Table2.e=Table.e Where Table1.a=XXX AND Table1.b>YYY Order Table1.a DESC
Table1 has around 2,000,000 Records and table has 3 records.
The query is running very slow.
How do Optimize the query using Indexes ?
On Which fields should I create the indexes ?
Thanks
ra294
ra294@.hotmail.com
On Jan 7, 6:56Xpm, "ra294" <ra...@.hotmail.com> wrote:
> I am using ASP.net application on SQL server 2005.
> I am getting data from the DB using this SQL statement from 2 tables:
> Select Table1.a,Table1.b,Table2.C from Table1 INNER JOIN Table1 ON
> Table2.e=Table.e Where Table1.a=XXX AND Table1.b>YYY Order Table1.a DESC
> Table1 has around 2,000,000 Records and table has 3 records.
> The query is running very slow.
> How do Optimize the query using Indexes ?
> On Which fields should I create the indexes X?
> Thanks
> ra294
> ra...@.hotmail.com
Do you have index on either columns a or b? Also, you might consider
remove the order by clause and do it later.

Create indexes for Query Optimization

I am using ASP.net application on SQL server 2005.
I am getting data from the DB using this SQL statement from 2 tables:
Select Table1.a,Table1.b,Table2.C from Table1 INNER JOIN Table1 ON
Table2.e=Table.e Where Table1.a=XXX AND Table1.b>YYY Order Table1.a DESC
Table1 has around 2,000,000 Records and table has 3 records.
The query is running very slow.
How do Optimize the query using Indexes ?
On Which fields should I create the indexes ?
Thanks
ra294
ra294@.hotmail.comra294 wrote:
> I am using ASP.net application on SQL server 2005.
> I am getting data from the DB using this SQL statement from 2 tables:
> Select Table1.a,Table1.b,Table2.C from Table1 INNER JOIN Table1 ON
> Table2.e=Table.e Where Table1.a=XXX AND Table1.b>YYY Order Table1.a DESC
> Table1 has around 2,000,000 Records and table has 3 records.
> The query is running very slow.
> How do Optimize the query using Indexes ?
> On Which fields should I create the indexes ?
> Thanks
> ra294
> ra294@.hotmail.com
>
>
try using database engine tuning advisor, find it in tools menu of the
management studio|||Without looking at the data Im just guessing but try doing this:
Table1 index: Key columns: a,b,e
Table2 index: key columns: e included: c
MC
"ra294" <ra294@.hotmail.com> wrote in message
news:uTLstxSUIHA.5404@.TK2MSFTNGP03.phx.gbl...
>I am using ASP.net application on SQL server 2005.
> I am getting data from the DB using this SQL statement from 2 tables:
> Select Table1.a,Table1.b,Table2.C from Table1 INNER JOIN Table1 ON
> Table2.e=Table.e Where Table1.a=XXX AND Table1.b>YYY Order Table1.a DESC
> Table1 has around 2,000,000 Records and table has 3 records.
> The query is running very slow.
> How do Optimize the query using Indexes ?
> On Which fields should I create the indexes ?
> Thanks
> ra294
> ra294@.hotmail.com
>
>|||ra294,
You might want to recheck your posting. The query is not valid (because
of typos?), and the number of rows are suspect (really just 3 rows in
one table and 2 million rows in the other table?).
How many rows does the query return? 3? 6 million? How big is the table
with the 2 million rows (how many pages or how much MB)?
How slow is the query currently? 200 milliseconds, 5 seconds, 5 minutes?
In general, you should always define a Primary Key for each table. This
will automatically create a corresponding unique index. You should also
define any Foreign Key relations. In general, it is a good idea to index
Foreign Key relations.
If you would like more assistence, then please post simplified DDL (and
the answers to the questions above).
--
Gert-jan
ra294 wrote:
> I am using ASP.net application on SQL server 2005.
> I am getting data from the DB using this SQL statement from 2 tables:
> Select Table1.a,Table1.b,Table2.C from Table1 INNER JOIN Table1 ON
> Table2.e=Table.e Where Table1.a=XXX AND Table1.b>YYY Order Table1.a DESC
> Table1 has around 2,000,000 Records and table has 3 records.
> The query is running very slow.
> How do Optimize the query using Indexes ?
> On Which fields should I create the indexes ?
> Thanks
> ra294
> ra294@.hotmail.com|||On Jan 7, 6:56=A0pm, "ra294" <ra...@.hotmail.com> wrote:
> I am using ASP.net application on SQL server 2005.
> I am getting data from the DB using this SQL statement from 2 tables:
> Select Table1.a,Table1.b,Table2.C from Table1 INNER JOIN Table1 ON
> Table2.e=3DTable.e Where Table1.a=3DXXX AND Table1.b>YYY Order Table1.a DE=SC
> Table1 has around 2,000,000 Records and table has 3 records.
> The query is running very slow.
> How do Optimize the query using Indexes ?
> On Which fields should I create the indexes =A0?
> Thanks
> ra294
> ra...@.hotmail.com
Do you have index on either columns a or b? Also, you might consider
remove the order by clause and do it later.

CREATE ENDPOINT (Transact-SQL)

hello all i am new here,

my question is how i can activate http in sql server 2005

i want to run a helpdesk application but i cant make a new database.

it has something to do with create endpoint, i have no knowlege off sql server 2005 its not my field

can someone help me please?

It seems like you may have 2 separate problems. One is creating a new database and the other is creating an endpoint. If the root problem of the "helpdesk application" is creating the new database, I suggest posting a new forum posting with the error you get when you try to create the database. That will help people understand what the problem is and be able to suggest solutions.

Regarding creating an endpoint, I assume you are already able to connect to SQL Server. You will need SQL administrator or equivalent permissions to create the endpoints. For additional information regarding creating endpoints, please refer to Books Online "CREATE ENDPOINT" topic (http://msdn2.microsoft.com/en-us/library/ms181591.aspx).

If you have problems creating the endpoint outside of the "helpdesk application" (ie. using SQL Management Studio), please post the error you get from the server.

Jimmy|||

thank you jimmy,

i have found a workaround by installing a different sqlserver

thanks anyway

paul

Create DB on the fly

Hi guys.

Here is what I want to do:

1. create a small desktop application

2. when this application starts, it will check if the file "...mydb.mdf" file exists in a specified folder

3. if the file doesn't exist, I want to create the database and connect to it using User Instance

How can I create the database?

I tried to connect to SQLExpress and run a script using SqlCommand. It didn't work.

Is there any other way to do this?

Or what is the correct way to do it?

Thanks.

Mircea

You can create entire databases in SQL Script using CREATE DATABASE, CREATE TABLE, and other similar commands. Some of them (like CREATE VIEW) must be sent by themselves, but otherwise its pretty simple.

Alternatively, you can embed an empty database in your application and extract it out as needed.

-Ryan / Kardax

|||

Hi Ryan.

I have the script for creating the database. My question is how should I run this script?

Thanks.

create data-driven subscription programatically

What is the best/easiest way to create a data-driven subscription
programatically? Can I create a C# console application to call the
ReportingService class?Yes you can - I use Soap to deliver and set reports via the report service.
See the documentation - lots of examples.
=-Chris
"Bucky" <uw_badgers@.mail.com> wrote in message
news:1109188603.308235.11520@.z14g2000cwz.googlegroups.com...
> What is the best/easiest way to create a data-driven subscription
> programatically? Can I create a C# console application to call the
> ReportingService class?
>

Create Database Script - Help Required

Hi,

I have a Database.sql script file that creates my application database (Malibu). My question is how do I get around the problem of the hard coded FILENAME as shown below...

CREATE DATABASE [Malibu] ON (NAME = N'Malibu_Data', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL\Data\Malibu_Data.MDF' , SIZE = 4, FILEGROWTH = 10%) LOG ON (NAME = N'Malibu_Log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL\Data\Malibu_Log.LDF' , SIZE = 1, FILEGROWTH = 10%)

If this path does not exist on the clients PC - the script will fail.

Any suggestions appreciated.

Thanks.

Steve.Build the script as a string and then execute the string.

Declare @.strScript as varchar(5000)

SET @.strScript = 'CREATE DATABASE [Malibu] FILENAME = ' + @.thecorrectpathhereasavariable + '\Malibu_Data.MDF' '

Exec(@.strScript)

How you actually determine the path I do not know, but you can variableize the script as the above example.|||Thanks Wes,

I currently run the script from a batch file using OSQL, however, the way things are heading with .Net, I guess I should look at doing it in VB code.

Steve.|||The code I posted was T-SQL code.