problem with CAST and CONVERT in SQL Server2000 converting decimal places from 4 to 2

All of my currency columns are only storing 2 decimal places when I insert into the database but when I pull out the data with a SELECT statement, I always get 4 decimal places instead of the 2 that were inserted.

For example:

Database Price SELECT statement Price

100.56 100.5600

I have tried to use the CAST and/or CONVERT commands but I cannot get the output to come out as 100.56. Has anyone had a similar problem?

Thanks

Look at the STR() function in Books Online.

STR(FloatExpression,LengthOverall,PlacesToRightOfDecimal)

|||Usually this type of formatting is done on the client side. You can use any of the string.format functions.|||You application is having a precision problem, it happens in SQL Server, there are two solutions a third party driver that on the TDS(Tabular Data Stream) level correct it or do it the cheap and free way use Decimal or Numeric instead of money and your problems will go away. Numeric is bigger than money but you will not get that problem. Hope this helps.|||I have tried to use the decimal datatype convert but it is rounding up or down and I am trying to show an amount, i.e. price. I just need something that will allow me to show the value as it exists in the database. When I view the data in the table, I only see the two decimal places.|||

Try the format in these links. Hope this helps

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconformattingnumericdataforspecificculture.asp

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconcustomnumericformatstringsoutputexample.asp

Problem with Case statement

not sure what's missing here but it's not liking my select with parameter check. The syntax is wrong here but not sure what.

CASE @.BKYChapter

WHEN 7 THEN

Insert into dbo.E_Final

(TransactionDate, TransactionTime, AccountNumber, TransactionCode, FieldCode, NewValue, InternalExternalFlag, RecovererCode, AS_400_UserID, ProductLoanTypeCode, NotUsed)

Select GetDate(), GetDate(), @.AcctNumber, 'MT', fc.R_FieldCode, 'BK07', 'I', ' ', ' ', ' ', ' ' FROM dbo.E_Field_FieldCode

INNER JOIN dbo.E_Field_FieldCode fc ON fc.R_FieldName = 'RecoveryCode' GROUP BY fc.R_FieldCode

Insert into dbo.E_Final

(TransactionDate, TransactionTime, AccountNumber, TransactionCode, FieldCode, NewValue, InternalExternalFlag, RecovererCode, AS_400_UserID, ProductLoanTypeCode, NotUsed)

Select GetDate(), GetDate(), @.AcctNumber, 'MT', fc.R_FieldCode, '992', 'I', ' ', ' ', ' ', ' ' FROM dbo.E_Field_FieldCode

INNER JOIN dbo.E_Field_FieldCode fc ON fc.R_FieldName = 'StatusCode' GROUP BY fc.R_FieldCode

WHEN 13 THEN

Insert into dbo.E_Final

(TransactionDate, TransactionTime, AccountNumber, TransactionCode, FieldCode, NewValue, InternalExternalFlag, RecovererCode, AS_400_UserID, ProductLoanTypeCode, NotUsed)

Select GetDate(), GetDate(), @.AcctNumber, 'MT', fc.R_FieldCode, 'BK13', 'I', ' ', ' ', ' ', ' ' FROM dbo.E_Field_FieldCode

INNER JOIN dbo.E_Field_FieldCode fc ON fc.R_FieldName = 'RecoveryCode' GROUP BY fc.R_FieldCode

Insert into dbo.E_Final

(TransactionDate, TransactionTime, AccountNumber, TransactionCode, FieldCode, NewValue, InternalExternalFlag, RecovererCode, AS_400_UserID, ProductLoanTypeCode, NotUsed)

Select GetDate(), GetDate(), @.AcctNumber, 'MT', fc.R_FieldCode, '993', 'I', ' ', ' ', ' ', ' ' FROM dbo.E_Field_FieldCode

INNER JOIN dbo.E_Field_FieldCode fc ON fc.R_FieldName = 'StatusCode' GROUP BY fc.R_FieldCode

[

ELSE SET @.Error = 'Chapter not valid'

]

END

CASE is not a control of flow statement. It is an expression. You have to use if...else to perform control of flow.|||thanks much!

Problem with case statement

With the syntax below, why is field1a not "A" if field1 does not
contain "_"

SELECT Field1a = CASE WHEN (field1 LIKE '%_%') THEN (charindex('_',
field1)) ELSE 'A' END, field1
FROM [Table1]<chudson007@.hotmail.com> wrote in message
news:1125938525.701908.180610@.f14g2000cwb.googlegr oups.com...
> With the syntax below, why is field1a not "A" if field1 does not
> contain "_"
>
> SELECT Field1a = CASE WHEN (field1 LIKE '%_%') THEN (charindex('_',
> field1)) ELSE 'A' END, field1
> FROM [Table1]

See LIKE and "Pattern Matching in Search Conditions" in Books Online - an
underscore is a wildcard for any single character, so you need to escape it
for a literal match:

LIKE '%[_]%'

Another issue is that CASE is an expression, so it can only return a single
data type - as you've written it, it could return either an int or a char,
so you would get a data type conversion error. See "Result Types" under CASE
in Books Online - you'll need to decide on a single return type, or perhaps
CAST the integer to a character type:

SELECT
Field1a = CASE
WHEN (field1 LIKE '%[_]%') THEN cast((charindex('_',field1)) as char(2))
ELSE 'A' END,
field1
FROM [Table1]

Simon|||cheers simon|||(chudson007@.hotmail.com) writes:
> With the syntax below, why is field1a not "A" if field1 does not
> contain "_"
>
> SELECT Field1a = CASE WHEN (field1 LIKE '%_%') THEN (charindex('_',
> field1)) ELSE 'A' END, field1
> FROM [Table1]

In SQL _ is a wildcard for exactly one occurrance of one characater. Thus
%_% matches anything but the empty string. Change to %[_]% to get what you
want.

...by the way, CASE is an expression, not a statement, in SQL.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql

Problem with Bulk insert

Hello,
I use the statement "Bulk insert" to insert data from a file to one table on
SQL Server.
I do like this:
bulk insert MyBase.dbo.Clients from 'D:\DataMyBase\TableClients.txt'
wiht (FieldSeparator = ',', Rowseparator='\n')
Table Clients(num int, name char(50), comment varchar(max))
But im my file "TableClients.txt", I have one row like this:
7,Peter,'Hello,World"\n
You can see, I want to use "," in my column comment but I have this message
error:
Type mismatch...
It's because I use "," in my comment and for statement "bulk insert", it's a
field terminator and then I have a number of colums different from my table
"Client".
How Can I resolve this problem I want to use "," in column "comment"?
Thanks!I'm losing anything, I dont' see anywhere 'fieldseparator' or 'rowseparator
'
options.
"bubixx" wrote:

> Hello,
> I use the statement "Bulk insert" to insert data from a file to one table
on
> SQL Server.
> I do like this:
> bulk insert MyBase.dbo.Clients from 'D:\DataMyBase\TableClients.txt'
> wiht (FieldSeparator = ',', Rowseparator='\n')
> Table Clients(num int, name char(50), comment varchar(max))
> But im my file "TableClients.txt", I have one row like this:
> 7,Peter,'Hello,World"\n
> You can see, I want to use "," in my column comment but I have this messag
e
> error:
> Type mismatch...
> It's because I use "," in my comment and for statement "bulk insert", it's
a
> field terminator and then I have a number of colums different from my tabl
e
> "Client".
> How Can I resolve this problem I want to use "," in column "comment"?
> Thanks!
>|||You can define what is your fieldsepartore in your file txt and what is your
rowseparator.
In my case, fieldseparator=','
rowseparator='\n'
But my question, it's: can we use ',' in a string in one line of filetext'
Consult the first message which I sent for more details
"bubixx" wrote:

> Hello,
> I use the statement "Bulk insert" to insert data from a file to one table
on
> SQL Server.
> I do like this:
> bulk insert MyBase.dbo.Clients from 'D:\DataMyBase\TableClients.txt'
> wiht (FieldSeparator = ',', Rowseparator='\n')
> Table Clients(num int, name char(50), comment varchar(max))
> But im my file "TableClients.txt", I have one row like this:
> 7,Peter,'Hello,World"\n
> You can see, I want to use "," in my column comment but I have this messag
e
> error:
> Type mismatch...
> It's because I use "," in my comment and for statement "bulk insert", it's
a
> field terminator and then I have a number of colums different from my tabl
e
> "Client".
> How Can I resolve this problem I want to use "," in column "comment"?
> Thanks!
>

Problem with blob field in SQL Server

In SQLServer database i have stored a blob using updateblob function,
type of the table column is image.
When i use Selectblob statement to get the blob into a blob variable, i am getting only 32 KB of data. But with ASA i am getting the complete data..
How to get Complete data in SQL Server?

regds.,
rangaWhich data provider are you using?

Terrisql

Problem with analytic sql function (The OVER SQL construct or statement is not supported)

Hi All!

Could You comment the next situation:
I'm configuring my TableAdapter just like Scott Mitchell does in his tutorial
http://www.asp.net/learn/data-access/tutorial-70-vb.aspx
The only principal difference is that I need Insert/update and delete
methods to be generated (His aim is only SELECT).
I'm also using analytic function (ROW_NUMBER) and I'm also gettin
warning "The OVER SQL construct or statement is not supported." You
say then that it could be ignored. But, in this case statements to
modify data (insert/update and delete) aren't being generated, though
after warning SQL command is executed without errors.

So, the question is obvious - why does this warning occur and how must
I perform configuration of TableAdapter based on SQL query with
analytic function?

Manually create your own insert/update/delete statements. The wizard isn't there to do it all.

Problem with an IIF Statement

In my group footer I build a string based on the number of rows in that
group plus the field the group was based on.
But if the group was based on a an empty string I get an #error in the
textbox.
So I wrapped the code with an IIF statement. If the field is empty do
nothing otherwise build the string.
= IIF(Fields!AdjustmentCode.Value = "","No Adjustment
Code",countrows("grpAdjustmentDesc") & " Records for Adjustment Code
Description: " & Fields!AdjustmentCode.Value.trim & " - " &
Fields!AdjustmentDescription.Value.tolower)FYI this is the error: The value expression for the textbox
'textbox35' contains an error: Object variable or With block
variable not set.|||VB.Net evaluates all parts of the IIF which is why you get the error. It
does not just evaluate the true portion. You could use code behind report to
do this.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"PAPutzback" <phillip_putzback@.insightbb.com> wrote in message
news:1128709777.373098.129000@.g49g2000cwa.googlegroups.com...
> In my group footer I build a string based on the number of rows in that
> group plus the field the group was based on.
> But if the group was based on a an empty string I get an #error in the
> textbox.
> So I wrapped the code with an IIF statement. If the field is empty do
> nothing otherwise build the string.
> = IIF(Fields!AdjustmentCode.Value = "","No Adjustment
> Code",countrows("grpAdjustmentDesc") & " Records for Adjustment Code
> Description: " & Fields!AdjustmentCode.Value.trim & " - " &
> Fields!AdjustmentDescription.Value.tolower)
>|||Can you point me in some direction for a Code Behind example. I can't
find anything yet.
Thanks.|||Look in books on line for the phrase code block
They have an example with that. I tend to develop my code in vb.net, test it
and then copy it over.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"PAPutzback" <phillip_putzback@.insightbb.com> wrote in message
news:1128712212.284978.143180@.z14g2000cwz.googlegroups.com...
> Can you point me in some direction for a Code Behind example. I can't
> find anything yet.
> Thanks.
>|||All i see is XML. This is going to be a lot of work to just handle the
fact the the Rpt can't handle a blank value.
So your saying I can put vb code in this.
<Textbox Name="textbox35">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<TextAlign>Left</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>25</ZIndex>
<rd:DefaultName>textbox35</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>= countrows("grpAdjustmentDesc") &
" Records for Adjustment Code Description: " &
Fields!AdjustmentCode.Value.trim & " - " &
Fields!AdjustmentDescription.Value.tolower</Value>
</Textbox>|||Can you post the hyperlink from the RSBook online address bar to where
the sample is.|||I put this in the code window
<Code>
Public Function test() As String
Dim strTest As String
strTest = "BLAH BLAH BLAH"
Return strTest
End Function
</Code>
I put this in a textbox
= code.test()
d:\phfxclaims\Pending Claims Detail.rdl There is an error on line 0 of
custom code: [BC32035] Attribute specifier is not a complete statement.
Use a line continuation to apply the attribute to the following
statement.
And this is my error|||You should not be having to modify the rdl at all (which is what you are
doing).
Search Books Online for the phrase: writing custom code
Also these two links:
ms-help://MS.RSBOL80.1033/RSCREATE/htm/rcr_creating_expressions_v1_84f9.htm
ms-help://MS.RSBOL80.1033/RShowto/htm/hrs_designer_v1_1nfp.htm
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"PAPutzback" <phillip_putzback@.insightbb.com> wrote in message
news:1128719715.084263.60890@.g43g2000cwa.googlegroups.com...
>I put this in the code window
> <Code>
> Public Function test() As String
> Dim strTest As String
> strTest = "BLAH BLAH BLAH"
> Return strTest
> End Function
> </Code>
>
> I put this in a textbox
> = code.test()
>
> d:\phfxclaims\Pending Claims Detail.rdl There is an error on line 0 of
> custom code: [BC32035] Attribute specifier is not a complete statement.
> Use a line continuation to apply the attribute to the following
> statement.
> And this is my error
>|||Thanks for the links. I finally got it to work
I had cut and pasted the code from this article
http://www.15seconds.com/issue/041110.htm and I had the tags in there
which were getting placed in the XML definition
Thanks for the quick responses

