Script User Rights

Is there an easy way to capture existing user rights from a SQL Server
instance in a scripted format and simply run the script in a new SQL Server
instance to set all of the user privileges? Thank you.Hi ,
You could use the Enterprise Manager to Script user Rights , Right click on
the database Click on all tasks -> Generate SQL Scripts -> goto Last Tab and
look at the security options.
Hope this helps
Cheers
Vishal Gandhi
"CR" wrote:

> Is there an easy way to capture existing user rights from a SQL Server
> instance in a scripted format and simply run the script in a new SQL Serve
r
> instance to set all of the user privileges? Thank you.
>
>|||Thanks. However, I get "[SQL-DMO]CreateFile error on
"servername.databasename.DP1' error when I try to generate script?
"Vishal Gandhi" <VishalGandhi@.discussions.microsoft.com> wrote in message
news:0C04E96C-CDD9-4F54-B5CA-C213CAD97127@.microsoft.com...
> Hi ,
> You could use the Enterprise Manager to Script user Rights , Right click
on
> the database Click on all tasks -> Generate SQL Scripts -> goto Last Tab
and[vbcol=seagreen]
> look at the security options.
> Hope this helps
> Cheers
> Vishal Gandhi
>
> "CR" wrote:
>
Server[vbcol=seagreen]

Script to change a connection manager

I am looking for code or assistance on how to modify an existing conneciton manger via a script component. I want to have a script change the column sizes for an existing connection manager. How can I get a specific connection manger to change its values.

I have looked at the connection manager, connections, etc classes but I am not certain how to do this as .NET is not my cup of tea.

Thanks.

Once set up though, you can't change column sizes because it will break any associated metadata in the package.|||

Not what I wanted to hear....

Maybe I was thinking about this wrong. If I can determine the file type, I could just change the connection expression for the various components based on a variable.

|||

1Dave wrote:

Not what I wanted to hear....

Well, SSIS can't just dynamically adjust itself depending on any changes to the source. Just not going to happen. Too many components rely on knowing the metadata in advance.

1Dave wrote:

Maybe I was thinking about this wrong. If I can determine the file type, I could just change the connection expression for the various components based on a variable.

Package configurations can change connection strings at run time. You can use an XML file, SQL Server based configurations, etc... You can also override connection strings on the command line, I believe. If you choose to store a connection string in a variable, that variable can be overwritten via the command line as well.|||

Let me explain the issue a little more.

I get a flat file from a source that is not reliable. The file is fixed width style. Sometimes the file is 1200 chars and other times it is 1201. The column that is off is a filler column. But this one character causes wrapping issues that the data on each row is off by one if the file is 1201. So I wanted to just alter the connections filler column on the fly. How do I deal with this so that I dont duplicate code... I just want it to do all the same steps and mapp all the same fields regardless of file size.

|||

1Dave wrote:

Let me explain the issue a little more.

I get a flat file from a source that is not reliable. The file is fixed width style. Sometimes the file is 1200 chars and other times it is 1201. The column that is off is a filler column. But this one character causes wrapping issues that the data on each row is off by one if the file is 1201. So I wanted to just alter the connections filler column on the fly. How do I deal with this so that I dont duplicate code... I just want it to do all the same steps and mapp all the same fields regardless of file size.

You could treat it as a variable column file and parse it manually inside your data flow with either script or a complex derived column. The connection manager would read the whole row as a single column.

Or, following your original path, you could use code to modify *another* package and execute it. Your original approach won't work because you can't modify the current package. You can modify another package and run that one against your file.

Script to alter Identity, Identity Seed, Identity Increment

