Drop the end of an ip address

does anyone know how to drop the last part of an ip address? I've been searching various sites for string functions and havn't come up with anything that works. What I want is something that will "find the third occurance of a "." and then return everything to the left of it"

I've tried various types of InStr and even found a SUBSTRING_INDEX function, but apparently it's only for mysql.

i'm using sql server and linking tables thru access

fiskyou can do reverse and then a right with a nested charindex. with another reverse at the end.

or you can stuff with a nested charindex.|||i'm getting "undefined function" error.

SELECT dbo_AeXInv_AeX_AC_TCPIP.WrkstaId, REVERSE(dbo_AeXInv_AeX_AC_TCPIP.[IP Address]), dbo_AeXInv_AeX_AC_TCPIP.[Host Name]
FROM dbo_AeXInv_AeX_AC_TCPIP;

the reverse string function seems pretty simple, but I must be missing something.

fisk|||Same error message for charindex

SELECT dbo_AeXInv_AeX_AC_TCPIP.WrkstaId, charindex('.', dbo_AeXInv_AeX_AC_TCPIP.[IP Address]), dbo_AeXInv_AeX_AC_TCPIP.[Host Name]
FROM dbo_AeXInv_AeX_AC_TCPIP;

I just ran this in sql analyzer and it worked fine. Any idea why i'm getting this error in access?

fisk|||microsoft access does not use the same functions as microsoft sql server

i mean, you would think it would, what with sql standards and all, but you gots to remember, microsoft bought access from a different company than it bought sql server from

:)

as long as we're playing loosey-goosey with database platforms, why don't you run it on mysql? it has a wonderful function called SUBSTRING_INDEX

select substring_index(`IP Address`, '.', 3) as stuff_before_the_third_period
from dbo_AeXInv_AeX_AC_TCPIP|||I would have thought that access would just pass the sql statement to sql server and let it figure it out. I guess not.|||it will only do that if you have declared it as a pass-through query|||Not quiet. For linked tables, Access will attempt to format the statement as a pass-through query on its own, but if it cannot (and it cannot handle anything more than moderately complex) then it will execute it locally (and ineffeciently).|||Yep, that's pretty much what's happening.

Oh well, thanks anyway guys.

fisk|||For linked tables, Access will attempt to format the statement as a pass-through query on its ownholey moley

i've been using access with linked tables for about 10 years, and i didn't know that

so, what does it do when the sql contains an access function like InStr()?

it barfs, right?

so when you say "moderately complex" you really mean "anything even slightly more complex than SELECT foo FROM bar WHERE qux=937", eh?|||It's somewhere in the documentation. I don't know how complex it can get before it has to run it locally. It may depend upon whether it has embedded functions, or it may depend upon whether it is able to succesfully translate MS Access SQL to TSQL.|||You should try some simple queries and then monitor server activity to see what gets sent through.

Drive Space Problem

