Altering table structure by comparing it to a MODEL table

Hi,

I have an application which needs to use the MODEL database (This is

created by me and it acts as a template) to synchronize the tables,

stored procedures, triggers, indexes across multiple client database.

Basically, if there is a structural change to the MODEL database the

client databases need to be updated. For example, If I create a new

table or alter a table or modify a stored procedure or modify an index

the client databases need to be updated when I run the sync routine.

So far I have managed to create/drop tables, create/drop columns, create/drop stored procedures and create/drop triggers.

I need to be able to drop all relationships (constraints) and indexes

and recreate them. I need to know how this can be done using SMO. I can

do it using TSQL but I want to know if there is an alternative.

Thanks in advance.I figured it out ...

#Region " Relationships "
Private Sub DropRelationships(ByVal db As Database)
For Each tbl As Table In db.Tables
If tbl.ForeignKeys.Count > 0 Then
For Each key As ForeignKey In tbl.ForeignKeys
key.MarkForDrop(True)
Next
tbl.Alter()
End If
Next
End Sub

Private Sub CreateRelationships(ByVal AlterDB As Database, ByVal ModelDB As Database)
For Each tbl As Table In ModelDB.Tables
If tbl.ForeignKeys.Count > 0 Then
For Each key As ForeignKey In tbl.ForeignKeys
For Each col As ForeignKeyColumn In key.Columns
Dim rc As Column
Dim fk As ForeignKey
fk = New ForeignKey(AlterDB.Tables(tbl.Name), key.Name)
For Each c As Column In AlterDB.Tables(key.ReferencedTable).Columns
If c.InPrimaryKey Then
rc = c
End If
Next
Dim fkc As ForeignKeyColumn
fkc = New ForeignKeyColumn(fk, col.Name, rc.Name)
fk.Columns.Add(fkc)

fk.ReferencedTable = key.ReferencedTable
fk.ReferencedTableSchema = key.ReferencedTableSchema

fk.Create()
Next
Next
End If
Next
End Sub
#End Regionsql

Altering table structure by comparing it to a MODEL table

Hi,

I have an application which needs to use the MODEL database (This is

created by me and it acts as a template) to synchronize the tables,

stored procedures, triggers, indexes across multiple client database.

Basically, if there is a structural change to the MODEL database the

client databases need to be updated. For example, If I create a new

table or alter a table or modify a stored procedure or modify an index

the client databases need to be updated when I run the sync routine.

So far I have managed to create/drop tables, create/drop columns, create/drop stored procedures and create/drop triggers.

I need to be able to drop all relationships (constraints) and indexes

and recreate them. I need to know how this can be done using SMO. I can

do it using TSQL but I want to know if there is an alternative.

Thanks in advance.I figured it out ...

#Region " Relationships "
Private Sub DropRelationships(ByVal db As Database)
For Each tbl As Table In db.Tables
If tbl.ForeignKeys.Count > 0 Then
For Each key As ForeignKey In tbl.ForeignKeys
key.MarkForDrop(True)
Next
tbl.Alter()
End If
Next
End Sub

Private Sub CreateRelationships(ByVal AlterDB As Database, ByVal ModelDB As Database)
For Each tbl As Table In ModelDB.Tables
If tbl.ForeignKeys.Count > 0 Then
For Each key As ForeignKey In tbl.ForeignKeys
For Each col As ForeignKeyColumn In key.Columns
Dim rc As Column
Dim fk As ForeignKey
fk = New ForeignKey(AlterDB.Tables(tbl.Name), key.Name)
For Each c As Column In AlterDB.Tables(key.ReferencedTable).Columns
If c.InPrimaryKey Then
rc = c
End If
Next
Dim fkc As ForeignKeyColumn
fkc = New ForeignKeyColumn(fk, col.Name, rc.Name)
fk.Columns.Add(fkc)

fk.ReferencedTable = key.ReferencedTable
fk.ReferencedTableSchema = key.ReferencedTableSchema

fk.Create()
Next
Next
End If
Next
End Sub
#End Region

ALTER TABLE problem

Hiya all,

Im doing a system tool application. The app has the functionality to edit and change language strings that other applicaction uses.

The table look as following:

STRING_ID English Swedish
----------
100 Cancel Avbryt
101 Apply Verst'a'll

STRING_ID : Int, NOT NULL, Primary key
English : NVARCHAR(512), NOT NULL
Swedish : NVARCHAR(512), NOT NULL

In my tool you're able to add new strings.

Now I want to be able to add languages by adding new columns to the table.

Example:

STRING_ID English Swedish Arabic
------------
100 Cancel Avbryt <Cancel in arabic>
101 Apply Verst'a'll <Apply in arabic>

I've made a Stored Procedure looking like following:

CREATE PROC My_sp_AddNewLanguage
@.NewLanguageName nvarchar(512), @.RetrievalMsg nvarchar(255) OUTPUT
AS
-- Find out how many rows that should be affected when altering the table
DECLARE @.NrOfRows integer
SELECT * FROM String_Resource
SET @.NrOfRows = @.@.ROWCOUNT

ALTER TABLE String_Resource
ADD @.NewLanguageName NVARCHAR(512) NOT NULL
DEFAULT ('')
IF @.@.ROWCOUNT <> @.NrOfRows
BEGIN
SET @.RetrievalMsg = 'Unable to Add New Language'
RETURN 8301 -- 8301 is something I've defined in my code
END
SET @.RetrievalMsg = 'Your new Language has now been added'
RETURN 0
GO

But I cant seem to do this because of the @.NewLanguageName in the following row:

ALTER TABLE String_Resource
ADD @.NewLanguageName NVARCHAR(512) NOT NULL
DEFAULT ('')

So my question is: How can you add a column to the Table using a variable @.Variable that contains the name of the new column?

OR

Can anybody tell me how I can write DEFAULT('') into a NVARCHAR variable since in Store Procedure Strings are using the '-sign and the DEFAULT ('') expression has those signs in it.

I've tried doing @.SQLQuery = N'ALTER TABLE String_Resource ADD ' + @.NewLanguageName + ' NVARCHAR(512) NOT NULL DEFAULT('')'

But it doesnt work since the DEFAULT('') expression screws up the string.

Thanks for your time,
FarekYou've got a really good idea, but I think you are going about it all wrong!

Take a look at the master.dbo.sysmessages table. It is designed to do exactly what you are trying to do. The secret is to have multiple rows with the same message id, but only one row per language. By adding rows to the language table, you can then add new rows to the message table for that language, and you are on your way!

The syntax should go something like:CREATE TABLE tLanguage (
languageId INT IDENTITY
CONSTRAINT XPKtLanguage
PRIMARY KEY (languageId)
, name NVARCHAR(25) NOT NULL
)
GO

CREATE TABLE tMessage (
languageId INT NOT NULL
CONSTRAINT XFK01tMessage
FOREIGN KEY (languageId)
REFERENCES tLanguage (languageId)
, messageId INT NOT NULL
CONSTRAINT XPKtMessage
PRIMARY KEY (languageId, messageId)
, message NVARCHAR(50) NOT NULL
)
GO-PatPsql

alter table inside a stored procedure

