Calculated field

can I create a field whose values will be derived from
other fields in the table without writing a stored proc or
script? For example if I have a table called Salary with
three fields: hours, rate and GrossPay. I want the
grosspay field to be updated automatically if values have
been provided for the hours and rate fields. Is this
feasible'
Thanks for your help in advance.This is called a computed column. The values are not stored but are
calculated when a result set is requested. The syntax is documented under
the the CREATE TABLE and ALTER TABLE commands in BOL (Books On-Line).
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"lala" <anonymous@.discussions.microsoft.com> wrote in message
news:194b01c4aafa$39e6cef0$7d02280a@.phx.gbl...
> can I create a field whose values will be derived from
> other fields in the table without writing a stored proc or
> script? For example if I have a table called Salary with
> three fields: hours, rate and GrossPay. I want the
> grosspay field to be updated automatically if values have
> been provided for the hours and rate fields. Is this
> feasible'
> Thanks for your help in advance.|||lala,
I believe you can use a trigger to accomplish what you are asking. in the
BOL navigate to 'create trigger', and you can look at some examples there.
essentially, every time someone updates those columns, the trigger should be
able to poulate the third column.
hth
"lala" wrote:
> can I create a field whose values will be derived from
> other fields in the table without writing a stored proc or
> script? For example if I have a table called Salary with
> three fields: hours, rate and GrossPay. I want the
> grosspay field to be updated automatically if values have
> been provided for the hours and rate fields. Is this
> feasible'
> Thanks for your help in advance.
>|||In addition to the other posts: consider having a view where you define the calculated columns and
use that view. This way you don't have to "litter" your tables with calculated values.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"lala" <anonymous@.discussions.microsoft.com> wrote in message
news:194b01c4aafa$39e6cef0$7d02280a@.phx.gbl...
> can I create a field whose values will be derived from
> other fields in the table without writing a stored proc or
> script? For example if I have a table called Salary with
> three fields: hours, rate and GrossPay. I want the
> grosspay field to be updated automatically if values have
> been provided for the hours and rate fields. Is this
> feasible'
> Thanks for your help in advance.sql

CAL License Question

I know that the number of CALs licensed to a given server is stored in some
system table because at one point I had and ran a script to provide that
information. But I either didn't keep the script or didn't name it well as
I
cannot find it. Does anyone know how to query to find this information?Hi,
From Query Analyzer you can Execute
select SERVERPROPERTY('LicenseType')
select SERVERPROPERTY('NumLicenses')
FYI, CALS are not stored in any system table.
Thanks
Hari
SQL Server MVP
"RKinder" <RKinder@.discussions.microsoft.com> wrote in message
news:D22C57DA-4CA4-41CF-B9B7-EE1F8ADDED03@.microsoft.com...
> I know that the number of CALs licensed to a given server is stored in
some
> system table because at one point I had and ran a script to provide that
> information. But I either didn't keep the script or didn't name it well
as I
> cannot find it. Does anyone know how to query to find this information?|||Thank You.
For whatever reason I'm getting Disabled and Null, but I can look into that.
Thanks again.
"Hari Prasad" wrote:

> Hi,
> From Query Analyzer you can Execute
>
> select SERVERPROPERTY('LicenseType')
> select SERVERPROPERTY('NumLicenses')
> FYI, CALS are not stored in any system table.
> Thanks
> Hari
> SQL Server MVP
> "RKinder" <RKinder@.discussions.microsoft.com> wrote in message
> news:D22C57DA-4CA4-41CF-B9B7-EE1F8ADDED03@.microsoft.com...
> some
> as I
>
>|||RKinder wrote:
> Thank You.
> For whatever reason I'm getting Disabled and Null, but I can look
> into that. Thanks again.
>
I believe that's a known issue prior to SP2. What service pack are you
running?
http://support.microsoft.com/defaul...KB;en-us;291332
David Gugick
Imceda Software
www.imceda.com

CAL License Question