Problem with a SQL Statement

I have three tables:
Products Table
PID
PName
PType
Linking Table
LID
PID
CID
Customers Table
CID
CName
CPhone
I am trying to make a single statement that will give me all the customers
that haven't bought a certain single product so i can make a list of people
to contact regarding that product.
Any help or suggestions would be greatly appreciated... I just can't seem to
get this one right.
@.SELECT c.CName, c.Cphone
FROM Customers c
LEFT OUTER JOIN
(Linking l
INNER JOIN Products p
ON l.pid = c.pid)
ON c.CID = l.CID
AND p.PName = 'some product name'
WHERE l.CID IS NULL
or
SELECT c.CName, c.Cphone
FROM Customers c
WHERE NOT EXISTS(
SELECT NULL
FROM Linking l
INNER JOIN Products p
ON l.pid = c.pid
WHERE c.CID = l.CID
AND p.PName = 'some product name')
Jacco Schalkwijk
SQL Server MVP
"Atley" <atley_1@.hotmail.com> wrote in message
news:%23n997$C6DHA.2696@.TK2MSFTNGP09.phx.gbl...

quote:

---

> I have three tables:
> Products Table
> PID
> PName
> PType
> Linking Table
> LID
> PID
> CID
> Customers Table
> CID
> CName
> CPhone
>
> I am trying to make a single statement that will give me all the customers
> that haven't bought a certain single product so i can make a list of

---

people

quote:

---

> to contact regarding that product.
> Any help or suggestions would be greatly appreciated... I just can't seem

---

to

quote:

---

> get this one right.
> @.
>
>

---

|||--The following will show records of all customers who haven't bought item
number 901.
SELECT * FROM Customers A
WHERE NOT EXISTS
(SELECT * FROM Link
WHERE CID = A.CID AND PID = 901)
Rohtash Kapoor
http://www.sqlmantra.com
"Atley" <atley_1@.hotmail.com> wrote in message
news:%23n997$C6DHA.2696@.TK2MSFTNGP09.phx.gbl...

quote:

---

> I have three tables:
> Products Table
> PID
> PName
> PType
> Linking Table
> LID
> PID
> CID
> Customers Table
> CID
> CName
> CPhone
>
> I am trying to make a single statement that will give me all the customers
> that haven't bought a certain single product so i can make a list of

---

people

quote:

---

> to contact regarding that product.
> Any help or suggestions would be greatly appreciated... I just can't seem

---

to

quote:

---

> get this one right.
> @.
>
>

---

|||I see you have got an answer.
But please next time, do not cross-post to every newsgroup you find. This is
not a Windows Server problem, nor a datamining or datawarehouse problem.
Regards,
Kristofer Gafvert - IIS MVP
Reply to newsgroup only. Remove NEWS if you must reply by email, but please
do not.
www.ilopia.com - FAQ and Tutorials for Windows Server 2003
"Atley" <atley_1@.hotmail.com> wrote in message
news:%23n997$C6DHA.2696@.TK2MSFTNGP09.phx.gbl...

quote:

---

> I have three tables:
> Products Table
> PID
> PName
> PType
> Linking Table
> LID
> PID
> CID
> Customers Table
> CID
> CName
> CPhone
>
> I am trying to make a single statement that will give me all the customers
> that haven't bought a certain single product so i can make a list of

---

people

quote:

---

> to contact regarding that product.
> Any help or suggestions would be greatly appreciated... I just can't seem

---

to

quote:

---

> get this one right.
> @.
>
>

---

sql

Problem with a SQL Statement

I have three tables:
Products Table
PID
PName
PType
Linking Table
LID
PID
CID
Customers Table
CID
CName
CPhone
I am trying to make a single statement that will give me all the customers
that haven't bought a certain single product so i can make a list of people
to contact regarding that product.
Any help or suggestions would be greatly appreciated... I just can't seem to
get this one right.
@.SELECT c.CName, c.Cphone
FROM Customers c
LEFT OUTER JOIN
(Linking l
INNER JOIN Products p
ON l.pid = c.pid)
ON c.CID = l.CID
AND p.PName = 'some product name'
WHERE l.CID IS NULL
or
SELECT c.CName, c.Cphone
FROM Customers c
WHERE NOT EXISTS(
SELECT NULL
FROM Linking l
INNER JOIN Products p
ON l.pid = c.pid
WHERE c.CID = l.CID
AND p.PName = 'some product name')
--
Jacco Schalkwijk
SQL Server MVP
"Atley" <atley_1@.hotmail.com> wrote in message
news:%23n997$C6DHA.2696@.TK2MSFTNGP09.phx.gbl...
> I have three tables:
> Products Table
> PID
> PName
> PType
> Linking Table
> LID
> PID
> CID
> Customers Table
> CID
> CName
> CPhone
>
> I am trying to make a single statement that will give me all the customers
> that haven't bought a certain single product so i can make a list of
people
> to contact regarding that product.
> Any help or suggestions would be greatly appreciated... I just can't seem
to
> get this one right.
> @.
>
>|||--The following will show records of all customers who haven't bought item
number 901.
SELECT * FROM Customers A
WHERE NOT EXISTS
(SELECT * FROM Link
WHERE CID = A.CID AND PID = 901)
--
Rohtash Kapoor
http://www.sqlmantra.com
"Atley" <atley_1@.hotmail.com> wrote in message
news:%23n997$C6DHA.2696@.TK2MSFTNGP09.phx.gbl...
> I have three tables:
> Products Table
> PID
> PName
> PType
> Linking Table
> LID
> PID
> CID
> Customers Table
> CID
> CName
> CPhone
>
> I am trying to make a single statement that will give me all the customers
> that haven't bought a certain single product so i can make a list of
people
> to contact regarding that product.
> Any help or suggestions would be greatly appreciated... I just can't seem
to
> get this one right.
> @.
>
>|||I see you have got an answer.
But please next time, do not cross-post to every newsgroup you find. This is
not a Windows Server problem, nor a datamining or datawarehouse problem.
--
Regards,
Kristofer Gafvert - IIS MVP
Reply to newsgroup only. Remove NEWS if you must reply by email, but please
do not.
www.ilopia.com - FAQ and Tutorials for Windows Server 2003
"Atley" <atley_1@.hotmail.com> wrote in message
news:%23n997$C6DHA.2696@.TK2MSFTNGP09.phx.gbl...
> I have three tables:
> Products Table
> PID
> PName
> PType
> Linking Table
> LID
> PID
> CID
> Customers Table
> CID
> CName
> CPhone
>
> I am trying to make a single statement that will give me all the customers
> that haven't bought a certain single product so i can make a list of
people
> to contact regarding that product.
> Any help or suggestions would be greatly appreciated... I just can't seem
to
> get this one right.
> @.
>
>