Hi,
Our application needs to issue an alter table statement. Since the user
using
the application does not have dbo permission, we are planning to use
a stored procedure with dynamic sql.
SET @.RUNSQL = "alter table dbo.gggg .."
EXEC(@.RUNSQL)
The stored procedure is owned by dbo. However it is not allowing
the alter table because of lack of permission. Does that mean
that any EXEC inside a stored procedure does not run as user
dbo.
Is there a workaround for it?
thanks.Hi
Well , if you use dynamic sql within a stored procedure, user must have
permissions (SELECT,UPDATE...) on underlyaing tables.
<dcruncher4@.aim.com> wrote in message
news:1140310808.885046.206220@.g47g2000cwa.googlegroups.com...
> Hi,
> Our application needs to issue an alter table statement. Since the user
> using
> the application does not have dbo permission, we are planning to use
> a stored procedure with dynamic sql.
> SET @.RUNSQL = "alter table dbo.gggg .."
> EXEC(@.RUNSQL)
> The stored procedure is owned by dbo. However it is not allowing
> the alter table because of lack of permission. Does that mean
> that any EXEC inside a stored procedure does not run as user
> dbo.
> Is there a workaround for it?
> thanks.
>|||This is a security feature.
> Is there a workaround for it?
In 2005, you can specify EXECUTE AS for the procedure.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<dcruncher4@.aim.com> wrote in message news:1140310808.885046.206220@.g47g2000cwa.googlegroups.com...
> Hi,
> Our application needs to issue an alter table statement. Since the user
> using
> the application does not have dbo permission, we are planning to use
> a stored procedure with dynamic sql.
> SET @.RUNSQL = "alter table dbo.gggg .."
> EXEC(@.RUNSQL)
> The stored procedure is owned by dbo. However it is not allowing
> the alter table because of lack of permission. Does that mean
> that any EXEC inside a stored procedure does not run as user
> dbo.
> Is there a workaround for it?
> thanks.
>|||To add to the other responses, an unbroken ownership chain (e.g. 'dbo' owns
all objects involved) does not change the execution context. With an
unbroken chain, *object* permissions are simply not checked on indirectly
referenced objects and note that dynamic SQL always breaks the ownership
chain. *Statement* permissions (e.g. ALTER TABLE) are always checked in the
execution security context. The execution context can't be changed on
versions prior to SQL 2005.
The need to execute DDL by non-privileged users and use dynamic SQL can
indicate an application design issue. Perhaps someone can suggest an
alternative if you provide the requirements driving this approach.
--
Hope this helps.
Dan Guzman
SQL Server MVP
<dcruncher4@.aim.com> wrote in message
news:1140310808.885046.206220@.g47g2000cwa.googlegroups.com...
> Hi,
> Our application needs to issue an alter table statement. Since the user
> using
> the application does not have dbo permission, we are planning to use
> a stored procedure with dynamic sql.
> SET @.RUNSQL = "alter table dbo.gggg .."
> EXEC(@.RUNSQL)
> The stored procedure is owned by dbo. However it is not allowing
> the alter table because of lack of permission. Does that mean
> that any EXEC inside a stored procedure does not run as user
> dbo.
> Is there a workaround for it?
> thanks.
>

alter table inside a stored procedure

Hi,
Our application needs to issue an alter table statement. Since the user
using
the application does not have dbo permission, we are planning to use
a stored procedure with dynamic sql.
SET @.RUNSQL = "alter table dbo.gggg .."
EXEC(@.RUNSQL)
The stored procedure is owned by dbo. However it is not allowing
the alter table because of lack of permission. Does that mean
that any EXEC inside a stored procedure does not run as user
dbo.
Is there a workaround for it?
thanks.Hi
Well , if you use dynamic sql within a stored procedure, user must have
permissions (SELECT,UPDATE...) on underlyaing tables.
<dcruncher4@.aim.com> wrote in message
news:1140310808.885046.206220@.g47g2000cwa.googlegroups.com...
> Hi,
> Our application needs to issue an alter table statement. Since the user
> using
> the application does not have dbo permission, we are planning to use
> a stored procedure with dynamic sql.
> SET @.RUNSQL = "alter table dbo.gggg .."
> EXEC(@.RUNSQL)
> The stored procedure is owned by dbo. However it is not allowing
> the alter table because of lack of permission. Does that mean
> that any EXEC inside a stored procedure does not run as user
> dbo.
> Is there a workaround for it?
> thanks.
>|||This is a security feature.

> Is there a workaround for it?
In 2005, you can specify EXECUTE AS for the procedure.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<dcruncher4@.aim.com> wrote in message news:1140310808.885046.206220@.g47g2000cwa.googlegroups
.com...
> Hi,
> Our application needs to issue an alter table statement. Since the user
> using
> the application does not have dbo permission, we are planning to use
> a stored procedure with dynamic sql.
> SET @.RUNSQL = "alter table dbo.gggg .."
> EXEC(@.RUNSQL)
> The stored procedure is owned by dbo. However it is not allowing
> the alter table because of lack of permission. Does that mean
> that any EXEC inside a stored procedure does not run as user
> dbo.
> Is there a workaround for it?
> thanks.
>|||To add to the other responses, an unbroken ownership chain (e.g. 'dbo' owns
all objects involved) does not change the execution context. With an
unbroken chain, *object* permissions are simply not checked on indirectly
referenced objects and note that dynamic SQL always breaks the ownership
chain. *Statement* permissions (e.g. ALTER TABLE) are always checked in the
execution security context. The execution context can't be changed on
versions prior to SQL 2005.
The need to execute DDL by non-privileged users and use dynamic SQL can
indicate an application design issue. Perhaps someone can suggest an
alternative if you provide the requirements driving this approach.
Hope this helps.
Dan Guzman
SQL Server MVP
<dcruncher4@.aim.com> wrote in message
news:1140310808.885046.206220@.g47g2000cwa.googlegroups.com...
> Hi,
> Our application needs to issue an alter table statement. Since the user
> using
> the application does not have dbo permission, we are planning to use
> a stored procedure with dynamic sql.
> SET @.RUNSQL = "alter table dbo.gggg .."
> EXEC(@.RUNSQL)
> The stored procedure is owned by dbo. However it is not allowing
> the alter table because of lack of permission. Does that mean
> that any EXEC inside a stored procedure does not run as user
> dbo.
> Is there a workaround for it?
> thanks.
>

alter table inside a stored procedure

Hi,
Our application needs to issue an alter table statement. Since the user
using
the application does not have dbo permission, we are planning to use
a stored procedure with dynamic sql.
SET @.RUNSQL = "alter table dbo.gggg .."
EXEC(@.RUNSQL)
The stored procedure is owned by dbo. However it is not allowing
the alter table because of lack of permission. Does that mean
that any EXEC inside a stored procedure does not run as user
dbo.
Is there a workaround for it?
thanks.
Hi
Well , if you use dynamic sql within a stored procedure, user must have
permissions (SELECT,UPDATE...) on underlyaing tables.
<dcruncher4@.aim.com> wrote in message
news:1140310808.885046.206220@.g47g2000cwa.googlegr oups.com...
> Hi,
> Our application needs to issue an alter table statement. Since the user
> using
> the application does not have dbo permission, we are planning to use
> a stored procedure with dynamic sql.
> SET @.RUNSQL = "alter table dbo.gggg .."
> EXEC(@.RUNSQL)
> The stored procedure is owned by dbo. However it is not allowing
> the alter table because of lack of permission. Does that mean
> that any EXEC inside a stored procedure does not run as user
> dbo.
> Is there a workaround for it?
> thanks.
>
|||This is a security feature.

