ActiveX VBScript problem

Below is a code snippet that is throwing the following error:

Error Source: Microsoft DTS Package

Error Description: Error Code: 0
Error Source= Microsoft VBScript runtime error
Error Description: Object required: 'Server'

Error on line 13

In my code line 13 is the following:
Set Cnxn = Server.CreateObject("ADODB.connection")

I did set up a Microsoft OLE DB connection in my DTS package for this
ActiveX Task. I'm new to VBScript and ActiveX. Any help would be
appreciated.

Thanks,

-p

'************************************************* *********************
' Visual Basic ActiveX Script
'************************************************* ***********************

Function Main()
Main = DTSTaskExecResult_Success

' connection, command and recordset variables
Dim Cnxn, strCnxn

' create and open connection
Set Cnxn = Server.CreateObject("ADODB.connection")
strCnxn = "data source=Pluto;initial catalog=Stats;User Id=sa;password=;"
Cnxn.Open strCnxn

End FunctionThe Server object is only available when running scripts under IIS. You
don't need it when running scripts via DTS or stand-alone. Try:

Set Cnxn = CreateObject("ADODB.Connection")

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Pippen" <123@.hotmail.com> wrote in message
news:ESCWc.223077$eM2.87297@.attbi_s51...
> Below is a code snippet that is throwing the following error:
> Error Source: Microsoft DTS Package
> Error Description: Error Code: 0
> Error Source= Microsoft VBScript runtime error
> Error Description: Object required: 'Server'
> Error on line 13
> In my code line 13 is the following:
> Set Cnxn = Server.CreateObject("ADODB.connection")
> I did set up a Microsoft OLE DB connection in my DTS package for this
> ActiveX Task. I'm new to VBScript and ActiveX. Any help would be
> appreciated.
> Thanks,
> -p
> '************************************************* *********************
> ' Visual Basic ActiveX Script
> '************************************************* ***********************
> Function Main()
> Main = DTSTaskExecResult_Success
> ' connection, command and recordset variables
> Dim Cnxn, strCnxn
> ' create and open connection
> Set Cnxn = Server.CreateObject("ADODB.connection")
> strCnxn = "data source=Pluto;initial catalog=Stats;User Id=sa;password=;"
> Cnxn.Open strCnxn
> End Function|||"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:NNGWc.4935$N04.1043@.newssvr23.news.prodigy.co m...
> The Server object is only available when running scripts under IIS. You
> don't need it when running scripts via DTS or stand-alone. Try:
> Set Cnxn = CreateObject("ADODB.Connection")
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Pippen" <123@.hotmail.com> wrote in message
> news:ESCWc.223077$eM2.87297@.attbi_s51...
> > Below is a code snippet that is throwing the following error:
> > Error Source: Microsoft DTS Package
> > Error Description: Error Code: 0
> > Error Source= Microsoft VBScript runtime error
> > Error Description: Object required: 'Server'
> > Error on line 13
> > In my code line 13 is the following:
> > Set Cnxn = Server.CreateObject("ADODB.connection")
> > I did set up a Microsoft OLE DB connection in my DTS package for this
> > ActiveX Task. I'm new to VBScript and ActiveX. Any help would be
> > appreciated.
> > Thanks,
> > -p
> > '************************************************* *********************
> > ' Visual Basic ActiveX Script
'************************************************* ***********************
> > Function Main()
> > Main = DTSTaskExecResult_Success
> > ' connection, command and recordset variables
> > Dim Cnxn, strCnxn
> > ' create and open connection
> > Set Cnxn = Server.CreateObject("ADODB.connection")
> > strCnxn = "data source=Pluto;initial catalog=Stats;User
Id=sa;password=;"
> > Cnxn.Open strCnxn
> > End Function

That fixed it! Thanks for your help.

-p

ActiveX from SQL?

Hello,
I'm trying to use ActiveX from SQL to save an Excel file. I use the
following code:
Set xlApp = CreateObject("Excel.Application") Set xlWb = xlApp.Workbooks.Open("C:\Report.xls")
' make some changes here to the file ...
xlWb.Save
Since the file already exist and I am making changes to it, the 'Save'
command will trigger a prompt and I have to answer 'yes' to the Excel
question 'File already exists. Do you want to replace this file?'
Does the Save command or another command accept a parameter so it will
overwrite the file without the prompt?
THANKS!!it does not overwrite the file without the prompt.
Because It occur security web browser.
cheers...
"Dean J Garrett" wrote:
> Hello,
>
> I'm trying to use ActiveX from SQL to save an Excel file. I use the
> following code:
> Set xlApp = CreateObject("Excel.Application") Set xlWb => xlApp.Workbooks.Open("C:\Report.xls")
> ' make some changes here to the file ...
> xlWb.Save
> Since the file already exist and I am making changes to it, the 'Save'
> command will trigger a prompt and I have to answer 'yes' to the Excel
> question 'File already exists. Do you want to replace this file?'
> Does the Save command or another command accept a parameter so it will
> overwrite the file without the prompt?
>
> THANKS!!
>
>|||Yes, we understand why it is doing it, but the question is: how can we get
around this problem. We just wanted to automatically over-write the file.
Any clues?
"jongwoo" <jongwoo@.discussions.microsoft.com> wrote in message
news:EFE9CE78-0B81-4A79-8669-F6B6B605F7B1@.microsoft.com...
> it does not overwrite the file without the prompt.
> Because It occur security web browser.
> cheers...
> "Dean J Garrett" wrote:
>> Hello,
>>
>> I'm trying to use ActiveX from SQL to save an Excel file. I use the
>> following code:
>> Set xlApp = CreateObject("Excel.Application") Set xlWb =>> xlApp.Workbooks.Open("C:\Report.xls")
>> ' make some changes here to the file ...
>> xlWb.Save
>> Since the file already exist and I am making changes to it, the 'Save'
>> command will trigger a prompt and I have to answer 'yes' to the Excel
>> question 'File already exists. Do you want to replace this file?'
>> Does the Save command or another command accept a parameter so it will
>> overwrite the file without the prompt?
>>
>> THANKS!!
>>

