One Node One Machine Cluster?

Hi,
I am in the process of building a SQL Server "farm," but having to tone down
the pace due to $$. I have just purchased a RAID1 (2 disc/10K rpm/146 GB)
Channel 0, RAID5 (3 disc/10K rpm/146 GB) Channel 1 server with 2 CPU's. This
summer, I plan to buy an identical machine. They will both run Win 2003 EE,
with SQL Server 2000 EE. Rather than install SQL Server locally on my
existing machine, is there a problem with installing MSCS and a virtual
server, even though I only have the one machine now. Of course, it won't
give me failover protection, but it would give me the configuration I want
when I get my second box. Does this make any sense? Any caveats or words of
wisdom?
PS. The servers will be accessed in OTLP mode by an ADO.NET app running on
Terminal Server (Win 2003), and BizTalk Server 2004 - also on a Win 2003 box.
Most of the ADO.NET traffic will occur during business hours, with BTS
getting busy after hours.
Thanks.
John
Hi
Why don't you rather build a single node cluster? Build a normal cluster
node, but not add another node. Once you have bought your other machine, add
it to the cluster.
Just be aware, it is very difficult to get the exact same hardware months
apart. If you are going to use clustering, make sure all the hardware is on
the Windows Hardware Compatibility List (HCL).
A cluster "on the cheap" will just cause you hassles later. I've seen enough
"lets do it with a s little as we can", and the cluster ends up with worse
uptime than a single machine.
Regards
Mike
"JT" wrote:

> Hi,
> I am in the process of building a SQL Server "farm," but having to tone down
> the pace due to $$. I have just purchased a RAID1 (2 disc/10K rpm/146 GB)
> Channel 0, RAID5 (3 disc/10K rpm/146 GB) Channel 1 server with 2 CPU's. This
> summer, I plan to buy an identical machine. They will both run Win 2003 EE,
> with SQL Server 2000 EE. Rather than install SQL Server locally on my
> existing machine, is there a problem with installing MSCS and a virtual
> server, even though I only have the one machine now. Of course, it won't
> give me failover protection, but it would give me the configuration I want
> when I get my second box. Does this make any sense? Any caveats or words of
> wisdom?
> PS. The servers will be accessed in OTLP mode by an ADO.NET app running on
> Terminal Server (Win 2003), and BizTalk Server 2004 - also on a Win 2003 box.
> Most of the ADO.NET traffic will occur during business hours, with BTS
> getting busy after hours.
> Thanks.
> --
> John

On error i want to send error description as mail

I have create a SSIS package, for data export import process, but if my task get failed then i have to send a mail with proper error description as the SSIS generate in output window.

Can any one sugget me to, how can i store that error desciption in my variable.?

Thanks in advance.

You can create a table with the following fields packageid,packagename,taskname,errormessage.

And later write a stored procedure which will insert the data into this table from the package variables. On event handler tab call this stored procedure and pass the system variables "system::executioninstanceguid","system::packagename","system::sourcename","system::errordescription".

Once this is done you can read values from this particular table and assign it to a variable and later on call it in the send mail task.Hope this will help you.

|||

Use OnError Event in Event Handler and system variables such as [System::ErrorCode] and [System::ErrorDescription]

http://msdn2.microsoft.com/en-us/library/ms139744.aspx

|||

As you define above i'm doing such a way

dts.variable("User::vName").value = dts.variable("System::ErrorDescription")

but it is going me error in my script task.

:-(

please provide more specific detail or information.

|||

Hi..

1. In OnError Event Handler, Add a Script Task..

2. Define ReadWriteVariables value in ScriptTask Properties like this

ReadWriteVariables | User::vName,System::ErrorDescription

3. In VSA, edit your script like this..

Dts.Variables("vName").Value = Dts.Variables("ErrorDescription").Value.ToString

HTH

ADConsulting / SQLLeader.com / Daeseong Han

|||

Thanks for you needful help.

:-)

sql

OLE DB timeout processing AS Cube

When doing a full process of the Analysis Services Cube with several partitioned measure groups. We receive: "OLE DB error: OLE DB or ODBC error: Query timeout expired HYT00" or "OLE DB error: OLE DB or ODBC error: Query timeout expired HY008". There are 410 million rows in one base table and 80 million rows in another base table. The other measure groups process fine. It has abended at 61 minutes the last 3 times. I can individually process each measure group successfully.

Any help is greatly appreciated, Edwina Derrick

Simular problem for me. Have a huge database to be queried and can't pass the 60 minutes query time despite having made sure that ALL!! timeout values are suposed to be eternal (zero in most cases). This seems like a bug to me. I used the developer release version of the SQL2005. Would appriciate the Microsoft development team to test this problem and to suply information of when and how to bypass this 1 hour timeout limitation.

|||Try increasing ExternalCommandTimeout Analysis Server advanced property.

