Integrated Security: column

Showing posts with label column. Show all posts

Wednesday, March 28, 2012

Intermittent ORDER BY not functionning

Hi!
I have this query running well, except that when
executing some times, the result change and doesn't start
with the urgence24 column order as requested!!! It's
really strange. Does somenone can help me or tell me where
to find?
Does the SELECT INTO with temporary table can make a
problem?
SELECT noLIFNR,
noLIFNR [LIFNR],
raisonSociale,
CASE urgence24 WHEN 1 THEN 'exclamation.gif'
ELSE 'spacer.gif' END AS urgence24,
indRegionRespVentes + '-' + telRespVentes AS
telRespVentes,
respVentes,
indRegion1 + '-' + tel1 AS tel1,
ville,
nomRegion,
nomMrc
INTO #Temp
FROM vFournisseurs
WHERE actif = 1
and version = 1
ORDER BY urgence24 ASC,
raisonSociale ASC,
1 ASC
select * from #temp
Thanks in advance
DavidDavid Parenteau wrote:
> Hi!
> I have this query running well, except that when
> executing some times, the result change and doesn't start
> with the urgence24 column order as requested!!! It's
> really strange. Does somenone can help me or tell me where
> to find?
> Does the SELECT INTO with temporary table can make a
> problem?
Yes. While ORDER BY will order the results selected, the insertion order
into the temp table is not guaranteed.
<snip>
> ORDER BY urgence24 ASC,
> raisonSociale ASC,
> 1 ASC
> select * from #temp
The only way to guarantee a particular order when selecting rows is to use
an ORDER BY clause in the SELECT statement that retrieves the rows:
select * from #temp
ORDER BY urgence24 ASC,
raisonSociale ASC,
1 ASC
HTH,
Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"|||IIRC, your technique is not guaranteed. There is a thread with the subject
"order by and identity" that recently discussed various techniques for doing
this, one of which is currently "guaranteed" according to MS.
"David Parenteau" <david.parenteau@.compuware.com> wrote in message
news:0d8401c503ad$94e0e780$a601280a@.phx.gbl...
> Hi!
> I have this query running well, except that when
> executing some times, the result change and doesn't start
> with the urgence24 column order as requested!!! It's
> really strange. Does somenone can help me or tell me where
> to find?
> Does the SELECT INTO with temporary table can make a
> problem?
> SELECT noLIFNR,
> noLIFNR [LIFNR],
> raisonSociale,
> CASE urgence24 WHEN 1 THEN 'exclamation.gif'
> ELSE 'spacer.gif' END AS urgence24,
> indRegionRespVentes + '-' + telRespVentes AS
> telRespVentes,
> respVentes,
> indRegion1 + '-' + tel1 AS tel1,
> ville,
> nomRegion,
> nomMrc
> INTO #Temp
> FROM vFournisseurs
> WHERE actif = 1
> and version = 1
> ORDER BY urgence24 ASC,
> raisonSociale ASC,
> 1 ASC
> select * from #temp
>
> Thanks in advance
> David|||Wow... I have read some part of that thread, but my
example does'nt use a IDENTITY column. How it applies the
same way? Does the problem is the SELECT INTO that don't
insert the rows after the ORDER BY occurs?
I need to give an answer to my client.
Thanks!
David