active/active sql server cluster

Hello, Can anyone please explain how the failover processes works on a sql server 2000 2 node active/active cluster.

Given the following
You have 2 node active/active cluster NodeA & NodeB

Question
1. How many SQL instances need to be installed on each node?

2. If the answer to question #1 is one instance per node, then
say if NodeA fails NodeB will take over all the resources of NodeA
including the master database, How does that work , how can once instance (in nodeB) handle two master database i.e its own master database and the one taken over from NodeA.Check this out,

http://www.sql-server-performance.com/clustering_intro1.asp

Active/Active Cluster.

Hi everyone,
We have the following setup:
Windows 2003 Server Enterprise edition Cluster with 2 nodes. SQL Server 2000
Enterprise edition Virtual Server1 - currently Active/Passive Cluster.
Shared storage and Quorum drive on SAN.
Database1 on Virtual Server1.
The goal is to set up a reports Database wich will server as a mirror(but
not exact mirror, because it will keep archive data as well) for Database1.
Currently we consider the following proposition:
We want to set up another database(Database2). Our Application will be
writing the same data to both Database1 and Database2(not necessarily at the
same time).
As data gets out of date it will be removed from Database1(but not from
Database2). Database2 is intended to be used for reports(intensive queries).
Would it be a good solution to set up an Active/Active Cluster(another
Virtual Server with the Active/Passive nodes swapped).
If yes, could you provide detailed information(or references/URLs to it)
regarding the implementation - what needs to be done and what are the steps
to follow.
If no, could you propose alternative solutions.
Best Regards.
Sezgin Rafed
I am not sure I have even to go on, no memory requirements, usage hours,
etc. You can indeed create another instance and run it on the current node
or the second node. You can also add a 3 node since you are running on
Windows Server 2003.
With that said, unless unavoidable, I like to stay away running instances on
each node. I like to have a spare at all times.
The setup steps are the same as the default node, except you have to name
the instance.
Cheers,
Rod
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering
http://www.msmvps.com/clustering - Blog
"Sezgin Rafed" <anonymous@.anonymous.com> wrote in message
news:%23ia3zHl6EHA.1204@.TK2MSFTNGP10.phx.gbl...
> Hi everyone,
> We have the following setup:
> Windows 2003 Server Enterprise edition Cluster with 2 nodes. SQL Server
> 2000
> Enterprise edition Virtual Server1 - currently Active/Passive Cluster.
> Shared storage and Quorum drive on SAN.
> Database1 on Virtual Server1.
> The goal is to set up a reports Database wich will server as a mirror(but
> not exact mirror, because it will keep archive data as well) for
> Database1.
> Currently we consider the following proposition:
> We want to set up another database(Database2). Our Application will be
> writing the same data to both Database1 and Database2(not necessarily at
> the
> same time).
> As data gets out of date it will be removed from Database1(but not from
> Database2). Database2 is intended to be used for reports(intensive
> queries).
> Would it be a good solution to set up an Active/Active Cluster(another
> Virtual Server with the Active/Passive nodes swapped).
> If yes, could you provide detailed information(or references/URLs to it)
> regarding the implementation - what needs to be done and what are the
> steps
> to follow.
> If no, could you propose alternative solutions.
> Best Regards.
> Sezgin Rafed
>
sql

ACTIVE X SCRIPT ERROR

I have the following code in an ActiveX Script task for SSIS 2005 package... This worked about a year ago when it was first developed. I am coming in to try to update some things and i am running into this problem

Function Main()

Dim crDocument

Dim crApp

Dim sCRConnectionString

Dim crTable

strServer = DTSGlobalVariables("DBServer")

strDatabase = DTSGlobalVariables("DBName")

strCR = DTSGlobalVariables("CrystalReportPath")

strOutput = DTSGlobalVariables("OutputPath")

sFileOutputPDF = strOutput & "EventLogReport.pdf"

strReportLocation = strCR & "LoggedEvents.rpt"

Set crApp = CreateObject("CrystalRuntime.Application")

Set crDocument = crApp.OpenReport(strReportLocation )

sCRConnectionString = "Provider=SQLOLEDB;server=" & strServer & ";database=" & strDatabase & ";Integrated Security=True"

crDocument.DisplayProgressDialog = False

crDocument.ExportOptions.DestinationType = 1

crDocument.ExportOptions.DiskFileName = sFileOutputPDF

crDocument.ExportOptions.FormatType = 31

MSGBOX("EXPORTING..")

crDocument.Export(False)

MSGBOX("EXPORTING FINISHED!!")

Main = DTSTaskExecResult_Success

End Function

It makes it to the "Exporting.." msg box and then i get the following error

Error: 0xC0048006 at ActiveX Script Task, ActiveX Script Task: Retrieving the file name for a component failed with error code 0x0173DA5C.

At first I thought it may have been an issue with the crystal report having a popup or something come up while this was executing.. but I was able to open the crystal and export the data to a pdf no problem... Any suggestions?

Thanks in Advance!!!

Just an FYI - and not what you want to hear likely, but you should spend your time to convert this to an SSIS Script Task, written in VB.Net. ActiveX is a depricated feature of SSIS.

http://technet.microsoft.com/en-us/library/ms137525.aspx|||

how do i convert

Dim crDocument

Dim crApp

Set crApp = CreateObject("CrystalRuntime.Application")

Set crDocument = crApp.OpenReport(strReportLocation )

into VB.Net for the SSIS Script task... do i also need to import anything? when i right clicked references in the project explore window of the script task editor, i did not see any references that related to crystal?

Thanks in advance

Active Directory Domain Local Groups