Hi,
After searching this forum I haven't found a specific answer for my problem.
We have a 2003 server used for sql databases. We are running version 8 w/
service pack 3. What is happening is we are losing on average of 10GB of disk
space a day. If I reboot the server, the space comes back. I have tried some
of things listed in this forum to no avail, i.e. setting the databases
recovery to "simple" instead of "full". The actual .ldf and .mdf files
combined are only around 200 megs and the Log directory has less than 1MB in
it. Is there anything else I can check on the SQL side to eliminate it as
culprit?
Thanks for any help,
Joe Garcia
Perhaps ODBC tracing is turned on? I believe the file is named SQL.LOG.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Joe G" <Joe G@.discussions.microsoft.com> wrote in message
news:064FEED9-C3F9-48B2-A5D1-79926005D8E8@.microsoft.com...
> Hi,
> After searching this forum I haven't found a specific answer for my problem.
> We have a 2003 server used for sql databases. We are running version 8 w/
> service pack 3. What is happening is we are losing on average of 10GB of disk
> space a day. If I reboot the server, the space comes back. I have tried some
> of things listed in this forum to no avail, i.e. setting the databases
> recovery to "simple" instead of "full". The actual .ldf and .mdf files
> combined are only around 200 megs and the Log directory has less than 1MB in
> it. Is there anything else I can check on the SQL side to eliminate it as
> culprit?
> Thanks for any help,
> Joe Garcia
|||Maybe it is just the tempdb database (it gets cleaned during every restart
of sql server)
Marc
"Joe G" <Joe G@.discussions.microsoft.com> wrote in message
news:064FEED9-C3F9-48B2-A5D1-79926005D8E8@.microsoft.com...
> Hi,
> After searching this forum I haven't found a specific answer for my
problem.
> We have a 2003 server used for sql databases. We are running version 8 w/
> service pack 3. What is happening is we are losing on average of 10GB of
disk
> space a day. If I reboot the server, the space comes back. I have tried
some
> of things listed in this forum to no avail, i.e. setting the databases
> recovery to "simple" instead of "full". The actual .ldf and .mdf files
> combined are only around 200 megs and the Log directory has less than 1MB
in
> it. Is there anything else I can check on the SQL side to eliminate it as
> culprit?
> Thanks for any help,
> Joe Garcia
|||"Joe G" <Joe G@.discussions.microsoft.com> schrieb im Newsbeitrag
news:064FEED9-C3F9-48B2-A5D1-79926005D8E8@.microsoft.com...
> Hi,
> After searching this forum I haven't found a specific answer for my
problem.
> We have a 2003 server used for sql databases. We are running version 8
w/
> service pack 3. What is happening is we are losing on average of 10GB of
disk
> space a day. If I reboot the server, the space comes back. I have tried
some
> of things listed in this forum to no avail, i.e. setting the databases
> recovery to "simple" instead of "full". The actual .ldf and .mdf files
> combined are only around 200 megs and the Log directory has less than
1MB in
> it. Is there anything else I can check on the SQL side to eliminate it
as
> culprit?
> Thanks for any help,
> Joe Garcia
You could set your databases to autoshrink.
robert
|||"Robert Klemme" <bob.news@.gmx.net> wrote in message
news:35pb3jF4obuq9U1@.individual.net...
> "Joe G" <Joe G@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:064FEED9-C3F9-48B2-A5D1-79926005D8E8@.microsoft.com...
> problem.
> w/
> disk
> some
> 1MB in
> as
> You could set your databases to autoshrink.
>
I would recommend against this though.
This can cause disk level file fragmentation. And it would only mask the
real problem.
I'd go for either tempdb or ODBC logging.

> robert
>

Drive Space Problem

Hi,
After searching this forum I haven't found a specific answer for my problem.
We have a 2003 server used for sql databases. We are running version 8 w/
service pack 3. What is happening is we are losing on average of 10GB of disk
space a day. If I reboot the server, the space comes back. I have tried some
of things listed in this forum to no avail, i.e. setting the databases
recovery to "simple" instead of "full". The actual .ldf and .mdf files
combined are only around 200 megs and the Log directory has less than 1MB in
it. Is there anything else I can check on the SQL side to eliminate it as
culprit?
Thanks for any help,
Joe GarciaPerhaps ODBC tracing is turned on? I believe the file is named SQL.LOG.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Joe G" <Joe G@.discussions.microsoft.com> wrote in message
news:064FEED9-C3F9-48B2-A5D1-79926005D8E8@.microsoft.com...
> Hi,
> After searching this forum I haven't found a specific answer for my problem.
> We have a 2003 server used for sql databases. We are running version 8 w/
> service pack 3. What is happening is we are losing on average of 10GB of disk
> space a day. If I reboot the server, the space comes back. I have tried some
> of things listed in this forum to no avail, i.e. setting the databases
> recovery to "simple" instead of "full". The actual .ldf and .mdf files
> combined are only around 200 megs and the Log directory has less than 1MB in
> it. Is there anything else I can check on the SQL side to eliminate it as
> culprit?
> Thanks for any help,
> Joe Garcia|||Maybe it is just the tempdb database (it gets cleaned during every restart
of sql server)
Marc
"Joe G" <Joe G@.discussions.microsoft.com> wrote in message
news:064FEED9-C3F9-48B2-A5D1-79926005D8E8@.microsoft.com...
> Hi,
> After searching this forum I haven't found a specific answer for my
problem.
> We have a 2003 server used for sql databases. We are running version 8 w/
> service pack 3. What is happening is we are losing on average of 10GB of
disk
> space a day. If I reboot the server, the space comes back. I have tried
some
> of things listed in this forum to no avail, i.e. setting the databases
> recovery to "simple" instead of "full". The actual .ldf and .mdf files
> combined are only around 200 megs and the Log directory has less than 1MB
in
> it. Is there anything else I can check on the SQL side to eliminate it as
> culprit?
> Thanks for any help,
> Joe Garcia|||"Joe G" <Joe G@.discussions.microsoft.com> schrieb im Newsbeitrag
news:064FEED9-C3F9-48B2-A5D1-79926005D8E8@.microsoft.com...
> Hi,
> After searching this forum I haven't found a specific answer for my
problem.
> We have a 2003 server used for sql databases. We are running version 8
w/
> service pack 3. What is happening is we are losing on average of 10GB of
disk
> space a day. If I reboot the server, the space comes back. I have tried
some
> of things listed in this forum to no avail, i.e. setting the databases
> recovery to "simple" instead of "full". The actual .ldf and .mdf files
> combined are only around 200 megs and the Log directory has less than
1MB in
> it. Is there anything else I can check on the SQL side to eliminate it
as
> culprit?
> Thanks for any help,
> Joe Garcia
You could set your databases to autoshrink.
robert|||"Robert Klemme" <bob.news@.gmx.net> wrote in message
news:35pb3jF4obuq9U1@.individual.net...
> "Joe G" <Joe G@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:064FEED9-C3F9-48B2-A5D1-79926005D8E8@.microsoft.com...
> > Hi,
> > After searching this forum I haven't found a specific answer for my
> problem.
> > We have a 2003 server used for sql databases. We are running version 8
> w/
> > service pack 3. What is happening is we are losing on average of 10GB of
> disk
> > space a day. If I reboot the server, the space comes back. I have tried
> some
> > of things listed in this forum to no avail, i.e. setting the databases
> > recovery to "simple" instead of "full". The actual .ldf and .mdf files
> > combined are only around 200 megs and the Log directory has less than
> 1MB in
> > it. Is there anything else I can check on the SQL side to eliminate it
> as
> > culprit?
> >
> > Thanks for any help,
> > Joe Garcia
> You could set your databases to autoshrink.
>
I would recommend against this though.
This can cause disk level file fragmentation. And it would only mask the
real problem.
I'd go for either tempdb or ODBC logging.
> robert
>

