Problem with bulk insert

Hi all,
I am newbie in all the stuff about xml importing into sql server.
What I try to do is simple. It is take an xml file and drop it into a
table. I am using VS2005, SQLXML 4.0 and SQL Server 2000 (I think
there is no problem of compatibility)
When I run my program using the SQLXMLBulkLoad4Class class,
everythings seems to run perfect and there is no errors. But when I
check my DB there isnt any record inserted.
My schema is:
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="table1">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="ele1" type="xsd:string"/>
<xsd:element name="ele2" type="xsd:string"/>
<xsd:element name="ele3" type="xsd:string"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
My xml:
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<ENGROLE>
<EROLE>
<ele1>dieg01p</ele1>
<ele2>IE01</ele2>
<ele3>IEL01</ele3>
</EROLE>
Hello,
This happens because your xml doesn't match the schema definition.
You have to update the schema as follows:
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="ENGROLE" sql:isconstant="true">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="EROLE" sql:relation="Table1">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="ele1" type="xsd:string"/>
<xsd:element name="ele2" type="xsd:string"/>
<xsd:element name="ele3" type="xsd:string"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
I hope this helps.
Regards,
Monica Frintu
"VicToro" wrote:

> Hi all,
> I am newbie in all the stuff about xml importing into sql server.
> What I try to do is simple. It is take an xml file and drop it into a
> table. I am using VS2005, SQLXML 4.0 and SQL Server 2000 (I think
> there is no problem of compatibility)
> When I run my program using the SQLXMLBulkLoad4Class class,
> everythings seems to run perfect and there is no errors. But when I
> check my DB there isnt any record inserted.
> My schema is:
> <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
> <xsd:element name="table1">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="ele1" type="xsd:string"/>
> <xsd:element name="ele2" type="xsd:string"/>
> <xsd:element name="ele3" type="xsd:string"/>
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
> </xsd:schema>
>
> My xml:
> <?xml version="1.0" encoding="UTF-8" standalone="no"?>
> <ENGROLE>
> <EROLE>
> <ele1>dieg01p</ele1>
> <ele2>IE01</ele2>
> <ele3>IEL01</ele3>
> </EROLE>
> .
> .
> .
> </ENGROLE>
>
> and my table definition where I try to insert:
> CREATE TABLE [dbo].[table1](
> [ele1] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> [ele2] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> [ele3] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> ) ON [PRIMARY]
> As you see it is very simple, but I cannot get it work. Can anyone
> give a hand?
> Thank!!
>

Problem with bulk insert

Hi all,
I am newbie in all the stuff about xml importing into sql server.
What I try to do is simple. It is take an xml file and drop it into a
table. I am using VS2005, SQLXML 4.0 and SQL Server 2000 (I think
there is no problem of compatibility)
When I run my program using the SQLXMLBulkLoad4Class class,
everythings seems to run perfect and there is no errors. But when I
check my DB there isnt any record inserted.
My schema is:
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="table1">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="ele1" type="xsd:string"/>
<xsd:element name="ele2" type="xsd:string"/>
<xsd:element name="ele3" type="xsd:string"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
My xml:
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<ENGROLE>
<EROLE>
<ele1>dieg01p</ele1>
<ele2>IE01</ele2>
<ele3>IEL01</ele3>
</EROLE>Hello,
This happens because your xml doesn't match the schema definition.
You have to update the schema as follows:
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="ENGROLE" sql:isconstant="true">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="EROLE" sql:relation="Table1">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="ele1" type="xsd:string"/>
<xsd:element name="ele2" type="xsd:string"/>
<xsd:element name="ele3" type="xsd:string"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
I hope this helps.
Regards,
--
Monica Frintu
"VicToro" wrote:

> Hi all,
> I am newbie in all the stuff about xml importing into sql server.
> What I try to do is simple. It is take an xml file and drop it into a
> table. I am using VS2005, SQLXML 4.0 and SQL Server 2000 (I think
> there is no problem of compatibility)
> When I run my program using the SQLXMLBulkLoad4Class class,
> everythings seems to run perfect and there is no errors. But when I
> check my DB there isnt any record inserted.
> My schema is:
> <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
> <xsd:element name="table1">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="ele1" type="xsd:string"/>
> <xsd:element name="ele2" type="xsd:string"/>
> <xsd:element name="ele3" type="xsd:string"/>
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
> </xsd:schema>
>
> My xml:
> <?xml version="1.0" encoding="UTF-8" standalone="no"?>
> <ENGROLE>
> <EROLE>
> <ele1>dieg01p</ele1>
> <ele2>IE01</ele2>
> <ele3>IEL01</ele3>
> </EROLE>
> .
> .
> .
> </ENGROLE>
>
> and my table definition where I try to insert:
> CREATE TABLE [dbo].[table1](
> [ele1] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> [ele2] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> [ele3] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> ) ON [PRIMARY]
> As you see it is very simple, but I cannot get it work. Can anyone
> give a hand?
> Thank!!
>