Hi,
I have the following problem with AD an RS: when creating a group of users
as a "domain local group" in AD, i'm able to give a specific item-level role
in RS to members of this group. But it looks like rights are not effective
for those users. For example if the group is defined as "reader" on a folder,
group members can't see it in RS!
Everything works fine when the created group is a "global group" in AD.
Thanx in advance.
Greg.I still need help.
Thanx in advance.
"Greg - NEOS" wrote:
> Hi,
> I have the following problem with AD an RS: when creating a group of users
> as a "domain local group" in AD, i'm able to give a specific item-level role
> in RS to members of this group. But it looks like rights are not effective
> for those users. For example if the group is defined as "reader" on a folder,
> group members can't see it in RS!
> Everything works fine when the created group is a "global group" in AD.
> Thanx in advance.
> Greg.

Active connections

Does anybody know an easy way to check how many connections are being made to the database? I'm getting the following error

An unhandled exception of type 'System.InvalidOperationException' occurred in system.data.dll
Additional information: A connection pooling error has occurred.

and find it hard to believe that there are no connections left in the pool.

I've checked in Enterprise Manager and the maxmimum number of process running under the user account used to access the DB from code is 2. Strangely the error seems to occur when I explicitly close the connection.

thanks
SimonHi Simon,

You could run sp_monitor from Query Anylizer to find out.

EXEC sp_monitor

It will tell you how many connections since the last start and how many are present.

:)

JB|||Make sure you're closing all of you connections on all of your pages. Here are a few perf counters to look at:
\\bob\.NET CLR Data(_global_)\SqlClient: Current # connection pools
\\bob\.NET CLR Data(_global_)\SqlClient: Current # pooled and nonpooled connections
\\bob\.NET CLR Data(_global_)\SqlClient: Current # pooled connections|||OK, looks like something weird to me.

When I run in debug mode my number of pooled connections:

1. Goes up by 4 when clean starting ... i.e. Application_Start fires.
2. Goes up by 2 when normal start.
3. Does not go up when navigating from page to page.

When I run in 'normal' mode the number of pooled connections:

1. Goes up by 2 when clean starting ... i.e. Application_Start fires.
2. Does not go up when normal start.
3. Does not go up when navigating from page to page.

It never seems to decrease though.

What is going on??

Simon|||pooled connections will not be terminated from the database server's perspective until the connection inactivity timeout is reached on the pooled connections.

So under normal circumstances, you wont see the number of connections decrease unless your app sits still for a while.

Activating Trial Edition of SQL Server 2005

I have the following situation.
I installed a couple of months ago SQL Server 2005 Trial Edition which is En
teprise Edition I guess.
Currently I am testing replication on our company database.
Now. In a few months the Trial Version will expire.
Can I avoid setting up my Merge Replication again and re-installing everythi
ng from scratch?
Is there a way to activate NOT YET expired trial version ?
More to that I prefer to purchase the Standard Ed. which is less expensive t
han Enterprise.
Can I convert somehow the Trial Enteprise ed. into Standard WITHOUT having t
o re-install everything and possibly losing oall my settings.Issue a select @.@.version to determine what you are running. I think your
best bet is to script our your publications and subscribers, and then to
detacth the databases. Remove the trial version and install standard noting
that web synchronization and other features of merge replication present
only in EE will not be available.
Then run the publication and subscription scripts on the new server.
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
"Yuri" <Yuri@.tester.de> wrote in message
news:5o-dnaz5Doik2dvYnZ2dnUVZ8qKdnZ2d@.giganews.com...
>I have the following situation.
> I installed a couple of months ago SQL Server 2005 Trial Edition which is
> Enteprise Edition I guess.
> Currently I am testing replication on our company database.
> Now. In a few months the Trial Version will expire.
>
> Can I avoid setting up my Merge Replication again and re-installing
> everything from scratch?
> Is there a way to activate NOT YET expired trial version ?
> More to that I prefer to purchase the Standard Ed. which is less expensive
> than Enterprise.
> Can I convert somehow the Trial Enteprise ed. into Standard WITHOUT having
> to re-install everything and possibly losing oall my settings.|||Hi Hillary,
thanks for your piece of advice.
This is actually a workaround solution.
Your are right, installing the Standard ed. over Trial Version isn't probabl
y the best idea.
But I guess you are mistaken saying that SE has no Web Merge Replication fea
utures
Look at this table: http://www.microsoft.com/sql/prodin...
es.mspx
Yuri

Activating Trial Edition of SQL Server 2005

I have the following situation.
I installed a couple of months ago SQL Server 2005 Trial Edition which is Enteprise Edition I guess.
Currently I am testing replication on our company database.
Now. In a few months the Trial Version will expire.
Can I avoid setting up my Merge Replication again and re-installing everything from scratch?
Is there a way to activate NOT YET expired trial version ?
More to that I prefer to purchase the Standard Ed. which is less expensive than Enterprise.
Can I convert somehow the Trial Enteprise ed. into Standard WITHOUT having to re-install everything and possibly losing oall my settings.Issue a select @.@.version to determine what you are running. I think your
best bet is to script our your publications and subscribers, and then to
detacth the databases. Remove the trial version and install standard noting
that web synchronization and other features of merge replication present
only in EE will not be available.
Then run the publication and subscription scripts on the new server.
--
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
"Yuri" <Yuri@.tester.de> wrote in message
news:5o-dnaz5Doik2dvYnZ2dnUVZ8qKdnZ2d@.giganews.com...
>I have the following situation.
> I installed a couple of months ago SQL Server 2005 Trial Edition which is
> Enteprise Edition I guess.
> Currently I am testing replication on our company database.
> Now. In a few months the Trial Version will expire.
>
> Can I avoid setting up my Merge Replication again and re-installing
> everything from scratch?
> Is there a way to activate NOT YET expired trial version ?
> More to that I prefer to purchase the Standard Ed. which is less expensive
> than Enterprise.
> Can I convert somehow the Trial Enteprise ed. into Standard WITHOUT having
> to re-install everything and possibly losing oall my settings.