>--Original Message--
>IIRC, your technique is not guaranteed. There is a
thread with the subject
>"order by and identity" that recently discussed various
techniques for doing
>this, one of which is currently "guaranteed" according to
MS.
>"David Parenteau" <david.parenteau@.compuware.com> wrote
in message
>news:0d8401c503ad$94e0e780$a601280a@.phx.gbl...
start
where
>
>.
>|||> Does the problem is the SELECT INTO that don't
> insert the rows after the ORDER BY occurs?
A table should be treated as an unordered set of rows. You need to specify
ORDER BY when selecting *from* the table in order to guarantee a particular
sequence. Insertion sequence isn't relevant in this case.
Hope this helps.
Dan Guzman
SQL Server MVP
"David Parenteau" <david.parenteau@.compuware.com> wrote in message
news:0dcc01c503b2$c5b96210$a601280a@.phx.gbl...
> Wow... I have read some part of that thread, but my
> example does'nt use a IDENTITY column. How it applies the
> same way? Does the problem is the SELECT INTO that don't
> insert the rows after the ORDER BY occurs?
> I need to give an answer to my client.
> Thanks!
> David
>
> thread with the subject
> techniques for doing
> MS.
> in message
> start
> where|||My understanding is that the guaranteed technique involves the creation of a
temp table with an identity column and the use of an insert/select
statement. However, the topic of the referenced thread is not exactly the
same as the issue you are experiencing (now that I read the post a bit more
carefully). Bob's post is actually more appropriate.
I don't particularly understand why you use a temp table when you can simply
return the result set generated select/into statement (just omit the "into"
part). In any event, you should always consider a table as an unordered set
of rows and that a select statement without an order by clause is never
guaranteed to return the rows in any consistent order. I believe that is
your fundamental flaw. In addition, I also find fault with the use of the
asterisk ("*"), especially if this is production code. Both of these issues
are frequently discussed in the newsgroup.
"David Parenteau" <david.parenteau@.compuware.com> wrote in message
news:0dcc01c503b2$c5b96210$a601280a@.phx.gbl...
> Wow... I have read some part of that thread, but my
> example does'nt use a IDENTITY column. How it applies the
> same way? Does the problem is the SELECT INTO that don't
> insert the rows after the ORDER BY occurs?
> I need to give an answer to my client.
> Thanks!
> David
>
> thread with the subject
> techniques for doing
> MS.
> in message
> start
> where|||David,
Your concern is not with the order in which rows
are inserted into #temp. Your concern is with the order
of rows in the result set of the select query:
SELECT * FROM #temp
At least that's what it sounds like - sometimes you see
the resulting rows in an order you don't want.
The easy solution, and the only guaranteed solution, if
you need to see results in a particular order, is to add
an ORDER BY clause. You will always get the order
you want if you execute
SELECT * FROM #temp
ORDER BY urgence24, raisonSociale, noLIFNR
The rows in a table are not in any order - they are
more like apples in a bag. They don't automatically
fall out of the bag in the exact order they went into
the bag.
In this particular case, the order in which the
rows were inserted is irrelevant, since you have no
IDENTITY column to record that order. The issue
here is the order or results from your SELECT query,
and that can be controlled with ORDER BY. Parallelism,
indexes, concurrency, and many other factors can change
the order in which the rows are returned, if there is no
ORDER BY clause.
Steve Kass
Drew University
David Parenteau wrote:
>Wow... I have read some part of that thread, but my
>example does'nt use a IDENTITY column. How it applies the
>same way? Does the problem is the SELECT INTO that don't
>insert the rows after the ORDER BY occurs?
>I need to give an answer to my client.
>Thanks!
>David
>
>
>thread with the subject
>
>techniques for doing
>
>MS.
>
>in message
>
>start
>
>where
>|||Ok, thanks for the *, I practice this too. The code here
was only the first part of all my code.
That table is used later to get only the record in the
middle of the entire result set (a particular page).
I need the order, as adviced here, only after that SELECT
INTO, to get always the same rows in the same page (row
460 to 469 to get the page 46 for example)
Thanks a lot of your help all you guys!

