SQL server questions: regarding

Showing posts with label regarding. Show all posts

Thursday, March 8, 2012

A simple question regarding SQL 2000 Transactional Replication

We have a database that we would like to replicate to another server.
Am i correct to asume that the database that is updated would be the
publisher and that the subscriber database would be the replicated copy?
Greg, in the case of plain transactional replication, yes, this is the case.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

a simple question regarding AES

ttt.tas@.gmail.com schrieb:

Quote:

Originally Posted by

Thanx Volker fo rteh gr8 help and time :D
>
actually, i'll be using MSSQL for my DB.
the problem is as follow, i want to develop some small exe file that
will read the fields of unencrypted DB and encrypt it field by field.
actually i'll get this DB from a client and he doesn't want me to view
the DB content, its already an exisiting one, so i should develop him
some exe file that he will run on this DB and will encrypt all its
fields, and not the DB as a whole, so i can then take this DB and work
on the encrypted fields instead.
>
this is the whole issue :(

Sounds strange. He basically wants to give you a database where /each/
field is encrypted, i.e. a database full of nonsense?
Why can't he just give you the table structure and let you fill
it with your own test data?
SQLServer surely can export a schema definition?

In any case I suggest you ask in comp.databases.ms-sqlserver. This is
more a database problem than an AES problem. SQLserver has an encryption
API and can do the whole thing (if this is what you really want) at SQL
level. So your client can use a small sql script or transact sql file
to do that.

A further advantage of that approach is that, if something goes wrong,
it was definitely the action of the client and not one of your programs.

I've added comp.databases.ms-sqlserver to the group list so my
posting should show up there too.

As for encryption, I fear there is no easy way to encrypt safely
under the conditions you want. Stream ciphers don't extend the
plaintext but are totally unsafe (i.e. you can easily find out
your clients data) if the stream is reused. If the stream is
not reused, all foreign key relations in your database will break.
So, you'd need to manage the reuse on a per-relation base and this
takes about as much effort as just copying the table structure and
writing a small program to generate test data.

Block ciphers either extend the data or are unsafe.

And both generate binary data which messes up your character and number
columns, bot to mention enumerations. They will also break constraints
if there are any and likely betray to you every field that is NULL.

In short: get the schema definition and write a small program that fills
it with test data.

Lots of Greetings!
Volker
--
For email replies, please substitute the obvious.Volker Hetzer wrote:

Quote:

Originally Posted by

ttt.tas@.gmail.com schrieb:

Quote:

Originally Posted by

Thanx Volker fo rteh gr8 help and time :D

actually, i'll be using MSSQL for my DB.
the problem is as follow, i want to develop some small exe file that
will read the fields of unencrypted DB and encrypt it field by field.
actually i'll get this DB from a client and he doesn't want me to view
the DB content, its already an exisiting one, so i should develop him
some exe file that he will run on this DB and will encrypt all its
fields, and not the DB as a whole, so i can then take this DB and work
on the encrypted fields instead.

What work do you need to do with the encrypted database? For most
purposes I'd say that what you have proposed is impractical and
probably impossible. If you encrypt the database in its entirity:

You won't be able to create or enforce constraints
You won't be able to index it effectively
You won't get any sensible performance metrics
You won't be able to create accurate test cases

In short, you won't really have a database to work with. So if you need
to do any development work I suggest you create some representative
test data for yourself instead.

However, SQL Server 2005 does have encryption built in to the engine,
including support for AES.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--|||Volker Hetzer (firstname.lastname@.ieee.org) writes:

Quote:

Originally Posted by

ttt.tas@.gmail.com schrieb:

Quote:

Originally Posted by

>actually, i'll be using MSSQL for my DB.
>the problem is as follow, i want to develop some small exe file that
>will read the fields of unencrypted DB and encrypt it field by field.
>actually i'll get this DB from a client and he doesn't want me to view
>the DB content, its already an exisiting one, so i should develop him
>some exe file that he will run on this DB and will encrypt all its
>fields, and not the DB as a whole, so i can then take this DB and work
>on the encrypted fields instead.

Actually, we had this sort of a problem with one of our customers, and
we developed a very cheesy low-budget solution. To our defense, I should
add that it was the customer's own idea.

In our case, the problem is that the customer cannot let us into the
database for support cases, if their customer data is visible, due the
regulations on financial secrecy in the country where they are active.

What they do when they need us to access the database, is that they
pull a handle (that is, they run a small application), that copies
all sensitive customer information to a database we do not have access
to, and then they replace this data with a string of question marks.
Once they are done, they copy the real data back.

That could serve as inspiration for ttt.tas's problem. Rather than
encrypting the entire database, just overwrite the sensitive information
with nonsense, and save the real database locally at the client.
Provided that there is a need to merge back at all. If there is no
need to merge back, then you can be more frivolous with destroying
the current information.

Note that depending on the purpose of getting a local copy, the
operation may be more or less successful. If the purpose is to
examine performance problems, replacing a lot of data can change
presumption, resulting in problems in reproducing performance
issues.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland Sommarskog schrieb:

Quote:

Originally Posted by

Volker Hetzer (firstname.lastname@.ieee.org) writes:

Quote:

Originally Posted by

>ttt.tas@.gmail.com schrieb:

Quote:

Originally Posted by

>>actually, i'll be using MSSQL for my DB.
>>the problem is as follow, i want to develop some small exe file that
>>will read the fields of unencrypted DB and encrypt it field by field.
>>actually i'll get this DB from a client and he doesn't want me to view
>>the DB content, its already an exisiting one, so i should develop him
>>some exe file that he will run on this DB and will encrypt all its
>>fields, and not the DB as a whole, so i can then take this DB and work
>>on the encrypted fields instead.

>
Actually, we had this sort of a problem with one of our customers, and
we developed a very cheesy low-budget solution. To our defense, I should
add that it was the customer's own idea.

Out of curiosity, what would the high-budget solution have looked like?

Lots of Greetings!
Volker
--
For email replies, please substitute the obvious.|||Volker Hetzer (firstname.lastname@.ieee.org) writes:

Quote:

Originally Posted by

Erland Sommarskog schrieb:

Quote:

Originally Posted by

>Actually, we had this sort of a problem with one of our customers, and
>we developed a very cheesy low-budget solution. To our defense, I should
>add that it was the customer's own idea.

>
Out of curiosity, what would the high-budget solution have looked like?

The initial idea was to use the new encryption facilities in SQL 2005,
but I do not really like that, since it would require the users to work
with multiple passwords. And the customer wanted to go live with a version
of our product that does not support SQL 2005, so encryption was not an
option at that stage anyway.

Instead my suggestion was to have a second database on a second server.
This database would have all the sensitive information. The few functions
that needs to access it would connect to that database with integrated
security (so the users would not need any extra passwords). If the user
is not authorised to that database, the GUI would just display the dummy
data from the main database. It would fall on the business layer to make
that second connection; it would not be in the stored procedures.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland Sommarskog schrieb:

Quote:

Originally Posted by

Volker Hetzer (firstname.lastname@.ieee.org) writes:

Quote:

Originally Posted by

>Erland Sommarskog schrieb:

Quote:

Originally Posted by

>>Actually, we had this sort of a problem with one of our customers, and
>>we developed a very cheesy low-budget solution. To our defense, I should
>>add that it was the customer's own idea.

>Out of curiosity, what would the high-budget solution have looked like?

>
The initial idea was to use the new encryption facilities in SQL 2005,
but I do not really like that, since it would require the users to work
with multiple passwords.

Hm. One could have /one/ password for the set of authorized users,
encrypted for each user separately, with the users normal password.
Then the user (or his program anyway) could look up the encrypted
password for the user and decrypt it with the users password.

Lots of Greetings!
Volker
--
For email replies, please substitute the obvious.|||Volker Hetzer (firstname.lastname@.ieee.org) writes:

Quote:

Originally Posted by

I'm not sure that works with SQL Server encryption, but I would need to
think both twice and thrice to say for sure. Then again,
encryption/descryption could also be done client-side.

Quote:

Originally Posted by

Volker Hetzer (firstname.lastname@.ieee.org) writes:

Quote:

Originally Posted by

>Hm. One could have /one/ password for the set of authorized users,
>encrypted for each user separately, with the users normal password.
>Then the user (or his program anyway) could look up the encrypted
>password for the user and decrypt it with the users password.

>
I'm not sure that works with SQL Server encryption, but I would need to
think both twice and thrice to say for sure. Then again,
encryption/descryption could also be done client-side.

Yes, en-/decryption would be done client-side. The scenario
above just makes sure that someone who hacked himself into
the database without a legitimate password cannot access
the data.
The normal access would go like this:
given a table keys
(
table_name varchar2(32),
column_name varchar2(32),
user_name varchar2(32),
encrypted_key <some binary>
);
Access would be like this:
- select encrypted_key from keys
where
table_name='XXX'
and column_name='YYY'
and user_name='myself';
- client decrypts key with login password
- client has the key to en-/decrypt the columns

Key change presents a problem.

Lots of Greetings!
Volker
--
For email replies, please substitute the obvious.|||Volker Hetzer wrote:
ttt.tas schrieb:

Quote:

Originally Posted by

Quote:

Originally Posted by

I'm not clear on what you're saying.

If you're encrypting the live data to create test data (a one-way
trip of the data), you only need to use a method that takes
printable n-byte field values and hashes them into printable n-byte
values. This is fairly easy to by hashing each n-byte field,
truncating to n bytes, then converting to printable characters.
This assumes that the encrypted data does not need to be
decrypted.

