Propery expressions: Values not updated

I see some strange behavior when running a package using the SQL Server Agent. The package I run calls a number of child packages, in which I use property expressions to set - among other things - the path to text files I read into a database.

The property expressions are in the form: @.[User::ThePath] + "\\TheFile.txt". The variable ThePath is read from an XML configuration file at runtime from the "master" package and passed on to the child packages using a parent package variable configuration.

My problem is this: When the package is executed by the SQL Server Agent, the properties for the path of the text files are not updated, and the package fails, as it cannot locate the files. When using DTExecUI to execute the same package, everything runs fine.

Could it be a permissions issue? I don't think so, as the SQL Server Agent runs under the same account as I am logged in when executing with DTExecUI.

Anyone?

It does sound a bit like permissions.
So are you referring to the Account the Agent service runs under OR the account the specific Agent Job Step is executing AS, which are 2 different things.

>>>Could it be a permissions issue? I don't think so, as the SQL Server Agent runs under the same account as I am logged in when executing with DTExecUI.

http://support.microsoft.com/?kbid=918760

|||The SQL Agent is running under a local administrator account, which is the same I use when I log in and run the package using DTExecUI. In the scheduled job, the package is "Run as" the "SQL Agent Service Account", which I think should mean that it is executing in the same security context as when I run it manually?!|||

This one has been bugging me big time!

Now, it seems that I might have found a workaround. If I store the main/parent package (the one executed in the Agent job) in MSDB and let the job execute it from here, everything seems to work fine and the property expressions are properly updated at runtime - even though those property expressions are used in the child packages. Previously I had all the packages stored in SSIS Package Store (File System), and that is also where I still keep the child packages.

Can anyone explain this behavior?

|||Can anyone give any explanation for this behavior?|||Now, it gets even more strange. When the job runs on a schedule, it fails, but when I start it manually by clicking "Start job at step..." it runs fine.

property probelm

We have a development machine, and a web server, both running SQL Reporting
Services. My problem is that when I move reports from the development machine
to the server, they lose properties, like font and can grow setttings. Please
help me identify the problem.
--
Thanks,
CGWNever mind... I was just missing a font on the other machine.
"CGW" wrote:
> We have a development machine, and a web server, both running SQL Reporting
> Services. My problem is that when I move reports from the development machine
> to the server, they lose properties, like font and can grow setttings. Please
> help me identify the problem.
> --
> Thanks,
> CGW

Property page giving error

