Best Practices Database Owner, Database Connection Method (asp)

Hi-

I have a sql server database, and am wring web apps to access it.

I've created databases different ways, and ended up with different owners (eg dbo, nt authority\network services...)

I also have connection strings using windows authentication, and some using a user name and password.

I have read that using windows authentication is the best way to go, as far as security goes, but I have noticed some connectivity issues when I upload the site to the server, and test it remotely.

What is the safest 'owner' of the database, and what's the safest way to connect?

Thanks

Dan

You may get somewhat different details from different people but I think most will agree with what I'm about to say (I may live to regret those words!). Remember that the goal is give your users a little privileges as possible

owner of the database should be dbo

|||

Create a login which has an entry in your Active Directory (AD)*, and give it the needed permissions.

Map that login to a database user (name it MyAppUser), this user has only needed permisions on the database (e.g. execute stored procedures and maybe SELECTing some fields from some tables).

Use Windows Authentication if it is possible.

Encrypt your ConnectionString in your Web.Config file.

*: you can enforce some policies like password has to be strong and changed every two weeks or months. Old password can not be used and some policies that can increase the security.

Remember: Too much security doesn't always good.

Good luck.

|||

One more thing I would like to mention is try to use stored procedures ONLY as much as you can.

This will increase the performance (usually) and make your App secure (e.g. SQL Injuction).

Try to not thatMyAppUserother thatEXECstored procedures.

Insred of sending a lot of T-SQL statments over the network, you will just send the stored procedure name.. and once it is executed it will be cached (better performance for later execution).

Make you logic in the stored procedure, allow you to change the logic later -if needed- without redeploying the application or compiling it.

Good luck.

|||

OK, so stored procedures seems to be a common theme.

hodw do I best use them(SP), and use the GUI advantage of visual studio.net?

Do I write, say a SP called "SP_Update_Client()" Then have the asp.net page call

"SP_Update_Client("Param1","Param2")

and how do I get a hold of the stored procedure IN Visual studio?

thanks

dan

(Im getting lazzy in this GUI world)

|||

You don't "get hold" of a proc like you would, say, a dll. You create a sql command and attach parameters to it as in this example http://www.codeproject.com/useritems/simplecodeasp.asp

Note esp their use of output parameters to return data

|||

hummm-

I think Im starting to get it.

If I am writing a small app (500 users, connecting 10 - 25 x a week) will I notice a benifit of procs? in speed? Or is it more of a security issue at this size?

Thanks so much for the discussion an the artilce

|||

Harperator:

If I am writing a small app (500 users, connecting 10 - 25 x a week) will I notice a benifit of procs? in speed? Or is it more of a security issue at this size?

Stored Procedure = Both security + performance, but the main thing here is the security especially SQL Injuction.

Good luck.

|||

Agree with CS4Ever's statement

Best Practice for Structuring XML?