On the other hand, if you want the encrypt the live data, then
process it on your end, then decrypt it back on the user's end,
perhaps your best bet is for the user to create a second database
containing all the live field values in his dB, indexed by unique
random n-byte values (which can be generated in various ways).
He then creates a copy of his live database, substituting the random
values for the live values. You operate on this copy dB, then return
it to him. He then reverses the process, replacing the random field
values with the old live data values. Obviously the replacement
process should only be applied to alphanumeric fields that you
do not intend to modify (e.g., names, addresses, SSNs, credit
card numbers, etc.).

-drt

Saturday, February 25, 2012

A Question Regarding MSDE Desktop Engine

Dear all,

This week I was told to setup MSDE Desktop Engine on my pc bcz my Access back end db is getting bigger in size. And as a little advance step, I decided to put my Access back end on SQL server.

To be frank, I have no experience with SQL server but after reading the instructions, I have installed MSDE (latest version) on my pc successfully. A small icon of SQL server appeared nearby clock icon of my pc showing the SQL server is running on ASHFAQUEPC.

I shifted a Trial db onto this server in following way.

Considering the opened Trial db would be my BE, from the Tools--Database Utility--Upsize wizard, I followed below steps according to Upsize wizard.

1. I selected Local server and check marked to Use Trusted Connection.
2. And then I selected some tables to export
3. Kept all default attributes of db as it is
4. Selected Let the wizard decide under data option to include.
5. At the next step What application change do you want to make?
a. Create a new Access client/Server application.
b. Link SQL Server tables to existing application.
c. No application changes. (I selected this option)

Then it showed me the report of my db description of changes that were made after deploying it. The report I did not print bcz it was a trial db with one table (2 fields) and a simple query.

My idea was to connect my Trial BE with another one of another Access mdb file so FE would be Access and BE would be from MSDE.

1. Now I dont know how to link my BE to my FE?
2. From where I can directly see my BE? Because SQL Server Service Manager does
not have any menu?
3. Where I was wrong in following steps?

Guidance in this regards is highly appreciated.

With kind regards,
AshfaqueI got it.