Problem with a sql statement

I have a table with the monthly internet usage in minutes for each account.

I wish to create a new table based of the previously mentioned that limits the number of records per account to 9, so only include records based on each account for their past 9 months worth of internet usage.

Can anyone help me do this?Take a look at the Transact-SQL forum for help in this regard.

http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=85&SiteID=1|||I think TSQl forum is a better place for this question...moved from SSIS forum

Problem with a SP. Help!

Why does only the first INSERT statement work?

CREATE PROCEDURE insert_inventory
@.item_name varchar(20),
@.description varchar(100),
@.notes varchar(255),
@.amount varchar(8)
AS
DECLARE @.item_id int
DECLARE @.transaction_date datetime

IF (@.item_name = '') SET @.item_name = NULL
IF (@.description = '') SET @.description = NULL
IF (@.notes = '') SET @.notes = NULL
IF (@.amount = '') SET @.amount = NULL

SET @.item_id = IDENT_CURRENT('inventory')
SET @.transaction_date = GETDATE()

INSERT INTO inventory (item_name, item_description, notes) VALUES (@.item_name, @.description, @.notes)

INSERT INTO expenditure (item_id, transaction_date, amount) VALUES (@.item_id, @.transaction_date, CAST(@.amount AS money))

Also, where I have IF(@.blah = '') SET...

I use these to convert blank fields in VB6 to NULL values, is there any easier way?do you get any error on the second insert?