Drive Space Problem

Hi,
After searching this forum I haven't found a specific answer for my problem.
We have a 2003 server used for sql databases. We are running version 8 w/
service pack 3. What is happening is we are losing on average of 10GB of dis
k
space a day. If I reboot the server, the space comes back. I have tried some
of things listed in this forum to no avail, i.e. setting the databases
recovery to "simple" instead of "full". The actual .ldf and .mdf files
combined are only around 200 megs and the Log directory has less than 1MB in
it. Is there anything else I can check on the SQL side to eliminate it as
culprit?
Thanks for any help,
Joe GarciaPerhaps ODBC tracing is turned on? I believe the file is named SQL.LOG.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Joe G" <Joe G@.discussions.microsoft.com> wrote in message
news:064FEED9-C3F9-48B2-A5D1-79926005D8E8@.microsoft.com...
> Hi,
> After searching this forum I haven't found a specific answer for my proble
m.
> We have a 2003 server used for sql databases. We are running version 8 w/
> service pack 3. What is happening is we are losing on average of 10GB of d
isk
> space a day. If I reboot the server, the space comes back. I have tried so
me
> of things listed in this forum to no avail, i.e. setting the databases
> recovery to "simple" instead of "full". The actual .ldf and .mdf files
> combined are only around 200 megs and the Log directory has less than 1MB
in
> it. Is there anything else I can check on the SQL side to eliminate it as
> culprit?
> Thanks for any help,
> Joe Garcia|||Maybe it is just the tempdb database (it gets cleaned during every restart
of sql server)
Marc
"Joe G" <Joe G@.discussions.microsoft.com> wrote in message
news:064FEED9-C3F9-48B2-A5D1-79926005D8E8@.microsoft.com...
> Hi,
> After searching this forum I haven't found a specific answer for my
problem.
> We have a 2003 server used for sql databases. We are running version 8 w/
> service pack 3. What is happening is we are losing on average of 10GB of
disk
> space a day. If I reboot the server, the space comes back. I have tried
some
> of things listed in this forum to no avail, i.e. setting the databases
> recovery to "simple" instead of "full". The actual .ldf and .mdf files
> combined are only around 200 megs and the Log directory has less than 1MB
in
> it. Is there anything else I can check on the SQL side to eliminate it as
> culprit?
> Thanks for any help,
> Joe Garcia|||"Joe G" <Joe G@.discussions.microsoft.com> schrieb im Newsbeitrag
news:064FEED9-C3F9-48B2-A5D1-79926005D8E8@.microsoft.com...
> Hi,
> After searching this forum I haven't found a specific answer for my
problem.
> We have a 2003 server used for sql databases. We are running version 8
w/
> service pack 3. What is happening is we are losing on average of 10GB of
disk
> space a day. If I reboot the server, the space comes back. I have tried
some
> of things listed in this forum to no avail, i.e. setting the databases
> recovery to "simple" instead of "full". The actual .ldf and .mdf files
> combined are only around 200 megs and the Log directory has less than
1MB in
> it. Is there anything else I can check on the SQL side to eliminate it
as
> culprit?
> Thanks for any help,
> Joe Garcia
You could set your databases to autoshrink.
robert|||"Robert Klemme" <bob.news@.gmx.net> wrote in message
news:35pb3jF4obuq9U1@.individual.net...
> "Joe G" <Joe G@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:064FEED9-C3F9-48B2-A5D1-79926005D8E8@.microsoft.com...
> problem.
> w/
> disk
> some
> 1MB in
> as
> You could set your databases to autoshrink.
>
I would recommend against this though.
This can cause disk level file fragmentation. And it would only mask the
real problem.
I'd go for either tempdb or ODBC logging.