The tables of db from MSDE2000A I can get connected thru database project file.

Thanks.
Ashfaque

A question for the experts regarding Rank sql2000

I am trying to rank a table but cannot get the desired
result.
Rank Name Points
1 Bud 82
2 Bill 37
2 Fred 37
3 Sally 26
4 Tim 23
I am getting the above results from this query
SELECT COUNT(DISTINCT U2.Points) AS Rank, U1.Alias, U1.Points
FROM Users U1 INNER JOIN
Users U2 ON U2.Points >= U1.Points
GROUP BY U1.Alias, U1.Points
ORDER BY Rank
I would like to achieve the following result. Is this possible?
Rank Name Points
1 Bud 82
2 Bill 37
2 Fred 37
4 Sally 26
5 Tim 23
Your help would be apreciated.Please provide DDL and sample data...
http://www.aspfaq.com/etiquette.asp?id=5006
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Ross" <andrew.ross@.racingworld.com> wrote in message
news:1124229285.283143.263590@.g43g2000cwa.googlegroups.com...
> I am trying to rank a table but cannot get the desired
> result.
> Rank Name Points
> 1 Bud 82
> 2 Bill 37
> 2 Fred 37
> 3 Sally 26
> 4 Tim 23
> I am getting the above results from this query
> SELECT COUNT(DISTINCT U2.Points) AS Rank, U1.Alias, U1.Points
> FROM Users U1 INNER JOIN
> Users U2 ON U2.Points >= U1.Points
> GROUP BY U1.Alias, U1.Points
> ORDER BY Rank
> I would like to achieve the following result. Is this possible?
> Rank Name Points
> 1 Bud 82
> 2 Bill 37
> 2 Fred 37
> 4 Sally 26
> 5 Tim 23
> Your help would be apreciated.
>|||Do:
SELECT t1.alias,
SUM( CASE WHEN t2.points >= t1.points
THEN 1 ELSE 0 END) -
SUM( CASE WHEN t2.points = t1.points
THEN 1 ELSE 0 END) + 1 AS "rank"
FROM Users t1, Users t2
GROUP BY t1.alias
ORDER BY "rank" ;
The idea is to identify the number of ties within the dataset. For an easy
alternative, consider using the following query with subqueries:
SELECT t1.*,
( SELECT COUNT(t2.points) FROM Users t2
WHERE t2.points >= t1.points) AS "rank",
( SELECT COUNT(t2.points) FROM Users t2
WHERE t2.points = t1.points ) AS "tied counts"
FROM Users t1 ;
You can manipulate this query making it a derived table construct.
Anith|||Try
SELECT COUNT(U2.Points) + 1 AS Rank, U1.Alias, U1.Points
FROM Users U1 INNER JOIN
Users U2 ON U2.Points > U1.Points
GROUP BY U1.Alias, U1.Points
ORDER BY Rank
Regards,
Willson
http://www.wsantoso.net
"Ross" wrote:

> I am trying to rank a table but cannot get the desired
> result.
> Rank Name Points
> 1 Bud 82
> 2 Bill 37
> 2 Fred 37
> 3 Sally 26
> 4 Tim 23
> I am getting the above results from this query
> SELECT COUNT(DISTINCT U2.Points) AS Rank, U1.Alias, U1.Points
> FROM Users U1 INNER JOIN
> Users U2 ON U2.Points >= U1.Points
> GROUP BY U1.Alias, U1.Points
> ORDER BY Rank
> I would like to achieve the following result. Is this possible?
> Rank Name Points
> 1 Bud 82
> 2 Bill 37
> 2 Fred 37
> 4 Sally 26
> 5 Tim 23
> Your help would be apreciated.
>|||Hi Willson
This works great except that it drops the first record (1 Bud
82)
2 Bill 37
2 Fred 37
4 Sally 26
5 Tim 23|||Oh yeah that, change the inner join to left outer JOIN as it's comparing
against those that has bigger point to get the rank. Seriously this is
something that is a lot more efficient to run on the client side rather than
SQL as noted by many other people in your previous thread.
"Ross" wrote:

> Hi Willson
> This works great except that it drops the first record (1 Bud
> 82)
> 2 Bill 37
> 2 Fred 37
> 4 Sally 26
> 5 Tim 23
>|||Thanks that works fine|||create table t (Name varchar(10), Points int)
go
insert into t
select 'Bud', 82 union all
select 'Bill', 37 union all
select 'Fred', 37 union all
select 'Sally', 26 union all
select 'Tim', 23
go
select a.Name,
a.Points,
(select count(distinct x.points) from t x where x.points >= a.points) [Rank]
from t a
order by rank
Rakesh
"Ross" wrote:

> I am trying to rank a table but cannot get the desired
> result.
> Rank Name Points
> 1 Bud 82
> 2 Bill 37
> 2 Fred 37
> 3 Sally 26
> 4 Tim 23
> I am getting the above results from this query
> SELECT COUNT(DISTINCT U2.Points) AS Rank, U1.Alias, U1.Points
> FROM Users U1 INNER JOIN
> Users U2 ON U2.Points >= U1.Points
> GROUP BY U1.Alias, U1.Points
> ORDER BY Rank
> I would like to achieve the following result. Is this possible?
> Rank Name Points
> 1 Bud 82
> 2 Bill 37
> 2 Fred 37
> 4 Sally 26
> 5 Tim 23
> Your help would be apreciated.
>

Friday, February 24, 2012

A question about Create Schema in SQL Server

