I need to fill a cursor with 3 columns.
A want to use a Select sprocs (for re-use de code), but this sproc return 15 columns and the 3 a need was not the 3 frist. :confused:
Do I need to map the 15 columns with 15 variables locally? Or they have a way easier?
Thankscan u post the code? some one here might come up with a better solution|||DECLARE cuTEST CURSOR FOR
ItemsSEL
OPEN cuTEST
FETCH NEXT FROM cuTEST
INTO @.col1, @.col2, @.col3, @.col4, @.col5,....
but I need only col2, col10 and col14
ItemsSEL is my Strore Procedure for my complexe Select Use many place in my application ( where a need all result columns...)|||Would it be possible to redesign the ItemsSel procedure into a view? How complex is this procedure?
Showing posts with label select. Show all posts
Showing posts with label select. Show all posts
Friday, March 23, 2012
it doesn't correlate
SELECT [Pcode], avg(convert(float,[Seq]))
FROM [DB].[dbo].[PartyTime]
WHERE [Seq] IN (select top 8 [PartyTime].[Seq]
from [PartyTime] JOIN [PartyTime] [PT]
ON [PartyTime].[Pcode] = [PT].[Pcode]
AND [PartyTime].[Seq] = [PT].[Seq]
order by [PartyTime].[Seq])
GROUP By [Pcode]
..yields:
TOP 4.5
..but what I'm after is:
BOT 11954.5
TOP 4.5
It takes the top 8 of all of PartyTime rather than the top 8 of each
Pcode that it's averaging. Just a subselect without any correlation.
Anyone got a correct syntax for this or another approach? Remember, the
idea is not to hardcode Pcode values; the set is unknown at query time.
thx
md
*** Sent via Developersdex http://www.examnotes.net ***SELECT [Pcode], avg(convert(float,[Seq]))
FROM [DB].[dbo].[PartyTime]
WHERE [Seq] IN (select top 8 [PartyTime].[Seq]
from [PartyTime] JOIN [PartyTime] [PT]
ON [PartyTime].[Pcode] = [PT].[Pcode]
AND [PartyTime].[Seq] = [PT].[Seq]
Group by [PartyTime].[Seq]
order by [PartyTime].[Seq])
GROUP By [Pcode]
Another Example for the subquery:
Use northwind
Select TOP 5 CustomerID
From Orders
group by CustomerID
order by CustomerID
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"M D" <mardukes@.aol.com> schrieb im Newsbeitrag
news:eykIxmoSFHA.3392@.TK2MSFTNGP12.phx.gbl...
> SELECT [Pcode], avg(convert(float,[Seq]))
> FROM [DB].[dbo].[PartyTime]
> WHERE [Seq] IN (select top 8 [PartyTime].[Seq]
> from [PartyTime] JOIN [PartyTime] [PT]
> ON [PartyTime].[Pcode] = [PT].[Pcode]
> AND [PartyTime].[Seq] = [PT].[Seq]
> order by [PartyTime].[Seq])
> GROUP By [Pcode]
> ..yields:
> TOP 4.5
> ..but what I'm after is:
> BOT 11954.5
> TOP 4.5
> It takes the top 8 of all of PartyTime rather than the top 8 of each
> Pcode that it's averaging. Just a subselect without any correlation.
> Anyone got a correct syntax for this or another approach? Remember, the
> idea is not to hardcode Pcode values; the set is unknown at query time.
> thx
> md
> *** Sent via Developersdex http://www.examnotes.net ***|||This modification has no effect on the output except to slow the query.
thx
md
*** Sent via Developersdex http://www.examnotes.net ***|||> SELECT [Pcode], avg(convert(float,[Seq]))
> FROM [DB].[dbo].[PartyTime] PT INNER JOIN
(select top 8 [PartyTime].[Seq]
> from [PartyTime] JOIN [PartyTime] [PT]
> ON [PartyTime].[Pcode] = [PT].[Pcode]
> AND [PartyTime].[Seq] = [PT].[Seq]
> Group by [PartyTime].[Seq]
> order by [PartyTime].[Seq]) SUBQuery
on SUBQuery sq = PT.sq
> WHERE [Seq] IN GROUP By [Pcode]
Use this query to keep up performace, what does the inner query bring for a
resultset ?
Jens Suessmeyer.
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> schrieb
im Newsbeitrag news:OXx213oSFHA.1152@.tk2msftngp13.phx.gbl...
> SELECT [Pcode], avg(convert(float,[Seq]))
> FROM [DB].[dbo].[PartyTime]
> WHERE [Seq] IN (select top 8 [PartyTime].[Seq]
> from [PartyTime] JOIN [PartyTime] [PT]
> ON [PartyTime].[Pcode] = [PT].[Pcode]
> AND [PartyTime].[Seq] = [PT].[Seq]
> Group by [PartyTime].[Seq]
> order by [PartyTime].[Seq])
> GROUP By [Pcode]
> Another Example for the subquery:
> Use northwind
> Select TOP 5 CustomerID
> From Orders
> group by CustomerID
> order by CustomerID
>
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>
> "M D" <mardukes@.aol.com> schrieb im Newsbeitrag
> news:eykIxmoSFHA.3392@.TK2MSFTNGP12.phx.gbl...
>|||the inner query returns {1,2,3,4,5,6,7,8} which all happen to be of
Pcode "TOP". Hence I get no avg for Pcode "BOT".
What I need the inner query to return is the what this hardcoded query
returns:
select top 8 [Seq] from [ProfTraining]
where Pcode = "TOP"
order by [ProfTraining].[Seq]
UNION
select top 8 [Seq] from [ProfTraining]
where Pcode = "BOT"
order by [ProfTraining].[Seq]
Again, I can't hardcode the Pcodes because the next set of data might
have other values OR I don't want to code out n UNION statements.
Interestingly, if I change it to
.. EXISTS (select top 8 * ...
I get two values but they are the avg's of the top 8's, they're avg's of
the entire groups.
thx
md
*** Sent via Developersdex http://www.examnotes.net ***|||Correction:
I get two values but they AREN'T the avg's of the top 8's, they're avg's
of the entire groups.
More accurately, I need the inner query to correlate; it would look like
the UNION because the inner query would be offering the top 8 records
having the same Pcode as the outer record.
thx
md
*** Sent via Developersdex http://www.examnotes.net ***|||On Tue, 26 Apr 2005 10:59:27 -0700, M D wrote:
>SELECT [Pcode], avg(convert(float,[Seq]))
> FROM [DB].[dbo].[PartyTime]
> WHERE [Seq] IN (select top 8 [PartyTime].[Seq]
> from [PartyTime] JOIN [PartyTime] [PT]
> ON [PartyTime].[Pcode] = [PT].[Pcode]
> AND [PartyTime].[Seq] = [PT].[Seq]
> order by [PartyTime].[Seq])
> GROUP By [Pcode]
>..yields:
>TOP 4.5
>..but what I'm after is:
>BOT 11954.5
>TOP 4.5
(snip)
Hi md,
Try if this one works better:
SELECT Pcode, AVG(CAST(Seq AS float))
FROM PartyTime
WHERE Seq IN (SELECT TOP 8 PT.Seq
FROM PartyTime AS PT
WHERE PT.Pcode = PartyTime.Pcode
ORDER BY PT.Seq)
GROUP BY Pcode
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||BINGO!
thx
md
*** Sent via Developersdex http://www.examnotes.net ***|||MD,
THis should work...
Select Pcode, Avg(Cast(Seq As Float))
From PartyTime Pt
Where (Select Count(*)
From PartyTime
Where PCode = Pt.PCode
And Seq >= pt.Seq) <= 8
Group By PCode
"M D" wrote:
> SELECT [Pcode], avg(convert(float,[Seq]))
> FROM [DB].[dbo].[PartyTime]
> WHERE [Seq] IN (select top 8 [PartyTime].[Seq]
> from [PartyTime] JOIN [PartyTime] [PT]
> ON [PartyTime].[Pcode] = [PT].[Pcode]
> AND [PartyTime].[Seq] = [PT].[Seq]
> order by [PartyTime].[Seq])
> GROUP By [Pcode]
> ...yields:
> TOP 4.5
> ...but what I'm after is:
> BOT 11954.5
> TOP 4.5
> It takes the top 8 of all of PartyTime rather than the top 8 of each
> Pcode that it's averaging. Just a subselect without any correlation.
> Anyone got a correct syntax for this or another approach? Remember, the
> idea is not to hardcode Pcode values; the set is unknown at query time.
> thx
> md
> *** Sent via Developersdex http://www.examnotes.net ***
>|||TOP 11946.5
MPAW 23573.5
I can't figure out why you think that works but it's slower and wrong.
Examine the one above for the solution.
thx
md
*** Sent via Developersdex http://www.examnotes.net ***sql
FROM [DB].[dbo].[PartyTime]
WHERE [Seq] IN (select top 8 [PartyTime].[Seq]
from [PartyTime] JOIN [PartyTime] [PT]
ON [PartyTime].[Pcode] = [PT].[Pcode]
AND [PartyTime].[Seq] = [PT].[Seq]
order by [PartyTime].[Seq])
GROUP By [Pcode]
..yields:
TOP 4.5
..but what I'm after is:
BOT 11954.5
TOP 4.5
It takes the top 8 of all of PartyTime rather than the top 8 of each
Pcode that it's averaging. Just a subselect without any correlation.
Anyone got a correct syntax for this or another approach? Remember, the
idea is not to hardcode Pcode values; the set is unknown at query time.
thx
md
*** Sent via Developersdex http://www.examnotes.net ***SELECT [Pcode], avg(convert(float,[Seq]))
FROM [DB].[dbo].[PartyTime]
WHERE [Seq] IN (select top 8 [PartyTime].[Seq]
from [PartyTime] JOIN [PartyTime] [PT]
ON [PartyTime].[Pcode] = [PT].[Pcode]
AND [PartyTime].[Seq] = [PT].[Seq]
Group by [PartyTime].[Seq]
order by [PartyTime].[Seq])
GROUP By [Pcode]
Another Example for the subquery:
Use northwind
Select TOP 5 CustomerID
From Orders
group by CustomerID
order by CustomerID
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"M D" <mardukes@.aol.com> schrieb im Newsbeitrag
news:eykIxmoSFHA.3392@.TK2MSFTNGP12.phx.gbl...
> SELECT [Pcode], avg(convert(float,[Seq]))
> FROM [DB].[dbo].[PartyTime]
> WHERE [Seq] IN (select top 8 [PartyTime].[Seq]
> from [PartyTime] JOIN [PartyTime] [PT]
> ON [PartyTime].[Pcode] = [PT].[Pcode]
> AND [PartyTime].[Seq] = [PT].[Seq]
> order by [PartyTime].[Seq])
> GROUP By [Pcode]
> ..yields:
> TOP 4.5
> ..but what I'm after is:
> BOT 11954.5
> TOP 4.5
> It takes the top 8 of all of PartyTime rather than the top 8 of each
> Pcode that it's averaging. Just a subselect without any correlation.
> Anyone got a correct syntax for this or another approach? Remember, the
> idea is not to hardcode Pcode values; the set is unknown at query time.
> thx
> md
> *** Sent via Developersdex http://www.examnotes.net ***|||This modification has no effect on the output except to slow the query.
thx
md
*** Sent via Developersdex http://www.examnotes.net ***|||> SELECT [Pcode], avg(convert(float,[Seq]))
> FROM [DB].[dbo].[PartyTime] PT INNER JOIN
(select top 8 [PartyTime].[Seq]
> from [PartyTime] JOIN [PartyTime] [PT]
> ON [PartyTime].[Pcode] = [PT].[Pcode]
> AND [PartyTime].[Seq] = [PT].[Seq]
> Group by [PartyTime].[Seq]
> order by [PartyTime].[Seq]) SUBQuery
on SUBQuery sq = PT.sq
> WHERE [Seq] IN GROUP By [Pcode]
Use this query to keep up performace, what does the inner query bring for a
resultset ?
Jens Suessmeyer.
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> schrieb
im Newsbeitrag news:OXx213oSFHA.1152@.tk2msftngp13.phx.gbl...
> SELECT [Pcode], avg(convert(float,[Seq]))
> FROM [DB].[dbo].[PartyTime]
> WHERE [Seq] IN (select top 8 [PartyTime].[Seq]
> from [PartyTime] JOIN [PartyTime] [PT]
> ON [PartyTime].[Pcode] = [PT].[Pcode]
> AND [PartyTime].[Seq] = [PT].[Seq]
> Group by [PartyTime].[Seq]
> order by [PartyTime].[Seq])
> GROUP By [Pcode]
> Another Example for the subquery:
> Use northwind
> Select TOP 5 CustomerID
> From Orders
> group by CustomerID
> order by CustomerID
>
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>
> "M D" <mardukes@.aol.com> schrieb im Newsbeitrag
> news:eykIxmoSFHA.3392@.TK2MSFTNGP12.phx.gbl...
>|||the inner query returns {1,2,3,4,5,6,7,8} which all happen to be of
Pcode "TOP". Hence I get no avg for Pcode "BOT".
What I need the inner query to return is the what this hardcoded query
returns:
select top 8 [Seq] from [ProfTraining]
where Pcode = "TOP"
order by [ProfTraining].[Seq]
UNION
select top 8 [Seq] from [ProfTraining]
where Pcode = "BOT"
order by [ProfTraining].[Seq]
Again, I can't hardcode the Pcodes because the next set of data might
have other values OR I don't want to code out n UNION statements.
Interestingly, if I change it to
.. EXISTS (select top 8 * ...
I get two values but they are the avg's of the top 8's, they're avg's of
the entire groups.
thx
md
*** Sent via Developersdex http://www.examnotes.net ***|||Correction:
I get two values but they AREN'T the avg's of the top 8's, they're avg's
of the entire groups.
More accurately, I need the inner query to correlate; it would look like
the UNION because the inner query would be offering the top 8 records
having the same Pcode as the outer record.
thx
md
*** Sent via Developersdex http://www.examnotes.net ***|||On Tue, 26 Apr 2005 10:59:27 -0700, M D wrote:
>SELECT [Pcode], avg(convert(float,[Seq]))
> FROM [DB].[dbo].[PartyTime]
> WHERE [Seq] IN (select top 8 [PartyTime].[Seq]
> from [PartyTime] JOIN [PartyTime] [PT]
> ON [PartyTime].[Pcode] = [PT].[Pcode]
> AND [PartyTime].[Seq] = [PT].[Seq]
> order by [PartyTime].[Seq])
> GROUP By [Pcode]
>..yields:
>TOP 4.5
>..but what I'm after is:
>BOT 11954.5
>TOP 4.5
(snip)
Hi md,
Try if this one works better:
SELECT Pcode, AVG(CAST(Seq AS float))
FROM PartyTime
WHERE Seq IN (SELECT TOP 8 PT.Seq
FROM PartyTime AS PT
WHERE PT.Pcode = PartyTime.Pcode
ORDER BY PT.Seq)
GROUP BY Pcode
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||BINGO!
thx
md
*** Sent via Developersdex http://www.examnotes.net ***|||MD,
THis should work...
Select Pcode, Avg(Cast(Seq As Float))
From PartyTime Pt
Where (Select Count(*)
From PartyTime
Where PCode = Pt.PCode
And Seq >= pt.Seq) <= 8
Group By PCode
"M D" wrote:
> SELECT [Pcode], avg(convert(float,[Seq]))
> FROM [DB].[dbo].[PartyTime]
> WHERE [Seq] IN (select top 8 [PartyTime].[Seq]
> from [PartyTime] JOIN [PartyTime] [PT]
> ON [PartyTime].[Pcode] = [PT].[Pcode]
> AND [PartyTime].[Seq] = [PT].[Seq]
> order by [PartyTime].[Seq])
> GROUP By [Pcode]
> ...yields:
> TOP 4.5
> ...but what I'm after is:
> BOT 11954.5
> TOP 4.5
> It takes the top 8 of all of PartyTime rather than the top 8 of each
> Pcode that it's averaging. Just a subselect without any correlation.
> Anyone got a correct syntax for this or another approach? Remember, the
> idea is not to hardcode Pcode values; the set is unknown at query time.
> thx
> md
> *** Sent via Developersdex http://www.examnotes.net ***
>|||TOP 11946.5
MPAW 23573.5
I can't figure out why you think that works but it's slower and wrong.
Examine the one above for the solution.
thx
md
*** Sent via Developersdex http://www.examnotes.net ***sql
Wednesday, March 21, 2012
Issues with performance of XQuery on SQL Server 2005
Hi folks,
we are executing the following Xquery on SQLserver 2005.
select
policy_xml.query('/Policy/PolicyApplication/Inuserer/InsurerID'),
policy_xml.query('/Policy/PolicyApplication/Insurer/AccountIdentifier'),
policy_xml.query('/Policy/PolicyApplication/Insurer/Type'),
policy_xml.query('/Policy/PolicyApplication/Insurer/HolderName')
from policyTable
where
policy_xml.exist('/Policy/PolicyApplication/Insurer/PolicyOwner/EntityID[.="E_1"]') = 1
Its taking 50 Secs to search from 10000 records{without indexes}
The table has 3 columns sno,policy_id,policy_xml.
We have primary index on policy_id field and 1 secondary index(path index) on the table.
When we enable the index the query takes 380 secs.
The size of loan_xml column is about 110 Kb for each row. We need to keep the indexes for some more complex update Xqueries. Is there a way out to improve the performance of the XQuery we are using? Please let us also know the reasons of decrease in performance using indexes on the table. Do indexs have any issues related to XQuery performance?
We have an urgent requirement to resolve this issue. Kindly let us know the resolution ASAP.
Please let us know if you require any other information in this regard.
Thanks,
Bhuvanesh
I am not a XML guru to exactly know where the problem might be but want to pass the following link
which talks of some performance techiniques.
http://msdn2.microsoft.com/en-us/library/ms345118(SQL.90).aspx
Regards
AK
|||Hi Bhuvanesh,
From what you said here:
The table has 3 columns sno,policy_id,policy_xml.
We have primary index on policy_id field and 1 secondary index(path index) on the table.
I assume you haven't create XML index on column policy_xml, the DDL statement will look like this
Code Snippet
createprimaryxmlindex p_xml_idx
on policyTable(policy_xml)
go
It should definitely improve your query performance. Please let me know if otherwise.
|||I've similar issue.
Table1 ( Xid , XML_data (xml)) where Xid is primary key and XML_data is of xml datatype.
I've set primary index on XML_data along with the three secondary indexes.
with fillfactor 90, padindex on and sort _in_tempdb is on.
xml_data stores 1000 xml's of size 150 kb each. i'm fetching single xml for particular value and it is taking 20 minutes on server with 2 gb ram.
the query is
SELECT
xml_data.query('/Info/PersonalInfo/Entity[1]/LastName/text()'),
xml_data.query('/Info/PersonalInfo/Entity[1]/FirstName/text()'),
xml_data.query('/Info/PersonalInfo/Entity[1]/Sno/text()'),
xml_data.query('/Info/PersonalInfo/Entity[2]/LastName/text()'),
xml_data.query('/Info/PersonalInfo/Entity[2]/FirstName/text()'),
xml_data.query('/Info/PersonalInfo/Entity[2]/Sno/text()')
FROM Table1
Looking out for valuable help.....
|||Try following query to see if any improvement. I change query() to value() since you seems want to get scalar value, not xml.
Code Snippet
SELECT
x.value('(.[1]/LastName)[1]','varchar(100)'),
x.value('(.[1]/FirstName)[1]','varchar(100)'),
x.value('(.[1]/Sno)[1]','varchar(100)'),
x.value('(.[2]/LastName)[1]','varchar(100)'),
x.value('(.[2]/FirstName)[1]','varchar(100)'),
x.value('(.[2]/Sno)[1]','varchar(100)')
FROM Table1 crossapply xml_data.nodes('/Info/PersonalInfo/Entity')as t(x)
Monday, March 12, 2012
Issue with SQLDataSource and FilterExpression
I'm not sure exactly how the FilterExpression works. I have a sqldatasource, stored procedure, and GridView. My stored procedure basicly a select statement to populate my gridview, it included the fields I want to filter on. In my codebehind file I build a WHERE Clause based on the entries a user makes. Then I add the my FilterExpr variable to the SqlDataSource1.FilterExpression = FilterExpr. My SqlDataSource has a number of control parameters that match the textboxes a users enters into.
My question, I guess is does my stored procedure need the variables matching my controlparameters for my sqldatasource? Or how does this work? My GridView is returning all rows no matter what I enter into the filter textboxes (first, last, etc...)
MY SQLSDATASOURCE
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString%>" SelectCommand="ClientSearch" SelectCommandType="StoredProcedure"> <FilterParameters> <asp:ControlParameter ControlID="SearchLastName" Name="LastName" PropertyName="Text" ConvertEmptyStringToNull="true" /> <asp:ControlParameter ControlID="SearchFirstName" Name="FirstName" PropertyName="Text" ConvertEmptyStringToNull="true" /> <asp:ControlParameter ControlID="SearchEmail" Name="Email" PropertyName="Text" ConvertEmptyStringToNull="true" /> <asp:ControlParameter ControlID="SearchAddress" Name="Address" PropertyName="Text" ConvertEmptyStringToNull="true" /> <asp:ControlParameter ControlID="SearchComment" Name="Comment" PropertyName="Text" ConvertEmptyStringToNull="true" /> </FilterParameters> </asp:SqlDataSource>
MY CODEBEHIND
Dim FilterExprAs StringIf SearchLastName.Text =""And _
SearchFirstName.Text =""And _
SearchEmail.Text =""And _
SearchComment.Text =""And _
SearchAddress.Text =""Then
lblMessage.Text ="You didn't enter any search parameters. Please try Again."
Me.GridViewSearch.DataSourceID = ""
Exit Sub
Else
Me.GridViewSearch.DataSourceID = "SqlDataSource1"
End IfFilterExpr = ""
If SearchLastName.Text <> "" Then
FilterExpr = FilterExpr & "LastNameLike'" & _
SearchLastName.Text &"%" &"' AND "
End IfIf SearchFirstName.Text <> "" Then
FilterExpr = FilterExpr & "FirstNameLike'" & _
SearchFirstName.Text &"%" &"' AND "
End If
If SearchEmail.Text <> "" Then
FilterExpr = FilterExpr & "Like'" & _
SearchEmail.Text &"%" &"' AND "
End If
If SearchComment.Text <> "" Then
FilterExpr = FilterExpr & "[Comments]Like'" & "%" & _
SearchComment.Text &"%" &"' AND "
End If
If SearchAddress.Text <> "" Then
FilterExpr = FilterExpr & "[Address]Like'" & "%" & _
SearchAddress.Text &"%" &"' AND "
End IfIf Right(FilterExpr, 4) = "AND "Then
FilterExpr = Left(FilterExpr, Len(FilterExpr) - 4)
End If
Try
Me.SqlDataSource1.FilterExpression = FilterExpr
Me.SqlDataSource1.DataBind()
Me.GridViewSearch.DataBind()
Me.lblMessage.Text =Me.SqlDataSource1.FilterExpression
Catch objExceptionAs SqlException
Dim objErrorAs SqlError
For Each objErrorIn objException.Errors
Response.Write(objError.Message)
Next
End Try
End Sub
MY SPROC
GOALTER PROCEDURE [dbo].[ClientSearch]ASSELECT C.ClientID, C.FirstName, C.LastName, A.Address, C.Comments, C.EMailFROM tblClient CINNERJOIN tblClientAddresses AON C.ClientID = A.ClientIDRemove all the <FilterParameters> and it should work fine.
Issue with SqlCeParameter (SqlCE 3.1)
Hi,
I have a simple query as follows:
SELECT COUNT(ID) FROM AI_DTREE DT WHERE PARENT =@.pPID AND
CARTRIDGE_ID = @.pCID AND COMMAND =@.pCMD AND OBJECT =@.pObj
Where @.pPID=16700130,@.pCID=43000000,@.pCMD=”=”, and
@.pObj=”the cecum, identified by appendiceal orifice & IC valve”
The filed OBJECT in AI_DTREE is of nvarchar(30)(of course the length of @.pObj is more than 30 in my current query).
I have build the SqlCeCommand sccmd object with the above sql text and the parameters.
returnval = sccmd.ExecuteScalar();
When I execute the above statement I am getting the following error:
ex.Message = "@.pObj : String truncation: max=30, len=55, value='the cecum, identified by appendiceal orifice & IC valve'."
But when I execute the same in Sql Server Management Studio against SqlCE db, it works fine and the result returnval =0.
How to overcome this SqlCeParameter issue?, for me it is difficult to messure the length of the filed before I exeuting the command.
Thanks
G Sreenaiah
Hi Erik,
Thanks for your prompt reply.
I am just executing the above command from my C# code. If I want to ensure the length of the parameter is less than the length of field, then I should go for one more database hit to fetch field’s length first.
This is not the way happening with i) OleDbCommand, OleDbParameter, ii) OracleParameter, OracleCommand
Please let me know if anybody else has an idea how this can be resolved with workout having one more db hit to fetch fields’ length.
Why it is happening only with SqlCeCommand, SqlCeParameter? why not with OracleParameter, OracleCommand ?
Thanks
G Sreenaiah
Issue with SELECT "IN" statement
In Reporting Services I have following query. For some reason query only
picks the FIRST choice for State or Product â?¦ even though user selects
multiple products or states â?¦ what is the issue here? Could you please help
me?
Thank you.
SELECT
DATENAME(MONTH, dbo.fnGetDSDate(ts_time)) AS SubmitMonth,
YEAR(dbo.fnGetDSDate(ts_time)) AS SubmitYear,
s.ts_name
FROM ShowAll INNER JOIN TS_STATE s on newstate = s.id
WHERE
(transitionlabel <> 'Update' OR transitionlabel <> '')
AND ts_productsubsystem IN (@.ProductId)
AND ts_newstate IN (@.StateId)On Jun 4, 10:43 am, ozcan <o...@.discussions.microsoft.com> wrote:
> In Reporting Services I have following query. For some reason query only
> picks the FIRST choice for State or Product ... even though user selects
> multiple products or states ... what is the issue here? Could you please help
> me?
> Thank you.
> SELECT
> DATENAME(MONTH, dbo.fnGetDSDate(ts_time)) AS SubmitMonth,
> YEAR(dbo.fnGetDSDate(ts_time)) AS SubmitYear,
> s.ts_name
> FROM ShowAll INNER JOIN TS_STATE s on newstate = s.id
> WHERE
> (transitionlabel <> 'Update' OR transitionlabel <> '')
> AND ts_productsubsystem IN (@.ProductId)
> AND ts_newstate IN (@.StateId)
You will most likely need to loop through the multi-select report
parameter and insert the values in a temp table (in a query/stored
procedure outside the report) and then use the above query to access
the values in the temp table. Here is a query that should get you
started in looping through the multi-select report parameter values
selected.
--CREATE PROC ParseRSMultiParameterList
DECLARE
@.STATES VARCHAR(MAX)
--AS
DECLARE @.STATEBUFFER VARCHAR(MAX),
@.END_POSITION INT;
--TEST DATA--
SET @.STATES = 'AL,TN,CA,OH';
--TEST DATA--
CREATE TABLE #STATELIST (State char(2));
SET @.STATEBUFFER = @.STATES;
WHILE (LEN(@.STATEBUFFER) > 0)
BEGIN
IF (CHARINDEX(',', @.STATEBUFFER) > 0)
BEGIN
SET @.END_POSITION = CHARINDEX(',', @.STATEBUFFER);
INSERT INTO #STATELIST VALUES (SUBSTRING(@.STATEBUFFER, 1,
(@.END_POSITION - 1)));
END
IF (CHARINDEX(',', @.STATEBUFFER) = 0)
BEGIN
SET @.END_POSITION = LEN(@.STATEBUFFER);
INSERT INTO #STATELIST VALUES (SUBSTRING(@.STATEBUFFER, 1,
(@.END_POSITION + 1)));
END
SET @.STATEBUFFER = RIGHT(@.STATEBUFFER, (LEN(@.STATEBUFFER) -
@.END_POSITION));
END
SELECT
DATENAME(MONTH, dbo.fnGetDSDate(ts_time)) AS SubmitMonth,
YEAR(dbo.fnGetDSDate(ts_time)) AS SubmitYear,
s.ts_name
FROM ShowAll INNER JOIN TS_STATE s on newstate = s.id
WHERE
(transitionlabel <> 'Update' OR transitionlabel <> '')
AND ts_productsubsystem IN (@.ProductId)
AND ts_newstate IN (select State from #STATELIST)
DROP TABLE #STATELIST;
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||You may want to look at the Report, Parameters setting (from layout view) and
see if that parameter has the "multi-value" checkbox selected. If it is not
checked, I would think that the end user would only be able to select one
single parameter, and this does not seem to be the issue - but may want to
check it.
picks the FIRST choice for State or Product â?¦ even though user selects
multiple products or states â?¦ what is the issue here? Could you please help
me?
Thank you.
SELECT
DATENAME(MONTH, dbo.fnGetDSDate(ts_time)) AS SubmitMonth,
YEAR(dbo.fnGetDSDate(ts_time)) AS SubmitYear,
s.ts_name
FROM ShowAll INNER JOIN TS_STATE s on newstate = s.id
WHERE
(transitionlabel <> 'Update' OR transitionlabel <> '')
AND ts_productsubsystem IN (@.ProductId)
AND ts_newstate IN (@.StateId)On Jun 4, 10:43 am, ozcan <o...@.discussions.microsoft.com> wrote:
> In Reporting Services I have following query. For some reason query only
> picks the FIRST choice for State or Product ... even though user selects
> multiple products or states ... what is the issue here? Could you please help
> me?
> Thank you.
> SELECT
> DATENAME(MONTH, dbo.fnGetDSDate(ts_time)) AS SubmitMonth,
> YEAR(dbo.fnGetDSDate(ts_time)) AS SubmitYear,
> s.ts_name
> FROM ShowAll INNER JOIN TS_STATE s on newstate = s.id
> WHERE
> (transitionlabel <> 'Update' OR transitionlabel <> '')
> AND ts_productsubsystem IN (@.ProductId)
> AND ts_newstate IN (@.StateId)
You will most likely need to loop through the multi-select report
parameter and insert the values in a temp table (in a query/stored
procedure outside the report) and then use the above query to access
the values in the temp table. Here is a query that should get you
started in looping through the multi-select report parameter values
selected.
--CREATE PROC ParseRSMultiParameterList
DECLARE
@.STATES VARCHAR(MAX)
--AS
DECLARE @.STATEBUFFER VARCHAR(MAX),
@.END_POSITION INT;
--TEST DATA--
SET @.STATES = 'AL,TN,CA,OH';
--TEST DATA--
CREATE TABLE #STATELIST (State char(2));
SET @.STATEBUFFER = @.STATES;
WHILE (LEN(@.STATEBUFFER) > 0)
BEGIN
IF (CHARINDEX(',', @.STATEBUFFER) > 0)
BEGIN
SET @.END_POSITION = CHARINDEX(',', @.STATEBUFFER);
INSERT INTO #STATELIST VALUES (SUBSTRING(@.STATEBUFFER, 1,
(@.END_POSITION - 1)));
END
IF (CHARINDEX(',', @.STATEBUFFER) = 0)
BEGIN
SET @.END_POSITION = LEN(@.STATEBUFFER);
INSERT INTO #STATELIST VALUES (SUBSTRING(@.STATEBUFFER, 1,
(@.END_POSITION + 1)));
END
SET @.STATEBUFFER = RIGHT(@.STATEBUFFER, (LEN(@.STATEBUFFER) -
@.END_POSITION));
END
SELECT
DATENAME(MONTH, dbo.fnGetDSDate(ts_time)) AS SubmitMonth,
YEAR(dbo.fnGetDSDate(ts_time)) AS SubmitYear,
s.ts_name
FROM ShowAll INNER JOIN TS_STATE s on newstate = s.id
WHERE
(transitionlabel <> 'Update' OR transitionlabel <> '')
AND ts_productsubsystem IN (@.ProductId)
AND ts_newstate IN (select State from #STATELIST)
DROP TABLE #STATELIST;
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||You may want to look at the Report, Parameters setting (from layout view) and
see if that parameter has the "multi-value" checkbox selected. If it is not
checked, I would think that the end user would only be able to select one
single parameter, and this does not seem to be the issue - but may want to
check it.
Friday, March 9, 2012
Issue with ReadNext, ReadPrev
I have an issue with the following sequence.
1) User select all recordsby executing a SqlCeResultSet ( using like % )
2) User selects "Last" by triggering the rset.readlast()
3) User now selects "Next" by triggering the rset.readnext()
4) User now select "Prev" by triggering rset.readprevious()
...
nothing happens...the textboxes do not update their values.
However if the "Prev" button is pressed again ( a second time ) now the textboxes populates the prev record's data and the dataset nav goes well again...
This behaviour is very annoying...any help here?
Thanks in advance!
I would say it’s a bug in SQL CE as exception should’ve been thrown on attempt to read past last record. Please file a bug report on http://connect.microsoft.com/
To make sure your application still works after that is fixed (and to eliminate that effect with existing versions) make sure not to read past last record, e.g. disable"Next" button if user hits "Last" or reaches last record by hitting "Next". That is easy to determine – if Read()/ReadLast() returns false then it’s the last record and “Next” should be disabled. Enable it as user moves back to valid records range.
|||Ilya,
I've found a workaround in my project, without resoriting to enabled/disabled buttons.
I'll try to post the bug
Thanks for your coop.
Gus
|||The bug 13334 in SQL Server CE has been filed to track this issue.
Hope the issue gets resolved soon.
Issue with ReadNext, ReadPrev
I have an issue with the following sequence.
1) User select all recordsby executing a SqlCeResultSet ( using like % )
2) User selects "Last" by triggering the rset.readlast()
3) User now selects "Next" by triggering the rset.readnext()
4) User now select "Prev" by triggering rset.readprevious()
...
nothing happens...the textboxes do not update their values.
However if the "Prev" button is pressed again ( a second time ) now the textboxes populates the prev record's data and the dataset nav goes well again...
This behaviour is very annoying...any help here?
Thanks in advance!
I would say it’s a bug in SQL CE as exception should’ve been thrown on attempt to read past last record. Please file a bug report on http://connect.microsoft.com/
To make sure your application still works after that is fixed (and to eliminate that effect with existing versions) make sure not to read past last record, e.g. disable"Next" button if user hits "Last" or reaches last record by hitting "Next". That is easy to determine – if Read()/ReadLast() returns false then it’s the last record and “Next” should be disabled. Enable it as user moves back to valid records range.
|||Ilya,
I've found a workaround in my project, without resoriting to enabled/disabled buttons.
I'll try to post the bug
Thanks for your coop.
Gus
|||The bug 13334 in SQL Server CE has been filed to track this issue.
Hope the issue gets resolved soon.
Issue with passing a parameter to Stored Procedure using IN keywor
Hello,
A program I've written creates a parameter to be passed to a stored
procedure based on a user's report selection.
A user can select one, two or three locations.
That parameter is used in an IN clause.
"ZMCC.WERKS IN (@.Locations) "
If a user requests a single location, it works just fine. However, when the
user makes multiple selection, the parameter fails.
I've built the parameter so it looks like " '0031', '0032', '0033' " and a
bunch of variations on this, but none of them work
How can I build the parameter and pass it to the stored procedure?
Thx.
Andy JacobsPlenty of suggestions here:
http://www.sommarskog.se/arrays-in-sql.html
"Andy Jacobs" <AndyJacobs@.discussions.microsoft.com> wrote in message
news:C6146921-878E-49BB-A92B-AFB091FDA821@.microsoft.com...
> Hello,
> A program I've written creates a parameter to be passed to a stored
> procedure based on a user's report selection.
> A user can select one, two or three locations.
> That parameter is used in an IN clause.
> "ZMCC.WERKS IN (@.Locations) "
> If a user requests a single location, it works just fine. However, when
> the
> user makes multiple selection, the parameter fails.
> I've built the parameter so it looks like " '0031', '0032', '0033' " and a
> bunch of variations on this, but none of them work
> How can I build the parameter and pass it to the stored procedure?
> Thx.
> Andy Jacobs
>
>|||http://www.aspfaq.com/2248
"Andy Jacobs" <AndyJacobs@.discussions.microsoft.com> wrote in message
news:C6146921-878E-49BB-A92B-AFB091FDA821@.microsoft.com...
> Hello,
> A program I've written creates a parameter to be passed to a stored
> procedure based on a user's report selection.
> A user can select one, two or three locations.
> That parameter is used in an IN clause.
> "ZMCC.WERKS IN (@.Locations) "
> If a user requests a single location, it works just fine. However, when
> the
> user makes multiple selection, the parameter fails.
> I've built the parameter so it looks like " '0031', '0032', '0033' " and a
> bunch of variations on this, but none of them work
> How can I build the parameter and pass it to the stored procedure?
> Thx.
> Andy Jacobs
>
>|||This one works for me
set @.StrType = ''''+ replace(@.StrType,',',''',''')+''''
Declare @.SQL varchar(5000)
Set @.SQL=
'Select ShipName AS [Cust Name],Reference,ReqNo,CorpName AS [Name],
ReqDate AS [Req Date],RptType AS [Rpt Type],OrderNo AS [Order No],Fee from
#Confirm WHERE RptType IN(' + @.StrType + ')'
--print @.sql
Exec(@.SQL)
END
"Andy Jacobs" <AndyJacobs@.discussions.microsoft.com> wrote in message
news:C6146921-878E-49BB-A92B-AFB091FDA821@.microsoft.com...
> Hello,
> A program I've written creates a parameter to be passed to a stored
> procedure based on a user's report selection.
> A user can select one, two or three locations.
> That parameter is used in an IN clause.
> "ZMCC.WERKS IN (@.Locations) "
> If a user requests a single location, it works just fine. However, when
the
> user makes multiple selection, the parameter fails.
> I've built the parameter so it looks like " '0031', '0032', '0033' " and a
> bunch of variations on this, but none of them work
> How can I build the parameter and pass it to the stored procedure?
> Thx.
> Andy Jacobs
>
>|||SQL is treating the passed variable as a single string, instead of
interpreting the string as a set.
What you will need to do in your stored procedure is parse the string and
insert the values into a temporary table. Then reference that temporary tabl
e
in your select statement with the IN clause.
See http://www.sommarskog.se/arrays-in-sql.html
"Andy Jacobs" wrote:
> Hello,
> A program I've written creates a parameter to be passed to a stored
> procedure based on a user's report selection.
> A user can select one, two or three locations.
> That parameter is used in an IN clause.
> "ZMCC.WERKS IN (@.Locations) "
> If a user requests a single location, it works just fine. However, when th
e
> user makes multiple selection, the parameter fails.
> I've built the parameter so it looks like " '0031', '0032', '0033' " and a
> bunch of variations on this, but none of them work
> How can I build the parameter and pass it to the stored procedure?
> Thx.
> Andy Jacobs
>
>
A program I've written creates a parameter to be passed to a stored
procedure based on a user's report selection.
A user can select one, two or three locations.
That parameter is used in an IN clause.
"ZMCC.WERKS IN (@.Locations) "
If a user requests a single location, it works just fine. However, when the
user makes multiple selection, the parameter fails.
I've built the parameter so it looks like " '0031', '0032', '0033' " and a
bunch of variations on this, but none of them work
How can I build the parameter and pass it to the stored procedure?
Thx.
Andy JacobsPlenty of suggestions here:
http://www.sommarskog.se/arrays-in-sql.html
"Andy Jacobs" <AndyJacobs@.discussions.microsoft.com> wrote in message
news:C6146921-878E-49BB-A92B-AFB091FDA821@.microsoft.com...
> Hello,
> A program I've written creates a parameter to be passed to a stored
> procedure based on a user's report selection.
> A user can select one, two or three locations.
> That parameter is used in an IN clause.
> "ZMCC.WERKS IN (@.Locations) "
> If a user requests a single location, it works just fine. However, when
> the
> user makes multiple selection, the parameter fails.
> I've built the parameter so it looks like " '0031', '0032', '0033' " and a
> bunch of variations on this, but none of them work
> How can I build the parameter and pass it to the stored procedure?
> Thx.
> Andy Jacobs
>
>|||http://www.aspfaq.com/2248
"Andy Jacobs" <AndyJacobs@.discussions.microsoft.com> wrote in message
news:C6146921-878E-49BB-A92B-AFB091FDA821@.microsoft.com...
> Hello,
> A program I've written creates a parameter to be passed to a stored
> procedure based on a user's report selection.
> A user can select one, two or three locations.
> That parameter is used in an IN clause.
> "ZMCC.WERKS IN (@.Locations) "
> If a user requests a single location, it works just fine. However, when
> the
> user makes multiple selection, the parameter fails.
> I've built the parameter so it looks like " '0031', '0032', '0033' " and a
> bunch of variations on this, but none of them work
> How can I build the parameter and pass it to the stored procedure?
> Thx.
> Andy Jacobs
>
>|||This one works for me
set @.StrType = ''''+ replace(@.StrType,',',''',''')+''''
Declare @.SQL varchar(5000)
Set @.SQL=
'Select ShipName AS [Cust Name],Reference,ReqNo,CorpName AS [Name],
ReqDate AS [Req Date],RptType AS [Rpt Type],OrderNo AS [Order No],Fee from
#Confirm WHERE RptType IN(' + @.StrType + ')'
--print @.sql
Exec(@.SQL)
END
"Andy Jacobs" <AndyJacobs@.discussions.microsoft.com> wrote in message
news:C6146921-878E-49BB-A92B-AFB091FDA821@.microsoft.com...
> Hello,
> A program I've written creates a parameter to be passed to a stored
> procedure based on a user's report selection.
> A user can select one, two or three locations.
> That parameter is used in an IN clause.
> "ZMCC.WERKS IN (@.Locations) "
> If a user requests a single location, it works just fine. However, when
the
> user makes multiple selection, the parameter fails.
> I've built the parameter so it looks like " '0031', '0032', '0033' " and a
> bunch of variations on this, but none of them work
> How can I build the parameter and pass it to the stored procedure?
> Thx.
> Andy Jacobs
>
>|||SQL is treating the passed variable as a single string, instead of
interpreting the string as a set.
What you will need to do in your stored procedure is parse the string and
insert the values into a temporary table. Then reference that temporary tabl
e
in your select statement with the IN clause.
See http://www.sommarskog.se/arrays-in-sql.html
"Andy Jacobs" wrote:
> Hello,
> A program I've written creates a parameter to be passed to a stored
> procedure based on a user's report selection.
> A user can select one, two or three locations.
> That parameter is used in an IN clause.
> "ZMCC.WERKS IN (@.Locations) "
> If a user requests a single location, it works just fine. However, when th
e
> user makes multiple selection, the parameter fails.
> I've built the parameter so it looks like " '0031', '0032', '0033' " and a
> bunch of variations on this, but none of them work
> How can I build the parameter and pass it to the stored procedure?
> Thx.
> Andy Jacobs
>
>
Wednesday, March 7, 2012
Issue with Export to CSV
Dear Anyone,
Whenever we export reports to CSV, it seems that the column headers in the CSV fileb are the actual column names in the select statement we used in the data set and not the column names that are in the report.
Is there a way to reflect the column names in the report to the CSV file rather than the actual physical column name in the report?
Thanks,
JosephAs there is no explicit binding in RDL between the data columns and the header columns, CSV export uses the names of the controls in the export. You can either change the textbox name or you can override the name using the DataElementName property (on the data tab of the textbox properties dialog). This is just like the XML data output, described here: http://msdn2.microsoft.com/en-us/library/ms156020(en-US,SQL.90).aspx. You can also exclude them here (DataElementOutput).
I have asked the documentation people to update the CSV topic.
Issue with cursor
Hi
I have created a cursor with the following syntax: "DECLARE costs_cursor CURSOR SCROLL DYNAMIC FOR SELECT RegNumber, FirstName, LastName, Assessment, Catering, Travel, Accommodation, Other from dbo.T_Course_Data ORDER BY LastName OPEN costs_cursor" which works.
What I don't understand is why, when I attempt to update a value in the cursor, irrespective of whether I use the 'FOR UPDATE' option or not, I get the error message to the effect that the cursor cannot be updated because it is READ ONLY. Clearly (to my mind anyway) the cursor wasn't created as read only. My update statement is "Update dbo.T_Course_Data set Assessment='222' WHERE CURRENT OF costs_cursor"
The odd thing is I have another cursor in my app using the exact same statements and it doesn't give this error.
Please help if you can.
Neil
I beleive, the answer's here: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_con_07_66sz.asp|||The link in the other post explains the cursor conversions. Below is the SQL Server 2005 Books Online topic link:
http://msdn2.microsoft.com/en-us/library/ms190641.aspx
You can use the TYPE_WARNING option in the DECLARE CURSOR statement to detect these conversions in SQL Server 2005. See link below for more details:
http://msdn2.microsoft.com/en-us/library/ms180169.aspx
The best thing will be however to eliminate the use of cursor altogether to get better performance, cleaner code and maintainability. So post an example of what you are doing with the cursor and it will be easier to suggest a set-based solution.
|||Hi
Thanks. Your previous answers helped me find the answer. I didn't have a unique index on the table and adding this has solved the issue.
Regards
Neil
Issue with CONTAINSTABLE statement
I am using the following query to search all columns in the 'dashboard'
table for the value 'Meets':
table for the value 'Meets':
SELECT * from dashboard AS FT_TBL INNER JOIN CONTAINSTABLE(dashboard,*,
'meets') AS KEY_TBL ON FT_TBL.employee = KEY_TBL.[KEY]
I have multiple records that contain the word 'Meets', but none are
showing up as a result of this query. Any ideas?
Also, anytime I use a space in my search condition (Meets Expectations
instead of Meets) I am getting an error:
Syntax error occurred near 'Expectations'. Expected '' in search
condition 'Meets Expectations'.
Any ideas?
Thanks in advance.GAH.. Had not run a start_full for the index. All is working now.
Thanks!
Nate wrote:
Quote:
Originally Posted by
I am using the following query to search all columns in the 'dashboard'
table for the value 'Meets':
>
SELECT * from dashboard AS FT_TBL INNER JOIN CONTAINSTABLE(dashboard,*,
'meets') AS KEY_TBL ON FT_TBL.employee = KEY_TBL.[KEY]
>
I have multiple records that contain the word 'Meets', but none are
showing up as a result of this query. Any ideas?
>
Also, anytime I use a space in my search condition (Meets Expectations
instead of Meets) I am getting an error:
Syntax error occurred near 'Expectations'. Expected '' in search
condition 'Meets Expectations'.
>
Any ideas?
>
Thanks in advance.
Friday, February 24, 2012
Issue selecting two rows from two columns
I'm trying to select two rows from two columns and while I can select the columns, the selection of multiple rows is not falling into place. I can pull one row into the report, but not two.
This is what I'm writing
SELECT name, number FROM table
WHERE name="variable1',''variable2'
Something is occurring around the , between variable1 and variable2. I know I doing something wrong, but can't figure it out. I can pull variable1 by itself just fine.
The specific error is:
sql: errorYou have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' 'variable2' at line 2
number: 1064
Any ideas would be greatly appreciated. I tried searching and could not find an answer to this problem.Found the answer
SELECT name, number FROM table
WHERE name IN('var1', 'var2', 'var3')|||SELECT name, number FROM table
WHERE name in('variable1','variable2')
Monday, February 20, 2012
Issolation level
When on Oracle in one transaction I modify data when I select data on other transaction I get old data (until commit on first transaction)
Using MS SQL I must wait or I get (dirty read) modified values
Example:
Transaction no 1:
begin transaction
update ckk_klienci set kl_skrot = 'KLINTIN' where kl_id = 300000001
-- commit
Transaction no2:
select kl_skrot from ckk_klienci where kl_id = 300000001
Until there is no commit on Transaction no 1 transaction no 2 waits (gives no answer) lowering issolation level I can get new value
Is it possible to configure MS SQL as it works like Oracle
Thanks for halp
I want to know; I don't want to say that this is wrong; I just want to know if it is possibleRE [Quote][Size=1]
When on Oracle in one transaction I modify data when I select data on other transaction I get old data (until commit on first transaction)
Using MS SQL I must wait or I get (dirty read) modified values
Example:
Transaction no 1:
begin transaction
update ckk_klienci set kl_skrot = 'KLINTIN' where kl_id = 300000001
-- commit
Transaction no2:
select kl_skrot from ckk_klienci where kl_id = 300000001
Until there is no commit on Transaction no 1 transaction no 2 waits (gives no answer) lowering issolation level I can get new value
Is it possible to configure MS SQL as it works like Oracle
Thanks for halp
I want to know; I don't want to say that this is wrong; I just want to know if it is possible
[\Quote][\Size]
Q1 [Is it possible to configure MS SQL as it works like Oracle?]
A1 Obviously they are different products; however you can certainly control transaction isolation levels as appropriate / needed. For example:
Set Transaction Isolation Level Read Committed
Using MS SQL I must wait or I get (dirty read) modified values
Example:
Transaction no 1:
begin transaction
update ckk_klienci set kl_skrot = 'KLINTIN' where kl_id = 300000001
-- commit
Transaction no2:
select kl_skrot from ckk_klienci where kl_id = 300000001
Until there is no commit on Transaction no 1 transaction no 2 waits (gives no answer) lowering issolation level I can get new value
Is it possible to configure MS SQL as it works like Oracle
Thanks for halp
I want to know; I don't want to say that this is wrong; I just want to know if it is possibleRE [Quote][Size=1]
When on Oracle in one transaction I modify data when I select data on other transaction I get old data (until commit on first transaction)
Using MS SQL I must wait or I get (dirty read) modified values
Example:
Transaction no 1:
begin transaction
update ckk_klienci set kl_skrot = 'KLINTIN' where kl_id = 300000001
-- commit
Transaction no2:
select kl_skrot from ckk_klienci where kl_id = 300000001
Until there is no commit on Transaction no 1 transaction no 2 waits (gives no answer) lowering issolation level I can get new value
Is it possible to configure MS SQL as it works like Oracle
Thanks for halp
I want to know; I don't want to say that this is wrong; I just want to know if it is possible
[\Quote][\Size]
Q1 [Is it possible to configure MS SQL as it works like Oracle?]
A1 Obviously they are different products; however you can certainly control transaction isolation levels as appropriate / needed. For example:
Set Transaction Isolation Level Read Committed
isql query output issue
Hi
I am planning to get a report out from isql using command
c:\> isql -Usa -P -d master -i c:\l.sql -o c:\op.lst
c:\> type l.sql
select * from sysobjects;
In op.lst I see 3 blank lines for every line of the output. How to avoid this.
I tried by creating a job to avoid this but there I found the first line of
the output says this
Job 'TSQL_Job_1' : Step 1, 'Step 1' : Began Executing 2005-02-14 14:54:16
---------
Please share with me how to avoid this unncessary data in report.
Thanks
MangeshHi
Thee are 3 blank lines, but that is the spacing for the column 'name'. It is
of datatype sysname, char(128).
Regards
Mike
"Mangesh Deshpande" wrote:
> Hi
> I am planning to get a report out from isql using command
> c:\> isql -Usa -P -d master -i c:\l.sql -o c:\op.lst
> c:\> type l.sql
> select * from sysobjects;
> In op.lst I see 3 blank lines for every line of the output. How to avoid this.
> I tried by creating a job to avoid this but there I found the first line of
> the output says this
>
> Job 'TSQL_Job_1' : Step 1, 'Step 1' : Began Executing 2005-02-14 14:54:16
>
>
> ---------
>
> Please share with me how to avoid this unncessary data in report.
> Thanks
> Mangesh|||Hi
Thanks for the reply. How do we get rid of this space. Is there a way to
this?
"Mangesh Deshpande" wrote:
> Hi
> I am planning to get a report out from isql using command
> c:\> isql -Usa -P -d master -i c:\l.sql -o c:\op.lst
> c:\> type l.sql
> select * from sysobjects;
> In op.lst I see 3 blank lines for every line of the output. How to avoid this.
> I tried by creating a job to avoid this but there I found the first line of
> the output says this
>
> Job 'TSQL_Job_1' : Step 1, 'Step 1' : Began Executing 2005-02-14 14:54:16
>
>
> ---------
>
> Please share with me how to avoid this unncessary data in report.
> Thanks
> Mangesh|||Hi
Don't use SELECT *, it is a bad practice. Rather specify each column.
RTRIM will trim trailing spaces.
SELECT RTRIM([Name]) AS MyName, ........
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote in
message news:AA4199DE-C15A-4507-BB15-C1BD1D835F3B@.microsoft.com...
> Hi
> Thanks for the reply. How do we get rid of this space. Is there a way to
> this?
> "Mangesh Deshpande" wrote:
> > Hi
> >
> > I am planning to get a report out from isql using command
> > c:\> isql -Usa -P -d master -i c:\l.sql -o c:\op.lst
> > c:\> type l.sql
> > select * from sysobjects;
> >
> > In op.lst I see 3 blank lines for every line of the output. How to avoid
this.
> >
> > I tried by creating a job to avoid this but there I found the first line
of
> > the output says this
> >
> >
> > Job 'TSQL_Job_1' : Step 1, 'Step 1' : Began Executing 2005-02-14
14:54:16
> >
> >
> >
> >
> ----
----
---
> >
> >
> > Please share with me how to avoid this unncessary data in report.
> >
> > Thanks
> > Mangesh
I am planning to get a report out from isql using command
c:\> isql -Usa -P -d master -i c:\l.sql -o c:\op.lst
c:\> type l.sql
select * from sysobjects;
In op.lst I see 3 blank lines for every line of the output. How to avoid this.
I tried by creating a job to avoid this but there I found the first line of
the output says this
Job 'TSQL_Job_1' : Step 1, 'Step 1' : Began Executing 2005-02-14 14:54:16
---------
Please share with me how to avoid this unncessary data in report.
Thanks
MangeshHi
Thee are 3 blank lines, but that is the spacing for the column 'name'. It is
of datatype sysname, char(128).
Regards
Mike
"Mangesh Deshpande" wrote:
> Hi
> I am planning to get a report out from isql using command
> c:\> isql -Usa -P -d master -i c:\l.sql -o c:\op.lst
> c:\> type l.sql
> select * from sysobjects;
> In op.lst I see 3 blank lines for every line of the output. How to avoid this.
> I tried by creating a job to avoid this but there I found the first line of
> the output says this
>
> Job 'TSQL_Job_1' : Step 1, 'Step 1' : Began Executing 2005-02-14 14:54:16
>
>
> ---------
>
> Please share with me how to avoid this unncessary data in report.
> Thanks
> Mangesh|||Hi
Thanks for the reply. How do we get rid of this space. Is there a way to
this?
"Mangesh Deshpande" wrote:
> Hi
> I am planning to get a report out from isql using command
> c:\> isql -Usa -P -d master -i c:\l.sql -o c:\op.lst
> c:\> type l.sql
> select * from sysobjects;
> In op.lst I see 3 blank lines for every line of the output. How to avoid this.
> I tried by creating a job to avoid this but there I found the first line of
> the output says this
>
> Job 'TSQL_Job_1' : Step 1, 'Step 1' : Began Executing 2005-02-14 14:54:16
>
>
> ---------
>
> Please share with me how to avoid this unncessary data in report.
> Thanks
> Mangesh|||Hi
Don't use SELECT *, it is a bad practice. Rather specify each column.
RTRIM will trim trailing spaces.
SELECT RTRIM([Name]) AS MyName, ........
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote in
message news:AA4199DE-C15A-4507-BB15-C1BD1D835F3B@.microsoft.com...
> Hi
> Thanks for the reply. How do we get rid of this space. Is there a way to
> this?
> "Mangesh Deshpande" wrote:
> > Hi
> >
> > I am planning to get a report out from isql using command
> > c:\> isql -Usa -P -d master -i c:\l.sql -o c:\op.lst
> > c:\> type l.sql
> > select * from sysobjects;
> >
> > In op.lst I see 3 blank lines for every line of the output. How to avoid
this.
> >
> > I tried by creating a job to avoid this but there I found the first line
of
> > the output says this
> >
> >
> > Job 'TSQL_Job_1' : Step 1, 'Step 1' : Began Executing 2005-02-14
14:54:16
> >
> >
> >
> >
> ----
----
---
> >
> >
> > Please share with me how to avoid this unncessary data in report.
> >
> > Thanks
> > Mangesh
Subscribe to:
Posts (Atom)