> Is there a workaround for it?
In 2005, you can specify EXECUTE AS for the procedure.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<dcruncher4@.aim.com> wrote in message news:1140310808.885046.206220@.g47g2000cwa.googlegr oups.com...
> Hi,
> Our application needs to issue an alter table statement. Since the user
> using
> the application does not have dbo permission, we are planning to use
> a stored procedure with dynamic sql.
> SET @.RUNSQL = "alter table dbo.gggg .."
> EXEC(@.RUNSQL)
> The stored procedure is owned by dbo. However it is not allowing
> the alter table because of lack of permission. Does that mean
> that any EXEC inside a stored procedure does not run as user
> dbo.
> Is there a workaround for it?
> thanks.
>
|||To add to the other responses, an unbroken ownership chain (e.g. 'dbo' owns
all objects involved) does not change the execution context. With an
unbroken chain, *object* permissions are simply not checked on indirectly
referenced objects and note that dynamic SQL always breaks the ownership
chain. *Statement* permissions (e.g. ALTER TABLE) are always checked in the
execution security context. The execution context can't be changed on
versions prior to SQL 2005.
The need to execute DDL by non-privileged users and use dynamic SQL can
indicate an application design issue. Perhaps someone can suggest an
alternative if you provide the requirements driving this approach.
Hope this helps.
Dan Guzman
SQL Server MVP
<dcruncher4@.aim.com> wrote in message
news:1140310808.885046.206220@.g47g2000cwa.googlegr oups.com...
> Hi,
> Our application needs to issue an alter table statement. Since the user
> using
> the application does not have dbo permission, we are planning to use
> a stored procedure with dynamic sql.
> SET @.RUNSQL = "alter table dbo.gggg .."
> EXEC(@.RUNSQL)
> The stored procedure is owned by dbo. However it is not allowing
> the alter table because of lack of permission. Does that mean
> that any EXEC inside a stored procedure does not run as user
> dbo.
> Is there a workaround for it?
> thanks.
>

ALTER TABLE CHANGE question ...

I need to be able to change a table column name from within my C# code. The system in this part of the application is intended to be highly configurable and column names on the table being operated upon can change. The code adds square brackets to the column name (because the user might set up a column name with one or more spaces) but I'm not sure if I'm doing it right because I'm getting an error.

The code that builds the SQL command is:

SqlCommand =new SqlCommand("ALTER TABLE wto_facilities CHANGE [" +oldAccomType +"][" + accomType.Text +"] varchar(20)", conn);

 On the first test run, the code produces the following: ALTER TABLE wto_facilities CHANGE [Hotel] [Hotels] varchar(20)

However, it's giving me the following error: Incorrect syntax near 'Hotels'

It looks fine to me, but it's obviously not.

To change a column name you need to use sp_Rename

EXEC sp_rename 'table.column', 'newcolumnname', 'column'

Alter Stored Procedure with asp.net code

I have looked all around and I am having no luck trying to figure out how to alter a stored procedure within an asp.net application.

Here is a short snippet of my code, but it keeps erroring out on me.

Try
myCommand.CommandText = "Using " & DatabaseName & vbNewLine & Me.txtStoredProcedures.Text
myCommand.ExecuteNonQuery()
myTran.Commit()
Catch ex As Exception
myTran.Rollback()
Response.Write(ex.ToString())
End Try

The reason for this is because I have to propagate stored procedures across many databases and was hoping to write an application for it.

Basically the database name is coming from a loop statement and I just want to keep on going through all the databases that I have chosen and have the stored procedure updated (altered) automatically

So i thought the code above was close, but it keeps catching on me. Anybody's help would be greatly appreciated!!!

This is one of the things that make stored procedures a maintenance nightmare.

It should be "USE", not "USING". It may be an idea to use separate connections or at least to execute the USE statement separately.

|||

Well, I changed it to Use (I should have seen that already) and still got nothing. I did try to do one stored proc at a time, but it kept catching on me. Any other ideas?

|||

Why not just run the stored procedure create/update within Query Analyser / SQL Server Management Studio?

alter replication triggers

I have a customer application where I utilize merge replication between
sqlserver2000 and pocketpc's that are running sqlserverce2.0 (vb.net 2003).
It appears that the customer has some tables that they use to keep track of
who modifies the data in certain tables. they want the same functionality
from me.
Since I'm utilizing replication I'm not 100% sure how to accomplish this.
I looked at the trigger that replication creates for on insert . I was
thinking I could add the code necesary to populate the log table there, but
am afraid of screwing up the replication.
I'm not very strong on triggers, anybody have any ideas/suggestions?
Thanks
In SQL Server 2000 you can have multiple triggers on a table, so I'd just
create your own separate ones which can be added using sp_addscriptexec or
as part of the article's properties.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Allowing secure connections to SQL Server 2000 through a firewall

Hello,

My question is about allowing and securing connections to SQL Server 2000 over the internet. The company that I work for has an application server that several of our clients connect to via the internet using secure .NET remoting. Basically, the clients have a desktop application that they run that creates a remoting connection to our server software and we handle the server/database part. Anyway, one of our clients now wants to use Crystal Reports to run ad hoc queries on their data that is hosted on our SQL 2000 database server behind our firewall. Obviously, opening up a port in our firewall and allowing someone to run ad hoc queries on the database makes us all more than a little nervous about security.

Has anyone else here had to deal with this sort of situation before? We'd like to set up a secure, encrypted connection for this one client, but still keep it locked down for everyone else. Is it as simple as enabling encryption and generating SSL certificates for the client machine and our server? I've only been able to find a few resources that help with bits and pieces of the problem, never anything tackling the issue as a whole. If anyone has any thoughts, experiences, links, etc. to share it would be greatly appreciated. We are a small company and no one here has experience with this sort of thing.

Cheers!
Justin

Hi Lovero,

You could setup your SQL Server to doesn't use default port (1433). You will use custom port (10030) for example or another.

Good coding!

Javier Luna
http://guydotnetxmlwebservices.blogspot.com/

|||Yes, we certainly plan to change the default port on SQL Server in addition to any other steps we take. Actually, since there will be port forwarding from the firewall, I'm not sure that it's a required step, but probably a good idea. I'm much less sure about how to set up the whole security/encryption framework.|||

Yes, It is good idea :)

Good Coding!

Javier Luna
http://guydotnetxmlwebservices.blogspot.com/

Allowing ReadOnly rights to a database

Hi,
I am currently having problems assigning read only privileges to a database
for a specific user.
Basically, I have an application which uses Windows Authentication to access
a database on SQL server 2000. I only want this user to have read only access
to this database. I have added the user into the Logins in SQL server (i.e.
domain\username) and granted them the db_datareader role to that database. I
was assuming that this would only allow them to have read access to the
database, but this is not the case as they can add, modify and delete records.
Any help/advice would be appreciated as this is driving me mad.
Thanks,
Jen
> Basically, I have an application which uses Windows Authentication to
access
> a database on SQL server 2000. I only want this user to have read only
access
> to this database. I have added the user into the Logins in SQL server
(i.e.
> domain\username) and granted them the db_datareader role to that database.
I
> was assuming that this would only allow them to have read access to the
> database, but this is not the case as they can add, modify and delete
records.
Maybe you added logins to some server-wide fixed role, like sysadmins? Or
maybe the Public db role in the db mentioned has some permissions?
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
|||Jen,
You can create role as USER and map all other users to it ..and assign
permissions to role for specific tables :
sp_addlogin @.loginame ='test_user', @.passwd ='test_user', @.defdb ='mydb'
sp_grantdbaccess 'test_user'
sp_addrole 'general_users'
sp_addrolemember 'general_users' ,'test_user'
Regards,
Swati
"Jen" <Jen@.discussions.microsoft.com> wrote in message
news:4374CB40-DF56-48A6-9C38-C7AD20514F21@.microsoft.com...
> Hi,
> I am currently having problems assigning read only privileges to a
database
> for a specific user.
> Basically, I have an application which uses Windows Authentication to
access
> a database on SQL server 2000. I only want this user to have read only
access
> to this database. I have added the user into the Logins in SQL server
(i.e.
> domain\username) and granted them the db_datareader role to that database.
I
> was assuming that this would only allow them to have read access to the
> database, but this is not the case as they can add, modify and delete
records.
> Any help/advice would be appreciated as this is driving me mad.
> Thanks,
> Jen
>
|||The db_datareader gives read permission to your tables. you also need to add
the user to db_denydatawriter.
Sasan Saidi, MSc in cs
Senior DBA
Brascan Business Services
"I saw it work in a cartoon once so I am pretty sure I can do it."
"Jen" wrote:

> Hi,
> I am currently having problems assigning read only privileges to a database
> for a specific user.
> Basically, I have an application which uses Windows Authentication to access
> a database on SQL server 2000. I only want this user to have read only access
> to this database. I have added the user into the Logins in SQL server (i.e.
> domain\username) and granted them the db_datareader role to that database. I
> was assuming that this would only allow them to have read access to the
> database, but this is not the case as they can add, modify and delete records.
> Any help/advice would be appreciated as this is driving me mad.
> Thanks,
> Jen
>

Allowing ReadOnly rights to a database

Hi,
I am currently having problems assigning read only privileges to a database
for a specific user.
Basically, I have an application which uses Windows Authentication to access
a database on SQL server 2000. I only want this user to have read only access
to this database. I have added the user into the Logins in SQL server (i.e.
domain\username) and granted them the db_datareader role to that database. I
was assuming that this would only allow them to have read access to the
database, but this is not the case as they can add, modify and delete records.
Any help/advice would be appreciated as this is driving me mad.
Thanks,
Jen> Basically, I have an application which uses Windows Authentication to
access
> a database on SQL server 2000. I only want this user to have read only
access
> to this database. I have added the user into the Logins in SQL server
(i.e.
> domain\username) and granted them the db_datareader role to that database.
I
> was assuming that this would only allow them to have read access to the
> database, but this is not the case as they can add, modify and delete
records.
Maybe you added logins to some server-wide fixed role, like sysadmins? Or
maybe the Public db role in the db mentioned has some permissions?
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com|||Jen,
You can create role as USER and map all other users to it ..and assign
permissions to role for specific tables :
sp_addlogin @.loginame ='test_user', @.passwd ='test_user', @.defdb ='mydb'
sp_grantdbaccess 'test_user'
sp_addrole 'general_users'
sp_addrolemember 'general_users' ,'test_user'
Regards,
Swati
"Jen" <Jen@.discussions.microsoft.com> wrote in message
news:4374CB40-DF56-48A6-9C38-C7AD20514F21@.microsoft.com...
> Hi,
> I am currently having problems assigning read only privileges to a
database
> for a specific user.
> Basically, I have an application which uses Windows Authentication to
access
> a database on SQL server 2000. I only want this user to have read only
access
> to this database. I have added the user into the Logins in SQL server
(i.e.
> domain\username) and granted them the db_datareader role to that database.
I
> was assuming that this would only allow them to have read access to the
> database, but this is not the case as they can add, modify and delete
records.
> Any help/advice would be appreciated as this is driving me mad.
> Thanks,
> Jen
>|||Hello Jen,
The person in question is probably already in, but using a
role / group such as BUILTIN\ADMINISTRATORS.
Thats the reason why they can still do the delete, insert
and update stuff as well as the select.
Look under the security, logons and see which types are
Windows Groups, then have a chat to your Server Bods to
see if the user is already included in the group.
Peter
"A man is never more truthful than when he acknowledges
himself a liar."
Mark Twain
>--Original Message--
>Hi,
>I am currently having problems assigning read only
privileges to a database
>for a specific user.
>Basically, I have an application which uses Windows
Authentication to access
>a database on SQL server 2000. I only want this user to
have read only access
>to this database. I have added the user into the Logins
in SQL server (i.e.
>domain\username) and granted them the db_datareader role
to that database. I
>was assuming that this would only allow them to have read
access to the
>database, but this is not the case as they can add,
modify and delete records.
>Any help/advice would be appreciated as this is driving
me mad.
>Thanks,
>Jen
>.
>|||The db_datareader gives read permission to your tables. you also need to add
the user to db_denydatawriter.
--
Sasan Saidi, MSc in cs
Senior DBA
Brascan Business Services
"I saw it work in a cartoon once so I am pretty sure I can do it."
"Jen" wrote:
> Hi,
> I am currently having problems assigning read only privileges to a database
> for a specific user.
> Basically, I have an application which uses Windows Authentication to access
> a database on SQL server 2000. I only want this user to have read only access
> to this database. I have added the user into the Logins in SQL server (i.e.
> domain\username) and granted them the db_datareader role to that database. I
> was assuming that this would only allow them to have read access to the
> database, but this is not the case as they can add, modify and delete records.
> Any help/advice would be appreciated as this is driving me mad.
> Thanks,
> Jen
>

Allowing ReadOnly rights to a database

Hi,
I am currently having problems assigning read only privileges to a database
for a specific user.
Basically, I have an application which uses Windows Authentication to access
a database on SQL server 2000. I only want this user to have read only acces
s
to this database. I have added the user into the Logins in SQL server (i.e.
domain\username) and granted them the db_datareader role to that database. I
was assuming that this would only allow them to have read access to the
database, but this is not the case as they can add, modify and delete record
s.
Any help/advice would be appreciated as this is driving me mad.
Thanks,
Jen> Basically, I have an application which uses Windows Authentication to
access
> a database on SQL server 2000. I only want this user to have read only
access
> to this database. I have added the user into the Logins in SQL server
(i.e.
> domain\username) and granted them the db_datareader role to that database.
I
> was assuming that this would only allow them to have read access to the
> database, but this is not the case as they can add, modify and delete
records.
Maybe you added logins to some server-wide fixed role, like sysadmins? Or
maybe the Public db role in the db mentioned has some permissions?
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com|||Jen,
You can create role as USER and map all other users to it ..and assign
permissions to role for specific tables :
sp_addlogin @.loginame ='test_user', @.passwd ='test_user', @.defdb ='mydb'
sp_grantdbaccess 'test_user'
sp_addrole 'general_users'
sp_addrolemember 'general_users' ,'test_user'
Regards,
Swati
"Jen" <Jen@.discussions.microsoft.com> wrote in message
news:4374CB40-DF56-48A6-9C38-C7AD20514F21@.microsoft.com...
> Hi,
> I am currently having problems assigning read only privileges to a
database
> for a specific user.
> Basically, I have an application which uses Windows Authentication to
access
> a database on SQL server 2000. I only want this user to have read only
access
> to this database. I have added the user into the Logins in SQL server
(i.e.
> domain\username) and granted them the db_datareader role to that database.
I
> was assuming that this would only allow them to have read access to the
> database, but this is not the case as they can add, modify and delete
records.
> Any help/advice would be appreciated as this is driving me mad.
> Thanks,
> Jen
>|||The db_datareader gives read permission to your tables. you also need to add
the user to db_denydatawriter.
Sasan Saidi, MSc in cs
Senior DBA
Brascan Business Services
"I saw it work in a cartoon once so I am pretty sure I can do it."
"Jen" wrote:

> Hi,
> I am currently having problems assigning read only privileges to a databas
e
> for a specific user.
> Basically, I have an application which uses Windows Authentication to acce
ss
> a database on SQL server 2000. I only want this user to have read only acc
ess
> to this database. I have added the user into the Logins in SQL server (i.e
.
> domain\username) and granted them the db_datareader role to that database.
I
> was assuming that this would only allow them to have read access to the
> database, but this is not the case as they can add, modify and delete reco
rds.
> Any help/advice would be appreciated as this is driving me mad.
> Thanks,
> Jen
>

Allowing null dates

My users want to be able to enter nothing in a date field.

I'm using asp.net v2, vb.net, and VS 2005 for my application. I'm not sure what to do or what code to write to allow the user not to enter a date and keep from hitting the sqldatetime overflow error.

I could use some help.

Thanks

If the application variable/control value for the datatime value is empty (EmptyString), your application should set the input parameter to SqlType.dbNull -NOT the input control.text value.

|||

Thank you.

I figured out I needed to write that code in my business logic layer. I know this is an issue with a lot of people. Here is how I solved my problem.

I set the variable to Nullable (of DateTime)

And made the following check

If Not fldIntake.HasValue Then ClientApp.SetfldIntakeNull() Else ClientApp.fldIntake = fldIntake.Value

allowing non sysadmin user to send email with attachments

i am aware that only sysadmin can send attachments using sp_send_dbmail. but the problem is, i don wan my application login to have sysadmin role and wan it to be able to send email with attachments using sp_send_dbmail. i'm using a stored procedure to call sp_send_dbmail, anyway can i impersonate sysadmin inside the stored procedure to execute sp_send_dbmail?

any suggestion will be appreciated. thanks.

Which version of SQL Server are you using?|||i'm using sql server 2005 sp2|||

One possibility could be using impersonation (EXECUTE AS) on the module and/or digital signatures. The idea would be to create a wrapper under your control that would escalate the privileges and call sp_snd_dbmail.

Personally I am not quite familiar with the DB mail functionally, but based on the fact that the invocation is via a SP, I am not sure if digital signatures will be enough in this particular case.

Digital signatures should allow the caller to execute the module, but the signature would be dropped in order to execute the code on sp_send_dbmail; if the SP has internal checks to verify the calling context the call will be terminated. Impersonating a privileged user (member of sysadmin in this case) and using a digital signature to vouch for the token at server scope (the signing certificate would require AUTHENTICATE SERVER) should be an option if the digital signature by itself is not enough in this scenario.

I would like to emphasize that because the escalation (either via signature or via EXECUTE AS) is going to be to sysadmin, you need to be extremely careful and make sure your code is safe and there are no possibilities of running arbitrary code (i.e. no SQL injection is possible, validate any input, make sure the parameters to sp_send_dbmail are safe, limit the escalated functionality to the essential minimum, etc.) by anyone who has permission to execute this module.

For more detailed information please refer to BOL:

http://msdn2.microsoft.com/en-us/library/ms188304.aspx

http://msdn2.microsoft.com/en-us/library/ms345102.aspx

Let us know if you have further questions or comments

Thanks a lot,

-Raul Garcia

SDE/T

SQL Server Engine

|||

I forgot to include Laurentiu's article on cross-DB as a reference: http://blogs.msdn.com/lcris/archive/2006/10/24/sql-server-2005-demo-for-enabling-database-impersonation-for-cross-database-access.aspx

Thanks,

-Raul Garcia

SDE/T

SQL Server Engine

Allowing multi-element user defined custom data

We are creating a phonebook application which allows users to add custom
data to each entry, in the form of a (name):(value) pair. The users should
be able to add names of custom data types to a look-up table, and then be
able to add data values of that "type" to any entry in the phonebook. The
data will be saved as nvarchar.
However, we now realize some of this custom data will have to be made up
of several data elements in itself. For example: If the users want to add
the data type "address at in-law's", this custom data will be more than just
(name):(long string value), it will have to be (name):((street)(city)(state)(zipcode)).
We have several ideas on how to do this but they all seem cumbersome, and
they complicate the design a lot. Has anyone created a system like this before?
Is there a tried and true way of doing this?Hi
Have you thought of using XML for this?
John
"Ido Kalir" wrote:
> We are creating a phonebook application which allows users to add custom
> data to each entry, in the form of a (name):(value) pair. The users should
> be able to add names of custom data types to a look-up table, and then be
> able to add data values of that "type" to any entry in the phonebook. The
> data will be saved as nvarchar.
> However, we now realize some of this custom data will have to be made up
> of several data elements in itself. For example: If the users want to add
> the data type "address at in-law's", this custom data will be more than just
> (name):(long string value), it will have to be (name):((street)(city)(state)(zipcode)).
> We have several ideas on how to do this but they all seem cumbersome, and
> they complicate the design a lot. Has anyone created a system like this before?
> Is there a tried and true way of doing this?
>

Allowing an applicaiton to create it's own tables

I have recently come across a situation where someone has asked me to
give an application the ability to create it's own data tables, stored
procs and other items in SQL Server 2000 if they do not exist in the
database that the client has pointed the application too. I find this
very troubling and have come up with a number of reasons why I don't
think this is a good idea, but would like some input from any DBA's or
Microsoft MVPs that care to comment on this situation. I was
considering posting my own ideas, but I think I would rather compare my
idea's to what input I recieve from the community afterwards so that I
have not 'lead the witness' or tainted the input from the community.
Thank you all in advance for any input, I appreciate any professional
opinions I can get my hands on.
Sincerely
russ
I'm not an MVP, but I have an opinion. Not sure what kind of
applicaiton you're writing. If you're writing a database utility
(Enterprise Manger, Embarcadero, etc.) then sure, this sounds like
something that's well in the scope of that. If you're writing any sort
of app that's going to be used by a a non-developer/non-DBA, then why
would they need access? If you have a one user application and that one
user is the DBA, then yes - maybe. But - in general, very bad idea. I
agree with you. The user/application could wreak havoc on the DB.
rhaley@.axys.com wrote:
> I have recently come across a situation where someone has asked me to
> give an application the ability to create it's own data tables, stored
> procs and other items in SQL Server 2000 if they do not exist in the
> database that the client has pointed the application too. I find this
> very troubling and have come up with a number of reasons why I don't
> think this is a good idea, but would like some input from any DBA's or
> Microsoft MVPs that care to comment on this situation. I was
> considering posting my own ideas, but I think I would rather compare my
> idea's to what input I recieve from the community afterwards so that I
> have not 'lead the witness' or tainted the input from the community.
> Thank you all in advance for any input, I appreciate any professional
> opinions I can get my hands on.
> Sincerely
> russ
|||If this is for an end user, (?) granting an end user the ability to write
his own Stored Procs would be out of the question for most DBA's. I don't
know what these SP's may do, how much resources they may consume, what
security I would need to assign, what blocking/ deadlocking they may
introduce, etc.
<unc27932@.yahoo.com> wrote in message
news:1123175440.352742.216950@.g14g2000cwa.googlegr oups.com...
> I'm not an MVP, but I have an opinion. Not sure what kind of
> applicaiton you're writing. If you're writing a database utility
> (Enterprise Manger, Embarcadero, etc.) then sure, this sounds like
> something that's well in the scope of that. If you're writing any sort
> of app that's going to be used by a a non-developer/non-DBA, then why
> would they need access? If you have a one user application and that one
> user is the DBA, then yes - maybe. But - in general, very bad idea. I
> agree with you. The user/application could wreak havoc on the DB.
> rhaley@.axys.com wrote:
>
|||The customer wants an installation program? That's fine. The customer
wants an installation program that runs each time he starts up the app?
That's a bit more unusual! It seems to me that if the tables and procs
weren't there then I'd want to know about it, not have the DB
automatically create a clean installation.
What is the application for and why wouldn't the tables be there when
the app is run?
Also, if this is a multi-user app and you roll out a new release then
how will you ensure that everyone gets the new release simultaneously?
Otherwise an extant prior release could end up replacing your new
version of the database with an older one.
David Portas
SQL Server MVP
|||The idea from the client was that they would be able to point this
application at any database and use it as an 'Add On' application.
Although this idea is neat, it's kinda half baked (as he has agreed
since I showed him some comments).
All really great points guys, thank you very much. As you have all
pointed out, it's rather silly to have an application just re-create
tables and other DB items 'willy nilly' when it doesn't find them. Some
other issues I felt were of concern were:
- The only way to create tables in a database is to give the
application SA or CREATE TABLE rights on the database or database
instance, which is very uncool for an application that users have
access too.
- How do you ensure that the tables and SPs are of the same version
that the application needs without using System Tables or
INFORMATION_SCHEMA and itterating through every bloody column and
comparing data types and field lengths?
- What if user JSmith creates the tables and then later they change to
a different user? The application won't be able to find the tables
anymore and will re-create them, effectively 'losing' the old data
- "duh... Master sounds like a good database to put this in..."
- And last but not least, I'm on a small network and there are 17
exposed instances of SQL server/MSDE kicking around... have fun finding
the right 'version' of the tables ever again.
I think the solution is probably what David was hinting at - create a
separate installation program for the database and/or tables that
prompts for credentials (UID/PWD) so that an administrator with valid
sa or Create Table rights can take care of things. This will save alot
of headaches and keep a truck load of DBAs from coming over to my
office and lynching me!
Cheers guys!
Russ