> robert
>

drillthrough problems with remote reports

After spending some time searching the forums for other people with these same issues, I've come up empty handed and decided it was time to make a post. I'm using the VS2005 WebForms ReportViewer object to embed remote (i.e. not using rdlc) reports on an asp.net page. When the page is first rendered, I have code to set the ServerReport.ReportPath property to the location of a parent-level report on my report server. Each record in this report has a drillthrough link to another server report, and each record in this detail-level report has ANOTHER drillthrough link to a third report that is an even more detailed view of the data. So in short, I have a set of drillthrough reports that can go three levels deep and the top-level report is rendered by default when the page is first viewed.

The behavior that I would expect is that when I click on the drillthrough links in the first or second report, the ReportViewer would incur a postback and automatically do the plumbing to render the appropriate drillthrough report with the proper parameters passed in by the parent report. I would also expect the "back" button in the ReportViewer toolbar to correctly navigate back up the tree to the top-level parent report without any additional programming.

The behavior that I'm ACTUALLY seeing is that the drillthrough works fine from TopLevelReport to MidLevelReport works fine, but when I click to drillthrough from MidLevelReport to BottomLevelReport, the ReportViewer re-renders MidLevelReport with different parameters, as if I had gone back to TopLevelReport and clicked on a different record. However, if at this point I click on a drillthrough link in the new rendering of MidLevelReport, it correctly navigates to BottomLevelReport.

Also, the back button functionality is unpredictable; sometimes I receive this exception when clicking the back button from MidLevelReport:

System.InvalidOperationException: Back call without drillthrough report at Microsoft.Reporting.WebForms.ReportViewer.Toolbar_Back(Object sender, EventArgs e) at Microsoft.Reporting.WebForms.ToolbarControl.OnBack(Object sender, EventArgs e) at Microsoft.Reporting.WebForms.BackGroup.BackButton_Click(Object sender, EventArgs e) at Microsoft.Reporting.WebForms.ScriptSwitchImage.EnabledImage_Click(Object sender, EventArgs e) at Microsoft.Reporting.WebForms.HoverImage.RaisePostBackEvent(String eventArgument) at System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) at System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)

When clicking the back button on BottomLevelReport, instead of throwing the above exception it correctly navigates back to MidLevelReport, but then the back button is disabled and I can't go back up to TopLevelReport.

I've experimented with hooking into the ReportViewer.DrillThrough and ReportViewer.Back events to see if maybe I'm just expecting the ReportViewer to handle some under-the-hood plumbing that I'm supposed to code myself, such as updating the value of ServerReport.ReportPath or calling ServerReport.Refresh. I won't go into lengthy details of all the combinations of coding I've tried, but all of them introduced some sort of incorrect navigation behavior or outright exceptions. What I really need is for somebody experienced with using ReportViewer to display remote drillthrough reports more than two levels deep to tell me exactly what I need to do in my DrillThrough and/or Back event handlers in order to make the ReportViewer behave in the expected way.

Thanks in advance to anybody who replies; I've been stuck on this problem for a couple of days now and I'm starting to consider abandoning the idea of using ReportViewer for this project.

