Creating a row guid in SQL Express

I am an asp developer who is finally serious about learning .net. I have downloaded SQL Express and am working through the ADO Step by Step book. I can easily create a table and set all the columns. What I cannot do is create a row guid. The option for this is grayed out in the column properties window and when I try to set a field in the "Row Guid Column" in the right side properties window I get an error mesage stating the field must match an entry in the list.

I have set the id field to primary key and no nulls and to identity and I still cannot set as row guid. I even downloaded Northwind and these tables also do not have a rowguid and I cannot set.

I noticed this as I tried to create a data adapter. I was able to create the Select and Insert statements but the update and delete failed I think due to the rowguid issue. Any help is appreciated Thanks Brad

Hi Brad...in order to set the rowguid column attribute, the column must be of the datatype 'uniqueidentifier'...once you have a column of that datatype, you can then assign the column the rowguid attribute. Note that you can only have a single rowguid column in a single table, though you can have multiple columns of the datatype uniqueidentifier.

HTH

|||Thanks Chad I have been working with MySql the last 3 years and was not used to the uniqueidentifier column. I now know and have fixed my db.

Creating a normalized database

Hi. I have a project I need to complete and I really don't know the first step I should take. Basically, we have a case management system that is normalized for the most part except for one major flaw: the clients table. Whenever we add a new case, we have to add the customer's name/address/phone etc. all over again. I would like to redo this current setup so we have one table just for clients and another table just for cases, so we don't have all this double entering all the time. Is there an easy way to do this or could someone point me in the right direction? It's on a SQL Server 2000 database. Thanks for your help!4 easy steps

CREATE the client table (ClientID, ClientName, Address...)

SELECT DISTINCT Client info from the case table into the new Client Table

Build Relationship between the 2 tables (on ClientID)

DELETE the redundant columns from case table|||You can use a ClientCase table with a many to many relationship between your Clients and your Cases tables.


Clients ClientCase Case
------ ------ ------
CliNumber --> CliNumber
CaseNumber <-- CaseNumber
Cliname CaseLeadAtty
CliAddress CaseSecondAtty


etc.|||tomh53,

Just curious...
What would be the need for the intermediate table, unless
one case number can have multiple clients?|||... unless one case number can have multiple clients?if a case only and forever belongs to only one client, then yeah, you don't need the many-to-many relationship table

however, note that you can implement a one-to-many relationship using a many-to-many relationship table -- just make sure (in your app logic) that you never store more than one client per case!!

then, when the day comes, and the case rolls in which requires two clients, you're all set!!

:) :)|||tomh53,

Just curious...
What would be the need for the intermediate table, unless
one case number can have multiple clients?

How about a class-action lawsuit?|||4 easy steps

CREATE the client table (ClientID, ClientName, Address...)

SELECT DISTINCT Client info from the case table into the new Client Table

Build Relationship between the 2 tables (on ClientID)

DELETE the redundant columns from case table
Thank you! That worked like a charm. I still have a few duplicates but it beats going through all of them manually. :)

Creating a Connection to Database that Does Not Yet Exist

Hello All!
I am in the process of developing a DTS package. My
package will behave as follows:
1. Backup my live database.
2. Using the backup from Step 1., the backup will be
restored using a DIFFERENT name (NewDatabase).
3. Manipulate NewDatabase database.
How can I create a connection in my DTS Package when, at
the time I am creating the package, the NewDatabase
database does not exist? Do I have to use Disconnected
Edits? If so, how? Or, do I use Microsoft Data Link?
If so, how? Or, do I use something else. (As you can
tell from my questions, I am not an expert at this :-)
Any suggestions are greatly appreciated!!You should be able to connect to the master database to do what you need to
do initially.
Ray Higdon MCSE, MCDBA, CCNA
--
"ERR" <anonymous@.discussions.microsoft.com> wrote in message
news:ef7b01c3f1b0$3f8eff20$a301280a@.phx.gbl...
> Hello All!
> I am in the process of developing a DTS package. My
> package will behave as follows:
> 1. Backup my live database.
> 2. Using the backup from Step 1., the backup will be
> restored using a DIFFERENT name (NewDatabase).
> 3. Manipulate NewDatabase database.
> How can I create a connection in my DTS Package when, at
> the time I am creating the package, the NewDatabase
> database does not exist? Do I have to use Disconnected
> Edits? If so, how? Or, do I use Microsoft Data Link?
> If so, how? Or, do I use something else. (As you can
> tell from my questions, I am not an expert at this :-)
> Any suggestions are greatly appreciated!!|||I forgot to mention that I do have an initial connection,
the one to the original database. However, I need my DTS
package to create a connection to the NewDatabase
database once the task of restoring with a new name is
complete.
Thanks in advance!