Problem with ALTER TABLE in stored procedure

Hello,
I am trying to drop a column from a table with a stored procedure that
chacks if the column exists before droping it. However, I am having trouble
with passing the Table Name and Column Name to the ALTER TABLE command. The
code is:
CREATE PROCEDURE usp_DeleteColumnEx
@.TableName varchar(200),
@.ColumnName varchar(200)
AS
IF EXISTS
(SELECT * FROM SysObjects O INNER JOIN SysColumns C ON O.ID=C.ID
WHERE ObjectProperty(O.ID,'IsUserTable')= 1
AND O.Name = @.TableName
AND C.Name = @.ColumnName)
ALTER TABLE @.TableName DROP COLUMN @.ColumnName
When I try to execute this the server returns an error: "Incorrect syntax
near '@.TableName'". I am assuming that I cannot just pass the table name as
a
parameter. Is there a another way to do this?
Thank you for your help.
Daniel> ALTER TABLE @.TableName DROP COLUMN @.ColumnName
You can't do this - SQL Server has to know what objects you're talking
about. The best you could do is dynamic SQL, please read:
http://www.sommarskog.se/dynamic_sql.html
On a side note, why on earth are you changing your table structure on the
fly like this? Sounds very dangerous and suspicious, but not in the Austin
Powers way.|||"Daniel" <Daniel@.discussions.microsoft.com> wrote in message
news:9A9DCE9F-191A-4E76-AD5A-FC6E7A316E61@.microsoft.com...
> Hello,
> I am trying to drop a column from a table with a stored procedure that
> chacks if the column exists before droping it. However, I am having
> trouble
> with passing the Table Name and Column Name to the ALTER TABLE command.
> The
> code is:
> CREATE PROCEDURE usp_DeleteColumnEx
> @.TableName varchar(200),
> @.ColumnName varchar(200)
> AS
> IF EXISTS
> (SELECT * FROM SysObjects O INNER JOIN SysColumns C ON O.ID=C.ID
> WHERE ObjectProperty(O.ID,'IsUserTable')= 1
> AND O.Name = @.TableName
> AND C.Name = @.ColumnName)
> ALTER TABLE @.TableName DROP COLUMN @.ColumnName
> When I try to execute this the server returns an error: "Incorrect syntax
> near '@.TableName'". I am assuming that I cannot just pass the table name
> as a
> parameter. Is there a another way to do this?
> Thank you for your help.
> Daniel
Why would you want a proc that drops columns? Help me understand what you
are trying to achieve.
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
--|||Aaron and David,
Thank you for your replies. The reason I need to alter the table dynamically
is
that I have a table where each record is corresponds to one employee, each
column represents a different skill involved in the employees' everyday work
.
In each column every employee have assigned a number corresponding to their
skill level. According to their skills they are assigned to their work
stations, which is VERY important for the management.
This is a basically how the table looks like:
Create table SkillsCheck (
emp_id int,
skill1 int,
skill2 int,
skill3 int,
skill4 int)
The problem is that the company has the option to change what skills they
want to track, so when that happens I have to drop or add a column to the
table to reflect the current situation. For example, they might decide that
they do not want to track skill3 anymore, but want to track skill5, so my
program needs to drop skill3 column and add skill5 column. I am working with
VB.NET and I can execute the ALTER TABLE statement from my code, but I think
that it would be better to use stored procedure that check if the column
exists before adding or droping.
After considering the situation I decided that would be easier to alter the
table instead of keeping the information in multiple tables. If you have
encountered a similiar scenario maybe you can give me an advice for more
efficient approach to this problem.
Thank you for your help,
Daniel
"Daniel" wrote:

> Hello,
> I am trying to drop a column from a table with a stored procedure that
> chacks if the column exists before droping it. However, I am having troubl
e
> with passing the Table Name and Column Name to the ALTER TABLE command. Th
e
> code is:
> CREATE PROCEDURE usp_DeleteColumnEx
> @.TableName varchar(200),
> @.ColumnName varchar(200)
> AS
> IF EXISTS
> (SELECT * FROM SysObjects O INNER JOIN SysColumns C ON O.ID=C.ID
> WHERE ObjectProperty(O.ID,'IsUserTable')= 1
> AND O.Name = @.TableName
> AND C.Name = @.ColumnName)
> ALTER TABLE @.TableName DROP COLUMN @.ColumnName
> When I try to execute this the server returns an error: "Incorrect syntax
> near '@.TableName'". I am assuming that I cannot just pass the table name a
s a
> parameter. Is there a another way to do this?
> Thank you for your help.
> Daniel|||Daniel wrote:
> Aaron and David,
> Thank you for your replies. The reason I need to alter the table dynamical
ly
> is
> that I have a table where each record is corresponds to one employee, each
> column represents a different skill involved in the employees' everyday wo
rk.
> In each column every employee have assigned a number corresponding to thei
r
> skill level. According to their skills they are assigned to their work
> stations, which is VERY important for the management.
> This is a basically how the table looks like:
> Create table SkillsCheck (
> emp_id int,
> skill1 int,
> skill2 int,
> skill3 int,
> skill4 int)
> The problem is that the company has the option to change what skills they
> want to track, so when that happens I have to drop or add a column to the
> table to reflect the current situation. For example, they might decide tha
t
> they do not want to track skill3 anymore, but want to track skill5, so my
> program needs to drop skill3 column and add skill5 column. I am working wi
th
> VB.NET and I can execute the ALTER TABLE statement from my code, but I thi
nk
> that it would be better to use stored procedure that check if the column
> exists before adding or droping.
> After considering the situation I decided that would be easier to alter th
e
> table instead of keeping the information in multiple tables. If you have
> encountered a similiar scenario maybe you can give me an advice for more
> efficient approach to this problem.
> Thank you for your help,
> Daniel
>
> "Daniel" wrote:
>
The type of relationship between employees and skills is called
many-to-many. The textbook solution looks like this:
CREATE TABLE EmployeeSkills
(emp_id INTEGER NOT NULL
REFERENCES Employees (emp_id),
skill_code INTEGER NOT NULL
REFERENCES Skills (skill_code),
PRIMARY KEY (emp_id,skill_code));
This has huge advantages over the design that you proposed: It can
support any number of skills. No redundancy. No nulls required. Joins
and queries always reference just one skills column. The table
structure doesn't ever need to change (!).
I recommend you read up and study some relational design theory. Most
database architects would consider your suggestion as a serious design
flaw. To appreciate why you need to understand principles like
normalization and the normal forms, which are some of the tools we use
to design effective databases.
Hope this helps.
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
--|||As you stated, it would take a schema changed to accommodate future
requirements. Such as that, you're better off by creating a skills table to
store the skills. Any changes will just be a simple delete from the tables.
Also, this approach will not incur a table lock like you have when doing
schema update.
e.g.
create table skills(skillid int primary key, skillname sysname)
create table skillscheck(empid int primary key, skillid int foreign key
references skills(skillid))
-oj
"Daniel" <Daniel@.discussions.microsoft.com> wrote in message
news:3505642D-711F-43F6-8D73-DA004C791B34@.microsoft.com...
> Aaron and David,
> Thank you for your replies. The reason I need to alter the table
> dynamically
> is
> that I have a table where each record is corresponds to one employee, each
> column represents a different skill involved in the employees' everyday
> work.
> In each column every employee have assigned a number corresponding to
> their
> skill level. According to their skills they are assigned to their work
> stations, which is VERY important for the management.
> This is a basically how the table looks like:
> Create table SkillsCheck (
> emp_id int,
> skill1 int,
> skill2 int,
> skill3 int,
> skill4 int)
> The problem is that the company has the option to change what skills they
> want to track, so when that happens I have to drop or add a column to the
> table to reflect the current situation. For example, they might decide
> that
> they do not want to track skill3 anymore, but want to track skill5, so my
> program needs to drop skill3 column and add skill5 column. I am working
> with
> VB.NET and I can execute the ALTER TABLE statement from my code, but I
> think
> that it would be better to use stored procedure that check if the column
> exists before adding or droping.
> After considering the situation I decided that would be easier to alter
> the
> table instead of keeping the information in multiple tables. If you have
> encountered a similiar scenario maybe you can give me an advice for more
> efficient approach to this problem.
> Thank you for your help,
> Daniel
>
> "Daniel" wrote:
>|||Correction. Don't forget to add a column for the skill level:
CREATE TABLE EmployeeSkills
(emp_id INTEGER NOT NULL
REFERENCES Employees (emp_id),
skill_code INTEGER NOT NULL
REFERENCES Skills (skill_code),
skill_level INTEGER NOT NULL
CHECK (skill_level BETWEEN 0 AND 10),
PRIMARY KEY (emp_id,skill_code));
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
--|||Thanks David,
I will follow your advice and will read about many-to-many relationships.
After reading more on Dynamic SQL, looks like it is not the best choice.
Thanks again,
Daniel
"David Portas" wrote:

> Correction. Don't forget to add a column for the skill level:
> CREATE TABLE EmployeeSkills
> (emp_id INTEGER NOT NULL
> REFERENCES Employees (emp_id),
> skill_code INTEGER NOT NULL
> REFERENCES Skills (skill_code),
> skill_level INTEGER NOT NULL
> CHECK (skill_level BETWEEN 0 AND 10),
> PRIMARY KEY (emp_id,skill_code));
> --
> 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
> --
>|||OJ,
Thanks you your reply. Looks like I need to revise the database design and
reorganize the tables differently.
Best Regards,
Daniel
"oj" wrote:

> As you stated, it would take a schema changed to accommodate future
> requirements. Such as that, you're better off by creating a skills table t
o
> store the skills. Any changes will just be a simple delete from the tables
.
> Also, this approach will not incur a table lock like you have when doing
> schema update.
> e.g.
> create table skills(skillid int primary key, skillname sysname)
> create table skillscheck(empid int primary key, skillid int foreign key
> references skills(skillid))
>
> --
> -oj
>
> "Daniel" <Daniel@.discussions.microsoft.com> wrote in message
> news:3505642D-711F-43F6-8D73-DA004C791B34@.microsoft.com...
>
>

Problem with <Select a value> in report parameters

Hello, Everyone

please, could you make clear how can I avoid appearence of <select a value> in drop downs at all.

thanks

not sure if this will work, but could you set a default value of a zero length string or null value?|||

steveareno wrote:

not sure if this will work, but could you set a default value of a zero length string or null value?

It will definately work if you automatically select the first (or any) item in the list.

sluggy

|||

but doesn't work =)

I have a next drop down with predefined values:

Show all = -2
Local = 0
Another one = -1

I make default value = -2
and when I open report via browser I get this drop down with prepopulated values and currently chosen is Show all

BUT also I can choose <select a value>

P.S. I afraid that I should write custom code for UI of Reporting Services

Thanks, lads

Problem whit Logshipping Error 952

Hi,

I have a problem with Log Shipping.

I implement Log Shipping in 2 servers with Sql 2005, but when i drop log shipping at the first server the second server locks the DB. I can not copy, drop or select any table, object...

The second server displays the following messaje

Error 952 - Database '%.*ls' is in transition. Try the statement later.

Any help you can give to me to the resolve this cuestion?

Thanks

>> " i drop log shipping at the first server"

Did you do that from the LS UI or running the LS stored precedures ?

thanks

Yunwen

Problem when passing parameter to Execute SQL Task

Hi!

I have a execute sql task to create and drop logins. I want to create/drop the ASPNET login, but I need to pass the domain using a parameter. So I mapped a parameter:

Variable name: User::serverName

Direction: Input

DataType: Varchar

Parameter Name:0

and the sql is the following:

CREATE LOGIN [?\ASPNET] FROM WINDOWS

But I get the error:

Executing the query "CREATE LOGIN [?\ASPNET] FROM WINDOWS failed with the following error: "Windows NT user or group '?\ASPNET' not found. Check the name again.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

What am I doing wrong?

Thank you!

Did you set the "BypassPrepare" to True on the execute task? That gets me every time I use variables.

If you don't set that, it treats the ? as a literal and fails.|||

Hi!

Yes, BypassPrepare is set to true...

Thank you!

|||Setup another variable to hold your full string so that in your SQL you only have the ? placeholder instead of ?/ASPNET.

So, in my test, I created a new variable, set its expression to concatenate the User::ServerName variable and the /ASPNET string. Set the new variable to EvaluateAsExpression and then use it in the parameter mapping of the ExecuteSQL task.

Does that do what you're looking for?|||

Hi!

I tried what you suggest and I still have the error... Then I tried something simpler, CREATE LOGIN ? WITH PASSWORD = 'lalala', and I got the following error:

Executing the query "CREATE LOGIN ? WITH PASSWORD = 'lalala'" failed with the following error: "Syntax error, permission violation, or other nonspecific error". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

And if I change it to CREATE LOGIN test WITH PASSWORD = 'lalala' it works fine, so I guess it's not a connection problem. I guess I'm missing something silly!

Thank you!

|||What type of connection are you using? OLE DB, ADO, ADO.NET, ODBC, etc..

The connection type that you use will dictate how you name the parameter and then use a parameter in your SQL.|||http://sqljunkies.com/WebLog/knight_reign/archive/2005/10/05/17016.aspx|||

I'm using an OLE DB connection, and it seems the parameter name is ok... Posting the code would help?

Thank you!

|||I have a variable (User::Test) set as a string with a value of "P0160".

In the Execute SQL Task editor, my sql statement is:
update client set clientdesc = 'testing' where client = ?

Then, in the parameter mapping section, I added a variable and selected User::Test as the variable name. Its data type is VARCHAR. The Parameter Name is simply, 0.

That's it. I run the task and it works correctly.|||It works now. Thank you very much for you help!