Hi there,
I've a question regarding Schema in SQLServer. If i compare with Oracle, in
Oracle there is an option to create multiple "Tablespaces" in ONE single dat
abase instance. When a database user is created, it needs to be assigned a s
pecific tablespace. Now whe
n user actually logs in, a new "Schema" is created with the same name as Use
rname in the tablespace assigned and all the operations are performed in thi
s user specific schema.
The advantage is here that in ONE database instance, multiple schemas can be
created each of them having same set of tables, views etc. And a specifc us
er per schema is having access to all objects created in Schema.
I wonder does MSSQL server 2000 has something like this? I see a "Create Sch
ema" option but the documentation did not help me.
I would appreciate if one can help with this scenario. The objective is that
in ONE single database instance SHOULD HAVE multile SCHEMAS with identical
objects (tables, views etc).
Thanks in advanceThe schema concept does not exist, per se, in 2000. But you will be able to
do something like this in SQL Server 2005.
http://www.aspfaq.com/
(Reverse address to reply.)
"HV" <HV@.discussions.microsoft.com> wrote in message
news:3962090B-2595-43A0-A148-D57CA6799F51@.microsoft.com...
> Hi there,
> I've a question regarding Schema in SQLServer. If i compare with Oracle,
in Oracle there is an option to create multiple "Tablespaces" in ONE single
database instance. When a database user is created, it needs to be assigned
a specific tablespace. Now when user actually logs in, a new "Schema" is
created with the same name as Username in the tablespace assigned and all
the operations are performed in this user specific schema.
> The advantage is here that in ONE database instance, multiple schemas can
be created each of them having same set of tables, views etc. And a specifc
user per schema is having access to all objects created in Schema.
> I wonder does MSSQL server 2000 has something like this? I see a "Create
Schema" option but the documentation did not help me.
> I would appreciate if one can help with this scenario. The objective is
that in ONE single database instance SHOULD HAVE multile SCHEMAS with
identical objects (tables, views etc).
> Thanks in advance
>|||> The advantage is here that in ONE database instance, multiple schemas can
be created each of them having same set of tables, views etc. And a specifc
user per schema is having access to all objects created in Schema.
As Aaron mentioned, SQL 2005 will extent this functionality. However, you
can have multiple schema in a single database and/or single instance in all
versions of SQL Server. The script below illustrates how one can do this in
a single database. If you need to isolate default physical object placement
(akin to default tablespace), you can use separate databases within the same
instance. Unlike Oracle, you can have multiple databases per SQL Server
instance.
USE MyDatabase
EXEC sp_addlogin 'Schema1'
EXEC sp_addlogin 'Schema2'
EXEC sp_adduser 'Schema1'
EXEC sp_adduser 'Schema2'
CREATE TABLE Schema1.Table1(Col1 int)
CREATE TABLE Schema2.Table1(Col1 int)
Hope this helps.
Dan Guzman
SQL Server MVP
"HV" <HV@.discussions.microsoft.com> wrote in message
news:3962090B-2595-43A0-A148-D57CA6799F51@.microsoft.com...
> Hi there,
> I've a question regarding Schema in SQLServer. If i compare with Oracle,
in Oracle there is an option to create multiple "Tablespaces" in ONE single
database instance. When a database user is created, it needs to be assigned
a specific tablespace. Now when user actually logs in, a new "Schema" is
created with the same name as Username in the tablespace assigned and all
the operations are performed in this user specific schema.
> The advantage is here that in ONE database instance, multiple schemas can
be created each of them having same set of tables, views etc. And a specifc
user per schema is having access to all objects created in Schema.
> I wonder does MSSQL server 2000 has something like this? I see a "Create
Schema" option but the documentation did not help me.
> I would appreciate if one can help with this scenario. The objective is
that in ONE single database instance SHOULD HAVE multile SCHEMAS with
identical objects (tables, views etc).
> Thanks in advance
>

A question about Create Schema in SQL Server

Hi there,
I've a question regarding Schema in SQLServer. If i compare with Oracle, in Oracle there is an option to create multiple "Tablespaces" in ONE single database instance. When a database user is created, it needs to be assigned a specific tablespace. Now whe
n user actually logs in, a new "Schema" is created with the same name as Username in the tablespace assigned and all the operations are performed in this user specific schema.
The advantage is here that in ONE database instance, multiple schemas can be created each of them having same set of tables, views etc. And a specifc user per schema is having access to all objects created in Schema.
I wonder does MSSQL server 2000 has something like this? I see a "Create Schema" option but the documentation did not help me.
I would appreciate if one can help with this scenario. The objective is that in ONE single database instance SHOULD HAVE multile SCHEMAS with identical objects (tables, views etc).
Thanks in advance
The schema concept does not exist, per se, in 2000. But you will be able to
do something like this in SQL Server 2005.
http://www.aspfaq.com/
(Reverse address to reply.)
"HV" <HV@.discussions.microsoft.com> wrote in message
news:3962090B-2595-43A0-A148-D57CA6799F51@.microsoft.com...
> Hi there,
> I've a question regarding Schema in SQLServer. If i compare with Oracle,
in Oracle there is an option to create multiple "Tablespaces" in ONE single
database instance. When a database user is created, it needs to be assigned
a specific tablespace. Now when user actually logs in, a new "Schema" is
created with the same name as Username in the tablespace assigned and all
the operations are performed in this user specific schema.
> The advantage is here that in ONE database instance, multiple schemas can
be created each of them having same set of tables, views etc. And a specifc
user per schema is having access to all objects created in Schema.
> I wonder does MSSQL server 2000 has something like this? I see a "Create
Schema" option but the documentation did not help me.
> I would appreciate if one can help with this scenario. The objective is
that in ONE single database instance SHOULD HAVE multile SCHEMAS with
identical objects (tables, views etc).
> Thanks in advance
>
|||> The advantage is here that in ONE database instance, multiple schemas can
be created each of them having same set of tables, views etc. And a specifc
user per schema is having access to all objects created in Schema.
As Aaron mentioned, SQL 2005 will extent this functionality. However, you
can have multiple schema in a single database and/or single instance in all
versions of SQL Server. The script below illustrates how one can do this in
a single database. If you need to isolate default physical object placement
(akin to default tablespace), you can use separate databases within the same
instance. Unlike Oracle, you can have multiple databases per SQL Server
instance.
USE MyDatabase
EXEC sp_addlogin 'Schema1'
EXEC sp_addlogin 'Schema2'
EXEC sp_adduser 'Schema1'
EXEC sp_adduser 'Schema2'
CREATE TABLE Schema1.Table1(Col1 int)
CREATE TABLE Schema2.Table1(Col1 int)
Hope this helps.
Dan Guzman
SQL Server MVP
"HV" <HV@.discussions.microsoft.com> wrote in message
news:3962090B-2595-43A0-A148-D57CA6799F51@.microsoft.com...
> Hi there,
> I've a question regarding Schema in SQLServer. If i compare with Oracle,
in Oracle there is an option to create multiple "Tablespaces" in ONE single
database instance. When a database user is created, it needs to be assigned
a specific tablespace. Now when user actually logs in, a new "Schema" is
created with the same name as Username in the tablespace assigned and all
the operations are performed in this user specific schema.
> The advantage is here that in ONE database instance, multiple schemas can
be created each of them having same set of tables, views etc. And a specifc
user per schema is having access to all objects created in Schema.
> I wonder does MSSQL server 2000 has something like this? I see a "Create
Schema" option but the documentation did not help me.
> I would appreciate if one can help with this scenario. The objective is
that in ONE single database instance SHOULD HAVE multile SCHEMAS with
identical objects (tables, views etc).
> Thanks in advance
>

