Sql Ignoring spaces: stumped

Showing posts with label stumped. Show all posts

Monday, March 19, 2012

I'm Stumped! - (AS 2005 - Remote Connections Cannot be enabled)

Hello SQL pros -
I am having a problem with enabling the remote connections for Analysis
Services 2005, Here's the gist...
--Error Information--
"To configure Analysis Services for Remote Connections, you must be an
adminsitrator for Analysis Services Windows Service (SQLSAC).
Additonal Info
A connection cannot be made. Ensure that service is running
(Microsoft.AnalysisServices)
No connection could be made because the target machine actively refused
it (System)."
--Actions Taken--
1) Analysis Services Windows Service was confirmed to be running.
2) Analysis Services Windows Service was stopped and restarted.
3) Analysis Services Windows Service (and SQL Accounts) confirmed to be
running under domain account with administrator priviledges.
4) Confirmed connectivity to Analysis Services instance through direct
connection (Windows Authentication)
--OS/System Information--
Windows 2000 Service Pack 5
--Clustering Roles--
Primary node for 1 SQL 2000 SP4 instance
Secondary node 1 SQL 2000 SP4 instance
Primary node for 1 Analysis Services 2000 instance
Primary node for 1 SQL 2005 instance, and primary
Primary node for 1 SQL 2005 Analysis Services Instance
I didn't find anything of relevance in the Windows logs or SQL logs.
So I am not sure where else to look. Is this a bug with 2005?
Any Suggestions to help troubleshoot are greatly appreciated!
thanks in advance.
benBP,
Do you have a firewall program installed? Is this or a previous version
CTP?
HTH
Jerry
"BP" <BPollitt01@.gmail.com> wrote in message
news:1148655002.461974.305510@.i39g2000cwa.googlegroups.com...
> Hello SQL pros -
> I am having a problem with enabling the remote connections for Analysis
> Services 2005, Here's the gist...
> --Error Information--
> "To configure Analysis Services for Remote Connections, you must be an
> adminsitrator for Analysis Services Windows Service (SQLSAC).
> Additonal Info
> A connection cannot be made. Ensure that service is running
> (Microsoft.AnalysisServices)
> No connection could be made because the target machine actively refused
> it (System)."
> --Actions Taken--
> 1) Analysis Services Windows Service was confirmed to be running.
> 2) Analysis Services Windows Service was stopped and restarted.
> 3) Analysis Services Windows Service (and SQL Accounts) confirmed to be
> running under domain account with administrator priviledges.
> 4) Confirmed connectivity to Analysis Services instance through direct
> connection (Windows Authentication)
> --OS/System Information--
> Windows 2000 Service Pack 5
> --Clustering Roles--
> Primary node for 1 SQL 2000 SP4 instance
> Secondary node 1 SQL 2000 SP4 instance
> Primary node for 1 Analysis Services 2000 instance
> Primary node for 1 SQL 2005 instance, and primary
> Primary node for 1 SQL 2005 Analysis Services Instance
> I didn't find anything of relevance in the Windows logs or SQL logs.
> So I am not sure where else to look. Is this a bug with 2005?
> Any Suggestions to help troubleshoot are greatly appreciated!
> thanks in advance.
> ben
>

I'm Stumped! - (AS 2005 - Remote Connections Cannot be enabled)

I'm Stumped (Complex Joins With and Updates)