Being in situation when Analysis Server needs to wait for an hour for some data to come from SQL Server is very unusual. Such situation ususally means that SQL server needs to perform some heavy operation before it can start sending data out. Try optimizing your SQL Server database.
Edward Melomed (MSFT)

---

This posting is provided "AS IS" with no warranties, and confers no rights.|||

WTF....

isn't there a way to set the timeout?

If I was able to "Optimize my SQL Server database".....

I have five fact tables (All of them are required) and each table has it's own Dimensions.

since I am unable to create a cube on a left join relationship I need to work with views ("SQL server needs to perform some heavy operation before it can start sending data out").

OLE DB timeout processing AS Cube

When doing a full process of the Analysis Services Cube with several partitioned measure groups. We receive: "OLE DB error: OLE DB or ODBC error: Query timeout expired HYT00" or "OLE DB error: OLE DB or ODBC error: Query timeout expired HY008". There are 410 million rows in one base table and 80 million rows in another base table. The other measure groups process fine. It has abended at 61 minutes the last 3 times. I can individually process each measure group successfully.

Any help is greatly appreciated, Edwina Derrick

Simular problem for me. Have a huge database to be queried and can't pass the 60 minutes query time despite having made sure that ALL!! timeout values are suposed to be eternal (zero in most cases). This seems like a bug to me. I used the developer release version of the SQL2005. Would appriciate the Microsoft development team to test this problem and to suply information of when and how to bypass this 1 hour timeout limitation.

|||Try increasing ExternalCommandTimeout Analysis Server advanced property.

Being in situation when Analysis Server needs to wait for an hour for some data to come from SQL Server is very unusual. Such situation ususally means that SQL server needs to perform some heavy operation before it can start sending data out. Try optimizing your SQL Server database.
Edward Melomed (MSFT)

---

This posting is provided "AS IS" with no warranties, and confers no rights.|||

WTF....

isn't there a way to set the timeout?

If I was able to "Optimize my SQL Server database".....

I have five fact tables (All of them are required) and each table has it's own Dimensions.

since I am unable to create a cube on a left join relationship I need to work with views ("SQL server needs to perform some heavy operation before it can start sending data out").

OLE DB timeout processing AS Cube

When doing a full process of the Analysis Services Cube with several partitioned measure groups. We receive: "OLE DB error: OLE DB or ODBC error: Query timeout expired HYT00" or "OLE DB error: OLE DB or ODBC error: Query timeout expired HY008". There are 410 million rows in one base table and 80 million rows in another base table. The other measure groups process fine. It has abended at 61 minutes the last 3 times. I can individually process each measure group successfully.

Any help is greatly appreciated, Edwina Derrick

Simular problem for me. Have a huge database to be queried and can't pass the 60 minutes query time despite having made sure that ALL!! timeout values are suposed to be eternal (zero in most cases). This seems like a bug to me. I used the developer release version of the SQL2005. Would appriciate the Microsoft development team to test this problem and to suply information of when and how to bypass this 1 hour timeout limitation.

|||Try increasing ExternalCommandTimeout Analysis Server advanced property.

Being in situation when Analysis Server needs to wait for an hour for some data to come from SQL Server is very unusual. Such situation ususally means that SQL server needs to perform some heavy operation before it can start sending data out. Try optimizing your SQL Server database.
Edward Melomed (MSFT)

---

This posting is provided "AS IS" with no warranties, and confers no rights.|||

WTF....

isn't there a way to set the timeout?

If I was able to "Optimize my SQL Server database".....

I have five fact tables (All of them are required) and each table has it's own Dimensions.

since I am unable to create a cube on a left join relationship I need to work with views ("SQL server needs to perform some heavy operation before it can start sending data out").

OLE DB Source Editor

hi,

I am using SSIS to extract data from sql server and import into MDB file. In
the process, under data flow task, I have used OLE DB Source Editor as source. Here
i have choosen SQL Command as mode of data population. In the box below i
have typed the following statements.

"Exec Site_Address"

I have used many temperory tables in this procedure.
When i run this procedure in the query analyzer window i get the desired data which has to be imported to an MDB. After typing the above statements and when i
click the button preview i can see the data. But when i click the
Columns.... i dont see anything there. I am unable to see any columns there.
This is getting to my nerves because, when i use OLE DB as Destination i am
unable to map the columns and i get an error.

I dont know how to solve this problem. cannot we map columns in temp tables .... or wat is it ?

Please help me to find a solution.

I will also paste the procedure code that i have used.

Create procedure Site_Address

as

begin

create table #Data_For_Site_Address_Table

(

unitid varchar(20),

city varchar(50),

cust_num varchar(40),

zip varchar(20),

CountryID varchar(20),

CreatedBy varchar(20)

)

-- tblcrdsiteaddress

insert into #Data_For_Site_Address_Table

select distinct * from

