Getting the host_name value used in dynamic filter when merge succ

I'm trying to find the value that was used when a merge replication suceeded
for an anonymous pull subscribtion. I'm using dynamic filtering with a
modified HOST_NAME value equal to the CustomerID.
I have a stored procedure called by a Replication alert when a merge succeed
on this procedure I need to know what is the HOST_NAME (=CustmoredId) modifed
value that was used. Is this value in one of the MSmerge_ tables or can I
pass it to the stored procedure somehow. Thanks in advance.
You would have to use triggers on some of the tables being replicated to
figure this out. Basically it resolves to the following
1) the host you connect to EM with and ran the merge job. So if your desktop
is called A and you connect to your publisher call B, and started your push
merge agent to subscriber C, the hostname would resolve to A, unless you
over rode the host name using the hostname parameter in your merge agent.
2) If you are using a push subscription and SQL Server agent kicks it off,
the hostname will be your publisher, unless you over rode the host name
using the hostname parameter in your merge agent..
3) If you are using a pull subscription and SQL Server agent kicks it off,
the hostname will be your subscriber, unless you over rode the host name
using the hostname parameter in your merge agent..
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Humberto" <Humberto@.discussions.microsoft.com> wrote in message
news:3D3B37BD-4197-46BC-BFE4-09DAE445E2D7@.microsoft.com...
> I'm trying to find the value that was used when a merge replication
suceeded
> for an anonymous pull subscribtion. I'm using dynamic filtering with a
> modified HOST_NAME value equal to the CustomerID.
> I have a stored procedure called by a Replication alert when a merge
succeed
> on this procedure I need to know what is the HOST_NAME (=CustmoredId)
modifed
> value that was used. Is this value in one of the MSmerge_ tables or can I
> pass it to the stored procedure somehow. Thanks in advance.

Getting report to create new page for each record retrieved from

I have a stored procedure to pull out the data of all the employees at
a company, and if I use a table, it lists the all the profiles.
However, the table is too big to view on one page and print, so I
created a report with textboxes and fields that fits onto one page
instead.
Using the same stored procedure, all I could see is the profile of the
first employee returned by the stored procedure.
Is there a way to get the report to create a new page for each person
returned by the stored procedure so that the final report would
consists of all the profiles?
Not sure if I should mention this, but I'm working with a web form on
VS2005.
Thanks.You put the text boxes right on the drawing surface. You need to put them on
a list control.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"k-chan" <kitty.sham@.gmail.com> wrote in message
news:c83e7712-1c9e-49fc-b551-4399c96e6936@.e4g2000hsg.googlegroups.com...
> I have a stored procedure to pull out the data of all the employees at
> a company, and if I use a table, it lists the all the profiles.
> However, the table is too big to view on one page and print, so I
> created a report with textboxes and fields that fits onto one page
> instead.
> Using the same stored procedure, all I could see is the profile of the
> first employee returned by the stored procedure.
> Is there a way to get the report to create a new page for each person
> returned by the stored procedure so that the final report would
> consists of all the profiles?
> Not sure if I should mention this, but I'm working with a web form on
> VS2005.
> Thanks.|||Oh! That worked!
Thank you!
On Jan 14, 3:23 pm, "Bruce L-C [MVP]" <bruce_lcNOS...@.hotmail.com>
wrote:
> You put the text boxes right on the drawing surface. You need to put them on
> a list control.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "k-chan" <kitty.s...@.gmail.com> wrote in message
> news:c83e7712-1c9e-49fc-b551-4399c96e6936@.e4g2000hsg.googlegroups.com...
>
> > I have a stored procedure to pull out the data of all the employees at
> > a company, and if I use a table, it lists the all the profiles.
> > However, the table is too big to view on one page and print, so I
> > created a report with textboxes and fields that fits onto one page
> > instead.
> > Using the same stored procedure, all I could see is the profile of the
> > first employee returned by the stored procedure.
> > Is there a way to get the report to create a new page for each person
> > returned by the stored procedure so that the final report would
> > consists of all the profiles?
> > Not sure if I should mention this, but I'm working with a web form on
> > VS2005.
> > Thanks.

