Invalid Object Name - Grr...

This is what I have. It works fine until I get to the select statement, then it tells me that I have an invalid object name. What am I missing? Thanks!

DECLARE @.SvrName varchar(100)

if @.@.SERVERNAME='pubs' begin
set @.SvrName=pubs.books.isbn
print @.SvrName
end
if @.@.SERVERNAME='MGMFILENET' begin
set @.SvrName=store.books.isbn
print@.SvrName
end

print @.@.SERVERNAME
PRINT @.SvrName
SELECT * FROM "@.SvrName"Doesn't work that way

DECALRE @.SQL
SET @.SQL = 'SELECT * FROM ' + @.SvrName
EXEC(@.SQL)

But why do this...uhh dynamic sql...|||Even in a stored proc?

What I am trying to do is find out what the server is, then point the rest of the gazillion SQL statements to follow to that server.|||Originally posted by acral
Even in a stored proc?

What I am trying to do is find out what the server is, then point the rest of the gazillion SQL statements to follow to that server.

Huh?|||This will be running as a stored proc... the proc takes many steps in moving data around, but first I need to determine what environment the user is in (i.e. what server)...

The there will be a series of statements such as Update this table, email a percentage to that group, make a temp table over there, and so on. In one environment, all of the databases are on one server, in another environment, the database are on different servers. In both cases, they have to interact.

So if YOU are logged into the system, when the stored proc executes, it will see which server you are logged to then point you from there by way of the rest of the statements.

Make sense?|||Not to me, but that's not saying much..

What's the application layer?|||Wouldn't it just be simpler to write a stored procedure that does what you need on each server, then call the stored procedure on the appropriate server? This gets about a gazillion RPC calls and cross-server queries (with potential cross-server joins) out of the way.

That way each box can call one stored procedure (you could even make it an sp_ if you wanted to make thing simple), and there are so many fewer moving parts.

-PatP|||Okay, here's what I ended up doing...

using a string like

Exec(@.SQL) was not going to cut it, so I have an if/then scenario that checks @.@.SERVERNAME then sets a variable. The contents of that variable in turn point to the right server for the right instance.

The reasoning is this.. in one evironment the databases referenced are on the same server, in another environment they are on different servers.

Thanks for the help... I would have kept pounding on that stupid "string" half the night had you guys not set me straight. :)

Interpolating a line for missing data points in line / bar graph

I know I'm not the only one with this problem. I have a set of data values I want to display as lines from left to right, grouped by month, and a series is specified. There is no aggregation as the series/month combination is unique. However, sometimes there is no data for a given series and month. When this happens, my line breaks at the previous month and starts again on the next month. I tried using a column chart and specifying plot data as line, but I still get the same result. Is there a way to get SQL Server Reporting Services 2000 to connect the dots with the existing data or do I have to alter my stored procedure and create an interpolation scheme for missing months?

If not, will this be addressed in 2005?

Thanks in advance

Please make sure you have RS 2000 SP2 installed (on the report server and the report designer machines).

On SQL Server 2005 it will work just fine.

-- Robert

|||

I have the opposite problem. I'm using SQL Server 2005. I have data to graph by month, but when I have no data for a given month, I'd like like to see the gap in the line. If I leave out the row, the graph just leaves the month out. If I make the data value null, it puts the month in, leaves the marker off, but still draws a line from the previous to the next month. Is there a trick to make the line break?

|||I have the same problem - I am plotting a financial instrument and want to overlay the plot with buy/sell activity. Thus I buy at, say 09:00 and sell at 09:30 before buying again at 10:00. The Chart plots a line between the end of the buy line at 09:30 and the next buy at 10:00. This is despite the result set having a a null value between these two times.
Has anyone an answer to this problem?

Interpolating a line for missing data points in line / bar graph

I know I'm not the only one with this problem. I have a set of data values I want to display as lines from left to right, grouped by month, and a series is specified. There is no aggregation as the series/month combination is unique. However, sometimes there is no data for a given series and month. When this happens, my line breaks at the previous month and starts again on the next month. I tried using a column chart and specifying plot data as line, but I still get the same result. Is there a way to get SQL Server Reporting Services 2000 to connect the dots with the existing data or do I have to alter my stored procedure and create an interpolation scheme for missing months?

If not, will this be addressed in 2005?

Thanks in advance

Please make sure you have RS 2000 SP2 installed (on the report server and the report designer machines).

On SQL Server 2005 it will work just fine.

-- Robert

|||

I have the opposite problem. I'm using SQL Server 2005. I have data to graph by month, but when I have no data for a given month, I'd like like to see the gap in the line. If I leave out the row, the graph just leaves the month out. If I make the data value null, it puts the month in, leaves the marker off, but still draws a line from the previous to the next month. Is there a trick to make the line break?

Internet Merge Replication IIS

Maybe I am missing something here, but it seems like the point of doing Merge Replication via IIS is so that you do not have to expose your DB server directly to the internet (i.e. all your clients), rather you expose a webserver to the internet and the webserver handles the replication keeping your DB server closed to direct internet access and thus keeping it more secure.