A question about Create Schema in SQL Server

Hi there,
I've a question regarding Schema in SQLServer. If i compare with Oracle, in Oracle there is an option to create multiple "Tablespaces" in ONE single database instance. When a database user is created, it needs to be assigned a specific tablespace. Now when user actually logs in, a new "Schema" is created with the same name as Username in the tablespace assigned and all the operations are performed in this user specific schema.
The advantage is here that in ONE database instance, multiple schemas can be created each of them having same set of tables, views etc. And a specifc user per schema is having access to all objects created in Schema.
I wonder does MSSQL server 2000 has something like this? I see a "Create Schema" option but the documentation did not help me.
I would appreciate if one can help with this scenario. The objective is that in ONE single database instance SHOULD HAVE multile SCHEMAS with identical objects (tables, views etc).
Thanks in advanceThe schema concept does not exist, per se, in 2000. But you will be able to
do something like this in SQL Server 2005.
--
http://www.aspfaq.com/
(Reverse address to reply.)
"HV" <HV@.discussions.microsoft.com> wrote in message
news:3962090B-2595-43A0-A148-D57CA6799F51@.microsoft.com...
> Hi there,
> I've a question regarding Schema in SQLServer. If i compare with Oracle,
in Oracle there is an option to create multiple "Tablespaces" in ONE single
database instance. When a database user is created, it needs to be assigned
a specific tablespace. Now when user actually logs in, a new "Schema" is
created with the same name as Username in the tablespace assigned and all
the operations are performed in this user specific schema.
> The advantage is here that in ONE database instance, multiple schemas can
be created each of them having same set of tables, views etc. And a specifc
user per schema is having access to all objects created in Schema.
> I wonder does MSSQL server 2000 has something like this? I see a "Create
Schema" option but the documentation did not help me.
> I would appreciate if one can help with this scenario. The objective is
that in ONE single database instance SHOULD HAVE multile SCHEMAS with
identical objects (tables, views etc).
> Thanks in advance
>|||> The advantage is here that in ONE database instance, multiple schemas can
be created each of them having same set of tables, views etc. And a specifc
user per schema is having access to all objects created in Schema.
As Aaron mentioned, SQL 2005 will extent this functionality. However, you
can have multiple schema in a single database and/or single instance in all
versions of SQL Server. The script below illustrates how one can do this in
a single database. If you need to isolate default physical object placement
(akin to default tablespace), you can use separate databases within the same
instance. Unlike Oracle, you can have multiple databases per SQL Server
instance.
USE MyDatabase
EXEC sp_addlogin 'Schema1'
EXEC sp_addlogin 'Schema2'
EXEC sp_adduser 'Schema1'
EXEC sp_adduser 'Schema2'
CREATE TABLE Schema1.Table1(Col1 int)
CREATE TABLE Schema2.Table1(Col1 int)
--
Hope this helps.
Dan Guzman
SQL Server MVP
"HV" <HV@.discussions.microsoft.com> wrote in message
news:3962090B-2595-43A0-A148-D57CA6799F51@.microsoft.com...
> Hi there,
> I've a question regarding Schema in SQLServer. If i compare with Oracle,
in Oracle there is an option to create multiple "Tablespaces" in ONE single
database instance. When a database user is created, it needs to be assigned
a specific tablespace. Now when user actually logs in, a new "Schema" is
created with the same name as Username in the tablespace assigned and all
the operations are performed in this user specific schema.
> The advantage is here that in ONE database instance, multiple schemas can
be created each of them having same set of tables, views etc. And a specifc
user per schema is having access to all objects created in Schema.
> I wonder does MSSQL server 2000 has something like this? I see a "Create
Schema" option but the documentation did not help me.
> I would appreciate if one can help with this scenario. The objective is
that in ONE single database instance SHOULD HAVE multile SCHEMAS with
identical objects (tables, views etc).
> Thanks in advance
>

Sunday, February 19, 2012

A problem on count when calculating business days

Here is the information regarding problem.

1) I have three fields in the detail section. They are calculated and displayed on the report from crystal side. The fields I am displaying are the release numbers which are invoiced with in 3 business days, invoiced more than 3 business days and Not invoiced more than 3 business days. The report is grouped by market field. When I am inserting summary for each field by market it is also counting the blank fields and displaying the count. The data that I was pulling from database is correct. I am unable to figure it out why it is counting the blank fields while calculating summary.