I know that the number of CALs licensed to a given server is stored in some
system table because at one point I had and ran a script to provide that
information. But I either didn't keep the script or didn't name it well as I
cannot find it. Does anyone know how to query to find this information?Hi,
From Query Analyzer you can Execute
select SERVERPROPERTY('LicenseType')
select SERVERPROPERTY('NumLicenses')
FYI, CALS are not stored in any system table.
Thanks
Hari
SQL Server MVP
"RKinder" <RKinder@.discussions.microsoft.com> wrote in message
news:D22C57DA-4CA4-41CF-B9B7-EE1F8ADDED03@.microsoft.com...
> I know that the number of CALs licensed to a given server is stored in
some
> system table because at one point I had and ran a script to provide that
> information. But I either didn't keep the script or didn't name it well
as I
> cannot find it. Does anyone know how to query to find this information?|||Thank You.
For whatever reason I'm getting Disabled and Null, but I can look into that.
Thanks again.
"Hari Prasad" wrote:
> Hi,
> From Query Analyzer you can Execute
>
> select SERVERPROPERTY('LicenseType')
> select SERVERPROPERTY('NumLicenses')
> FYI, CALS are not stored in any system table.
> Thanks
> Hari
> SQL Server MVP
> "RKinder" <RKinder@.discussions.microsoft.com> wrote in message
> news:D22C57DA-4CA4-41CF-B9B7-EE1F8ADDED03@.microsoft.com...
> > I know that the number of CALs licensed to a given server is stored in
> some
> > system table because at one point I had and ran a script to provide that
> > information. But I either didn't keep the script or didn't name it well
> as I
> > cannot find it. Does anyone know how to query to find this information?
>
>|||RKinder wrote:
> Thank You.
> For whatever reason I'm getting Disabled and Null, but I can look
> into that. Thanks again.
>
I believe that's a known issue prior to SP2. What service pack are you
running?
http://support.microsoft.com/default.aspx?scid=KB;en-us;291332
--
David Gugick
Imceda Software
www.imceda.com

CAL License Question

I know that the number of CALs licensed to a given server is stored in some
system table because at one point I had and ran a script to provide that
information. But I either didn't keep the script or didn't name it well as I
cannot find it. Does anyone know how to query to find this information?
Hi,
From Query Analyzer you can Execute
select SERVERPROPERTY('LicenseType')
select SERVERPROPERTY('NumLicenses')
FYI, CALS are not stored in any system table.
Thanks
Hari
SQL Server MVP
"RKinder" <RKinder@.discussions.microsoft.com> wrote in message
news:D22C57DA-4CA4-41CF-B9B7-EE1F8ADDED03@.microsoft.com...
> I know that the number of CALs licensed to a given server is stored in
some
> system table because at one point I had and ran a script to provide that
> information. But I either didn't keep the script or didn't name it well
as I
> cannot find it. Does anyone know how to query to find this information?
|||Thank You.
For whatever reason I'm getting Disabled and Null, but I can look into that.
Thanks again.
"Hari Prasad" wrote:

> Hi,
> From Query Analyzer you can Execute
>
> select SERVERPROPERTY('LicenseType')
> select SERVERPROPERTY('NumLicenses')
> FYI, CALS are not stored in any system table.
> Thanks
> Hari
> SQL Server MVP
> "RKinder" <RKinder@.discussions.microsoft.com> wrote in message
> news:D22C57DA-4CA4-41CF-B9B7-EE1F8ADDED03@.microsoft.com...
> some
> as I
>
>
|||RKinder wrote:
> Thank You.
> For whatever reason I'm getting Disabled and Null, but I can look
> into that. Thanks again.
>
I believe that's a known issue prior to SP2. What service pack are you
running?
http://support.microsoft.com/default...B;en-us;291332
David Gugick
Imceda Software
www.imceda.com

Caclulate database size and free space left