I've created a large XML document from a relational database (using AUTO,
EXPLICIT, etc.) with many elements, attributes, and subelements but now
wonder if there is a "best practice" for designing the structure for going
the other way, XML -> relational. Since I have not yet worked on the data
extraction side, maybe what I've put together makes data extraction awkward
(requiring many lines of T-SQL vs. one or two). But I definitely cannot
stomach the 'all attribute' or 'all element' practices. Between the two
examples below, which is better/easier/more efficient/flexible for
retrieving information (e.g. with OPENXML). I like the first example
theoretically, but the second is REAL easy to generate (with FOR XML AUTO,
ELEMENTS). Thanks for any tips or insights.
<entities>
<entity>
<entityAttribute>nameOfThisEntity</entityAttribute>
<entityValue>valueOfThisEntity</entityValue>
</entity>
<entity>
<entityAttribute>nameOfNextEntity</entityAttribute>
<entityValue>valueOfNextEntity</entityValue>
</entity>
<entity>
...
</entity>
</entities>
vs.
<entities>
<nameOfThisEntity>valueOfThisEntity</nameOfThisEntity>
<nameOfNextEntity>valueOfNextEntity</nameOfNextEntity>
...
</entities>
Hi Don,
I preferred to the first one, although I do not think there will be much
performance difference between the following two XML structures. The fist
XML structure will be more readable and efficient for search. The following
article will tell you how to optimize SQLXML performance for databases,
including SQL Server 2000.
SQLXML best practice paper on MSDN
http://msdn.microsoft.com/library/de...us/dnsql2k/htm
l/sqlxml_optimperformance.asp?frame=true
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
|||If you plan on using OpenXML, then size and ability to query structure
instead of values will most likely make your second format perform better.
Best regards
Michael
"Don Miller" <nospam@.nospam.com> wrote in message
news:es8k9YfLEHA.2396@.TK2MSFTNGP12.phx.gbl...
> I've created a large XML document from a relational database (using AUTO,
> EXPLICIT, etc.) with many elements, attributes, and subelements but now
> wonder if there is a "best practice" for designing the structure for going
> the other way, XML -> relational. Since I have not yet worked on the data
> extraction side, maybe what I've put together makes data extraction
> awkward
> (requiring many lines of T-SQL vs. one or two). But I definitely cannot
> stomach the 'all attribute' or 'all element' practices. Between the two
> examples below, which is better/easier/more efficient/flexible for
> retrieving information (e.g. with OPENXML). I like the first example
> theoretically, but the second is REAL easy to generate (with FOR XML AUTO,
> ELEMENTS). Thanks for any tips or insights.
> <entities>
> <entity>
> <entityAttribute>nameOfThisEntity</entityAttribute>
> <entityValue>valueOfThisEntity</entityValue>
> </entity>
> <entity>
> <entityAttribute>nameOfNextEntity</entityAttribute>
> <entityValue>valueOfNextEntity</entityValue>
> </entity>
> <entity>
> ...
> </entity>
> </entities>
> vs.
> <entities>
> <nameOfThisEntity>valueOfThisEntity</nameOfThisEntity>
> <nameOfNextEntity>valueOfNextEntity</nameOfNextEntity>
> ...
> </entities>
>

Best approach to creating an annotated schema?

I have painfully found out that the schema created from a
dataset.writexmlschema does NOT create an XML schema that can be used with
the XMLBULKLOADER. Does anybody have any insights/code snippets/ideas on ho
w
to create an annotated schema with the 'sql:relation' and 'sql:field'
annotations that are necessary for the xmlbulkloader'You can check out the Books online:
http://msdn.microsoft.com/library/d...ations_0gqb.asp
Bertan ARI
This posting is provided "AS IS" with no warranties, and confers no rights.
"MSSQLServerDeveloper" <MSSQLServerDeveloper@.discussions.microsoft.com>
wrote in message news:51ABC14A-47D6-4F50-8390-F8ADF79B55D9@.microsoft.com...
> I have painfully found out that the schema created from a
> dataset.writexmlschema does NOT create an XML schema that can be used with
> the XMLBULKLOADER. Does anybody have any insights/code snippets/ideas on
how
> to create an annotated schema with the 'sql:relation' and 'sql:field'
> annotations that are necessary for the xmlbulkloader'

Best approach to creating an annotated schema?

I have painfully found out that the schema created from a
dataset.writexmlschema does NOT create an XML schema that can be used with
the XMLBULKLOADER. Does anybody have any insights/code snippets/ideas on how
to create an annotated schema with the 'sql:relation' and 'sql:field'
annotations that are necessary for the xmlbulkloader?
You can check out the Books online:
http://msdn.microsoft.com/library/de...tions_0gqb.asp
Bertan ARI
This posting is provided "AS IS" with no warranties, and confers no rights.
"MSSQLServerDeveloper" <MSSQLServerDeveloper@.discussions.microsoft.com>
wrote in message news:51ABC14A-47D6-4F50-8390-F8ADF79B55D9@.microsoft.com...
> I have painfully found out that the schema created from a
> dataset.writexmlschema does NOT create an XML schema that can be used with
> the XMLBULKLOADER. Does anybody have any insights/code snippets/ideas on
how
> to create an annotated schema with the 'sql:relation' and 'sql:field'
> annotations that are necessary for the xmlbulkloader?

Beginner Questions (and introduction to forum)