2) And the other problem is, it is displaying the blank spaces in the report when the condition fails.

For reference here I am inserting report . Any Help could be greatly appeaciated. Its a little bit urgent.Hi all

I figured it out my self.

Monday, February 13, 2012

a little trouble understanding about primary keys

Hi,

I have a couple of questions regarding primary keys, and whether I really need one or not.

Right now, I am using a GridView control to display all the data in my Access database, but am using a SqlDataSource control to do it. Everything works fine, and I am also using the GridView to Edit/Delete records, and I am using a DetailsView control to insert new records into the database.

The questions I have are these:

1) What I have right now in the database is a value called ID, which is just an autonumber, which has the order of the database, but I would like to change it so that the database sorts by the date awarded, which is a field in the database called "mdate", and make it so that when an admin enters a new date, it sorts automatically by date. Because of that, I am not really sure if I need to have the ID value at all.

I dont understand if it will be of any use, if I want all the values to show up by date, starting from 2006 back. If anyone can explain, or tell me if I am thinking correctly?

2) Also, right now in the database, the clients who started the database inserted the values in the "mdate" field as "Awarded mm/dd/year" instead of just "mm/dd/year".

How could I write a function to go through each record in the "mdate" column, delete the word "Awarded " and then convert it into a datetime object, so I could sort it by date? Is it possible, or would I have to do it manually?

Here is the code I have now:

<%@.PageLanguage="C#"AutoEventWireup="true"CodeFile="Default.aspx.cs"Inherits="_Default" %>

<!DOCTYPEhtmlPUBLIC"-//W3C//DTD XHTML 1.0 Transitional//EN""http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<htmlxmlns="http://www.w3.org/1999/xhtml">

<headrunat="server">

<title>Untitled Page</title>

</head>

<body>

<formid="form1"runat="server">

<div>

<asp:DetailsViewID="DetailsView1"runat="server"AllowPaging="True"DataSourceID="myDataSource1"

Height="50px"Width="300px"OnPageIndexChanging="DetailsView1_PageIndexChanging"Font-Names="Arial"Font-Size="Smaller">

<asp:CommandFieldShowDeleteButton="True"ShowEditButton="True"ShowInsertButton="True"/>

</Fields>

</asp:DetailsView>

<br/>

</div>

<asp:GridViewID="GridView1"runat="server"AutoGenerateColumns="false"Font-Names="Verdana"AutoGenerateEditButton="True"AutoGenerateDeleteButton="true"DataSourceID="myDataSource1"DataKeyNames="ID">

<asp:BoundFieldHeaderText="ID"DataField="ID"ReadOnly="true"Visible="false"/>

<asp:BoundFieldHeaderText="Name"DataField="name"/>

<asp:BoundFieldHeaderText="Department Retired From"DataField="dept"/>

<asp:BoundFieldHeaderText="Current State Of Residence"DataField="state"/>

<asp:BoundFieldHeaderText="Purpose Of Award"DataField="award"/>

<asp:BoundFieldHeaderText="Date Awarded"DataField="mdate"/>

</Columns>

<RowStyleFont-Size="Smaller"Height="50px"HorizontalAlign="Center"/>

</asp:GridView>

<asp:SqlDataSourceID="myDataSource1"runat="server"SelectCommand="SELECT * from [finawards]"ConnectionString="Provider=Microsoft.Jet.OLEDB.4.0; Data Source=|DataDirectory|finawards_new.mdb"ProviderName="System.Data.OleDb"UpdateCommand="UPDATE [finawards] SET [name] = @.name, [dept] = @.dept, [state] = @.state, [award] = @.award, [mdate] = @.mdate WHERE [ID] = @.ID"DeleteCommand="DELETE FROM finawards WHERE [ID] = @.ID"InsertCommand="INSERT INTO finawards (name, dept, state, award, mdate) VALUES (@.name, @.dept, @.state, @.award, @.mdate)"></asp:SqlDataSource>

</form>

</body>

</html>

Thanks,

sls29 wrote:
1) What I have right now in the database is a value called ID, which is just an autonumber, which has the order of the database, but I would like to change it so that the database sorts by the date awarded, which is a field in the database called "mdate", and make it so that when an admin enters a new date, it sorts automatically by date. Because of that, I am not really sure if I need to have the ID value at all.

Modify your SELECT statement to add an ORDER BY with ASC or DESC clause at the end.

SelectCommand="SELECT * from [finawards] ORDER BY mdate"

Yes. It is a good idea to have a Primary Key. You may not be using it now but every table must have a PK.

sls29 wrote:
How could I write a function to go through each record in the "mdate" column, delete the word "Awarded " and then convert it into a datetime object, so I could sort it by date? Is it possible, or would I have to do it manually?

You could use a REPLACE function to remove all the "Awarded" values. something like this:

SELECT REPLACE(Field1, "Awarded", "") FROM Table1;

After you do that you can manually change the datatype of the column

|||

Close, you want:

UPDATE Table1 SET Field1=REPLACE(Field1,"Awarded ","")

that'll get rid of the pesky text.

Yes, every table should have a primary key. If you want to pull back by mdate, fine, put an index on it too. But without a primary key column like the id field, what if two awards are given out to the same person/people/thing on the same day? How would/could you distinguish between the records if all the columns are exactly the same? That's what the ID field is doing for you. In addition, if you have records that refer back to the award (by ID), then if at a later time you decide to change the mdate or some other field in the table, then your other tables will still be able to find the corresponding record because the ID never changes (for that record).

|||

Thank you for the help.

So, basically, I can sort the GridView display anyway I want, but will still need the ID value there, just for unique identification purposes? I do not have to have the ID effect the ordering of how the GridView will display...

About how to get rid of the "Awarded" text. So, basically, I should be able to run a command:

UPDATE finawards SET mdate=REPLACE(mdate, "Awarded ", "");

Right in Access, and then change the column type to a date/time object?