Activating the Queue

Hi,

I have created Queue with the following syntax. But it is not getting activated itself. What I have to do to get it activated itself, and what could be the frequency by default.

CREATE QUEUE NewCustomerQueue
WITH ACTIVATION
(PROCEDURE_NAME = prProcessNewCustomers,
STATUS = ON,
MAX_QUEUE_READERS = 1,
EXECUTE AS SELF)
GO

If I execute the prProcessNewCustomers procedure manually it is showing that the Queue has been activated. What change I have to make in the syntax to get it activated itself.

Actually I have two scenarios in my requirement,

1. One Queue processing immediately when it receives data (Order Processing)

2. Another Queue, Process when the server is idle i.e., off-peak time (for mailing)

What syntax I have to use for these.

Please help.

Thanks in advance

Babu

Activation can be turned on and off using ALTER QUEUE

ALTER QUEUE NewCustomerQueue WITH ACTIVATION (STATUS = ON|OFF);

So in your first scenario you can keep activation on always. In your second scenario, you can keep it off by default and turn it on whenever desired.

Activation launches the specified stored procedure in the background upon receipt of incoming messages. If the procedure cannot keep with the rate of incoming messages, it can launch concurrent instances of the stored procedure (you need to set max_queue_readers > 1 for that).

Hope that helps,

Rushi

|||

Hi Rushi,

Thanks for your reply.

I have created the Queue with the ACTIVATION (STATUS = ON) only.

Currently I'm trying with a typical example what I got from some books. When a new customer is added, the trigger in the Customer table will "BEGIN DIALOG CONVERSATION" and send a message. I think when a message is sent, the Queue should get activated. It is not happening in this case. What could be the reason.

Please help.

Regards

Babu

|||

Hi Babu:

First make sure the trigger runs by checking in query analyser. Check the sql profiler to see whats the errror your getting.

Pramod

|||

Do you see the message in the target queue?

select * from [target queue]

(where [target queue] is the name of your target queue)

Rushi

|||

Hi Rushi, Pramod,

Thank you both for the responses.

The problem was occured because the 'Idle CPU Condition' was not defined.

I'll come back with new doubts . Thank you once again.

Regards

Babu

Activated Proc not firing.

Hi There

I just had the following scenario.

I checked sys.service_queues to confirm the my queue has an activated proc assigned to it and is activation_enabled.

I send messages to the service, however i see that the messages just sit in the queue. I run profiler with all SB and Error events there is nothing, i check the sql server log, there is also nothing.

So i have no idea why the activated proc is not firing, all i see in the profiler trace is that the messages are acknowledged but the activated proc does not execute.

For a while i was stumped. i then tried to execute the proc myself and i got a syntax error.

Basically what happened was that i altered a table that the proc used and now the proc cannot execute cos there are more columns that must be inserted so i altered the proc.

However the activated proc still did not fire.

I had to disable and re-enable activation for it to work.

What i am wondering is why was there no indication in profiler or the sql og as the the fact that something was wrong with the activated proc ? Surely the activated proc should have fired and given the same error i got either to the sql log or profiler ?

And why did i have to re-enable activation ? Does sql cache the proc when you activate it and not know that the proc has changed when you alter it ?

Thanx

I had a similar issue...and I am trying to get a resolution on this. In my scenario, it was a new service broker startup (complete rebuild of queues from build scripts that were working in our development database environment)...We have about 30 queues in which all but 1 worked fine. When I went to look to see what happened, the queue was up and activation was on and the messages were sitting in the target queue. When I manually executed the activation stored procedure everything was successful and processed fine (no errors). This is not the first time this has happened, and after it is manually executed the activation seems to occur without issue from that point forward. I am just kind of concerned that when this system is in production and they have to take it down to perform maintenance, this will happen again and since nothing is really "wrong" with the queue, monitoring this is fairly difficult.

Any ideas or suggestions?

|||

When you run into this problem, look at sys.dm_broker_queue_monitors, sys.dm_broker_activated_tasks and sys.service_queues views. We need the content of these views to diagnose the issue.

HTH,
~ Remus

|||

Hi Remus

Ok next time i will get that information.

Thanx

Activated Proc not firing.

Hi There

I just had the following scenario.

I checked sys.service_queues to confirm the my queue has an activated proc assigned to it and is activation_enabled.

I send messages to the service, however i see that the messages just sit in the queue. I run profiler with all SB and Error events there is nothing, i check the sql server log, there is also nothing.

So i have no idea why the activated proc is not firing, all i see in the profiler trace is that the messages are acknowledged but the activated proc does not execute.

For a while i was stumped. i then tried to execute the proc myself and i got a syntax error.

Basically what happened was that i altered a table that the proc used and now the proc cannot execute cos there are more columns that must be inserted so i altered the proc.

However the activated proc still did not fire.

I had to disable and re-enable activation for it to work.

What i am wondering is why was there no indication in profiler or the sql og as the the fact that something was wrong with the activated proc ? Surely the activated proc should have fired and given the same error i got either to the sql log or profiler ?

And why did i have to re-enable activation ? Does sql cache the proc when you activate it and not know that the proc has changed when you alter it ?

Thanx

I had a similar issue...and I am trying to get a resolution on this. In my scenario, it was a new service broker startup (complete rebuild of queues from build scripts that were working in our development database environment)...We have about 30 queues in which all but 1 worked fine. When I went to look to see what happened, the queue was up and activation was on and the messages were sitting in the target queue. When I manually executed the activation stored procedure everything was successful and processed fine (no errors). This is not the first time this has happened, and after it is manually executed the activation seems to occur without issue from that point forward. I am just kind of concerned that when this system is in production and they have to take it down to perform maintenance, this will happen again and since nothing is really "wrong" with the queue, monitoring this is fairly difficult.

Any ideas or suggestions?