vigg:

After spending some time searching the forums for other people with these same issues, I've come up empty handed and decided it was time to make a post. I'm using the VS2005 WebForms ReportViewer object to embed remote (i.e. not using rdlc) reports on an asp.net page. When the page is first rendered, I have code to set the ServerReport.ReportPath property to the location of a parent-level report on my report server. Each record in this report has a drillthrough link to another server report, and each record in this detail-level report has ANOTHER drillthrough link to a third report that is an even more detailed view of the data. So in short, I have a set of drillthrough reports that can go three levels deep and the top-level report is rendered by default when the page is first viewed.

The behavior that I would expect is that when I click on the drillthrough links in the first or second report, the ReportViewer would incur a postback and automatically do the plumbing to render the appropriate drillthrough report with the proper parameters passed in by the parent report. I would also expect the "back" button in the ReportViewer toolbar to correctly navigate back up the tree to the top-level parent report without any additional programming.

The behavior that I'm ACTUALLY seeing is that the drillthrough works fine from TopLevelReport to MidLevelReport works fine, but when I click to drillthrough from MidLevelReport to BottomLevelReport, the ReportViewer re-renders MidLevelReport with different parameters, as if I had gone back to TopLevelReport and clicked on a different record. However, if at this point I click on a drillthrough link in the new rendering of MidLevelReport, it correctly navigates to BottomLevelReport.

Also, the back button functionality is unpredictable; sometimes I receive this exception when clicking the back button from MidLevelReport:

System.InvalidOperationException: Back call without drillthrough report at Microsoft.Reporting.WebForms.ReportViewer.Toolbar_Back(Object sender, EventArgs e) at Microsoft.Reporting.WebForms.ToolbarControl.OnBack(Object sender, EventArgs e) at Microsoft.Reporting.WebForms.BackGroup.BackButton_Click(Object sender, EventArgs e) at Microsoft.Reporting.WebForms.ScriptSwitchImage.EnabledImage_Click(Object sender, EventArgs e) at Microsoft.Reporting.WebForms.HoverImage.RaisePostBackEvent(String eventArgument) at System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) at System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)

When clicking the back button on BottomLevelReport, instead of throwing the above exception it correctly navigates back to MidLevelReport, but then the back button is disabled and I can't go back up to TopLevelReport.

I've experimented with hooking into the ReportViewer.DrillThrough and ReportViewer.Back events to see if maybe I'm just expecting the ReportViewer to handle some under-the-hood plumbing that I'm supposed to code myself, such as updating the value of ServerReport.ReportPath or calling ServerReport.Refresh. I won't go into lengthy details of all the combinations of coding I've tried, but all of them introduced some sort of incorrect navigation behavior or outright exceptions. What I really need is for somebody experienced with using ReportViewer to display remote drillthrough reports more than two levels deep to tell me exactly what I need to do in my DrillThrough and/or Back event handlers in order to make the ReportViewer behave in the expected way.

Thanks in advance to anybody who replies; I've been stuck on this problem for a couple of days now and I'm starting to consider abandoning the idea of using ReportViewer for this project.

I am facing the exact same issues and am in the same boat. Depending on whether these multilevel drillthrough server reports work properly, I might abandon or use the Reportviewer for this project. its very crucial.

I am also having another issue. In my "mid level drill through report", the Dundas chart that i am using isnt showing either. It shows when its not a drillthrough but the top level report. But not in the drillthrough mode. HELP PLEASE !!

|||

I try this before and it works perfectly but i don't get your problem exactly so i advice you to check the examples in this link

http://www.gotreportviewer.com/

|||

Same issue here, with a variation...

I've got a report that takes a parameter, and another similar report without a parameter. Both have drillthroughs to detail reports. On the report without the parameter, the drillthrough works just fine. On the report *with* the parameter, clicking on the drillthrough link simply reloads the page. It does a postback, the event targets appear to be set, but the drillthrough event does not get fired. I discovered that the very fact that I am setting the parameters (eg, ReportViewer1.ServerReport.SetParameters(_params)) is causing the drillthrough event to not fire--I set the parameters on one of the non-parameterized reports to an empty list of parameters, and behold, the drillthrough event is no longer fired.

Is this a bug with the report viewer, or is there another way to set parameters that doesn't break the drillthrough event? I've tried setting the callback both before and after the parameters are set, so I'm really out of ideas.