Hi ,
I just deployed a report on a report Server from my Dev m/c...
The Report is running fine except the Property page for the Rpeort shows
the following error
"Unable to generate a temporary class (result=1). error CS1514: { expected
error CS1001: Identifier expected error CS1031: Type expected error CS1519:
Invalid token '{' in class, struct, or interface member declaration error
CS1514: { expected error CS1001: Identifier expected error CS1031: Type
expected error CS1519: Invalid token '{' in class, struct, or interface
member declaration "
I am Using MS RS version 8.00.743.00, My Resporting server is Win 2000 and
My Dev mc is Win XP.
BTW just found a bug with this version of the MSRS as the name of the rdl
file should not contain "Attachment" Key word else the Report can be deployed
but cant be viewed.
any Idea on above issue will be highly aprreciated.
siajDoes this happens to that particular report only?
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"siaj" <siaj@.discussions.microsoft.com> wrote in message
news:069A7DCF-3388-40B6-A2E0-51219071C613@.microsoft.com...
> Hi ,
> I just deployed a report on a report Server from my Dev m/c...
> The Report is running fine except the Property page for the Rpeort shows
> the following error
> "Unable to generate a temporary class (result=1). error CS1514: { expected
> error CS1001: Identifier expected error CS1031: Type expected error
> CS1519:
> Invalid token '{' in class, struct, or interface member declaration error
> CS1514: { expected error CS1001: Identifier expected error CS1031: Type
> expected error CS1519: Invalid token '{' in class, struct, or interface
> member declaration "
> I am Using MS RS version 8.00.743.00, My Resporting server is Win 2000 and
> My Dev mc is Win XP.
> BTW just found a bug with this version of the MSRS as the name of the rdl
> file should not contain "Attachment" Key word else the Report can be
> deployed
> but cant be viewed.
> any Idea on above issue will be highly aprreciated.
> siaj
>
>|||No it happens to all the Reports I deploy......
any suggestions will be highly appreciated..
siaj...
"Lev Semenets [MSFT]" wrote:
> Does this happens to that particular report only?
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "siaj" <siaj@.discussions.microsoft.com> wrote in message
> news:069A7DCF-3388-40B6-A2E0-51219071C613@.microsoft.com...
> > Hi ,
> > I just deployed a report on a report Server from my Dev m/c...
> >
> > The Report is running fine except the Property page for the Rpeort shows
> > the following error
> > "Unable to generate a temporary class (result=1). error CS1514: { expected
> > error CS1001: Identifier expected error CS1031: Type expected error
> > CS1519:
> > Invalid token '{' in class, struct, or interface member declaration error
> > CS1514: { expected error CS1001: Identifier expected error CS1031: Type
> > expected error CS1519: Invalid token '{' in class, struct, or interface
> > member declaration "
> >
> > I am Using MS RS version 8.00.743.00, My Resporting server is Win 2000 and
> > My Dev mc is Win XP.
> >
> > BTW just found a bug with this version of the MSRS as the name of the rdl
> > file should not contain "Attachment" Key word else the Report can be
> > deployed
> > but cant be viewed.
> >
> > any Idea on above issue will be highly aprreciated.
> >
> > siaj
> >
> >
> >
> >
>
>|||Please check this related posting:
http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=41317637-24ab-414b-a650-6d1bb7e55085&sloc=en-us
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"siaj" <siaj@.discussions.microsoft.com> wrote in message
news:38566414-72A9-4F5F-881D-290172AECBDC@.microsoft.com...
> No it happens to all the Reports I deploy......
> any suggestions will be highly appreciated..
> siaj...
>
> "Lev Semenets [MSFT]" wrote:
>> Does this happens to that particular report only?
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "siaj" <siaj@.discussions.microsoft.com> wrote in message
>> news:069A7DCF-3388-40B6-A2E0-51219071C613@.microsoft.com...
>> > Hi ,
>> > I just deployed a report on a report Server from my Dev m/c...
>> >
>> > The Report is running fine except the Property page for the Rpeort
>> > shows
>> > the following error
>> > "Unable to generate a temporary class (result=1). error CS1514: {
>> > expected
>> > error CS1001: Identifier expected error CS1031: Type expected error
>> > CS1519:
>> > Invalid token '{' in class, struct, or interface member declaration
>> > error
>> > CS1514: { expected error CS1001: Identifier expected error CS1031: Type
>> > expected error CS1519: Invalid token '{' in class, struct, or interface
>> > member declaration "
>> >
>> > I am Using MS RS version 8.00.743.00, My Resporting server is Win 2000
>> > and
>> > My Dev mc is Win XP.
>> >
>> > BTW just found a bug with this version of the MSRS as the name of the
>> > rdl
>> > file should not contain "Attachment" Key word else the Report can be
>> > deployed
>> > but cant be viewed.
>> >
>> > any Idea on above issue will be highly aprreciated.
>> >
>> > siaj
>> >
>> >
>> >
>> >
>>

Property IsLocked is not available

I'm running Vista Ultimate x64 and when I tried to connect to my 2005 database using SQL server authentication it gave me an error. When I went to the management studio to check on the login it said this:

TITLE: Microsoft SQL Server Management Studio

Cannot show requested dialog.

ADDITIONAL INFORMATION:

Cannot show requested dialog. (SqlMgmt)

Property IsLocked is not available for Login '[sa]'. This property may not exist for this object, or may not be retrievable due to insufficient access rights. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.PropertyCannotBeRetrievedExceptionText&EvtID=IsLocked&LinkId=20476

BUTTONS:

OK

Has anyone ever seen this before and know how to solve it?

What build is the server running, and what build is your workstation running?|||Product: Microsoft SQL Server Developer Edition (64-bit)
Operating System: Microsoft Windows NT 6.0 (6000)
Version: 9.00.3054.00

|||Anybody?

I know others are having this problem. I've seen it on a couple of message boards.
|||

I ran into the same issue today when I installed an instance of SQL 2005 using Windows Auth. mode. After the install I switched the server over to SQL and Windows Auth mode and ran into the 'sa' problem. I ran the following command and then could see the properties of 'sa' within mgmt studio.

alter login sa

with password = 'yourpwd' unlock,

check_policy = off,

check_expiration = off

|||Works great... Except now I still can't log in with the sa account that I just unlocked from any machine.

TITLE: Connect to Server

Cannot connect to ANGEL.

ADDITIONAL INFORMATION:

Cannot open user default database. Login failed.
Login failed for user 'sa'. (Microsoft SQL Server, Error: 4064)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=4064&LinkId=20476

BUTTONS:

OK

|||You need to change the default database of the sa login. It should always be set to master to avoide this problem.

Property IsLocked is not available

I'm running Vista Ultimate x64 and when I tried to connect to my 2005 database using SQL server authentication it gave me an error. When I went to the management studio to check on the login it said this:

TITLE: Microsoft SQL Server Management Studio

Cannot show requested dialog.

ADDITIONAL INFORMATION:

Cannot show requested dialog. (SqlMgmt)

Property IsLocked is not available for Login '[sa]'. This property may not exist for this object, or may not be retrievable due to insufficient access rights. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.PropertyCannotBeRetrievedExceptionText&EvtID=IsLocked&LinkId=20476

BUTTONS:

OK

Has anyone ever seen this before and know how to solve it?

What build is the server running, and what build is your workstation running?|||Product: Microsoft SQL Server Developer Edition (64-bit)
Operating System: Microsoft Windows NT 6.0 (6000)
Version: 9.00.3054.00

|||Anybody?

I know others are having this problem. I've seen it on a couple of message boards.
|||

I ran into the same issue today when I installed an instance of SQL 2005 using Windows Auth. mode. After the install I switched the server over to SQL and Windows Auth mode and ran into the 'sa' problem. I ran the following command and then could see the properties of 'sa' within mgmt studio.

alterlogin sa

with password ='yourpwd' unlock,

check_policy =off,

check_expiration =off

|||Works great... Except now I still can't log in with the sa account that I just unlocked from any machine.

TITLE: Connect to Server

Cannot connect to ANGEL.

ADDITIONAL INFORMATION:

Cannot open user default database. Login failed.
Login failed for user 'sa'. (Microsoft SQL Server, Error: 4064)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=4064&LinkId=20476

BUTTONS:

OK

|||You need to change the default database of the sa login. It should always be set to master to avoide this problem.sql

Properties

Why is it on some sql server 2000 machines - sp3a, running windows 2000,
all being similar in hardware setup, speed, memory, etc that it really
takes long to return a properties screen or simply right click to
disconnect? Curious, any ideas?
Thanks
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!"Dave Brooks" <dbrooks@.neca.org> wrote in message
news:uW9y8Jo6DHA.1636@.TK2MSFTNGP12.phx.gbl...

quote:

---

> Why is it on some sql server 2000 machines - sp3a, running windows 2000,
> all being similar in hardware setup, speed, memory, etc that it really
> takes long to return a properties screen or simply right click to
> disconnect? Curious, any ideas?
> Thanks
>
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!

---

If you're talking about slow access through Enterprise Manager, then one
cause could be that there are databases on the server which are set to
auto-close; another possibility is that you have ODBC tracing enabled.
Simon

Propagate NEW Table to Anon Merge Replicas

How do I get a New Table to propagate to Anonymous Merge Subscribers, WITH
Add, Update, Delete Triggers?
I created a new Table (by running a SQL Script created on a Developer
machine) on a Remote SBS 2003 Server (SQL 2K). It Propagated to Subscribers,
OK. But it is useless, because is missing the Triggers: dbo.ins_ ... ,
dbo.upd_ ... , dbo.del_ ...
What did I miss, and Where? when I created the Prototype?
Aubrey Kelley
It sounds like merge replication has not placed all of the object it
requires on the subscriber. Are all objects owned by dbo? There were some
problems on pre SP 4 service packs with this.
How did you create the new table and add it to the merge publication? How
did you deploy it to your subscribers?
The best way to fix this is to reinitialize, generate a new snapshot and
send it to the subscribers. Any other approach may fix this particular
problem but there could be further missing objects and the merge replication
metadata may be in an inconsistent state.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Aubrey" <miscuates@.online.nospam> wrote in message
news:B1E8B402-7B5B-4DD3-8389-9BBC7D99F109@.microsoft.com...
> How do I get a New Table to propagate to Anonymous Merge Subscribers, WITH
> Add, Update, Delete Triggers?
> I created a new Table (by running a SQL Script created on a Developer
> machine) on a Remote SBS 2003 Server (SQL 2K). It Propagated to
> Subscribers,
> OK. But it is useless, because is missing the Triggers: dbo.ins_ ... ,
> dbo.upd_ ... , dbo.del_ ...
> What did I miss, and Where? when I created the Prototype?
> --
> Aubrey Kelley

Promoting SQL Server to a Domain Controller?

Does anyone have any thoughts or have tried to promote a Win2k Server running SQL Standard to a Domain controller using dcpromo? Should this be done? have you done or tried this? What are the issues? Thank you for your response in advance.
Ric
I think this is a really bad idea. (Performance, security, etc)
WHY do you want to do this out of curiuosity?
Greg Jackson
PDX, Oregon
|||Why would you want to? Is the server underutilized in your opinion? Do you
not have enough hardware? What do you have for a DHCP, DNS, and WINS server?
Generally it is better to keep application servers (which SQL Server is) and
domain servers separate due to resource issues associated with the two
different usages.
Andrew C. Madsen
Information Architect
Harley-Davidson Motor Company
"Ric" <Ric@.discussions.microsoft.com> wrote in message
news:68217993-6559-4F7F-9B12-54AA0443F315@.microsoft.com...
> Does anyone have any thoughts or have tried to promote a Win2k Server
running SQL Standard to a Domain controller using dcpromo? Should this be
done? have you done or tried this? What are the issues? Thank you for
your response in advance.
> Ric
|||We would be using this as the secondary domain controller. (Backup to our main domain controller in case of it failing. No other reason)
"Jaxon" wrote:

> I think this is a really bad idea. (Performance, security, etc)
> WHY do you want to do this out of curiuosity?
>
> Greg Jackson
> PDX, Oregon
>
>
|||We would be using this as the secondary domain controller. (Backup to our main domain controller in case of it failing. No other reason)
"Andrew Madsen" wrote:

> Why would you want to? Is the server underutilized in your opinion? Do you
> not have enough hardware? What do you have for a DHCP, DNS, and WINS server?
> Generally it is better to keep application servers (which SQL Server is) and
> domain servers separate due to resource issues associated with the two
> different usages.
> --
> Andrew C. Madsen
> Information Architect
> Harley-Davidson Motor Company
> "Ric" <Ric@.discussions.microsoft.com> wrote in message
> news:68217993-6559-4F7F-9B12-54AA0443F315@.microsoft.com...
> running SQL Standard to a Domain controller using dcpromo? Should this be
> done? have you done or tried this? What are the issues? Thank you for
> your response in advance.
>
>
|||There is no longer a "Backup Domain" controller concept in AD anymore. They
are all peers and therefore do the same amount of work. Unless you don't
transfer the global catalog. So I would advise against it because you would
potentially have the same issues accessing domain resources if SQL server
was doing some heavy processing and acting as a DC.
Andrew C. Madsen
Information Architect
Harley-Davidson Motor Company
"Ric" <Ric@.discussions.microsoft.com> wrote in message
news:681154C6-23E2-43E7-9EC6-BA5F3CCAA2D3@.microsoft.com...
> We would be using this as the secondary domain controller. (Backup to our
main domain controller in case of it failing. No other reason)[vbcol=seagreen]
> "Andrew Madsen" wrote:
you[vbcol=seagreen]
server?[vbcol=seagreen]
and[vbcol=seagreen]
be[vbcol=seagreen]

Promoting SQL Server to a Domain Controller?

Does anyone have any thoughts or have tried to promote a Win2k Server runnin
g SQL Standard to a Domain controller using dcpromo? Should this be done?
have you done or tried this? What are the issues? Thank you for your respo
nse in advance.
RicI think this is a really bad idea. (Performance, security, etc)
WHY do you want to do this out of curiuosity?
Greg Jackson
PDX, Oregon|||Why would you want to? Is the server underutilized in your opinion? Do you
not have enough hardware? What do you have for a DHCP, DNS, and WINS server?
Generally it is better to keep application servers (which SQL Server is) and
domain servers separate due to resource issues associated with the two
different usages.
Andrew C. Madsen
Information Architect
Harley-Davidson Motor Company
"Ric" <Ric@.discussions.microsoft.com> wrote in message
news:68217993-6559-4F7F-9B12-54AA0443F315@.microsoft.com...
> Does anyone have any thoughts or have tried to promote a Win2k Server
running SQL Standard to a Domain controller using dcpromo? Should this be
done? have you done or tried this? What are the issues? Thank you for
your response in advance.
> Ric|||We would be using this as the secondary domain controller. (Backup to our ma
in domain controller in case of it failing. No other reason)
"Jaxon" wrote:

> I think this is a really bad idea. (Performance, security, etc)
> WHY do you want to do this out of curiuosity?
>
> Greg Jackson
> PDX, Oregon
>
>|||We would be using this as the secondary domain controller. (Backup to our ma
in domain controller in case of it failing. No other reason)
"Andrew Madsen" wrote:

> Why would you want to? Is the server underutilized in your opinion? Do you
> not have enough hardware? What do you have for a DHCP, DNS, and WINS serve
r?
> Generally it is better to keep application servers (which SQL Server is) a
nd
> domain servers separate due to resource issues associated with the two
> different usages.
> --
> Andrew C. Madsen
> Information Architect
> Harley-Davidson Motor Company
> "Ric" <Ric@.discussions.microsoft.com> wrote in message
> news:68217993-6559-4F7F-9B12-54AA0443F315@.microsoft.com...
> running SQL Standard to a Domain controller using dcpromo? Should this be
> done? have you done or tried this? What are the issues? Thank you for
> your response in advance.
>
>|||There is no longer a "Backup Domain" controller concept in AD anymore. They
are all peers and therefore do the same amount of work. Unless you don't
transfer the global catalog. So I would advise against it because you would
potentially have the same issues accessing domain resources if SQL server
was doing some heavy processing and acting as a DC.
Andrew C. Madsen
Information Architect
Harley-Davidson Motor Company
"Ric" <Ric@.discussions.microsoft.com> wrote in message
news:681154C6-23E2-43E7-9EC6-BA5F3CCAA2D3@.microsoft.com...
> We would be using this as the secondary domain controller. (Backup to our
main domain controller in case of it failing. No other reason)[vbcol=seagreen]
> "Andrew Madsen" wrote:
>
you[vbcol=seagreen]
server?[vbcol=seagreen]
and[vbcol=seagreen]
be[vbcol=seagreen]

Proliant HP 380 With SQL Server

Hi,
I have an SQL Server 2000 standard Edition running on a
Third geration HP Proliant 380 with two physical XEON
processors. The SO detects 4 processors and SQL Server
also. I think that 2 are virtual processors. My question
is, why this happens ? If i upgrade for 4 physical
processors should i upgrade to Enterprise Edition ' i
will have 8 processors detected, 4 of them virtual
processors.
One more question. Enterprise Edition upgrade from
Standard Edition is it possible ' i think that i must
reinstall all the server, am i right '
Thanks a lot for the help
MiguelIt's likely that these CPU's use a feature called 'Hyper Threading' which
makes them look like 2 processors (rather than one) to the OS.
You can upgrade Standard Edition to Enterprise without a full reinstall.
Enterprise is 4X as expensive. Do you really think you need it? For what
reasons?
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Miguel" <anonymous@.discussions.microsoft.com> wrote in message
news:f4b501c41324$2a541380$a001280a@.phx.gbl...
> Hi,
> I have an SQL Server 2000 standard Edition running on a
> Third geration HP Proliant 380 with two physical XEON
> processors. The SO detects 4 processors and SQL Server
> also. I think that 2 are virtual processors. My question
> is, why this happens ? If i upgrade for 4 physical
> processors should i upgrade to Enterprise Edition ' i
> will have 8 processors detected, 4 of them virtual
> processors.
> One more question. Enterprise Edition upgrade from
> Standard Edition is it possible ' i think that i must
> reinstall all the server, am i right '
> Thanks a lot for the help
> Miguel|||Just to extend on what Brian said, SQL server 2000 SP2 and higher understand
hyperthreading from a licensing standpoint. SQL Server 2000 Standard
detects the actual physical processor count in a hyperthreaded system for
licensing purposes when you have SP2 or later installed. You don't need
Enterprise Edition to deal with the virtual processor count.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Brian Moran" <brian@.solidqualitylearning.com> wrote in message
news:uXwsR7zEEHA.2408@.TK2MSFTNGP10.phx.gbl...
> It's likely that these CPU's use a feature called 'Hyper Threading' which
> makes them look like 2 processors (rather than one) to the OS.
> You can upgrade Standard Edition to Enterprise without a full reinstall.
> Enterprise is 4X as expensive. Do you really think you need it? For what
> reasons?
> --
> Brian Moran
> Principal Mentor
> Solid Quality Learning
> SQL Server MVP
> http://www.solidqualitylearning.com
>
> "Miguel" <anonymous@.discussions.microsoft.com> wrote in message
> news:f4b501c41324$2a541380$a001280a@.phx.gbl...
>|||Thanks, but actually i'm running the server with 2
physical processors and SQL Server Standard Edition + SP3a
detects 4. My manager wants to upgrade to 4GB memory and
more 2 processors, so i will need Enterprise Edition
right? The application will grow at least 3 times what we
have now. Another question: How to upgrade without
reinstalling the server ?

>--Original Message--
>Just to extend on what Brian said, SQL server 2000 SP2
and higher understand
>hyperthreading from a licensing standpoint. SQL Server
2000 Standard
>detects the actual physical processor count in a
hyperthreaded system for
>licensing purposes when you have SP2 or later installed.
You don't need
>Enterprise Edition to deal with the virtual processor
count.
>--
>Geoff N. Hiten
>Microsoft SQL Server MVP
>Senior Database Administrator
>Careerbuilder.com
>I support the Professional Association for SQL Server
>www.sqlpass.org
>"Brian Moran" <brian@.solidqualitylearning.com> wrote in
message
>news:uXwsR7zEEHA.2408@.TK2MSFTNGP10.phx.gbl...
called 'Hyper Threading' which
the OS.
a full reinstall.
need it? For what
message
a
question
>
>.
>|||comments in line...
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Miguel" <anonymous@.discussions.microsoft.com> wrote in message
news:13fc901c41356$7f0892c0$a301280a@.phx
.gbl...
> Thanks, but actually i'm running the server with 2
> physical processors and SQL Server Standard Edition + SP3a
> detects 4.
<<
right, that's hte hyper threading issue Geoff and I mentioned...
My manager wants to upgrade to 4GB memory and
> more 2 processors, so i will need Enterprise Edition
> right?
<<
SQL Standard will support 2G of memory and 4 processors. You need Enterprise
if you want more than 4 procs or more than 2G.
SQL Server 2000 and Windows2K do not have an ability to differentiate
between hyperthreaded and real procs. Win2003 can tell the difference.
On Win2K... I know that SQL will not use the 8 hyper thread CPUs. Honestly,
on Win2003, I'm not sure. You do NOT have to pay for a license for them. I
don't know if SQL will use them or not. I supposed I need to find that out
for myself...

The application will grow at least 3 times what we
> have now. Another question: How to upgrade without
> reinstalling the server ?
>
<<
stick in the Enterprise CD and run setup. It's that simple.
Finally, your app may grow to 3 times what it is today. But are you sure you
need 8 hyperthread procs and 4G? MANY customers have MUCH more server than
they need. You're looking at going from ~10K for a SQL license that you
already own to spending 80K for a SQL EE license. Are you sure you need it?
Also, have you considered IO?
I can't tell you how many customers I've worked with who have tons of memory
and CPU but are running it all on a few IDE drives. Not an effecient way to
spend your money...
> and higher understand
> 2000 Standard
> hyperthreaded system for
> You don't need
> count.
> message
> called 'Hyper Threading' which
> the OS.
> a full reinstall.
> need it? For what
> message
> a
> question|||Actually, SQL 2000 SP2 or later can tell the difference between
Hyperthreaded and standard procesors. W2K3 does know the difference, but
still shows the virtual processor count with all the utilities. So, you can
have a 4 proc Hyperthreaded system and still run SQL 2000 Standard edition
even though the OS shows 8 procs. That goes for Win2k or Win2003.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Brian Moran" <brian@.solidqualitylearning.com> wrote in message
news:uaMm4x3EEHA.3804@.TK2MSFTNGP09.phx.gbl...
> comments in line...
> --
> Brian Moran
> Principal Mentor
> Solid Quality Learning
> SQL Server MVP
> http://www.solidqualitylearning.com
>
> "Miguel" <anonymous@.discussions.microsoft.com> wrote in message
> news:13fc901c41356$7f0892c0$a301280a@.phx
.gbl...
> <<
> right, that's hte hyper threading issue Geoff and I mentioned...
>
> My manager wants to upgrade to 4GB memory and
> <<
> SQL Standard will support 2G of memory and 4 processors. You need
Enterprise
> if you want more than 4 procs or more than 2G.
> SQL Server 2000 and Windows2K do not have an ability to differentiate
> between hyperthreaded and real procs. Win2003 can tell the difference.
> On Win2K... I know that SQL will not use the 8 hyper thread CPUs.
Honestly,
> on Win2003, I'm not sure. You do NOT have to pay for a license for them. I
> don't know if SQL will use them or not. I supposed I need to find that out
> for myself...
>
>
>
>
>
> The application will grow at least 3 times what we
> <<
> stick in the Enterprise CD and run setup. It's that simple.
> Finally, your app may grow to 3 times what it is today. But are you sure
you
> need 8 hyperthread procs and 4G? MANY customers have MUCH more server than
> they need. You're looking at going from ~10K for a SQL license that you
> already own to spending 80K for a SQL EE license. Are you sure you need
it?
> Also, have you considered IO?
> I can't tell you how many customers I've worked with who have tons of
memory
> and CPU but are running it all on a few IDE drives. Not an effecient way
to
> spend your money...
>
>

Progress Indicator for Table Reindex

I am reindex a 100 Gig Table with 30 million rows. It is currently running 14
hours.
Is there any way for me to find out approx when this will end (ie percent
complete)
No. There is no progress reporting for index ops in SQL Server 2000 or 2005.
Do you know how long the index took to reindex last time? That would be the
basis for a rough first-order approximation of the run-time this time. Other
factors that make determining this very difficult are concurrent cpu and I/O
load and distribution of free space in the database (basically you need to
have another 120 Gb of free space to be able to rebuild a 100 Gb clustered
index - if you don't the files will grow during the operation).
Regards
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Mark Gozick" <MarkGozick@.discussions.microsoft.com> wrote in message
news:666E1A4F-D8AF-440C-8D56-84A5DF1DE0EC@.microsoft.com...
> I am reindex a 100 Gig Table with 30 million rows. It is currently running
14
> hours.
> Is there any way for me to find out approx when this will end (ie percent
> complete)
>
|||100GB "TABLE" ?
how big is the DB ?
Greg Jackson
PDX, Oregon
|||the DB is 400 Gig. The 100 Gig table is the General Ledger Detail table which
is quite large. We can only keep 2 years on-line.
"pdxJaxon" wrote:

> 100GB "TABLE" ?
> how big is the DB ?
>
> Greg Jackson
> PDX, Oregon
>
>
|||Mark Gozick wrote:
> the DB is 400 Gig. The 100 Gig table is the General Ledger Detail
> table which is quite large. We can only keep 2 years on-line.
Did you just say "only"? ...
robert
|||That's not that big any more...
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Robert Klemme" <bob.news@.gmx.net> wrote in message
news:u4COG9HYFHA.3356@.TK2MSFTNGP15.phx.gbl...
> Mark Gozick wrote:
> Did you just say "only"? ...
> robert
>

Progress Indicator for Table Reindex

I am reindex a 100 Gig Table with 30 million rows. It is currently running 14
hours.
Is there any way for me to find out approx when this will end (ie percent
complete)No. There is no progress reporting for index ops in SQL Server 2000 or 2005.
Do you know how long the index took to reindex last time? That would be the
basis for a rough first-order approximation of the run-time this time. Other
factors that make determining this very difficult are concurrent cpu and I/O
load and distribution of free space in the database (basically you need to
have another 120 Gb of free space to be able to rebuild a 100 Gb clustered
index - if you don't the files will grow during the operation).
Regards
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Mark Gozick" <MarkGozick@.discussions.microsoft.com> wrote in message
news:666E1A4F-D8AF-440C-8D56-84A5DF1DE0EC@.microsoft.com...
> I am reindex a 100 Gig Table with 30 million rows. It is currently running
14
> hours.
> Is there any way for me to find out approx when this will end (ie percent
> complete)
>|||100GB "TABLE" ?
how big is the DB ?
Greg Jackson
PDX, Oregon|||the DB is 400 Gig. The 100 Gig table is the General Ledger Detail table which
is quite large. We can only keep 2 years on-line.
"pdxJaxon" wrote:
> 100GB "TABLE" ?
> how big is the DB ?
>
> Greg Jackson
> PDX, Oregon
>
>|||Mark Gozick wrote:
> the DB is 400 Gig. The 100 Gig table is the General Ledger Detail
> table which is quite large. We can only keep 2 years on-line.
Did you just say "only"? ...
robert|||That's not that big any more...
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Robert Klemme" <bob.news@.gmx.net> wrote in message
news:u4COG9HYFHA.3356@.TK2MSFTNGP15.phx.gbl...
> Mark Gozick wrote:
> > the DB is 400 Gig. The 100 Gig table is the General Ledger Detail
> > table which is quite large. We can only keep 2 years on-line.
> Did you just say "only"? ...
> robert
>

Progress Indicator for Table Reindex

I am reindex a 100 Gig Table with 30 million rows. It is currently running 1
4
hours.
Is there any way for me to find out approx when this will end (ie percent
complete)No. There is no progress reporting for index ops in SQL Server 2000 or 2005.
Do you know how long the index took to reindex last time? That would be the
basis for a rough first-order approximation of the run-time this time. Other
factors that make determining this very difficult are concurrent cpu and I/O
load and distribution of free space in the database (basically you need to
have another 120 Gb of free space to be able to rebuild a 100 Gb clustered
index - if you don't the files will grow during the operation).
Regards
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Mark Gozick" <MarkGozick@.discussions.microsoft.com> wrote in message
news:666E1A4F-D8AF-440C-8D56-84A5DF1DE0EC@.microsoft.com...
> I am reindex a 100 Gig Table with 30 million rows. It is currently running
14
> hours.
> Is there any way for me to find out approx when this will end (ie percent
> complete)
>|||100GB "TABLE" ?
how big is the DB ?
Greg Jackson
PDX, Oregon|||the DB is 400 Gig. The 100 Gig table is the General Ledger Detail table whic
h
is quite large. We can only keep 2 years on-line.
"pdxJaxon" wrote:

> 100GB "TABLE" ?
> how big is the DB ?
>
> Greg Jackson
> PDX, Oregon
>
>|||Mark Gozick wrote:
> the DB is 400 Gig. The 100 Gig table is the General Ledger Detail
> table which is quite large. We can only keep 2 years on-line.
Did you just say "only"? ...
robert|||That's not that big any more...
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Robert Klemme" <bob.news@.gmx.net> wrote in message
news:u4COG9HYFHA.3356@.TK2MSFTNGP15.phx.gbl...
> Mark Gozick wrote:
> Did you just say "only"? ...
> robert
>

programmatically setting package variables in job step command line

We are trying to start a server job running an SSIS package and supply some parameters to the package when we start the job using SMO.

What we have now is this:

string cmdLine = job.JobSteps[0].Command;

cmdLine += @." /SET \Package\GetGroupRatingYear_Id.Variables[User::RatingId].Value;1";

cmdLine += @." /SET \Package\GetGroupRatingYear_Id.Variables[User::GroupId].Value;1";

cmdLine += " /SET \\Package.Variables[User::period].Value;\"" + periodEndDate + "\"";

job.JobSteps[0].Command = cmdLine;

job.Start();

It appears that when the job is run, the modified command line is not used.

What is needed to supply runtime parameters to a job step when starting the job via SMO?

Thanks,

So managing a job in this way seems a bit of a pain. Why not let the package go and get the values from an external location when it is required.

One example would be to use a package configuration, perhaps using a SQL Server configuration. You could update the table values, and then just design the package to use that configuration value, assigning the values to the variables as required. Read up on package configurations if you are not familiar with them.

A variation on the theme it to do the work yourself. You could use any table, not just a configuration format table. Use an Execuite SQL Task to query for the values and using the result set option, you can return values and on the results page of the task, set the output to variable values.

|||

Yes it's been a learning curve in how to do what we're trying to do. The application is driven by a web page where the user says 'run this job, use these parameters'. However, you can't have one predefined job with multiple instances on the server, each with their own set of parameters - which needs to be possible because of the application requirements.

What we are doing now that seems to work is creating a new job, setting the type to ssis, setting the command line to specify the package and parameters, and then starting the job. It is also set to auto delete upon success.

The other way we thought of but decided against was to have the job pick up its runtime parameters from a queue - but then we'd have to create and manage the queue.

The 'create new job' approach lets us run now or set a schedule to run later, all the instances are visible as jobs on the server (based on category to filter out for the UI), and they clean up themselves if they run successfully.

NB: if anyone is curious, changing the command line of an existing job requires the Alter() method to persist the change back to the server, otherwise it just runs with the original command. like this:

string cmdLine = job.JobSteps[0].Command;
cmdLine += @." /SET \Package\GetGroupRatingYear_Id.Variables[User::RatingId].Value;1";
job.JobSteps[0].Command = cmdLine;
job.JobSteps[0].Alter();
job.Start();

However, this permanently changes the command line in the job of course and you have to deal with that.

The code that that we're using to dynamically create the job and supply the parameters is pretty close to this:

string jobName = "the name to give to the new job";
string cmdLine = "the command line to run the package and set parameters";
ServerConnection svrConnection = new ServerConnection(sqlConnection);
Server svr = new Server(svrConnection);
JobServer agent = svr.JobServer;
if (agent.Jobs.Contains(jobName))
{
agent.Jobs[jobName].Drop();
}

job = new Job(agent, jobName);
job.DeleteLevel = CompletionAction.OnSuccess;
job.Category = "Calculate";
JobStep js = new JobStep(job, "Step 1");
js.SubSystem = AgentSubSystem.Ssis;
js.Command = cmdLine;
job.Create();
js.Create();
job.ApplyToTargetServer("(local)");
job.Alter();
job.Start();

Programmatically determining if a data driven subscription is running

Hi,

Quick question: how do I determine programmatically if a data driven subscription is currently running?

More info:

I’m writing a web application which allows the user to kick off an existing data driven subscription (reporting services 2000), which runs from a table with parameters, paths, etc which the user has populated.

The subscription can take several minutes to run, during which time I need to prevent other users from attempting to run the subscription or alter data on the table driving the subscription.

All I’ve found in the docs so far is

1. The ActiveState on the subscription.

This seems to have more to do with weather or not it can run than if it is running.

2. The status of the subscription.

This seems to only return “done: {0} of {1} with {2} errors” Parsing this is likely to be too flaky to be acceptable.

I really need to move on this as soon as possible, any help is appreciated.

Thanks

If you look at the ReportServer.dbo.Subscriptions table, isn't there a LastRunTime column? Will that get you what you want?|||

I should have clarified that I want to obtain the information through soap access. I belive I can actually obtain that value through soap access, but I believe the lastruntime states the time at which the process was kicked off, not the time at which it finished. From the reading i've been doing I'm staring to think that this is a very difficult problem to solve. If I come up with a solution more interesting than parsing the status field I'll post the details.

Thanks anyway

|||

Ok,

Looks like I was wrong in my original post, the status returned from the subscription will say either Done: blah of blah or Processing Blah of Blah. So as an imperfect (and quick) solution I can and will parse that. I'm still not certain if i'm missing something and everything I need is already available through the soap api, but anyway... If I had more time and we weren't switching to 2005 in a few months I'd probably write a custom delivery extension that fires off an event to a web service or something to that effect.

Programmatically Deleting an Instance

I'm trying to just get a basic NS app up and running. While I can get through the samples just fine, it seems there are no samples that programmatically manage Instances/Applications, etc. I've googled around and not found anything either. Essentially it looks to me that everyone uses the IDF/ADF files. The SDK has some very sparse documentation on it, so I've started there and tried to get things working.

I have code to create the instance/application programmatically, but when I want to drop the whole thing and create it again, upon calling instance.Create() I get an exception (actually this is what the innerException says) saying:

Notification Services failed to read the NSVersionInfo table.

I’m following the example in the SDK (doing Disable/UnregisterLocal/Drop). Interestingly when I do the Disable/Unregister/Drop in SQL Management Studio it works fine. It's also interesting to note that when I ignore the exception it comes up once more, but after that the instance and associated databases seem to be created.

Here’s the short version of my code (getFreshInstance() is the entry method):

private void configureInstance(nmo.Instance instance)
{
prototypeApplication = new nmo.Application(instance, applicationName);

AddDeliveryChannels(instance);
configureApp(prototypeApplication);
instance.Applications.Add(prototypeApplication);
}

private void configureApp(nmo.Application app)
{
AddGenerator(app);
AddDistributor(app);
}

private void checkAndDrop(string name)
{
if (notificationServices.Instances.Contains(name))
{
nmo.Instance instance = new nmo.Instance(notificationServices, name);
instance.Refresh();

instance.Disable();
instance.UnregisterLocal();
instance.Drop();
}
}

private nmo.Instance getFreshInstance()
{
nmo.Instance instance = new nmo.Instance(notificationServices, instanceName);

checkAndDrop(instanceName);

configureInstance(instance);

instance.Create();
instance.RegisterLocal();
instance.Enable();

return instance;
}

Anyone know what I'm doing wrong?

Ok, seems you've asked a question not many folks in the wild know the answer to :-).

I've tracked down what is probably the answer care of Shyam Pather:

He’s creating a new in-memory object to reflect his instance, like this:

Nmo.Instance instance = new Nmo.Instance(…, “InstanceName”);

Instead, he should do something like:

Smo.Server s = new Smo.Server(…);

Nmo.Instance instance = s.NotificationServices.Instances[“InstanceName”];

That will get him an instance object that’s already populated with the metadata from the database and actually represents the existing instance.

The FlightNMO sample that comes with SQL Server 2005 should also provide you a working example of how to make the API calls.

Hope that helps,

-Lukasz

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

|||

I just gave that snippet a try and unfortunately, I ended up with the same error when I attempt to enable. The need for this functionality in my project has been removed by a change in design, so I'm no longer really worried about this, but I would still be curious to know the cause of this or hear confirmation of someone else running into this.

Now that you mention it, I vaguely remember seeing the FlightNMO sample when I first looked at NS, but had since forgotten it. Thanks! I'm sure that'll prove quite useful later. As far as this particular functionality though, I'm afraid FlightNMO doesn't ever delete the instance, only create.

Thanks for your help,

-Francis

Programmatically checking whether Integration service is running or not

I am building an application that uses .NET 2.0 transaction feature. Since .NET 2.0 transaction model uses SQL Server 2005 Integration services for its operation, I want to check from my application (.NET 2.0 code) whether the Integration service is running on SQL box or not? Is there a way in which we can accomplish this task?

Prashant

You can query the SSIS service with the GetRunningPackages method to get the packages running on a server.

http://msdn2.microsoft.com/en-us/library/microsoft.sqlserver.dts.runtime.application.getrunningpackages.aspx
|||Using GetRunningPackages is perhaps not quite the same as saying is the windows service running, which seemed to be the original question. The fact that the service supports that method would probably allow you to infer that result, but maybe you would want to find out more about the service or control it, in which case have a look at the System.ServiceProcess.ServiceController class. The System.ServiceProcess namespace contains classes for manging Windows Services, not anything specific to SSIS.

Programmatically changing shared data sources when running SQL Reporting Service 2005 Repo

Can someone please help me or point me to the right sources on how I can possibly programmatically change the shared data source that was assigned to an RDL when a user runs it from my asp page? I have to create several reports and would like to have the option to let users choose the sql server to connect to and possibly different login credentials. What should I do?

Please help.

Hope these articles can help you -

http://msdn2.microsoft.com/en-us/library/ms252085(VS.80).aspx

http://www.aspfree.com/c/a/ASP.NET/Binding-Data-to-the-ReportViewer-Control-Dynamically-in-ASPNET-20/

Programatically running the SQL Server Agent

I have a scenerio where I am using a Push Merge Subscription set with "Run
On Demand". How can I programatically (Stored procedure) run the SQL Agent
Job that synchronizes the Subscriptions?
Thanks,
RSHHave a look at sp_start_Job.
Andrew J. Kelly SQL MVP
"RSH" <way_beyond_oops@.yahoo.com> wrote in message
news:O%23tZgcf9FHA.3660@.TK2MSFTNGP09.phx.gbl...
>I have a scenerio where I am using a Push Merge Subscription set with "Run
>On Demand". How can I programatically (Stored procedure) run the SQL Agent
>Job that synchronizes the Subscriptions?
> Thanks,
> RSH
>

Program Connection Problem

Hi there,
I really hope that someone recognises this problem and can point me in the
direction of a solution.
We have SQL Server 2000 running on a server at our head office. We have a
program that runs in other sites that connect to the server via a VPN
connection HO. The program has been running perfectly for about a year now
without any problems. A few weeks ago, one machine in one of our sites
stopped opening a connection and has refused to open one ever since. We
hadn't had any problems anywhere else so we swapped the machine with a newer
one and thought nothing else about it. However, a couple of days ago another
machine in another site did the same thing. It simply refuses to open a
connection to the server. The error it throws is "SQL Server does not exist
or access denied". This strikes me as strange because he machine can ping
the sql server happily and it has no problems with the rest of its network
connectivity and the connection string uses the server admin user and
password. I've tried changing the machine IP address and HostName but that
has had no effect. The machine is running Windows 98 SE.
Now, I am pretty sure that there is nothing wrong with the program because
it has been running perfectly for ages and hasn't changed for nearly a year.
The program is running on about 20 identical 98 boxes in 15 sites. The first
machine to go was probably the busiest user of the program and this second
one is probably next.
So I'm thinking that this must be a SQL issue but we really don't have
anyone that knows that much about it. What I do know is that, in general,
the server is running ok. The program in question is probably the smallest
data-wise and has very little traffic, we have other much more complex
databases and programs running 24/7 on the server and they are all ok.
So, I guess the leading question is - does anyone have any ideas? Is there
some kind of connection log that has maxed out or something like that?
Any pointers would be fantastic.
Cheers
C-Ya Aly
Do client tools like UDL, osql and isql work from this problem machine? Can
this problem client machine ping any other sql servers in your network?
Cheers,
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.