(select

(select top 1 fsu.ser_num

from fs_unit fsu

where ca.cust_seq <> 0 and fsu.cust_num = ca.cust_num

order by ca.city desc) as UnitID,ca.city,ca.cust_num,ca.zip,

CASE

WHEN ca.country like 'Luxembourg' THEN 'LU'

WHEN ca.country like 'Deutschland' THEN 'DE'

WHEN ca.country like 'Austria' THEN 'AT'

WHEN ca.country like 'Czech Republic' THEN 'CZ'

WHEN ca.country like 'Denmark' THEN 'DK'

WHEN ca.country like 'CHINA' THEN 'CN'

WHEN ca.country like 'CROATIA' THEN 'HR'

WHEN ca.country like 'Egypt' THEN 'EG'

WHEN ca.country like 'Germany' THEN 'DE'

WHEN ca.country like 'Hungary' THEN 'HU'

WHEN ca.country like 'Jordan' THEN 'JO'

WHEN ca.country like 'Korea, Republic Of' THEN 'KR'

WHEN ca.country like 'Poland' THEN 'PL'

WHEN ca.country like 'Switzerland' THEN 'CH'

WHEN ca.country like 'United Kingdom' THEN 'GB'

ELSE '- N/A -' END AS CountryID, CA.CreatedBy

from custaddr ca

) al

where unitid is not null

Select TT.Unitid as Short_Site_Name, TT.City as Site_Name,'N.A' as Street_Po_Box,TT.Zip as Postal_Code_City, Null as State_Region,

TT.CountryID as CountryID,Null as Zone, Null as Note, TT.CreatedBy as UserID, GetDate() as Date, 'A' as [Action]

From #Data_For_Site_Address_Table TT

END

Thanks.

Rgds,
Meher Krishna.V

I just did a quick search in the forum and found a couple threads dealing with SP being used inside of an OLE DB Source:

http://forums.microsoft.com/MSDN/Search/Search.aspx?words=ole+db+source+procedure&localechoice=9&SiteID=1&searchscope=forumscope&ForumID=80

This, particularly, seems to talk about same issue:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=161617&SiteID=1

I hope it helps

|||

Hopefully this should explain the problem:

Using stored procedures inside an OLE DB Source component
(http://blogs.conchango.com/jamiethomson/archive/2006/12/20/SSIS_3A00_-Using-stored-procedures-inside-an-OLE-DB-Source-component.aspx)

-Jamie

|||

I knew I have read about a solution somewhere; I just could not remember the exact place.

Good stuff Jamie!

|||

Rafael Salas wrote:

I knew I have read about a solution somewhere; I just could not remember the exact place.

Good stuff Jamie!

Well it wasn't at the above link cos I only wrote it about an hour ago

-Jamie

|||So, probably I dreamed about it. |||

thanks Jaime... that was great.

The problem is solved when i used a function......

But there should be a way how to use a sproc right ? In another posts that you had written, saying SET FMTONLY can solve the issue. i tried using it, but it was futile.

Any clue how to use it ?

|||

meher666 wrote:

thanks Jaime... that was great.

The problem is solved when i used a function......

But there should be a way how to use a sproc right ? In another posts that you had written, saying SET FMTONLY can solve the issue. i tried using it, but it was futile.

Any clue how to use it ?

No, in some circumstances I just don't think it can be done. Where did I suggest SET FMTONLY?

-Jamie

|||

okieee.... i am sorry again.... i thought it was you suggested using that statement. It was somebody else.

Thanks for your help.

Ole Automation procedures. DB scope?

I have been looking at the OLE automation procs (ex. sp_OACreate, etc. ) and
am wondering about their scope. Does the OLE process have scope on the
executing transaction? Or must the OLE object establish its own database
connection? For example, I have an example using 'SQLDMO.SQLServer' and it
establishes its own database connection, so some of its parameters are
instance, login name, and PW.
We have several db instances and databases and we move procs around quite
often and I don't want to have to worry about passing db connection info.
Thanks,
MichaelHi
there is no scope in SQL Server 2000. In SQL Server 2005, CLR objects have a
scope.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Snake" wrote:

> I have been looking at the OLE automation procs (ex. sp_OACreate, etc. ) a
nd
> am wondering about their scope. Does the OLE process have scope on the
> executing transaction? Or must the OLE object establish its own database
> connection? For example, I have an example using 'SQLDMO.SQLServer' and i
t
> establishes its own database connection, so some of its parameters are
> instance, login name, and PW.
> We have several db instances and databases and we move procs around quite
> often and I don't want to have to worry about passing db connection info.
> Thanks,
> Michael|||Mike,
I am not sure if you answered my questions in a way that I can understand.
Can you give me a little more detail?
Thanks,
Michael
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> there is no scope in SQL Server 2000. In SQL Server 2005, CLR objects have
a
> scope.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "Snake" wrote:
>