Allowing an applicaiton to create it's own tables

I have recently come across a situation where someone has asked me to
give an application the ability to create it's own data tables, stored
procs and other items in SQL Server 2000 if they do not exist in the
database that the client has pointed the application too. I find this
very troubling and have come up with a number of reasons why I don't
think this is a good idea, but would like some input from any DBA's or
Microsoft MVPs that care to comment on this situation. I was
considering posting my own ideas, but I think I would rather compare my
idea's to what input I recieve from the community afterwards so that I
have not 'lead the witness' or tainted the input from the community.
Thank you all in advance for any input, I appreciate any professional
opinions I can get my hands on.
Sincerely
russI'm not an MVP, but I have an opinion. Not sure what kind of
applicaiton you're writing. If you're writing a database utility
(Enterprise Manger, Embarcadero, etc.) then sure, this sounds like
something that's well in the scope of that. If you're writing any sort
of app that's going to be used by a a non-developer/non-DBA, then why
would they need access? If you have a one user application and that one
user is the DBA, then yes - maybe. But - in general, very bad idea. I
agree with you. The user/application could wreak havoc on the DB.
rhaley@.axys.com wrote:
> I have recently come across a situation where someone has asked me to
> give an application the ability to create it's own data tables, stored
> procs and other items in SQL Server 2000 if they do not exist in the
> database that the client has pointed the application too. I find this
> very troubling and have come up with a number of reasons why I don't
> think this is a good idea, but would like some input from any DBA's or
> Microsoft MVPs that care to comment on this situation. I was
> considering posting my own ideas, but I think I would rather compare my
> idea's to what input I recieve from the community afterwards so that I
> have not 'lead the witness' or tainted the input from the community.
> Thank you all in advance for any input, I appreciate any professional
> opinions I can get my hands on.
> Sincerely
> russ|||If this is for an end user, (?) granting an end user the ability to write
his own Stored Procs would be out of the question for most DBA's. I don't
know what these SP's may do, how much resources they may consume, what
security I would need to assign, what blocking/ deadlocking they may
introduce, etc.
<unc27932@.yahoo.com> wrote in message
news:1123175440.352742.216950@.g14g2000cwa.googlegroups.com...
> I'm not an MVP, but I have an opinion. Not sure what kind of
> applicaiton you're writing. If you're writing a database utility
> (Enterprise Manger, Embarcadero, etc.) then sure, this sounds like
> something that's well in the scope of that. If you're writing any sort
> of app that's going to be used by a a non-developer/non-DBA, then why
> would they need access? If you have a one user application and that one
> user is the DBA, then yes - maybe. But - in general, very bad idea. I
> agree with you. The user/application could wreak havoc on the DB.
> rhaley@.axys.com wrote:
>|||The customer wants an installation program? That's fine. The customer
wants an installation program that runs each time he starts up the app?
That's a bit more unusual! It seems to me that if the tables and procs
weren't there then I'd want to know about it, not have the DB
automatically create a clean installation.
What is the application for and why wouldn't the tables be there when
the app is run?
Also, if this is a multi-user app and you roll out a new release then
how will you ensure that everyone gets the new release simultaneously?
Otherwise an extant prior release could end up replacing your new
version of the database with an older one.
David Portas
SQL Server MVP
--|||The idea from the client was that they would be able to point this
application at any database and use it as an 'Add On' application.
Although this idea is neat, it's kinda half baked (as he has agreed
since I showed him some comments).
All really great points guys, thank you very much. As you have all
pointed out, it's rather silly to have an application just re-create
tables and other DB items 'willy nilly' when it doesn't find them. Some
other issues I felt were of concern were:
- The only way to create tables in a database is to give the
application SA or CREATE TABLE rights on the database or database
instance, which is very uncool for an application that users have
access too.
- How do you ensure that the tables and SPs are of the same version
that the application needs without using System Tables or
INFORMATION_SCHEMA and itterating through every bloody column and
comparing data types and field lengths?
- What if user JSmith creates the tables and then later they change to
a different user? The application won't be able to find the tables
anymore and will re-create them, effectively 'losing' the old data
- "duh... Master sounds like a good database to put this in..."
- And last but not least, I'm on a small network and there are 17
exposed instances of SQL server/MSDE kicking around... have fun finding
the right 'version' of the tables ever again.
I think the solution is probably what David was hinting at - create a
separate installation program for the database and/or tables that
prompts for credentials (UID/PWD) so that an administrator with valid
sa or Create Table rights can take care of things. This will save alot
of headaches and keep a truck load of DBAs from coming over to my
office and lynching me!
Cheers guys!
Russ

Allowing an applicaiton to create it's own tables