Hi all
I need some Help in creating a SQL Script which I can Run direct on MS SQL
2000 Server.
Of existing tables I want to change the:
- Identity
- Identity Seed
- Identity Increment
I already tried something with ALTER TABLE, but I guess I did something
wrong, did not work sofar.
Would be glad if someone could give me an example of how to do this.
Thanks for the help
Cheers
MarcelMarcel
1) What do you mean "change indentity"? You can drop the column defined as
an IDENTITY property
2)DBCC CHECKIDENT
3) DROP column and re-create with a new Increment
"Marcel Stoop" <marcel.stoop@.synspace.com> wrote in message
news:%23ceE0KeSHHA.4844@.TK2MSFTNGP03.phx.gbl...
> Hi all
> I need some Help in creating a SQL Script which I can Run direct on MS SQL
> 2000 Server.
> Of existing tables I want to change the:
> - Identity
> - Identity Seed
> - Identity Increment
> I already tried something with ALTER TABLE, but I guess I did something
> wrong, did not work sofar.
> Would be glad if someone could give me an example of how to do this.
> Thanks for the help
> Cheers
> Marcel
>|||On Feb 6, 1:33 pm, "Marcel Stoop" <marcel.st...@.synspace.com> wrote:
> Hi all
> I need some Help in creating a SQL Script which I can Run direct on MS SQL
> 2000 Server.
>
You can change the identity seed with dbcc checkident (you can see
more details in BOL). I don't know of any supported way to modify
the identity increment.
Adi
> Of existing tables I want to change the:
> - Identity
> - Identity Seed
> - Identity Increment
> I already tried something with ALTER TABLE, but I guess I did something
> wrong, did not work sofar.
> Would be glad if someone could give me an example of how to do this.
> Thanks for the help
> Cheers
> Marcel|||With change I mean:
For Table X, the Collumn Name with the Primary Key, has Identity set to No
For this Collumn I want to Set the Identity to:Yes, with Identity Seed = 0
and Identity Increment = 1.
Because I have to do this more then once for several Tables, I do not want
to do this manually but through a Script.
The thing is: do not have a clue how to do it.
Cheers
Marcel
"Uri Dimant" <urid@.iscar.co.il> schrieb im Newsbeitrag
news:ebvO2SeSHHA.4260@.TK2MSFTNGP06.phx.gbl...
> Marcel
> 1) What do you mean "change indentity"? You can drop the column defined as
> an IDENTITY property
> 2)DBCC CHECKIDENT
> 3) DROP column and re-create with a new Increment
>
>
> "Marcel Stoop" <marcel.stoop@.synspace.com> wrote in message
> news:%23ceE0KeSHHA.4844@.TK2MSFTNGP03.phx.gbl...
>> Hi all
>> I need some Help in creating a SQL Script which I can Run direct on MS
>> SQL 2000 Server.
>> Of existing tables I want to change the:
>> - Identity
>> - Identity Seed
>> - Identity Increment
>> I already tried something with ALTER TABLE, but I guess I did something
>> wrong, did not work sofar.
>> Would be glad if someone could give me an example of how to do this.
>> Thanks for the help
>> Cheers
>> Marcel
>|||Marcel
> With change I mean:
> For Table X, the Collumn Name with the Primary Key, has Identity set to No
> For this Collumn I want to Set the Identity to:Yes, with Identity Seed = 0
> and Identity Increment = 1.
You cannot do that
CREATE TABLE Test (c INT NOT NULL)
INSERT INTO Test VALUES (1)
INSERT INTO Test VALUES (2)
--Want to add an IDENTITY Property
ALTER TABLE Test ADD c1 INT NOT NULL IDENTITY(1,1)
GO
ALTER TABLE Test DROP COLUMN c
GO
sp_rename 'Test.c1','c','column'
GO
SELECT * FROM Test
DROP TABLE Test
"Marcel Stoop" <marcel.stoop@.synspace.com> wrote in message
news:OI0qPKfSHHA.4956@.TK2MSFTNGP04.phx.gbl...
> With change I mean:
> For Table X, the Collumn Name with the Primary Key, has Identity set to No
> For this Collumn I want to Set the Identity to:Yes, with Identity Seed = 0
> and Identity Increment = 1.
> Because I have to do this more then once for several Tables, I do not want
> to do this manually but through a Script.
> The thing is: do not have a clue how to do it.
> Cheers
> Marcel
>
> "Uri Dimant" <urid@.iscar.co.il> schrieb im Newsbeitrag
> news:ebvO2SeSHHA.4260@.TK2MSFTNGP06.phx.gbl...
>> Marcel
>> 1) What do you mean "change indentity"? You can drop the column defined
>> as an IDENTITY property
>> 2)DBCC CHECKIDENT
>> 3) DROP column and re-create with a new Increment
>>
>>
>> "Marcel Stoop" <marcel.stoop@.synspace.com> wrote in message
>> news:%23ceE0KeSHHA.4844@.TK2MSFTNGP03.phx.gbl...
>> Hi all
>> I need some Help in creating a SQL Script which I can Run direct on MS
>> SQL 2000 Server.
>> Of existing tables I want to change the:
>> - Identity
>> - Identity Seed
>> - Identity Increment
>> I already tried something with ALTER TABLE, but I guess I did something
>> wrong, did not work sofar.
>> Would be glad if someone could give me an example of how to do this.
>> Thanks for the help
>> Cheers
>> Marcel
>>
>|||Thanks for the Answer
I will try that with ALTER TABLE
Cheers
Marcel
"Uri Dimant" <urid@.iscar.co.il> schrieb im Newsbeitrag
news:eQO0vRfSHHA.2212@.TK2MSFTNGP02.phx.gbl...
> Marcel
>> With change I mean:
>> For Table X, the Collumn Name with the Primary Key, has Identity set to
>> No
>> For this Collumn I want to Set the Identity to:Yes, with Identity Seed =>> 0 and Identity Increment = 1.
> You cannot do that
> CREATE TABLE Test (c INT NOT NULL)
> INSERT INTO Test VALUES (1)
> INSERT INTO Test VALUES (2)
> --Want to add an IDENTITY Property
> ALTER TABLE Test ADD c1 INT NOT NULL IDENTITY(1,1)
> GO
> ALTER TABLE Test DROP COLUMN c
> GO
> sp_rename 'Test.c1','c','column'
> GO
> SELECT * FROM Test
> DROP TABLE Test
>
>
> "Marcel Stoop" <marcel.stoop@.synspace.com> wrote in message
> news:OI0qPKfSHHA.4956@.TK2MSFTNGP04.phx.gbl...
>> With change I mean:
>> For Table X, the Collumn Name with the Primary Key, has Identity set to
>> No
>> For this Collumn I want to Set the Identity to:Yes, with Identity Seed =>> 0 and Identity Increment = 1.
>> Because I have to do this more then once for several Tables, I do not
>> want to do this manually but through a Script.
>> The thing is: do not have a clue how to do it.
>> Cheers
>> Marcel
>>
>> "Uri Dimant" <urid@.iscar.co.il> schrieb im Newsbeitrag
>> news:ebvO2SeSHHA.4260@.TK2MSFTNGP06.phx.gbl...
>> Marcel
>> 1) What do you mean "change indentity"? You can drop the column defined
>> as an IDENTITY property
>> 2)DBCC CHECKIDENT
>> 3) DROP column and re-create with a new Increment
>>
>>
>> "Marcel Stoop" <marcel.stoop@.synspace.com> wrote in message
>> news:%23ceE0KeSHHA.4844@.TK2MSFTNGP03.phx.gbl...
>> Hi all
>> I need some Help in creating a SQL Script which I can Run direct on MS
>> SQL 2000 Server.
>> Of existing tables I want to change the:
>> - Identity
>> - Identity Seed
>> - Identity Increment
>> I already tried something with ALTER TABLE, but I guess I did something
>> wrong, did not work sofar.
>> Would be glad if someone could give me an example of how to do this.
>> Thanks for the help
>> Cheers
>> Marcel
>>
>>
>

