Ok,
In my c# app, i have a string with an invoice number: "str = 12345".
Using sqlserver2k:
first of all, i understand the c# requirments for putting a command
string in, but don't know the sql command.
this is what i need:
tblPrintInvoice has one record with this column_invoice_number#.
tblInvoiceDetails has maybe up to 50.
How can I delete str(12345) in both tables most efficiently?
Also, i may need to put a flag in each record instead of deleting
them...like:
column_invoice_flag for all 12345 records gets an "insert?" of (reprint
- don't delete) instead of deleting.
any help is appreciated.
thanks,
trintHere are two examples:
DELETE FROM tblInvoiceDetails
WHERE invoice_number = '12345'
UPDATE tblInvoiceDetails
SET deleted = 'Y'
WHERE invoice_number = '12345'
Lookup the DELETE and UPDATE statements in Books Online for the full
syntax.
David Portas
SQL Server MVP
--
Showing posts with label number. Show all posts
Showing posts with label number. Show all posts
Monday, March 26, 2012
Friday, March 23, 2012
How can I open hyperlink in a new window?
I have a number of dynamically configured hyperlinks in my report. Clicking
on them, takes user to a different application. I want for this new app to
open in a different browser window from the report itself, but instead it
opens in the same window and replaces my report data. Any ideas how to force
it to open in a new window?
Thank you!Copied from newsgroup:
You put it in the document map label. This is a standard thing for
hyperlinks. You can also use _self, _top, _parent, etc...
From Books Online,
"Advanced Textbox Properties (Navigation Tab)
Use the Navigation tab of the Advanced Textbox Properties dialog box to
define navigation options for the text box.
Options
Document map label
Type or select a label or an expression that evaluates to a label. Click the
expression (fx) button to edit the expression. The value of the label
appears on the document map."
HTH, Jens Suessmeyer,.
http://www.sqlserver2005.de
--
"Misha_SQL" <Misha_SQL@.discussions.microsoft.com> schrieb im Newsbeitrag
news:AB7CB9AF-6CAA-4A13-9A75-8FF174E9E5D3@.microsoft.com...
>I have a number of dynamically configured hyperlinks in my report.
>Clicking
> on them, takes user to a different application. I want for this new app
> to
> open in a different browser window from the report itself, but instead it
> opens in the same window and replaces my report data. Any ideas how to
> force
> it to open in a new window?
> Thank you!|||Some more links:
#1:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RShowto/htm/hrs_designer_v1_6mwb.asp
#2: Take a look at _LinkTarget device info setting:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSPROG/htm/rsp_prog_soapapi_dev_3i49.asp
Jens Suessmeyer.
"Jens Süßmeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> schrieb
im Newsbeitrag news:uv4rf7dRFHA.3716@.TK2MSFTNGP14.phx.gbl...
> Copied from newsgroup:
>
> You put it in the document map label. This is a standard thing for
> hyperlinks. You can also use _self, _top, _parent, etc...
> From Books Online,
> "Advanced Textbox Properties (Navigation Tab)
> Use the Navigation tab of the Advanced Textbox Properties dialog box to
> define navigation options for the text box.
> Options
> Document map label
> Type or select a label or an expression that evaluates to a label. Click
> the expression (fx) button to edit the expression. The value of the label
> appears on the document map."
>
> HTH, Jens Suessmeyer,.
>
>
> --
> http://www.sqlserver2005.de
> --
> "Misha_SQL" <Misha_SQL@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:AB7CB9AF-6CAA-4A13-9A75-8FF174E9E5D3@.microsoft.com...
>>I have a number of dynamically configured hyperlinks in my report.
>>Clicking
>> on them, takes user to a different application. I want for this new app
>> to
>> open in a different browser window from the report itself, but instead it
>> opens in the same window and replaces my report data. Any ideas how to
>> force
>> it to open in a new window?
>> Thank you!
>|||For the following you need SP1 or greater installed.
= "javascript:void(window.open('http://www.google.com','_blank'))"
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Misha_SQL" <Misha_SQL@.discussions.microsoft.com> wrote in message
news:AB7CB9AF-6CAA-4A13-9A75-8FF174E9E5D3@.microsoft.com...
> I have a number of dynamically configured hyperlinks in my report.
Clicking
> on them, takes user to a different application. I want for this new app
to
> open in a different browser window from the report itself, but instead it
> opens in the same window and replaces my report data. Any ideas how to
force
> it to open in a new window?
> Thank you!|||Thank you, Jens!
These links contained information that solved my problem.
"Jens Sü�meyer" wrote:
> Some more links:
> #1:
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RShowto/htm/hrs_designer_v1_6mwb.asp
> #2: Take a look at _LinkTarget device info setting:
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSPROG/htm/rsp_prog_soapapi_dev_3i49.asp
>
> Jens Suessmeyer.
> "Jens Sü�meyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> schrieb
> im Newsbeitrag news:uv4rf7dRFHA.3716@.TK2MSFTNGP14.phx.gbl...
> > Copied from newsgroup:
> >
> >
> > You put it in the document map label. This is a standard thing for
> > hyperlinks. You can also use _self, _top, _parent, etc...
> >
> > From Books Online,
> >
> > "Advanced Textbox Properties (Navigation Tab)
> > Use the Navigation tab of the Advanced Textbox Properties dialog box to
> > define navigation options for the text box.
> >
> > Options
> >
> > Document map label
> > Type or select a label or an expression that evaluates to a label. Click
> > the expression (fx) button to edit the expression. The value of the label
> > appears on the document map."
> >
> >
> >
> > HTH, Jens Suessmeyer,.
> >
> >
> >
> >
> >
> > --
> >
> > http://www.sqlserver2005.de
> >
> > --
> >
> > "Misha_SQL" <Misha_SQL@.discussions.microsoft.com> schrieb im Newsbeitrag
> > news:AB7CB9AF-6CAA-4A13-9A75-8FF174E9E5D3@.microsoft.com...
> >>I have a number of dynamically configured hyperlinks in my report.
> >>Clicking
> >> on them, takes user to a different application. I want for this new app
> >> to
> >> open in a different browser window from the report itself, but instead it
> >> opens in the same window and replaces my report data. Any ideas how to
> >> force
> >> it to open in a new window?
> >>
> >> Thank you!
> >
> >
>
>|||Hi Bruce/Misha,
I tried the solution given below and it opens in the new window with the URL
given,even when i change it to "_Self".
Please note i am trying to open the url in a particular frame and not the
full window.
And also i see that "javascript:void(window.open
'http://www.google.com','_blank'))" gets pasted in the address bar of the IE
, removing all the frames and also the page is not loaded untill i press the
enter key.
Please help me find a solution to this.
Thanks in advance,
Regads,
Archana Kamath
"Bruce L-C [MVP]" wrote:
> For the following you need SP1 or greater installed.
> = "javascript:void(window.open('http://www.google.com','_blank'))"
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Misha_SQL" <Misha_SQL@.discussions.microsoft.com> wrote in message
> news:AB7CB9AF-6CAA-4A13-9A75-8FF174E9E5D3@.microsoft.com...
> > I have a number of dynamically configured hyperlinks in my report.
> Clicking
> > on them, takes user to a different application. I want for this new app
> to
> > open in a different browser window from the report itself, but instead it
> > opens in the same window and replaces my report data. Any ideas how to
> force
> > it to open in a new window?
> >
> > Thank you!
>
>|||Window.open create a new browser window. You want soemthign like:
(javascript)
Window.parent.FRAMENAME.src = 'http://www.google.com'
or
<a href='http://www.google.com' target='FRAMENAME'>Insert Text Here</a>
"Archana" <Archana@.discussions.microsoft.com> wrote in message
news:992668D3-C45F-4950-ACE0-E7D04FB034DF@.microsoft.com...
> Hi Bruce/Misha,
> I tried the solution given below and it opens in the new window with the
URL
> given,even when i change it to "_Self".
> Please note i am trying to open the url in a particular frame and not the
> full window.
> And also i see that "javascript:void(window.open
> 'http://www.google.com','_blank'))" gets pasted in the address bar of the
IE
> , removing all the frames and also the page is not loaded untill i press
the
> enter key.
> Please help me find a solution to this.
> Thanks in advance,
> Regads,
> Archana Kamath
>
> "Bruce L-C [MVP]" wrote:
> > For the following you need SP1 or greater installed.
> >
> > = "javascript:void(window.open('http://www.google.com','_blank'))"
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> >
> >
> > "Misha_SQL" <Misha_SQL@.discussions.microsoft.com> wrote in message
> > news:AB7CB9AF-6CAA-4A13-9A75-8FF174E9E5D3@.microsoft.com...
> > > I have a number of dynamically configured hyperlinks in my report.
> > Clicking
> > > on them, takes user to a different application. I want for this new
app
> > to
> > > open in a different browser window from the report itself, but instead
it
> > > opens in the same window and replaces my report data. Any ideas how
to
> > force
> > > it to open in a new window?
> > >
> > > Thank you!
> >
> >
> >|||Thanks Jim for a quick response.
But i am still facing the problem.
The problem is Reporting services report expression doesnot accept "<a href>".
And when i try to give the javascript,it doesnot get exceuted.What it simply
does is opens up a new browser and paste the javascript on the adress bar.It
doesnt not get executed untill we press the enter key.
In most of the solutions for such problems they have suggested to use the
javascript.But in my case, it doesnt get executed at all.
Do i need to set any internet settings so that javascript get executed with
out user pressing the enter key.
Please help me out with this issue.
Many Thanks,
Archana
"Jim Underwood" wrote:
> Window.open create a new browser window. You want soemthign like:
> (javascript)
> Window.parent.FRAMENAME.src = 'http://www.google.com'
> or
> <a href='http://www.google.com' target='FRAMENAME'>Insert Text Here</a>
> "Archana" <Archana@.discussions.microsoft.com> wrote in message
> news:992668D3-C45F-4950-ACE0-E7D04FB034DF@.microsoft.com...
> > Hi Bruce/Misha,
> >
> > I tried the solution given below and it opens in the new window with the
> URL
> > given,even when i change it to "_Self".
> >
> > Please note i am trying to open the url in a particular frame and not the
> > full window.
> >
> > And also i see that "javascript:void(window.open
> > 'http://www.google.com','_blank'))" gets pasted in the address bar of the
> IE
> > , removing all the frames and also the page is not loaded untill i press
> the
> > enter key.
> >
> > Please help me find a solution to this.
> >
> > Thanks in advance,
> > Regads,
> > Archana Kamath
> >
> >
> > "Bruce L-C [MVP]" wrote:
> >
> > > For the following you need SP1 or greater installed.
> > >
> > > = "javascript:void(window.open('http://www.google.com','_blank'))"
> > >
> > >
> > > --
> > > Bruce Loehle-Conger
> > > MVP SQL Server Reporting Services
> > >
> > >
> > >
> > > "Misha_SQL" <Misha_SQL@.discussions.microsoft.com> wrote in message
> > > news:AB7CB9AF-6CAA-4A13-9A75-8FF174E9E5D3@.microsoft.com...
> > > > I have a number of dynamically configured hyperlinks in my report.
> > > Clicking
> > > > on them, takes user to a different application. I want for this new
> app
> > > to
> > > > open in a different browser window from the report itself, but instead
> it
> > > > opens in the same window and replaces my report data. Any ideas how
> to
> > > force
> > > > it to open in a new window?
> > > >
> > > > Thank you!
> > >
> > >
> > >
>
>|||Hi Jim,
Javascript works fine in the report.That is when i try opening the report
using the report manager /reportserver it works fine.When i click the link,
the page ('google') gets loaded.
But when i include the same report in my sharepoint site ,using the page
viewer webpart and try accessing the link,it throws up an error saying "Page
cannot be displayed" and the javascript gets pasted on the address bar.
Any idea why this behaviour?
Many thanks inadvance,
Regards,
Archana
"Archana" wrote:
> Thanks Jim for a quick response.
> But i am still facing the problem.
> The problem is Reporting services report expression doesnot accept "<a href>".
> And when i try to give the javascript,it doesnot get exceuted.What it simply
> does is opens up a new browser and paste the javascript on the adress bar.It
> doesnt not get executed untill we press the enter key.
> In most of the solutions for such problems they have suggested to use the
> javascript.But in my case, it doesnt get executed at all.
> Do i need to set any internet settings so that javascript get executed with
> out user pressing the enter key.
> Please help me out with this issue.
> Many Thanks,
> Archana
> "Jim Underwood" wrote:
> > Window.open create a new browser window. You want soemthign like:
> >
> > (javascript)
> > Window.parent.FRAMENAME.src = 'http://www.google.com'
> >
> > or
> > <a href='http://www.google.com' target='FRAMENAME'>Insert Text Here</a>
> >
> > "Archana" <Archana@.discussions.microsoft.com> wrote in message
> > news:992668D3-C45F-4950-ACE0-E7D04FB034DF@.microsoft.com...
> > > Hi Bruce/Misha,
> > >
> > > I tried the solution given below and it opens in the new window with the
> > URL
> > > given,even when i change it to "_Self".
> > >
> > > Please note i am trying to open the url in a particular frame and not the
> > > full window.
> > >
> > > And also i see that "javascript:void(window.open
> > > 'http://www.google.com','_blank'))" gets pasted in the address bar of the
> > IE
> > > , removing all the frames and also the page is not loaded untill i press
> > the
> > > enter key.
> > >
> > > Please help me find a solution to this.
> > >
> > > Thanks in advance,
> > > Regads,
> > > Archana Kamath
> > >
> > >
> > > "Bruce L-C [MVP]" wrote:
> > >
> > > > For the following you need SP1 or greater installed.
> > > >
> > > > = "javascript:void(window.open('http://www.google.com','_blank'))"
> > > >
> > > >
> > > > --
> > > > Bruce Loehle-Conger
> > > > MVP SQL Server Reporting Services
> > > >
> > > >
> > > >
> > > > "Misha_SQL" <Misha_SQL@.discussions.microsoft.com> wrote in message
> > > > news:AB7CB9AF-6CAA-4A13-9A75-8FF174E9E5D3@.microsoft.com...
> > > > > I have a number of dynamically configured hyperlinks in my report.
> > > > Clicking
> > > > > on them, takes user to a different application. I want for this new
> > app
> > > > to
> > > > > open in a different browser window from the report itself, but instead
> > it
> > > > > opens in the same window and replaces my report data. Any ideas how
> > to
> > > > force
> > > > > it to open in a new window?
> > > > >
> > > > > Thank you!
> > > >
> > > >
> > > >
> >
> >
> >|||HI Jim,
Thanks for your help.
The javascript works fine.There was some problem with mysharepoint page.
Anyways Many thanks
Regards,
Archana
"Archana" wrote:
> Hi Jim,
> Javascript works fine in the report.That is when i try opening the report
> using the report manager /reportserver it works fine.When i click the link,
> the page ('google') gets loaded.
> But when i include the same report in my sharepoint site ,using the page
> viewer webpart and try accessing the link,it throws up an error saying "Page
> cannot be displayed" and the javascript gets pasted on the address bar.
> Any idea why this behaviour?
> Many thanks inadvance,
> Regards,
> Archana
> "Archana" wrote:
> > Thanks Jim for a quick response.
> > But i am still facing the problem.
> > The problem is Reporting services report expression doesnot accept "<a href>".
> > And when i try to give the javascript,it doesnot get exceuted.What it simply
> > does is opens up a new browser and paste the javascript on the adress bar.It
> > doesnt not get executed untill we press the enter key.
> >
> > In most of the solutions for such problems they have suggested to use the
> > javascript.But in my case, it doesnt get executed at all.
> >
> > Do i need to set any internet settings so that javascript get executed with
> > out user pressing the enter key.
> >
> > Please help me out with this issue.
> >
> > Many Thanks,
> > Archana
> >
> > "Jim Underwood" wrote:
> >
> > > Window.open create a new browser window. You want soemthign like:
> > >
> > > (javascript)
> > > Window.parent.FRAMENAME.src = 'http://www.google.com'
> > >
> > > or
> > > <a href='http://www.google.com' target='FRAMENAME'>Insert Text Here</a>
> > >
> > > "Archana" <Archana@.discussions.microsoft.com> wrote in message
> > > news:992668D3-C45F-4950-ACE0-E7D04FB034DF@.microsoft.com...
> > > > Hi Bruce/Misha,
> > > >
> > > > I tried the solution given below and it opens in the new window with the
> > > URL
> > > > given,even when i change it to "_Self".
> > > >
> > > > Please note i am trying to open the url in a particular frame and not the
> > > > full window.
> > > >
> > > > And also i see that "javascript:void(window.open
> > > > 'http://www.google.com','_blank'))" gets pasted in the address bar of the
> > > IE
> > > > , removing all the frames and also the page is not loaded untill i press
> > > the
> > > > enter key.
> > > >
> > > > Please help me find a solution to this.
> > > >
> > > > Thanks in advance,
> > > > Regads,
> > > > Archana Kamath
> > > >
> > > >
> > > > "Bruce L-C [MVP]" wrote:
> > > >
> > > > > For the following you need SP1 or greater installed.
> > > > >
> > > > > = "javascript:void(window.open('http://www.google.com','_blank'))"
> > > > >
> > > > >
> > > > > --
> > > > > Bruce Loehle-Conger
> > > > > MVP SQL Server Reporting Services
> > > > >
> > > > >
> > > > >
> > > > > "Misha_SQL" <Misha_SQL@.discussions.microsoft.com> wrote in message
> > > > > news:AB7CB9AF-6CAA-4A13-9A75-8FF174E9E5D3@.microsoft.com...
> > > > > > I have a number of dynamically configured hyperlinks in my report.
> > > > > Clicking
> > > > > > on them, takes user to a different application. I want for this new
> > > app
> > > > > to
> > > > > > open in a different browser window from the report itself, but instead
> > > it
> > > > > > opens in the same window and replaces my report data. Any ideas how
> > > to
> > > > > force
> > > > > > it to open in a new window?
> > > > > >
> > > > > > Thank you!
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >|||I am glad to hear you got it working.
"Archana" <Archana@.discussions.microsoft.com> wrote in message
news:95947B79-1C8F-4850-A528-231B95C82451@.microsoft.com...
> HI Jim,
> Thanks for your help.
> The javascript works fine.There was some problem with mysharepoint page.
> Anyways Many thanks
> Regards,
> Archana
> "Archana" wrote:
> > Hi Jim,
> >
> > Javascript works fine in the report.That is when i try opening the
report
> > using the report manager /reportserver it works fine.When i click the
link,
> > the page ('google') gets loaded.
> >
> > But when i include the same report in my sharepoint site ,using the page
> > viewer webpart and try accessing the link,it throws up an error saying
"Page
> > cannot be displayed" and the javascript gets pasted on the address bar.
> >
> > Any idea why this behaviour?
> > Many thanks inadvance,
> > Regards,
> > Archana
> >
> > "Archana" wrote:
> >
> > > Thanks Jim for a quick response.
> > > But i am still facing the problem.
> > > The problem is Reporting services report expression doesnot accept "<a
href>".
> > > And when i try to give the javascript,it doesnot get exceuted.What it
simply
> > > does is opens up a new browser and paste the javascript on the adress
bar.It
> > > doesnt not get executed untill we press the enter key.
> > >
> > > In most of the solutions for such problems they have suggested to use
the
> > > javascript.But in my case, it doesnt get executed at all.
> > >
> > > Do i need to set any internet settings so that javascript get executed
with
> > > out user pressing the enter key.
> > >
> > > Please help me out with this issue.
> > >
> > > Many Thanks,
> > > Archana
> > >
> > > "Jim Underwood" wrote:
> > >
> > > > Window.open create a new browser window. You want soemthign like:
> > > >
> > > > (javascript)
> > > > Window.parent.FRAMENAME.src = 'http://www.google.com'
> > > >
> > > > or
> > > > <a href='http://www.google.com' target='FRAMENAME'>Insert Text
Here</a>
> > > >
> > > > "Archana" <Archana@.discussions.microsoft.com> wrote in message
> > > > news:992668D3-C45F-4950-ACE0-E7D04FB034DF@.microsoft.com...
> > > > > Hi Bruce/Misha,
> > > > >
> > > > > I tried the solution given below and it opens in the new window
with the
> > > > URL
> > > > > given,even when i change it to "_Self".
> > > > >
> > > > > Please note i am trying to open the url in a particular frame and
not the
> > > > > full window.
> > > > >
> > > > > And also i see that "javascript:void(window.open
> > > > > 'http://www.google.com','_blank'))" gets pasted in the address bar
of the
> > > > IE
> > > > > , removing all the frames and also the page is not loaded untill i
press
> > > > the
> > > > > enter key.
> > > > >
> > > > > Please help me find a solution to this.
> > > > >
> > > > > Thanks in advance,
> > > > > Regads,
> > > > > Archana Kamath
> > > > >
> > > > >
> > > > > "Bruce L-C [MVP]" wrote:
> > > > >
> > > > > > For the following you need SP1 or greater installed.
> > > > > >
> > > > > > ="javascript:void(window.open('http://www.google.com','_blank'))"
> > > > > >
> > > > > >
> > > > > > --
> > > > > > Bruce Loehle-Conger
> > > > > > MVP SQL Server Reporting Services
> > > > > >
> > > > > >
> > > > > >
> > > > > > "Misha_SQL" <Misha_SQL@.discussions.microsoft.com> wrote in
message
> > > > > > news:AB7CB9AF-6CAA-4A13-9A75-8FF174E9E5D3@.microsoft.com...
> > > > > > > I have a number of dynamically configured hyperlinks in my
report.
> > > > > > Clicking
> > > > > > > on them, takes user to a different application. I want for
this new
> > > > app
> > > > > > to
> > > > > > > open in a different browser window from the report itself, but
instead
> > > > it
> > > > > > > opens in the same window and replaces my report data. Any
ideas how
> > > > to
> > > > > > force
> > > > > > > it to open in a new window?
> > > > > > >
> > > > > > > Thank you!
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
on them, takes user to a different application. I want for this new app to
open in a different browser window from the report itself, but instead it
opens in the same window and replaces my report data. Any ideas how to force
it to open in a new window?
Thank you!Copied from newsgroup:
You put it in the document map label. This is a standard thing for
hyperlinks. You can also use _self, _top, _parent, etc...
From Books Online,
"Advanced Textbox Properties (Navigation Tab)
Use the Navigation tab of the Advanced Textbox Properties dialog box to
define navigation options for the text box.
Options
Document map label
Type or select a label or an expression that evaluates to a label. Click the
expression (fx) button to edit the expression. The value of the label
appears on the document map."
HTH, Jens Suessmeyer,.
http://www.sqlserver2005.de
--
"Misha_SQL" <Misha_SQL@.discussions.microsoft.com> schrieb im Newsbeitrag
news:AB7CB9AF-6CAA-4A13-9A75-8FF174E9E5D3@.microsoft.com...
>I have a number of dynamically configured hyperlinks in my report.
>Clicking
> on them, takes user to a different application. I want for this new app
> to
> open in a different browser window from the report itself, but instead it
> opens in the same window and replaces my report data. Any ideas how to
> force
> it to open in a new window?
> Thank you!|||Some more links:
#1:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RShowto/htm/hrs_designer_v1_6mwb.asp
#2: Take a look at _LinkTarget device info setting:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSPROG/htm/rsp_prog_soapapi_dev_3i49.asp
Jens Suessmeyer.
"Jens Süßmeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> schrieb
im Newsbeitrag news:uv4rf7dRFHA.3716@.TK2MSFTNGP14.phx.gbl...
> Copied from newsgroup:
>
> You put it in the document map label. This is a standard thing for
> hyperlinks. You can also use _self, _top, _parent, etc...
> From Books Online,
> "Advanced Textbox Properties (Navigation Tab)
> Use the Navigation tab of the Advanced Textbox Properties dialog box to
> define navigation options for the text box.
> Options
> Document map label
> Type or select a label or an expression that evaluates to a label. Click
> the expression (fx) button to edit the expression. The value of the label
> appears on the document map."
>
> HTH, Jens Suessmeyer,.
>
>
> --
> http://www.sqlserver2005.de
> --
> "Misha_SQL" <Misha_SQL@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:AB7CB9AF-6CAA-4A13-9A75-8FF174E9E5D3@.microsoft.com...
>>I have a number of dynamically configured hyperlinks in my report.
>>Clicking
>> on them, takes user to a different application. I want for this new app
>> to
>> open in a different browser window from the report itself, but instead it
>> opens in the same window and replaces my report data. Any ideas how to
>> force
>> it to open in a new window?
>> Thank you!
>|||For the following you need SP1 or greater installed.
= "javascript:void(window.open('http://www.google.com','_blank'))"
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Misha_SQL" <Misha_SQL@.discussions.microsoft.com> wrote in message
news:AB7CB9AF-6CAA-4A13-9A75-8FF174E9E5D3@.microsoft.com...
> I have a number of dynamically configured hyperlinks in my report.
Clicking
> on them, takes user to a different application. I want for this new app
to
> open in a different browser window from the report itself, but instead it
> opens in the same window and replaces my report data. Any ideas how to
force
> it to open in a new window?
> Thank you!|||Thank you, Jens!
These links contained information that solved my problem.
"Jens Sü�meyer" wrote:
> Some more links:
> #1:
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RShowto/htm/hrs_designer_v1_6mwb.asp
> #2: Take a look at _LinkTarget device info setting:
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSPROG/htm/rsp_prog_soapapi_dev_3i49.asp
>
> Jens Suessmeyer.
> "Jens Sü�meyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> schrieb
> im Newsbeitrag news:uv4rf7dRFHA.3716@.TK2MSFTNGP14.phx.gbl...
> > Copied from newsgroup:
> >
> >
> > You put it in the document map label. This is a standard thing for
> > hyperlinks. You can also use _self, _top, _parent, etc...
> >
> > From Books Online,
> >
> > "Advanced Textbox Properties (Navigation Tab)
> > Use the Navigation tab of the Advanced Textbox Properties dialog box to
> > define navigation options for the text box.
> >
> > Options
> >
> > Document map label
> > Type or select a label or an expression that evaluates to a label. Click
> > the expression (fx) button to edit the expression. The value of the label
> > appears on the document map."
> >
> >
> >
> > HTH, Jens Suessmeyer,.
> >
> >
> >
> >
> >
> > --
> >
> > http://www.sqlserver2005.de
> >
> > --
> >
> > "Misha_SQL" <Misha_SQL@.discussions.microsoft.com> schrieb im Newsbeitrag
> > news:AB7CB9AF-6CAA-4A13-9A75-8FF174E9E5D3@.microsoft.com...
> >>I have a number of dynamically configured hyperlinks in my report.
> >>Clicking
> >> on them, takes user to a different application. I want for this new app
> >> to
> >> open in a different browser window from the report itself, but instead it
> >> opens in the same window and replaces my report data. Any ideas how to
> >> force
> >> it to open in a new window?
> >>
> >> Thank you!
> >
> >
>
>|||Hi Bruce/Misha,
I tried the solution given below and it opens in the new window with the URL
given,even when i change it to "_Self".
Please note i am trying to open the url in a particular frame and not the
full window.
And also i see that "javascript:void(window.open
'http://www.google.com','_blank'))" gets pasted in the address bar of the IE
, removing all the frames and also the page is not loaded untill i press the
enter key.
Please help me find a solution to this.
Thanks in advance,
Regads,
Archana Kamath
"Bruce L-C [MVP]" wrote:
> For the following you need SP1 or greater installed.
> = "javascript:void(window.open('http://www.google.com','_blank'))"
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Misha_SQL" <Misha_SQL@.discussions.microsoft.com> wrote in message
> news:AB7CB9AF-6CAA-4A13-9A75-8FF174E9E5D3@.microsoft.com...
> > I have a number of dynamically configured hyperlinks in my report.
> Clicking
> > on them, takes user to a different application. I want for this new app
> to
> > open in a different browser window from the report itself, but instead it
> > opens in the same window and replaces my report data. Any ideas how to
> force
> > it to open in a new window?
> >
> > Thank you!
>
>|||Window.open create a new browser window. You want soemthign like:
(javascript)
Window.parent.FRAMENAME.src = 'http://www.google.com'
or
<a href='http://www.google.com' target='FRAMENAME'>Insert Text Here</a>
"Archana" <Archana@.discussions.microsoft.com> wrote in message
news:992668D3-C45F-4950-ACE0-E7D04FB034DF@.microsoft.com...
> Hi Bruce/Misha,
> I tried the solution given below and it opens in the new window with the
URL
> given,even when i change it to "_Self".
> Please note i am trying to open the url in a particular frame and not the
> full window.
> And also i see that "javascript:void(window.open
> 'http://www.google.com','_blank'))" gets pasted in the address bar of the
IE
> , removing all the frames and also the page is not loaded untill i press
the
> enter key.
> Please help me find a solution to this.
> Thanks in advance,
> Regads,
> Archana Kamath
>
> "Bruce L-C [MVP]" wrote:
> > For the following you need SP1 or greater installed.
> >
> > = "javascript:void(window.open('http://www.google.com','_blank'))"
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> >
> >
> > "Misha_SQL" <Misha_SQL@.discussions.microsoft.com> wrote in message
> > news:AB7CB9AF-6CAA-4A13-9A75-8FF174E9E5D3@.microsoft.com...
> > > I have a number of dynamically configured hyperlinks in my report.
> > Clicking
> > > on them, takes user to a different application. I want for this new
app
> > to
> > > open in a different browser window from the report itself, but instead
it
> > > opens in the same window and replaces my report data. Any ideas how
to
> > force
> > > it to open in a new window?
> > >
> > > Thank you!
> >
> >
> >|||Thanks Jim for a quick response.
But i am still facing the problem.
The problem is Reporting services report expression doesnot accept "<a href>".
And when i try to give the javascript,it doesnot get exceuted.What it simply
does is opens up a new browser and paste the javascript on the adress bar.It
doesnt not get executed untill we press the enter key.
In most of the solutions for such problems they have suggested to use the
javascript.But in my case, it doesnt get executed at all.
Do i need to set any internet settings so that javascript get executed with
out user pressing the enter key.
Please help me out with this issue.
Many Thanks,
Archana
"Jim Underwood" wrote:
> Window.open create a new browser window. You want soemthign like:
> (javascript)
> Window.parent.FRAMENAME.src = 'http://www.google.com'
> or
> <a href='http://www.google.com' target='FRAMENAME'>Insert Text Here</a>
> "Archana" <Archana@.discussions.microsoft.com> wrote in message
> news:992668D3-C45F-4950-ACE0-E7D04FB034DF@.microsoft.com...
> > Hi Bruce/Misha,
> >
> > I tried the solution given below and it opens in the new window with the
> URL
> > given,even when i change it to "_Self".
> >
> > Please note i am trying to open the url in a particular frame and not the
> > full window.
> >
> > And also i see that "javascript:void(window.open
> > 'http://www.google.com','_blank'))" gets pasted in the address bar of the
> IE
> > , removing all the frames and also the page is not loaded untill i press
> the
> > enter key.
> >
> > Please help me find a solution to this.
> >
> > Thanks in advance,
> > Regads,
> > Archana Kamath
> >
> >
> > "Bruce L-C [MVP]" wrote:
> >
> > > For the following you need SP1 or greater installed.
> > >
> > > = "javascript:void(window.open('http://www.google.com','_blank'))"
> > >
> > >
> > > --
> > > Bruce Loehle-Conger
> > > MVP SQL Server Reporting Services
> > >
> > >
> > >
> > > "Misha_SQL" <Misha_SQL@.discussions.microsoft.com> wrote in message
> > > news:AB7CB9AF-6CAA-4A13-9A75-8FF174E9E5D3@.microsoft.com...
> > > > I have a number of dynamically configured hyperlinks in my report.
> > > Clicking
> > > > on them, takes user to a different application. I want for this new
> app
> > > to
> > > > open in a different browser window from the report itself, but instead
> it
> > > > opens in the same window and replaces my report data. Any ideas how
> to
> > > force
> > > > it to open in a new window?
> > > >
> > > > Thank you!
> > >
> > >
> > >
>
>|||Hi Jim,
Javascript works fine in the report.That is when i try opening the report
using the report manager /reportserver it works fine.When i click the link,
the page ('google') gets loaded.
But when i include the same report in my sharepoint site ,using the page
viewer webpart and try accessing the link,it throws up an error saying "Page
cannot be displayed" and the javascript gets pasted on the address bar.
Any idea why this behaviour?
Many thanks inadvance,
Regards,
Archana
"Archana" wrote:
> Thanks Jim for a quick response.
> But i am still facing the problem.
> The problem is Reporting services report expression doesnot accept "<a href>".
> And when i try to give the javascript,it doesnot get exceuted.What it simply
> does is opens up a new browser and paste the javascript on the adress bar.It
> doesnt not get executed untill we press the enter key.
> In most of the solutions for such problems they have suggested to use the
> javascript.But in my case, it doesnt get executed at all.
> Do i need to set any internet settings so that javascript get executed with
> out user pressing the enter key.
> Please help me out with this issue.
> Many Thanks,
> Archana
> "Jim Underwood" wrote:
> > Window.open create a new browser window. You want soemthign like:
> >
> > (javascript)
> > Window.parent.FRAMENAME.src = 'http://www.google.com'
> >
> > or
> > <a href='http://www.google.com' target='FRAMENAME'>Insert Text Here</a>
> >
> > "Archana" <Archana@.discussions.microsoft.com> wrote in message
> > news:992668D3-C45F-4950-ACE0-E7D04FB034DF@.microsoft.com...
> > > Hi Bruce/Misha,
> > >
> > > I tried the solution given below and it opens in the new window with the
> > URL
> > > given,even when i change it to "_Self".
> > >
> > > Please note i am trying to open the url in a particular frame and not the
> > > full window.
> > >
> > > And also i see that "javascript:void(window.open
> > > 'http://www.google.com','_blank'))" gets pasted in the address bar of the
> > IE
> > > , removing all the frames and also the page is not loaded untill i press
> > the
> > > enter key.
> > >
> > > Please help me find a solution to this.
> > >
> > > Thanks in advance,
> > > Regads,
> > > Archana Kamath
> > >
> > >
> > > "Bruce L-C [MVP]" wrote:
> > >
> > > > For the following you need SP1 or greater installed.
> > > >
> > > > = "javascript:void(window.open('http://www.google.com','_blank'))"
> > > >
> > > >
> > > > --
> > > > Bruce Loehle-Conger
> > > > MVP SQL Server Reporting Services
> > > >
> > > >
> > > >
> > > > "Misha_SQL" <Misha_SQL@.discussions.microsoft.com> wrote in message
> > > > news:AB7CB9AF-6CAA-4A13-9A75-8FF174E9E5D3@.microsoft.com...
> > > > > I have a number of dynamically configured hyperlinks in my report.
> > > > Clicking
> > > > > on them, takes user to a different application. I want for this new
> > app
> > > > to
> > > > > open in a different browser window from the report itself, but instead
> > it
> > > > > opens in the same window and replaces my report data. Any ideas how
> > to
> > > > force
> > > > > it to open in a new window?
> > > > >
> > > > > Thank you!
> > > >
> > > >
> > > >
> >
> >
> >|||HI Jim,
Thanks for your help.
The javascript works fine.There was some problem with mysharepoint page.
Anyways Many thanks
Regards,
Archana
"Archana" wrote:
> Hi Jim,
> Javascript works fine in the report.That is when i try opening the report
> using the report manager /reportserver it works fine.When i click the link,
> the page ('google') gets loaded.
> But when i include the same report in my sharepoint site ,using the page
> viewer webpart and try accessing the link,it throws up an error saying "Page
> cannot be displayed" and the javascript gets pasted on the address bar.
> Any idea why this behaviour?
> Many thanks inadvance,
> Regards,
> Archana
> "Archana" wrote:
> > Thanks Jim for a quick response.
> > But i am still facing the problem.
> > The problem is Reporting services report expression doesnot accept "<a href>".
> > And when i try to give the javascript,it doesnot get exceuted.What it simply
> > does is opens up a new browser and paste the javascript on the adress bar.It
> > doesnt not get executed untill we press the enter key.
> >
> > In most of the solutions for such problems they have suggested to use the
> > javascript.But in my case, it doesnt get executed at all.
> >
> > Do i need to set any internet settings so that javascript get executed with
> > out user pressing the enter key.
> >
> > Please help me out with this issue.
> >
> > Many Thanks,
> > Archana
> >
> > "Jim Underwood" wrote:
> >
> > > Window.open create a new browser window. You want soemthign like:
> > >
> > > (javascript)
> > > Window.parent.FRAMENAME.src = 'http://www.google.com'
> > >
> > > or
> > > <a href='http://www.google.com' target='FRAMENAME'>Insert Text Here</a>
> > >
> > > "Archana" <Archana@.discussions.microsoft.com> wrote in message
> > > news:992668D3-C45F-4950-ACE0-E7D04FB034DF@.microsoft.com...
> > > > Hi Bruce/Misha,
> > > >
> > > > I tried the solution given below and it opens in the new window with the
> > > URL
> > > > given,even when i change it to "_Self".
> > > >
> > > > Please note i am trying to open the url in a particular frame and not the
> > > > full window.
> > > >
> > > > And also i see that "javascript:void(window.open
> > > > 'http://www.google.com','_blank'))" gets pasted in the address bar of the
> > > IE
> > > > , removing all the frames and also the page is not loaded untill i press
> > > the
> > > > enter key.
> > > >
> > > > Please help me find a solution to this.
> > > >
> > > > Thanks in advance,
> > > > Regads,
> > > > Archana Kamath
> > > >
> > > >
> > > > "Bruce L-C [MVP]" wrote:
> > > >
> > > > > For the following you need SP1 or greater installed.
> > > > >
> > > > > = "javascript:void(window.open('http://www.google.com','_blank'))"
> > > > >
> > > > >
> > > > > --
> > > > > Bruce Loehle-Conger
> > > > > MVP SQL Server Reporting Services
> > > > >
> > > > >
> > > > >
> > > > > "Misha_SQL" <Misha_SQL@.discussions.microsoft.com> wrote in message
> > > > > news:AB7CB9AF-6CAA-4A13-9A75-8FF174E9E5D3@.microsoft.com...
> > > > > > I have a number of dynamically configured hyperlinks in my report.
> > > > > Clicking
> > > > > > on them, takes user to a different application. I want for this new
> > > app
> > > > > to
> > > > > > open in a different browser window from the report itself, but instead
> > > it
> > > > > > opens in the same window and replaces my report data. Any ideas how
> > > to
> > > > > force
> > > > > > it to open in a new window?
> > > > > >
> > > > > > Thank you!
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >|||I am glad to hear you got it working.
"Archana" <Archana@.discussions.microsoft.com> wrote in message
news:95947B79-1C8F-4850-A528-231B95C82451@.microsoft.com...
> HI Jim,
> Thanks for your help.
> The javascript works fine.There was some problem with mysharepoint page.
> Anyways Many thanks
> Regards,
> Archana
> "Archana" wrote:
> > Hi Jim,
> >
> > Javascript works fine in the report.That is when i try opening the
report
> > using the report manager /reportserver it works fine.When i click the
link,
> > the page ('google') gets loaded.
> >
> > But when i include the same report in my sharepoint site ,using the page
> > viewer webpart and try accessing the link,it throws up an error saying
"Page
> > cannot be displayed" and the javascript gets pasted on the address bar.
> >
> > Any idea why this behaviour?
> > Many thanks inadvance,
> > Regards,
> > Archana
> >
> > "Archana" wrote:
> >
> > > Thanks Jim for a quick response.
> > > But i am still facing the problem.
> > > The problem is Reporting services report expression doesnot accept "<a
href>".
> > > And when i try to give the javascript,it doesnot get exceuted.What it
simply
> > > does is opens up a new browser and paste the javascript on the adress
bar.It
> > > doesnt not get executed untill we press the enter key.
> > >
> > > In most of the solutions for such problems they have suggested to use
the
> > > javascript.But in my case, it doesnt get executed at all.
> > >
> > > Do i need to set any internet settings so that javascript get executed
with
> > > out user pressing the enter key.
> > >
> > > Please help me out with this issue.
> > >
> > > Many Thanks,
> > > Archana
> > >
> > > "Jim Underwood" wrote:
> > >
> > > > Window.open create a new browser window. You want soemthign like:
> > > >
> > > > (javascript)
> > > > Window.parent.FRAMENAME.src = 'http://www.google.com'
> > > >
> > > > or
> > > > <a href='http://www.google.com' target='FRAMENAME'>Insert Text
Here</a>
> > > >
> > > > "Archana" <Archana@.discussions.microsoft.com> wrote in message
> > > > news:992668D3-C45F-4950-ACE0-E7D04FB034DF@.microsoft.com...
> > > > > Hi Bruce/Misha,
> > > > >
> > > > > I tried the solution given below and it opens in the new window
with the
> > > > URL
> > > > > given,even when i change it to "_Self".
> > > > >
> > > > > Please note i am trying to open the url in a particular frame and
not the
> > > > > full window.
> > > > >
> > > > > And also i see that "javascript:void(window.open
> > > > > 'http://www.google.com','_blank'))" gets pasted in the address bar
of the
> > > > IE
> > > > > , removing all the frames and also the page is not loaded untill i
press
> > > > the
> > > > > enter key.
> > > > >
> > > > > Please help me find a solution to this.
> > > > >
> > > > > Thanks in advance,
> > > > > Regads,
> > > > > Archana Kamath
> > > > >
> > > > >
> > > > > "Bruce L-C [MVP]" wrote:
> > > > >
> > > > > > For the following you need SP1 or greater installed.
> > > > > >
> > > > > > ="javascript:void(window.open('http://www.google.com','_blank'))"
> > > > > >
> > > > > >
> > > > > > --
> > > > > > Bruce Loehle-Conger
> > > > > > MVP SQL Server Reporting Services
> > > > > >
> > > > > >
> > > > > >
> > > > > > "Misha_SQL" <Misha_SQL@.discussions.microsoft.com> wrote in
message
> > > > > > news:AB7CB9AF-6CAA-4A13-9A75-8FF174E9E5D3@.microsoft.com...
> > > > > > > I have a number of dynamically configured hyperlinks in my
report.
> > > > > > Clicking
> > > > > > > on them, takes user to a different application. I want for
this new
> > > > app
> > > > > > to
> > > > > > > open in a different browser window from the report itself, but
instead
> > > > it
> > > > > > > opens in the same window and replaces my report data. Any
ideas how
> > > > to
> > > > > > force
> > > > > > > it to open in a new window?
> > > > > > >
> > > > > > > Thank you!
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
Wednesday, March 21, 2012
How Can i Now the RecordSource number of records?
Hi...
I just begining to use the asp.net 2.0 and have tow littel problems... in my project the user makes a "list of problems on a house" when hi post the house number the page shoud to generete a master recod with a keynumber, the date an hour and the state of problems... and a form to insert a list of problems... but if the house dosn′t exist this forms are hidden... then i need to know the nomber of records that gets a recordsource if its more than cero show the forms else hiden it... if show the forms i need to store in a variable the keynumber to store it in the problem details table... ?How i can khow the number of records of a RecordSource gets?... and ?How I can store in a variable a field value retrived for a RecordSource?... I′m using VWD and SQL Express that means ADO.NET 2.0...
cheers.
How are you binding to your datasource? A SqlDataSource, ObjectDataSource, or are you binding to the datasource in your code using a DataTable, DataReader, or DataSet?Monday, March 12, 2012
How can I know No of Connection With My, SQL server!!
Hey Friends !!
How can know the number of connection with our DataBase server !!
Hope it is easy ,
But still i don't know !
Do you mean the current connections to the SQL Server database ?Then you have a look on either the procedure sp_who / sp_who2 or the system table / view
sysprocesses
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
|||Use the windows counters:
General Statistics --> User Connections
Ref: http://msdn2.microsoft.com/en-us/library/ms190697.aspx
|||HI,
you may run sp_who 'Active' which will display all active connections or you may query master..sysprocesses system table also, for more detailed information refer sp_who3 from http://vyaskn.tripod.com/sp_who3.htm
Hemantgiri S. Goswami
How can I know No of Connection With My, SQL server!!
Hey Friends !!
How can know the number of connection with our DataBase server !!
Hope it is easy ,
But still i don't know !
Do you mean the current connections to the SQL Server database ?Then you have a look on either the procedure sp_who / sp_who2 or the system table / view
sysprocesses
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
|||Use the windows counters:
General Statistics --> User Connections
Ref: http://msdn2.microsoft.com/en-us/library/ms190697.aspx
|||HI,
you may run sp_who 'Active' which will display all active connections or you may query master..sysprocesses system table also, for more detailed information refer sp_who3 from http://vyaskn.tripod.com/sp_who3.htm
Hemantgiri S. Goswami
How can I keep only the first 3 digits after decimal with no rounding?
I have a query with a field that returns a decimal number in it, such
as 56.995501432. I would like to display only up to the first 3
digits after the decimal point, like so: 56.995.
I have tried using the FORMAT and FORMATNUMBER functions within the
field expression using a mask of "###,###.000", but both of these want
to round the fourth decimal up, so that the end result is, 56.996
which is not correct for our needs. Why on earth do the format
functions implement rounding!' Is there a way to turn this off? Is
there something like a TRUNCATE function that allows me to specify how
many decimals to keep?
Please help! I'm stuckTry this in the control (not the query) that is displaying the data:
=Left(Fields!Fieldname.Value,Instr(Fields!FieldName.Value,".")-1) + "." +
Mid(Fields!FieldName.Value,Instr(Fields!FieldName.Value)+1,3)
Where
1.Left(Fields!Fieldname.Value,Instr(Fields!FieldName.Value,".")-1) finds
the everything before the decimal
2. Mid(Fields!FieldName.Value,Instr(Fields!FieldName.Value)+1,3) finds 3
digits after the decimal.
Hope this helps!
Michael
"Jerry H." wrote:
> I have a query with a field that returns a decimal number in it, such
> as 56.995501432. I would like to display only up to the first 3
> digits after the decimal point, like so: 56.995.
> I have tried using the FORMAT and FORMATNUMBER functions within the
> field expression using a mask of "###,###.000", but both of these want
> to round the fourth decimal up, so that the end result is, 56.996
> which is not correct for our needs. Why on earth do the format
> functions implement rounding!' Is there a way to turn this off? Is
> there something like a TRUNCATE function that allows me to specify how
> many decimals to keep?
> Please help! I'm stuck
>|||"Michael C" wrote:
> Try this in the control (not the query) that is displaying the data:
> =Left(Fields!Fieldname.Value,Instr(Fields!FieldName.Value,".")-1) + "." +
> Mid(Fields!FieldName.Value,Instr(Fields!FieldName.Value)+1,3)
> Where
> 1.Left(Fields!Fieldname.Value,Instr(Fields!FieldName.Value,".")-1) finds
> the everything before the decimal
> 2. Mid(Fields!FieldName.Value,Instr(Fields!FieldName.Value)+1,3) finds 3
> digits after the decimal.
> Hope this helps!
> Michael
> "Jerry H." wrote:
> > I have a query with a field that returns a decimal number in it, such
> > as 56.995501432. I would like to display only up to the first 3
> > digits after the decimal point, like so: 56.995.
> >
> > I have tried using the FORMAT and FORMATNUMBER functions within the
> > field expression using a mask of "###,###.000", but both of these want
> > to round the fourth decimal up, so that the end result is, 56.996
> > which is not correct for our needs. Why on earth do the format
> > functions implement rounding!' Is there a way to turn this off? Is
> > there something like a TRUNCATE function that allows me to specify how
> > many decimals to keep?
> >
> > Please help! I'm stuck
> >
> >
or) in the properties of the field, in format type : N3
or) use the round function round(field!...,3)
if you don't want rounding ?
=cint(field!...*1000)/1000
hope this helps
as 56.995501432. I would like to display only up to the first 3
digits after the decimal point, like so: 56.995.
I have tried using the FORMAT and FORMATNUMBER functions within the
field expression using a mask of "###,###.000", but both of these want
to round the fourth decimal up, so that the end result is, 56.996
which is not correct for our needs. Why on earth do the format
functions implement rounding!' Is there a way to turn this off? Is
there something like a TRUNCATE function that allows me to specify how
many decimals to keep?
Please help! I'm stuckTry this in the control (not the query) that is displaying the data:
=Left(Fields!Fieldname.Value,Instr(Fields!FieldName.Value,".")-1) + "." +
Mid(Fields!FieldName.Value,Instr(Fields!FieldName.Value)+1,3)
Where
1.Left(Fields!Fieldname.Value,Instr(Fields!FieldName.Value,".")-1) finds
the everything before the decimal
2. Mid(Fields!FieldName.Value,Instr(Fields!FieldName.Value)+1,3) finds 3
digits after the decimal.
Hope this helps!
Michael
"Jerry H." wrote:
> I have a query with a field that returns a decimal number in it, such
> as 56.995501432. I would like to display only up to the first 3
> digits after the decimal point, like so: 56.995.
> I have tried using the FORMAT and FORMATNUMBER functions within the
> field expression using a mask of "###,###.000", but both of these want
> to round the fourth decimal up, so that the end result is, 56.996
> which is not correct for our needs. Why on earth do the format
> functions implement rounding!' Is there a way to turn this off? Is
> there something like a TRUNCATE function that allows me to specify how
> many decimals to keep?
> Please help! I'm stuck
>|||"Michael C" wrote:
> Try this in the control (not the query) that is displaying the data:
> =Left(Fields!Fieldname.Value,Instr(Fields!FieldName.Value,".")-1) + "." +
> Mid(Fields!FieldName.Value,Instr(Fields!FieldName.Value)+1,3)
> Where
> 1.Left(Fields!Fieldname.Value,Instr(Fields!FieldName.Value,".")-1) finds
> the everything before the decimal
> 2. Mid(Fields!FieldName.Value,Instr(Fields!FieldName.Value)+1,3) finds 3
> digits after the decimal.
> Hope this helps!
> Michael
> "Jerry H." wrote:
> > I have a query with a field that returns a decimal number in it, such
> > as 56.995501432. I would like to display only up to the first 3
> > digits after the decimal point, like so: 56.995.
> >
> > I have tried using the FORMAT and FORMATNUMBER functions within the
> > field expression using a mask of "###,###.000", but both of these want
> > to round the fourth decimal up, so that the end result is, 56.996
> > which is not correct for our needs. Why on earth do the format
> > functions implement rounding!' Is there a way to turn this off? Is
> > there something like a TRUNCATE function that allows me to specify how
> > many decimals to keep?
> >
> > Please help! I'm stuck
> >
> >
or) in the properties of the field, in format type : N3
or) use the round function round(field!...,3)
if you don't want rounding ?
=cint(field!...*1000)/1000
hope this helps
Friday, March 9, 2012
How can I increase the maximum number of parallel connections when exporting to Oracle via
Hi to everybody,
When exporting to Oracle from SQL Server using Import/Export Wizard,
I've discovered that SQL Server only creates 4 simultaneous
connections to Oracle. Obviously this fact prevents SQL Server from
exporting the tables in a parallel way. Instead, the tables are forced
to wait until one of the 4 connections gets free to export and due to
this fact the performance of the export operation suffers from an
enormous slow down.
Even though I create new connections in the DTS Designer (up to 20),
SQL Server 2K continues only using the same 4 ones which makes me
suspect that there must be a hidden option somewhere to change this.
Is there any way in SQL Server 2K to increase the number of maximum
simultaneous connections to Oracle?
Thank your from beforehand for reading my post.
Greetings,
David GrantBy default, DTS executes only 4 tasks concurrently. Check how to change this
at
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dtsdsgnr/dtsdsgnr2_60mp.asp
(the last option on the General tab of package properties).
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"David Grant" <icebold54@.hotmail.com> wrote in message
news:18503386.0504181032.2b96883c@.posting.google.com...
> Hi to everybody,
> When exporting to Oracle from SQL Server using Import/Export Wizard,
> I've discovered that SQL Server only creates 4 simultaneous
> connections to Oracle. Obviously this fact prevents SQL Server from
> exporting the tables in a parallel way. Instead, the tables are forced
> to wait until one of the 4 connections gets free to export and due to
> this fact the performance of the export operation suffers from an
> enormous slow down.
>
> Even though I create new connections in the DTS Designer (up to 20),
> SQL Server 2K continues only using the same 4 ones which makes me
> suspect that there must be a hidden option somewhere to change this.
> Is there any way in SQL Server 2K to increase the number of maximum
> simultaneous connections to Oracle?
>
> Thank your from beforehand for reading my post.
> Greetings,
> David Grant|||In addition to Dejan's excellent advice, note that you will need to look at how optimal hiking the tasks in parallel actually is.
If you are running on a 4 way then hiking this value to 20 would not be a good thing and you would see a lot of context switches as
things come in and off the processors. I would start with CPU Count -1
Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - SQL Server 2005 Integration Services.
www.Konesans.com
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in message news:%23RTgwCFRFHA.204@.TK2MSFTNGP15.phx.gbl...
> By default, DTS executes only 4 tasks concurrently. Check how to change this
> at
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dtsdsgnr/dtsdsgnr2_60mp.asp
> (the last option on the General tab of package properties).
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> www.SolidQualityLearning.com
> "David Grant" <icebold54@.hotmail.com> wrote in message
> news:18503386.0504181032.2b96883c@.posting.google.com...
>> Hi to everybody,
>> When exporting to Oracle from SQL Server using Import/Export Wizard,
>> I've discovered that SQL Server only creates 4 simultaneous
>> connections to Oracle. Obviously this fact prevents SQL Server from
>> exporting the tables in a parallel way. Instead, the tables are forced
>> to wait until one of the 4 connections gets free to export and due to
>> this fact the performance of the export operation suffers from an
>> enormous slow down.
>>
>> Even though I create new connections in the DTS Designer (up to 20),
>> SQL Server 2K continues only using the same 4 ones which makes me
>> suspect that there must be a hidden option somewhere to change this.
>> Is there any way in SQL Server 2K to increase the number of maximum
>> simultaneous connections to Oracle?
>>
>> Thank your from beforehand for reading my post.
>> Greetings,
>> David Grant
>
When exporting to Oracle from SQL Server using Import/Export Wizard,
I've discovered that SQL Server only creates 4 simultaneous
connections to Oracle. Obviously this fact prevents SQL Server from
exporting the tables in a parallel way. Instead, the tables are forced
to wait until one of the 4 connections gets free to export and due to
this fact the performance of the export operation suffers from an
enormous slow down.
Even though I create new connections in the DTS Designer (up to 20),
SQL Server 2K continues only using the same 4 ones which makes me
suspect that there must be a hidden option somewhere to change this.
Is there any way in SQL Server 2K to increase the number of maximum
simultaneous connections to Oracle?
Thank your from beforehand for reading my post.
Greetings,
David GrantBy default, DTS executes only 4 tasks concurrently. Check how to change this
at
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dtsdsgnr/dtsdsgnr2_60mp.asp
(the last option on the General tab of package properties).
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"David Grant" <icebold54@.hotmail.com> wrote in message
news:18503386.0504181032.2b96883c@.posting.google.com...
> Hi to everybody,
> When exporting to Oracle from SQL Server using Import/Export Wizard,
> I've discovered that SQL Server only creates 4 simultaneous
> connections to Oracle. Obviously this fact prevents SQL Server from
> exporting the tables in a parallel way. Instead, the tables are forced
> to wait until one of the 4 connections gets free to export and due to
> this fact the performance of the export operation suffers from an
> enormous slow down.
>
> Even though I create new connections in the DTS Designer (up to 20),
> SQL Server 2K continues only using the same 4 ones which makes me
> suspect that there must be a hidden option somewhere to change this.
> Is there any way in SQL Server 2K to increase the number of maximum
> simultaneous connections to Oracle?
>
> Thank your from beforehand for reading my post.
> Greetings,
> David Grant|||In addition to Dejan's excellent advice, note that you will need to look at how optimal hiking the tasks in parallel actually is.
If you are running on a 4 way then hiking this value to 20 would not be a good thing and you would see a lot of context switches as
things come in and off the processors. I would start with CPU Count -1
Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - SQL Server 2005 Integration Services.
www.Konesans.com
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in message news:%23RTgwCFRFHA.204@.TK2MSFTNGP15.phx.gbl...
> By default, DTS executes only 4 tasks concurrently. Check how to change this
> at
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dtsdsgnr/dtsdsgnr2_60mp.asp
> (the last option on the General tab of package properties).
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> www.SolidQualityLearning.com
> "David Grant" <icebold54@.hotmail.com> wrote in message
> news:18503386.0504181032.2b96883c@.posting.google.com...
>> Hi to everybody,
>> When exporting to Oracle from SQL Server using Import/Export Wizard,
>> I've discovered that SQL Server only creates 4 simultaneous
>> connections to Oracle. Obviously this fact prevents SQL Server from
>> exporting the tables in a parallel way. Instead, the tables are forced
>> to wait until one of the 4 connections gets free to export and due to
>> this fact the performance of the export operation suffers from an
>> enormous slow down.
>>
>> Even though I create new connections in the DTS Designer (up to 20),
>> SQL Server 2K continues only using the same 4 ones which makes me
>> suspect that there must be a hidden option somewhere to change this.
>> Is there any way in SQL Server 2K to increase the number of maximum
>> simultaneous connections to Oracle?
>>
>> Thank your from beforehand for reading my post.
>> Greetings,
>> David Grant
>
How can I increase the maximum number of parallel connections when exporting to Oracle via
Hi to everybody,
When exporting to Oracle from SQL Server using Import/Export Wizard,
I've discovered that SQL Server only creates 4 simultaneous
connections to Oracle. Obviously this fact prevents SQL Server from
exporting the tables in a parallel way. Instead, the tables are forced
to wait until one of the 4 connections gets free to export and due to
this fact the performance of the export operation suffers from an
enormous slow down.
Even though I create new connections in the DTS Designer (up to 20),
SQL Server 2K continues only using the same 4 ones which makes me
suspect that there must be a hidden option somewhere to change this.
Is there any way in SQL Server 2K to increase the number of maximum
simultaneous connections to Oracle?
Thank your from beforehand for reading my post.
Greetings,
David Grant
By default, DTS executes only 4 tasks concurrently. Check how to change this
at
http://msdn.microsoft.com/library/de...sgnr2_60mp.asp
(the last option on the General tab of package properties).
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"David Grant" <icebold54@.hotmail.com> wrote in message
news:18503386.0504181032.2b96883c@.posting.google.c om...
> Hi to everybody,
> When exporting to Oracle from SQL Server using Import/Export Wizard,
> I've discovered that SQL Server only creates 4 simultaneous
> connections to Oracle. Obviously this fact prevents SQL Server from
> exporting the tables in a parallel way. Instead, the tables are forced
> to wait until one of the 4 connections gets free to export and due to
> this fact the performance of the export operation suffers from an
> enormous slow down.
>
> Even though I create new connections in the DTS Designer (up to 20),
> SQL Server 2K continues only using the same 4 ones which makes me
> suspect that there must be a hidden option somewhere to change this.
> Is there any way in SQL Server 2K to increase the number of maximum
> simultaneous connections to Oracle?
>
> Thank your from beforehand for reading my post.
> Greetings,
> David Grant
|||In addition to Dejan's excellent advice, note that you will need to look at how optimal hiking the tasks in parallel actually is.
If you are running on a 4 way then hiking this value to 20 would not be a good thing and you would see a lot of context switches as
things come in and off the processors. I would start with CPU Count -1
Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - SQL Server 2005 Integration Services.
www.Konesans.com
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si > wrote in message news:%23RTgwCFRFHA.204@.TK2MSFTNGP15.phx.gbl...
> By default, DTS executes only 4 tasks concurrently. Check how to change this
> at
> http://msdn.microsoft.com/library/de...sgnr2_60mp.asp
> (the last option on the General tab of package properties).
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> www.SolidQualityLearning.com
> "David Grant" <icebold54@.hotmail.com> wrote in message
> news:18503386.0504181032.2b96883c@.posting.google.c om...
>
When exporting to Oracle from SQL Server using Import/Export Wizard,
I've discovered that SQL Server only creates 4 simultaneous
connections to Oracle. Obviously this fact prevents SQL Server from
exporting the tables in a parallel way. Instead, the tables are forced
to wait until one of the 4 connections gets free to export and due to
this fact the performance of the export operation suffers from an
enormous slow down.
Even though I create new connections in the DTS Designer (up to 20),
SQL Server 2K continues only using the same 4 ones which makes me
suspect that there must be a hidden option somewhere to change this.
Is there any way in SQL Server 2K to increase the number of maximum
simultaneous connections to Oracle?
Thank your from beforehand for reading my post.
Greetings,
David Grant
By default, DTS executes only 4 tasks concurrently. Check how to change this
at
http://msdn.microsoft.com/library/de...sgnr2_60mp.asp
(the last option on the General tab of package properties).
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"David Grant" <icebold54@.hotmail.com> wrote in message
news:18503386.0504181032.2b96883c@.posting.google.c om...
> Hi to everybody,
> When exporting to Oracle from SQL Server using Import/Export Wizard,
> I've discovered that SQL Server only creates 4 simultaneous
> connections to Oracle. Obviously this fact prevents SQL Server from
> exporting the tables in a parallel way. Instead, the tables are forced
> to wait until one of the 4 connections gets free to export and due to
> this fact the performance of the export operation suffers from an
> enormous slow down.
>
> Even though I create new connections in the DTS Designer (up to 20),
> SQL Server 2K continues only using the same 4 ones which makes me
> suspect that there must be a hidden option somewhere to change this.
> Is there any way in SQL Server 2K to increase the number of maximum
> simultaneous connections to Oracle?
>
> Thank your from beforehand for reading my post.
> Greetings,
> David Grant
|||In addition to Dejan's excellent advice, note that you will need to look at how optimal hiking the tasks in parallel actually is.
If you are running on a 4 way then hiking this value to 20 would not be a good thing and you would see a lot of context switches as
things come in and off the processors. I would start with CPU Count -1
Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - SQL Server 2005 Integration Services.
www.Konesans.com
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si > wrote in message news:%23RTgwCFRFHA.204@.TK2MSFTNGP15.phx.gbl...
> By default, DTS executes only 4 tasks concurrently. Check how to change this
> at
> http://msdn.microsoft.com/library/de...sgnr2_60mp.asp
> (the last option on the General tab of package properties).
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> www.SolidQualityLearning.com
> "David Grant" <icebold54@.hotmail.com> wrote in message
> news:18503386.0504181032.2b96883c@.posting.google.c om...
>
How can I increase the maximum number of parallel connections when exporting to Oracle via
Hi to everybody,
When exporting to Oracle from SQL Server using Import/Export Wizard,
I've discovered that SQL Server only creates 4 simultaneous
connections to Oracle. Obviously this fact prevents SQL Server from
exporting the tables in a parallel way. Instead, the tables are forced
to wait until one of the 4 connections gets free to export and due to
this fact the performance of the export operation suffers from an
enormous slow down.
Even though I create new connections in the DTS Designer (up to 20),
SQL Server 2K continues only using the same 4 ones which makes me
suspect that there must be a hidden option somewhere to change this.
Is there any way in SQL Server 2K to increase the number of maximum
simultaneous connections to Oracle?
Thank your from beforehand for reading my post.
Greetings,
David Grant
By default, DTS executes only 4 tasks concurrently. Check how to change this
at
http://msdn.microsoft.com/library/de...sgnr2_60mp.asp
(the last option on the General tab of package properties).
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"David Grant" <icebold54@.hotmail.com> wrote in message
news:18503386.0504181032.2b96883c@.posting.google.c om...
> Hi to everybody,
> When exporting to Oracle from SQL Server using Import/Export Wizard,
> I've discovered that SQL Server only creates 4 simultaneous
> connections to Oracle. Obviously this fact prevents SQL Server from
> exporting the tables in a parallel way. Instead, the tables are forced
> to wait until one of the 4 connections gets free to export and due to
> this fact the performance of the export operation suffers from an
> enormous slow down.
>
> Even though I create new connections in the DTS Designer (up to 20),
> SQL Server 2K continues only using the same 4 ones which makes me
> suspect that there must be a hidden option somewhere to change this.
> Is there any way in SQL Server 2K to increase the number of maximum
> simultaneous connections to Oracle?
>
> Thank your from beforehand for reading my post.
> Greetings,
> David Grant
|||In addition to Dejan's excellent advice, note that you will need to look at how optimal hiking the tasks in parallel actually is.
If you are running on a 4 way then hiking this value to 20 would not be a good thing and you would see a lot of context switches as
things come in and off the processors. I would start with CPU Count -1
Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - SQL Server 2005 Integration Services.
www.Konesans.com
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si > wrote in message news:%23RTgwCFRFHA.204@.TK2MSFTNGP15.phx.gbl...
> By default, DTS executes only 4 tasks concurrently. Check how to change this
> at
> http://msdn.microsoft.com/library/de...sgnr2_60mp.asp
> (the last option on the General tab of package properties).
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> www.SolidQualityLearning.com
> "David Grant" <icebold54@.hotmail.com> wrote in message
> news:18503386.0504181032.2b96883c@.posting.google.c om...
>
When exporting to Oracle from SQL Server using Import/Export Wizard,
I've discovered that SQL Server only creates 4 simultaneous
connections to Oracle. Obviously this fact prevents SQL Server from
exporting the tables in a parallel way. Instead, the tables are forced
to wait until one of the 4 connections gets free to export and due to
this fact the performance of the export operation suffers from an
enormous slow down.
Even though I create new connections in the DTS Designer (up to 20),
SQL Server 2K continues only using the same 4 ones which makes me
suspect that there must be a hidden option somewhere to change this.
Is there any way in SQL Server 2K to increase the number of maximum
simultaneous connections to Oracle?
Thank your from beforehand for reading my post.
Greetings,
David Grant
By default, DTS executes only 4 tasks concurrently. Check how to change this
at
http://msdn.microsoft.com/library/de...sgnr2_60mp.asp
(the last option on the General tab of package properties).
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"David Grant" <icebold54@.hotmail.com> wrote in message
news:18503386.0504181032.2b96883c@.posting.google.c om...
> Hi to everybody,
> When exporting to Oracle from SQL Server using Import/Export Wizard,
> I've discovered that SQL Server only creates 4 simultaneous
> connections to Oracle. Obviously this fact prevents SQL Server from
> exporting the tables in a parallel way. Instead, the tables are forced
> to wait until one of the 4 connections gets free to export and due to
> this fact the performance of the export operation suffers from an
> enormous slow down.
>
> Even though I create new connections in the DTS Designer (up to 20),
> SQL Server 2K continues only using the same 4 ones which makes me
> suspect that there must be a hidden option somewhere to change this.
> Is there any way in SQL Server 2K to increase the number of maximum
> simultaneous connections to Oracle?
>
> Thank your from beforehand for reading my post.
> Greetings,
> David Grant
|||In addition to Dejan's excellent advice, note that you will need to look at how optimal hiking the tasks in parallel actually is.
If you are running on a 4 way then hiking this value to 20 would not be a good thing and you would see a lot of context switches as
things come in and off the processors. I would start with CPU Count -1
Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - SQL Server 2005 Integration Services.
www.Konesans.com
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si > wrote in message news:%23RTgwCFRFHA.204@.TK2MSFTNGP15.phx.gbl...
> By default, DTS executes only 4 tasks concurrently. Check how to change this
> at
> http://msdn.microsoft.com/library/de...sgnr2_60mp.asp
> (the last option on the General tab of package properties).
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> www.SolidQualityLearning.com
> "David Grant" <icebold54@.hotmail.com> wrote in message
> news:18503386.0504181032.2b96883c@.posting.google.c om...
>
How can I increase the maximum number of parallel connections when exporting to Oracle
Hi to everybody,
When exporting to Oracle from SQL Server using Import/Export Wizard,
I've discovered that SQL Server only creates 4 simultaneous
connections to Oracle. Obviously this fact prevents SQL Server from
exporting the tables in a parallel way. Instead, the tables are forced
to wait until one of the 4 connections gets free to export and due to
this fact the performance of the export operation suffers from an
enormous slow down.
Even though I create new connections in the DTS Designer (up to 20),
SQL Server 2K continues only using the same 4 ones which makes me
suspect that there must be a hidden option somewhere to change this.
Is there any way in SQL Server 2K to increase the number of maximum
simultaneous connections to Oracle?
Thank your from beforehand for reading my post.
Greetings,
David GrantBy default, DTS executes only 4 tasks concurrently. Check how to change this
at
2_60mp.asp" target="_blank">http://msdn.microsoft.com/library/d... />
2_60mp.asp
(the last option on the General tab of package properties).
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"David Grant" <icebold54@.hotmail.com> wrote in message
news:18503386.0504181032.2b96883c@.posting.google.com...
> Hi to everybody,
> When exporting to Oracle from SQL Server using Import/Export Wizard,
> I've discovered that SQL Server only creates 4 simultaneous
> connections to Oracle. Obviously this fact prevents SQL Server from
> exporting the tables in a parallel way. Instead, the tables are forced
> to wait until one of the 4 connections gets free to export and due to
> this fact the performance of the export operation suffers from an
> enormous slow down.
>
> Even though I create new connections in the DTS Designer (up to 20),
> SQL Server 2K continues only using the same 4 ones which makes me
> suspect that there must be a hidden option somewhere to change this.
> Is there any way in SQL Server 2K to increase the number of maximum
> simultaneous connections to Oracle?
>
> Thank your from beforehand for reading my post.
> Greetings,
> David Grant|||In addition to Dejan's excellent advice, note that you will need to look at
how optimal hiking the tasks in parallel actually is.
If you are running on a 4 way then hiking this value to 20 would not be a go
od thing and you would see a lot of context switches as
things come in and off the processors. I would start with CPU Count -1
Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - SQL Server 2005 Integration Services.
www.Konesans.com
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in message news:%23R
TgwCFRFHA.204@.TK2MSFTNGP15.phx.gbl...
> By default, DTS executes only 4 tasks concurrently. Check how to change th
is
> at
> nr2_60mp.asp" target="_blank">http://msdn.microsoft.com/library/d...>
nr2_60mp.asp
> (the last option on the General tab of package properties).
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> www.SolidQualityLearning.com
> "David Grant" <icebold54@.hotmail.com> wrote in message
> news:18503386.0504181032.2b96883c@.posting.google.com...
>
When exporting to Oracle from SQL Server using Import/Export Wizard,
I've discovered that SQL Server only creates 4 simultaneous
connections to Oracle. Obviously this fact prevents SQL Server from
exporting the tables in a parallel way. Instead, the tables are forced
to wait until one of the 4 connections gets free to export and due to
this fact the performance of the export operation suffers from an
enormous slow down.
Even though I create new connections in the DTS Designer (up to 20),
SQL Server 2K continues only using the same 4 ones which makes me
suspect that there must be a hidden option somewhere to change this.
Is there any way in SQL Server 2K to increase the number of maximum
simultaneous connections to Oracle?
Thank your from beforehand for reading my post.
Greetings,
David GrantBy default, DTS executes only 4 tasks concurrently. Check how to change this
at
2_60mp.asp" target="_blank">http://msdn.microsoft.com/library/d... />
2_60mp.asp
(the last option on the General tab of package properties).
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"David Grant" <icebold54@.hotmail.com> wrote in message
news:18503386.0504181032.2b96883c@.posting.google.com...
> Hi to everybody,
> When exporting to Oracle from SQL Server using Import/Export Wizard,
> I've discovered that SQL Server only creates 4 simultaneous
> connections to Oracle. Obviously this fact prevents SQL Server from
> exporting the tables in a parallel way. Instead, the tables are forced
> to wait until one of the 4 connections gets free to export and due to
> this fact the performance of the export operation suffers from an
> enormous slow down.
>
> Even though I create new connections in the DTS Designer (up to 20),
> SQL Server 2K continues only using the same 4 ones which makes me
> suspect that there must be a hidden option somewhere to change this.
> Is there any way in SQL Server 2K to increase the number of maximum
> simultaneous connections to Oracle?
>
> Thank your from beforehand for reading my post.
> Greetings,
> David Grant|||In addition to Dejan's excellent advice, note that you will need to look at
how optimal hiking the tasks in parallel actually is.
If you are running on a 4 way then hiking this value to 20 would not be a go
od thing and you would see a lot of context switches as
things come in and off the processors. I would start with CPU Count -1
Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - SQL Server 2005 Integration Services.
www.Konesans.com
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in message news:%23R
TgwCFRFHA.204@.TK2MSFTNGP15.phx.gbl...
> By default, DTS executes only 4 tasks concurrently. Check how to change th
is
> at
> nr2_60mp.asp" target="_blank">http://msdn.microsoft.com/library/d...>
nr2_60mp.asp
> (the last option on the General tab of package properties).
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> www.SolidQualityLearning.com
> "David Grant" <icebold54@.hotmail.com> wrote in message
> news:18503386.0504181032.2b96883c@.posting.google.com...
>
Wednesday, March 7, 2012
How can I have a variable number of parameter values in a dataset?
I have a strongly typed dataset, and I need to be able to do a search on multiple values of a parameter. The problem is I don't know how many. I have a textbox that the user can enter search words in. The select string is built from the string of words that are entered, like this:
For iCount = 0 To UBound(sArray)
strSQL = strSQL & "Description LIKE '%" & sArray(iCount) & "%' OR "
Next
Can I do this is a dataset method? How?
If I can't, what are my options?
Diane
make use of temp table or table variable
declare @.word table
(
word varchar(100)
)
select *
from sometable t inner join @.word w
on t.Description like '%' + w.word + '%'
|||Hi,
From the question you mentioned, do you want to make a query with multiple input variables? If so, I just want to know how to split them in your solution?
Actually, the user input the keywords in the textbox and they use space on the keyboard to split each word. And when we recieve the request from the user,
we may use split method to separate each words into array.
string s = "keyword1 keyword2 keyword3";
string[] myar = s.Split(' ');
for (int i = 0; i < myar.Length; i++) {
strSQL = strSQL & "Description LIKE '%" & myar(i) & "%' OR "
}
After the sql statement is created, you may put it into the SqlDataAdapter and fill into a DataSet. So you can get the query result which matches the multiple keywords.
SqlDataAdapter myadpt = new SqlDataAdapter(strSQL, myconn);
DataSet myds = new DataSet();
myadpt.Fill(myds);
If this does not answer your question, please feel free to reply. Thank you!
|||Hi,
You can search more efficiently using 'Contains' in SQL server. For this you need to enable Full-Text search in sql server for a field.
Contains(FieldName, '+''''+@.GroupKeyWord+''''+')'
@.GroupKeyWord is the keyword you can directly pass, i.e. the user input string. See SQL Server help for more details. It is really interesting. If you don't want to use this feature then you can go for split. But split needs a delimiter string, i.e. a single space or a particular character. The resultant array can be looped from lower bound to upper bound to create Sql statement.
The advantage of using contains is its flexibility. User can create Boolean search himself and you just have to pass the value user entered into the textbox directly to the sql statement.
Don't forget to mark this as Answer if this post helps you
KH, you have me totally confused <grin>!
Michael, I'm not having a problem spliting the variables. Say I have a table with fields name, company, address, description. I know how to use a parameter in a strongly typed dataset, say to search for Name=@.name in a method that expects a name parameter. What I need is a method that returns all records where the description field contains one or more of the words the user entered. I can build the select string, but I don't know how to implement this in a dataset method.
Hamlin, this would be optimal. Unfortunately, full text search isn't enabled, and I can't get it enabled. I asked my host, and they won't permit it.
I'm wondering if I should maybe use an SqlDataSource instead of the dataset?
Diane
|||Hi Mainship,
Sorry for my misunderstanding. Now I know that your problem is focused on how to implement the select string in a dataset method cause you don't know the number of parameter values.
Actually you can use DataSet.TableName.Select(SelectString) Method instead of inputing the select string in the TableAdapter configuration wizard. For example:
northwindDataSet.Products.Select(string);
For more details about DataTable.Select Method ,pls check:http://msdn2.microsoft.com/en-us/library/system.data.datatable.select(VS.80).aspx
If this does not answer your question, please feel free to mark the post as Not Answered and reply. Thank you!
|||Thank you!
Diane
Monday, February 27, 2012
how can I get the row number(error row) while constraint failure at the OLE DB destination?
my project is insert data to OLE DB destination from csv file.
my question is
how can I get the row number(error row) while constraint failure at the OLE DB destination?
thanks.
You can't. There is no concept of row numbers in the SSIS pipeline (for good reasons).
What you CAN do is divert the erroring rows elsewhere for examination later.
-Jamie
|||thank you.
it saves me a lot of time.
|||What good reasons ?how can I get the row number(error row) while constraint failure at the OLE DB destination?
my project is insert data to OLE DB destination from csv file.
my question is
how can I get the row number(error row) while constraint failure at the OLE DB destination?
thanks.
You can't. There is no concept of row numbers in the SSIS pipeline (for good reasons).
What you CAN do is divert the erroring rows elsewhere for examination later.
-Jamie
|||
thank you.
it saves me a lot of time.
|||What good reasons ?How can I get the log sequence number on C#?
Hi! I would like to get the log sequence number from the database backup so
that I can let the users select the restored time. Can I do this with the
SMO?
Thanks!One way would be to use methods such as the Database.ExecuteWithResults method to execute your
RESTORE HEADERONLY command and loop the result (DataTable in the DataSet) from that method.
Or, you should be able to use BackupDevice.ReadBackupHeader method and read the returned DataTable.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Cheryl" <justtosayhi@.excite.com> wrote in message
news:FBB10E85-25FF-49B7-96D4-97ADE7552575@.microsoft.com...
> Hi! I would like to get the log sequence number from the database backup so that I can let the
> users select the restored time. Can I do this with the SMO?
> Thanks!
that I can let the users select the restored time. Can I do this with the
SMO?
Thanks!One way would be to use methods such as the Database.ExecuteWithResults method to execute your
RESTORE HEADERONLY command and loop the result (DataTable in the DataSet) from that method.
Or, you should be able to use BackupDevice.ReadBackupHeader method and read the returned DataTable.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Cheryl" <justtosayhi@.excite.com> wrote in message
news:FBB10E85-25FF-49B7-96D4-97ADE7552575@.microsoft.com...
> Hi! I would like to get the log sequence number from the database backup so that I can let the
> users select the restored time. Can I do this with the SMO?
> Thanks!
Friday, February 24, 2012
How can I get the actual line number from inside flatfile sources for each record?
hi all,
maybe this is not a serious problem, but I tried for days to come toi a solution without success.
My Problem:
I have have several flatfile sources I need to import into a sql-server 2005 DB.
It is very important for me to have the original line number from inside the source file for each record. The rowcount transform doesn't fit in for this task, because it accumulates all rows until the end of the dataflow.
I tried script components and it works fine if i assume there are no errors in my source. then I simply could declare a local variable and count it up and add a custom collumn to my output. But for errors in my source this won't work, because a second script component won't know the actual value of a package level variable, which i use to store the value, because i am only allowed to access this variable in the post execujte method of the script.
How can I achieve my goal? Please help me...
Thanks in advance .. Bernd
Can I clarify something?
Do you want the number of rows in the file or the row number adding for each row? Your post above is a little ambiguous about this because you say "original line number from inside the source" but then mention the rowcount component which is nothing to do with line numbers.
-Jamie
|||
I want the line number of the Flat File, which holds the Record.
I don't have them in the source. you may think of it as an Row ID.
Thanks Bernd
|||OK, gotcha. Well the rowcount component is completely irrelevant here.
SSIS does not give row numbers to rows in the pipeline. I've previously asked for this to be changed which you can vote for and add a comment on here if you like: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=131335
In the meantime, the alternative is to artifically create your own row numbers as explained here: http://www.sqlis.com/default.aspx?37 however this may not work for you as the row numbers cannot be added until after the rows have been extracted into the pipeline. It depends on your requirement.
If you need row numbers added as the file is parsed then you're in script source component or custom component territory I'm afraid.
-Jamie
|||
Thank you very, even if my problem isn't solved.
Personally I think it is a major flaw, because we are not able to track direcly back into the source, which sometimes is extremely important. I support your request for adding this information into the Pipeline without question.
Thank you again
Bernd
|||berndS wrote:
Thank you very, even if my problem isn't solved.
Personally I think it is a major flaw, because we are not able to track direcly back into the source, which sometimes is extremely important. I support your request for adding this information into the Pipeline without question.
Thank you again
Bernd
Thanks Bernd,
In that case, click the first link in my last post and vote for it. I forgot to add the link in before (sorry) but I have now edited it!
I also think this is a really important feature!
-Jamie
|||
Hmm, hope it's OK to play devil's advocate here. How would this work in a data flow merge, conditional split, etc? Seems like a lot of the processing that is needed in ETL and supported in SSIS would confound the meaning of source row number?
|||
Hmm, hope it's OK to play devil's advocate here. How would this work in a data flow merge, conditional split, etc? Seems like a lot of the processing that is needed in ETL and supported in SSIS would confound the meaning of source row number?
Ken
Yeah of course it would, absolutely - in which case it has no meaning and you don't use it. Ideally you'd simply have a boolean property on every source adapter saying whether or not to add this "rownumber" column.
If its there and you don't want it - ignore it!
I'd still like to have this feature.
-Jamie
|||
I was just wondering if this might be one feature that sounds good on paper but has limited practical value. Since I insist on data models and domains that make use of natural keys (and always design solutions based on this paradigm), I can't see how it would be of much use in my work. If I ever need to find a row, I always can do so by using the key...
|||
I was just wondering if this might be one feature that sounds good on paper but has limited practical value. Since I insist on data models and domains that make use of natural keys (and always design solutions based on this paradigm), I can't see how it would be of much use in my work. If I ever need to find a row, I always can do so by using the key...
True enough. Where I think it would be useful would be when importing a file. If a row goes down the error flow it'd be nice to know which row it was.
-Jamie
|||
Jamie,
is there any way to get the error details from a source connector when you set it to ignore errors? Otherwise it would be easy to add the row number by a counter (there are some ways to do that, you know)... The only problem are the error lines. SSIS simply changes the value of a non-readable field to NULL. You don't get any error information (at least that's what I see). If there would be any way to find out that a record has an error (and probably what kind of error in which field) then we could just ignore all errors first, add the counter and do some manual error handling later...
Thanks,
|||
Jamie,
is there any way to get the error details from a source connector when you set it to ignore errors?
I don't think so Thomas, no. Do these errors trigger the OnError eventhandler? I'm 99% sure they don't but don't have a SSIS instance to hand to check.
-Jamie
How can I get the actual line number from inside flatfile sources for each record?
hi all,
maybe this is not a serious problem, but I tried for days to come toi a solution without success.
My Problem:
I have have several flatfile sources I need to import into a sql-server 2005 DB.
It is very important for me to have the original line number from inside the source file for each record. The rowcount transform doesn't fit in for this task, because it accumulates all rows until the end of the dataflow.
I tried script components and it works fine if i assume there are no errors in my source. then I simply could declare a local variable and count it up and add a custom collumn to my output. But for errors in my source this won't work, because a second script component won't know the actual value of a package level variable, which i use to store the value, because i am only allowed to access this variable in the post execujte method of the script.
How can I achieve my goal? Please help me...
Thanks in advance .. Bernd
Can I clarify something?
Do you want the number of rows in the file or the row number adding for each row? Your post above is a little ambiguous about this because you say "original line number from inside the source" but then mention the rowcount component which is nothing to do with line numbers.
-Jamie
|||
I want the line number of the Flat File, which holds the Record.
I don't have them in the source. you may think of it as an Row ID.
Thanks Bernd
|||OK, gotcha. Well the rowcount component is completely irrelevant here.
SSIS does not give row numbers to rows in the pipeline. I've previously asked for this to be changed which you can vote for and add a comment on here if you like: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=131335
In the meantime, the alternative is to artifically create your own row numbers as explained here: http://www.sqlis.com/default.aspx?37 however this may not work for you as the row numbers cannot be added until after the rows have been extracted into the pipeline. It depends on your requirement.
If you need row numbers added as the file is parsed then you're in script source component or custom component territory I'm afraid.
-Jamie
|||
Thank you very, even if my problem isn't solved.
Personally I think it is a major flaw, because we are not able to track direcly back into the source, which sometimes is extremely important. I support your request for adding this information into the Pipeline without question.
Thank you again
Bernd
|||berndS wrote:
Thank you very, even if my problem isn't solved.
Personally I think it is a major flaw, because we are not able to track direcly back into the source, which sometimes is extremely important. I support your request for adding this information into the Pipeline without question.
Thank you again
Bernd
Thanks Bernd,
In that case, click the first link in my last post and vote for it. I forgot to add the link in before (sorry) but I have now edited it!
I also think this is a really important feature!
-Jamie
|||Hmm, hope it's OK to play devil's advocate here. How would this work in a data flow merge, conditional split, etc? Seems like a lot of the processing that is needed in ETL and supported in SSIS would confound the meaning of source row number?
|||Hmm, hope it's OK to play devil's advocate here. How would this work in a data flow merge, conditional split, etc? Seems like a lot of the processing that is needed in ETL and supported in SSIS would confound the meaning of source row number?
Ken
Yeah of course it would, absolutely - in which case it has no meaning and you don't use it. Ideally you'd simply have a boolean property on every source adapter saying whether or not to add this "rownumber" column.
If its there and you don't want it - ignore it!
I'd still like to have this feature.
-Jamie
|||I was just wondering if this might be one feature that sounds good on paper but has limited practical value. Since I insist on data models and domains that make use of natural keys (and always design solutions based on this paradigm), I can't see how it would be of much use in my work. If I ever need to find a row, I always can do so by using the key...
|||I was just wondering if this might be one feature that sounds good on paper but has limited practical value. Since I insist on data models and domains that make use of natural keys (and always design solutions based on this paradigm), I can't see how it would be of much use in my work. If I ever need to find a row, I always can do so by using the key...
True enough. Where I think it would be useful would be when importing a file. If a row goes down the error flow it'd be nice to know which row it was.
-Jamie
|||Jamie,
is there any way to get the error details from a source connector when you set it to ignore errors? Otherwise it would be easy to add the row number by a counter (there are some ways to do that, you know)... The only problem are the error lines. SSIS simply changes the value of a non-readable field to NULL. You don't get any error information (at least that's what I see). If there would be any way to find out that a record has an error (and probably what kind of error in which field) then we could just ignore all errors first, add the counter and do some manual error handling later...
Thanks,
|||Jamie,
is there any way to get the error details from a source connector when you set it to ignore errors?
I don't think so Thomas, no. Do these errors trigger the OnError eventhandler? I'm 99% sure they don't but don't have a SSIS instance to hand to check.
-Jamie
How Can I Get Client User Ip Number
Hi,
Use Mssql,
How can I,
Get Client User Ip Number in Mssql
Thanks,I don't know of a direct way to do this is TSQL.
There is a workaround, in your ADO connection string set the workstation id
= 123.123.13.233
Then in TSQL you can retreive this with SELECT HOST_NAME()
HTH. Ryan
"kimsin_sen" <kimsinsen@.hotmail.com> wrote in message
news:%23UYiMISEGHA.1264@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Use Mssql,
> How can I,
> Get Client User Ip Number in Mssql
> Thanks,
>|||I Can Try This Code
In Sql Query Analyzer
SELECT HOST_NAME()
Return Machine Name
No Ip Number
"Ryan" <Ryan_Waight@.nospam.hotmail.com> wrote in message
news:e1oolNSEGHA.2728@.TK2MSFTNGP14.phx.gbl...
I don't know of a direct way to do this is TSQL.
There is a workaround, in your ADO connection string set the workstation id
= 123.123.13.233
Then in TSQL you can retreive this with SELECT HOST_NAME()
HTH. Ryan
"kimsin_sen" <kimsinsen@.hotmail.com> wrote in message
news:%23UYiMISEGHA.1264@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Use Mssql,
> How can I,
> Get Client User Ip Number in Mssql
> Thanks,
>|||Did you try changing the ADO connection string on the client 1st or is this
not an option.?
HTH. Ryan
"kimsin_sen" <kimsinsen@.hotmail.com> wrote in message
news:uDEX5USEGHA.1508@.TK2MSFTNGP15.phx.gbl...
>I Can Try This Code
> In Sql Query Analyzer
> SELECT HOST_NAME()
> Return Machine Name
> No Ip Number
>
> "Ryan" <Ryan_Waight@.nospam.hotmail.com> wrote in message
> news:e1oolNSEGHA.2728@.TK2MSFTNGP14.phx.gbl...
> I don't know of a direct way to do this is TSQL.
> There is a workaround, in your ADO connection string set the workstation
> id
> = 123.123.13.233
> Then in TSQL you can retreive this with SELECT HOST_NAME()
> --
> HTH. Ryan
>
> "kimsin_sen" <kimsinsen@.hotmail.com> wrote in message
> news:%23UYiMISEGHA.1264@.TK2MSFTNGP09.phx.gbl...
>
>
Use Mssql,
How can I,
Get Client User Ip Number in Mssql
Thanks,I don't know of a direct way to do this is TSQL.
There is a workaround, in your ADO connection string set the workstation id
= 123.123.13.233
Then in TSQL you can retreive this with SELECT HOST_NAME()
HTH. Ryan
"kimsin_sen" <kimsinsen@.hotmail.com> wrote in message
news:%23UYiMISEGHA.1264@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Use Mssql,
> How can I,
> Get Client User Ip Number in Mssql
> Thanks,
>|||I Can Try This Code
In Sql Query Analyzer
SELECT HOST_NAME()
Return Machine Name
No Ip Number
"Ryan" <Ryan_Waight@.nospam.hotmail.com> wrote in message
news:e1oolNSEGHA.2728@.TK2MSFTNGP14.phx.gbl...
I don't know of a direct way to do this is TSQL.
There is a workaround, in your ADO connection string set the workstation id
= 123.123.13.233
Then in TSQL you can retreive this with SELECT HOST_NAME()
HTH. Ryan
"kimsin_sen" <kimsinsen@.hotmail.com> wrote in message
news:%23UYiMISEGHA.1264@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Use Mssql,
> How can I,
> Get Client User Ip Number in Mssql
> Thanks,
>|||Did you try changing the ADO connection string on the client 1st or is this
not an option.?
HTH. Ryan
"kimsin_sen" <kimsinsen@.hotmail.com> wrote in message
news:uDEX5USEGHA.1508@.TK2MSFTNGP15.phx.gbl...
>I Can Try This Code
> In Sql Query Analyzer
> SELECT HOST_NAME()
> Return Machine Name
> No Ip Number
>
> "Ryan" <Ryan_Waight@.nospam.hotmail.com> wrote in message
> news:e1oolNSEGHA.2728@.TK2MSFTNGP14.phx.gbl...
> I don't know of a direct way to do this is TSQL.
> There is a workaround, in your ADO connection string set the workstation
> id
> = 123.123.13.233
> Then in TSQL you can retreive this with SELECT HOST_NAME()
> --
> HTH. Ryan
>
> "kimsin_sen" <kimsinsen@.hotmail.com> wrote in message
> news:%23UYiMISEGHA.1264@.TK2MSFTNGP09.phx.gbl...
>
>
How can I get another copy of RS2000? Need help ASAP!
I need another enterprise RS2000 CD. What is the quickest way to get one?
If you have a link or contact number please post. I am located in Atlanta
GA. I can't seem to find it on the website.
thanks in advance
OSOn Nov 26, 10:30 am, OriginalStealth
<OriginalStea...@.discussions.microsoft.com> wrote:
> I need another enterprise RS2000 CD. What is the quickest way to get one?
> If you have a link or contact number please post. I am located in Atlanta
> GA. I can't seem to find it on the website.
> thanks in advance
> OS
This link might be helpful.
http://technet.microsoft.com/en-us/sqlserver/bb331729.aspx
Regards,
Enrique Martinez
Sr. Software Consultant
If you have a link or contact number please post. I am located in Atlanta
GA. I can't seem to find it on the website.
thanks in advance
OSOn Nov 26, 10:30 am, OriginalStealth
<OriginalStea...@.discussions.microsoft.com> wrote:
> I need another enterprise RS2000 CD. What is the quickest way to get one?
> If you have a link or contact number please post. I am located in Atlanta
> GA. I can't seem to find it on the website.
> thanks in advance
> OS
This link might be helpful.
http://technet.microsoft.com/en-us/sqlserver/bb331729.aspx
Regards,
Enrique Martinez
Sr. Software Consultant
How can I get and use an OUTPUT parameter
Here is what I have so far, I can get a number added to the table running my sproc from management studio. But how do I get it out as it is being intserted and then use it in my code?
ALTER PROCEDURE [dbo].[NumberCounter]-- Add the parameters for the stored procedure here@.InsertDatedatetimeASBEGIN-- SET NOCOUNT ON added to prevent extra result sets from-- interfering with SELECT statements.SET NOCOUNT ON;-- Insert statements for procedure hereINSERT INTO tblNumberCounter (InsertDate)Values (@.InsertDate);SelectIDENT_CURRENT('tblNumberCounter')ENDPublic Sub SubmitAdd_Click(ByVal SenderAs System.Object, ByVal EAs System.EventArgs) Dim ConAs SqlConnection Dim StrInsertAs String Dim cmdInsertAs SqlCommand Dim myNewReceiptNumberAs Integer Dim ConnectStrAs String = _ ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString'Add row to receipt table, then get it for ReceiptNumberText field. cmdInsert = New SqlCommand cmdInsert.CommandText = "NumberCounter" cmdInsert.CommandType = CommandType.StoredProcedure cmdInsert.Connection = New SqlConnection(ConnectStr) cmdInsert.Parameters.AddWithValue("@.InsertDate", System.DateTime.Today.ToShortDateString()) Try Con.Open() myNewReceiptNumber = cmdInsert.ExecuteScalar() 'Response.Write(myNewReceiptNumber) Catch objExceptionAs SqlException Dim objErrorAs SqlErrorFor Each objErrorIn objException.Errors Response.Write(objError.Message)Next Finally Con.Close()End TryEnd SubTry this...
ALTER PROCEDURE [dbo].[NumberCounter]
-- Add the parameters for the stored procedure here
@.InsertDatedatetime,
@.InsertedInteger int OUTPUT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
INSERT INTO tblNumberCounter (InsertDate)Values (@.InsertDate);Select @.InsertedInteger =IDENT_CURRENT('tblNumberCounter')
END
Public Sub SubmitAdd_Click(ByVal SenderAs System.Object, ByVal EAs System.EventArgs)
Dim ConAs SqlConnection
Dim StrInsertAs String
Dim cmdInsertAs SqlCommand
Dim myNewReceiptNumberAs Integer
Dim ConnectStrAs String = _
ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString
'Add row to receipt table, then get it for ReceiptNumberText field.
cmdInsert = New SqlCommand
cmdInsert.CommandText = "NumberCounter"
cmdInsert.CommandType = CommandType.StoredProcedure
cmdInsert.Connection = New SqlConnection(ConnectStr)
cmdInsert.Parameters.AddWithValue("@.InsertDate", System.DateTime.Today.ToShortDateString())
'Create a new parameter with direction set to output, and add it to the parameters collection
dim InsertedInteger as new SqlParameter("@.InsertedInteger", SqlDbType.Int)
InsertedInteger.Direction = ParameterDirection.Output
cmdInsert.Parameters.Add(InsertedInteger)
Try
Con.Open()
myNewReceiptNumber = cmdInsert.ExecuteScalar()
'Response.Write(myNewReceiptNumber)
Catch objExceptionAs SqlException
Dim objErrorAs SqlError
For Each objErrorIn objException.Errors
Response.Write(objError.Message)
Next
Finally
Con.Close()
End Try
MessageBox.Show(InsertedInteger.Value)
End Sub
Well, I could use a little more help with this:
I need to use the ID that is output in a query string, however, when I try to use it I get an error 'Object reference not set to an instance of an object'
Anyone see why this might happen??
Try cmdInsert.Connection.Open() InsertedInteger = cmdInsert.ExecuteScalar()Catch objExceptionAs SqlExceptionDim objErrorAs SqlErrorFor Each objErrorIn objException.Errors Response.Write(objError.Message)Next Finally cmdInsert.Connection.Close() Response.Redirect("../editnew.aspx?ID=" & InsertedInteger.Value)End Try|||You would use ExecuteScalar to capture the output from your SELECT statement and it only captures one value. If you are using output parameters you would use ExecuteNonQuery and create parameters of type OUTPUT and use them to retrieve the values. You can use the sample code below and modify it accordingly:
Dim myCommandAs SqlCommandDim myParamAs SqlParametermyCommand =New SqlCommand()myCommand.Connection = objconmyCommand.CommandText ="usp_testproc"myCommand.CommandType = CommandType.StoredProceduremyCommand.Parameters.Add(New SqlParameter("@.userid",SqlDbType.int))myCommand.Parameters("@.userid").Value = Trim(userid)myCommand.Parameters.Add(New SqlParameter("@.ttype",SqlDbType.VarChar,25))myCommand.Parameters("@.ttype").Value ="charge"'output parametermyParam = mycommand.CreateParameter()myParam.ParameterName ="@.result"myParam.Direction = ParameterDirection.OutputmyParam.SqlDbType = SqlDbType.bigintmycommand.Parameters.Add(myParam)TryIf objCon.State = 0Then objCon.Open()mycommand.ExecuteNonQuery()Response.Write(convert.todouble(mycommand.Parameters("@.result").Value))Catch excAs ExceptionResponse.Write(exc)FinallyIf objCon.State = ConnectionState.OpenThen objCon.Close()End IfEnd Try
Sunday, February 19, 2012
How can I get a count of all records in each table in my DB?
Hoping you can help me here... I have a situation where I need to provide
a
script that can be run through query analyzer to return the number of record
s
in each table in my DB.
I would like it to be dynamic, as the number of tables in the DB changes.
Pretty simple query (Select count(*) from tblXXX), but I can't seem to find
where the actual table names are stored within the system tables so that I
can run through all of them.
I know that I could use Enterprise Manager and view the DB as a TaskPad to
see the number of records in each table, but the person who will be doing
this only has permissions to view the DB through QA.
Any thoughts on a script that will do this?
Thanks in advance...
gTry,
use northwind
go
-- undocumented sp
exec sp_msforeachtable 'select ''?'', count(*) from ?'
go
-- using sysindexes
dbcc updateusage (0)
select object_name([id]), rowcnt
from sysindexes
where indid in (0, 1) and objectproperty([id], 'IsUserTable') = 1
order by 1
go
-- using a cursor and dyanmic sql
create table #t (
table_name sysname,
rowcnt int,
)
declare @.sql nvarchar(4000)
declare @.ts sysname
declare @.tn sysname
declare my_cursor cursor
local
fast_forward
for
select
table_schema,
table_name
from
information_schema.tables
where
table_type = 'BASE TABLE'
open my_cursor
while 1 = 1
begin
fetch next from my_cursor into @.ts, @.tn
if @.@.error != 0 or @.@.fetch_status != 0 break
set @.sql = N'select ''' + @.tn + ''' as table_name, count(*) as rowcnt from
[' + @.ts + '].[' + @.tn + ']'
insert into #t
exec sp_executesql @.sql
end
close my_cursor
deallocate my_cursor
select * from #t order by table_name
drop table #t
go
AMB
"Greg Toronto" wrote:
> Hoping you can help me here... I have a situation where I need to provid
e a
> script that can be run through query analyzer to return the number of reco
rds
> in each table in my DB.
> I would like it to be dynamic, as the number of tables in the DB changes.
> Pretty simple query (Select count(*) from tblXXX), but I can't seem to fin
d
> where the actual table names are stored within the system tables so that I
> can run through all of them.
> I know that I could use Enterprise Manager and view the DB as a TaskPad to
> see the number of records in each table, but the person who will be doing
> this only has permissions to view the DB through QA.
> Any thoughts on a script that will do this?
> Thanks in advance...
> g|||In SQL Server 2000:
DBCC UPDATEUSAGE(0);
select o.name, i.rowcnt
from sysobjects o
inner join sysindexes i
on o.id = i.id
WHERE i.indid IN (0,1)
AND OBJECTPROPERTY(o.id, 'IsMsShipped') = 0
ORDER BY o.name
In SQL Server 2005, it's a little more complex, accounting for table
partitions:
SELECT
t.name,
[RowCount] = SUM
(
CASE
WHEN (p.index_id < 2) AND (a.type = 1) THEN p.rows
ELSE 0
END
)
FROM
sys.tables t
INNER JOIN sys.partitions p
ON t.object_id = p.object_id
INNER JOIN sys.allocation_units a
ON p.partition_id = a.container_id
GROUP BY
t.name;
"Greg Toronto" <GregToronto@.discussions.microsoft.com> wrote in message
news:1AEB569D-DA79-4B7B-9892-932B37D4823E@.microsoft.com...
> Hoping you can help me here... I have a situation where I need to
> provide a
> script that can be run through query analyzer to return the number of
> records
> in each table in my DB.
> I would like it to be dynamic, as the number of tables in the DB changes.
> Pretty simple query (Select count(*) from tblXXX), but I can't seem to
> find
> where the actual table names are stored within the system tables so that I
> can run through all of them.
> I know that I could use Enterprise Manager and view the DB as a TaskPad to
> see the number of records in each table, but the person who will be doing
> this only has permissions to view the DB through QA.
> Any thoughts on a script that will do this?
> Thanks in advance...
> g
a
script that can be run through query analyzer to return the number of record
s
in each table in my DB.
I would like it to be dynamic, as the number of tables in the DB changes.
Pretty simple query (Select count(*) from tblXXX), but I can't seem to find
where the actual table names are stored within the system tables so that I
can run through all of them.
I know that I could use Enterprise Manager and view the DB as a TaskPad to
see the number of records in each table, but the person who will be doing
this only has permissions to view the DB through QA.
Any thoughts on a script that will do this?
Thanks in advance...
gTry,
use northwind
go
-- undocumented sp
exec sp_msforeachtable 'select ''?'', count(*) from ?'
go
-- using sysindexes
dbcc updateusage (0)
select object_name([id]), rowcnt
from sysindexes
where indid in (0, 1) and objectproperty([id], 'IsUserTable') = 1
order by 1
go
-- using a cursor and dyanmic sql
create table #t (
table_name sysname,
rowcnt int,
)
declare @.sql nvarchar(4000)
declare @.ts sysname
declare @.tn sysname
declare my_cursor cursor
local
fast_forward
for
select
table_schema,
table_name
from
information_schema.tables
where
table_type = 'BASE TABLE'
open my_cursor
while 1 = 1
begin
fetch next from my_cursor into @.ts, @.tn
if @.@.error != 0 or @.@.fetch_status != 0 break
set @.sql = N'select ''' + @.tn + ''' as table_name, count(*) as rowcnt from
[' + @.ts + '].[' + @.tn + ']'
insert into #t
exec sp_executesql @.sql
end
close my_cursor
deallocate my_cursor
select * from #t order by table_name
drop table #t
go
AMB
"Greg Toronto" wrote:
> Hoping you can help me here... I have a situation where I need to provid
e a
> script that can be run through query analyzer to return the number of reco
rds
> in each table in my DB.
> I would like it to be dynamic, as the number of tables in the DB changes.
> Pretty simple query (Select count(*) from tblXXX), but I can't seem to fin
d
> where the actual table names are stored within the system tables so that I
> can run through all of them.
> I know that I could use Enterprise Manager and view the DB as a TaskPad to
> see the number of records in each table, but the person who will be doing
> this only has permissions to view the DB through QA.
> Any thoughts on a script that will do this?
> Thanks in advance...
> g|||In SQL Server 2000:
DBCC UPDATEUSAGE(0);
select o.name, i.rowcnt
from sysobjects o
inner join sysindexes i
on o.id = i.id
WHERE i.indid IN (0,1)
AND OBJECTPROPERTY(o.id, 'IsMsShipped') = 0
ORDER BY o.name
In SQL Server 2005, it's a little more complex, accounting for table
partitions:
SELECT
t.name,
[RowCount] = SUM
(
CASE
WHEN (p.index_id < 2) AND (a.type = 1) THEN p.rows
ELSE 0
END
)
FROM
sys.tables t
INNER JOIN sys.partitions p
ON t.object_id = p.object_id
INNER JOIN sys.allocation_units a
ON p.partition_id = a.container_id
GROUP BY
t.name;
"Greg Toronto" <GregToronto@.discussions.microsoft.com> wrote in message
news:1AEB569D-DA79-4B7B-9892-932B37D4823E@.microsoft.com...
> Hoping you can help me here... I have a situation where I need to
> provide a
> script that can be run through query analyzer to return the number of
> records
> in each table in my DB.
> I would like it to be dynamic, as the number of tables in the DB changes.
> Pretty simple query (Select count(*) from tblXXX), but I can't seem to
> find
> where the actual table names are stored within the system tables so that I
> can run through all of them.
> I know that I could use Enterprise Manager and view the DB as a TaskPad to
> see the number of records in each table, but the person who will be doing
> this only has permissions to view the DB through QA.
> Any thoughts on a script that will do this?
> Thanks in advance...
> g
Subscribe to:
Posts (Atom)