Showing posts with label newbie. Show all posts
Showing posts with label newbie. Show all posts

Wednesday, March 28, 2012

Problems with SQLXML bulk load

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?

Thanks!!If your data is involved in a DataSet, try to use
SQLBulkCopy class.|||It is not. I receive the data from a process that generates the data into XML format, and I cannot change the process.|||Try also to post the question on the .Net forum, you may get the right answer quicker.|||Ok, thanks!

Friday, March 9, 2012

Problems with Dynamic Query from Newbie

Thanks for the help thus far. I'm still having problems
Below are the error message.
I can run the print statement noted below and it works.
I can also just do the select statement but placing the insert into breaks
the procedure.
Questions.
1. What am I doing wrong.
2. I need to output the @.TempVS variable to use. How is that done?
3. Can I do a Select * from @.VStemp after the table has been inserted?
4/ Any other way to optimize this code?
The query below works when I substitute values
(Insert Into @.Level3
Select Distinct patNo, DateDrawn, Sort,TestnameDisplay,
Result, Units, abn, Range
FROM PatLabVal2 where PatNo = @.level and (Result <> 'DNR' ) and Result
<>''
and (DateDrawn > (GetDate() - @.Level2)
)
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'Insert'.
Server: Msg 137, Level 15, State 1, Line 2
Must declare the variable '@.Level3'.
Server: Msg 170, Level 15, State 1, Line 7
Line 7: Incorrect syntax near ')'.
/****** Object: Table [dbo].[TempVsTest] Script Date: 5/7/2006 3:02:18
PM ******/
if not exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[TempVSTEst]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [TempVSTest] (
[patNo] [int] NULL ,
[DateDrawn] [datetime] NULL ,
[Sort] [int] NULL ,
[TestnameDisplay] [varchar] (50) NULL ,
[Result] [varchar] (100) NULL ,
[Units] [varchar] (50) NULL ,
[abn] [varchar] (50) NULL ,
[Range] [varchar] (50) NULL
) ON [PRIMARY]
END
Insert Into TempVSTest
(patNo, DateDrawn, Sort, TestnameDisplay, Result, Units,abn, Range)
values
(240, '1/1/2005', 1,'Cholesterol', '300', 'mg/dl','H','<200')
GO
/* Test script */
DECLARE
@.TempVS varchar(20),
@.table Varchar(20)
-- @.PatNo int,
-- @.Type int,
-- @.Range int
-- @.sqlStmt nvarchar(500)
Set @.table = 'TempVSTest'
--Set @.Type = 0
--Set @.Range = 36000
--Make unique Global Temp Table Name
set @.TempVS = '##TempVS' + cast(datepart(minute, getdate()) as
varchar(3)) + cast(datepart(millisecond, getdate()) as varchar(3))
--print @.TempVS
EXEC ('SELECT * INTO ' + @.TempVS + ' FROM ' + @.table + ' WHERE 1=2')
--EXEC ('Select * from '+ @.TempVS)
--IF @.Type = 0 -- All
--BEGIN
Declare @.sqlStmt nvarchar(500)
Declare @.Param1 NVARCHAR(500)
Declare @.Param2 NVARCHAR(500)
Declare @.PatNo int
Declare @.Range int
set @.PatNO = 1
Set @.Range = 120
SET @.sqlStmt = N'(Insert Into @.Level3
Select Distinct patNo, DateDrawn, Sort,TestnameDisplay,
Result, Units, abn, Range
FROM PatLabVal2 where PatNo = @.level and (Result <> ''DNR'' ) and Result
<>''''
and (DateDrawn > (GetDate() - @.Level2))
)'
print @.sqlStmt
SET @.Param1 = N'@.level int, @.level2 int, @.Level3 varchar(20)'
EXECUTE sp_Executesql @.sqlStmt,
@.Param1,
@.level = @.PatNO ,
@.level2 = @.Range,
@.level3 = @.TempVSHi
I am not sure what the braces are at the start and end of the statement! You
can not use a variable as the table name in an insert statement, you will
need to build up the string in the same way as you use @.TempVS in EXEC
('SELECT * INTO ' + @.TempVS + ' FROM ' + @.table + ' WHERE 1=2')
It is not clear why you are going to the trouble of using a global temporary
table, your code should work with a temporary table, and therefore remove th
e
need to substituting the table name.
John
"Stephen K. Miyasato" wrote:

> Thanks for the help thus far. I'm still having problems
> Below are the error message.
> I can run the print statement noted below and it works.
> I can also just do the select statement but placing the insert into breaks
> the procedure.
> Questions.
> 1. What am I doing wrong.
> 2. I need to output the @.TempVS variable to use. How is that done?
> 3. Can I do a Select * from @.VStemp after the table has been inserted?
> 4/ Any other way to optimize this code?
> The query below works when I substitute values
> (Insert Into @.Level3
> Select Distinct patNo, DateDrawn, Sort,TestnameDisplay,
> Result, Units, abn, Range
> FROM PatLabVal2 where PatNo = @.level and (Result <> 'DNR' ) and Result
> <>''
> and (DateDrawn > (GetDate() - @.Level2)
> )
> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'Insert'.
> Server: Msg 137, Level 15, State 1, Line 2
> Must declare the variable '@.Level3'.
> Server: Msg 170, Level 15, State 1, Line 7
> Line 7: Incorrect syntax near ')'.
>
> /****** Object: Table [dbo].[TempVsTest] Script Date: 5/7/2006 3:02:18
> PM ******/
> if not exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[TempVSTEst]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> BEGIN
> CREATE TABLE [TempVSTest] (
> [patNo] [int] NULL ,
> [DateDrawn] [datetime] NULL ,
> [Sort] [int] NULL ,
> [TestnameDisplay] [varchar] (50) NULL ,
> [Result] [varchar] (100) NULL ,
> [Units] [varchar] (50) NULL ,
> [abn] [varchar] (50) NULL ,
> [Range] [varchar] (50) NULL
> ) ON [PRIMARY]
> END
> Insert Into TempVSTest
> (patNo, DateDrawn, Sort, TestnameDisplay, Result, Units,abn, Range)
> values
> (240, '1/1/2005', 1,'Cholesterol', '300', 'mg/dl','H','<200')
> GO
> /* Test script */
> DECLARE
> @.TempVS varchar(20),
> @.table Varchar(20)
> -- @.PatNo int,
> -- @.Type int,
> -- @.Range int
> -- @.sqlStmt nvarchar(500)
> Set @.table = 'TempVSTest'
> --Set @.Type = 0
> --Set @.Range = 36000
> --Make unique Global Temp Table Name
> set @.TempVS = '##TempVS' + cast(datepart(minute, getdate()) as
> varchar(3)) + cast(datepart(millisecond, getdate()) as varchar(3))
> --print @.TempVS
> EXEC ('SELECT * INTO ' + @.TempVS + ' FROM ' + @.table + ' WHERE 1=2')
> --EXEC ('Select * from '+ @.TempVS)
> --IF @.Type = 0 -- All
> --BEGIN
>
> Declare @.sqlStmt nvarchar(500)
> Declare @.Param1 NVARCHAR(500)
> Declare @.Param2 NVARCHAR(500)
> Declare @.PatNo int
> Declare @.Range int
> set @.PatNO = 1
> Set @.Range = 120
> SET @.sqlStmt = N'(Insert Into @.Level3
> Select Distinct patNo, DateDrawn, Sort,TestnameDisplay,
> Result, Units, abn, Range
> FROM PatLabVal2 where PatNo = @.level and (Result <> ''DNR'' ) and Result
> <>''''
> and (DateDrawn > (GetDate() - @.Level2))
> )'
> print @.sqlStmt
> SET @.Param1 = N'@.level int, @.level2 int, @.Level3 varchar(20)'
> EXECUTE sp_Executesql @.sqlStmt,
> @.Param1,
> @.level = @.PatNO ,
> @.level2 = @.Range,
> @.level3 = @.TempVS
>
>|||> It is not clear why you are going to the trouble of using a global
> temporary
> table, your code should work with a temporary table, and therefore remove
> the
> need to substituting the table name.
I need the table to build a array similar to crosstabs. I then had problems
with multi-user use and other persons data coming into the tables. I thought
that using global temp tables would be the answer to this. Any comments?
Thanks
Stephen K. Miyasato
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:881223D1-5B5E-45BB-B154-940BF5A4DE2E@.microsoft.com...
> Hi
> I am not sure what the braces are at the start and end of the statement!
> You
> can not use a variable as the table name in an insert statement, you will
> need to build up the string in the same way as you use @.TempVS in EXEC
> ('SELECT * INTO ' + @.TempVS + ' FROM ' + @.table + ' WHERE 1=2')
> It is not clear why you are going to the trouble of using a global
> temporary
> table, your code should work with a temporary table, and therefore remove
> the
> need to substituting the table name.
>
> John
>
> "Stephen K. Miyasato" wrote:
>|||Stephen K. Miyasato (miyasat@.flex.com) writes:
> I need the table to build a array similar to crosstabs. I then had
> problems with multi-user use and other persons data coming into the
> tables. I thought that using global temp tables would be the answer to
> this. Any comments?
Use a regular temp table instead. A plain #temp table is visible only
to the process that created it.
Global temp tables are very messy to work with. And I have a strong
feeling that it was a design mistake that brought them into the product.
I've yet found any useful application for them.
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|||Hi
For static crosstabs you should not need to use a temporary table
http://msdn.microsoft.com/library/d...r />
_04j7.asp
or use the new PIVOT function in SQL 2005, for dynamic crosstabs you may wan
t
to look at Itziks articles on crosstabs in SQL Server magazine
http://www.sqlmag.com/Articles/Arti...5608/15608.html
John
"Stephen K. Miyasato" wrote:

> I need the table to build a array similar to crosstabs. I then had problem
s
> with multi-user use and other persons data coming into the tables. I thoug
ht
> that using global temp tables would be the answer to this. Any comments?
> Thanks
> Stephen K. Miyasato
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:881223D1-5B5E-45BB-B154-940BF5A4DE2E@.microsoft.com...
>
>

Problems with dimensions and measures

hi i a newbie in the world of cubes and i have a problems to show a report of quantity of products sales for year

this show like this

1997 62

1998 62

1999 62

2000 62

and only sale producs sales in 1999 the right thing is

1997 0

1998 0

1999 62

2000 0

If you are using Analysis Services 2005 this seems like a problem with the relation between your product dimension and your fact table/measure group. Check the dimension usage tab in the cube designer in Business Intelligence Development Studio.

When dimensions and fact tables are not related correctly Analysis Services will show the All member value for the unrelated dimension.

HTH

Thomas Ivarsson

|||thanks for you help..