Script to alter Identity, Identity Seed, Identity Increment

Hi all
I need some Help in creating a SQL Script which I can Run direct on MS SQL
2000 Server.
Of existing tables I want to change the:
- Identity
- Identity Seed
- Identity Increment
I already tried something with ALTER TABLE, but I guess I did something
wrong, did not work sofar.
Would be glad if someone could give me an example of how to do this.
Thanks for the help
Cheers
MarcelMarcel
1) What do you mean "change indentity"? You can drop the column defined as
an IDENTITY property
2)DBCC CHECKIDENT
3) DROP column and re-create with a new Increment
"Marcel Stoop" <marcel.stoop@.synspace.com> wrote in message
news:%23ceE0KeSHHA.4844@.TK2MSFTNGP03.phx.gbl...
> Hi all
> I need some Help in creating a SQL Script which I can Run direct on MS SQL
> 2000 Server.
> Of existing tables I want to change the:
> - Identity
> - Identity Seed
> - Identity Increment
> I already tried something with ALTER TABLE, but I guess I did something
> wrong, did not work sofar.
> Would be glad if someone could give me an example of how to do this.
> Thanks for the help
> Cheers
> Marcel
>|||On Feb 6, 1:33 pm, "Marcel Stoop" <marcel.st...@.synspace.com> wrote:
> Hi all
> I need some Help in creating a SQL Script which I can Run direct on MS SQL
> 2000 Server.
>
You can change the identity seed with dbcc checkident (you can see
more details in BOL). I don't know of any supported way to modify
the identity increment.
Adi