I'm new to the forum as well as Report Services. I have created a couple of
sample reports and I'm monkeying with the settings. I have a book on the
way which I hope will help answer some of my questions but in the meantime I
have a couple beginner questions...
1) I want to concatenate a string in the Title of a report with a parameter
I have assigned to the report... Basically I want the title to read "Here's
the Data for 01/01/2003 thru 12/31/2003" where both of the dates in the
title are runtime parameters specified by the user.
2) Is there a way to do conditional formatting and or use constants when
formatting fields... How about records? For instance, make the value in
the field RED if it meets a certain criterion... or, make the record
background blue if the record meets a certain criterion.
Any direction you may provide or answers will be appreciated.
Thanks
REM7600#1: ="Here's the data for " & Parameters!Report_Parameter_0.Value & " to " &
Parameters!Report_Parameter_1.Value [Check
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSMAIN/htm/rsc_ov_using_v1_6foz.asp
for details]
#2: =iif(Trim(Fields!type.Value) = "business", "Red", "Blue") [Check
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/htm/rcr_creating_expressions_v1_3983.asp
for details]
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"REM7600" <rem7600@.hotmail.com> wrote in message
news:uVjBs%23RaEHA.2892@.TK2MSFTNGP10.phx.gbl...
> I'm new to the forum as well as Report Services. I have created a couple
of
> sample reports and I'm monkeying with the settings. I have a book on the
> way which I hope will help answer some of my questions but in the meantime
I
> have a couple beginner questions...
> 1) I want to concatenate a string in the Title of a report with a
parameter
> I have assigned to the report... Basically I want the title to read
"Here's
> the Data for 01/01/2003 thru 12/31/2003" where both of the dates in the
> title are runtime parameters specified by the user.
> 2) Is there a way to do conditional formatting and or use constants when
> formatting fields... How about records? For instance, make the value in
> the field RED if it meets a certain criterion... or, make the record
> background blue if the record meets a certain criterion.
> Any direction you may provide or answers will be appreciated.
> Thanks
> REM7600
>|||1) Set title value to an expression:
="Here's the Data for " + CStr(Parameters!StartDate.Value) + " thru " +
CStr(Parameters!EndDate.Value)
2) You may set color or background color properties to expressions returning
color name, for example:
=iif(Fields!MyData.Value < 0, "Red", "Blue")
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"REM7600" <rem7600@.hotmail.com> wrote in message
news:uVjBs%23RaEHA.2892@.TK2MSFTNGP10.phx.gbl...
> I'm new to the forum as well as Report Services. I have created a couple
> of
> sample reports and I'm monkeying with the settings. I have a book on the
> way which I hope will help answer some of my questions but in the meantime
> I
> have a couple beginner questions...
> 1) I want to concatenate a string in the Title of a report with a
> parameter
> I have assigned to the report... Basically I want the title to read
> "Here's
> the Data for 01/01/2003 thru 12/31/2003" where both of the dates in the
> title are runtime parameters specified by the user.
> 2) Is there a way to do conditional formatting and or use constants when
> formatting fields... How about records? For instance, make the value in
> the field RED if it meets a certain criterion... or, make the record
> background blue if the record meets a certain criterion.
> Any direction you may provide or answers will be appreciated.
> Thanks
> REM7600
>|||Ravi... I could have sworn I did option 1... I do that in access all the
time. Oh well, I'll follow the KB article referenced and see what I left
out (I'm sure there's something).
Thanks for the help!
REM7600
> #1: ="Here's the data for " & Parameters!Report_Parameter_0.Value & " to "
&
> Parameters!Report_Parameter_1.Value [Check
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSMAIN/htm/rsc_ov_using_v1_6foz.asp
> for details]
> #2: =iif(Trim(Fields!type.Value) = "business", "Red", "Blue") [Check
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/htm/rcr_creating_expressions_v1_3983.asp
> for details]|||I didn't try doing the Convert on the dates... Maybe that's what I did
wrong.
Thanks for the quick answers!
REM7600
> 1) Set title value to an expression:
> ="Here's the Data for " + CStr(Parameters!StartDate.Value) + " thru " +
> CStr(Parameters!EndDate.Value)
> 2) You may set color or background color properties to expressions
returning
> color name, for example:
> =iif(Fields!MyData.Value < 0, "Red", "Blue")
> --|||Note:
* if you use "+" to concatenate strings no implicit data type conversions
will happen.
* if you use "&" to concatenate strings, implicit conversions will be done
(invoking the default .ToString() implementation).
i.e. both expressions should work:
="Here's the Data for " + CStr(Parameters!StartDate.Value) + " thru " +
CStr(Parameters!EndDate.Value)
="Here's the Data for " & Parameters!StartDate.Value & " thru " &
Parameters!EndDate.Value
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"REM7600" <rem7600@.hotmail.com> wrote in message
news:OnsrcYSaEHA.1840@.TK2MSFTNGP11.phx.gbl...
> I didn't try doing the Convert on the dates... Maybe that's what I did
> wrong.
> Thanks for the quick answers!
> REM7600
>
> > 1) Set title value to an expression:
> > ="Here's the Data for " + CStr(Parameters!StartDate.Value) + " thru " +
> > CStr(Parameters!EndDate.Value)
> >
> > 2) You may set color or background color properties to expressions
> returning
> > color name, for example:
> > =iif(Fields!MyData.Value < 0, "Red", "Blue")
> >
> > --
>