However, to set up Internet Merge Replication using IIS a client has to have direct internet access to the DB server as a stored procedure needs to be run on the publisher.

So Internet Merge Replciation using IIS makes it so your DB does not have to be directly exposed to the internet except your db needs to be exposed directly to the internet so every client can execute a stored procedure against it. How does this make sense? Yeah, I can write a webservice or something that gets the SP from the client and executes it against my db server but why should I have to? Why isn't the dll that the "Configure Web Synchronization" wizard puts into place capable of doing this?

Using web sync not only "hide" your sql server from the client, but also extends the merge replication to devices as well as PCs in anywhere that has the internet access. the later is actually the main reason to have this new sync type.

Sure, one can write their own web services to do the sync ( in fact, I believe there are some companies doing that already ), but this will not fully utilize the merge replication's functionalities to the web client subscriptions as it was to the fully connected subscriptions.

hence, the "Configure web synchronization" wizard means to provide some walk throguh steps to help users to set up the IIS server for replication and not to intend to setup the webservices.

thanks

Yunwen

|||

Thank you for your reply, but your reply...parts don't make sense and other parts don't answer what I asked.

>> but also extends the merge replication to devices as well as PCs in anywhere that has the internet access. the later is actually the main reason to have this new sync type.

That is NOT the "main reason" of doing web sync via IIS. Since for a client to do merge synching offsite, they have to have internet access to the sql server location, synching could be accomplished quite nicely without IIS by merely poking the appropriate holes in your firewall to allow the needed direct sql connections to be made. Thus IIS is not what facilitates merge synching over the internet. It still seems to me that the ONLY point of doing merge replication via IIS is to hide your SQL server from direct internet access.

>>Sure, one can write their own web services to do the sync

I never said this. What I said was that to set up Merge replication a client needed direct access to a sql server because a SP (Stored Procedure) has to be run against the Publisher that registeres the subscriber with the publisher. If you are using IIS to accomplish your merge replication, then you do not have your sql server directly exposed to the internet which makes it rather hard for your subscriber to connect directly to the publisher to execute a SP. What I said was that I could write a Webservice that could facilitate the subscriber sending the SP to the publisher but that I shouldn't have to as the replisapi.dll should be doing that. I said nothing about writing a webservice to do the sync, just that as things stand now I would have to write a webservice to facilitate setting up the sync.

So again, it seems like the point of using IIS to do merge replication is to hide your SQL Server publisher from the internet. However, part of setting up a subscriber involves running a SP (Stored Procedure) against the SQL Server Publisher. If your SQL Server publisher is hidden from the internet, it makes it rather impossible for your subscriber to connect to it to run a SP against it. It seems that currently the only work around is to make your own webservice that can access the SP arguments from the subscriber and run the proper SP with the proper arguments against your publisher for you. It seems sort of silly that you would have to set that up yourself, that MS hasn't built that in to something that already exists.

The situation is analguous to the Exchange RPC over HTTPS problem that occured when Office 2003 first came out. RPC over HTTPS allows Outlook to run against an Exchange server without having to have direct port 135-139 access. However in order to set up RPC over HTTPS your computer FIRST had to be able to connect directly to Exchange on ports 135-139 (which was a problem for me since I use Comcast and Comcast blocks these ports). The only work around was to lug my computer into the office, put it on a lan with the exchange server, setup RPC over HTTPS, then take it back home.) MS has since fixed this problem. However, this seems like the exact same problem. Doing merge replication via IIS makes it so that clients do not have to connect directly to the SQL Server, however to initially set up each client, that client has to connect directly to the SQL Server. Is there a way around this?

Thank you

|||

Many people may not have ability to connect to publisher directly through port 1433. That is one of the main motivation to support https (port 443) replication. IIS server is needed to understand the https protocol.

|||

Yes, that is right along the lines of what I have been saying.

Why is this so hard to understand? I will try to make it simple.

I have a sql server that is acting as a publisher. This publisher is not directly accessable via the internet. I have set up Merge replication using IIS and https on a server running IIS. The IIS server is accessable to the internet on ports 80 and 443. Ok? Simple enough.

I want to connect a subscriber to this publisher. The subscriber is somewhere on the internet. In order for the subscriber to successfully set up its subscription, it needs to execute the Stored Procedure named "sp_addmergesubscription" ON THE PUBLISHER!

As you so aptly pointed out "Many people may not have ability to connect to publisher directly through port 1433" that being the case, how then is a subscriber supposed to run the stored procedure "sp_addmergesubscription" on the publisher?

|||

You have two choices:

1. Add the subscription at the publisher before subscriber connects. Meaning an administrator at the publisher will need to add this information, not the subscriber since the subscriber does not have access.

2. Connect as an anonymous subscriber. Meaning if subscriber cannot have their subscription added to the publication, then connect as an anonymous subscriber, which will add the info for you on your first connect.

Internet explorer lost from server