>--Original Message--
>My understanding is that the guaranteed technique
involves the creation of a
>temp table with an identity column and the use of an
insert/select
>statement. However, the topic of the referenced thread
is not exactly the
>same as the issue you are experiencing (now that I read
the post a bit more
>carefully). Bob's post is actually more appropriate.
>I don't particularly understand why you use a temp table
when you can simply
>return the result set generated select/into statement
(just omit the "into"
>part). In any event, you should always consider a table
as an unordered set
>of rows and that a select statement without an order by
clause is never
>guaranteed to return the rows in any consistent order. I
believe that is
>your fundamental flaw. In addition, I also find fault
with the use of the
>asterisk ("*"), especially if this is production code.
Both of these issues
>are frequently discussed in the newsgroup.
>"David Parenteau" <david.parenteau@.compuware.com> wrote
in message
>news:0dcc01c503b2$c5b96210$a601280a@.phx.gbl...|||Well that clarifies things. Usually questions about a particular technique
generate "better" answers when the reason driving its usage is included.
Perhaps you will find the following link useful:
"David Parenteau" <david.parenteau@.compuware.com> wrote in message
news:072801c503b7$34654e00$a401280a@.phx.gbl...
> Ok, thanks for the *, I practice this too. The code here
> was only the first part of all my code.
> That table is used later to get only the record in the
> middle of the entire result set (a particular page).
> I need the order, as adviced here, only after that SELECT
> INTO, to get always the same rows in the same page (row
> 460 to 469 to get the page 46 for example)
> Thanks a lot of your help all you guys!
>
> involves the creation of a
> insert/select
> is not exactly the
> the post a bit more
> when you can simply
> (just omit the "into"
> as an unordered set
> clause is never
> believe that is
> with the use of the
> Both of these issues
> in message
>|||Thanks, I will go to that page :)
David
>--Original Message--
>slipped ... and that link is
http://www.aspfaq.com/show.asp?id=2120
>"Scott Morris" <bogus@.bogus.com> wrote in message
>news:eKW$No7AFHA.1400@.TK2MSFTNGP11.phx.gbl...
particular
>technique
usage is included.
in message
here
SELECT
(row
thread
read
table
table
by
order. I
code.
wrote
>
>.
>

Friday, March 23, 2012

Interesting SQLProblem using joins

Using Join when 2 columns in one table point to 1 column in another table

I have spent hours on this problem and no research has turned in my favour. Does anyone have any examples they can put forward for me.

I am really desperate to sort this out, and any help will be greatly appreciated.

Thanks
ShaunAre you meaning col1.value + col2.value from table1 is the equal to colx.value of table2
P

Monday, March 19, 2012

Intercept Result

Hi,

I am trying to write a system (Stored Proc, View, CLR Proc, ?), where if you query a specific table and it returns data from only 1 column for the submitted SELECT statement then substitute data obtained from an external web service.

Does anyone have any suggestions about how best to implement this? I have searched through the BOL but nothing directly deals with what I want to do.

Thanks,

Blair

P.S. I am using SQL SERVER 2005 ENT Edition with everything available.

Hi,

I'm not very clear with your question.

For substituting a result, you can use CASE within your SELECT statement.

Assuming Table1 table with Col1 as column, and a scalar valued function CLRFunction() which will call a web service,

SELECT CASE Col1
WHEN 'abc' THEN CLRFunction(1)
WHEN 'def' THEN CLRFunction(2)
END 'Col1'
FROM Table1

Here 'abc' and 'def' are the values stored in Col1 of Table1

Hope this answers for your query, else please explain your requirement with some more detail.

Regards

Babu

|||

Thanks that is exactly what I was looking for. I now know what to do.

blair

|||

Hello...

I think calling a webservie inside a SP is not a very good behavior. You should write your SPs so that they finish as fast as possible. If you try to access an outside resource you never know how long it will take(and a webservice is even worse). You should consider moving this logic into your application. Also you need to access external resources in that function, so you have to declare it as "unsafe" which will also have some implications on security...

Also it will be more complicated to make your app responsive while your code is waiting on the SQL Server. Calling a webservice async is very easy... Calling SQL is a little more work (but still no big deal)

Interactive Sorting/Execution of query

Does clicking interactive sort button in a column reporting services 2005 result re-execution of the query.

Or will it just re-print the rendered data in the layout and so perform better in comparison to the implementation which can be done using drill down to same report with the help of some extra parameters

Priyank

If the user session hasn't expired, interactive sorting doesn't result in re-executing of the query. The server simply re-uses the cached report.

|||I'd definately prefer the cache over generating another report. But if you feel so inclined, try both and time them to see which one is faster.

|||

Thanks, I tried this, rendered data got re-printed without execution of query.

Interactive Sorting -prevent group from collapsing

is there a way to configure interactive sorting so that a group that has
been expanded does not get hidden again when the sort arrow in a column
heading is clicked?
TIA
DeanHi Dean,
Have you got any resolution? If yes please share...I am also facing same
problem.
Thanks
-Shailesh
"Dean" wrote:
> is there a way to configure interactive sorting so that a group that has
> been expanded does not get hidden again when the sort arrow in a column
> heading is clicked?
> TIA
> Dean
>
>|||I'm looking for the answer to the same question.
--
FionaDM
"Shailesh K" wrote:
> Hi Dean,
> Have you got any resolution? If yes please share...I am also facing same
> problem.
> Thanks
> -Shailesh
> "Dean" wrote:
> > is there a way to configure interactive sorting so that a group that has
> > been expanded does not get hidden again when the sort arrow in a column
> > heading is clicked?
> >
> > TIA
> > Dean
> >
> >
> >|||I guess this is a flaw since there has been no MSFT replies since the post
started. MSFT, any solution or workaround to this?

Interactive sorting of a list

I have a list data region that has column headings outside the list. I would like to be able to click a column heading and sort the list. However I cannot do this because the scope of the header can't access the list. Has anyone sorted a list by clicking textbox column headings? Please help.

Hi Patrick-

In general, when applying a user sort, you can sort items that are at the same scope, or are in a child scope. You might try setting the textbox to sort on the data set wihch the list is using, and then set the sort expression scope to details. If you are using RS 2005, I've included a sample RDL below. Just copy the text to a file, rename the extension to .rdl and open it in report designer.

-Jon

<?xml version="1.0" encoding="utf-8"?>

</ConnectionProperties>

<rd:DataSourceID>8d7185f8-cc23-497e-a4a0-cb6c5ec79844</rd:DataSourceID>

</DataSource>

</DataSources>

<rd:DrawGrid>true</rd:DrawGrid>

<rd:SnapToGrid>true</rd:SnapToGrid>

<Body>

<rd:DefaultName>textbox1</rd:DefaultName>

<Style>

</Style>

<SortTarget>DataSet1</SortTarget>

<SortExpression>=Count(Fields!Col.Value, "DataSet1")</SortExpression>

</UserSort>

</Textbox>

<rd:DefaultName>Col</rd:DefaultName>

<Style>

</Style>

<Value>=Fields!Col.Value</Value>

</Textbox>

</ReportItems>

<DataSetName>DataSet1</DataSetName>

</List>

</ReportItems>

</Body>

<rd:ReportID>50318574-728b-4b7c-a2f9-82ab09948b39</rd:ReportID>

<Query>

<DataSourceName>DataSource1</DataSourceName>

</Query>

<rd:TypeName>System.String</rd:TypeName>

</Field>

</Fields>

</DataSet>

</DataSets>

</Report>

Monday, March 12, 2012

Interactive sort not working with snapshot

I'm using SSRS 2005. I have a pretty simple report: it's a table with an
optional date parameter. I have one column designated to allow interactive
sort. When testing the report everything works fine. When I change the report
execution to snapshot, the interactive sort no longer works. The icon will
sometimes change to make it appear as if it is sorted, but the data doesn't
actually get sorted.
Is there some config or option I've missed?
TIAThe interactive sort also does not appear to work when the report is cached.
I know this feature was new for 2005. I'm just wondering if this behavior is
by design, a bug, or a config setting that I'm missing.
Any info appreciated...
"Denise" wrote:
> I'm using SSRS 2005. I have a pretty simple report: it's a table with an
> optional date parameter. I have one column designated to allow interactive
> sort. When testing the report everything works fine. When I change the report
> execution to snapshot, the interactive sort no longer works. The icon will
> sometimes change to make it appear as if it is sorted, but the data doesn't
> actually get sorted.
> Is there some config or option I've missed?
> TIA|||SP1 appears to have fixed this bug. After applying the service pack, all
interactive sorting works, even when the report is executed as a snapshot.
"Denise" wrote:
> The interactive sort also does not appear to work when the report is cached.
> I know this feature was new for 2005. I'm just wondering if this behavior is
> by design, a bug, or a config setting that I'm missing.
> Any info appreciated...
> "Denise" wrote:
> > I'm using SSRS 2005. I have a pretty simple report: it's a table with an
> > optional date parameter. I have one column designated to allow interactive
> > sort. When testing the report everything works fine. When I change the report
> > execution to snapshot, the interactive sort no longer works. The icon will
> > sometimes change to make it appear as if it is sorted, but the data doesn't
> > actually get sorted.
> >
> > Is there some config or option I've missed?
> >
> > TIA

Interactive Sort in Matrix

Hi,

I making a report using Matrix with 2 row groups and 1 column group. The group names are:

Row : CompanyName, TotalUsers

Column: UserType

Detail Group has a count of number of user in each type.

I want to use interactive sort feature on my column group so that when I click on my column header, it sort my detail group data ascending or decending and companyName and total users based on datail group.

Thanks a lot,

-Rohit

I tried different options for the scope but non of them works. Does any one know how to do this?

Thanks.

Interactive Sort in Matrix

I have a Matrix in a report - created like below. Each row has a different dataset and I need to make each Column to be sortable so they can click on the column header say "Summary of Accounts" and sort them and do that for each column header. So far I havent seen anything that looks promising that can do this. Is there away this can be done?

6 Columns and 14 Rows

--headers

Summary of Accounts No debts origBal AccrInter PaidDate CurrBal

Activity count() Sum() Sum() First() Sum()

phase count() Sum() Sum() First() Sum()

attorney etc etc

collection

not inititated

resolved

interrupted

cancel

closed

revised

letter service

Totals:

The combination of Interactive Sort and Matrix are not good! I managed to get it working but only for row groups.|||I know they are not how about how did you do the row groups? If they can sort by row then fine that is good enough for me then!

Interactive Sort

I'm using the interactive sort in a report. Can I pass in the previously
sorted column so that a user could build a sort order of their choice using
two or more columns by appending their prior selections to the sort?Hi Brent,
Thanks for your posting.
From your descriptions, I understood you would like to know whether it is
possible to specify two or more columns to make interactive sorting. If I
have misunderstood your concern, please feel free to point it out.
Based on my knowledge, we are not able to use previously sorted column. To
make priority of sorted columns, you are encouraged to Parameterized
Sorting. Specify two or more parameters and then combile them in a dynamic
query. For more detailed information, see section "Parameterized Sorting"
below
Sorting Data
http://msdn2.microsoft.com/en-us/library/ms157313.aspx
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a week to all
Microsoft technology partners in the United States and Canada.
This and other support options are available here:
BCPS:
https://partner.microsoft.com/US/technicalsupport/supportoverview/40010469
Others: https://partner.microsoft.com/US/technicalsupport/supportoverview/
If you are outside the United States, please visit our International
Support page: http://support.microsoft.com/common/international.aspx
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.

Interactive column sort in Reporting services

Hi,

I have a report with fiive columns, I have implemented interactive column sorting on the report. I have added a group to the report based on Column 2 and there is a page break by group. Now if I am on the second page ( page break by column 2 ) and sort on column 3(there is no grouping on column 3), the sorting happens but after the sort, the first page is displayed.IS there any way to remain on the same page while sorting?

Thanks in Advance.

I do not think that is possible. Once you click on the sort it will sort all the pages in the report.|||

It is ok if it sorts all the pages. I wanted to know if there is any way i can stick to the same page even after sorting. ie. If I am on page 5 and I click on sort, it sorts that records and takes me back to Page 1. Is there any way I can remain on page 5 after sorting?

|||I agree... I don't think that is possible (not without some nifty trickery), but do you really want that anyways? What good does it do to remain on the same page if the data is sorted differently? The data being referenced on that page will not be the same so you might as well start from the beginning.

Interactive column sort in Reporting services

Hi,

Thanks in Advance.

I do not think that is possible. Once you click on the sort it will sort all the pages in the report.|||

Interactive column sort

Hi All

I'm Climbing the walls here, does anyone know how to do an interactive sort but keep one of the returned rows pinned at the end of the result set?

i've tried this

=iif(Fields!Item.Value <> "c", 1 ,2) & Fields!Item.Value

Doesn't work to well

Results to Sort

Sort should return

which works fine on the ascending sort

with c remaining at the bottom

But i get this on the descending sort

Thanks in advance

Dave

Dont use sort expression, just use the direction...

then exclude 'c' from detail records (display if item <> 'c') and include 'c' in the footer (if item='c')

Shyam

Interactive charts, etc.

Hi everyone,

Two questions about charts in reports.

1) They seem pretty static. I have a report with vertical column chart, and I would like to enable hyperlinks through chart columns, so, say, a height of column shows a number of experiments done by a person. I want a user to be able to click on the column or something that would be associated with that column and go either to another report or external URL that would show info about those experiments (similarly as it is done for any field in a regular report). Is it possible to do something like that? (Help files unfortunately were of no use Sad )