|||

When you run into this problem, look at sys.dm_broker_queue_monitors, sys.dm_broker_activated_tasks and sys.service_queues views. We need the content of these views to diagnose the issue.

HTH,
~ Remus

|||

Hi Remus

Ok next time i will get that information.

Thanx

act/pass cluster upg from geerhead...

following up on the geerhead message:
first, uncluster the SQL then reboot node1
second, upg the sql 7 to sql 2000
third, upg the sql 2k to sql 2000 cluster instance...
OK...are there any upgrades to node2? Or will thw sql 2k
cluster instance take care of that?
The upgrade from a standalone to a clustered instance will apply the RTM
version of the SQL Server files to any nodes listed as possible owners. So
you have to then reapply the latest service pack and any hotfixes (see the
readme for sp3a).
Note that you can only upgrade from standalone to clustered if the binaries
are on the local drives and the data is all on one or more shared drives.
Cindy Gross, MCDBA, MCSE
http://cindygross.tripod.com
This posting is provided "AS IS" with no warranties, and confers no rights.
|||So, if I have all the programs and data and the quorum on
a shared drive, this should be ok?

>--Original Message--
>The upgrade from a standalone to a clustered instance
will apply the RTM
>version of the SQL Server files to any nodes listed as
possible owners. So
>you have to then reapply the latest service pack and any
hotfixes (see the
>readme for sp3a).
>Note that you can only upgrade from standalone to
clustered if the binaries
>are on the local drives and the data is all on one or
more shared drives.
>Cindy Gross, MCDBA, MCSE
>http://cindygross.tripod.com
>This posting is provided "AS IS" with no warranties, and
confers no rights.
>.
>
|||No and No. First, SQL should not share a physical disk resource with teh
quorum drive. This defeats the entire high availability strategy of
clustering. Second, SQL operates on local binaries and shared data.
Binaries (Programs) need to be on local disks. The installer will handle
installing/updating all cluster nodes at once.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"geerhead" <anonymous@.discussions.microsoft.com> wrote in message
news:607701c42e09$ac9bede0$a601280a@.phx.gbl...[vbcol=seagreen]
> So, if I have all the programs and data and the quorum on
> a shared drive, this should be ok?
>
>
> will apply the RTM
> possible owners. So
> hotfixes (see the
> clustered if the binaries
> more shared drives.
> confers no rights.
|||The binaries are separated. You can put data on the same drive as the
quorum. It isn't recommended, but it can be done without any side effects
to the cluster.
Mike
Principal Mentor
Solid Quality Learning
"More than just Training"
SQL Server MVP
http://www.solidqualitylearning.com
http://www.mssqlserver.com

AcquireConnection returns null value

I have the following code in my custom source component's AcquireConnection method -

if (ComponentMetaData.RuntimeConnectionCollection[0].ConnectionManager != null)

{

ConnectionManager cm = Microsoft.SqlServer.Dts.Runtime.DtsConvert.ToConnectionManager(ComponentMetaData.RuntimeConnectionCollection[0].ConnectionManager);

ConnectionManagerAdoNet cmado = cm.InnerObject as ConnectionManagerAdoNet;

if (cmado == null)

throw new Exception("The ConnectionManager " + cm.Name + " is not an ADO.NET connection.");

// Get the underlying connection object.
this.oledbConnection = cmado.AcquireConnection(transaction) as OleDbConnection;

if (oledbConnection == null)

throw new Exception("The ConnectionManager is not an ADO.NET connection.");

isConnected = true;

}

The value of oledbConnection is null.

I am trying to invoke the above method within a package that I am trying to create dynamically. Any help ?

private const string ADONETConnectionString = "Provider=SQLOLEDB.1;Data Source={0};Initial Catalog={1};Integrated Security=True;";

ConnectionManager PubTRCM = package.Connections.Add("ADO.NET:OLEDB");

PubTRCM.Name = "PubTR";

PubTRCM.ConnectionString = String.Format(ADONETConnectionString, paramPubServer, paramPubTRDB);

#region Create OLEDB Source Component

//Create a OLEDB Source Component
IDTSComponentMetaData90 Source = dataFlow.ComponentMetaDataCollection.New();
Source.Name = "OLEDBSource";

Source.ComponentClassID = typeof(Microsoft.Samples.SqlServer.Dts.AdoSourceSample).AssemblyQualifiedName;

//Get the design time instance of the source
CManagedComponentWrapper SourceDesignTime = Source.Instantiate();

//Initialize the component
SourceDesignTime.ProvideComponentProperties();

if (Source.RuntimeConnectionCollection.Count > 0)
{
Source.RuntimeConnectionCollection[0].ConnectionManagerID = PubTRCM.ID;
Source.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(PubTRCM);
}

SourceDesignTime.SetComponentProperty("SqlStatement", "Select PlantId from PlantDim");

//Reinitialize the metadata
SourceDesignTime.AcquireConnections(null);
SourceDesignTime.ReinitializeMetaData();
SourceDesignTime.ReleaseConnections();

#endregion

So what stage does the problem start? Is cmado an object? If so what type? What is the result from cmado.AcquireConnection, never mind the cast?

Have you tried debugging your component whilst it is being called like this?

Does the component work in the designer, I assume not.

|||

I could resolve this problem by changing the connection string from -

"Provider=SQLOLEDB.1;Data Source={0};Initial Catalog={1};Integrated Security=True;";

to

"Provider=SQLOLEDB.1;Data Source={0};Initial Catalog={1};Integrated Security=SSPI;";

and adding the connection manager to the package as follows -

ConnectionManager CMgr = package.Connections.Add("ADO.NETystem.Data.OleDb.OleDbConnection, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089");

Thanks,

Reni

AcquireConnection returns null value

I have the following code in my custom source component's AcquireConnection method -

if (ComponentMetaData.RuntimeConnectionCollection[0].ConnectionManager != null)

{

ConnectionManager cm = Microsoft.SqlServer.Dts.Runtime.DtsConvert.ToConnectionManager(ComponentMetaData.RuntimeConnectionCollection[0].ConnectionManager);

ConnectionManagerAdoNet cmado = cm.InnerObject as ConnectionManagerAdoNet;

if (cmado == null)

throw new Exception("The ConnectionManager " + cm.Name + " is not an ADO.NET connection.");

// Get the underlying connection object.
this.oledbConnection = cmado.AcquireConnection(transaction) as OleDbConnection;

if (oledbConnection == null)

throw new Exception("The ConnectionManager is not an ADO.NET connection.");

isConnected = true;

}

The value of oledbConnection is null.

I am trying to invoke the above method within a package that I am trying to create dynamically. Any help ?

private const string ADONETConnectionString = "Provider=SQLOLEDB.1;Data Source={0};Initial Catalog={1};Integrated Security=True;";

ConnectionManager PubTRCM = package.Connections.Add("ADO.NET:OLEDB");

PubTRCM.Name = "PubTR";

PubTRCM.ConnectionString = String.Format(ADONETConnectionString, paramPubServer, paramPubTRDB);

#region Create OLEDB Source Component

//Create a OLEDB Source Component
IDTSComponentMetaData90 Source = dataFlow.ComponentMetaDataCollection.New();
Source.Name = "OLEDBSource";

Source.ComponentClassID = typeof(Microsoft.Samples.SqlServer.Dts.AdoSourceSample).AssemblyQualifiedName;

//Get the design time instance of the source
CManagedComponentWrapper SourceDesignTime = Source.Instantiate();

//Initialize the component
SourceDesignTime.ProvideComponentProperties();

if (Source.RuntimeConnectionCollection.Count > 0)
{
Source.RuntimeConnectionCollection[0].ConnectionManagerID = PubTRCM.ID;
Source.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(PubTRCM);
}

SourceDesignTime.SetComponentProperty("SqlStatement", "Select PlantId from PlantDim");

//Reinitialize the metadata
SourceDesignTime.AcquireConnections(null);
SourceDesignTime.ReinitializeMetaData();
SourceDesignTime.ReleaseConnections();

#endregion

So what stage does the problem start? Is cmado an object? If so what type? What is the result from cmado.AcquireConnection, never mind the cast?

Have you tried debugging your component whilst it is being called like this?

Does the component work in the designer, I assume not.

|||

I could resolve this problem by changing the connection string from -

"Provider=SQLOLEDB.1;Data Source={0};Initial Catalog={1};Integrated Security=True;";

to

"Provider=SQLOLEDB.1;Data Source={0};Initial Catalog={1};Integrated Security=SSPI;";

and adding the connection manager to the package as follows -

ConnectionManager CMgr = package.Connections.Add("ADO.NETystem.Data.OleDb.OleDbConnection, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089");

Thanks,

Reni

AcquireConnection Error - Lookup Component

Hi,

Trying to get a particular SSIS is package working - while running the Package Installation wizard a package is reported with the following errors:

AcquireConnection method fails with error code 0xC0202009 Then Lookup fails validation and returns code 0xC020801C

The odd thing is the Lookup does not exist in the package, it did exist at one point but was deleted.

I have tried searching the XML and can't find any reference to the Lookup

Any suggestions appreciated...

Thanks,

John

Could you check if you are not somehow referencing the old package?|||

Any ideas how?

The steps I've tried are:

Delete all the packages in the server file system store

Check the local package for the Lookup (not found)

Rebuild the solution.

Double click the manifest to deploy on the server to a file store with the validate option turned on.

Validation warning above occurs.

If I open the package in notepad and search for the name of the lookup I don't find anything

|||

You may want to try a brute force here and search for all files with extension .dtsx on your machine. You could find some you do not know about (VS sometimes makes copies). If you do, see if any of them is the troublemaker.

HTH.

accumulation column

If my query returns the following result:

NAME1 10
NAME2 20
NAME3 15
NAME4 5
NAME5 25

is there any way (without using cursors) to add a column that adds the numbers so that I get the following result:

NAME1 10 10
NAME2 20 30
NAME3 15 45
NAME4 5 50
NAME5 25 75

? thanks in advanceyes, with a theta joinselect t1.name
, t1.num
, sum(t2.num) as accum
from yourtable as t1
inner
join yourtable as t2
on t1.name >= t2.name
group
by t1.name
, t1.num
order by t1.namenotice the inequality in the join condition

Accounting procedure

Hi,
I have the following procedure that I am trying to run on a data warehouse -
it is supposed to increase or decrease the running premium balance and the
fire fee balance based on the type of transaction, but it seems to just be
increasing the running balance - do you see where I am going wrong?
Thanks in advance
-- Local Variables
DECLARE
@.PolicyKeyID INT,
@.Policy_Number VARCHAR (10),
@.Trans_Type VARCHAR(40),
@.Billed_Premium DECIMAL(10,2),
@.Billed_Fire_Fee DECIMAL(10,2),
@.Pymt_Recvd DECIMAL(10,2),
@.SC_Recvd DECIMAL(10,2),
@.Transcode VARCHAR (4),
@.Portfolio_Set VARCHAR (4),
@.Type VARCHAR (25),
@.Typecode VARCHAR (4),
@.Previous_Policy_Number VARCHAR (10),
@.Running_Premium DECIMAL(10,2),
@.Running_Fire_Fee DECIMAL(10,2),
@.Error_Code_Tran INT,
@.Error_Code_Proc INT,
-- CONSTANTS declared for Transaction Types
@.NEW_BILL VARCHAR(15),
@.RENEWAL VARCHAR(20),
@.CANCELLATION VARCHAR(35),
@.CHANGE VARCHAR(15),
@.CASH_WITH_APP VARCHAR(25),
@.RETURNED_CHECK VARCHAR(35),
@.PAYMENT VARCHAR(35),
@.PYMT_REVERSAL VARCHAR(25),
@.DISBURSEMENT VARCHAR(25),
@.CANCEL_DISBURSEMENT VARCHAR(35)
-- Assign CONSTANTS
SET @.NEW_BILL = 'New'
SET @.RENEWAL = 'Renewal'
SET @.CANCELLATION = 'Cancellation'
SET @.CHANGE = 'Change'
SET @.CASH_WITH_APP = 'Cash With App'
SET @.RETURNED_CHECK = 'Returned Check'
SET @.PAYMENT = 'Payment'
SET @.PYMT_REVERSAL = 'Payment Reversal'
SET @.DISBURSEMENT = 'Disbursement'
SET @.CANCEL_DISBURSEMENT = 'Cancel Disbursement'
-- Set Error Codes
SET @.Error_Code_Tran = 0
SET @.Error_Code_Proc = 0
SET NOCOUNT ON
DECLARE crsrTransactions CURSOR LOCAL STATIC FOR
SELECT PolicyKeyID, Policy_Number, Portfolio_Set, Billed_Premium,
Billed_Fire_Fee, Pymt_Recvd, SC_Recvd,Trans_Type,Transcode,Type,Typec
ode
FROM Stage_Fire_Fee
ORDER BY Policy_Number, Policy_Date_Time ASC
OPEN crsrTransactions
FETCH NEXT FROM crsrTransactions INTO @.PolicyKeyID, @.Policy_Number,
@.Portfolio_Set, @.Billed_Premium, @.Billed_Fire_Fee, @.Pymt_Recvd,
@.SC_Recvd,@.Trans_Type,@.Transcode,@.Type,@.
Typecode
-- Outer Loop for all Transactions
WHILE @.@.FETCH_STATUS = 0
BEGIN
-- Initialize Running totals, reinitialize for each Policy #
SET @.Running_Premium = 0
SET @.Running_Fire_Fee = 0
SET @.Previous_Policy_Number = @.Policy_Number
IF @.Trans_Type IN
(@.NEW_BILL,@.RENEWAL,@.CANCELLATION,@.CHANG
E,@.RETURNED_CHECK,@.DISBURSEMENT,@.CAN
CEL_DISBURSEMENT,@.PYMT_REVERSAL)
-- Found basic Billed, so increment our running Premium and Fire Fee totals
BEGIN
SET @.Running_Premium = @.Running_Premium + @.Billed_Premium
SET @.Running_Fire_Fee = @.Running_Fire_Fee + @.Billed_Fire_Fee
END
ELSE IF @.Trans_Type = (@.PAYMENT)
-- Found Payment transaction, so subtract the paymnet from the total
amounts. Apply as much of the payment as possible to the Fire Fee Balance
BEGIN
SET @.Running_Premium = (@.Running_Fire_Fee + @.Running_Premium) -
@.Pymt_Recvd
IF @.Running_Fire_Fee <= @.Pymt_Recvd SET @.Running_Fire_Fee = 0
ELSE SET @.Running_Fire_Fee = @.Running_Fire_Fee - @.Pymt_Recvd
END
ELSE IF @.Trans_Type = (@.CASH_WITH_APP)
-- Found Payment transaction, so subtract the paymnet from the total
amounts. Apply as much of the payment as possible to the Fire Fee Balance
BEGIN
SET @.Running_Premium = (@.Running_Fire_Fee + @.Running_Premium) -
@.Pymt_Recvd
IF @.Running_Fire_Fee <= @.Pymt_Recvd SET @.Running_Fire_Fee = 0
ELSE SET @.Running_Fire_Fee = @.Running_Fire_Fee - @.Pymt_Recvd
END
ELSE
BEGIN
RAISERROR ('Unknown Transaction Type: %s', 0, 1, @.Trans_Type)
SET @.Error_Code_Tran = 1
SET @.Error_Code_Proc = 1
END
IF @.Error_Code_Tran = 0 UPDATE Stage_Fire_Fee SET Stage_Premium_Bal =
@.Running_Premium, Stage_Fire_Fee_Bal = @.Running_Fire_Fee WHERE PolicyKeyID =
@.PolicyKeyID
SET @.Previous_Policy_Number = @.Policy_Number
FETCH NEXT FROM crsrTransactions INTO @.PolicyKeyID, @.Policy_Number,
@.Portfolio_Set, @.Billed_Premium, @.Billed_Fire_Fee, @.Pymt_Recvd,
@.SC_Recvd,@.Trans_Type,@.Transcode,@.Type,@.
Typecode
SET @.Error_Code_Tran = 0
END
ENDOn Sun, 6 Nov 2005 16:44:36 -0800, Patrice wrote:

>Hi,
>I have the following procedure that I am trying to run on a data warehouse
-
>it is supposed to increase or decrease the running premium balance and the
>fire fee balance based on the type of transaction, but it seems to just be
>increasing the running balance - do you see where I am going wrong?
>Thanks in advance
Hi Patrice,
First some general advice:
1. Generally, don't store data that can be calculated in the database.
Each time the base data changes, you'll have to re-do all calculations.
Or you can choose to recalculate periodically, but then, the stored data
might be incorrect when you query the table.
2. Try to avoid cursors. They should only be used when all else fails,
or when you can prove that there is no reasonable set-based alternative.
On to your code.
It's hard to see what's going wrong, becuase you didn't include all
information needed to troubleshoot. Please post:
- The table structures, as CREATE TABLE statements (including all
constraints and properties, but excluding irrelevant columns),
- Some rows of sample data to illustrate the problem, as INSERT
statements,
- The expected output, and
- The output you are actually seeing.
Check out www.aspfaq.com/5006 for more suggestions on how to provide the
information we need in order to help you.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Accessing UDF on a linked server

Hi all-

I need to invoke a UDF on a linked server. Is this possible?

eg:
I need to execute the following query from Server1

select *
from Server2.Database.owner.fnGetTree() AS Server2Tree
inner join Database.owner.fnGetTree() AS Server1Tree
on Server1Tree.NodeId = Server2Tree.NodeId

Thanks for your timeNever mind. I got it. I could do this using OPNEQUERY

Accessing Stored procedure in ASP.Net - What is wrong?

I have the following stored procedure

drop procedure ce_selectCity;

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
create PROCEDURE ce_selectCity
@.recordCount int output
-- Add the parameters for the stored procedure here

--<@.Param2, sysname, @.p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2,, 0>
AS

declare @.errNo int

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.

SET NOCOUNT ON;

-- Insert statements for procedure here

select ciId,name from ce_city order by name

select @.recordCount = @.@.ROWCOUNT
select @.errNo = @.@.ERROR

if @.errNo <> 0 GOTO HANDLE_ERROR

return @.errNo
HANDLE_ERROR:
Rollback transaction
return @.errNo

Go

and i was just testing it like

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
db.connect()

Dim reader As SqlDataReader

Dim sqlCommand As New SqlCommand("ce_selectCity", db.getConnection)

Dim recordCountParam As New SqlParameter("@.recordCount", SqlDbType.Int)
Dim errNoParam As New SqlParameter("@.errNo", SqlDbType.Int)

recordCountParam.Direction = ParameterDirection.Output
errNoParam.Direction = ParameterDirection.ReturnValue

sqlCommand.Parameters.Add(recordCountParam)
sqlCommand.Parameters.Add(errNoParam)

reader = db.runStoredProcedureGetReader(sqlCommand)

If (db.isError = False And reader.HasRows) Then
Response.Write("Total::" & Convert.ToInt32(recordCountParam.Value) & "<br />")
While (reader.Read())
Response.Write(reader("ciId") & "::" & reader("name") & "<br />")
End While

End If
db.close()
End Sub

It returns ALL ROWS (5 in the table right now). So,recordCount should be 5. (When i run it inside SQL Server (directly) it does return 5, so i know its working there).

BUT, its returning 0.

What am i doing wrong??

EDIT:
Oh, and this is the function i use to execute stored procedure and get the reader

Public Function runStoredProcedureGetReader(ByRef sqlCommand As SqlCommand) As SqlDataReader
sqlCommand.CommandType = CommandType.StoredProcedure
Return sqlCommand.ExecuteReader
End Function
I dont know why, but its not showing the code properly.
i tried to put [ code ] [ /code ] aroung the code as in other forums, but i think this one works differently.

Can any Mod please change it? i couldn't find how to edit my post.|||

drop procedure ce_selectCity;

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
create PROCEDURE ce_selectCity
@.recordCount int output
-- Add the parameters for the stored procedure here

--<@.Param2, sysname, @.p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2,, 0>
AS

declare @.errNo int

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.

SET NOCOUNT ON;

-- Insert statements for procedure here

select ciId,name from ce_city order by name

select @.recordCount = @.@.ROWCOUNT
select @.errNo = @.@.ERROR

if @.errNo <> 0 GOTO HANDLE_ERROR

return @.errNo
HANDLE_ERROR:
Rollback transaction
return @.errNo

Go

and i was just testing it like

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
db.connect()

Dim reader As SqlDataReader

Dim sqlCommand As New SqlCommand("ce_selectCity", db.getConnection)

Dim recordCountParam As New SqlParameter("@.recordCount", SqlDbType.Int)
Dim errNoParam As New SqlParameter("@.errNo", SqlDbType.Int)

recordCountParam.Direction = ParameterDirection.Output
errNoParam.Direction = ParameterDirection.ReturnValue

sqlCommand.Parameters.Add(recordCountParam)
sqlCommand.Parameters.Add(errNoParam)

reader = db.runStoredProcedureGetReader(sqlCommand)

If (db.isError = False And reader.HasRows) Then
Response.Write("Total::" & Convert.ToInt32(recordCountParam.Value) & "<br />")
While (reader.Read())
Response.Write(reader("ciId") & "::" & reader("name") & "<br />")
End While

End If
db.close()
End Sub

It returns ALL ROWS (5 in the table right now). So, recordCount should be 5. (When i run it inside SQL Server (directly) it does return 5, so i know its working there).

BUT, its returning 0.

What am i doing wrong??

EDIT:
Oh, and this is the function i use to execute stored procedure and get the reader

Public Function runStoredProcedureGetReader(ByRef sqlCommand As SqlCommand) As SqlDataReader
sqlCommand.CommandType = CommandType.StoredProcedure
Return sqlCommand.ExecuteReader
End Function

|||

I don't do that personally, however, this has been answered a few times on this forum already, so I'll repeat it for you:

Output parameters are not available until you have read all the records if you use .ExecuteReader.

|||Damn and double damn for me

and
thanks and double thanks to you.

Sorry, i should've checked before, but was frustrated as i've been working on this one for 2 hours.|||

Besides what Motley said here's more info:

@.@. functions need to follow the statements for which they intend to be used against. In your code @.recordcount will have the correct value.But @.@.ERROR wil have the error information for the SELECT @.recordcount = @.@.Rowcount statement rather than the original SELECT you intended to. If you change the order it will be the other way. Your @.@.ERROR will have correct information but @.@.Rowcount will show only 1 record since it gets you the information from the SELECT @.err?No = @.@.ERROR statement.

Hope I was clear. Now to get both info, you can use COUNT(*) in your SELECT statement and follow it up with SELECT @.@.ERROR.

As for your .NET code its all messed up. You are initializing all your command objects and connection info and calling a function and not passing any of that info. You are better off putting everything in one event. Declare and initialize all the variables either in the page_load or in the function and just get the result.

Finally, you would add the parameters as :

sqlCommand.Parameters.Add("@.recordCountParam")
sqlCommand.Parameters.Add("@.errNoParam")