I have recently come across a situation where someone has asked me to
give an application the ability to create it's own data tables, stored
procs and other items in SQL Server 2000 if they do not exist in the
database that the client has pointed the application too. I find this
very troubling and have come up with a number of reasons why I don't
think this is a good idea, but would like some input from any DBA's or
Microsoft MVPs that care to comment on this situation. I was
considering posting my own ideas, but I think I would rather compare my
idea's to what input I recieve from the community afterwards so that I
have not 'lead the witness' or tainted the input from the community.
Thank you all in advance for any input, I appreciate any professional
opinions I can get my hands on.
Sincerely
russI'm not an MVP, but I have an opinion. Not sure what kind of
applicaiton you're writing. If you're writing a database utility
(Enterprise Manger, Embarcadero, etc.) then sure, this sounds like
something that's well in the scope of that. If you're writing any sort
of app that's going to be used by a a non-developer/non-DBA, then why
would they need access? If you have a one user application and that one
user is the DBA, then yes - maybe. But - in general, very bad idea. I
agree with you. The user/application could wreak havoc on the DB.
rhaley@.axys.com wrote:
> I have recently come across a situation where someone has asked me to
> give an application the ability to create it's own data tables, stored
> procs and other items in SQL Server 2000 if they do not exist in the
> database that the client has pointed the application too. I find this
> very troubling and have come up with a number of reasons why I don't
> think this is a good idea, but would like some input from any DBA's or
> Microsoft MVPs that care to comment on this situation. I was
> considering posting my own ideas, but I think I would rather compare my
> idea's to what input I recieve from the community afterwards so that I
> have not 'lead the witness' or tainted the input from the community.
> Thank you all in advance for any input, I appreciate any professional
> opinions I can get my hands on.
> Sincerely
> russ|||If this is for an end user, (?) granting an end user the ability to write
his own Stored Procs would be out of the question for most DBA's. I don't
know what these SP's may do, how much resources they may consume, what
security I would need to assign, what blocking/ deadlocking they may
introduce, etc.
<unc27932@.yahoo.com> wrote in message
news:1123175440.352742.216950@.g14g2000cwa.googlegroups.com...
> I'm not an MVP, but I have an opinion. Not sure what kind of
> applicaiton you're writing. If you're writing a database utility
> (Enterprise Manger, Embarcadero, etc.) then sure, this sounds like
> something that's well in the scope of that. If you're writing any sort
> of app that's going to be used by a a non-developer/non-DBA, then why
> would they need access? If you have a one user application and that one
> user is the DBA, then yes - maybe. But - in general, very bad idea. I
> agree with you. The user/application could wreak havoc on the DB.
> rhaley@.axys.com wrote:
>> I have recently come across a situation where someone has asked me to
>> give an application the ability to create it's own data tables, stored
>> procs and other items in SQL Server 2000 if they do not exist in the
>> database that the client has pointed the application too. I find this
>> very troubling and have come up with a number of reasons why I don't
>> think this is a good idea, but would like some input from any DBA's or
>> Microsoft MVPs that care to comment on this situation. I was
>> considering posting my own ideas, but I think I would rather compare my
>> idea's to what input I recieve from the community afterwards so that I
>> have not 'lead the witness' or tainted the input from the community.
>> Thank you all in advance for any input, I appreciate any professional
>> opinions I can get my hands on.
>> Sincerely
>> russ
>|||The customer wants an installation program? That's fine. The customer
wants an installation program that runs each time he starts up the app?
That's a bit more unusual! It seems to me that if the tables and procs
weren't there then I'd want to know about it, not have the DB
automatically create a clean installation.
What is the application for and why wouldn't the tables be there when
the app is run?
Also, if this is a multi-user app and you roll out a new release then
how will you ensure that everyone gets the new release simultaneously?
Otherwise an extant prior release could end up replacing your new
version of the database with an older one.
--
David Portas
SQL Server MVP
--|||The idea from the client was that they would be able to point this
application at any database and use it as an 'Add On' application.
Although this idea is neat, it's kinda half baked (as he has agreed
since I showed him some comments).
All really great points guys, thank you very much. As you have all
pointed out, it's rather silly to have an application just re-create
tables and other DB items 'willy nilly' when it doesn't find them. Some
other issues I felt were of concern were:
- The only way to create tables in a database is to give the
application SA or CREATE TABLE rights on the database or database
instance, which is very uncool for an application that users have
access too.
- How do you ensure that the tables and SPs are of the same version
that the application needs without using System Tables or
INFORMATION_SCHEMA and itterating through every bloody column and
comparing data types and field lengths?
- What if user JSmith creates the tables and then later they change to
a different user? The application won't be able to find the tables
anymore and will re-create them, effectively 'losing' the old data
- "duh... Master sounds like a good database to put this in..."
- And last but not least, I'm on a small network and there are 17
exposed instances of SQL server/MSDE kicking around... have fun finding
the right 'version' of the tables ever again.
I think the solution is probably what David was hinting at - create a
separate installation program for the database and/or tables that
prompts for credentials (UID/PWD) so that an administrator with valid
sa or Create Table rights can take care of things. This will save alot
of headaches and keep a truck load of DBAs from coming over to my
office and lynching me!
Cheers guys!
Russ

Allow Multiple Parameters form Windows Application