if @.blah='' set... can be removed, but you will have to check for the value somewhere if you care for it: either in your vb6, in the if as you do now, or in the values clause (...values (nullif(@.blah, ''), ...)|||Try to change your SP this way (you have to get id after insert - not before):

SET @.transaction_date = GETDATE()
begin tran
INSERT INTO inventory (item_name, item_description, notes) VALUES (@.item_name, @.description, @.notes)
if @.@.error<>0 begin
rollback
return
end
SET @.item_id = IDENT_CURRENT('inventory')

INSERT INTO expenditure (item_id, transaction_date, amount) VALUES (@.item_id, @.transaction_date, CAST(@.amount AS money))
commit|||i think ident_current(..) returns the _last_ new identity value for the specified table, not the identity value generated by the current scope. in this case if your insert occurred before another insert in another scope you may acquire the value that is not yours. scope_identity() guarantees that identity value belongs to insert from your session.

also, it's better to write the error handler this way:

declare @.error int, @.id int
...insert operation
select @.error = @.@.error, @.id = scope_identity()
if @.error <> 0 begin
raiserror (...)
rollback tran
return (1)
end
commit tran|||Cheers, for those, they were all good recommendations which I am now using, but it still would only execute the first INSERT statement. That is until I used this:

SET NOCOUNT ON

Works beautifully now!!!

Rayden

Problem with %....% in the Parameterized Queries

I am using MS SQL 2000.I am writing a simple search statement in the stored procedure which goes like this
Create Procedure.[dbo].[SelectSearch]
(
@.searchtext nvarchar(100)
)
SELECT * FROM TableName WHERE ColumnName LIKE @.searchtext

i want to enter the value in place of @.searchtext as '%strsearch%'
and i'm trying to enter the value of @.searchtext using Parameterized Queries like this

objcmd = new SqlCommand ("SelectSearch", objConn);
objcmd.CommandType = CommandType.StoredProcedure;
objcmd.Parameters.Add("@.searchtext", "'%" + strsearch + "%'");

but its not working out and giving the error message as
Procedure SelectSearch has no parameters and arguments were supplied

Is this the correct way to add the parameters ? Or
How to add' % on both sides of strsearch thro parameterized queries
Thank you very much in advance


You could simply append the %'s to the value.
strsearch = "%" + strsearch + "%";
objcmd = new SqlCommand ("SelectSearch", objConn);
objcmd.CommandType = CommandType.StoredProcedure;
objcmd.Parameters.Add("@.searchtext" );
Also I'd recommend adding the size of the paremeter to avoid problems later on.
|||

Thank you very much mate .

I was struggling to solve this problem atlast i got the answer . Thnx once again

Problem with "if" statement in Stored Proc

Hi All,

I'm haveing problems with a simple if statement within a stored proc. Here is a snippet of the stored proc

SELECT *
FROM [tbl_jobs], [tbl_users]
WHERE tbl_jobs.companyid = tbl_users.id
IF @.industry = 31
BEGIN
AND industry = @.industry
END

The error message i get from enterprise manager is:

Error 156: Incorrect syntax near the keyword 'AND'

If i remove the if statement and select and select an 'industry' value other than 31 the it works fine.

Thanks

What exactly are you trying to do?|||

Ok, i have a job search function, that allows the users to filter the jobs by industry. This is done with an industry dropdown list. If the value selected is "All Industries" the value passed is '31', therefore i need to ignore the industry filter and output all the results (job) regardless of what industry they are under.

I originally had the following asp.net (c#) code:

if(Request.QueryString["industry"] != "31"){
SQL += " AND industry = @.industry";
prm = new SqlParameter("@.industry",SqlDbType.VarChar,50);
prm.Direction=ParameterDirection.Input;
prm.Value = Request.QueryString["industry"];
command.Parameters.Add(prm);
}

The above worked fine, but now i'm using a stored proc (becaues i have now implemented a paging function which utilises a stored proc). I could use a "if statement" in asp.net but if i don't pass any value for @.industry, the stored proc will throw an error.

Please let me know if you require further information.

Thanks

|||

Try something like this in your stored procedure:

SELECT *
FROM [tbl_jobs], [tbl_users]
WHERE tbl_jobs.companyid = tbl_users.id AND (industry = @.industry OR @.industry <> 31)

|||

Thanks for your response Terri,

Unfortunately this seems to have the opposite result to what i was trying to achieve. It basically returns all results no matter what industry value is passed (used to filter results), unless the value "31" has been passed. When "31" is passed it outputs nothing (this is because there aren't any records have have an industry equal to "31").

|||

Give this a shot:

SELECT *FROM [tbl_jobs], [tbl_users]WHERE tbl_jobs.companyid = tbl_users.idAND industry = (CASEWHEN @.industry = 31then industryELSE @.industryEND)

|||

This will be more efficient for you (if you only have one or two of these "ALL" type queries):

IF @.industry=31BEGIN SELECT *FROM [tbl_jobs], [tbl_users]WHERE tbl_jobs.companyid = tbl_users.idENDELSEBEGIN SELECT *FROM [tbl_jobs], [tbl_users]WHERE tbl_jobs.companyid = tbl_users.idAND industry=@.industryENDAlthough you can also do:SELECT *FROM [tbl_jobs], [tbl_users]WHERE tbl_jobs.companyid = tbl_users.idAND (@.industry=31ORindustry=@.industry)

Which is also more efficient, because the comparison to the field is a fixed number, SQL Server can then use indexes more efficiently -- index range vs complete index scan.

problem when addin an extra field to a select

I have a select statement that works fine:
SELECT DISTINCT E.EmpNo, E.FirstName, E.Surname, A.StartDate, A.EndDate, Round((A.DurDays), 2) AS Days, Round((A.DurMins/60), 2) AS Hours, (K.Name) AS LeaveType,
decode (A.cost, '!!!!', 0, '!!#*', 1, '!!$1', 2, '!!%8', 3, '!!&?', 4, '!!(F', 5, '!!)M', 6,
'!!*T', 7, '!!+[', 8, '!!,b', 9, '!!-i', 10, '!!.p', 11, '!!/w', 12,
'!!0~', 13, '!!2(', 14, 25) as CostDays
from
clockwise.Employee E, clockwise.Absence A, clockwise.AbsKeys K,
ADMIN.BASIC_DETAILS@.cwselinkptec c, ADMIN.EMP_POST_DETAILS@.cwselinkptec d, ADMIN.POST_DETAILS@.cwselinkptec f
WHERE E.EmpID = A.EmpID AND Trim(E.EmpNo) = Trim(c.EMPLOYEE_NUMBER)
AND A.StartDate >= to_date('01-12-2004','dd-mm-yyyy') AND A.StartDate <= to_date('01-01-2005','dd-mm-yyyy')
AND A.AbsKeyID = K.AbsKeyID AND (K.AbsKeyID = '0' OR K.AbsKeyID = '1') AND c.EMPLOYEE_NUMBER = d.EMPLOYEE_NUMBER
AND d.LINK_EFF_LINK = f.LINK_EFF_LINK AND f.post_location = '4106'
ORDER BY E.Surname ASC, A.StartDate

I get 26 rows returned. I need to add an extra field to the select. The Reason field on the Absence table contains a code, the value for this code, which I need, is stored in the Choices table. The Choices table has KEYID and ITEMID as the Primary Key. Reason field links to the ITEMID field.

When I run the following sql:
SELECT DISTINCT E.EmpNo, E.FirstName, E.Surname, A.StartDate, A.EndDate, Round((A.DurDays), 2) AS Days, Round((A.DurMins/60), 2) AS Hours, (K.Name) AS LeaveType,
decode (A.cost, '!!!!', 0, '!!#*', 1, '!!$1', 2, '!!%8', 3, '!!&?', 4, '!!(F', 5, '!!)M', 6,
'!!*T', 7, '!!+[', 8, '!!,b', 9, '!!-i', 10, '!!.p', 11, '!!/w', 12,
'!!0~', 13, '!!2(', 14, 25) as CostDays, (B.Name) as SLReason
FROM clockwise.Employee E, clockwise.Absence A, clockwise.AbsKeys K, clockwise.choices B,
ADMIN.BASIC_DETAILS@.cwselinkptec c, ADMIN.EMP_POST_DETAILS@.cwselinkptec d, ADMIN.POST_DETAILS@.cwselinkptec f
WHERE E.EmpID = A.EmpID AND Trim(E.EmpNo) = Trim(c.EMPLOYEE_NUMBER)
AND A.StartDate >= to_date('01-12-2004','dd-mm-yyyy') AND A.StartDate <= to_date('01-01-2005','dd-mm-yyyy')
AND A.AbsKeyID = K.AbsKeyID AND (K.AbsKeyID = '0' OR K.AbsKeyID = '1') AND c.EMPLOYEE_NUMBER = d.EMPLOYEE_NUMBER
AND d.LINK_EFF_LINK = f.LINK_EFF_LINK AND f.post_location = '4106'
AND B.KeyID = 'R%' AND B.ItemID = A.Reason
ORDER BY E.Surname ASC, A.StartDate

I get 1 row, the only person from the previous select who has an entry in the Choices table. I understand why.

When I run the following sql:
SELECT DISTINCT E.EmpNo, E.FirstName, E.Surname, A.StartDate, A.EndDate, Round((A.DurDays), 2) AS Days, Round((A.DurMins/60), 2) AS Hours, (K.Name) AS LeaveType,
decode (A.cost, '!!!!', 0, '!!#*', 1, '!!$1', 2, '!!%8', 3, '!!&?', 4, '!!(F', 5, '!!)M', 6,
'!!*T', 7, '!!+[', 8, '!!,b', 9, '!!-i', 10, '!!.p', 11, '!!/w', 12,
'!!0~', 13, '!!2(', 14, 25) as CostDays, SLReason
FROM (SELECT (B.Name) SLReason
FROM clockwise.choices B,
clockwise.Absence A
WHERE B.KeyID = 'R%'
AND B.ItemID = A.Reason),
clockwise.Employee E, clockwise.Absence A, clockwise.AbsKeys K,
ADMIN.BASIC_DETAILS@.cwselinkptec c, ADMIN.EMP_POST_DETAILS@.cwselinkptec d, ADMIN.POST_DETAILS@.cwselinkptec f
WHERE E.EmpID = A.EmpID AND Trim(E.EmpNo) = Trim(c.EMPLOYEE_NUMBER)
AND A.StartDate >= to_date('01-12-2004','dd-mm-yyyy') AND A.StartDate <= to_date('01-01-2005','dd-mm-yyyy')
AND A.AbsKeyID = K.AbsKeyID AND (K.AbsKeyID = '0' OR K.AbsKeyID = '1') AND c.EMPLOYEE_NUMBER = d.EMPLOYEE_NUMBER
AND d.LINK_EFF_LINK = f.LINK_EFF_LINK AND f.post_location = '4106'
ORDER BY E.Surname ASC, A.StartDate

I get 104 rows, each person from the first select now appears 4 times with 1 of 4 different reasons.

How do I get 26 rows with just the 1 person showing his reason and the 25 other's having blank reasons.

Thanks for any help.Have a look at the Join predicate. Rather than define you realtionship using a where clause use a join. The join can cater for the situation where the value is null.

Sorry, don't have time to examine id detail your SQL but will try to later on tonight, that is if no one else has come up with a better answer in the mean time.|||What you need is a LEFT JOIN.

Based on the SQL that you've posted, you appear to be using Oracle, but I can't tell which version. If your Oracle is current enough to support the SQL-92 syntax, I'd strongly suggest that you switch to it because it makes a lot of things easier than the SQL-89 syntax that you are using in this query.

If you need more help, please post your Oracle version and whatever question(s) you might have.

-PatP|||I'm using Oracle8i, since I posted I've been trying to use a join (left outer) with no success so far.

Thanks for the info, I'll keep trying.|||Got it working by totally ignoring the choices table. I was getting more comlicated than I needed, I just added a decode for the Reason field:
SELECT DISTINCT E.EmpNo, E.FirstName, E.Surname, A.StartDate, A.EndDate, Round((A.DurDays), 2) AS Days, Round((A.DurMins/60), 2) AS Hours, (K.Name) AS LeaveType,
decode(A.Cost, '!!!!', 0, '!!#*', 1, '!!$1', 2, '!!%8', 3, '!!&?', 4, '!!(F', 5, '!!)M', 6,
'!!*T', 7, '!!+[', 8, '!!,b', 9, '!!-i', 10, '!!.p', 11, '!!/w', 12,
'!!0~', 13, '!!2(', 14, 25) as CostDays,
Decode(A.Reason, '!', 'Certified', '#', 'Hospitalised', '$', 'Uncertified', '%', 'Unwell',
'&', 'Throat Infection', Null) as SLReason
FROM clockwise.Employee E, clockwise.Absence A, clockwise.AbsKeys K,
ADMIN.BASIC_DETAILS@.cwselinkptec c, ADMIN.EMP_POST_DETAILS@.cwselinkptec d, ADMIN.POST_DETAILS@.cwselinkptec f
WHERE E.EmpID = A.EmpID AND Trim(E.EmpNo) = Trim(c.EMPLOYEE_NUMBER)
AND A.StartDate >= to_date('01-12-2004','dd-mm-yyyy') AND A.StartDate <= to_date('01-01-2005','dd-mm-yyyy')
AND A.AbsKeyID = K.AbsKeyID AND (K.AbsKeyID = '0' OR K.AbsKeyID = '1') AND c.EMPLOYEE_NUMBER = d.EMPLOYEE_NUMBER
AND d.LINK_EFF_LINK = f.LINK_EFF_LINK AND f.post_location = '4106'
ORDER BY E.Surname ASC, A.StartDate

I got the 26 rows with the correct value.

Thanks for the help,
Mark.

Problem Warming Cache (Fails to Run MDX Statement)

Hello all – I’m running into an issue that has me a little stuck and I was hoping to get your advice.I have an SSIS package which runs after my dimension / cube processing that iterates through a relational table containing MDX statements (from several key reports) and executes them to warm the cache.

This has been a very successful strategy for me until the recent addition of a MDX statement that absolutely refuses to be executed via SSIS using the ADO.NET connection type / MSOLAP.3 provider.This MDX statement will run fine in Management Studio as well as from the report.To make matters worse, if I run the MDX statement from the report or from Management Studio, the SSIS package will not fail on this particular statement.It only fails if the cache is cold:

{SQL Server Analysis Services 9.0 build 3042 (SP2)}

Error: 0xC002F210 at Run MDX Query, Execute SQL Task: Executing the query " SELECT NON EMPTY { [Measures].[Volume - Sales Forecast], [Measures].[Volume - Prior Year Actuals], [Measures].[Volume - Sales Plan], [Measures].[Estimated Sales Volume], [Measures].[Volume - Financial Forecast], [Measures].[Volume - Open Orders], [Measures].[Volume - Actuals] } ON COLUMNS, NON EMPTY { ([Sales Channel].[Sales Channel].[Sales Channel].ALLMEMBERS * [Location].[Location Name].[Location Name].ALLMEMBERS * [Profile].[Profile].[Profile].ALLMEMBERS * [Location].[Location ID].[Location ID].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM [Closure Flash Current] CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS" failed with the following error: "Errors in the back-end database access module. The data provider does not support preparing queries.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

SELECT NON EMPTY

{[Measures].[Volume - Sales Forecast], [Measures].[Volume - Prior Year Actuals],

[Measures].[Volume - Sales Plan], [Measures].[Estimated Sales Volume],

[Measures].[Volume - Financial Forecast], [Measures].[Volume - Open Orders],

[Measures].[Volume - Actuals] } ON COLUMNS,

NON EMPTY { ([Sales Channel].[Sales Channel].[Sales Channel].ALLMEMBERS *

[Location].[Location Name].[Location Name].ALLMEMBERS *

[Profile].[Profile].[Profile].ALLMEMBERS *

[Location].[Location ID].[Location ID].ALLMEMBERS ) } ON ROWS

FROM [Closure Flash Current]

I’m sure I’m missing something obvious, but whatever it may be is successfully stumping me.I appreciate any help or advice you can provide!

I figured it out; thought I would share it with all in-case you run across a similar scenario (I know when I was searching for this problem I found very little out there in the way of help):

When I ran profiler against the SSAS instance I noticed that it was trying to resolve the offending MDX statement into T-SQL statements (like you would expect to see in ROLAP storage) but it was attempting to PREPARE them against the SSAS instance, which of course would never work.

After some investigation I found that one of the partitions on the cube had been set to ROLAP and was causing the issue. After converting to MOLAP and deploying / processing, the issue went away and now my cache warming SSIS package is successful.

I would argue that this is a bug since the provider from SSIS is trying to prepare the T-SQL statements for a ROLAP cube against SSAS, but the same behavior isn't experienced in SSMS / SSRS.

Problem Warming Cache (Fails to Run MDX Statement)

Hello all – I’m running into an issue that has me a little stuck and I was hoping to get your advice.I have an SSIS package which runs after my dimension / cube processing that iterates through a relational table containing MDX statements (from several key reports) and executes them to warm the cache.

This has been a very successful strategy for me until the recent addition of a MDX statement that absolutely refuses to be executed via SSIS using the ADO.NET connection type / MSOLAP.3 provider.This MDX statement will run fine in Management Studio as well as from the report.To make matters worse, if I run the MDX statement from the report or from Management Studio, the SSIS package will not fail on this particular statement.It only fails if the cache is cold:

{SQL Server Analysis Services 9.0 build 3042 (SP2)}

Error: 0xC002F210 at Run MDX Query, Execute SQL Task: Executing the query " SELECT NON EMPTY { [Measures].[Volume - Sales Forecast], [Measures].[Volume - Prior Year Actuals], [Measures].[Volume - Sales Plan], [Measures].[Estimated Sales Volume], [Measures].[Volume - Financial Forecast], [Measures].[Volume - Open Orders], [Measures].[Volume - Actuals] } ON COLUMNS, NON EMPTY { ([Sales Channel].[Sales Channel].[Sales Channel].ALLMEMBERS * [Location].[Location Name].[Location Name].ALLMEMBERS * [Profile].[Profile].[Profile].ALLMEMBERS * [Location].[Location ID].[Location ID].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM [Closure Flash Current] CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS" failed with the following error: "Errors in the back-end database access module. The data provider does not support preparing queries.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

SELECTNONEMPTY

{[Measures].[Volume - Sales Forecast], [Measures].[Volume - Prior Year Actuals],

[Measures].[Volume - Sales Plan], [Measures].[Estimated Sales Volume],

[Measures].[Volume - Financial Forecast], [Measures].[Volume - Open Orders],

[Measures].[Volume - Actuals] }ONCOLUMNS,

NONEMPTY { ([Sales Channel].[Sales Channel].[Sales Channel].ALLMEMBERS *

[Location].[Location Name].[Location Name].ALLMEMBERS *

[Profile].[Profile].[Profile].ALLMEMBERS *

[Location].[Location ID].[Location ID].ALLMEMBERS ) }ONROWS

FROM [Closure Flash Current]

I’m sure I’m missing something obvious, but whatever it may be is successfully stumping me.I appreciate any help or advice you can provide!

I figured it out; thought I would share it with all in-case you run across a similar scenario (I know when I was searching for this problem I found very little out there in the way of help):

When I ran profiler against the SSAS instance I noticed that it was trying to resolve the offending MDX statement into T-SQL statements (like you would expect to see in ROLAP storage) but it was attempting to PREPARE them against the SSAS instance, which of course would never work.

After some investigation I found that one of the partitions on the cube had been set to ROLAP and was causing the issue. After converting to MOLAP and deploying / processing, the issue went away and now my cache warming SSIS package is successful.

I would argue that this is a bug since the provider from SSIS is trying to prepare the T-SQL statements for a ROLAP cube against SSAS, but the same behavior isn't experienced in SSMS / SSRS.

problem using sql statement in Visual Studio 2005

thanks for read my question.
i have problem when i using sql statement in Visual Studio 2005:
for example:
sp_configure 'show advanced options',1;
GO
RECONFIGURE;
GO
sp_configure 'Ad Hoc Distributed Queries',1;
GO
RECONFIGURE;
GO
with these statement i have put on separate line to exec, but if i but these statement on separate in VS2005 it is failed, what can i do?
how can i run file .sql from VS2005
HOW?
thanks a lot.

use the following statement to execute the .sql file from your sql server..

USE master
GO
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure 'show advanced options', 0
GO

Exec XP_CmdShell 'sqlcmd -SWXP-J72MM1S\MSSQL2005 -USA -Psqladmin -i"PATH OF THE .SQL FILE"'

|||it return error:

Msg 15281, Level 16, State 1, Procedure xp_cmdshell, Line 1
SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'xp_cmdshell' by using sp_configure. For more information about enabling 'xp_cmdshell', see "Surface Area Configuration" in SQL Server Books Online.

What can i do? how can i solve?
thanks
|||

Before executing the xp_cmdshell you have to change the server config.

USE master
GO
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure 'show advanced options', 0
GO

To execute the above batch you shoule be a System Admin.

|||thanks for your answer but i can't get my result
it return 6 rows with output

HResult 0xFFFFFFFF, Level 16, State 1
SQL Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].
Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote c
onnections..
Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.
NULL

what must i do?|||

The cause may be,

#1. The server is not enabled the Remote Connection (if you are not executing your query from remote server leave this cause and goto #2). To enable the remote login goto Programs -> Microsoft SQL Server 2005 -> Configuration Tools and open the "SQL Server Surface Area Configuration" click the Service & Connections link.

Select the Remote Connections node under the data base engine and check the Local and Remote Connection radio button.

#2. The login you entered is incorrect. don't put more space between your password and -i

sqlcmd -SSERVERNAME -UUSERNAME -PPASSWORD -i"PATH OF THE FILE"

Problem using SELECT statement on Access Column Name

Im writing a VB program that queries an MS Access db. The column name is CONTACT#. Using the SQL statement...
"SELECT * FROM CLIENT WHERE CONTACT# = '1'"
Produces an error. I cannot change the name of the column name is there anyway around this?Try this:
SELECT * FROM CLIENT WHERE [CONTACT#] = '1'
:eek:

Problem using Portugueses characters

Dear friends,
I got a XML string that I need to convert in a select statement. I'm using
OPENXML to do it and everything works well, but when I write Portugueses
words in my XML document(for sample: valida?o) is shown something weird
like: valida?o
I tried to change the collation, using SQL_Latin1_General_CP1_CS_AS and so
on, but nothing works. Check out my code.
declare @.idoc int
declare @.doc_xml_nota varchar(8000)
set @.doc_xml_nota = '
<?xml version="1.0" encoding="ISO-8859-1"?>
<NewDataSet>
<nota_fiscal_fornecedor_setor_publico>
<des_informacao_complement>valida?o </des_informacao_complement>
</nota_fiscal_fornecedor_setor_publico>
</NewDataSet>
'
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc_xml_nota
select *
FROM OPENXML (@.idoc, '/NewDataSet/nota_fiscal_fornecedor_setor_publico', 2)
WITH
(
des_informacao_complement char(225) COLLATE SQL_Latin1_General_CP1_CS_AS
)
EXEC sp_xml_removedocument @.idoc
Thanks,
Euler Almeida
Message posted via http://www.sqlmonster.com
Can you use Unicode characters instead?
E.g,
declare @.doc_xml_nota nvarchar(8000)
set @.doc_xml_nota = N'<NewDataSet>
<nota_fiscal_fornecedor_setor_publico>
<des_informacao_complement>valida?o </des_informacao_complement>
</nota_fiscal_fornecedor_setor_publico>
</NewDataSet> '
Otherwise, you need to make sure that the database collation and the
encoding inside the XML and the character codes are all aligned...
Best regards
Michael
"Euler Almeida via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:cdc1fae2f9d34240ac26017dcba8b8aa@.SQLMonster.c om...
> Dear friends,
> I got a XML string that I need to convert in a select statement. I'm using
> OPENXML to do it and everything works well, but when I write Portugueses
> words in my XML document(for sample: valida?o) is shown something weird
> like: valida?o
> I tried to change the collation, using SQL_Latin1_General_CP1_CS_AS and so
> on, but nothing works. Check out my code.
> declare @.idoc int
> declare @.doc_xml_nota varchar(8000)
> set @.doc_xml_nota = '
> <?xml version="1.0" encoding="ISO-8859-1"?>
> <NewDataSet>
> <nota_fiscal_fornecedor_setor_publico>
> <des_informacao_complement>valida?o </des_informacao_complement>
> </nota_fiscal_fornecedor_setor_publico>
> </NewDataSet>
> '
> EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc_xml_nota
> select *
> FROM OPENXML (@.idoc, '/NewDataSet/nota_fiscal_fornecedor_setor_publico',
> 2)
> WITH
> (
> des_informacao_complement char(225) COLLATE SQL_Latin1_General_CP1_CS_AS
> )
> EXEC sp_xml_removedocument @.idoc
>
> Thanks,
> Euler Almeida
> --
> Message posted via http://www.sqlmonster.com

Problem using Portugueses characters

Dear friends,
I got a XML string that I need to convert in a select statement. I'm using
OPENXML to do it and everything works well, but when I write Portugueses
words in my XML document(for sample: valida'o) is shown something weird
like: valida'o
I tried to change the collation, using SQL_Latin1_General_CP1_CS_AS and so
on, but nothing works. Check out my code.
---
declare @.idoc int
declare @.doc_xml_nota varchar(8000)
set @.doc_xml_nota = '
<?xml version="1.0" encoding="ISO-8859-1"?>
<NewDataSet>
<nota_fiscal_fornecedor_setor_publico>
<des_informacao_complement>valida'o </des_informacao_complement>
</nota_fiscal_fornecedor_setor_publico>
</NewDataSet>
'
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc_xml_nota
select *
FROM OPENXML (@.idoc, '/NewDataSet/nota_fiscal_fornecedor_setor_publico', 2)
WITH
(
des_informacao_complement char(225) COLLATE SQL_Latin1_General_CP1_CS_AS
)
EXEC sp_xml_removedocument @.idoc
Thanks,
Euler Almeida
Message posted via http://www.webservertalk.comCan you use Unicode characters instead?
E.g,
declare @.doc_xml_nota nvarchar(8000)
set @.doc_xml_nota = N'<NewDataSet>
<nota_fiscal_fornecedor_setor_publico>
<des_informacao_complement>valida'o </des_informacao_complement>
</nota_fiscal_fornecedor_setor_publico>
</NewDataSet> '
Otherwise, you need to make sure that the database collation and the
encoding inside the XML and the character codes are all aligned...
Best regards
Michael
"Euler Almeida via webservertalk.com" <forum@.webservertalk.com> wrote in message
news:cdc1fae2f9d34240ac26017dcba8b8aa@.SQ
webservertalk.com...
> Dear friends,
> I got a XML string that I need to convert in a select statement. I'm using
> OPENXML to do it and everything works well, but when I write Portugueses
> words in my XML document(for sample: valida'o) is shown something weird
> like: valida'o
> I tried to change the collation, using SQL_Latin1_General_CP1_CS_AS and so
> on, but nothing works. Check out my code.
> ---
> declare @.idoc int
> declare @.doc_xml_nota varchar(8000)
> set @.doc_xml_nota = '
> <?xml version="1.0" encoding="ISO-8859-1"?>
> <NewDataSet>
> <nota_fiscal_fornecedor_setor_publico>
> <des_informacao_complement>valida'o </des_informacao_complement>
> </nota_fiscal_fornecedor_setor_publico>
> </NewDataSet>
> '
> EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc_xml_nota
> select *
> FROM OPENXML (@.idoc, '/NewDataSet/nota_fiscal_fornecedor_setor_publico',
> 2)
> WITH
> (
> des_informacao_complement char(225) COLLATE SQL_Latin1_General_CP1_CS_AS
> )
> EXEC sp_xml_removedocument @.idoc
>
> Thanks,
> Euler Almeida
> --
> Message posted via http://www.webservertalk.com