Thanks again, I really appreciate it. I am trying to learn and get practice with ASP.NET 2.0, and it is nice to have a place to ask questions, and get some advice!

|||

1). Personally,I try my best not to use date as primary key as it can be duplicated. If the reason just want to get the data sorted in the database, I believe you can always sort the data in your query. So, I'd use the ID for PK.

2). Try this

// this will set the mdate to the 10 characters of the mdate from the right, I assume the last 10 characters in the mdate field is all date format.

Update [Table_Name] Set mdate = Right(mdate,10);

run the query, and change the column type to datetime manually in the design view.

good luck

|||

Thanks again for all the help. I changed all the values of my "mdate" field to take away the "Awarded " test, and then converted that field to be of type "datetime" and changed the format string to "Short Date" so it would only show the date in the form mm/dd/year.

But, for some reason, when it displays the mdate field in the GridView it shows up as "mm/dd/year 12:00am".

Is there a reason it is showing a time as well, even though there is no time in the database? Is there something I have to add to my code so that it only shows up in short date form?

Right now, I have the same code except for theORDER BY mdate addition to my SelectCommand.

I was not sure where to add the formatting restrictions. When I try to insert data also, it also gives me a type mismatch error. I am assuming that is because I put in only a date, and not a time?

Thanks in advance for any advice.

|||

I was able to get only the date to display in the GridView by adding this to my boundfield for the mdate field.

DataFormatString = "{0:mm/dd/yyyy}" HtmlEncode="False"3

The problem I am having now is that when I try to insert a new record, I get a "Data type mismatch criteria" error.

I was reading that the problem is because even if it is in the right format, Access will not allow a DateTime object to be inserted as a string. How can I convert the value that is entered into the "mdate" textbox created by the DetailsView control to a ShortDate object so that I can insert it into the database?

Thanks again.

|||

I tried a couple of new things to fix my problem, but with no luck.

First, I tried adding the InsertParameters collection to my SqlDataSource and set the Type of my "mdate" as DateTime, but that did not work.

Then, I tried to modify the ItemInserting function of the DetailsView so that it checks for which value is the "mdate" function, and converts it to a DateTime object, but I am still getting the same error.

protected void DetailsView1_ItemInserting(object sender, DetailsViewInsertEventArgs e)
{
for (int i = 0; i < e.Values.Count; i++)
{
if (e.Values[i].ToString().Contains("/"))
{
Convert.ToDateTime(e.Values[i].ToString());
}
else
{
e.Values[i] = Server.HtmlEncode(e.Values[i].ToString());
}
}
}

I know the function is a little primitive, but I just figured that if the current value contains a "/", it would have to be the mdate field, since there is no other field that would have that in it.

Is there anything fundamentally wrong? I am totally confused...

|||

Try playing around in the SqlDataSource_Inserting event. There is a lot more control there with what actually gets sent to the database.

Just curious, shouldn't you be using the AccessDataSource control with Access?

|||

Thanks for the tip, I will look at that event also. The reason I am using the SqlDataSource control is because in the quickstarts it had mentioned that you can use the SqlDataSource control with an Access database also, and it would give you the "added functionality" that comes with the SqlDataSource control.

Maybe I can try to change it, and see if that works better.

|||

Well, I tried to change my SqlDataSource_Inserting function to the following:

Convert.ToDateTime(myDataSource1.InsertParameters["mdate"].ToString().Trim());

That did not work, but atleast now I get a different error Smile [:)]

Now, when I try to insert a new record, I get the error:

"The string was not recognized as a valid DateTime. There is an unknown word at index 0".

Is the above code that I wrote enough for the Inserting function? I thought since the only problem I was having was with mdate, it would convert that value to a DateTime object, and then perform the insert command, but now I am getting this new error... Confused [*-)]

I tried adding the Trim() to my Convert statement thinking that was why I was getting my error, but I am still getting it.

Any ideas? Thanks again...

|||

I finally got this resolved! I ended up getting rid of the DetailsView control altogether, and just included a regular form to insert into the database. Then I used an OleDbCommand and a ExecuteNonQuery method to insert the values.

I was able to insert the date properly also using DateTime.Parse(), and everything is working great now.

Thanks to everyone for their help.

Thursday, February 9, 2012

A few questions on Table and Index Partitioning in SQL2K5.