I have been working through a solution to allow multiple parameters to be
passed into a SQL RS report. I have created a windows application that calls
sql reporting services. The problem that I am having now is passing the
multiple values the user selects from the dropdown to sql reporting services.
I am setting: returnValues.Value
When I try to set this parameters to 1;2;3, report fails. However, setting
that value to 1 works.
Is this possbile?RS 2000 does not support multiple selections. For instance,
select * from blah where somefield in (@.Param)
will not work. What you can do is use either an expression or call a stored
procedure that takes the parameter and handles appropriately).
This will work:
= "select * from blah where somefield in (" & Parameters!Paramname.value &
")"
Note that this assume you have dealt with putting in all the proper syntax
like single quotes around charater type parameters, etc and that this will
be a valid query when done.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Ms Code Buster" <MsCodeBuster@.discussions.microsoft.com> wrote in message
news:07E56EC7-8D86-492E-A751-199C8E4D8869@.microsoft.com...
>I have been working through a solution to allow multiple parameters to be
> passed into a SQL RS report. I have created a windows application that
> calls
> sql reporting services. The problem that I am having now is passing the
> multiple values the user selects from the dropdown to sql reporting
> services.
>
> I am setting: returnValues.Value
> When I try to set this parameters to 1;2;3, report fails. However,
> setting
> that value to 1 works.
> Is this possbile?|||I have been able to get the mulitple selection parameters to work if I call
the SQL RS report from a web page. I basically created dropdown listboxes
and have the form post to the url of the page I want to run. The multiple
selection values are sent to the report and the report works fine. However,
I am trying to call SQL RS report from a Windows Application. I have my
report created in a way that it uses a stored procedure to parse the multiple
values passed to in and joins to those values from a temp table.
"Bruce L-C [MVP]" wrote:
> RS 2000 does not support multiple selections. For instance,
> select * from blah where somefield in (@.Param)
> will not work. What you can do is use either an expression or call a stored
> procedure that takes the parameter and handles appropriately).
> This will work:
> = "select * from blah where somefield in (" & Parameters!Paramname.value &
> ")"
> Note that this assume you have dealt with putting in all the proper syntax
> like single quotes around charater type parameters, etc and that this will
> be a valid query when done.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Ms Code Buster" <MsCodeBuster@.discussions.microsoft.com> wrote in message
> news:07E56EC7-8D86-492E-A751-199C8E4D8869@.microsoft.com...
> >I have been working through a solution to allow multiple parameters to be
> > passed into a SQL RS report. I have created a windows application that
> > calls
> > sql reporting services. The problem that I am having now is passing the
> > multiple values the user selects from the dropdown to sql reporting
> > services.
> >
> >
> > I am setting: returnValues.Value
> >
> > When I try to set this parameters to 1;2;3, report fails. However,
> > setting
> > that value to 1 works.
> >
> > Is this possbile?
>
>|||OK, so you are doing the stored procedure method. That a good way to do it.
This whole thing work if from a web page passing in the multiple selections,
the only difference is that you are doing this from a windows app?
How are you integrating your windows app? URL integration or web services?
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Ms Code Buster" <MsCodeBuster@.discussions.microsoft.com> wrote in message
news:89FA2276-2654-40C1-B03C-EFE7F1B5D0F9@.microsoft.com...
>I have been able to get the mulitple selection parameters to work if I call
> the SQL RS report from a web page. I basically created dropdown listboxes
> and have the form post to the url of the page I want to run. The multiple
> selection values are sent to the report and the report works fine.
> However,
> I am trying to call SQL RS report from a Windows Application. I have my
> report created in a way that it uses a stored procedure to parse the
> multiple
> values passed to in and joins to those values from a temp table.
> "Bruce L-C [MVP]" wrote:
>> RS 2000 does not support multiple selections. For instance,
>> select * from blah where somefield in (@.Param)
>> will not work. What you can do is use either an expression or call a
>> stored
>> procedure that takes the parameter and handles appropriately).
>> This will work:
>> = "select * from blah where somefield in (" & Parameters!Paramname.value
>> &
>> ")"
>> Note that this assume you have dealt with putting in all the proper
>> syntax
>> like single quotes around charater type parameters, etc and that this
>> will
>> be a valid query when done.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>>
>> "Ms Code Buster" <MsCodeBuster@.discussions.microsoft.com> wrote in
>> message
>> news:07E56EC7-8D86-492E-A751-199C8E4D8869@.microsoft.com...
>> >I have been working through a solution to allow multiple parameters to
>> >be
>> > passed into a SQL RS report. I have created a windows application that
>> > calls
>> > sql reporting services. The problem that I am having now is passing
>> > the
>> > multiple values the user selects from the dropdown to sql reporting
>> > services.
>> >
>> >
>> > I am setting: returnValues.Value
>> >
>> > When I try to set this parameters to 1;2;3, report fails. However,
>> > setting
>> > that value to 1 works.
>> >
>> > Is this possbile?
>>|||I actually need the ability to write the report to a file or display the
report in a web browser on the screen. In both cases I have to set the
Parameter values using an array.
"Bruce L-C [MVP]" wrote:
> OK, so you are doing the stored procedure method. That a good way to do it.
> This whole thing work if from a web page passing in the multiple selections,
> the only difference is that you are doing this from a windows app?
> How are you integrating your windows app? URL integration or web services?
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Ms Code Buster" <MsCodeBuster@.discussions.microsoft.com> wrote in message
> news:89FA2276-2654-40C1-B03C-EFE7F1B5D0F9@.microsoft.com...
> >I have been able to get the mulitple selection parameters to work if I call
> > the SQL RS report from a web page. I basically created dropdown listboxes
> > and have the form post to the url of the page I want to run. The multiple
> > selection values are sent to the report and the report works fine.
> > However,
> > I am trying to call SQL RS report from a Windows Application. I have my
> > report created in a way that it uses a stored procedure to parse the
> > multiple
> > values passed to in and joins to those values from a temp table.
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> RS 2000 does not support multiple selections. For instance,
> >> select * from blah where somefield in (@.Param)
> >>
> >> will not work. What you can do is use either an expression or call a
> >> stored
> >> procedure that takes the parameter and handles appropriately).
> >>
> >> This will work:
> >>
> >> = "select * from blah where somefield in (" & Parameters!Paramname.value
> >> &
> >> ")"
> >>
> >> Note that this assume you have dealt with putting in all the proper
> >> syntax
> >> like single quotes around charater type parameters, etc and that this
> >> will
> >> be a valid query when done.
> >>
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >>
> >>
> >> "Ms Code Buster" <MsCodeBuster@.discussions.microsoft.com> wrote in
> >> message
> >> news:07E56EC7-8D86-492E-A751-199C8E4D8869@.microsoft.com...
> >> >I have been working through a solution to allow multiple parameters to
> >> >be
> >> > passed into a SQL RS report. I have created a windows application that
> >> > calls
> >> > sql reporting services. The problem that I am having now is passing
> >> > the
> >> > multiple values the user selects from the dropdown to sql reporting
> >> > services.
> >> >
> >> >
> >> > I am setting: returnValues.Value
> >> >
> >> > When I try to set this parameters to 1;2;3, report fails. However,
> >> > setting
> >> > that value to 1 works.
> >> >
> >> > Is this possbile?
> >>
> >>
> >>
>
>|||What I was trying to clarify is what does and does not work.
It sounds like calling the report from a web page using URL integration does
work. But, you are trying to use web services from your windows app (as an
alternative you can embed an IE control and use URL integration). Since you
say that a single value selected work, I wonder if some seperator character
is causing a problem. In your windows app try having a textbox that you key
in the correct value and see if that works.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Ms Code Buster" <MsCodeBuster@.discussions.microsoft.com> wrote in message
news:A07E6166-50E6-44D8-8ED4-3E3C279EAB1E@.microsoft.com...
>I actually need the ability to write the report to a file or display the
> report in a web browser on the screen. In both cases I have to set the
> Parameter values using an array.
> "Bruce L-C [MVP]" wrote:
>> OK, so you are doing the stored procedure method. That a good way to do
>> it.
>> This whole thing work if from a web page passing in the multiple
>> selections,
>> the only difference is that you are doing this from a windows app?
>> How are you integrating your windows app? URL integration or web
>> services?
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>>
>> "Ms Code Buster" <MsCodeBuster@.discussions.microsoft.com> wrote in
>> message
>> news:89FA2276-2654-40C1-B03C-EFE7F1B5D0F9@.microsoft.com...
>> >I have been able to get the mulitple selection parameters to work if I
>> >call
>> > the SQL RS report from a web page. I basically created dropdown
>> > listboxes
>> > and have the form post to the url of the page I want to run. The
>> > multiple
>> > selection values are sent to the report and the report works fine.
>> > However,
>> > I am trying to call SQL RS report from a Windows Application. I have
>> > my
>> > report created in a way that it uses a stored procedure to parse the
>> > multiple
>> > values passed to in and joins to those values from a temp table.
>> >
>> > "Bruce L-C [MVP]" wrote:
>> >
>> >> RS 2000 does not support multiple selections. For instance,
>> >> select * from blah where somefield in (@.Param)
>> >>
>> >> will not work. What you can do is use either an expression or call a
>> >> stored
>> >> procedure that takes the parameter and handles appropriately).
>> >>
>> >> This will work:
>> >>
>> >> = "select * from blah where somefield in (" &
>> >> Parameters!Paramname.value
>> >> &
>> >> ")"
>> >>
>> >> Note that this assume you have dealt with putting in all the proper
>> >> syntax
>> >> like single quotes around charater type parameters, etc and that this
>> >> will
>> >> be a valid query when done.
>> >>
>> >>
>> >> --
>> >> Bruce Loehle-Conger
>> >> MVP SQL Server Reporting Services
>> >>
>> >>
>> >>
>> >> "Ms Code Buster" <MsCodeBuster@.discussions.microsoft.com> wrote in
>> >> message
>> >> news:07E56EC7-8D86-492E-A751-199C8E4D8869@.microsoft.com...
>> >> >I have been working through a solution to allow multiple parameters
>> >> >to
>> >> >be
>> >> > passed into a SQL RS report. I have created a windows application
>> >> > that
>> >> > calls
>> >> > sql reporting services. The problem that I am having now is passing
>> >> > the
>> >> > multiple values the user selects from the dropdown to sql reporting
>> >> > services.
>> >> >
>> >> >
>> >> > I am setting: returnValues.Value
>> >> >
>> >> > When I try to set this parameters to 1;2;3, report fails. However,
>> >> > setting
>> >> > that value to 1 works.
>> >> >
>> >> > Is this possbile?
>> >>
>> >>
>> >>
>>