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 html form using loop?

Hello

Maybe someone has faced such a problem?

It's simple to create html form, when a table has few fields. But there are more than 200 fields in my tables! So, a way like below becomes unpleasant :) .

CREATE OR REPLACE PROCEDURE pr_name
(
id1 in varchar2 default null,
id2 in char default null
)
IS
begin
<...>
htp.bodyopen;
htp.p('<FORM>');
htp.tableOpen;
htp.p(' <TR><TD>NAME=id1 VALUE='||id1||'></TD>
<TD>NAME=id2 VALUE='||id2||'></TD></TR> ');
htp.bodyclose;
<...>
end;
/

I had an idea to put this : <TD>NAME=id1 VALUE='||id1||'>
in the loop. But then i cannot find how to define VALUE!

of course, i can put the name of parameters in varray. So, NAME it's not a problem longer: NAME=my_varray(i). But the VALUE is still a headache!

I will appreciate any suggestion.
Thanks.Make use of the possibilitys of cursors. Read the data from your table into a cursor and then loop the cursor. There's a specific cursorloop (a kind of for loop). Something like ...

CURSOR <cursorname> IS
SELECT <blabla>
FROM <blabla>;

put the cursor in the DECLARE part of the code. Now you can loop the cursor like this:

FOR <name> IN <cursorname> LOOP
htp.print( <name>.<column selected in table>);
END LOOP;

How to create file using field from a table.

I am using SQL 8.0 and have the challenge of writing text files from a table.
My table contains three fields, file name, file data, and an index. I need to
create a file for every row that uses the 1st field as the file name, which
is already in the correct format [filename.txt] and I need the value from the
second column, filed data, to be the text within the text file.
Can anyone assit me in creating these files? I cannot find anything to
assist me with this in the online books nor MSDN.
You can use this demo code as a start:
-- OJ: TEXTCOPY example
-- Loading files into db &
-- exporting files out to folder
--
--TEXTCOPY IN
--create tb to hold data
create table tmp(fname varchar(100),img image default '0x0')
go
declare @.sql varchar(255),
@.fname varchar(100),
@.path varchar(50),
@.user sysname,
@.pass sysname
set @.user='myuser'
set @.pass='mypass'
--specify desired folder
set @.path='c:\winnt\'
set @.sql='dir ' + @.path + '*.bmp /c /b'
--insert filenames into tb
insert tmp(fname)
exec master..xp_cmdshell @.sql
--loop through and insert file contents into tb
declare cc cursor
for select fname from tmp
open cc
fetch next from cc into @.fname
while @.@.fetch_status=0
begin
set @.sql='textcopy /s"'+@.@.servername+'" /u"'+@.user+'" /p"'+@.pass+'"
/d"'+db_name()+'" /t"tmp" /c"img" /w"where fname=''' + @.fname + '''"'
set @.sql=@.sql + ' /f"' + @.path + @.fname + '" /i' + ' /z'
print @.sql
exec master..xp_cmdshell @.sql ,no_output
fetch next from cc into @.fname
end
close cc
deallocate cc
go
select * from tmp
go
--TEXTCOPY OUT
declare @.sql varchar(255),
@.fname varchar(100),
@.path varchar(50),
@.user sysname,
@.pass sysname
set @.user='myuser'
set @.pass='mypass,'
--specify desired output folder
set @.path='c:\tmp\'
set @.sql='md ' + @.path
--create output folder
exec master..xp_cmdshell @.sql
--loop through and insert file contents into tb
declare cc cursor
for select fname from tmp
open cc
fetch next from cc into @.fname
while @.@.fetch_status=0
begin
set @.sql='textcopy /s"'+@.@.servername+'" /u"'+@.user+'" /p"'+@.pass+'"
/d"'+db_name()+'" /t"tmp" /c"img" /w"where fname=''' + @.fname + '''"'
set @.sql=@.sql + ' /f"' + @.path + @.fname + '" /o' + ' /z'
print @.sql
exec master..xp_cmdshell @.sql ,no_output
fetch next from cc into @.fname
end
close cc
deallocate cc
set @.sql='dir ' + @.path + '*.bmp /c /b'
exec master..xp_cmdshell @.sql
go
drop table tmp
go
"Dorian Foster" <DorianFoster@.discussions.microsoft.com> wrote in message
news:94D63419-1F96-4437-A07E-C293141D84C8@.microsoft.com...
> I am using SQL 8.0 and have the challenge of writing text files from a
table.
> My table contains three fields, file name, file data, and an index. I need
to
> create a file for every row that uses the 1st field as the file name,
which
> is already in the correct format [filename.txt] and I need the value from
the
> second column, filed data, to be the text within the text file.
> Can anyone assit me in creating these files? I cannot find anything to
> assist me with this in the online books nor MSDN.
|||Refer to Books Online; search for "Write File Transformation"
"Dorian Foster" wrote:

> I am using SQL 8.0 and have the challenge of writing text files from a table.
> My table contains three fields, file name, file data, and an index. I need to
> create a file for every row that uses the 1st field as the file name, which
> is already in the correct format [filename.txt] and I need the value from the
> second column, filed data, to be the text within the text file.
> Can anyone assit me in creating these files? I cannot find anything to
> assist me with this in the online books nor MSDN.

How to create file using field from a table.

I am using SQL 8.0 and have the challenge of writing text files from a table
.
My table contains three fields, file name, file data, and an index. I need t
o
create a file for every row that uses the 1st field as the file name, which
is already in the correct format [filename.txt] and I need the value fro
m the
second column, filed data, to be the text within the text file.
Can anyone assit me in creating these files? I cannot find anything to
assist me with this in the online books nor MSDN.You can use this demo code as a start:
-- OJ: TEXTCOPY example
-- Loading files into db &
-- exporting files out to folder
--
--TEXTCOPY IN
--
--create tb to hold data
create table tmp(fname varchar(100),img image default '0x0')
go
declare @.sql varchar(255),
@.fname varchar(100),
@.path varchar(50),
@.user sysname,
@.pass sysname
set @.user='myuser'
set @.pass='mypass'
--specify desired folder
set @.path='c:\winnt'
set @.sql='dir ' + @.path + '*.bmp /c /b'
--insert filenames into tb
insert tmp(fname)
exec master..xp_cmdshell @.sql
--loop through and insert file contents into tb
declare cc cursor
for select fname from tmp
open cc
fetch next from cc into @.fname
while @.@.fetch_status=0
begin
set @.sql='textcopy /s"'+@.@.servername+'" /u"'+@.user+'" /p"'+@.pass+'"
/d"'+db_name()+'" /t"tmp" /c"img" /w"where fname=''' + @.fname + '''"'
set @.sql=@.sql + ' /f"' + @.path + @.fname + '" /i' + ' /z'
print @.sql
exec master..xp_cmdshell @.sql ,no_output
fetch next from cc into @.fname
end
close cc
deallocate cc
go
select * from tmp
go
--
--TEXTCOPY OUT
--
declare @.sql varchar(255),
@.fname varchar(100),
@.path varchar(50),
@.user sysname,
@.pass sysname
set @.user='myuser'
set @.pass='mypass,'
--specify desired output folder
set @.path='c:\tmp'
set @.sql='md ' + @.path
--create output folder
exec master..xp_cmdshell @.sql
--loop through and insert file contents into tb
declare cc cursor
for select fname from tmp
open cc
fetch next from cc into @.fname
while @.@.fetch_status=0
begin
set @.sql='textcopy /s"'+@.@.servername+'" /u"'+@.user+'" /p"'+@.pass+'"
/d"'+db_name()+'" /t"tmp" /c"img" /w"where fname=''' + @.fname + '''"'
set @.sql=@.sql + ' /f"' + @.path + @.fname + '" /o' + ' /z'
print @.sql
exec master..xp_cmdshell @.sql ,no_output
fetch next from cc into @.fname
end
close cc
deallocate cc
set @.sql='dir ' + @.path + '*.bmp /c /b'
exec master..xp_cmdshell @.sql
go
drop table tmp
go
"Dorian Foster" <DorianFoster@.discussions.microsoft.com> wrote in message
news:94D63419-1F96-4437-A07E-C293141D84C8@.microsoft.com...
> I am using SQL 8.0 and have the challenge of writing text files from a
table.
> My table contains three fields, file name, file data, and an index. I need
to
> create a file for every row that uses the 1st field as the file name,
which
> is already in the correct format [filename.txt] and I need the value from[/vbc
ol]
the[vbcol=seagreen]
> second column, filed data, to be the text within the text file.
> Can anyone assit me in creating these files? I cannot find anything to
> assist me with this in the online books nor MSDN.|||Refer to Books Online; search for "Write File Transformation"
"Dorian Foster" wrote:

> I am using SQL 8.0 and have the challenge of writing text files from a tab
le.
> My table contains three fields, file name, file data, and an index. I need
to
> create a file for every row that uses the 1st field as the file name, whic
h
> is already in the correct format [filename.txt] and I need the value f
rom the
> second column, filed data, to be the text within the text file.
> Can anyone assit me in creating these files? I cannot find anything to
> assist me with this in the online books nor MSDN.

How to create file using field from a table.

I am using SQL 8.0 and have the challenge of writing text files from a table.
My table contains three fields, file name, file data, and an index. I need to
create a file for every row that uses the 1st field as the file name, which
is already in the correct format [filename.txt] and I need the value from the
second column, filed data, to be the text within the text file.
Can anyone assit me in creating these files? I cannot find anything to
assist me with this in the online books nor MSDN.You can use this demo code as a start:
-- OJ: TEXTCOPY example
-- Loading files into db &
-- exporting files out to folder
--
--
--TEXTCOPY IN
--
--create tb to hold data
create table tmp(fname varchar(100),img image default '0x0')
go
declare @.sql varchar(255),
@.fname varchar(100),
@.path varchar(50),
@.user sysname,
@.pass sysname
set @.user='myuser'
set @.pass='mypass'
--specify desired folder
set @.path='c:\winnt\'
set @.sql='dir ' + @.path + '*.bmp /c /b'
--insert filenames into tb
insert tmp(fname)
exec master..xp_cmdshell @.sql
--loop through and insert file contents into tb
declare cc cursor
for select fname from tmp
open cc
fetch next from cc into @.fname
while @.@.fetch_status=0
begin
set @.sql='textcopy /s"'+@.@.servername+'" /u"'+@.user+'" /p"'+@.pass+'"
/d"'+db_name()+'" /t"tmp" /c"img" /w"where fname=''' + @.fname + '''"'
set @.sql=@.sql + ' /f"' + @.path + @.fname + '" /i' + ' /z'
print @.sql
exec master..xp_cmdshell @.sql ,no_output
fetch next from cc into @.fname
end
close cc
deallocate cc
go
select * from tmp
go
--
--TEXTCOPY OUT
--
declare @.sql varchar(255),
@.fname varchar(100),
@.path varchar(50),
@.user sysname,
@.pass sysname
set @.user='myuser'
set @.pass='mypass,'
--specify desired output folder
set @.path='c:\tmp\'
set @.sql='md ' + @.path
--create output folder
exec master..xp_cmdshell @.sql
--loop through and insert file contents into tb
declare cc cursor
for select fname from tmp
open cc
fetch next from cc into @.fname
while @.@.fetch_status=0
begin
set @.sql='textcopy /s"'+@.@.servername+'" /u"'+@.user+'" /p"'+@.pass+'"
/d"'+db_name()+'" /t"tmp" /c"img" /w"where fname=''' + @.fname + '''"'
set @.sql=@.sql + ' /f"' + @.path + @.fname + '" /o' + ' /z'
print @.sql
exec master..xp_cmdshell @.sql ,no_output
fetch next from cc into @.fname
end
close cc
deallocate cc
set @.sql='dir ' + @.path + '*.bmp /c /b'
exec master..xp_cmdshell @.sql
go
drop table tmp
go
"Dorian Foster" <DorianFoster@.discussions.microsoft.com> wrote in message
news:94D63419-1F96-4437-A07E-C293141D84C8@.microsoft.com...
> I am using SQL 8.0 and have the challenge of writing text files from a
table.
> My table contains three fields, file name, file data, and an index. I need
to
> create a file for every row that uses the 1st field as the file name,
which
> is already in the correct format [filename.txt] and I need the value from
the
> second column, filed data, to be the text within the text file.
> Can anyone assit me in creating these files? I cannot find anything to
> assist me with this in the online books nor MSDN.|||Refer to Books Online; search for "Write File Transformation"
"Dorian Foster" wrote:
> I am using SQL 8.0 and have the challenge of writing text files from a table.
> My table contains three fields, file name, file data, and an index. I need to
> create a file for every row that uses the 1st field as the file name, which
> is already in the correct format [filename.txt] and I need the value from the
> second column, filed data, to be the text within the text file.
> Can anyone assit me in creating these files? I cannot find anything to
> assist me with this in the online books nor MSDN.

How to create dynamic reports based on custom business objects?

How can we create dynamic reports(or reports created by the end user) by
selected fields from the list of custom business objects.
We would like to offer full data consolidation. Right Crystal offers this
type of functionality but I would like to steer away from that.
Any ideas how to proceed or whether is it even feasible. We do not want to
use reflection on the objects, I mean we can use it but that would not be the
best approach.
ThanksHi Amarnath,
you said SSRS has a new feature using which report builder could be given to
end user to create reports.
Is this Report builder browser based(so that the end user does not need to
install anything) and could design reports and modify the existing reports,
if yes, how to make use of this?
Thanks
Ponnu
"Amarnath" <Amarnath@.discussions.microsoft.com> wrote in message
news:9A2A1500-E374-45F1-8288-56CB8EC97883@.microsoft.com...
> What do you mean by custom BO, is it in the form of dll or assembly sort
> of
> thing then you can always refer the assembly in SS 2005. If it is in the
> form
> of Stored proc then you can always refer that in the report query..
> Infact in RS you need to create a report model and give it to the end
> users,
> SSRS new feature has report builder which is given to the end user to
> create
> their own reports.
>
> Amarnath
>
> "Atul Bahl" wrote:
>> How can we create dynamic reports(or reports created by the end user) by
>> selected fields from the list of custom business objects.
>> We would like to offer full data consolidation. Right Crystal offers this
>> type of functionality but I would like to steer away from that.
>> Any ideas how to proceed or whether is it even feasible. We do not want
>> to
>> use reflection on the objects, I mean we can use it but that would not be
>> the
>> best approach.
>> Thanks

How to create and combine 4 reports, one being a total off fields from the other 3 ?

If I have one rdl.. and I need 4 reports in it, one has a table that is a summary off certain fields from the other 3. I have some questions then

1) How do I add 4 reports to the same report so that I end up with 4 Excel tabs?