Howdy all. I've got a couple questions regarding Partition Function (PF) and
Partition Scheme (PS) and I was wondering if someone could assist?
1. How do I assign a table to a Partition Scheme? I created a test PF and
PS, but how could they really partition my table if the table doesnt know to
use them? I know I can assign the table to a filegroup that has a PS on it,
but didn't know if that alone would cut it?
2. Is there some place I can verify all my settings once I think I have them
right?
3. Would it ever make sense to partition an index without partitioning the
table it resides on?
TIA, ChrisRI got number 1, but could still use help with 2 and 3. Here is number 1.
CREATE PARTITION FUNCTION myRangePF2 (int)
AS RANGE LEFT FOR VALUES (1, 100, 1000);
GO
CREATE PARTITION SCHEME myRangePS2
AS PARTITION myRangePF2
TO ( fgtest1, fgtest1, fgtest1, fgtest2 );
CREATE TABLE [dbo].[PartitionTest](
[Table1ID] [int] IDENTITY(1,1) NOT NULL,
[TransactionNumber] [int] NOT NULL)
ON myRangePS2 (TransactionNumber)
"ChrisR" <noFudgingWay@.NoEmail.com> wrote in message
news:e7gBSYEBHHA.4212@.TK2MSFTNGP02.phx.gbl...
> Howdy all. I've got a couple questions regarding Partition Function (PF)
and
> Partition Scheme (PS) and I was wondering if someone could assist?
> 1. How do I assign a table to a Partition Scheme? I created a test PF and
> PS, but how could they really partition my table if the table doesnt know
to
> use them? I know I can assign the table to a filegroup that has a PS on
it,
> but didn't know if that alone would cut it?
> 2. Is there some place I can verify all my settings once I think I have
them
> right?
> 3. Would it ever make sense to partition an index without partitioning the
> table it resides on?
> TIA, ChrisR
>|||> 2. Is there some place I can verify all my settings once I think I have
> them right?
You can test the partition function using the $PARTITION and sample values:
SELECT $PARTITION.myRangePF2(0);
SELECT $PARTITION.myRangePF2(1);
SELECT $PARTITION.myRangePF2(2);
> 3. Would it ever make sense to partition an index without partitioning the
> table it resides on?
Off the top of my head, I guess you might do this if you had a heap (table
with no clustered index) and partitioned only for index manageability. In
most cases you'll have a clustered index and align as well.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"ChrisR" <noFudgingWay@.NoEmail.com> wrote in message
news:eqSA72EBHHA.4864@.TK2MSFTNGP04.phx.gbl...
>I got number 1, but could still use help with 2 and 3. Here is number 1.
> CREATE PARTITION FUNCTION myRangePF2 (int)
> AS RANGE LEFT FOR VALUES (1, 100, 1000);
> GO
>
> CREATE PARTITION SCHEME myRangePS2
> AS PARTITION myRangePF2
> TO ( fgtest1, fgtest1, fgtest1, fgtest2 );
>
> CREATE TABLE [dbo].[PartitionTest](
> [Table1ID] [int] IDENTITY(1,1) NOT NULL,
> [TransactionNumber] [int] NOT NULL)
> ON myRangePS2 (TransactionNumber)
>
>
>
> "ChrisR" <noFudgingWay@.NoEmail.com> wrote in message
> news:e7gBSYEBHHA.4212@.TK2MSFTNGP02.phx.gbl...
>> Howdy all. I've got a couple questions regarding Partition Function (PF)
> and
>> Partition Scheme (PS) and I was wondering if someone could assist?
>> 1. How do I assign a table to a Partition Scheme? I created a test PF and
>> PS, but how could they really partition my table if the table doesnt know
> to
>> use them? I know I can assign the table to a filegroup that has a PS on
> it,
>> but didn't know if that alone would cut it?
>> 2. Is there some place I can verify all my settings once I think I have
> them
>> right?
>> 3. Would it ever make sense to partition an index without partitioning
>> the
>> table it resides on?
>> TIA, ChrisR
>>
>|||Thanks Dan. To piggyback on your idea, I also just stumbled on the
sys.partition_* functions that are proving to be useful.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:56080A38-716B-4144-8694-616FA7B84903@.microsoft.com...
> > 2. Is there some place I can verify all my settings once I think I have
> > them right?
> You can test the partition function using the $PARTITION and sample
values:
> SELECT $PARTITION.myRangePF2(0);
> SELECT $PARTITION.myRangePF2(1);
> SELECT $PARTITION.myRangePF2(2);
> > 3. Would it ever make sense to partition an index without partitioning
the
> > table it resides on?
> Off the top of my head, I guess you might do this if you had a heap (table
> with no clustered index) and partitioned only for index manageability. In
> most cases you'll have a clustered index and align as well.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "ChrisR" <noFudgingWay@.NoEmail.com> wrote in message
> news:eqSA72EBHHA.4864@.TK2MSFTNGP04.phx.gbl...
> >I got number 1, but could still use help with 2 and 3. Here is number 1.
> >
> > CREATE PARTITION FUNCTION myRangePF2 (int)
> >
> > AS RANGE LEFT FOR VALUES (1, 100, 1000);
> >
> > GO
> >
> >
> >
> > CREATE PARTITION SCHEME myRangePS2
> >
> > AS PARTITION myRangePF2
> >
> > TO ( fgtest1, fgtest1, fgtest1, fgtest2 );
> >
> >
> >
> > CREATE TABLE [dbo].[PartitionTest](
> >
> > [Table1ID] [int] IDENTITY(1,1) NOT NULL,
> >
> > [TransactionNumber] [int] NOT NULL)
> >
> > ON myRangePS2 (TransactionNumber)
> >
> >
> >
> >
> >
> >
> >
> > "ChrisR" <noFudgingWay@.NoEmail.com> wrote in message
> > news:e7gBSYEBHHA.4212@.TK2MSFTNGP02.phx.gbl...
> >> Howdy all. I've got a couple questions regarding Partition Function
(PF)
> > and
> >> Partition Scheme (PS) and I was wondering if someone could assist?
> >>
> >> 1. How do I assign a table to a Partition Scheme? I created a test PF
and
> >> PS, but how could they really partition my table if the table doesnt
know
> > to
> >> use them? I know I can assign the table to a filegroup that has a PS on
> > it,
> >> but didn't know if that alone would cut it?
> >>
> >> 2. Is there some place I can verify all my settings once I think I have
> > them
> >> right?
> >>
> >> 3. Would it ever make sense to partition an index without partitioning
> >> the
> >> table it resides on?
> >>
> >> TIA, ChrisR
> >>
> >>
> >
> >
>

Thursday, March 8, 2012

A simple question regarding SQL 2000 Transactional Replication

a simple question regarding AES

Saturday, February 25, 2012

A Question Regarding MSDE Desktop Engine

A question for the experts regarding Rank sql2000

Friday, February 24, 2012

A question about Create Schema in SQL Server

A question about Create Schema in SQL Server

A question about Create Schema in SQL Server

Sunday, February 19, 2012

A problem on count when calculating business days

Monday, February 13, 2012

a little trouble understanding about primary keys

Thursday, February 9, 2012

A few questions on Table and Index Partitioning in SQL2K5.

A few questions on Table and Index Partitioning in SQL2K5.

A few questions on Table and Index Partitioning in SQL2K5.

SQL server questions

Blog Archive

About Me