Gurus--
I am not really sure how it happened but on a small local server that I
admin seems to have lost IE, "The E" is missing, I am running xp pro I
have a connection to msn dsl on this server and had to reload all os and
server due to spyware attack. Since then the "E" is gone I need to have
in order to set up wireless network. How do you reload ie without
running the whole setup again ? Is this possible?
*** Sent via Developersdex http://www.codecomments.com ***
I think this should be posted in one of the windowsXP newsgroups.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"Bill" <nospam@.devdex.com> wrote in message
news:OutdtFu8GHA.4476@.TK2MSFTNGP04.phx.gbl...
> Gurus--
> I am not really sure how it happened but on a small local server that I
> admin seems to have lost IE, "The E" is missing, I am running xp pro I
> have a connection to msn dsl on this server and had to reload all os and
> server due to spyware attack. Since then the "E" is gone I need to have
> in order to set up wireless network. How do you reload ie without
> running the whole setup again ? Is this possible?
> *** Sent via Developersdex http://www.codecomments.com ***
|||Hi
This does not seem to have anything to do with SQL server so I don't know
why you have posted it to this group. It sounds like your network settings
are incorrect, therefore you need to check them against what they should be.
John
"Bill" wrote:

> Gurus--
> I am not really sure how it happened but on a small local server that I
> admin seems to have lost IE, "The E" is missing, I am running xp pro I
> have a connection to msn dsl on this server and had to reload all os and
> server due to spyware attack. Since then the "E" is gone I need to have
> in order to set up wireless network. How do you reload ie without
> running the whole setup again ? Is this possible?
> *** Sent via Developersdex http://www.codecomments.com ***
>

Internet explorer lost from server

Gurus--
I am not really sure how it happened but on a small local server that I
admin seems to have lost IE, "The E" is missing, I am running xp pro I
have a connection to msn dsl on this server and had to reload all os and
server due to spyware attack. Since then the "E" is gone I need to have
in order to set up wireless network. How do you reload ie without
running the whole setup again ? Is this possible'
*** Sent via Developersdex http://www.developersdex.com ***I think this should be posted in one of the windowsXP newsgroups.
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"Bill" <nospam@.devdex.com> wrote in message
news:OutdtFu8GHA.4476@.TK2MSFTNGP04.phx.gbl...
> Gurus--
> I am not really sure how it happened but on a small local server that I
> admin seems to have lost IE, "The E" is missing, I am running xp pro I
> have a connection to msn dsl on this server and had to reload all os and
> server due to spyware attack. Since then the "E" is gone I need to have
> in order to set up wireless network. How do you reload ie without
> running the whole setup again ? Is this possible'
> *** Sent via Developersdex http://www.developersdex.com ***|||Hi
This does not seem to have anything to do with SQL server so I don't know
why you have posted it to this group. It sounds like your network settings
are incorrect, therefore you need to check them against what they should be.
John
"Bill" wrote:
> Gurus--
> I am not really sure how it happened but on a small local server that I
> admin seems to have lost IE, "The E" is missing, I am running xp pro I
> have a connection to msn dsl on this server and had to reload all os and
> server due to spyware attack. Since then the "E" is gone I need to have
> in order to set up wireless network. How do you reload ie without
> running the whole setup again ? Is this possible'
> *** Sent via Developersdex http://www.developersdex.com ***
>

Internet explorer lost from server

Gurus--
I am not really sure how it happened but on a small local server that I
admin seems to have lost IE, "The E" is missing, I am running xp pro I
have a connection to msn dsl on this server and had to reload all os and
server due to spyware attack. Since then the "E" is gone I need to have
in order to set up wireless network. How do you reload ie without
running the whole setup again ? Is this possible'
*** Sent via Developersdex http://www.codecomments.com ***I think this should be posted in one of the windowsXP newsgroups.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"Bill" <nospam@.devdex.com> wrote in message
news:OutdtFu8GHA.4476@.TK2MSFTNGP04.phx.gbl...
> Gurus--
> I am not really sure how it happened but on a small local server that I
> admin seems to have lost IE, "The E" is missing, I am running xp pro I
> have a connection to msn dsl on this server and had to reload all os and
> server due to spyware attack. Since then the "E" is gone I need to have
> in order to set up wireless network. How do you reload ie without
> running the whole setup again ? Is this possible'
> *** Sent via Developersdex http://www.codecomments.com ***|||Hi
This does not seem to have anything to do with SQL server so I don't know
why you have posted it to this group. It sounds like your network settings
are incorrect, therefore you need to check them against what they should be.
John
"Bill" wrote:

> Gurus--
> I am not really sure how it happened but on a small local server that I
> admin seems to have lost IE, "The E" is missing, I am running xp pro I
> have a connection to msn dsl on this server and had to reload all os and
> server due to spyware attack. Since then the "E" is gone I need to have
> in order to set up wireless network. How do you reload ie without
> running the whole setup again ? Is this possible'
> *** Sent via Developersdex http://www.codecomments.com ***
>