BCPing into a Table with more columns than the source data file

Hi,
I have a table with 15 columns. However, in my data file I only have 9
columns. I have created a format file to map the data fields to the
relevant columns. Each time I BCP data into the table though it just
inserts the data into the first 9 columns regardless of the fact that
I have mapped fields 7,8 and 9 in the data file to other columns in
the table.
My Fomat file:
<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/
format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<RECORD>
<FIELD ID="1" xsi:type="NativeFixed" LENGTH="8"/>
<FIELD ID="2" xsi:type="NativeFixed" LENGTH="4"/>
<FIELD ID="3" xsi:type="NativeFixed" LENGTH="4"/>
<FIELD ID="4" xsi:type="NativeFixed" LENGTH="4"/>
<FIELD ID="5" xsi:type="NativeFixed" LENGTH="4"/>
<FIELD ID="6" xsi:type="NativeFixed" LENGTH="8"/>
<FIELD ID="7" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
<FIELD ID="8" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
<FIELD ID="9" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
</RECORD>
<ROW>
<COLUMN SOURCE="1" NAME="Col1" xsi:type="SQLDATETIME"/>
<COLUMN SOURCE="2" NAME="Col2" xsi:type="SQLINT"/>
<COLUMN SOURCE="3" NAME="Col3" xsi:type="SQLINT"/>
<COLUMN SOURCE="4" NAME="Col4" xsi:type="SQLINT"/>
<COLUMN SOURCE="5" NAME="Col5" xsi:type="SQLINT"/>
<COLUMN SOURCE="6" NAME="Col6" xsi:type="SQLFLT8"/>
<COLUMN SOURCE="7" NAME="Col13" xsi:type="SQLFLT8"/>
<COLUMN SOURCE="8" NAME="Col14" xsi:type="SQLFLT8"/>
<COLUMN SOURCE="9" NAME="Col15" xsi:type="SQLFLT8"/>
</ROW>
</BCPFORMAT>
Fields 7,8 and 9 are mapped to columns 13,14 and 15. However the data
from fields 7,8 and 9 in the file are still being inserted into
columns 7,8 and 9 in the table.
What I also notice is that if I change the "NAME" in the column
element to something other than the name of the column in the table
then it doesn't cause an error.
It isn't using the column name then when doing the BCP in.
The BCP command I am using is:
bcp testdb.dbo.testTable in D:\test\testData.bcp -f D:\test
\TestFormat.fmt -T -t
HOWEVER, if I use the following command:
INSERT INTO testTable (col1 ... coln)
SELECT col1 ... coln
FROM OPENROWSET(BULK 'D:\test\testData.bcp',
FORMATFILE='D:\test\TestFormat.xml'
) AS t1;
This inserts the data correctly. But I need to use BCP because the
BULK INSERT method will write to the transaction log ... not good with
the volumes of data I am dealing.
Does anyone have any ideas of what I am doing wrong here? From what I
have read on Books online this should be feasible but I just cannot
get it working:-(
Any ideas / suggestions would be much appreciated.
Many Thanks
On Mar 31, 4:57 am, scud...@.yahoo.com wrote:
> Hi,
> I have a table with 15 columns. However, in my data file I only have 9
> columns. I have created a format file to map the data fields to the
> relevant columns. Each time I BCP data into the table though it just
> inserts the data into the first 9 columns regardless of the fact that
> I have mapped fields 7,8 and 9 in the data file to other columns in
> the table.
> My Fomat file:
> <?xml version="1.0"?>
> <BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/
> format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
> <RECORD>
> <FIELD ID="1" xsi:type="NativeFixed" LENGTH="8"/>
> <FIELD ID="2" xsi:type="NativeFixed" LENGTH="4"/>
> <FIELD ID="3" xsi:type="NativeFixed" LENGTH="4"/>
> <FIELD ID="4" xsi:type="NativeFixed" LENGTH="4"/>
> <FIELD ID="5" xsi:type="NativeFixed" LENGTH="4"/>
> <FIELD ID="6" xsi:type="NativeFixed" LENGTH="8"/>
> <FIELD ID="7" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
> <FIELD ID="8" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
> <FIELD ID="9" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
> </RECORD>
> <ROW>
> <COLUMN SOURCE="1" NAME="Col1" xsi:type="SQLDATETIME"/>
> <COLUMN SOURCE="2" NAME="Col2" xsi:type="SQLINT"/>
> <COLUMN SOURCE="3" NAME="Col3" xsi:type="SQLINT"/>
> <COLUMN SOURCE="4" NAME="Col4" xsi:type="SQLINT"/>
> <COLUMN SOURCE="5" NAME="Col5" xsi:type="SQLINT"/>
> <COLUMN SOURCE="6" NAME="Col6" xsi:type="SQLFLT8"/>
> <COLUMN SOURCE="7" NAME="Col13" xsi:type="SQLFLT8"/>
> <COLUMN SOURCE="8" NAME="Col14" xsi:type="SQLFLT8"/>
> <COLUMN SOURCE="9" NAME="Col15" xsi:type="SQLFLT8"/>
> </ROW>
> </BCPFORMAT>
> Fields 7,8 and 9 are mapped to columns 13,14 and 15. However the data
> from fields 7,8 and 9 in the file are still being inserted into
> columns 7,8 and 9 in the table.
> What I also notice is that if I change the "NAME" in the column
> element to something other than the name of the column in the table
> then it doesn't cause an error.
> It isn't using the column name then when doing the BCP in.
> The BCP command I am using is:
> bcp testdb.dbo.testTable in D:\test\testData.bcp -f D:\test
> \TestFormat.fmt -T -t
> HOWEVER, if I use the following command:
> INSERT INTO testTable (col1 ... coln)
> SELECT col1 ... coln
> FROM OPENROWSET(BULK 'D:\test\testData.bcp',
> FORMATFILE='D:\test\TestFormat.xml'
> ) AS t1;
> This inserts the data correctly. But I need to use BCP because the
> BULK INSERT method will write to the transaction log ... not good with
> the volumes of data I am dealing.
> Does anyone have any ideas of what I am doing wrong here? From what I
> have read on Books online this should be feasible but I just cannot
> get it working:-(
> Any ideas / suggestions would be much appreciated.
> Many Thanks
You can Create a View with Required columns ( columns in your input
file) and BCP in to the view . Make sure other columns in the table
should have null allowed
|||<scudi54@.yahoo.com> wrote in message
news:1175299043.974699.83050@.l77g2000hsb.googlegro ups.com...
> This inserts the data correctly. But I need to use BCP because the
> BULK INSERT method will write to the transaction log ... not good with
> the volumes of data I am dealing.
BCP writes to the transaction log as well. If you're concerned about the
transaction log size growing out of control, then look up minimally logged
operations here: http://msdn2.microsoft.com/en-us/library/ms191244.aspx and
here http://msdn2.microsoft.com/en-us/library/ms190422.aspx

BCPing into a Table with more columns than the source data file

Hi,
I have a table with 15 columns. However, in my data file I only have 9
columns. I have created a format file to map the data fields to the
relevant columns. Each time I BCP data into the table though it just
inserts the data into the first 9 columns regardless of the fact that
I have mapped fields 7,8 and 9 in the data file to other columns in
the table.
My Fomat file:
<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/
format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<RECORD>
<FIELD ID="1" xsi:type="NativeFixed" LENGTH="8"/>
<FIELD ID="2" xsi:type="NativeFixed" LENGTH="4"/>
<FIELD ID="3" xsi:type="NativeFixed" LENGTH="4"/>
<FIELD ID="4" xsi:type="NativeFixed" LENGTH="4"/>
<FIELD ID="5" xsi:type="NativeFixed" LENGTH="4"/>
<FIELD ID="6" xsi:type="NativeFixed" LENGTH="8"/>
<FIELD ID="7" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
<FIELD ID="8" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
<FIELD ID="9" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
</RECORD>
<ROW>
<COLUMN SOURCE="1" NAME="Col1" xsi:type="SQLDATETIME"/>
<COLUMN SOURCE="2" NAME="Col2" xsi:type="SQLINT"/>
<COLUMN SOURCE="3" NAME="Col3" xsi:type="SQLINT"/>
<COLUMN SOURCE="4" NAME="Col4" xsi:type="SQLINT"/>
<COLUMN SOURCE="5" NAME="Col5" xsi:type="SQLINT"/>
<COLUMN SOURCE="6" NAME="Col6" xsi:type="SQLFLT8"/>
<COLUMN SOURCE="7" NAME="Col13" xsi:type="SQLFLT8"/>
<COLUMN SOURCE="8" NAME="Col14" xsi:type="SQLFLT8"/>
<COLUMN SOURCE="9" NAME="Col15" xsi:type="SQLFLT8"/>
</ROW>
</BCPFORMAT>
Fields 7,8 and 9 are mapped to columns 13,14 and 15. However the data
from fields 7,8 and 9 in the file are still being inserted into
columns 7,8 and 9 in the table.
What I also notice is that if I change the "NAME" in the column
element to something other than the name of the column in the table
then it doesn't cause an error.
It isn't using the column name then when doing the BCP in.
The BCP command I am using is:
bcp testdb.dbo.testTable in D:\test\testData.bcp -f D:\test
\TestFormat.fmt -T -t
HOWEVER, if I use the following command:
INSERT INTO testTable (col1 ... coln)
SELECT col1 ... coln
FROM OPENROWSET(BULK 'D:\test\testData.bcp',
FORMATFILE='D:\test\TestFormat.xml'
) AS t1;
This inserts the data correctly. But I need to use BCP because the
BULK INSERT method will write to the transaction log ... not good with
the volumes of data I am dealing.
Does anyone have any ideas of what I am doing wrong here? From what I
have read on Books online this should be feasible but I just cannot
get it working:-(
Any ideas / suggestions would be much appreciated.
Many ThanksOn Mar 31, 4:57 am, scud...@.yahoo.com wrote:
> Hi,
> I have a table with 15 columns. However, in my data file I only have 9
> columns. I have created a format file to map the data fields to the
> relevant columns. Each time I BCP data into the table though it just
> inserts the data into the first 9 columns regardless of the fact that
> I have mapped fields 7,8 and 9 in the data file to other columns in
> the table.
> My Fomat file:
> <?xml version="1.0"?>
> <BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/
> format" xmlns:xsi="">http://www.w3.org/2001/XMLSchema-instance">
> <RECORD>
> <FIELD ID="1" xsi:type="NativeFixed" LENGTH="8"/>
> <FIELD ID="2" xsi:type="NativeFixed" LENGTH="4"/>
> <FIELD ID="3" xsi:type="NativeFixed" LENGTH="4"/>
> <FIELD ID="4" xsi:type="NativeFixed" LENGTH="4"/>
> <FIELD ID="5" xsi:type="NativeFixed" LENGTH="4"/>
> <FIELD ID="6" xsi:type="NativeFixed" LENGTH="8"/>
> <FIELD ID="7" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
> <FIELD ID="8" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
> <FIELD ID="9" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
> </RECORD>
> <ROW>
> <COLUMN SOURCE="1" NAME="Col1" xsi:type="SQLDATETIME"/>
> <COLUMN SOURCE="2" NAME="Col2" xsi:type="SQLINT"/>
> <COLUMN SOURCE="3" NAME="Col3" xsi:type="SQLINT"/>
> <COLUMN SOURCE="4" NAME="Col4" xsi:type="SQLINT"/>
> <COLUMN SOURCE="5" NAME="Col5" xsi:type="SQLINT"/>
> <COLUMN SOURCE="6" NAME="Col6" xsi:type="SQLFLT8"/>
> <COLUMN SOURCE="7" NAME="Col13" xsi:type="SQLFLT8"/>
> <COLUMN SOURCE="8" NAME="Col14" xsi:type="SQLFLT8"/>
> <COLUMN SOURCE="9" NAME="Col15" xsi:type="SQLFLT8"/>
> </ROW>
> </BCPFORMAT>
> Fields 7,8 and 9 are mapped to columns 13,14 and 15. However the data
> from fields 7,8 and 9 in the file are still being inserted into
> columns 7,8 and 9 in the table.
> What I also notice is that if I change the "NAME" in the column
> element to something other than the name of the column in the table
> then it doesn't cause an error.
> It isn't using the column name then when doing the BCP in.
> The BCP command I am using is:
> bcp testdb.dbo.testTable in D:\test\testData.bcp -f D:\test
> \TestFormat.fmt -T -t
> HOWEVER, if I use the following command:
> INSERT INTO testTable (col1 ... coln)
> SELECT col1 ... coln
> FROM OPENROWSET(BULK 'D:\test\testData.bcp',
> FORMATFILE='D:\test\TestFormat.xml'
> ) AS t1;
> This inserts the data correctly. But I need to use BCP because the
> BULK INSERT method will write to the transaction log ... not good with
> the volumes of data I am dealing.
> Does anyone have any ideas of what I am doing wrong here? From what I
> have read on Books online this should be feasible but I just cannot
> get it working:-(
> Any ideas / suggestions would be much appreciated.
> Many Thanks
You can Create a View with Required columns ( columns in your input
file) and BCP in to the view . Make sure other columns in the table
should have null allowed|||<scudi54@.yahoo.com> wrote in message
news:1175299043.974699.83050@.l77g2000hsb.googlegroups.com...
> This inserts the data correctly. But I need to use BCP because the
> BULK INSERT method will write to the transaction log ... not good with
> the volumes of data I am dealing.
BCP writes to the transaction log as well. If you're concerned about the
transaction log size growing out of control, then look up minimally logged
operations here: http://msdn2.microsoft.com/en-us/library/ms191244.aspx and
here http://msdn2.microsoft.com/en-us/library/ms190422.aspx

BCPing into a Table with more columns than the source data file

Hi,
I have a table with 15 columns. However, in my data file I only have 9
columns. I have created a format file to map the data fields to the
relevant columns. Each time I BCP data into the table though it just
inserts the data into the first 9 columns regardless of the fact that
I have mapped fields 7,8 and 9 in the data file to other columns in
the table.
My Fomat file:
<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/
format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<RECORD>
<FIELD ID="1" xsi:type="NativeFixed" LENGTH="8"/>
<FIELD ID="2" xsi:type="NativeFixed" LENGTH="4"/>
<FIELD ID="3" xsi:type="NativeFixed" LENGTH="4"/>
<FIELD ID="4" xsi:type="NativeFixed" LENGTH="4"/>
<FIELD ID="5" xsi:type="NativeFixed" LENGTH="4"/>
<FIELD ID="6" xsi:type="NativeFixed" LENGTH="8"/>
<FIELD ID="7" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
<FIELD ID="8" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
<FIELD ID="9" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
</RECORD>
<ROW>
<COLUMN SOURCE="1" NAME="Col1" xsi:type="SQLDATETIME"/>
<COLUMN SOURCE="2" NAME="Col2" xsi:type="SQLINT"/>
<COLUMN SOURCE="3" NAME="Col3" xsi:type="SQLINT"/>
<COLUMN SOURCE="4" NAME="Col4" xsi:type="SQLINT"/>
<COLUMN SOURCE="5" NAME="Col5" xsi:type="SQLINT"/>
<COLUMN SOURCE="6" NAME="Col6" xsi:type="SQLFLT8"/>
<COLUMN SOURCE="7" NAME="Col13" xsi:type="SQLFLT8"/>
<COLUMN SOURCE="8" NAME="Col14" xsi:type="SQLFLT8"/>
<COLUMN SOURCE="9" NAME="Col15" xsi:type="SQLFLT8"/>
</ROW>
</BCPFORMAT>
Fields 7,8 and 9 are mapped to columns 13,14 and 15. However the data
from fields 7,8 and 9 in the file are still being inserted into
columns 7,8 and 9 in the table.
What I also notice is that if I change the "NAME" in the column
element to something other than the name of the column in the table
then it doesn't cause an error.
It isn't using the column name then when doing the BCP in.
The BCP command I am using is:
bcp testdb.dbo.testTable in D:\test\testData.bcp -f D:\test
\TestFormat.fmt -T -t
HOWEVER, if I use the following command:
INSERT INTO testTable (col1 ... coln)
SELECT col1 ... coln
FROM OPENROWSET(BULK 'D:\test\testData.bcp',
FORMATFILE='D:\test\TestFormat.xml'
) AS t1;
This inserts the data correctly. But I need to use BCP because the
BULK INSERT method will write to the transaction log ... not good with
the volumes of data I am dealing.
Does anyone have any ideas of what I am doing wrong here? From what I
have read on Books online this should be feasible but I just cannot
get it working:-(
Any ideas / suggestions would be much appreciated.
Many ThanksOn Mar 31, 4:57 am, scud...@.yahoo.com wrote:
> Hi,
> I have a table with 15 columns. However, in my data file I only have 9
> columns. I have created a format file to map the data fields to the
> relevant columns. Each time I BCP data into the table though it just
> inserts the data into the first 9 columns regardless of the fact that
> I have mapped fields 7,8 and 9 in the data file to other columns in
> the table.
> My Fomat file:
> <?xml version="1.0"?>
> <BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/
> format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
> <RECORD>
> <FIELD ID="1" xsi:type="NativeFixed" LENGTH="8"/>
> <FIELD ID="2" xsi:type="NativeFixed" LENGTH="4"/>
> <FIELD ID="3" xsi:type="NativeFixed" LENGTH="4"/>
> <FIELD ID="4" xsi:type="NativeFixed" LENGTH="4"/>
> <FIELD ID="5" xsi:type="NativeFixed" LENGTH="4"/>
> <FIELD ID="6" xsi:type="NativeFixed" LENGTH="8"/>
> <FIELD ID="7" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
> <FIELD ID="8" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
> <FIELD ID="9" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
> </RECORD>
> <ROW>
> <COLUMN SOURCE="1" NAME="Col1" xsi:type="SQLDATETIME"/>
> <COLUMN SOURCE="2" NAME="Col2" xsi:type="SQLINT"/>
> <COLUMN SOURCE="3" NAME="Col3" xsi:type="SQLINT"/>
> <COLUMN SOURCE="4" NAME="Col4" xsi:type="SQLINT"/>
> <COLUMN SOURCE="5" NAME="Col5" xsi:type="SQLINT"/>
> <COLUMN SOURCE="6" NAME="Col6" xsi:type="SQLFLT8"/>
> <COLUMN SOURCE="7" NAME="Col13" xsi:type="SQLFLT8"/>
> <COLUMN SOURCE="8" NAME="Col14" xsi:type="SQLFLT8"/>
> <COLUMN SOURCE="9" NAME="Col15" xsi:type="SQLFLT8"/>
> </ROW>
> </BCPFORMAT>
> Fields 7,8 and 9 are mapped to columns 13,14 and 15. However the data
> from fields 7,8 and 9 in the file are still being inserted into
> columns 7,8 and 9 in the table.
> What I also notice is that if I change the "NAME" in the column
> element to something other than the name of the column in the table
> then it doesn't cause an error.
> It isn't using the column name then when doing the BCP in.
> The BCP command I am using is:
> bcp testdb.dbo.testTable in D:\test\testData.bcp -f D:\test
> \TestFormat.fmt -T -t
> HOWEVER, if I use the following command:
> INSERT INTO testTable (col1 ... coln)
> SELECT col1 ... coln
> FROM OPENROWSET(BULK 'D:\test\testData.bcp',
> FORMATFILE='D:\test\TestFormat.xml'
> ) AS t1;
> This inserts the data correctly. But I need to use BCP because the
> BULK INSERT method will write to the transaction log ... not good with
> the volumes of data I am dealing.
> Does anyone have any ideas of what I am doing wrong here? From what I
> have read on Books online this should be feasible but I just cannot
> get it working:-(
> Any ideas / suggestions would be much appreciated.
> Many Thanks
You can Create a View with Required columns ( columns in your input
file) and BCP in to the view . Make sure other columns in the table
should have null allowed|||<scudi54@.yahoo.com> wrote in message
news:1175299043.974699.83050@.l77g2000hsb.googlegroups.com...
> This inserts the data correctly. But I need to use BCP because the
> BULK INSERT method will write to the transaction log ... not good with
> the volumes of data I am dealing.
BCP writes to the transaction log as well. If you're concerned about the
transaction log size growing out of control, then look up minimally logged
operations here: http://msdn2.microsoft.com/en-us/library/ms191244.aspx and
here http://msdn2.microsoft.com/en-us/library/ms190422.aspx