2) Is there any way to show a numeric value for the column height in vertical column chart like this:

40
_
| | 20
| | _
| | | |
| | | |
--

Thanks, I appreciate your help!

Anton Bagayev
TransForm Pharmaceuticals
29 Hartwell Avenue
Lexington, MA1) Open the Chart Properties dialog. Go to the Data tab. Select the data point that you want the action on. Select Edit. Click on Action tab. Choose an action.

2) Open the Chart Properties dialog. Go to the Data tab. Select the data point that you want the label on. Select Edit. Click on Point Labels tab. Enable point labels checkbox and enter expression to display.|||

Brian,

What kind of technology does Reporting Services use to display Interactive Charts? Is it Office Web Components?

If it is Office Web Components, then will I need to license it for every web user who uses the Sql Reporting Server to view the reports?

Thanks,

Kanwal

|||

No, it does not use OWC. Your web users are covered by the RS license.

-- Robert

Interactive charts, etc.

Hi everyone,

Two questions about charts in reports.

2) Is there any way to show a numeric value for the column height in vertical column chart like this:

40
_
| | 20
| | _
| | | |
| | | |
--

Thanks, I appreciate your help!

Brian,

What kind of technology does Reporting Services use to display Interactive Charts? Is it Office Web Components?

If it is Office Web Components, then will I need to license it for every web user who uses the Sql Reporting Server to view the reports?