> Of existing tables I want to change the:
> - Identity
> - Identity Seed
> - Identity Increment
> I already tried something with ALTER TABLE, but I guess I did something
> wrong, did not work sofar.
> Would be glad if someone could give me an example of how to do this.
> Thanks for the help
> Cheers
> Marcel|||With change I mean:
For Table X, the Collumn Name with the Primary Key, has Identity set to No
For this Collumn I want to Set the Identity to:Yes, with Identity Seed = 0
and Identity Increment = 1.
Because I have to do this more then once for several Tables, I do not want
to do this manually but through a Script.
The thing is: do not have a clue how to do it.
Cheers
Marcel
"Uri Dimant" <urid@.iscar.co.il> schrieb im Newsbeitrag
news:ebvO2SeSHHA.4260@.TK2MSFTNGP06.phx.gbl...
> Marcel
> 1) What do you mean "change indentity"? You can drop the column defined as
> an IDENTITY property
> 2)DBCC CHECKIDENT
> 3) DROP column and re-create with a new Increment
>
>
> "Marcel Stoop" <marcel.stoop@.synspace.com> wrote in message
> news:%23ceE0KeSHHA.4844@.TK2MSFTNGP03.phx.gbl...
>|||Marcel
> With change I mean:
> For Table X, the Collumn Name with the Primary Key, has Identity set to No
> For this Collumn I want to Set the Identity to:Yes, with Identity Seed = 0
> and Identity Increment = 1.
You cannot do that
CREATE TABLE Test (c INT NOT NULL)
INSERT INTO Test VALUES (1)
INSERT INTO Test VALUES (2)
--Want to add an IDENTITY Property
ALTER TABLE Test ADD c1 INT NOT NULL IDENTITY(1,1)
GO
ALTER TABLE Test DROP COLUMN c
GO
sp_rename 'Test.c1','c','column'
GO
SELECT * FROM Test
DROP TABLE Test
"Marcel Stoop" <marcel.stoop@.synspace.com> wrote in message
news:OI0qPKfSHHA.4956@.TK2MSFTNGP04.phx.gbl...
> With change I mean:
> For Table X, the Collumn Name with the Primary Key, has Identity set to No
> For this Collumn I want to Set the Identity to:Yes, with Identity Seed = 0
> and Identity Increment = 1.
> Because I have to do this more then once for several Tables, I do not want
> to do this manually but through a Script.
> The thing is: do not have a clue how to do it.
> Cheers
> Marcel
>
> "Uri Dimant" <urid@.iscar.co.il> schrieb im Newsbeitrag
> news:ebvO2SeSHHA.4260@.TK2MSFTNGP06.phx.gbl...
>|||Thanks for the Answer
I will try that with ALTER TABLE
Cheers
Marcel
"Uri Dimant" <urid@.iscar.co.il> schrieb im Newsbeitrag
news:eQO0vRfSHHA.2212@.TK2MSFTNGP02.phx.gbl...
> Marcel
> You cannot do that
> CREATE TABLE Test (c INT NOT NULL)
> INSERT INTO Test VALUES (1)
> INSERT INTO Test VALUES (2)
> --Want to add an IDENTITY Property
> ALTER TABLE Test ADD c1 INT NOT NULL IDENTITY(1,1)
> GO
> ALTER TABLE Test DROP COLUMN c
> GO
> sp_rename 'Test.c1','c','column'
> GO
> SELECT * FROM Test
> DROP TABLE Test
>
>
> "Marcel Stoop" <marcel.stoop@.synspace.com> wrote in message
> news:OI0qPKfSHHA.4956@.TK2MSFTNGP04.phx.gbl...
>