Dear all,
Is that possible to calculate the database size and free space left by t-sql
script?
IvanIvan
sp_helpdb 'northwind'
There is an article in the BOL for this subject
"Ivan" <ivan@.microsoft.com> wrote in message
news:ug9q%233L8FHA.1028@.TK2MSFTNGP11.phx.gbl...
> Dear all,
> Is that possible to calculate the database size and free space left by
> t-sql script?
> Ivan
>|||It had the db size and file size. However, I need the space available in the
file.
The most similar function is sp_space_used.
But I found the space left is not match with the one I saw in Enterprise
Manager.
Do you know how to calculate the space left in enterprise manager?
Ivan
"Uri Dimant" <urid@.iscar.co.il> ¼¶¼g©ó¶l¥ó·s»D:e%23JXCgM8FHA.3752@.tk2msftngp13.phx.gbl...
> Ivan
> sp_helpdb 'northwind'
> There is an article in the BOL for this subject
>
>
> "Ivan" <ivan@.microsoft.com> wrote in message
> news:ug9q%233L8FHA.1028@.TK2MSFTNGP11.phx.gbl...
>> Dear all,
>> Is that possible to calculate the database size and free space left by
>> t-sql script?
>> Ivan
>|||Ivan
sp_spaceused in the BOL
"Ivan" <ivan@.microsoft.com> wrote in message
news:%23FcO0QO8FHA.2304@.TK2MSFTNGP10.phx.gbl...
> It had the db size and file size. However, I need the space available in
> the file.
> The most similar function is sp_space_used.
> But I found the space left is not match with the one I saw in Enterprise
> Manager.
> Do you know how to calculate the space left in enterprise manager?
> Ivan
>
> "Uri Dimant" <urid@.iscar.co.il>
> ¼¶¼g©ó¶l¥ó·s»D:e%23JXCgM8FHA.3752@.tk2msftngp13.phx.gbl...
>> Ivan
>> sp_helpdb 'northwind'
>> There is an article in the BOL for this subject
>>
>>
>> "Ivan" <ivan@.microsoft.com> wrote in message
>> news:ug9q%233L8FHA.1028@.TK2MSFTNGP11.phx.gbl...
>> Dear all,
>> Is that possible to calculate the database size and free space left by
>> t-sql script?
>> Ivan
>>
>|||I once wrote a post for that:
http://groups.google.de/group/comp.databases.ms-sqlserver/browse_frm/thread/7494eacf442935d5
HTH, jens SUessmeyer.|||Hi Ivan,
I use the below mentioned script for this purpose. This provides me separate
result set for Data Files and Log Files. The only possible issue with this
script is that it is using undocumented SP (DBCC SHOWFILESTATS). The positive
side is that it gives the sizes as shown in EM.
--Script to calculate information about the Data Files
SET QUOTED_IDENTIFIER OFF
SET NOCOUNT ON
DECLARE @.dbname varchar(50)
declare @.string varchar(250)
set @.string = ''
create table #datafilestats
( Fileid tinyint,
FileGroup1 tinyint,
TotalExtents1 dec (8, 2),
UsedExtents1 dec (8, 2),
[Name] varchar(50),
[FileName] sysname )
create table #dbstats
( dbname varchar(50),
FileGroupId tinyint,
FileGroupName varchar(25),
TotalSizeinMB dec (8, 2),
UsedSizeinMB dec (8, 2),
FreeSizeinMB dec (8, 2))
DECLARE dbnames_cursor CURSOR FOR SELECT name FROM master..sysdatabases
OPEN dbnames_cursor
FETCH NEXT FROM dbnames_cursor INTO @.dbname
WHILE (@.@.fetch_status = 0)
BEGIN
set @.string = 'use ' + @.dbname + ' DBCC SHOWFILESTATS'
insert into #datafilestats exec (@.string)
insert into #dbstats (dbname, FileGroupId, TotalSizeinMB, UsedSizeinMB)
select @.dbname, FileGroup1, sum(TotalExtents1)*65536.0/1048576.0,
sum(UsedExtents1)*65536.0/1048576.0
from #datafilestats group by FileGroup1
set @.string = 'use ' + @.dbname + ' update #dbstats set FileGroupName =sysfilegroups.groupname from #dbstats, sysfilegroups where
#dbstats.FileGroupId = sysfilegroups.groupid and #dbstats.dbname =''' +
@.dbname + ''''
exec (@.string)
update #dbstats set FreeSizeinMB = TotalSizeinMB - UsedSizeinMB where
dbname = @.dbname
truncate table #datafilestats
FETCH NEXT FROM dbnames_cursor INTO @.dbname
END
CLOSE dbnames_cursor
DEALLOCATE dbnames_cursor
drop table #datafilestats
select * from #dbstats
drop table #dbstats
--
--Script to calculate information about the Log Files
set nocount on
create table #LogUsageInfo
( db_name varchar(50),
log_size dec (8, 2),
log_used_percent dec (8, 2),
status dec (7, 1) )
insert #LogUsageInfo exec ('dbcc sqlperf(logspace) with no_infomsgs')
select * from #LogUsageInfo
drop table #LogUsageInfo
---
"Ivan" wrote:
> Dear all,
> Is that possible to calculate the database size and free space left by t-sql
> script?
> Ivan
>
>|||I have another new task for finding free space left.
Before, I need get the free space left in SQL 2000 databse.
Now, I need get the free space left in SQL 6.5
Need help~~~~~~~~~
Ivan
"Uri Dimant" <urid@.iscar.co.il> ¼¶¼g©ó¶l¥ó·s»D:uu74eVO8FHA.1188@.TK2MSFTNGP12.phx.gbl...
> Ivan
> sp_spaceused in the BOL
>
> "Ivan" <ivan@.microsoft.com> wrote in message
> news:%23FcO0QO8FHA.2304@.TK2MSFTNGP10.phx.gbl...
>> It had the db size and file size. However, I need the space available in
>> the file.
>> The most similar function is sp_space_used.
>> But I found the space left is not match with the one I saw in Enterprise
>> Manager.
>> Do you know how to calculate the space left in enterprise manager?
>> Ivan
>>
>> "Uri Dimant" <urid@.iscar.co.il> ¼¶¼g©ó¶l¥ó·s»D:e%23JXCgM8FHA.3752@.tk2msftngp13.phx.gbl...
>> Ivan
>> sp_helpdb 'northwind'
>> There is an article in the BOL for this subject
>>
>>
>> "Ivan" <ivan@.microsoft.com> wrote in message
>> news:ug9q%233L8FHA.1028@.TK2MSFTNGP11.phx.gbl...
>> Dear all,
>> Is that possible to calculate the database size and free space left by
>> t-sql script?
>> Ivan
>>
>>
>|||See my post below.
HTH, Jens Suessmeyer.|||Any SQL 6.5 version?
there's no sysfiles in sql 6.5
Ivan
"Jens" <Jens@.sqlserver2005.de>
'?:1132912972.019598.201170@.g47g2000cwa.googlegroups.com...
> See my post below.
> HTH, Jens Suessmeyer.
>

C# SQL-DMO ExecuteImmediate

Received by email from LineVoltageHalogen [tropicalfruitdrops@.yahoo.com]

> I am trying to run a sql script via SQL-DMO. The script just rebuilds
> some stored procs and I can get it to work, however it always runs against
> the "master" database. Could you show me how to specify which database?
> Here is what my code looks like:
> SQLDMO.SQLServer2 DMOSQLServerName = new SQLDMO.SQLServer2();
> SQLDMO.Database2 DMOPerStoreDbName = new SQLDMO.Database2();
> DMOSQLServerName.Connect(myGetConfigData.OlapServe rName,myGetConfigData.OlapUserLogin,OlapPass);
> DMOPerStoreDbName.Name =
> myConnectionData.OlapDatabaseName.ToString().Trim( );
> if (File.Exists(@."MyScript.sql"))
> {
> SR2=File.OpenText(@."MyScript.sql");
> S2=SR2.ReadLine();
> try
> {
> SQLScript = SR2.ReadToEnd();
> SR2.Close();
> DMOPerStoreDbName.ExecuteImmediate(SQLScript,SQLDM O.SQLDMO_EXEC_TYPE.SQLDMOExec_Default,
> null);
> }
>
> So as you can see the script runs but against the master database, I need
> it to run against a database I specify. Can you help?
>
> TFD

That's because you're setting the database name, instead of getting a
reference to an existing database from the server's Databases collection -
this code works for me:

SQLDMO.SQLServer2 srv = new SQLDMO.SQLServer2();
SQLDMO._Database db = new SQLDMO.Database();

srv.Name = "MyServer";
srv.LoginSecure = true;
srv.Connect(null,null,null);

db = srv.Databases.Item("MyDatabase", null);
db.ExecuteImmediate(" /* SQL or script contents go here */ ",
SQLDMO.SQLDMO_EXEC_TYPE.SQLDMOExec_Default, null);

srv.DisConnect();

SimonThank You.

TFD

bypass script component

is it possible to have the script component read X number of rows and then tell it don't read anymore, just pass this X number of rows to the destination?

Yes, sort of. Create two outputs on your script component, one for rows to keep and one to throw away. Then use the DirectRow to send them down the keep or throw away output.

You cannot stop the data flow, and prevent whatever source from reading more, you kind of filter then to an empty output.

|||

i was hoping there is a way to stop the source from reading but you've answered my question, you can't.

Thanks!

|||

Thanh Duong wrote:

i was hoping there is a way to stop the source from reading but you've answered my question, you can't.

Thanks!

Also be aware that if you use an asynch script component you don't have to divert the rows. If you don't do anything with them then they just disappear.

Performance-wise you might still be best doing this synchronously though.

All discussed here:

Select Top N in a data-flow
(http://blogs.conchango.com/jamiethomson/archive/2005/07/27/1877.aspx)

And there's a follow-up article that compares the two approaches in terms of performance that was published in a back issue of SQL Server Standard. Unfortunately there's no list of back issues on their website so I don't know which issue it was in.

-Jamie

BulkLoad Problem

Anyone know why this will not work... when I run the script it just inserts
one blank row... No Data !
The Script...
set objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkload.3.0")
objBL.ConnectionString = "provider=SQLOLEDB;data
source=localhost;database=SqlXmlTest;integrated security=SSPI"
objBL.ErrorLogFile = "c:\error.log"
objBL.Execute "C:\Schema1.xml", "C:\Test.xml"
set objBL=Nothing
The Schema...
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="Invoices" sql:relation="Invoices" >
<xsd:complexType>
<xsd:sequence>
<xsd:element name="Invoice" type="xsd:string" />
<xsd:element name="Supplier" type="xsd:string" />
<xsd:element name="NetAmount" type="xsd:string" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
The Data...
<?xml version="1.0"?><Invoices><Invoice id="4F10OKGRR91" Supplier="501122"
NetAmount="52.17" /><Invoice id="4F10OPPRR41" Supplier="501122"
NetAmount="100.00" /><Invoice id="4F18LXLDE51" Supplier="176696"
NetAmount="5150.00" /><Invoice id="4F18LXXCJ72" Supplier="176696"
NetAmount="1000.00" /><Invoice id="4F18O9TJM31" Supplier="176580"
NetAmount="593.60" /><Invoice id="4F21JBLHK81" Supplier="100900"
NetAmount="17990.00" /><Invoice id="4F21K2SPU51" Supplier="100900"
NetAmount="449.50" /><Invoice id="4F21OXHKI11" Supplier="503972"
NetAmount="382.10" /><Invoice id="4F22IMTLO61" Supplier="176534"
NetAmount="669.86" /><Invoice id="4F22L1JCD54" Supplier="176596"
NetAmount="150.00" /><Invoice id="4F22L1OQF72" Supplier="176542"
NetAmount="4.66" /><Invoice id="4F22L1UQJ51" Supplier="176696"
NetAmount="1200.00" /><Invoice id="4F22L1VSM43" Supplier="176542"
NetAmount="20.00" /><Invoice id="4F23NIPEO42" Supplier="176696"
NetAmount="1200.00" /><Invoice id="4F23NIYCG21" Supplier="176542"
NetAmount="100.00" /><Invoice id="4F28PTYGJ51" Supplier="176696"
NetAmount="5150.00" /><Invoice id="4F28PUOST42" Supplier="503302"
NetAmount="105.00" /><Invoice id="4G01LBMTJ81" Supplier="176696"
NetAmount="" /><Invoice id="4H20KTFGH31" Supplier="948650"
NetAmount="3083.14" /><Invoice id="4H20KTFVC32" Supplier="948650"
NetAmount="22536.02" /><Invoice id="4H20NTSPE91" Supplier="503302"
NetAmount="107.10" /><Invoice id="4H23IVGMK51" Supplier="176157"
NetAmount="40.54" /><Invoice id="4H30JEIMH21" Supplier="948650"
NetAmount="68.66" /></Invoices>
The Table...
CREATE TABLE [dbo].[Invoices] (
[Invoice] [char] (50) COLLATE Latin1_General_BIN NULL ,
[Supplier] [char] (50) COLLATE Latin1_General_BIN NULL ,
[NetAmount] [char] (50) COLLATE Latin1_General_BIN NULL
) ON [PRIMARY]
Your schema doesn't actually describe your XML. Try something like this:
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="Invoices" sql:is-constant="1" >
<xsd:complexType>
<xsd:sequence>
<xsd:element name="Invoice" sql:relation="Invoices" />
<xsd:complexType>
<xsd:attribute name="id" type="xsd:string" sql:field=
"Invoice"/>
<xsd:attribute name="Supplier" type="xsd:string" sql:field=
"Supplier"/>
<xsd:attribute name="NetAmount" type="xsd:string"
sql:field="NetAmount"/>
</xsd:complexType>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
--
Graeme Malcolm
Principal Technologist
Content Master Ltd.
www.contentmaster.com
"Rob C" <RobC@.discussions.microsoft.com> wrote in message
news:72538FD0-A75B-483F-A549-37C993C21432@.microsoft.com...
Anyone know why this will not work... when I run the script it just inserts
one blank row... No Data !
The Script...
set objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkload.3.0")
objBL.ConnectionString = "provider=SQLOLEDB;data
source=localhost;database=SqlXmlTest;integrated security=SSPI"
objBL.ErrorLogFile = "c:\error.log"
objBL.Execute "C:\Schema1.xml", "C:\Test.xml"
set objBL=Nothing
The Schema...
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="Invoices" sql:relation="Invoices" >
<xsd:complexType>
<xsd:sequence>
<xsd:element name="Invoice" type="xsd:string" />
<xsd:element name="Supplier" type="xsd:string" />
<xsd:element name="NetAmount" type="xsd:string" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
The Data...
<?xml version="1.0"?><Invoices><Invoice id="4F10OKGRR91" Supplier="501122"
NetAmount="52.17" /><Invoice id="4F10OPPRR41" Supplier="501122"
NetAmount="100.00" /><Invoice id="4F18LXLDE51" Supplier="176696"
NetAmount="5150.00" /><Invoice id="4F18LXXCJ72" Supplier="176696"
NetAmount="1000.00" /><Invoice id="4F18O9TJM31" Supplier="176580"
NetAmount="593.60" /><Invoice id="4F21JBLHK81" Supplier="100900"
NetAmount="17990.00" /><Invoice id="4F21K2SPU51" Supplier="100900"
NetAmount="449.50" /><Invoice id="4F21OXHKI11" Supplier="503972"
NetAmount="382.10" /><Invoice id="4F22IMTLO61" Supplier="176534"
NetAmount="669.86" /><Invoice id="4F22L1JCD54" Supplier="176596"
NetAmount="150.00" /><Invoice id="4F22L1OQF72" Supplier="176542"
NetAmount="4.66" /><Invoice id="4F22L1UQJ51" Supplier="176696"
NetAmount="1200.00" /><Invoice id="4F22L1VSM43" Supplier="176542"
NetAmount="20.00" /><Invoice id="4F23NIPEO42" Supplier="176696"
NetAmount="1200.00" /><Invoice id="4F23NIYCG21" Supplier="176542"
NetAmount="100.00" /><Invoice id="4F28PTYGJ51" Supplier="176696"
NetAmount="5150.00" /><Invoice id="4F28PUOST42" Supplier="503302"
NetAmount="105.00" /><Invoice id="4G01LBMTJ81" Supplier="176696"
NetAmount="" /><Invoice id="4H20KTFGH31" Supplier="948650"
NetAmount="3083.14" /><Invoice id="4H20KTFVC32" Supplier="948650"
NetAmount="22536.02" /><Invoice id="4H20NTSPE91" Supplier="503302"
NetAmount="107.10" /><Invoice id="4H23IVGMK51" Supplier="176157"
NetAmount="40.54" /><Invoice id="4H30JEIMH21" Supplier="948650"
NetAmount="68.66" /></Invoices>
The Table...
CREATE TABLE [dbo].[Invoices] (
[Invoice] [char] (50) COLLATE Latin1_General_BIN NULL ,
[Supplier] [char] (50) COLLATE Latin1_General_BIN NULL ,
[NetAmount] [char] (50) COLLATE Latin1_General_BIN NULL
) ON [PRIMARY]
|||Oops! Sorry, I meant:
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="Invoices" sql:is-constant="1">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="Invoice" sql:relation="Invoices">
<xsd:complexType>
<xsd:attribute name="id" type="xsd:string" sql:field="Invoice"
/>
<xsd:attribute name="Supplier" type="xsd:string"
sql:field="Supplier" />
<xsd:attribute name="NetAmount" type="xsd:string"
sql:field="NetAmount" />
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
Cheers,
Graeme
Graeme Malcolm
Principal Technologist
Content Master Ltd.
www.contentmaster.com
"Graeme Malcolm" <graemem_cm@.hotmail.com> wrote in message
news:u0GX%23FYpEHA.3392@.TK2MSFTNGP15.phx.gbl...
Your schema doesn't actually describe your XML. Try something like this:
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="Invoices" sql:is-constant="1" >
<xsd:complexType>
<xsd:sequence>
<xsd:element name="Invoice" sql:relation="Invoices" />
<xsd:complexType>
<xsd:attribute name="id" type="xsd:string" sql:field=
"Invoice"/>
<xsd:attribute name="Supplier" type="xsd:string" sql:field=
"Supplier"/>
<xsd:attribute name="NetAmount" type="xsd:string"
sql:field="NetAmount"/>
</xsd:complexType>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
--
Graeme Malcolm
Principal Technologist
Content Master Ltd.
www.contentmaster.com
"Rob C" <RobC@.discussions.microsoft.com> wrote in message
news:72538FD0-A75B-483F-A549-37C993C21432@.microsoft.com...
Anyone know why this will not work... when I run the script it just inserts
one blank row... No Data !
The Script...
set objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkload.3.0")
objBL.ConnectionString = "provider=SQLOLEDB;data
source=localhost;database=SqlXmlTest;integrated security=SSPI"
objBL.ErrorLogFile = "c:\error.log"
objBL.Execute "C:\Schema1.xml", "C:\Test.xml"
set objBL=Nothing
The Schema...
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="Invoices" sql:relation="Invoices" >
<xsd:complexType>
<xsd:sequence>
<xsd:element name="Invoice" type="xsd:string" />
<xsd:element name="Supplier" type="xsd:string" />
<xsd:element name="NetAmount" type="xsd:string" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
The Data...
<?xml version="1.0"?><Invoices><Invoice id="4F10OKGRR91" Supplier="501122"
NetAmount="52.17" /><Invoice id="4F10OPPRR41" Supplier="501122"
NetAmount="100.00" /><Invoice id="4F18LXLDE51" Supplier="176696"
NetAmount="5150.00" /><Invoice id="4F18LXXCJ72" Supplier="176696"
NetAmount="1000.00" /><Invoice id="4F18O9TJM31" Supplier="176580"
NetAmount="593.60" /><Invoice id="4F21JBLHK81" Supplier="100900"
NetAmount="17990.00" /><Invoice id="4F21K2SPU51" Supplier="100900"
NetAmount="449.50" /><Invoice id="4F21OXHKI11" Supplier="503972"
NetAmount="382.10" /><Invoice id="4F22IMTLO61" Supplier="176534"
NetAmount="669.86" /><Invoice id="4F22L1JCD54" Supplier="176596"
NetAmount="150.00" /><Invoice id="4F22L1OQF72" Supplier="176542"
NetAmount="4.66" /><Invoice id="4F22L1UQJ51" Supplier="176696"
NetAmount="1200.00" /><Invoice id="4F22L1VSM43" Supplier="176542"
NetAmount="20.00" /><Invoice id="4F23NIPEO42" Supplier="176696"
NetAmount="1200.00" /><Invoice id="4F23NIYCG21" Supplier="176542"
NetAmount="100.00" /><Invoice id="4F28PTYGJ51" Supplier="176696"
NetAmount="5150.00" /><Invoice id="4F28PUOST42" Supplier="503302"
NetAmount="105.00" /><Invoice id="4G01LBMTJ81" Supplier="176696"
NetAmount="" /><Invoice id="4H20KTFGH31" Supplier="948650"
NetAmount="3083.14" /><Invoice id="4H20KTFVC32" Supplier="948650"
NetAmount="22536.02" /><Invoice id="4H20NTSPE91" Supplier="503302"
NetAmount="107.10" /><Invoice id="4H23IVGMK51" Supplier="176157"
NetAmount="40.54" /><Invoice id="4H30JEIMH21" Supplier="948650"
NetAmount="68.66" /></Invoices>
The Table...
CREATE TABLE [dbo].[Invoices] (
[Invoice] [char] (50) COLLATE Latin1_General_BIN NULL ,
[Supplier] [char] (50) COLLATE Latin1_General_BIN NULL ,
[NetAmount] [char] (50) COLLATE Latin1_General_BIN NULL
) ON [PRIMARY]
|||Thank you ! I am very grateful for your help.
Cheers,
Rob
"Graeme Malcolm" <graemem_cm@.hotmail.com> wrote in message
news:OomSYYYpEHA.3716@.TK2MSFTNGP10.phx.gbl...
> Oops! Sorry, I meant:
> <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
> <xsd:element name="Invoices" sql:is-constant="1">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="Invoice" sql:relation="Invoices">
> <xsd:complexType>
> <xsd:attribute name="id" type="xsd:string" sql:field="Invoice"
> />
> <xsd:attribute name="Supplier" type="xsd:string"
> sql:field="Supplier" />
> <xsd:attribute name="NetAmount" type="xsd:string"
> sql:field="NetAmount" />
> </xsd:complexType>
> </xsd:element>
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
> </xsd:schema>
> Cheers,
> Graeme
> --
> Graeme Malcolm
> Principal Technologist
> Content Master Ltd.
> www.contentmaster.com
>
> "Graeme Malcolm" <graemem_cm@.hotmail.com> wrote in message
> news:u0GX%23FYpEHA.3392@.TK2MSFTNGP15.phx.gbl...
> Your schema doesn't actually describe your XML. Try something like this:
> <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
> <xsd:element name="Invoices" sql:is-constant="1" >
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="Invoice" sql:relation="Invoices" />
> <xsd:complexType>
> <xsd:attribute name="id" type="xsd:string" sql:field=
> "Invoice"/>
> <xsd:attribute name="Supplier" type="xsd:string" sql:field=
> "Supplier"/>
> <xsd:attribute name="NetAmount" type="xsd:string"
> sql:field="NetAmount"/>
> </xsd:complexType>
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
> </xsd:schema>
> --
> --
> Graeme Malcolm
> Principal Technologist
> Content Master Ltd.
> www.contentmaster.com
>
> "Rob C" <RobC@.discussions.microsoft.com> wrote in message
> news:72538FD0-A75B-483F-A549-37C993C21432@.microsoft.com...
> Anyone know why this will not work... when I run the script it just
> inserts
> one blank row... No Data !
> The Script...
> set objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkload.3.0")
> objBL.ConnectionString = "provider=SQLOLEDB;data
> source=localhost;database=SqlXmlTest;integrated security=SSPI"
> objBL.ErrorLogFile = "c:\error.log"
> objBL.Execute "C:\Schema1.xml", "C:\Test.xml"
> set objBL=Nothing
> The Schema...
> <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
> <xsd:element name="Invoices" sql:relation="Invoices" >
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="Invoice" type="xsd:string" />
> <xsd:element name="Supplier" type="xsd:string" />
> <xsd:element name="NetAmount" type="xsd:string" />
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
> </xsd:schema>
> The Data...
> <?xml version="1.0"?><Invoices><Invoice id="4F10OKGRR91" Supplier="501122"
> NetAmount="52.17" /><Invoice id="4F10OPPRR41" Supplier="501122"
> NetAmount="100.00" /><Invoice id="4F18LXLDE51" Supplier="176696"
> NetAmount="5150.00" /><Invoice id="4F18LXXCJ72" Supplier="176696"
> NetAmount="1000.00" /><Invoice id="4F18O9TJM31" Supplier="176580"
> NetAmount="593.60" /><Invoice id="4F21JBLHK81" Supplier="100900"
> NetAmount="17990.00" /><Invoice id="4F21K2SPU51" Supplier="100900"
> NetAmount="449.50" /><Invoice id="4F21OXHKI11" Supplier="503972"
> NetAmount="382.10" /><Invoice id="4F22IMTLO61" Supplier="176534"
> NetAmount="669.86" /><Invoice id="4F22L1JCD54" Supplier="176596"
> NetAmount="150.00" /><Invoice id="4F22L1OQF72" Supplier="176542"
> NetAmount="4.66" /><Invoice id="4F22L1UQJ51" Supplier="176696"
> NetAmount="1200.00" /><Invoice id="4F22L1VSM43" Supplier="176542"
> NetAmount="20.00" /><Invoice id="4F23NIPEO42" Supplier="176696"
> NetAmount="1200.00" /><Invoice id="4F23NIYCG21" Supplier="176542"
> NetAmount="100.00" /><Invoice id="4F28PTYGJ51" Supplier="176696"
> NetAmount="5150.00" /><Invoice id="4F28PUOST42" Supplier="503302"
> NetAmount="105.00" /><Invoice id="4G01LBMTJ81" Supplier="176696"
> NetAmount="" /><Invoice id="4H20KTFGH31" Supplier="948650"
> NetAmount="3083.14" /><Invoice id="4H20KTFVC32" Supplier="948650"
> NetAmount="22536.02" /><Invoice id="4H20NTSPE91" Supplier="503302"
> NetAmount="107.10" /><Invoice id="4H23IVGMK51" Supplier="176157"
> NetAmount="40.54" /><Invoice id="4H30JEIMH21" Supplier="948650"
> NetAmount="68.66" /></Invoices>
> The Table...
> CREATE TABLE [dbo].[Invoices] (
> [Invoice] [char] (50) COLLATE Latin1_General_BIN NULL ,
> [Supplier] [char] (50) COLLATE Latin1_General_BIN NULL ,
> [NetAmount] [char] (50) COLLATE Latin1_General_BIN NULL
> ) ON [PRIMARY]
>
>
>