Thanks,

Kanwal

|||

No, it does not use OWC. Your web users are covered by the RS license.

-- Robert

Interactive charts, etc.

Hi everyone,

Two questions about charts in reports.

2) Is there any way to show a numeric value for the column height in vertical column chart like this:

40
_
| | 20
| | _
| | | |
| | | |
--

Thanks, I appreciate your help!

Brian,

What kind of technology does Reporting Services use to display Interactive Charts? Is it Office Web Components?

If it is Office Web Components, then will I need to license it for every web user who uses the Sql Reporting Server to view the reports?

Thanks,

Kanwal

|||

No, it does not use OWC. Your web users are covered by the RS license.

-- Robert

Friday, March 9, 2012

Inter Table Multiplication and Result (Newbie)

I want to multiply a column (Units) for all transactions in the current
table by a price from another table and reflect the result a column in the
'Units' table. How would I do this and update this daily using SQL server.
The transactions and pricing are matched by a 'Date' field.
Thanks for any help or direction.http://www.aspfaq.com/etiquette.asp?id=5006
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"dl" <d@.l.com> wrote in message news:eRqusn$pFHA.364@.TK2MSFTNGP11.phx.gbl...
> I want to multiply a column (Units) for all transactions in the current
> table by a price from another table and reflect the result a column in the
> 'Units' table. How would I do this and update this daily using SQL server.
> The transactions and pricing are matched by a 'Date' field.
> Thanks for any help or direction.
>|||Adam
The SQL statement I am using is below but I get the error that is shown
thereafter. What I am really trying to achieve is as documented int eh
second statement.
UPDATE [JOHCM-Test].[dbo].[numbers]
SET [number_result]=[numbers]*10
WHERE [Dates]=[dbo].[Prices].[Dates]
Server: Msg 107, Level 16, State 2, Line 1
The column prefix 'dbo.Prices' does not match with a table name or alias
name used in the query.
UPDATE [JOHCM-Test].[dbo].[numbers]
SET [number_result]=[numbers]*[dbo].[Prices].[Prices]
WHERE [Dates]=[dbo].[Prices].[Dates]
Thanks for an assistance.
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23hfzzu$pFHA.3160@.TK2MSFTNGP14.phx.gbl...
> http://www.aspfaq.com/etiquette.asp?id=5006
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>
> "dl" <d@.l.com> wrote in message
> news:eRqusn$pFHA.364@.TK2MSFTNGP11.phx.gbl...
>|||Total guess, since you still haven't provided DDL (I don't know what the
keys are):
UPDATE [JOHCM-Test].[dbo].[numbers]
SET [number_result]=
(
SELECT [dbo].[Prices].[numbers]*10
FROM [dbo].[Prices]
WHERE [Dates]=[dbo].[Prices].[Dates]
)
WHERE EXISTS
(
(SELECT *
FROM [dbo].[Prices]
WHERE [Dates]=[dbo].[Prices].[Dates]
)
If this still doesn't do it, please post DDL and sample data.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"dl" <d@.l.com> wrote in message
news:uNPC%236IqFHA.644@.TK2MSFTNGP10.phx.gbl...
> Adam
> The SQL statement I am using is below but I get the error that is shown
> thereafter. What I am really trying to achieve is as documented int eh
> second statement.
> UPDATE [JOHCM-Test].[dbo].[numbers]
> SET [number_result]=[numbers]*10
> WHERE [Dates]=[dbo].[Prices].[Dates]
> Server: Msg 107, Level 16, State 2, Line 1
> The column prefix 'dbo.Prices' does not match with a table name or alias
> name used in the query.
>
> UPDATE [JOHCM-Test].[dbo].[numbers]
> SET [number_result]=[numbers]*[dbo].[Prices].[Prices]
> WHERE [Dates]=[dbo].[Prices].[Dates]
> Thanks for an assistance.
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:%23hfzzu$pFHA.3160@.TK2MSFTNGP14.phx.gbl...
>|||Adam
I hope I have the relevant info correct as requested. Thanks again.
CREATE TABLE [Prices] (
[Prices] [float] NULL ,
[Dates] [float] NULL
) ON [PRIMARY]
GO
CREATE TABLE [numbers] (
[numbers] [float] NULL ,
[number_result] [float] NULL ,
[Dates] [float] NULL
) ON [PRIMARY]
GO
INSERT INTO [JOHCM-Test].[dbo].[numbers]([numbers], [number_result],
[Dates])
VALUES(100, 0, 38564)
INSERT INTO [JOHCM-Test].[dbo].[numbers]([numbers], [number_result],
[Dates])
VALUES(200, 0, 38564)
INSERT INTO [JOHCM-Test].[dbo].[numbers]([numbers], [number_result],
[Dates])
VALUES(300, 0, 38565)
INSERT INTO [JOHCM-Test].[dbo].[numbers]([numbers], [number_result],
[Dates])
VALUES(400, 0, 38565)
INSERT INTO [JOHCM-Test].[dbo].[Prices]([Prices], [Dates])
VALUES(2.5, 38564)
INSERT INTO [JOHCM-Test].[dbo].[Prices]([Prices], [Dates])
VALUES(3.2, 38565)
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:e9JZGMLqFHA.3180@.TK2MSFTNGP15.phx.gbl...
> Total guess, since you still haven't provided DDL (I don't know what the
> keys are):
> UPDATE [JOHCM-Test].[dbo].[numbers]
> SET [number_result]=
> (
> SELECT [dbo].[Prices].[numbers]*10
> FROM [dbo].[Prices]
> WHERE [Dates]=[dbo].[Prices].[Dates]
> )
> WHERE EXISTS
> (
> (SELECT *
> FROM [dbo].[Prices]
> WHERE [Dates]=[dbo].[Prices].[Dates]
> )
>
> If this still doesn't do it, please post DDL and sample data.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>
> "dl" <d@.l.com> wrote in message
> news:uNPC%236IqFHA.644@.TK2MSFTNGP10.phx.gbl...
>|||Try:
UPDATE [JOHCM-Test].[dbo].[numbers]
SET [number_result]=
(
SELECT [dbo].[numbers].[numbers]*[dbo].[Prices].[Prices]
FROM [dbo].[Prices]
WHERE [dbo].[numbers].[Dates]=[dbo].[Prices].[Dates]
)
WHERE EXISTS
(
(SELECT *
FROM [dbo].[Prices]
WHERE [dbo].[numbers].[Dates]=[dbo].[Prices].[Dates]
)
By the way, what are these floating-point "dates" ?
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"dl" <d@.l.com> wrote in message
news:eSa%236NMqFHA.1272@.TK2MSFTNGP11.phx.gbl...
> Adam
> I hope I have the relevant info correct as requested. Thanks again.
>
> CREATE TABLE [Prices] (
> [Prices] [float] NULL ,
> [Dates] [float] NULL
> ) ON [PRIMARY]
> GO
>
> CREATE TABLE [numbers] (
> [numbers] [float] NULL ,
> [number_result] [float] NULL ,
> [Dates] [float] NULL
> ) ON [PRIMARY]
> GO
> INSERT INTO [JOHCM-Test].[dbo].[numbers]([numbers], [number_result],
> [Dates])
> VALUES(100, 0, 38564)
> INSERT INTO [JOHCM-Test].[dbo].[numbers]([numbers], [number_result],
> [Dates])
> VALUES(200, 0, 38564)
> INSERT INTO [JOHCM-Test].[dbo].[numbers]([numbers], [number_result],
> [Dates])
> VALUES(300, 0, 38565)
> INSERT INTO [JOHCM-Test].[dbo].[numbers]([numbers], [number_result],
> [Dates])
> VALUES(400, 0, 38565)
> INSERT INTO [JOHCM-Test].[dbo].[Prices]([Prices], [Dates])
> VALUES(2.5, 38564)
> INSERT INTO [JOHCM-Test].[dbo].[Prices]([Prices], [Dates])
> VALUES(3.2, 38565)
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:e9JZGMLqFHA.3180@.TK2MSFTNGP15.phx.gbl...
alias
message
in
>|||Adam
I get the error 'Server: Msg 170, Level 15, State 1, Line 13
Line 13: Incorrect syntax near ')'.
I have tried adding another ')' after line and then get the error message
Invalid object name 'dbo.Prices'
Thanks
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%235WjsUMqFHA.3544@.TK2MSFTNGP15.phx.gbl...
> Try:
> UPDATE [JOHCM-Test].[dbo].[numbers]
> SET [number_result]=
> (
> SELECT [dbo].[numbers].[numbers]*[dbo].[Prices].[Prices]
> FROM [dbo].[Prices]
> WHERE [dbo].[numbers].[Dates]=[dbo].[Prices].[Dates]
> )
> WHERE EXISTS
> (
> (SELECT *
> FROM [dbo].[Prices]
> WHERE [dbo].[numbers].[Dates]=[dbo].[Prices].[Dates]
> )
>
> By the way, what are these floating-point "dates" ?
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>
> "dl" <d@.l.com> wrote in message
> news:eSa%236NMqFHA.1272@.TK2MSFTNGP11.phx.gbl...
> alias
> message
> in
>|||On Wed, 24 Aug 2005 17:53:48 +0100, dl wrote:

>Adam
>I get the error 'Server: Msg 170, Level 15, State 1, Line 13
>Line 13: Incorrect syntax near ')'.
Hi dl,
There's a small typo in Adam's message:
should be
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||I changed this and got the error message 'Invalid object name 'dbo.Prices'.
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:8uapg19u9imvq32ca4a1nc2ujhdq84186o@.
4ax.com...
> On Wed, 24 Aug 2005 17:53:48 +0100, dl wrote:
>
> Hi dl,
> There's a small typo in Adam's message:
>
> should be
>
>
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)

Integrity Constraint Error on SQL Server column with no constraints

I have created a simple package to load an Excel Spreadsheet into a SQL Server table. There is a one to one relationship between the columns in the .xls file and the columns in the DB record. I am getting integrity constraint errors when I try to load all numeric data from the spreadsheet (defined as Category General in excel, not defined as numeric but consisting of all numeric characters) into a column defined as (nvarchar(20), not null) in SQL Server Management Studio. There are no constraints on the column.

I have been able to temporarily bypass the offending rows, but I do need to load them into SQL Server. The problem column has a mixture of data, two examples would be: N255, 168050. It's the 168050 value that's causing the Task to bomb. How can I get this loaded into my table ?

I am running the package from within MS Visual Studio 2005 Version 8, Excel is version 2003 (11.8120.8122) SP2

Thanks,

Chris

Try setting the extended property IMEX to 1 in the Excel Source properties. Search this forum for IMEX and you'll get plenty of answers.|||And make sure you don't have any empty values in the excel source. Your table is set to not accept NULLs, so make sure there are none going into it.|||

Ok, so I did a search of this forum and found the recommendation to add "IMEX=1; MAXROWSTOSCAN=0" to the Excel Connection manager connection string. So I hunted and pecked and found that if I displayed the properties on my excel connection manager, there was a property called Expressions with three dots next to it that if you clicked on would display a Property and an Expression column. I chose connection string for the Property and pasted the recommended string into the Expression. I tried with and without quotes, eliminated the ; MAXROWSTOSCAN=0 part

(IMEX=1), also tried IMEX==1 because that's what the ensuing error message recommended - All to no avail.

what am I missing ?

Thanks,

Chris

|||Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\test.xls;Extended Properties="Excel 8.0;HDR=YES;IMEX=1";

Your connection string would have to be similar to the above. Look at your existing ConnectionString property, copy it, and add IMEX=1 to the Extended Properties as I have done above.|||

Sorry to drag this out, but I had to add a Connection String Property to my Excel Connection Manager properties. When I bring up the Expressions dialog as I described above, all I see is a blank dialog box. Should I be using an Excel Connection manager or a modified OLE DB connection manager ? I have a text book that recommends using the latter for an excel file if you need finer control.

|||

You may use either one connection if you plan to set it only once. You do not need to use Expressions, change the ConnectionString property directly.

If you plan to edit/change the connection settings, than you might be better with pure OLE DB connection because the Excel wrapper might override the settings you manually put into the connection string.

Thanks,

Bob

|||

I directly edited the Connection string for my Excel Connection manager, adding IMEX=1; to what was there. The complete string is now:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=I:\Financials\Outlooksoft\Workbooks\Fact Table\SSIS_Source\OSOFAC01.xls;Extended Properties="EXCEL 8.0;HDR=YES";IMEX=1;

I am unable to use the connection manager like that - I'm getting another error "Could not find Installable ISAM"

I assume HDR=YES means there is a header row in the spreadsheet, which there is in this case.

Thanks,

Chris

|||

Christohperrobin wrote:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=I:\Financials\Outlooksoft\Workbooks\Fact Table\SSIS_Source\OSOFAC01.xls;Extended Properties="EXCEL 8.0;HDR=YES";IMEX=1;

IMEX goes *INSIDE* the Extended Properties property as I've shown earlier.

....Extended Properties="EXCEL 8.0;HDR=YES;IMEX=1";|||

I put the quote (") in the wrong place. I corrected that so my connection string is now:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=I:\Financials\Outlooksoft\Workbooks\Fact Table\SSIS_Source\OSOFAC01.xls;Extended Properties="EXCEL 8.0;HDR=YES;IMEX=1";

IT'S WORKING FINE NOW!

Thanks for all your help,

Chris

|||

Actually I had to run it a few more times before I got all the rows to load. The package was still skipping (Redirecting) some of the rows due to the SAME error on another column that had mixed format data just like the one that originally caused the problem. This other column also had both straight numeric and combination strings (ex: 84001, 57B42).

From what I gathered reading about this problem, it looks like Integration Services makes certain decisions about the metadata associated with the contents of Excel files based upon the first few rows of the input spreadsheet. With this in mind I moved the row containing the 57B42 value up closer to the top, so now instead of the first 10 rows having all numeric values in that column, there was a non-numeric value appearing early on in the mix.

When I tried to run the package after this change, It threw an error right at the beginning indicating there was a problem with the Excel Data Flow source. The error indicated that there was mismatched metadata between the Excel Source and the SQL Server OLE DB Destination. The error message also offered to fix the problem without my intervention. I did choose to allow this to happen. After that the package ran to completion and all 2034 rows in the spreadsheet loaded into the DB table.

I am just wondering how I should deal with this in the future. Do I actually have to be concerned with the order of the rows in my spreadsheet ? Why should it be a problem to load character data and apparent numeric data into an nvarchar field ?

Wednesday, March 28, 2012

Intermittent ORDER BY not functionning

Friday, March 23, 2012

Interesting SQLProblem using joins

Monday, March 19, 2012

Intercept Result

Interactive Sorting/Execution of query

Interactive Sorting -prevent group from collapsing

Interactive sorting of a list

Monday, March 12, 2012

Interactive sort not working with snapshot

Interactive Sort in Matrix

Interactive Sort in Matrix

Interactive Sort

Interactive column sort in Reporting services

Interactive column sort in Reporting services

Interactive column sort

Interactive charts, etc.

Interactive charts, etc.

Interactive charts, etc.

Friday, March 9, 2012

Inter Table Multiplication and Result (Newbie)

Integrity Constraint Error on SQL Server column with no constraints

Integrated Security

Blog Archive

About Me