SCRIPT TO ALTER for functions in a SQL 2000

I installed SQL Server Management Express and try to access to a SQL Server 2000 remotly.

I connected correctly but when I try to alter an existing function, I get the following error message:

TITLE: Microsoft SQL Server Management Studio Express

Property AnsiNullsStatus is not available for UserDefinedFunction '[dbo].[functionName]'. This property may not exist for this object, or may not be retrievable due to insufficient access rights. (Microsoft.SqlServer.Express.Smo)

Note that functionName contain the correct function name.

Can someone confirm me that SQL Server Managmt Express can not alter function from previous SQL version or may I need to set some parameters on Server 2000.

Thanks

I'm moving this to the Tools forum.

Mike

sql

script index

i have four indexes on a table.

i was wondering if anybody can tell me how to script an existing index?

i know how to do this in 2000...wondering how to do in 2005

thanks

? Hi RookieDBA, In Query Analyzer: 1. Hit F8 to open the object explorer; 2. Locate correct database; go to User Tables, find right table and drill down to "Indexes" 3. Right-click Index; choose Script Object to New Window As / Create -- Hugo Kornelis, SQL Server MVP <RookieDBA@.discussions..microsoft.com> schreef in bericht news:ec51452a-622b-48dc-aa2b-dc2ebb7db970@.discussions.microsoft.com... i have four indexes on a table. i was wondering if anybody can tell me how to script an existing index? i.e the sql used to create the index thanks

Scoped calulated measures for unrelated dimensions

Hi,

I am not sure if this is a correct way to design this,

Here is my scenario:

We are migrating an existing databwarehouse application from vb, which used to pull out records using the criteria provided in the input screen. The input conditions are many and multiple selections and combinations constitute the final data that is returned in excel format.

Most of the input conditions specified are filters or slicing of data, which we have modelled as dimensions, and they were existing tables in the database. other filters for dicing the measures which is modelled as the fact were also existing tables.

There are some input conditions that are not dimensions at all, meaning there are no tables which exists for them.

One for example, a collection of 4 values (A,B,C,D) , when A is selected the output excel comes in a paticular format, with calculations specific to a paticular region. and so on for B,C,D.etc

1. Now if I model this as a dimension in ssas 2005, I dont have related fact data for this. what should I do?

2. Anyway , I have created a dimension in ssas 2005 that contains this data. I now want to create calculations (either named or calc measures) that dynamically have formula x+y if the browsed value/queried value of the unrelated dimension is A and a different formula if the viewed dimensiion value is B,etc

meaning I want to have a dynamic calculated measure that changes its formula based on the unrelated dimension that is used in the query's row or column

Is this possible? is this the correct way to model this problem?

Regards

Yes, it is possible. The very related example how to do it - is the Time Intelligence Wizard. When you run it, it modifies DSV (you don't even need to create artificial table for A,B,C and D !) to add such factless attribute members, and then in MDX Script it assigns to them formulas. I.e., you MDX Script would look something like

ArtificialAttribute.A = formulaforA;

ArtificialAttribute.B = formulaforY;

...

etc.

|||

On this note, can I ask you a question.
Isn't using time intelligence an expensive operation during querying?

Rgds

Hari

|||

Sorry, but this question isn't posed in a way that it is possible to answer. It's like asking "Isn't running SQL queries expensive ?". And the answer to this question is also unrelated to the original problem.