2) How can I relate a 4th report to be a summary report of all 3 other reports in the same rdl?

My Excel file in the end will have 4 sheets. 3 of them will be reports, and the final will be a summary table that sums up certain fields based off the other 3 reports. I am not sure the approach to both create the 4 reports together, then ensure pageination is correct in that I end up wtih 4 sheets in an excel workbook when doing an export in the end. You gave a great explanation but now I have more questions :)

Please also reference: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=233219&SiteID=1&mode=1

I responded on the original thread:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=233219&SiteID=1&mode=1

How to create a dimension for multi-value fields

Hi,

I'm new to SSAS and really confused... I'm creating a cube for "Accounts", each account can belong to N categories, I created a "bridge" table on my model where I say:

Account 1 - Category 2
Account 1 - Category 3
Account 2 - Category 2
....

I dont know how to use SSAS to use the bridge table accordingly so whenever I'm browsing the cube using DimCategory and another dimension, lets say DimTerritory I get a proper count. Can you tell me how should I proceed?

Thanks.

Hi,

take a look into Many-to-many dimensional modeling paper from Marko Russo.

It's really good stuff about problems like your one.

How to copy the image field

Hi,
I have to write a procedure which copies some data within and between
tables, including image fields of unknown size. Those image fields contain
documents in various formats and can vary from couple of kilobytes to couple
of megabytes. How can I do it? My first instinct was to look at READTEXT,
WRITETEXT and UPDATETEXT functions, but it seems Microsoft does not
recommend them any more and is going to drop them altogether. Can anyone
direct me to some materials that would show how to do it?
Thanks,
MiroslawMPA (miroslaw_pa@.pf.pl) writes:
> I have to write a procedure which copies some data within and between
> tables, including image fields of unknown size. Those image fields
> contain documents in various formats and can vary from couple of
> kilobytes to couple of megabytes. How can I do it? My first instinct was
> to look at READTEXT, WRITETEXT and UPDATETEXT functions, but it seems
> Microsoft does not recommend them any more and is going to drop them
> altogether. Can anyone direct me to some materials that would show how
> to do it?
You are correct that these operations are depreacated, but so is the
image data type.
The preferred data type for image data in SQL 2005 is varbinary(MAX). If
your data type is image, and you can change that, then you probably
have to use WRITETEXT and UPDATETEXT.
varbinary(MAX) works very much like the regular varbinary(n) data type,
but you can work with chunks with the .write mutator, and this way
update only a part of the column.
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|||MPA wrote:
> Hi,
> I have to write a procedure which copies some data within and between
> tables, including image fields of unknown size. Those image fields contai
n
> documents in various formats and can vary from couple of kilobytes to coup
le
> of megabytes. How can I do it? My first instinct was to look at READTEXT,
> WRITETEXT and UPDATETEXT functions, but it seems Microsoft does not
> recommend them any more and is going to drop them altogether. Can anyone
> direct me to some materials that would show how to do it?
> Thanks,
> Miroslaw
You can use the regular UPDATE statement to copy data between IMAGE
columns. However, in SQL Server 2005 the IMAGE type exists for
backwards compatibility only. Microsoft recommends that you use
VARBINARY(MAX) instead, which is why the old TEXT/IMAGE functions are
also deprecated.
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/ms130214(en-US,SQL.90).aspx
--|||The customers have different servers. The first one has SQL Server 2000, so
it looks like I have to use READTEXT and UPDATETEXT. I just wanted first to
make a test to copy the small piece of data from one row of the table to
another, but it does not seem to work. It runs like this:
DECLARE @.ptrval varbinary(16)
DECLARE @.ptrval2 varbinary(16)
SET @.ptrval = (SELECT TEXTPTR(doc_blob) FROM SMS_DOKUMENTE where doc_num=1
and doc_zkz_id=35)
READTEXT SMS_DOKUMENTE.DOC_BLOB @.ptrval 0 16
SELECT @.ptrval2=TEXTPTR(doc_blob) FROM SMS_DOKUMENTE where doc_num=2 and
doc_zkz_id=35
WRITETEXT SMS_DOKUMENTE.DOC_BLOB @.ptrval2 @.ptrval
I was trying here to read 16 first bytes of DOC_BLOB column from the first
row of SMS_DOKUMENTE table and write them to DOC_BLOB in the second row.
After the operation the written 16 bytes are completely different than the
data in the first row. On the other hand I was able to update a second row
with the statement
like:
WRITETEXT SMS_DOKUMENTE.DOC_BLOB @.ptrval2 'abcdef"
What I am missing?
Thanks,
Miroslaw
Uzytkownik "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> napisal w
wiadomosci news:1147621152.612045.54660@.j55g2000cwa.googlegroups.com...
> MPA wrote:
contain
couple
READTEXT,
> You can use the regular UPDATE statement to copy data between IMAGE
> columns. However, in SQL Server 2005 the IMAGE type exists for
> backwards compatibility only. Microsoft recommends that you use
> VARBINARY(MAX) instead, which is why the old TEXT/IMAGE functions are
> also deprecated.
> --
> 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/ms130214(en-US,SQL.90).aspx
> --
>|||MPA (miroslaw_pa@.pf.pl) writes:
> The customers have different servers. The first one has SQL Server 2000,
> so it looks like I have to use READTEXT and UPDATETEXT. I just wanted
> first to make a test to copy the small piece of data from one row of the
> table to another, but it does not seem to work. It runs like this:
> DECLARE @.ptrval varbinary(16)
> DECLARE @.ptrval2 varbinary(16)
> SET @.ptrval = (SELECT TEXTPTR(doc_blob) FROM SMS_DOKUMENTE where doc_num=1
> and doc_zkz_id=35)
> READTEXT SMS_DOKUMENTE.DOC_BLOB @.ptrval 0 16
> SELECT @.ptrval2=TEXTPTR(doc_blob) FROM SMS_DOKUMENTE where doc_num=2 and
> doc_zkz_id=35
> WRITETEXT SMS_DOKUMENTE.DOC_BLOB @.ptrval2 @.ptrval
> I was trying here to read 16 first bytes of DOC_BLOB column from the first
> row of SMS_DOKUMENTE table and write them to DOC_BLOB in the second row.
> After the operation the written 16 bytes are completely different than the
> data in the first row. On the other hand I was able to update a second row
> with the statement
The statement
READTEXT SMS_DOKUMENTE.DOC_BLOB @.ptrval 0 16
does not assign @.ptrval. Rather @.ptrval is a pointer to the blob data
in the column. The 16 bytes gets returned to the client. So when you
say:
WRITETEXT SMS_DOKUMENTE.DOC_BLOB @.ptrval2 @.ptrval
you are just writing that pointer to the blob.
You can copy blob data with UPDATETEXT. But please don't ask me for an
example. I very rarely work with any of the text operations, and when I
do, I need to study Books Online carefully.
Now, you mentioned that the source and target tables were on different
servers. This is going to make it even more painful.
I would try a straight UPDATE across the linked server. If this breaks
down, I would try to use BCP to bring the source table over the target
server.
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