Getting one row from a clustered date table

Is it possible to efficiently pull one row from a table that is clustered by the date. What i want to do is pull one row wheather it be the first row the 1028th row or row 3000. Is this possible to do?

What I have is:
SELECT * FROM CUST WHERE '?' = 3000

My cust column is already in order because it is clustered.that query will be efficient only if the '?' column has an index

(that's not the real name of the column, is it?)

also, first, you say clustered by date, and then you say clustered by cust -- that's not possible, because a table can have only one clustered index|||OH sorry CUST is the table and i have a date column that clustered. I do have an index "CUST_ID" but this column is not ordered like the date column. When I view the table the CUST_ID values are scattered i am not sure why.

Also, one more question is there any kind of limit to how many tables i can have in one database?|||of course the CUST_ID values are scattered -- the rows are in sequence by date

Getting Just The Date

This sounds stupid, but I cannot find a way to just get today's date without
the time to pull a date range...
I have tried convert (datetime, GetDate(), 112) and some others, all to no
avail... isn't there just a date function that doesn't include the time?
Try,
select convert (varchar(12), GetDate(), 101)
"Atley" <atley_1@.homtmail.com> wrote in message
news:%2315icKpEEHA.2640@.TK2MSFTNGP09.phx.gbl...
> This sounds stupid, but I cannot find a way to just get today's date
without
> the time to pull a date range...
> I have tried convert (datetime, GetDate(), 112) and some others, all to no
> avail... isn't there just a date function that doesn't include the time?
>
>
|||convert (char(10), GetDate(), 111)
"Atley" <atley_1@.homtmail.com> wrote in message
news:%2315icKpEEHA.2640@.TK2MSFTNGP09.phx.gbl...
> This sounds stupid, but I cannot find a way to just get today's date
without
> the time to pull a date range...
> I have tried convert (datetime, GetDate(), 112) and some others, all to no
> avail... isn't there just a date function that doesn't include the time?
>
>
|||You need to use a convert and convert it into a string. There is no date-only datatype in SQL Server. You can
of course have a datetime with time 00:00:00, but there's still a time portion.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Atley" <atley_1@.homtmail.com> wrote in message news:%2315icKpEEHA.2640@.TK2MSFTNGP09.phx.gbl...
> This sounds stupid, but I cannot find a way to just get today's date without
> the time to pull a date range...
> I have tried convert (datetime, GetDate(), 112) and some others, all to no
> avail... isn't there just a date function that doesn't include the time?
>
>
|||> I have tried convert (datetime, GetDate(), 112) and some others, all to no
> avail...
How exactly did you try this? What does "to no avail" mean?
Here is how I would retrieve all the rows that have a datetime within today.
DECLARE @.dt SMALLDATETIME
SET @.dt = {fn CURDATE()}
SELECT cols
FROM tbl
WHERE dtCol >= @.dt
AND dtCol < (@.dt + 1)

> isn't there just a date function that doesn't include the time?
SELECT {fn CURDATE()}
SELECT CONVERT(SMALLDATETIME,
CONVERT(CHAR(8), GETDATE(), 112))
Why is this posted to clients and datamining? Follow-ups adjusted.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/

Getting Images to render in HTML using Web Service (SOAP)

I have created a web app that uses the reporting services Web Service to
pull reports from the SQL Server.
The web server is on one machine, SQL Server on another and I use the web
service to get the reports. I do not use URLs in any way in order to keep as
much information hidden as possible.
I have been able to list all reports, select a report, get back the
parameters required, set the parameter values and render reports using
ReportingService.Render
HOWEVER...
The reports that work do not include charts (graphics). When I render a
report using my app, if it has no chart I can view it in HTML, PDF, TIF,
Excel and MHTML. If it has a chart then for PDF, Excel and TIF all is fine.
If I try to use HTML or MHTML I get a big empty space where my charts should
be.
In reading the BOL under ReportingService.Render Method
I found this: "StreamIds
[out] The stream identifiers. These IDs are passed to the RenderStream
method. You can use them to render the external resources (images, etc.)
that are associated with a given report. "
I have found this topic:
ms-help://MS.RSBOL80.1033/RSPROG/htm/rsp_ref_soapapi_service_lz_49f6.htm
ReportingService.RenderStream Method
I read the code, and I have tried to implement it as in the BOL. I am able
to write the images onto the web-server hard-drive, and the image place
holder properties no longer point to my SQL server (which they did at first)
but I still don't get an image.
For example, my image is actually at:
http://sandy.murdocks.on.ca/1/reports/temp/C_79_S
It is there and it works. Now my report opens in a new window since I do the
final call to open it like this:
Response.AddHeader("Content-Disposition", "attachment; filename=" &
fileName)
Response.BinaryWrite(data)
When I right click on the image place holder I get this:
file:///C:/Documents%20and%20Settings/Administrator/Local%20Settings/Temporary%20Internet%20Files/Content.IE5/QPIH2VE9/1/Reports/Temp/C_79_S
The images don't seem to get from being written on the drive, to being
picked up for display.
My code for writing the images looks like this:
If format = "HTML4.0" Then
For Each streamid In streamIDs
image = rs.RenderStream(strpath, "HTML4.0", streamid,
Nothing, Nothing, parameters, Nothing, Nothing)
Dim stream As System.IO.FileStream = _
System.IO.File.OpenWrite(Application("TempReports") &
streamid)
stream.Write(image, 0, CInt(image.Length))
stream.Close()
Next
End If
Sandy Murdock MCPlook at my previuos post:
http://www.microsoft.com/sql/community/newsgroups/dgbrowser/en-us/default.mspx?&query=rome&lang=en&cr=US&guid=&dg=microsoft.public.sqlserver.reportingsvcs&p=1&tid=a830cc14-9b06-496b-b1f4-e55ab3611f0b
Antonio
"Roger Twomey" wrote:
> I have created a web app that uses the reporting services Web Service to
> pull reports from the SQL Server.
> The web server is on one machine, SQL Server on another and I use the web
> service to get the reports. I do not use URLs in any way in order to keep as
> much information hidden as possible.
> I have been able to list all reports, select a report, get back the
> parameters required, set the parameter values and render reports using
> ReportingService.Render
> HOWEVER...
> The reports that work do not include charts (graphics). When I render a
> report using my app, if it has no chart I can view it in HTML, PDF, TIF,
> Excel and MHTML. If it has a chart then for PDF, Excel and TIF all is fine.
> If I try to use HTML or MHTML I get a big empty space where my charts should
> be.
> In reading the BOL under ReportingService.Render Method
> I found this: "StreamIds
> [out] The stream identifiers. These IDs are passed to the RenderStream
> method. You can use them to render the external resources (images, etc.)
> that are associated with a given report. "
> I have found this topic:
> ms-help://MS.RSBOL80.1033/RSPROG/htm/rsp_ref_soapapi_service_lz_49f6.htm
> ReportingService.RenderStream Method
> I read the code, and I have tried to implement it as in the BOL. I am able
> to write the images onto the web-server hard-drive, and the image place
> holder properties no longer point to my SQL server (which they did at first)
> but I still don't get an image.
> For example, my image is actually at:
> http://sandy.murdocks.on.ca/1/reports/temp/C_79_S
> It is there and it works. Now my report opens in a new window since I do the
> final call to open it like this:
> Response.AddHeader("Content-Disposition", "attachment; filename=" &
> fileName)
> Response.BinaryWrite(data)
> When I right click on the image place holder I get this:
> file:///C:/Documents%20and%20Settings/Administrator/Local%20Settings/Temporary%20Internet%20Files/Content.IE5/QPIH2VE9/1/Reports/Temp/C_79_S
> The images don't seem to get from being written on the drive, to being
> picked up for display.
> My code for writing the images looks like this:
> If format = "HTML4.0" Then
> For Each streamid In streamIDs
> image = rs.RenderStream(strpath, "HTML4.0", streamid,
> Nothing, Nothing, parameters, Nothing, Nothing)
> Dim stream As System.IO.FileStream = _
> System.IO.File.OpenWrite(Application("TempReports") &
> streamid)
> stream.Write(image, 0, CInt(image.Length))
> stream.Close()
> Next
> End If
>
> Sandy Murdock MCP
>
>|||Thank you!
I was able to take your solution and from it build one that works for me. I
was stuck.
Thanks.
"Antonio Rome" <AntonioRome@.discussions.microsoft.com> wrote in message
news:61BF34C8-98DB-4388-8E06-F5F8C1F22BBE@.microsoft.com...
> look at my previuos post:
>
http://www.microsoft.com/sql/community/newsgroups/dgbrowser/en-us/default.mspx?&query=rome&lang=en&cr=US&guid=&dg=microsoft.public.sqlserver.reportingsvcs&p=1&tid=a830cc14-9b06-496b-b1f4-e55ab3611f0b
> Antonio
>
> "Roger Twomey" wrote:
> > I have created a web app that uses the reporting services Web Service to
> > pull reports from the SQL Server.
> >
> > The web server is on one machine, SQL Server on another and I use the
web
> > service to get the reports. I do not use URLs in any way in order to
keep as
> > much information hidden as possible.
> >
> > I have been able to list all reports, select a report, get back the
> > parameters required, set the parameter values and render reports using
> > ReportingService.Render
> >
> > HOWEVER...
> >
> > The reports that work do not include charts (graphics). When I render a
> > report using my app, if it has no chart I can view it in HTML, PDF, TIF,
> > Excel and MHTML. If it has a chart then for PDF, Excel and TIF all is
fine.
> > If I try to use HTML or MHTML I get a big empty space where my charts
should
> > be.
> >
> > In reading the BOL under ReportingService.Render Method
> > I found this: "StreamIds
> > [out] The stream identifiers. These IDs are passed to the RenderStream
> > method. You can use them to render the external resources (images, etc.)
> > that are associated with a given report. "
> >
> > I have found this topic:
> > ms-help://MS.RSBOL80.1033/RSPROG/htm/rsp_ref_soapapi_service_lz_49f6.htm
> > ReportingService.RenderStream Method
> >
> > I read the code, and I have tried to implement it as in the BOL. I am
able
> > to write the images onto the web-server hard-drive, and the image place
> > holder properties no longer point to my SQL server (which they did at
first)
> > but I still don't get an image.
> >
> > For example, my image is actually at:
> > http://sandy.murdocks.on.ca/1/reports/temp/C_79_S
> >
> > It is there and it works. Now my report opens in a new window since I do
the
> > final call to open it like this:
> > Response.AddHeader("Content-Disposition", "attachment; filename=" &
> > fileName)
> > Response.BinaryWrite(data)
> >
> > When I right click on the image place holder I get this:
> >
file:///C:/Documents%20and%20Settings/Administrator/Local%20Settings/Temporary%20Internet%20Files/Content.IE5/QPIH2VE9/1/Reports/Temp/C_79_S
> >
> > The images don't seem to get from being written on the drive, to being
> > picked up for display.
> >
> > My code for writing the images looks like this:
> >
> > If format = "HTML4.0" Then
> > For Each streamid In streamIDs
> > image = rs.RenderStream(strpath, "HTML4.0", streamid,
> > Nothing, Nothing, parameters, Nothing, Nothing)
> >
> > Dim stream As System.IO.FileStream = _
> > System.IO.File.OpenWrite(Application("TempReports") &
> > streamid)
> >
> > stream.Write(image, 0, CInt(image.Length))
> > stream.Close()
> > Next
> > End If
> >
> >
> > Sandy Murdock MCP
> >
> >
> >|||I've been rendering chart images successfully for a report using the
ReportingService web services for a few weeks now. Today I had to add a
parameter to the report and now after I call my render method and loop
through the returned streamId's to get the images, I get the exception:
"rsStreamNotFound The stream cannot be found. The stream identifier that is
provided to an operation cannot be located in the report server database."
Why would this be happening?! I'm looping through the very streamId's that
the render method just returned to me!
Thanks in advance, Brian Parker