My head is spinning trying to figure this one out. Ok. Here is the schema...

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE TABLE [snmpPoller_HostConfig](

[id] [int] IDENTITY(1,1) NOT NULL,

[friendlyName] [nvarchar](50) NOT NULL,

[hostname] [nvarchar](255) NOT NULL,

[community] [nvarchar](255) NOT NULL,

[version] [int] NOT NULL,

[queryTimeout] [int] NOT NULL,

[isActive] [bit] NOT NULL,

[passedSanityCheck] [bit] NOT NULL CONSTRAINT [DF_snmpPoller_HostConfig_passedSanityCheck] DEFAULT ((0)),

CONSTRAINT [PK_snmpPoller_HostConfig] PRIMARY KEY CLUSTERED

(

[id] ASC

)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE TABLE [snmpPoller_TargetConfig](

[id] [int] IDENTITY(1,1) NOT NULL,

[hostId] [int] NOT NULL,

[description] [nvarchar](50) NOT NULL,

[oid] [nvarchar](255) NOT NULL,

[interval] [int] NOT NULL,

[lastPollTimestamp] [datetime] NOT NULL,

[nextPollTime] AS (dateadd(second,[interval],[lastPollTimestamp])),

[isActive] [bit] NOT NULL,

[isPolling] [bit] NOT NULL,

CONSTRAINT [PK_snmpPoller_TargetConfig] PRIMARY KEY CLUSTERED

(

[id] ASC

)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

ALTER TABLE [dbo].[snmpPoller_TargetConfig] WITH CHECK ADD CONSTRAINT [FK_snmpPoller_TargetConfig_snmpPoller_HostConfig] FOREIGN KEY([hostId])

REFERENCES [snmpPoller_HostConfig] ([id])

Ok. So I have this query

WITH WorkTable ( Tag, Parent, [Host!1!hostId],

[Target!2!targetId], [Host!1!hostname], [Host!1!community],

[Host!1!version], [Host!1!timeout], [Target!2!oid], [Target!2!interval]

)

(

SELECT DISTINCT

1 AS Tag,

NULL AS Parent,

H.id AS [Host!1!hostId],

NULL AS [Target!2!targetId],

hostname AS [Host!1!hostname],

community AS [Host!1!community],

version AS [Host!1!version],

queryTimeout AS [Host!1!timeout],

NULL AS [Target!2!oid],

NULL AS [Target!2!interval]

FROM snmpPoller_HostConfig H, snmpPoller_TargetConfig T

WHERE H.id = T.hostId

AND t.isPolling = 0

AND t.isActive = 1

AND h.isActive = 1

AND t.lastPollTimestamp < { fn NOW() }

AND h.passedSanityCheck = 1

UNION ALL

SELECT DISTINCT

2 AS Tag,

1 AS Parent,

hostId AS [Host!1!hostId],

T.id AS [Target!2!targetId],

NULL AS [Host!1!hostname],

NULL AS [Host!1!community],

NULL AS [Host!1!version],

NULL AS [Host!1!timeout],

oid AS [Target!2!oid],

interval AS [Target!2!interval]

FROM snmpPoller_HostConfig H, snmpPoller_TargetConfig T

WHERE H.id = T.hostId

AND t.isPolling = 0

AND t.isActive = 1

AND h.isActive = 1

AND t.lastPollTimestamp < { fn NOW() }

AND h.passedSanityCheck = 1

)

SELECT * FROM WorkTable

ORDER BY [Host!1!hostId], [Target!2!targetId]

FOR XML EXPLICIT, ROOT ('Hosts')

That generates this xml

<Hosts>

</Host>

</Host>

</Hosts>

Now the problem is that the xml that I generate is to ‘big’. When I submit a get request to the host, I can only fit 128 OIDs into the request PDU. My query, as it is, returns many hosts with upwards with 1000 targets. So question number 1 is how do return the top 128 targets for each host?

Now here is the real problem. I am replacing a working design in an effort to pick up a scale point. The current design has a dispatcher that will hand off the hostId to a work queue where it is picked up by a ‘thread’. This thread will then execute this sql.

SqlCommand^ command = connection->CreateCommand();

command->Parameters->AddWithValue("@.hostId", hostId);
command->CommandText =

"DECLARE @.resultTable TABLE("+

" targetId int NOT NULL,"+

" hostId int NOT NULL,"+

" oid nvarchar(255) NOT NULL"+

")"+

""+

"UPDATE TOP(128) snmpPoller_TargetConfig"+

" SET isPolling = 1 "+

" OUTPUT INSERTED.id, INSERTED.hostId, INSERTED.oid INTO @.resultTable"+

"WHERE (isPolling = 0) AND (isActive = 1) AND (id = @.hostId) AND (lastPollTimestamp < { fn NOW() })"+

""+

"SELECT * FROM @.resultTable";

SqlDataReader^ reader = command->ExecuteReader();

So rather than make (host * (number of targets / 128)) calls per dispatch cycle, I need to make it all in one call.

So SQL gurus…. What’s the kung-fu? How do I get all my hosts with the top 128 targets AND set a flag on all targets in one atomic shot (I believe this ‘one-shot’ statement can be built off the with(), but I’m nothing but confused at this point)

Paul

Need SQL Advice? http://sqladvice.com
Need RegEx Advice? http://regexadvice.com
Need XML Advice? http://xmladvice.com

is it possible to use ROW_NUM() ranking function within your query and then get the outputs based on row num. Also if you paste your sample data it shall aid the understanding|||

I actually just came up with this. It works just fine, but I'd like to consolidate it down to one statement. Any thoughts?

BEGIN TRANSACTION

DECLARE @.buffer TABLE

(

Tag int,

Parent int,

[Host!1!hostId] int,

[Target!2!targetId] int,

[Host!1!hostname] nvarchar(255),

[Host!1!community] nvarchar(255),

[Host!1!version] int,

[Host!1!timeout] int,

[Target!2!oid] nvarchar(255),

[Target!2!interval] int

);

WITH WorkTable ( Tag, Parent, [Host!1!hostId],

[Target!2!targetId], [Host!1!hostname], [Host!1!community],

[Host!1!version], [Host!1!timeout], [Target!2!oid], [Target!2!interval]

)

(

SELECT DISTINCT

1 AS Tag,

NULL AS Parent,

H.id AS [Host!1!hostId],

NULL AS [Target!2!targetId],

hostname AS [Host!1!hostname],

community AS [Host!1!community],

version AS [Host!1!version],

queryTimeout AS [Host!1!timeout],

NULL AS [Target!2!oid],

NULL AS [Target!2!interval]

FROM snmpPoller_HostConfig H, snmpPoller_TargetConfig T

WHERE H.id = T.hostId

AND t.isPolling = 0

AND t.isActive = 1

AND h.isActive = 1

AND t.lastPollTimestamp < { fn NOW() }

AND h.passedSanityCheck = 1

UNION ALL

SELECT DISTINCT

2 AS Tag,

1 AS Parent,

hostId AS [Host!1!hostId],

T.id AS [Target!2!targetId],

NULL AS [Host!1!hostname],

NULL AS [Host!1!community],

NULL AS [Host!1!version],

NULL AS [Host!1!timeout],

oid AS [Target!2!oid],

interval AS [Target!2!interval]

FROM snmpPoller_HostConfig H, snmpPoller_TargetConfig T

WHERE H.id = T.hostId

AND t.isPolling = 0

AND t.isActive = 1

AND h.isActive = 1

AND t.lastPollTimestamp < { fn NOW() }

AND h.passedSanityCheck = 1

AND t.id IN(

SELECT TOP(128) id FROM snmpPoller_TargetConfig

WHERE isPolling = 0 AND isActive = 1 AND lastPollTimestamp < { fn NOW() }

AND hostId = h.id

)

INSERT INTO @.buffer

SELECT * FROM WorkTable

UPDATE t SET isPolling = 1

FROM snmpPoller_TargetConfig t,

@.buffer b

WHERE

t.id = b.[Target!2!targetId]

SELECT * FROM @.buffer

ORDER BY [Host!1!hostId], [Target!2!targetId]

FOR XML EXPLICIT, ROOT ('Hosts')

COMMIT TRANSACTION

Sorry, I can't post the data, the schema should be suffecient. Just say there for 100 hosts records and 750 target records per host.

|||

Please take a look at the OUTPUT clause. You can just perform the UPDATE statement and get the relevant columns (even expressions) from the inserted/deleted tables into a table variable for example. You can then do the SELECT against the table variable. Please take a look at my blog post below for one such example:

http://blogs.msdn.com/sqltips/archive/2005/06/13/OUTPUT_clause.aspx

I'm Stumped - Adding values of two columns

I am at a loss...I have tried everything.. Hopefull this is so easy
I'm missing it all together.
I have a very simple data entry table that is going to be used in other
areas (asp, etc.)
The table contains several columns. I need to have some of the columns
automatically equal the values of other columns added together.
ie.
Column Value
JanSalesGoals 4
FebSalesGoalsn 6
MarSalesGoals 3
Qtr1SalesGoals =[JanSalesGoals]+[FebSalesGoals]+[MarSal
esGoals]
Having the column of Qtr1SalesGoals come out to equal '13'.
The best I have acheived is getting the column to equal 463, which is
not the desired result.
There is a reason why I am trying to make it work this way in the table
itself.
Any and all sugestions are aprreciated.Convert to integer before adding, see example
declare @.val1 char(1)
declare @.val2 char(1)
declare @.val3 char(1)
select @.val1='4',@.val2 ='6',@.val3 ='3'
select @.val1+ @.val2 + @.val3
select convert(int,@.val1)+ convert(int,@.val2) + convert(int,@.val3)
Denis the SQL Menace
http://sqlservercode.blogspot.com/
ehorde@.burgoonberger.com wrote:
> I am at a loss...I have tried everything.. Hopefull this is so easy
> I'm missing it all together.
> I have a very simple data entry table that is going to be used in other
> areas (asp, etc.)
> The table contains several columns. I need to have some of the columns
> automatically equal the values of other columns added together.
> ie.
> Column Value
> JanSalesGoals 4
> FebSalesGoalsn 6
> MarSalesGoals 3
> Qtr1SalesGoals =[JanSalesGoals]+[FebSalesGoals]+[MarSal
esGoals]
> Having the column of Qtr1SalesGoals come out to equal '13'.
> The best I have acheived is getting the column to equal 463, which is
> not the desired result.
> There is a reason why I am trying to make it work this way in the table
> itself.
> Any and all sugestions are aprreciated.|||What is the datatype of "value"? If it is not a numeric datatype, why not?
This is not VBScript, and you can't just switch in math operators against
strings. SQL Server doubles up the use of + for string concatenation and
mathematical addition. Observe:
DECLARE @.foo VARCHAR(12), @.bar VARCHAR(12);
SET @.foo = 4;
SET @.bar = 3;
SELECT @.foo + @.bar;
SELECT CONVERT(INT, @.foo) + CONVERT(INT, @.bar);
GO
DECLARE @.foo INT, @.bar INT;
SET @.foo = 4;
SET @.bar = 3;
SELECT @.foo + @.bar;
SELECT CONVERT(VARCHAR(12), @.foo) + CONVERT(VARCHAR(12), @.bar);
<ehorde@.burgoonberger.com> wrote in message
news:1149270430.339282.245030@.h76g2000cwa.googlegroups.com...
>I am at a loss...I have tried everything.. Hopefull this is so easy
> I'm missing it all together.
> I have a very simple data entry table that is going to be used in other
> areas (asp, etc.)
> The table contains several columns. I need to have some of the columns
> automatically equal the values of other columns added together.
> ie.
> Column Value
> JanSalesGoals 4
> FebSalesGoalsn 6
> MarSalesGoals 3
> Qtr1SalesGoals =[JanSalesGoals]+[FebSalesGoals]+[MarSal
esGoals]
> Having the column of Qtr1SalesGoals come out to equal '13'.
> The best I have acheived is getting the column to equal 463, which is
> not the desired result.
> There is a reason why I am trying to make it work this way in the table
> itself.
> Any and all sugestions are aprreciated.
>|||Generally you should never have columns like that, since you can always
calculate the values on the way back out for viewing and reporting.
So how are you attempting to update this column? In code? In a trigger?
Bottom line, convert the values to numeric first before adding. You are
seeing string concatenation.
Jeff
<ehorde@.burgoonberger.com> wrote in message
news:1149270430.339282.245030@.h76g2000cwa.googlegroups.com...
>I am at a loss...I have tried everything.. Hopefull this is so easy
> I'm missing it all together.
> I have a very simple data entry table that is going to be used in other
> areas (asp, etc.)
> The table contains several columns. I need to have some of the columns
> automatically equal the values of other columns added together.
> ie.
> Column Value
> JanSalesGoals 4
> FebSalesGoalsn 6
> MarSalesGoals 3
> Qtr1SalesGoals =[JanSalesGoals]+[FebSalesGoals]+[MarSal
esGoals]
> Having the column of Qtr1SalesGoals come out to equal '13'.
> The best I have acheived is getting the column to equal 463, which is
> not the desired result.
> There is a reason why I am trying to make it work this way in the table
> itself.
> Any and all sugestions are aprreciated.
>|||Setting to int from varchar fixed the formula.
([Jan Sales Goals] + [Feb Sales Goals] + [Mar Sales Goals])
Thanks very much,
Trees in the way of the Forrest View syndrome
SQL Menace wrote:
> Convert to integer before adding, see example
> declare @.val1 char(1)
> declare @.val2 char(1)
> declare @.val3 char(1)
> select @.val1='4',@.val2 ='6',@.val3 ='3'
> select @.val1+ @.val2 + @.val3
> select convert(int,@.val1)+ convert(int,@.val2) + convert(int,@.val3)
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/
> ehorde@.burgoonberger.com wrote:

Monday, March 19, 2012

I'm Stumped! - (AS 2005 - Remote Connections Cannot be enabled)

I'm Stumped! - (AS 2005 - Remote Connections Cannot be enabled)

I'm Stumped (Complex Joins With and Updates)

I'm Stumped - Adding values of two columns

Sql Ignoring spaces

Blog Archive

About Me