how to create primary keys on compound fields?

Hi everyone,
I am now getting seriously into VB.Net development as a front end to SQL
server. After working about 20 yrs with DBF's and various xBase
programs (dBase, FoxPro, Clipper, etc), I am now convinced VB/SQL is the
way to go
I have a few questions:
I've successfully imported DBF files into SQL server (MSDE) but I can't
seem to create a data adapter (in VB) unless I have a primary key
defined. Do tables require primary keys be defined prior to dragging
them onto a forms and working with datagrids?
How can I create a primary key on a compound field? For a simple key
(one field), I know to open the server explorer, go into design mode,
then right click the field and set primary key. This works well ... but
... what about a 'details' table where the only unique key is a
combination of many fields (might be mixed type too), ex: customer
(char) + item (numeric) + color (char). How does one do that?
Thanks in advance
Richard Fagen
--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
In the table design high-light each column name you want to make PK and
click the PK icon (a gold key symbol) in the toolbar. You high-light
each column by holding down the control key and clicking the square to
the left of the column name.
You could also use a DDL statement to create a PK:
ALTER TABLE table_name ADD CONSTRAINT PK_table_name PRIMARY KEY (col1,
col2, col3)
The coln are the column names that will comprise the PK.
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/AwUBQbTkE4echKqOuFEgEQI7uACg3KvX6c2lJy7M9loRVsXQbs 732YwAnRKD
6CSubCv+n/TA2EUvULyWke2E
=qfj3
--END PGP SIGNATURE--
Richard Fagen wrote:
> Hi everyone,
> I am now getting seriously into VB.Net development as a front end to SQL
> server. After working about 20 yrs with DBF's and various xBase
> programs (dBase, FoxPro, Clipper, etc), I am now convinced VB/SQL is the
> way to go
> I have a few questions:
> I've successfully imported DBF files into SQL server (MSDE) but I can't
> seem to create a data adapter (in VB) unless I have a primary key
> defined. Do tables require primary keys be defined prior to dragging
> them onto a forms and working with datagrids?
> How can I create a primary key on a compound field? For a simple key
> (one field), I know to open the server explorer, go into design mode,
> then right click the field and set primary key. This works well ... but
> ... what about a 'details' table where the only unique key is a
> combination of many fields (might be mixed type too), ex: customer
> (char) + item (numeric) + color (char). How does one do that?
|||Hi,
Thanks for your reply. I never noticed the gold key icon, I was using
the right click popup menu While it appears to work, when I try to
save the changes to the file, I get the same error message...
'PRODUCT' table
- Unable to modify table.
ADO error: Cannot insert the value NULL into column 'PREF', table
'sterilite.dbo.Tmp_PRODUCT'; column does not allow nulls. INSERT fails.
The statement has been terminated.
I imported the DBF files via SQL servers' tools. I thought everything
was ok as in the server explorer, I see the database with all the tables
(foxpro files) inside it. I can also browse the data. I did notice the
'allow nulls' column was checked. I tried unchecking it but I get the
same error.
The foxpro file originally had a 'pref' C4 (for the 'prefix' code, a
character field, width 4) and I don't know what to try next.
In DBF syntax, the unique fields are:
pref, C4 (prefix of item)
item, C4 (item code)
color, C2 (color code)
cu_code, C4 (customer code)
All these fields get translated into 'varchar' type with the appropriate
widths and the allow nulls checked.
Any ideas?
Thanks
Richard
MGFoster wrote:
> --BEGIN PGP SIGNED MESSAGE--
> Hash: SHA1
> In the table design high-light each column name you want to make PK and
> click the PK icon (a gold key symbol) in the toolbar. You high-light
> each column by holding down the control key and clicking the square to
> the left of the column name.
> You could also use a DDL statement to create a PK:
> ALTER TABLE table_name ADD CONSTRAINT PK_table_name PRIMARY KEY (col1,
> col2, col3)
> The coln are the column names that will comprise the PK.
>
|||--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
In RDBMS (SQL dbs) each row's Primary Key columns require data, i.e.,
they cannot be NULL. The error you're getting means that the PREF
column has NULL values in some, or all, of the imported rows. This
means the columns you've selected for PKs are not a good choice, or you
need to fix the data so there is a value in the PREF column for each
row.
It would seem that the columns item, color, cu_code, would be good
candidates for PK if they always have values per row, and, the 3 column
values, taken together, represent a unique value in the table.
Is the PREF column useful as part of a unique identifier (PK)? If so,
then it should have data.
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/AwUBQbUGaoechKqOuFEgEQK43gCg4pund4SFw+VpPdFrYlcLoG iwV5wAnjzV
aFvBbWaz/Xm1Y1XKxbpzG7wQ
=rrhn
--END PGP SIGNATURE--
Richard Fagen wrote:[vbcol=seagreen]
> Hi,
> Thanks for your reply. I never noticed the gold key icon, I was using
> the right click popup menu While it appears to work, when I try to
> save the changes to the file, I get the same error message...
> 'PRODUCT' table
> - Unable to modify table.
> ADO error: Cannot insert the value NULL into column 'PREF', table
> 'sterilite.dbo.Tmp_PRODUCT'; column does not allow nulls. INSERT fails.
> The statement has been terminated.
> I imported the DBF files via SQL servers' tools. I thought everything
> was ok as in the server explorer, I see the database with all the tables
> (foxpro files) inside it. I can also browse the data. I did notice the
> 'allow nulls' column was checked. I tried unchecking it but I get the
> same error.
> The foxpro file originally had a 'pref' C4 (for the 'prefix' code, a
> character field, width 4) and I don't know what to try next.
> In DBF syntax, the unique fields are:
> pref, C4 (prefix of item)
> item, C4 (item code)
> color, C2 (color code)
> cu_code, C4 (customer code)
> All these fields get translated into 'varchar' type with the appropriate
> widths and the allow nulls checked.
> Any ideas?
> Thanks
> Richard
>
>
> MGFoster wrote:
|||That must be it! Yes, some values of Pref are null. Strange, in the
DBF format it was a blank character field, but when it was imported in
SQL, it became <null>
While I might be able to get around this by excluding 'pref' from the
key, I'll have that problem for other fields as some items don't have
colours and they also appear with <null> in SQL format.
They would still be unique as if an item only comes in one colour, the
DBF file had a blank. Can I globally change all <null> to " " (blank
characters) to fix this.
Thanks for figuring out what the problem was!
Richard
MGFoster wrote:
> --BEGIN PGP SIGNED MESSAGE--
> Hash: SHA1
> In RDBMS (SQL dbs) each row's Primary Key columns require data, i.e.,
> they cannot be NULL. The error you're getting means that the PREF
> column has NULL values in some, or all, of the imported rows. This
> means the columns you've selected for PKs are not a good choice, or you
> need to fix the data so there is a value in the PREF column for each
> row.
> It would seem that the columns item, color, cu_code, would be good
> candidates for PK if they always have values per row, and, the 3 column
> values, taken together, represent a unique value in the table.
> Is the PREF column useful as part of a unique identifier (PK)? If so,
> then it should have data.
>
|||--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
You can change all values of one column, but that will not fix your
problem. For the PK, what you need to do is pick one, or more, columns
whose values uniquely identify the row. Each of these columns MUST have
data for every row. There cannot be rows where these columns have equal
values. E.g. (the 3 columns that make up the PK):
item colour cu_code
1 2 9988
1 3 9988
1 2 9988 <- PK constraint violation 'cuz equals 1st
row.
It would seem that this table would have a Date column. You could
include the date column in the PK & have something like this:
item colour cu_code order_date
1 2 9988 20040115
1 3 9988 20040115
1 2 9988 20040120 <- now OK 'cuz order_date makes
row different from 1st row.
If the columns you pick as the PK have rows w/o data then you will have
to do, what is known as, "Data Clean Up." Which should be done by your
client since they are the ones who created this mess to begin with.
I suggest you get a good book on database design to help you figure out
construction of primary keys, foreign keys, indexes, etc. I've
recommended _Database Design for Mere Mortals_ by Hernandez.
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/AwUBQbVhPIechKqOuFEgEQLJGQCgjejMWSmlyiNAELht8FvPb2 BluRsAoORW
6ofwJ8Gby1KYNurkiHCb1NM+
=Ode3
--END PGP SIGNATURE--
Richard Fagen wrote:[vbcol=seagreen]
> That must be it! Yes, some values of Pref are null. Strange, in the
> DBF format it was a blank character field, but when it was imported in
> SQL, it became <null>
> While I might be able to get around this by excluding 'pref' from the
> key, I'll have that problem for other fields as some items don't have
> colours and they also appear with <null> in SQL format.
> They would still be unique as if an item only comes in one colour, the
> DBF file had a blank. Can I globally change all <null> to " " (blank
> characters) to fix this.
> Thanks for figuring out what the problem was!
> Richard
> MGFoster wrote:
|||Hi,
Thanks for your prompt reply. I will definitely check out that book. I
have collected quite a library over the years and I'm always reading
many tech books at once
I'm aware of the requirement of a unique value for each record, it is
just some records, when imported from DBF (Foxpro/DOS) have null values.
In DBF format, spaces were stored. I'm not sure why importing the
files into SQL tables cause them to change to <nulls> Maybe I didn't
check off some option during the import process?
Ex:
ItemColourCu_code... more fields here
11Walmart
12Walmart
13Walmart
2<null>Walmart <- problem here, it was blank in the DBF,
some items have a single record that is blank,
it will still be unique, just one " " value
for that cu_code/item combination
31Walmart
32Walmart
Your idea to incorporate another field is a great idea. You are right,
I will have the same problem elsewhere unless I add an orderdate (or
invoice #) to the key. I don't know how to get rid of the <nulls> for
the blank DBFs that were imported into SQL/
I was thinking of replacing the blanks in the DBF with a dummy symbol
(say *) before importing into SQL format. Then I'd use OSQL to replace
the dummy with " ". Is there an easier way?
Thanks again.
Richard
MGFoster wrote:
> --BEGIN PGP SIGNED MESSAGE--
> Hash: SHA1
> You can change all values of one column, but that will not fix your
> problem. For the PK, what you need to do is pick one, or more, columns
> whose values uniquely identify the row. Each of these columns MUST have
> data for every row. There cannot be rows where these columns have equal
> values. E.g. (the 3 columns that make up the PK):
> item colour cu_code
> 1 2 9988
> 1 3 9988
> 1 2 9988 <- PK constraint violation 'cuz equals 1st
> row.
> It would seem that this table would have a Date column. You could
> include the date column in the PK & have something like this:
> item colour cu_code order_date
> 1 2 9988 20040115
> 1 3 9988 20040115
> 1 2 9988 20040120 <- now OK 'cuz order_date makes
> row different from 1st row.
> If the columns you pick as the PK have rows w/o data then you will have
> to do, what is known as, "Data Clean Up." Which should be done by your
> client since they are the ones who created this mess to begin with.
> I suggest you get a good book on database design to help you figure out
> construction of primary keys, foreign keys, indexes, etc. I've
> recommended _Database Design for Mere Mortals_ by Hernandez.
>
|||--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
It would seem that replacing the "*" with a space would be redundant,
since you're just inserting a "holding character" until you get valid
data, and that holding character can be an asterisk as easily as a
space.
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/AwUBQbYol4echKqOuFEgEQIcRACg0oDX4sr3G7MWGLBjVSDoTU 2tUjgAn3UM
Cg//imB7G3nJKMWBWBZRffvf
=Is8Z
--END PGP SIGNATURE--
Richard Fagen wrote:[vbcol=seagreen]
> Hi,
> Thanks for your prompt reply. I will definitely check out that book. I
> have collected quite a library over the years and I'm always reading
> many tech books at once
> I'm aware of the requirement of a unique value for each record, it is
> just some records, when imported from DBF (Foxpro/DOS) have null values.
> In DBF format, spaces were stored. I'm not sure why importing the
> files into SQL tables cause them to change to <nulls> Maybe I didn't
> check off some option during the import process?
> Ex:
> Item Colour Cu_code ... more fields here
> 1 1 Walmart
> 1 2 Walmart
> 1 3 Walmart
> 2 <null> Walmart <- problem here, it was blank in the DBF,
> some items have a single record that is blank,
> it will still be unique, just one " " value
> for that cu_code/item combination
> 3 1 Walmart
> 3 2 Walmart
> Your idea to incorporate another field is a great idea. You are right,
> I will have the same problem elsewhere unless I add an orderdate (or
> invoice #) to the key. I don't know how to get rid of the <nulls> for
> the blank DBFs that were imported into SQL/
> I was thinking of replacing the blanks in the DBF with a dummy symbol
> (say *) before importing into SQL format. Then I'd use OSQL to replace
> the dummy with " ". Is there an easier way?
> Thanks again.
> Richard
> MGFoster wrote:
|||Can't a blank value (" ") be valid if it is part of a unique compound
key?
There must be some OSQL statement that I can use.
In anycase, is there a simple way to change all the <null> values to
blank spaces? I know I can use the server explorer to browse and edit
values, but certain tables have 1000's of <null> values in certain columns.
Thanks
Richard
MGFoster wrote:

> --BEGIN PGP SIGNED MESSAGE--
> Hash: SHA1
> It would seem that replacing the "*" with a space would be redundant,
> since you're just inserting a "holding character" until you get valid
> data, and that holding character can be an asterisk as easily as a
> space.
>
|||--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
You can change NULLs to another character(s) using an update like this:
UPDATE table_name
SET column_name = '*'
WHERE column_name IS NULL
I'd use an actual character, 'cuz some query results thru other
applications (MS Access) will seem to be a space (blank), but, in fact,
will be a NULL.
What I was trying to get at is this may not enable a PK to be
established on the columns you want to make a PK, because, putting a
character in the column may not make a unique PK. E.g.:
BEFORE:
item colour cu_code
NULL 1 9998
2 NULL 9998
NULL 1 9998
AFTER:
item colour cu_code
* 1 9998
2 * 9998
* 1 9998 <- violates PK constraint 'cuz equals row 1.
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/AwUBQbY6KoechKqOuFEgEQKgvACfeROn2fzUzR88HCv9HxPmvY Pl3YcAoMM6
lDykLHrwNeYAAUzf6vFHbpmb
=r+De
--END PGP SIGNATURE--
Richard Fagen wrote:[vbcol=seagreen]
> Can't a blank value (" ") be valid if it is part of a unique compound
> key?
> There must be some OSQL statement that I can use.
> In anycase, is there a simple way to change all the <null> values to
> blank spaces? I know I can use the server explorer to browse and edit
> values, but certain tables have 1000's of <null> values in certain columns.
> Thanks
> Richard
>
> MGFoster wrote:
sql

How to create ODBC to SQLServer in a LAN accessed by another LAN through gateway ?

Hi all!
I have two LAN connected with a single PC with two network cards. IP
addresses are 10.0.0.x for the first LAN and 192.168.0.x for the
second. I have a SQLServer at 192.168.0.109 and I would like to define
an ODBC that point to it from the other network, for example on a PC
with IP 10.0.0.164. TCP/IP ping works fine from 10.0.0.164 to
192.168.0.109, but ODBC doesn't work!
Anyone suggest some ideas or point me in the right direction ?
Thanks in advance
Eugenio ALESSIEugenio,
Do you have firewall between this networks?
try from 10.0.0.164: telnet 192.168.0.109 1433.
--
Regards,
Rodrigo Fernandes
"eugenio alessi" wrote:
> Hi all!
> I have two LAN connected with a single PC with two network cards. IP
> addresses are 10.0.0.x for the first LAN and 192.168.0.x for the
> second. I have a SQLServer at 192.168.0.109 and I would like to define
> an ODBC that point to it from the other network, for example on a PC
> with IP 10.0.0.164. TCP/IP ping works fine from 10.0.0.164 to
> 192.168.0.109, but ODBC doesn't work!
> Anyone suggest some ideas or point me in the right direction ?
> Thanks in advance
> Eugenio ALESSI
>|||No, I haven't any firewall...
Is there some user access policy to connect to SQLServer (I use sa
nopassword...)
Eugenio

how to create filegroups

I was directed to create filegroups so that clients that install msde or sql
server would look the same Can anyone point me in the right direction on how
to
create the filegroups on msde.
I was thinking about just splitting up the indexes and tables at this
point. Is this what a sql server install would do so that they could take
advantage of Raid drive?
See Filegroups|Creating in Books Online. The basic syntax is:
ALTER DATABASE <dbname> ADD FILEGROUP <filegroupname>
Then add files to the filegroup with:
ALTER DATABASE <dbname> ADD FILE (NAME = <filename>) TO FILEGROUP
<filegroupname>
Jacco Schalkwijk
SQL Server MVP
"Keeper" <Keeper@.discussions.microsoft.com> wrote in message
news:A271C5F7-636B-4E88-B2EE-D9DFED0FB3DF@.microsoft.com...
>I was directed to create filegroups so that clients that install msde or
>sql
> server would look the same Can anyone point me in the right direction on
> how
> to
> create the filegroups on msde.
> I was thinking about just splitting up the indexes and tables at this
> point. Is this what a sql server install would do so that they could take
> advantage of Raid drive?
>
|||i appreciate the information
thanx
"Jacco Schalkwijk" wrote:

> See Filegroups|Creating in Books Online. The basic syntax is:
> ALTER DATABASE <dbname> ADD FILEGROUP <filegroupname>
> Then add files to the filegroup with:
> ALTER DATABASE <dbname> ADD FILE (NAME = <filename>) TO FILEGROUP
> <filegroupname>
>
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Keeper" <Keeper@.discussions.microsoft.com> wrote in message
> news:A271C5F7-636B-4E88-B2EE-D9DFED0FB3DF@.microsoft.com...
>
>

How to create database from script file?

Hi
I use SQLServer 2000 with SP3
I have script file that has been created using "generate SQL script"
command from SQL server Enterprise manager.
But I can't find how to create database using this script file. Is there
any way to create database from script file?
I appreciate your help!Copy the script to the clipboard during the "generate sql script" command.
Start the query analyzer and past the script
Run the script.
(You could also save the script and open it in the query analyzer)
Nico De Greef
Belgium
Freelance Software Architect
MCP, MCSD, .NET certified
"JK" <invalid@.address> wrote in message
news:e0JzX2Q$DHA.808@.TK2MSFTNGP12.phx.gbl...
> Hi
> I use SQLServer 2000 with SP3
> I have script file that has been created using "generate SQL script"
> command from SQL server Enterprise manager.
> But I can't find how to create database using this script file. Is there
> any way to create database from script file?
>
> I appreciate your help!
>|||In the Options tab of generate script, you have the option to "Script
Database". This will include the CREATE DATABASE statement.
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"JK" <invalid@.address> wrote in message
news:e0JzX2Q$DHA.808@.TK2MSFTNGP12.phx.gbl...
> Hi
> I use SQLServer 2000 with SP3
> I have script file that has been created using "generate SQL script"
> command from SQL server Enterprise manager.
> But I can't find how to create database using this script file. Is there
> any way to create database from script file?
>
> I appreciate your help!
>sql

How to create database from script file?

Hi
I use SQLServer 2000 with SP3
I have script file that has been created using "generate SQL script"
command from SQL server Enterprise manager.
But I can't find how to create database using this script file. Is there
any way to create database from script file?
I appreciate your help!check out www.dbghost.com
>--Original Message--
>Hi
>I use SQLServer 2000 with SP3
>I have script file that has been created using "generate
SQL script"
>command from SQL server Enterprise manager.
>But I can't find how to create database using this
script file. Is there
>any way to create database from script file?
>
>I appreciate your help!
>
>.
>|||Copy the script to the clipboard during the "generate sql script" command.
Start the query analyzer and past the script
Run the script.
(You could also save the script and open it in the query analyzer)
--
Nico De Greef
Belgium
Freelance Software Architect
MCP, MCSD, .NET certified
"JK" <invalid@.address> wrote in message
news:e0JzX2Q$DHA.808@.TK2MSFTNGP12.phx.gbl...
> Hi
> I use SQLServer 2000 with SP3
> I have script file that has been created using "generate SQL script"
> command from SQL server Enterprise manager.
> But I can't find how to create database using this script file. Is there
> any way to create database from script file?
>
> I appreciate your help!
>|||In the Options tab of generate script, you have the option to "Script
Database". This will include the CREATE DATABASE statement.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"JK" <invalid@.address> wrote in message
news:e0JzX2Q$DHA.808@.TK2MSFTNGP12.phx.gbl...
> Hi
> I use SQLServer 2000 with SP3
> I have script file that has been created using "generate SQL script"
> command from SQL server Enterprise manager.
> But I can't find how to create database using this script file. Is there
> any way to create database from script file?
>
> I appreciate your help!
>

how to create array column and how to retrive in sqlserver

hi

i am using database sqlserver,
i am searching for varray concept like in oracle to store multiple values in a single column(as array column) like that shell i do in sql server

1.how to create array column in a table using sqlserver

if possible how can i use select query for that

there is no array concept in sql server. you can however store the values as a concatenated string with a delimiter and use some custom function to parse through the string to split them up.|||

Try the link below for samples using the string functions in SQL Server and Oracle PL/SQL is closer to C++ than T-SQL. I would also look at Ken Henderson books at my local bookstore run a search for String functions in the BOL(books online). Hope this helps.

http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm

|||Why do you want to store multiple values in a single column? Thishas some pretty huge implications from a relational modelingstandpoint, not to mention the fact that it's going to kill performanceif you ever need to query the thing. If you can post someinformation about what business problem you're trying to solve, I'mcertian we can help you find a better solution.

|||

Sorry I forgot I have a link with SQL Server Arrays. Try the link below. Hope this helps.

http://www.sommarskog.se/arrays-in-sql.html

How to create a storeprocedure

Hi,
I'm very very new in SQLServer.
I need to create a store procedure that execute a sql file located in a PATH
for example:
C:\Inetpub\Reserved\update.sql
HOw can I made this?
Thanks a lot!Hi
what version of sql server you using.
if you have sql server installed on your computer, then you can open .sql
file in query analyser or management studio to execute it
i still don't understand why you a need a SP to execute update.sql file
Regards
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"Andrea" <noreply@.nospam.net> wrote in message
news:5Fkni.2101$BM.412@.tornado.fastwebnet.it...
> Hi,
> I'm very very new in SQLServer.
> I need to create a store procedure that execute a sql file located in a
> PATH
> for example:
>
> C:\Inetpub\Reserved\update.sql
>
>
> HOw can I made this?
>
> Thanks a lot!
>
>|||Andrea
Sorry, I type it from the memory, please test it
EXEC master..xp_cmdshell 'osql.exe -S SVR -U sa -P pswd -i
"c:\Inetpub\Reserved\update.sql" -o "C:\output.txt"'
"Andrea" <noreply@.nospam.net> wrote in message
news:5Fkni.2101$BM.412@.tornado.fastwebnet.it...
> Hi,
> I'm very very new in SQLServer.
> I need to create a store procedure that execute a sql file located in a
> PATH
> for example:
>
> C:\Inetpub\Reserved\update.sql
>
>
> HOw can I made this?
>
> Thanks a lot!
>
>sql

How to create a storeprocedure

Hi,
I'm very very new in SQLServer.
I need to create a store procedure that execute a sql file located in a PATH
for example:
C:\Inetpub\Reserved\update.sql
HOw can I made this?
Thanks a lot!
Andrea
Sorry, I type it from the memory, please test it
EXEC master..xp_cmdshell 'osql.exe -S SVR -U sa -P pswd -i
"c:\Inetpub\Reserved\update.sql" -o "C:\output.txt"'
"Andrea" <noreply@.nospam.net> wrote in message
news:5Fkni.2101$BM.412@.tornado.fastwebnet.it...
> Hi,
> I'm very very new in SQLServer.
> I need to create a store procedure that execute a sql file located in a
> PATH
> for example:
>
> C:\Inetpub\Reserved\update.sql
>
>
> HOw can I made this?
>
> Thanks a lot!
>
>

How to create a storeprocedure

Hi,
I'm very very new in SQLServer.
I need to create a store procedure that execute a sql file located in a PATH
for example:
C:\Inetpub\Reserved\update.sql
HOw can I made this?
Thanks a lot!Hi
what version of sql server you using.
if you have sql server installed on your computer, then you can open .sql
file in query analyser or management studio to execute it
i still don't understand why you a need a SP to execute update.sql file
Regards
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"Andrea" <noreply@.nospam.net> wrote in message
news:5Fkni.2101$BM.412@.tornado.fastwebnet.it...
> Hi,
> I'm very very new in SQLServer.
> I need to create a store procedure that execute a sql file located in a
> PATH
> for example:
>
> C:\Inetpub\Reserved\update.sql
>
>
> HOw can I made this?
>
> Thanks a lot!
>
>|||Andrea
Sorry, I type it from the memory, please test it
EXEC master..xp_cmdshell 'osql.exe -S SVR -U sa -P pswd -i
"c:\Inetpub\Reserved\update.sql" -o "C:\output.txt"'
"Andrea" <noreply@.nospam.net> wrote in message
news:5Fkni.2101$BM.412@.tornado.fastwebnet.it...
> Hi,
> I'm very very new in SQLServer.
> I need to create a store procedure that execute a sql file located in a
> PATH
> for example:
>
> C:\Inetpub\Reserved\update.sql
>
>
> HOw can I made this?
>
> Thanks a lot!
>
>

How to create a single 2005 .mdf file from a 2000 .mdf and multiple .ndf data files ?

Greetings,
We're migrating one of our older databases from Sql Server 2000 to Sql
Server 2005.
The old sql server 2000 data files were limited to 2G .mdf and .ndf files.
This was basically because we backed the files up to a UNIX system that had
a 2G file size limit.
Now we're migrating to a brand new Sql Server 2005 system with a 400G tape
backup system and it would seem preferable to append all the 2G .ndf files
into one 10G .mdf file on the new system.
Unfortunately all of the Restore, Move, etc options that I've explored want
to move and recreate the original 2G .mdf and the 6 additional 2G .ndf files
on the new Sql Server 2005 server.
Is there some tool or method I can use that will allow me to create a single
..mdf on the Sql Server 2005 side that consists of the original .mdf and
appended .ndf Sql Server 2000 data files ?
Thanks in advance.
Barry
in Oregon
"frostbb" <barry.b.frost@.remove-this-spam-filter.state.or.us> wrote in
message news:e8GUoGCNHHA.3268@.TK2MSFTNGP04.phx.gbl...
> Greetings,
> We're migrating one of our older databases from Sql Server 2000 to Sql
> Server 2005.
> The old sql server 2000 data files were limited to 2G .mdf and .ndf files.
> This was basically because we backed the files up to a UNIX system that
> had a 2G file size limit.
> Now we're migrating to a brand new Sql Server 2005 system with a 400G tape
> backup system and it would seem preferable to append all the 2G .ndf files
> into one 10G .mdf file on the new system.
>
There isn't any pressing need to do that.

> Unfortunately all of the Restore, Move, etc options that I've explored
> want to move and recreate the original 2G .mdf and the 6 additional 2G
> .ndf files on the new Sql Server 2005 server.
> Is there some tool or method I can use that will allow me to create a
> single .mdf on the Sql Server 2005 side that consists of the original .mdf
> and appended .ndf Sql Server 2000 data files ?
>
No. You can empty and remove each ndf file once the database is restored
using DBCC SHRINKFILE and ALTER DATABASE.
David
|||Hi Barry
"frostbb" wrote:

> Greetings,
> We're migrating one of our older databases from Sql Server 2000 to Sql
> Server 2005.
> The old sql server 2000 data files were limited to 2G .mdf and .ndf files.
> This was basically because we backed the files up to a UNIX system that had
> a 2G file size limit.
> Now we're migrating to a brand new Sql Server 2005 system with a 400G tape
> backup system and it would seem preferable to append all the 2G .ndf files
> into one 10G .mdf file on the new system.
There may still be advantages to having two files especially if they are on
different sets of discs. If they are the same filegroup the allocation of
space to each file will be balanced. If the files are different filegroups
then you can partition your tables/indexes to separate I/O
> Unfortunately all of the Restore, Move, etc options that I've explored want
> to move and recreate the original 2G .mdf and the 6 additional 2G .ndf files
> on the new Sql Server 2005 server.
It is probably easier to do this re-configuration on the new server. If they
are the same file group you can use DBCC SHRINKFILE with the EMPTYFILE
option. if they are different filegroups in each file, then changing the
clustered index so that it is on the primary filegroup will move it to the
..mdf file. If you have text columns defined to be on the second file group
then you might be able to get away with altering the column to varchar(MAX),
failing that you would need to create a new table (with text in the other
filegroup), move the data, remove the original table and then rename the new
table.

> Is there some tool or method I can use that will allow me to create a single
> ..mdf on the Sql Server 2005 side that consists of the original .mdf and
> appended .ndf Sql Server 2000 data files ?
>
Once the files are empty you can use ALTER DATABASE to remove the file.

> Thanks in advance.
> Barry
> in Oregon
>
HTH
John
|||John,
Thanks for the quick reply. Its very much appreciated. Still have a lot to
learn about Sql Server.
Will be reading up on DBCC SHRINKFILE.
Many thanks!
Barry
in Oregon
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:ECF459F6-381C-43AF-B577-0F02F4952048@.microsoft.com...
> Hi Barry
> "frostbb" wrote:
> There may still be advantages to having two files especially if they are
> on
> different sets of discs. If they are the same filegroup the allocation of
> space to each file will be balanced. If the files are different filegroups
> then you can partition your tables/indexes to separate I/O
> It is probably easier to do this re-configuration on the new server. If
> they
> are the same file group you can use DBCC SHRINKFILE with the EMPTYFILE
> option. if they are different filegroups in each file, then changing the
> clustered index so that it is on the primary filegroup will move it to the
> .mdf file. If you have text columns defined to be on the second file group
> then you might be able to get away with altering the column to
> varchar(MAX),
> failing that you would need to create a new table (with text in the other
> filegroup), move the data, remove the original table and then rename the
> new
> table.
> Once the files are empty you can use ALTER DATABASE to remove the file.
> HTH
> John
|||David,
Many thanks for the quick response. I've got plenty to learn about Sql
Server.
Will read up on DBCC Shrinkfile. Still trying to get my 'head around' how
Sql Server 'thinks'
about it's data & log files.
Barry
in Oregon
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:ODTolWCNHHA.320@.TK2MSFTNGP06.phx.gbl...
>
> "frostbb" <barry.b.frost@.remove-this-spam-filter.state.or.us> wrote in
> message news:e8GUoGCNHHA.3268@.TK2MSFTNGP04.phx.gbl...
> There isn't any pressing need to do that.
>
> No. You can empty and remove each ndf file once the database is restored
> using DBCC SHRINKFILE and ALTER DATABASE.
> David
>
>

How to create a single 2005 .mdf file from a 2000 .mdf and multiple .ndf data files ?

Greetings,
We're migrating one of our older databases from Sql Server 2000 to Sql
Server 2005.
The old sql server 2000 data files were limited to 2G .mdf and .ndf files.
This was basically because we backed the files up to a UNIX system that had
a 2G file size limit.
Now we're migrating to a brand new Sql Server 2005 system with a 400G tape
backup system and it would seem preferable to append all the 2G .ndf files
into one 10G .mdf file on the new system.
Unfortunately all of the Restore, Move, etc options that I've explored want
to move and recreate the original 2G .mdf and the 6 additional 2G .ndf files
on the new Sql Server 2005 server.
Is there some tool or method I can use that will allow me to create a single
.mdf on the Sql Server 2005 side that consists of the original .mdf and
appended .ndf Sql Server 2000 data files '
Thanks in advance.
Barry
in Oregon"frostbb" <barry.b.frost@.remove-this-spam-filter.state.or.us> wrote in
message news:e8GUoGCNHHA.3268@.TK2MSFTNGP04.phx.gbl...
> Greetings,
> We're migrating one of our older databases from Sql Server 2000 to Sql
> Server 2005.
> The old sql server 2000 data files were limited to 2G .mdf and .ndf files.
> This was basically because we backed the files up to a UNIX system that
> had a 2G file size limit.
> Now we're migrating to a brand new Sql Server 2005 system with a 400G tape
> backup system and it would seem preferable to append all the 2G .ndf files
> into one 10G .mdf file on the new system.
>
There isn't any pressing need to do that.

> Unfortunately all of the Restore, Move, etc options that I've explored
> want to move and recreate the original 2G .mdf and the 6 additional 2G
> .ndf files on the new Sql Server 2005 server.
> Is there some tool or method I can use that will allow me to create a
> single .mdf on the Sql Server 2005 side that consists of the original .mdf
> and appended .ndf Sql Server 2000 data files '
>
No. You can empty and remove each ndf file once the database is restored
using DBCC SHRINKFILE and ALTER DATABASE.
David|||David,
Many thanks for the quick response. I've got plenty to learn about Sql
Server.
Will read up on DBCC Shrinkfile. Still trying to get my 'head around' how
Sql Server 'thinks'
about it's data & log files.
Barry
in Oregon
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:ODTolWCNHHA.320@.TK2MSFTNGP06.phx.gbl...
>
> "frostbb" <barry.b.frost@.remove-this-spam-filter.state.or.us> wrote in
> message news:e8GUoGCNHHA.3268@.TK2MSFTNGP04.phx.gbl...
> There isn't any pressing need to do that.
>
> No. You can empty and remove each ndf file once the database is restored
> using DBCC SHRINKFILE and ALTER DATABASE.
> David
>
>

How to covert Oracle Store Procedure to SQLServer

Is there any easy to do this, covert Oracle store procedure to SQL Server 2000.
Any help is thankful.
--iqueen.try using the oracle stored procedure directly in oracle.
I hope it will execute.
Again it depends on what version of oracle u have.

I have used sql SP on oracle with minor changes|||Originally posted by cyrus
I have used sql SP on oracle with minor changes

I doubt it...

Why not post some code to show us though...|||the problem is : I have change my Database Server, Oracle->SQLServer2000, the data have transfer to SQL Server, easy done with DTS, but Store Procedure won't, what shall I do ,wanna enable the same store procedure in SQL Server.
Any help is thankful.
--iqueen.|||There is really a lot of store procedure , more than 2000.|||I do not have ORACLE here however I think it can be done using DTS.

After you select the Target Source and Destination Source, you are provided with 3 options one of which says "Copy Objects and..."

If you opt for this option, you get options for all types of objects.

Please let me know if that does not work. Thanks.|||DTS can NOT transfer the store procedure... :(:(:(
Originally posted by tampu
I do not have ORACLE here however I think it can be done using DTS.

After you select the Target Source and Destination Source, you are provided with 3 options one of which says "Copy Objects and..."

If you opt for this option, you get options for all types of objects.

Please let me know if that does not work. Thanks.|||Oracle uses PL-SQL and SQL Server uses T-SQL.. . . .although they are much similar as regards ANSI standards but syntactical differences are definitely there. .. . .. ..functions... . processes and other things sre different
you can refer to microsoft site for they provide much info as to shift orcale code to SQL Server ... ..|||see if this attachment helps u|||the attachment contains basics of oracle Architecture :D
it will not help for Oracle to Sql 2k conversion.

Regards,
Selva Balaji B.|||Sorry ,Probably I gave a wrong one
There no concrete help but difference between oracle and sql
see if it helps u|||Hi cyrus

This is an useful document. :)

Regards,
Selva Balaji B.

How to copy the DTS Package from one server to another?

Hello,
I have created some DTS packages in my sql server now i want to copy them to some other SQLServer. Is it possible to do that? If so pls help me to do that.
Advance thanks...
MuraliWhen saving, choose "Save As" and then change "Location" to "Structured Storage File" instead of "SQL Server".

That will give you a file.dts that you can transfer and load onto another SQL Server.

(To open it on a target server, place yourself on "Data Transformation Services" in EM, and right-click the mouse on right blank field. You'll see "Open Package" in pop-up menu|||Hi KUKUK,
Thanks for your reply. but I know this method. Is there any other way to do this?. Instead of creating seperate dts files i want to eport all of them at one shot. same like import/export database.

If any one knows pls help me.

--Murali|||Well, maybe try to copy msdb ?
Or just transfer msdb's tables that are related to DTS ?|||The same explained in detail here (http://www.sqlservercentral.com/faq/viewfaqanswer.asp?categoryid=2&faqid=204) .

How to copy the content of 3000 Oracle tables into SQLServer?

I have to copy a large (3000) amount of different tables from a Oracle machine into an
SQLServer machine.
I am able to do this using a (VB) script.
I use now several methods:

1) INSERT INTO TABLE1 SELECT * FROM SID1..DB.TABLE1 (SID1 is a linked server)

2) INSERT INTO TABLE1 SELECT * FROM OPENQUERY(SID1,'SELECT * FROM DB.TABLE1')

3) Also used OPENROWSET method (similar to 2)

For small tables this is fine, however for BIG tables (15M Rows/150Cols) the methods above are too slow.
If I compare the same copy action with a simple DTS, the DTS is 3 times faster.
Also, the DTS seems to bulk copy the data directly into the desired database while the
mentioned methods first fill the tempdb, then the transaction log of the desired database and
then finally the desired table (need very much extra space on your filesystem).
The total size of data is about 300GB.

Can anyone supply me with a simple example how to copy data from an Oracle table into a
SQLServer table in script (or SQL) that is as fast as the DTS and not filling my logfiles??
I read the bcp (which I use for import/export files) and bulk insert commands, but
I do not understand how to use them in this question.Your best bet is going to be to dump the data out to delimited files from Oracle and bcp them in to SQL Server...

I would say your going to have at least Date conversion issues...|||I'm sorry. Did you say three THOUSAND tables?
Might I suggest that now might be a good time to redesign your application? A crappy database is still a crappy database, whether it resides on Oracle or SQL Server.|||..the "crappy application" is a quite well known ERP system... (FYI even M$ Navision has over 2000 tables for a single company).
It is a merge of over 30 different companies that went bankrupt recently which of a part will continue to exist while the other must be kept alive for the time being on another platform for the administrators of the company.

Indeed, a dump (master.dbo.xp_cmdshell 'bcp ""dbx.dbo.table1"" in ""\\OLS1\ddumps\Od1.dmp"" -q -k -N -U ""sa"" -P""xyz""') proposed by Brett runs a little (30%) faster (unfortunately Unicode is needed), however not the factor 3 that I can reach with a DTS.
The difference in this speed is (12hours vs 4 hours), that I am able to do an extraction every night (which is a must) or just once a week.|||Well, at least we agree upon its crappiness.|||No other solution(s) in mind?
I am really desperate... No experience with DTS scripting etc?|||Set your SQL Server database's recovery model option to "Simple" to reduce logging. Use DTS to transfer the data directly from your Oracle source, or use BCP to transfer it from a file.
Yes, BCP syntax is esoteric, but it is still the fastest method of loading data.
It seems to me that you are able to transfer the data, but are balking about individually coding all 3000 tables. I don't see any way around that unless you write a batch file to loop through BCPing each file.
Maybe there is a 3rd party utility that will do this for you.

How to copy table from oracle database to sqlserver database ?

Hello,

I need to copy a table from an 8i oracle database to a sqlserver 2000 database.

Is it possible to use the command "COPY FROM ... TO ..." ?
So, what is the correct syntax ?

Thanks for your help

Cyril"Cyril" <jewelk@.free.fr> wrote in message
news:cd38c3d6.0408100617.6f7b9f3e@.posting.google.c om...
> Hello,
> I need to copy a table from an 8i oracle database to a sqlserver 2000
database.
> Is it possible to use the command "COPY FROM ... TO ..." ?
> So, what is the correct syntax ?
>
> Thanks for your help
> Cyril

Look into linked servers in Books Online - you can create a linked server
pointing to the Oracle database, then do this:

insert into dbo.MSSQLTable
select col1, col2, ...
from Oracle..SCHEMA.TABLE

Alternatively, DTS can move data from Oracle to MSSQL.

Simon

How to copy object between SqlServer 2005

How to copy objects like View or Tables from on SqlServer 2005 to another?
If you are talking about just hte DDL then script it out and run that script
on the other server. If you need data as well you can use the wizard in
SSIS.
Andrew J. Kelly SQL MVP
"ad" <flying@.wfes.tcc.edu.tw> wrote in message
news:ey%23EdWOJGHA.1728@.TK2MSFTNGP14.phx.gbl...
> How to copy objects like View or Tables from on SqlServer 2005 to another?
>

How to copy object between SqlServer 2005

How to copy objects like View or Tables from on SqlServer 2005 to another?If you are talking about just hte DDL then script it out and run that script
on the other server. If you need data as well you can use the wizard in
SSIS.
--
Andrew J. Kelly SQL MVP
"ad" <flying@.wfes.tcc.edu.tw> wrote in message
news:ey%23EdWOJGHA.1728@.TK2MSFTNGP14.phx.gbl...
> How to copy objects like View or Tables from on SqlServer 2005 to another?
>

How to copy object between SqlServer 2005

How to copy objects like View or Tables from on SqlServer 2005 to another?If you are talking about just hte DDL then script it out and run that script
on the other server. If you need data as well you can use the wizard in
SSIS.
Andrew J. Kelly SQL MVP
"ad" <flying@.wfes.tcc.edu.tw> wrote in message
news:ey%23EdWOJGHA.1728@.TK2MSFTNGP14.phx.gbl...
> How to copy objects like View or Tables from on SqlServer 2005 to another?
>