Showing posts with label sp3. Show all posts
Showing posts with label sp3. Show all posts

Thursday, March 22, 2012

Easier way to change collation on columns.

Hi everybody
Running a SQL Server 2000 std edition + SP3.
Restored a couple of databases from a SQL Server 7 to 2000.
I need to change the collation of these databases.
So I used alter database to change the default collation.
Now I need to know if there is a way to change all the
columns in the existing tables to the new default
collation other than having to change them by hand.
Is there a script or some wizard that can help me or am I
stuck with this monster assignment to change every string
datatype to the new default collation?
Thanks in advance
RisunRisun,
You have to script it and do it manually. There are some scripts on the
internet, I believe that will do this for you. One easy way is to create
a new database with the new collation and then copy the data over using
a script or dts.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Risun wrote:
> Hi everybody
> Running a SQL Server 2000 std edition + SP3.
> Restored a couple of databases from a SQL Server 7 to 2000.
> I need to change the collation of these databases.
> So I used alter database to change the default collation.
> Now I need to know if there is a way to change all the
> columns in the existing tables to the new default
> collation other than having to change them by hand.
> Is there a script or some wizard that can help me or am I
> stuck with this monster assignment to change every string
> datatype to the new default collation?
> Thanks in advance
> Risun

Easier way to change collation on columns.

Hi everybody
Running a SQL Server 2000 std edition + SP3.
Restored a couple of databases from a SQL Server 7 to 2000.
I need to change the collation of these databases.
So I used alter database to change the default collation.
Now I need to know if there is a way to change all the
columns in the existing tables to the new default
collation other than having to change them by hand.
Is there a script or some wizard that can help me or am I
stuck with this monster assignment to change every string
datatype to the new default collation?
Thanks in advance
Risun
Risun,
You have to script it and do it manually. There are some scripts on the
internet, I believe that will do this for you. One easy way is to create
a new database with the new collation and then copy the data over using
a script or dts.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Risun wrote:

> Hi everybody
> Running a SQL Server 2000 std edition + SP3.
> Restored a couple of databases from a SQL Server 7 to 2000.
> I need to change the collation of these databases.
> So I used alter database to change the default collation.
> Now I need to know if there is a way to change all the
> columns in the existing tables to the new default
> collation other than having to change them by hand.
> Is there a script or some wizard that can help me or am I
> stuck with this monster assignment to change every string
> datatype to the new default collation?
> Thanks in advance
> Risun
sql

Easier way to change collation on columns.

Hi everybody
Running a SQL Server 2000 std edition + SP3.
Restored a couple of databases from a SQL Server 7 to 2000.
I need to change the collation of these databases.
So I used alter database to change the default collation.
Now I need to know if there is a way to change all the
columns in the existing tables to the new default
collation other than having to change them by hand.
Is there a script or some wizard that can help me or am I
stuck with this monster assignment to change every string
datatype to the new default collation?
Thanks in advance
RisunRisun,
You have to script it and do it manually. There are some scripts on the
internet, I believe that will do this for you. One easy way is to create
a new database with the new collation and then copy the data over using
a script or dts.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Risun wrote:

> Hi everybody
> Running a SQL Server 2000 std edition + SP3.
> Restored a couple of databases from a SQL Server 7 to 2000.
> I need to change the collation of these databases.
> So I used alter database to change the default collation.
> Now I need to know if there is a way to change all the
> columns in the existing tables to the new default
> collation other than having to change them by hand.
> Is there a script or some wizard that can help me or am I
> stuck with this monster assignment to change every string
> datatype to the new default collation?
> Thanks in advance
> Risun

Wednesday, February 15, 2012

Dynamic SQL in Stored Proc

Environment:
Window2K workstation, SQL Server 2000 Vesion 8.00.760 (SP3)
Setup:
I have a database setup so that NO users (except dbo) have READ,
UPDATE, or DELETE access to my database. But I have a single role
called MySPUser that is granted EXECUTE access to all of my stored
procs that do all data access for the system. The MySPUser role has a
single user in that group called MyUser, which is a windows domain
level account. My Webserver then impersonates that user when it calls
the stored procs. This setup worked on both my development machine and
my development test machine.
Problem:
So everything was going great for about a year when my dev machine
crashed. When I rebuilt the box with the same software (os and sql
included) everything seemed to be working just fine. The impersonated
user can still call all the stored proc and either retrieve or update
data. The only problem is that I have 2 stored proc that require
Dynamic SQL and they have stopped working. I now receive the following
error message when executing one of the stored procs.
SELECT permission denied on object 'tblMyTable', database
'MyApplication-Dev', owner 'dbo'.
I have tried deleting the users from the database and server and fully
rebuilding the users and roles with no luck. If I change my connection
string to point to my Test machine, which was built a year ago and also
uses Win2K and SQL2K SP3 everything seems to work fine.
Question:
What could cause Dynamic SQL Stored Procs to execute under a different
security context than Non-Dynamic SQL Stored Procs?
Any help would be greatly appreciated.
Will
P.S. I need to user dynamic sql because the sql statement is a query
for data by the user that can be searched on 12 different fields
simultaniously. Therefore the number of combinations of statements I
would need to build would be huge.It sounds like the user was granted access to the underlying tables.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Will" <WillCWirtz@.Yahoo.com> wrote in message
news:1129649346.104637.10300@.g43g2000cwa.googlegroups.com...
Environment:
Window2K workstation, SQL Server 2000 Vesion 8.00.760 (SP3)
Setup:
I have a database setup so that NO users (except dbo) have READ,
UPDATE, or DELETE access to my database. But I have a single role
called MySPUser that is granted EXECUTE access to all of my stored
procs that do all data access for the system. The MySPUser role has a
single user in that group called MyUser, which is a windows domain
level account. My Webserver then impersonates that user when it calls
the stored procs. This setup worked on both my development machine and
my development test machine.
Problem:
So everything was going great for about a year when my dev machine
crashed. When I rebuilt the box with the same software (os and sql
included) everything seemed to be working just fine. The impersonated
user can still call all the stored proc and either retrieve or update
data. The only problem is that I have 2 stored proc that require
Dynamic SQL and they have stopped working. I now receive the following
error message when executing one of the stored procs.
SELECT permission denied on object 'tblMyTable', database
'MyApplication-Dev', owner 'dbo'.
I have tried deleting the users from the database and server and fully
rebuilding the users and roles with no luck. If I change my connection
string to point to my Test machine, which was built a year ago and also
uses Win2K and SQL2K SP3 everything seems to work fine.
Question:
What could cause Dynamic SQL Stored Procs to execute under a different
security context than Non-Dynamic SQL Stored Procs?
Any help would be greatly appreciated.
Will
P.S. I need to user dynamic sql because the sql statement is a query
for data by the user that can be searched on 12 different fields
simultaniously. Therefore the number of combinations of statements I
would need to build would be huge.