>--Original Message--
>You should be able to connect to the master database to
do what you need to
>do initially.
>--
>Ray Higdon MCSE, MCDBA, CCNA
>--
>"ERR" <anonymous@.discussions.microsoft.com> wrote in
message
>news:ef7b01c3f1b0$3f8eff20$a301280a@.phx.gbl...
at
>
>.
>|||To do what? If it's only to run a sql script you can use workflow and
connect to the master database and then do the "use yournewdb" command after
you have created it
Ray Higdon MCSE, MCDBA, CCNA
--
"ERR" <anonymous@.discussions.microsoft.com> wrote in message
news:f43101c3f22e$9ff978d0$a301280a@.phx.gbl...
> I forgot to mention that I do have an initial connection,
> the one to the original database. However, I need my DTS
> package to create a connection to the NewDatabase
> database once the task of restoring with a new name is
> complete.
> Thanks in advance!
>
> do what you need to
> message
> at|||what you need to do as per Ray's suggestion is to create the dts package and
set it to connect to master, then create a dynamic task properties and a
global variable <dbname> to change the value of catalog property in
Disconnect Edit DE at run time.
then when you execute the dts package need to execute with the /A switch to
pass the <db> variable which u proide at run time see example below
dtsrun /S <servername> /E /N<packagename> /A <db>:8=newdatabase
this way u can connect to a database at runtime.
--
Olu Adedeji
"Ray Higdon" <sqlhigdon@.nospam.yahoo.com> wrote in message
news:eR5zJ8y8DHA.3880@.tk2msftngp13.phx.gbl...
> To do what? If it's only to run a sql script you can use workflow and
> connect to the master database and then do the "use yournewdb" command
after
> you have created it
> --
> Ray Higdon MCSE, MCDBA, CCNA
> --
> "ERR" <anonymous@.discussions.microsoft.com> wrote in message
> news:f43101c3f22e$9ff978d0$a301280a@.phx.gbl...
>

Create variables with same format in one single step?

I would need to create multiple variables with the same format for
future update, what I did is listing them separately, is there an easy
way to combine them in one step? please see query below. Thanks a lot!

--Current Query--
SELECT cast(0.0 as money) as balance_1
,cast(0.0 as money) as balance_2
,cast(0.0 as money) as balance_3
,cast(0.0 as money) as balance_4
,cast(0.0 as money) as balance_5
,cast(0.0 as money) as balance_6
,cast(0.0 as money) as balance_7
,cast(0.0 as money) as balance_8
,account_no
,XXX
INTO table1
FROM account

Can I do something like this? This one didn't work.

SELECT balance_1 to balance_8 (cast 0.0 as money)
,account_no
,XXX
INTO table1
FROM accountIt's not a single step but this might work for you:

This might work for you:

declare @.balance_1 money,
@.balance_2 money,
@.balance_3 money,
@.balance_4 money,
@.balance_5 money,
@.balance_6 money,
@.balance_7 money,
@.balance_8 money

set @.balance_1 = 0
set @.balance_2 = 0
set @.balance_3 = 0
set @.balance_4 = 0
set @.balance_5 = 0
set @.balance_6 = 0
set @.balance_7 = 0
set @.balance_8 = 0

SELECT @.balance_1,
@.balance_2,
@.balance_3,
@.balance_4,
@.balance_5,
@.balance_6,
@.balance_7,
@.balance_8,
account_no,
XXX
INTO table1
FROM account|||I'm confused. There are no variables in the code you posted - you are
creating a table with eight columns. There is only one step in a query
- the whole query operates as one logical unit.

What you seem to be looking for is a syntax shortcut that saves you
typing out the column names. The best shortcut is probably to click and
drag a list of names from the Object Browser in Query Analyzer then
search and replace the rest. I expect you could do that in much less
time than it took to write out this question. :-)

Your query itself looks a little strange. Are you sure you can't
calculate the balances with a single query rather than SELECT followed
by an UPDATE / INSERT?

--
David Portas
SQL Server MVP
--|||(rong.guo@.gmail.com) writes:
> I would need to create multiple variables with the same format for
> future update, what I did is listing them separately, is there an easy
> way to combine them in one step? please see query below. Thanks a lot!
> --Current Query--
> SELECT cast(0.0 as money) as balance_1
> ,cast(0.0 as money) as balance_2
> ,cast(0.0 as money) as balance_3
> ,cast(0.0 as money) as balance_4
> ,cast(0.0 as money) as balance_5
> ,cast(0.0 as money) as balance_6
> ,cast(0.0 as money) as balance_7
> ,cast(0.0 as money) as balance_8
> ,account_no
> ,XXX
> INTO table1
> FROM account
> Can I do something like this? This one didn't work.
> SELECT balance_1 to balance_8 (cast 0.0 as money)
> ,account_no
> ,XXX
> INTO table1
> FROM account

You could build an string with the SQL statement by iterating from
1 to 8, and use EXEC() to execute that statement. However, this is
definnitely not recommendable.

I have no idea what you are up to, but columns are usually distinct
entities. If you find that you need balance_1 to balance_8, maybe
you should make them rows instead. That is how you work with array
data in SQL, rows with pairs (index, value).

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||To answer the question I think you are trying to ask: No, there are not
arrays in SQL.

You create a table with a CREATE TABLE statement and you have to know
what it looks like before you type in the command.

You need to put the column names in the SELECT list. Using the SELECT
* option is a really bad idea for production code.

You need to give every column a meaningful name from your data model.
But fromthe look of this, you do not have a data model and are writing
code on the fly.|||Thank you ALL so much!!

I do have a bunch of other variables in the table (about 20), I only
listed account_no as an example of the variables.

Doing the calculation in a single step instead of using update is a
great idea, but for some reason, it created duplicates. I haven't found
out if it is due to the database duplicates or because of my query. I
will do further research... Thanks again...

--CELKO-- wrote:
> To answer the question I think you are trying to ask: No, there are
not
> arrays in SQL.
> You create a table with a CREATE TABLE statement and you have to know
> what it looks like before you type in the command.
> You need to put the column names in the SELECT list. Using the
SELECT
> * option is a really bad idea for production code.
> You need to give every column a meaningful name from your data model.
> But fromthe look of this, you do not have a data model and are
writing
> code on the fly.