Embedded VB.NET Code Limitations?

What are the limitations to using the Embedded VB.NET Code within a RDL?
Is it possible to control the IE Browser features via the Embedded Code?
JeffThere are no syntax limitations in VB code used in report expressions and
Code element. You can also call custom assemblies that must only be managed
but could be writting in VB, C# or even managed C++, etc...
If I correctly understand your question you want to write a code hosted
inside a report that will run on client and control browser via ActiveX
interface. This is not possible because all code inside report is always
executed on server and never passed back to the client for execution.
RS also does not support embedding scripts into HTML output. If it was
allowed it would be a security hole...
You can write your own application that will host IE Browser control and
will talk to report server to retrieve reports.
"Jeff" <Jeff@.discussions.microsoft.com> wrote in message
news:C762FC98-66B6-4358-9E91-28F2B3129475@.microsoft.com...
> What are the limitations to using the Embedded VB.NET Code within a RDL?
> Is it possible to control the IE Browser features via the Embedded Code?
> Jeff

Emailing SQL 2000 maintenance report via SMTP?

Hi All
As per my post in microsoft... server NG, I keep getting a problem where my
DB maintenance plan just stops doing what it should, which in turn means
that if I don't paranoid-ly check it each day I get the scenario (like
today) where I find my backups haven't been working for a month!!!
What I would like to do is email the maintenance plan to a designated email
address so that I can easily see every morning whether or not it has worked.
I know the old SQL 6.5 used to provide SQL Mail, but this links in with MS
Exchange Server and I don't use this. I don't even have it running on my
SBS 2000 Server.
Is there anyway in SQL 2000 that I can get it to email the report via SMTP?
I'm familiar with JMail and it's COM/DLL is installed on my server if I
could use that.
Thanks RobbieFirst of all, can I suggest that you not use the maintenance plan for
backups? You can create jobs within the SQL Server Agent that will backup
your databases. Books Online (within the SQL Server program group) contains
T-SQL examples of backup scripts. If you are so inclined you can also
create jobs that perform the appropriate maintenance tasks (such as checkdb,
checkcatalog, updateusage, update statistics).
You could use XPSMTP http://sqldev.net/xp/xpsmtp.htm to send email alerts.
Keith
"Astra" <info@.noemail.com> wrote in message
news:uXPyFCzGFHA.2452@.TK2MSFTNGP10.phx.gbl...
> Hi All
> As per my post in microsoft... server NG, I keep getting a problem where
my
> DB maintenance plan just stops doing what it should, which in turn means
> that if I don't paranoid-ly check it each day I get the scenario (like
> today) where I find my backups haven't been working for a month!!!
> What I would like to do is email the maintenance plan to a designated
email
> address so that I can easily see every morning whether or not it has
worked.
> I know the old SQL 6.5 used to provide SQL Mail, but this links in with MS
> Exchange server and I don't use this. I don't even have it running on my
> SBS 2000 Server.
> Is there anyway in SQL 2000 that I can get it to email the report via
SMTP?
> I'm familiar with JMail and it's COM/DLL is installed on my server if I
> could use that.
> Thanks Robbie
>|||Hi
Check out http://www.sqldev.net/xp/xpsmtp.htm
Other possible mechanisms might mbe Notification Services or Reporting
Services.
John
"Astra" wrote:

> Hi All
> As per my post in microsoft... server NG, I keep getting a problem where
my
> DB maintenance plan just stops doing what it should, which in turn means
> that if I don't paranoid-ly check it each day I get the scenario (like
> today) where I find my backups haven't been working for a month!!!
> What I would like to do is email the maintenance plan to a designated emai
l
> address so that I can easily see every morning whether or not it has worke
d.
> I know the old SQL 6.5 used to provide SQL Mail, but this links in with MS
> Exchange server and I don't use this. I don't even have it running on my
> SBS 2000 Server.
> Is there anyway in SQL 2000 that I can get it to email the report via SMTP
?
> I'm familiar with JMail and it's COM/DLL is installed on my server if I
> could use that.
> Thanks Robbie
>
>|||Check out http://www.sqldev.net/xp.htm
HTH...
Joe Webb
SQL Server MVP
~~~
Get up to speed quickly with SQLNS
http://www.amazon.com/exec/obidos/t...il/-/0972688811
Keith Kratochvil wrote:
> First of all, can I suggest that you not use the maintenance plan for
> backups? You can create jobs within the SQL Server Agent that will backup
> your databases. Books Online (within the SQL Server program group) contai
ns
> T-SQL examples of backup scripts. If you are so inclined you can also
> create jobs that perform the appropriate maintenance tasks (such as checkd
b,
> checkcatalog, updateusage, update statistics).
> You could use XPSMTP http://sqldev.net/xp/xpsmtp.htm to send email alerts.
>|||Thanks Guys
"John Bell" <JohnBell@.discussions.microsoft.com> wrote in message
news:2E065D49-E910-48D1-8B7D-1B4FFB55DA73@.microsoft.com...
Hi
Check out http://www.sqldev.net/xp/xpsmtp.htm
Other possible mechanisms might mbe Notification Services or Reporting
Services.
John
"Astra" wrote:

> Hi All
> As per my post in microsoft... server NG, I keep getting a problem where
> my
> DB maintenance plan just stops doing what it should, which in turn means
> that if I don't paranoid-ly check it each day I get the scenario (like
> today) where I find my backups haven't been working for a month!!!
> What I would like to do is email the maintenance plan to a designated
> email
> address so that I can easily see every morning whether or not it has
> worked.
> I know the old SQL 6.5 used to provide SQL Mail, but this links in with MS
> Exchange server and I don't use this. I don't even have it running on my
> SBS 2000 Server.
> Is there anyway in SQL 2000 that I can get it to email the report via
> SMTP?
> I'm familiar with JMail and it's COM/DLL is installed on my server if I
> could use that.
> Thanks Robbie
>
>|||Keith,
I have found this useful as well, so thanks for the tip.
I am having difficulty putting in carriage returns and linefeeds and
wondered if you could help.
This works (names changed!):
exec master.dbo.xp_smtp_sendmail
@.FROM = 'Fred@.mail.com',
@.TO = 'Jim@.mail.com',
@.subject = 'Hello SQL Server SMTP Mail',
@.message = 'Goodbye MAPI and Outlook
@.server = 'ServerIPAddress'
But when I try and add a carriage return and line feed I get 'Incorrect
syntax near '+'' error:
exec master.dbo.xp_smtp_sendmail
@.FROM = 'Fred@.mail.com',
@.TO = 'Jim@.mail.com',
@.subject = 'Hello SQL Server SMTP Mail',
@.message = 'Goodbye MAPI and Outlook' + CHAR(13) + CHAR(10),
@.server = 'ServerIPAddress'
I would welcome some help.
Thanks,
Adrian
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:Oia5I9zGFHA.1172@.TK2MSFTNGP12.phx.gbl...
> First of all, can I suggest that you not use the maintenance plan for
> backups? You can create jobs within the SQL Server Agent that will backup
> your databases. Books Online (within the SQL Server program group)
contains
> T-SQL examples of backup scripts. If you are so inclined you can also
> create jobs that perform the appropriate maintenance tasks (such as
checkdb,
> checkcatalog, updateusage, update statistics).
> You could use XPSMTP http://sqldev.net/xp/xpsmtp.htm to send email alerts.
> --
> Keith
>
> "Astra" <info@.noemail.com> wrote in message
> news:uXPyFCzGFHA.2452@.TK2MSFTNGP10.phx.gbl...
where
> my
> email
> worked.
MS
my
> SMTP?
>|||On Fri, 25 Feb 2005 16:33:12 -0000, Adrian wrote:
(snip)
>But when I try and add a carriage return and line feed I get 'Incorrect
>syntax near '+'' error:
>exec master.dbo.xp_smtp_sendmail
> @.FROM = 'Fred@.mail.com',
> @.TO = 'Jim@.mail.com',
> @.subject = 'Hello SQL Server SMTP Mail',
> @.message = 'Goodbye MAPI and Outlook' + CHAR(13) + CHAR(10),
> @.server = 'ServerIPAddress'
>I would welcome some help.
Hi Adrian,
Try this:
DECLARE @.msg varchar(40)
SET @.msg = 'Goodbye MAPI and Outlook' + CHAR(13) + CHAR(10)
exec master.dbo.xp_smtp_sendmail
@.FROM = 'Fred@.mail.com',
@.TO = 'Jim@.mail.com',
@.subject = 'Hello SQL Server SMTP Mail',
@.message = @.msg,
@.server = 'ServerIPAddress'
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||CHAR(13) + CHAR(10)
should do it
...
select 'test' + CHAR(13) + CHAR(10) + 'ing'
Keith
"Adrian" <NoSpam@.hotmail.com> wrote in message
news:uMM70e1GFHA.588@.TK2MSFTNGP10.phx.gbl...
> Keith,
> I have found this useful as well, so thanks for the tip.
> I am having difficulty putting in carriage returns and linefeeds and
> wondered if you could help.
> This works (names changed!):
> exec master.dbo.xp_smtp_sendmail
> @.FROM = 'Fred@.mail.com',
> @.TO = 'Jim@.mail.com',
> @.subject = 'Hello SQL Server SMTP Mail',
> @.message = 'Goodbye MAPI and Outlook
> @.server = 'ServerIPAddress'
> But when I try and add a carriage return and line feed I get 'Incorrect
> syntax near '+'' error:
> exec master.dbo.xp_smtp_sendmail
> @.FROM = 'Fred@.mail.com',
> @.TO = 'Jim@.mail.com',
> @.subject = 'Hello SQL Server SMTP Mail',
> @.message = 'Goodbye MAPI and Outlook' + CHAR(13) + CHAR(10),
> @.server = 'ServerIPAddress'
> I would welcome some help.
> Thanks,
> Adrian
> "Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
> news:Oia5I9zGFHA.1172@.TK2MSFTNGP12.phx.gbl...
backup
> contains
> checkdb,
alerts.
> where
means
with
> MS
> my
I
>|||Thanks Keith and Hugo.
It works now.
Adrian
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:u00Ti51GFHA.2936@.TK2MSFTNGP15.phx.gbl...
> CHAR(13) + CHAR(10)
> should do it
> ...
> select 'test' + CHAR(13) + CHAR(10) + 'ing'
> --
> Keith
>
> "Adrian" <NoSpam@.hotmail.com> wrote in message
> news:uMM70e1GFHA.588@.TK2MSFTNGP10.phx.gbl...
> backup
> alerts.
> means
(like
designated
> with
on
via
if
> I
>sql

Emailed Reports in Blackberry

Does anyone have a way to get reports that are emailed out via the
Subscriptions service as a WebArchive to appear normally in a
Blackberry? Each element in a row in a table gets put into a new line.
It looks fine in any normal email interface, but not in a Blackberry.
e.g.
Row1Col1 Row1Col2
Row2Col1 Row2Col2
will display in the Blackberry as
Row1Col1
Row1Col2
Row2Col1
Row2Col2
There seems to be a <p> tag in the raw html in each table item.
Any help is much appreciated.On Oct 3, 4:29 pm, TGav <cara...@.gmail.com> wrote:
> Does anyone have a way to get reports that are emailed out via the
> Subscriptions service as a WebArchive to appear normally in a
> Blackberry? Each element in a row in a table gets put into a new line.
> It looks fine in any normal email interface, but not in a Blackberry.
> e.g.
> Row1Col1 Row1Col2
> Row2Col1 Row2Col2
> will display in the Blackberry as
> Row1Col1
> Row1Col2
> Row2Col1
> Row2Col2
> There seems to be a <p> tag in the raw html in each table item.
> Any help is much appreciated.
You might try changing the report format to accommodate the
Blackberry. Also, you might want to look into a different export
option and/or a different type solution that possibly is geared toward
Visual Studio on Mobile Devices. Sorry that I could not be of greater
assistance.
Regards,
Enrique Martinez
Sr. Software Consultant

Email subscription Reported as Successful but not arriving in recipients inbox.

I have a report that is sent via email to a list of recipients. When I look at the subscription in 'My Subscriptions' the value in the Status column states that the message was 'sent successfully', however the email does not appear in the inboxes of any of the intended recipents. The report renders OK via the browser and only takes a few seconds to process. The subscription exports the report as an Excel file. The excel file is approx 8MB in size but this is still below the attachment size limit for internal emails within my organisation. The subscription is tied to a shared schedule. Other reports that are tied to the shared schedule are shown as successful in 'My Subscriptions' and also appear in the inboxes of recipients. All email address are correct.

I forgot to mention, the report was successfully received by all recipients until very recently and the report has not been modified since this time.

Has anyone come across this problem before.

Thanks

Mike

The Reporting Services e-mail delivery extension considers "sent successfully" to be "I handed it off to some SMTP forwarder". What happens to the e-mail after that is outside the scope of RS. You need to investigate within the e-mail system.

As a diagnostic, you might try delivering to an SMTP pickup folder temporarily to convince yourself that the Report Server is generating the e-mails. Find the rsReportServer.config file, change the "SendUsing" element to 1, specify some SMTPServerPickupDirectory, and try the delivery. If you get .EML files created in the specified folder, RS has done it's part of the delivery. Make sure you set SendUsing back to 2 or <blank> after you've done your test.

Here's a sample of the config file section you need to modify:

<RSEmailDPConfiguration>
<SMTPServer></SMTPServer>
<SMTPServerPort></SMTPServerPort>
<SMTPAccountName></SMTPAccountName>
<SMTPConnectionTimeout></SMTPConnectionTimeout>
<SMTPServerPickupDirectory>D:\Email</SMTPServerPickupDirectory>
<SMTPUseSSL></SMTPUseSSL>
<SendUsing>1</SendUsing>
<SMTPAuthenticate></SMTPAuthenticate>
<From>_awtest@.Microsoft.Com</From>
<EmbeddedRenderFormats>
<RenderingExtension>MHTML</RenderingExtension>
</EmbeddedRenderFormats>
<PrivilegedUserRenderFormats></PrivilegedUserRenderFormats>
<ExcludedRenderFormats>
<RenderingExtension>HTMLOWC</RenderingExtension>
<RenderingExtension>NULL</RenderingExtension>
<RenderingExtension>RGDI</RenderingExtension>
</ExcludedRenderFormats>
<SendEmailToUserAlias>True</SendEmailToUserAlias>
<DefaultHostName></DefaultHostName>
<PermittedHosts></PermittedHosts>
</RSEmailDPConfiguration>

Email subscription error on credentials

Hello,

When trying to subscribe to an report via email I get to following error

The current action cannot be completed because the user data source credentials that are required to execute this report are not stored in the report server database. (rsInvalidDataSourceCredentialSetting)

Can anybody help me with this issue.

Greetings

Vinnie

The username and password need to be stored on the server.

The report is trying to connect to the db with the datasource, but you have not supplied any credentials.

In the data source properties, enter the username and password you wish the RS to connect to the db with.|||

hi,

thanks for the quick response.

I get what you mean, but why does the reportserver does not ask for those credentials when i generate the report

with the reportviewer, but only for a subscription?

Does this mean that i have to specify username and password in my datasource for my reports (in the credentials tab)

Greetings

Vinnie

|||When you are running the report through reportviewer, it already has your credentials.
When it is running as a subscription, it is essentially running 'unattended' so it cannot validate whether it can connect to the db.

By embedding your credentials, it can check if it has permissions to run the report, just as if you were logged on to your machine and trying to run it through IE/report viewer

Email reports based on a condition

Hi,

Data is fed to our database from 10 different places. We massage the data and then send out reports via email with rs subscriptions.

Everything works fine except when at least one of the data feeds does not load properly on time. The reports go out but with uncomplete data generating undesired effect in management.

I can create a "Loaded" flag on the database. Is there a way to use this or other method to send out the reports based on a condition?

Thank you.

One more thing, we only have the standard edition of rs, not the enterprise edition where you can use data-driven subscription.

In the meantime, we thought of a possible solution: Create a status table and new dataset in rs. Based on the status of the day, hide or make visible detail and header rows of the report. The same idea apply to a text box stating the status of the loads.

|||

I did some research on that subject for SQL2000 RPS.

Peter Blackburn had a solution, here is his message as well as my comments.

It should still work with 2005

For the condition, you can set a step to test your "Status" table ad success only when your data is ready and fire the report job.

Have fun,

Philippe

-

Hi,

Here the trick is to use SP_Start_Job to start the report from a sp or a dts.

You can also use something like exec ReportServer.dbo.AddEvent @.EventType='TimedSubscription', @.EventData='6273f0b2-3899-416c-ada7-29c801a2c16f'

in your sp or DTS.

As Peter says, this breaks when you edit the subscription schedule from ReportServer but still, you can do it and you can also set a test like

DECLARE @.JobID BINARY(16)

SELECT @.JobID = job_id
FROM msdb.dbo.sysjobs
WHERE (name = N'EF3028CF-D22A-4EA7-B197-D9018D6BA262')
IF (@.JobID IS NULL)
BEGIN

-- send some sort of alert to the developer so he recreates the correct calls....

EXEC msdb.dbo.sp_send_dbmail

@.recipients = 'someone@.someplace.com',

@.body = 'The report BlahBlah EF3028CF-D22A-4EA7-B197-D9018D6BA262 could not run, it may have been updated on the report server, sorry.',

@.subject = 'Report could not run';

else

-- run the report

exec ReportServer.dbo.AddEvent @.EventType='TimedSubscription', @.EventData='6273f0b2-3899-416c-ada7-29c801a2c16f'

end

This is not optimal however this may be very handful for reports where you absolutely need it.

When you set your initial schedule in the past, set a time easy to find in the job list.

Phil

Subject: Re: Scheduling a Report based on an event

11/9/2004 10:38 PM PST

By:

Peter Blackburn (www.sqlreportingservice

In:

microsoft.public.sqlserver.reportingsvcs

Was this post helpful to you?

Sure this is real easy to do.Create a schedule that has completed in the past - so effectively it will never fire. Associate this schedule with a Report.Now what happens is that a SQL Agent Job is created - that maps to the schedule. You can run SQL Agent Jobs from the SQL Agent Management interface by hand - or you can cause that job to run through T-SQL.All that the SQL Agent Job does is create an entry in the Report Server's Event table at the scheduled time. The Report Server Windows Service is polling the Event table every 10 seconds or so - and if there are any events to process it gets on and processes them.So what you do is either include in your long running stored procedure a call that will create the required entry in the Event table directly - or a call that fires the SQL Agent Job.- One word of warning though if you start editing the schedule in the Report Manager, then the Report Manager can end up re-creating the SQL Agent Jobs - and you lose reference to the actual Job.However if you are disciplined enough then this approach works fine - (Schedule in the past, have your own process force the SQL Agent Job to run)Peter BlackburnHitchhiker's Guide to SQL Server 2000 Reporting Serviceshttp://www.sqlreportingservices.net"SqlJunkies User" <User@.-NOSPAM-SqlJunkies.com> wrote in message news:OWMxVAoxEHA.3080@.TK2MSFTNGP14.phx.gbl...> Is it possible to schedule a report based on a flag or stored procedure > completing. Currently have an overnight load process which must complete > before the report starts. Any suggestions would be most appreciated>> > Posted using Wimdows.net NntpNews Component -

|||

Thank you for your response.

We got the results we wanted by creating a load_status table. In my report I created a new dataset that looks up for the flag value on this table.

Then we condition the visibility of each row of the table based on this flag. We added another header row with the "sorry, not all data was loaded, report will be sent later" with the opposite condition and it works great.

Good luck!

Email on demand

Looking for an example of sending a 2005 RS report via a web page on demand.
I would need to fire it when a user clicked a button on the web form.
In the old days, in plain old ASP, we used to build an HTML formatted email
body and then fire it off using CDO. We could still do that with ASPX but
I'd much prefer to build a report in RS (more formatting and doc type options)
and then fire it off.
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200710/1On Oct 3, 1:07 pm, "wnichols via SQLMonster.com" <u3357@.uwe> wrote:
> Looking for an example of sending a 2005 RS report via a web page on demand.
> I would need to fire it when a user clicked a button on the web form.
> In the old days, in plain old ASP, we used to build an HTML formatted email
> body and then fire it off using CDO. We could still do that with ASPX but
> I'd much prefer to build a report in RS (more formatting and doc type options)
> and then fire it off.
> --
> Message posted via SQLMonster.comhttp://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200710/1
This link should help.
http://msdn2.microsoft.com/en-us/library/microsoft.wssux.reportingserviceswebservice.rsexecutionservice2005.reportexecutionservice.render.aspx
You will want to create a Proxy (Web Reference) to
ReportingService2005.asmx. Then attach the newly created file to an
email created via ASP.NET. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||Enrique,
Thanks for the info.
EMartinez wrote:
>> Looking for an example of sending a 2005 RS report via a web page on demand.
>> I would need to fire it when a user clicked a button on the web form.
>[quoted text clipped - 6 lines]
>> --
>> Message posted via SQLMonster.comhttp://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200710/1
>This link should help.
>http://msdn2.microsoft.com/en-us/library/microsoft.wssux.reportingserviceswebservice.rsexecutionservice2005.reportexecutionservice.render.aspx
>You will want to create a Proxy (Web Reference) to
>ReportingService2005.asmx. Then attach the newly created file to an
>email created via ASP.NET. Hope this helps.
>Regards,
>Enrique Martinez
>Sr. Software Consultant
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200710/1|||On Oct 4, 11:04 am, "wnichols via SQLMonster.com" <u3357@.uwe> wrote:
> Enrique,
> Thanks for the info.
>
> EMartinez wrote:
> >> Looking for an example of sending a 2005 RS report via a web page on demand.
> >> I would need to fire it when a user clicked a button on the web form.
> >[quoted text clipped - 6 lines]
> >> --
> >> Message posted via SQLMonster.comhttp://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200710/1
> >This link should help.
> >http://msdn2.microsoft.com/en-us/library/microsoft.wssux.reportingser...
> >You will want to create a Proxy (Web Reference) to
> >ReportingService2005.asmx. Then attach the newly created file to an
> >email created via ASP.NET. Hope this helps.
> >Regards,
> >Enrique Martinez
> >Sr. Software Consultant
> --
> Message posted via SQLMonster.comhttp://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200710/1
You're welcome. Let me know if I can be of further assistance.
Regards,
Enrique Martinez
Sr. Software Consultant

Email format for Reporting Services

Hi, this question is related to Reporting Services on SQL Server 2000. When creating a report and setting it to send via email as an attached excel spreadsheet, the email is in the format HTML. Does anyone know if it possible to default this format to something else like Rich Text. I have a feeling that this might involve a change to the RSEmailDPConfiguration within the RSReportServer.config file but am not sure which tag needs changing or adding.

Any help/suggestions greatfully received.According to documentation:

rsreportserver.config

EmbeddedRenderFormats

Specifies the rendering format used to encapsulate a report within the body

of an e-mail message. Images within the report are subsequently embedded within

the report. Valid values are MHTML and HTML4.0.

Does not look like you'll get Rich Text in an email without some custom code.

cheers,
Andrew|||

RTF is not supported by out-of-the-box RS. Theoretically you can write your own renderer but it's a difficult task, plus then you'll need to write custom delivery provider to support it as well. Support for RTF is in the wish list for the next RS version.

|||Thanks for both of your comments. I had a feelling it might be a custom option.

EM vs QA login

This morning I have a (2000) server that continually
times out when I try to connect via Ent. Manager. It is
registred through an sa Sql Server login that has not
changed in months. Also, I upped the login timeout in EM
to 10 seconds and it still timed out.
The odd thin is this: I can connect instantly through
Query Analyzer through this same sa login and the server
looks very quiet in Perf Mon.
Is there something different about how Sql Server logins
through Enterprise Manager? Any ideas what would cause
this?
The login process should be the same. Though, EM will try to execute a bunch
of queries after a successful login to get server/database properties.
Try up the timeout to at least 60 seconds.
-oj
"Stsh" <anonymous@.discussions.microsoft.com> wrote in message
news:057301c53ebe$672f0410$a501280a@.phx.gbl...
> This morning I have a (2000) server that continually
> times out when I try to connect via Ent. Manager. It is
> registred through an sa Sql Server login that has not
> changed in months. Also, I upped the login timeout in EM
> to 10 seconds and it still timed out.
> The odd thin is this: I can connect instantly through
> Query Analyzer through this same sa login and the server
> looks very quiet in Perf Mon.
> Is there something different about how Sql Server logins
> through Enterprise Manager? Any ideas what would cause
> this?

EM vs QA login

This morning I have a (2000) server that continually
times out when I try to connect via Ent. Manager. It is
registred through an sa Sql Server login that has not
changed in months. Also, I upped the login timeout in EM
to 10 seconds and it still timed out.
The odd thin is this: I can connect instantly through
Query Analyzer through this same sa login and the server
looks very quiet in Perf Mon.
Is there something different about how Sql Server logins
through Enterprise Manager? Any ideas what would cause
this?The login process should be the same. Though, EM will try to execute a bunch
of queries after a successful login to get server/database properties.
Try up the timeout to at least 60 seconds.
--
-oj
"Stsh" <anonymous@.discussions.microsoft.com> wrote in message
news:057301c53ebe$672f0410$a501280a@.phx.gbl...
> This morning I have a (2000) server that continually
> times out when I try to connect via Ent. Manager. It is
> registred through an sa Sql Server login that has not
> changed in months. Also, I upped the login timeout in EM
> to 10 seconds and it still timed out.
> The odd thin is this: I can connect instantly through
> Query Analyzer through this same sa login and the server
> looks very quiet in Perf Mon.
> Is there something different about how Sql Server logins
> through Enterprise Manager? Any ideas what would cause
> this?

EM vs QA login

This morning I have a (2000) server that continually
times out when I try to connect via Ent. Manager. It is
registred through an sa Sql Server login that has not
changed in months. Also, I upped the login timeout in EM
to 10 seconds and it still timed out.
The odd thin is this: I can connect instantly through
Query Analyzer through this same sa login and the server
looks very quiet in Perf Mon.
Is there something different about how Sql Server logins
through Enterprise Manager? Any ideas what would cause
this?The login process should be the same. Though, EM will try to execute a bunch
of queries after a successful login to get server/database properties.
Try up the timeout to at least 60 seconds.
-oj
"Stsh" <anonymous@.discussions.microsoft.com> wrote in message
news:057301c53ebe$672f0410$a501280a@.phx.gbl...
> This morning I have a (2000) server that continually
> times out when I try to connect via Ent. Manager. It is
> registred through an sa Sql Server login that has not
> changed in months. Also, I upped the login timeout in EM
> to 10 seconds and it still timed out.
> The odd thin is this: I can connect instantly through
> Query Analyzer through this same sa login and the server
> looks very quiet in Perf Mon.
> Is there something different about how Sql Server logins
> through Enterprise Manager? Any ideas what would cause
> this?

EM showing (No Items)

Having made no changes to the database server we are now getting no
database list when connecting via EM from client machines, as well as
locally on the database server. I've researched the problem to no
success. The database list shows up fine in QA, however. I used the
following script in QA:
use master
go
select * from sysdatabases
go
And this is the error message I received:
(36 row(s) affected)
Server: Msg 220, Level 16, State 1, Line 1
Arithmetic overflow error for data type smallint, value = 51805.
I saw a similar message when trying to delete an outdated Login
account.
Anyone have any ideas? Thanks in advance!
Jon Brookins
Paragon New Media, Inc.What version? Have you run DBCC CHECKDB recently?
"Jon Brookins" <jonathan@.pnmi.com> wrote in message
news:fa11c6b4.0310101200.58233742@.posting.google.com...
> Having made no changes to the database server we are now getting no
> database list when connecting via EM from client machines, as well as
> locally on the database server. I've researched the problem to no
> success. The database list shows up fine in QA, however. I used the
> following script in QA:
> use master
> go
> select * from sysdatabases
> go
> And this is the error message I received:
>
> (36 row(s) affected)
> Server: Msg 220, Level 16, State 1, Line 1
> Arithmetic overflow error for data type smallint, value = 51805.
> I saw a similar message when trying to delete an outdated Login
> account.
> Anyone have any ideas? Thanks in advance!
> Jon Brookins
> Paragon New Media, Inc.|||CHECKDB sounds like a good suggestion. Also, you might want to prepare yourself for restoring a
clean backup of the master database.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Jon Brookins" <jonathan@.pnmi.com> wrote in message
news:fa11c6b4.0310101200.58233742@.posting.google.com...
> Having made no changes to the database server we are now getting no
> database list when connecting via EM from client machines, as well as
> locally on the database server. I've researched the problem to no
> success. The database list shows up fine in QA, however. I used the
> following script in QA:
> use master
> go
> select * from sysdatabases
> go
> And this is the error message I received:
>
> (36 row(s) affected)
> Server: Msg 220, Level 16, State 1, Line 1
> Arithmetic overflow error for data type smallint, value = 51805.
> I saw a similar message when trying to delete an outdated Login
> account.
> Anyone have any ideas? Thanks in advance!
> Jon Brookins
> Paragon New Media, Inc.

Efficiency of INSERT with multiple rows

Let's say that I am inserting rows into a table via a statement like this
INSERT INTO A SELECT * FROM B
And let's say that table B has 1,000,000 rows
I was just wondering if SQL Server rebuilds the indexes on table A after
each row is inserted or if it waits until all 1,000,000 rows are inserted
and then rebuilds the indexes at the end? If it rebuilds the indexes upon
each insert then I would probably drop the indexes first and then just add
them at the end.
Does anybody know?
Thanks
Richard Speiss"Richard Speiss" <rspeiss@.mtxinc.com> wrote in message
news:utEtTH6NEHA.2468@.TK2MSFTNGP11.phx.gbl...
> Let's say that I am inserting rows into a table via a statement like this
> INSERT INTO A SELECT * FROM B
> And let's say that table B has 1,000,000 rows
> I was just wondering if SQL Server rebuilds the indexes on table A after
> each row is inserted or if it waits until all 1,000,000 rows are inserted
> and then rebuilds the indexes at the end? If it rebuilds the indexes upon
> each insert then I would probably drop the indexes first and then just add
> them at the end.
THe index is not 'rebuild' upon each insert. It is updated on each insert.
Since 'table splits' on indexes can be costly, it is sometimes, a good idea
to remove the index.
But that depends, if you have a very large table with bilions of rows and
remove and reapply the index :<<
ps: For batch inserting and bulking rows, always initiate an explicit
transation!

> Does anybody know?
> Thanks
> Richard Speiss
>|||Oops, I did mean does the index get updated (bad wording on my part. I
didn't expect the entire thing to be rebuilt).
Thanks for the reply
Richard

> THe index is not 'rebuild' upon each insert. It is updated on each insert.
> Since 'table splits' on indexes can be costly, it is sometimes, a good
idea
> to remove the index.
> But that depends, if you have a very large table with bilions of rows and
> remove and reapply the index :<<
> ps: For batch inserting and bulking rows, always initiate an explicit
> transation!
>|||Richard,
SQL Server does not rebuild the indexes as data is inserted, rather the
indexes are "maintained". i.e. if you insert a new row into the table, then
all indexes on the table will need to have the index pages maintained at the
same time.
Sometimes you will find that dropping all indexes, doing a large insert, and
then creating the index after the load is quicker than doing the load with
the indexes defined. Sometimes you won't. Only testing will reveal which way
will be quick for your environment.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
"Richard Speiss" <rspeiss@.mtxinc.com> wrote in message
news:utEtTH6NEHA.2468@.TK2MSFTNGP11.phx.gbl...
> Let's say that I am inserting rows into a table via a statement like this
> INSERT INTO A SELECT * FROM B
> And let's say that table B has 1,000,000 rows
> I was just wondering if SQL Server rebuilds the indexes on table A after
> each row is inserted or if it waits until all 1,000,000 rows are inserted
> and then rebuilds the indexes at the end? If it rebuilds the indexes upon
> each insert then I would probably drop the indexes first and then just add
> them at the end.
> Does anybody know?
> Thanks
> Richard Speiss
>

Efficiency of INSERT with multiple rows

Let's say that I am inserting rows into a table via a statement like this
INSERT INTO A SELECT * FROM B
And let's say that table B has 1,000,000 rows
I was just wondering if SQL Server rebuilds the indexes on table A after
each row is inserted or if it waits until all 1,000,000 rows are inserted
and then rebuilds the indexes at the end? If it rebuilds the indexes upon
each insert then I would probably drop the indexes first and then just add
them at the end.
Does anybody know?
Thanks
Richard Speiss
"Richard Speiss" <rspeiss@.mtxinc.com> wrote in message
news:utEtTH6NEHA.2468@.TK2MSFTNGP11.phx.gbl...
> Let's say that I am inserting rows into a table via a statement like this
> INSERT INTO A SELECT * FROM B
> And let's say that table B has 1,000,000 rows
> I was just wondering if SQL Server rebuilds the indexes on table A after
> each row is inserted or if it waits until all 1,000,000 rows are inserted
> and then rebuilds the indexes at the end? If it rebuilds the indexes upon
> each insert then I would probably drop the indexes first and then just add
> them at the end.
THe index is not 'rebuild' upon each insert. It is updated on each insert.
Since 'table splits' on indexes can be costly, it is sometimes, a good idea
to remove the index.
But that depends, if you have a very large table with bilions of rows and
remove and reapply the index :<<
ps: For batch inserting and bulking rows, always initiate an explicit
transation!

> Does anybody know?
> Thanks
> Richard Speiss
>
|||Oops, I did mean does the index get updated (bad wording on my part. I
didn't expect the entire thing to be rebuilt).
Thanks for the reply
Richard

> THe index is not 'rebuild' upon each insert. It is updated on each insert.
> Since 'table splits' on indexes can be costly, it is sometimes, a good
idea
> to remove the index.
> But that depends, if you have a very large table with bilions of rows and
> remove and reapply the index :<<
> ps: For batch inserting and bulking rows, always initiate an explicit
> transation!
>
|||Richard,
SQL Server does not rebuild the indexes as data is inserted, rather the
indexes are "maintained". i.e. if you insert a new row into the table, then
all indexes on the table will need to have the index pages maintained at the
same time.
Sometimes you will find that dropping all indexes, doing a large insert, and
then creating the index after the load is quicker than doing the load with
the indexes defined. Sometimes you won't. Only testing will reveal which way
will be quick for your environment.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
"Richard Speiss" <rspeiss@.mtxinc.com> wrote in message
news:utEtTH6NEHA.2468@.TK2MSFTNGP11.phx.gbl...
> Let's say that I am inserting rows into a table via a statement like this
> INSERT INTO A SELECT * FROM B
> And let's say that table B has 1,000,000 rows
> I was just wondering if SQL Server rebuilds the indexes on table A after
> each row is inserted or if it waits until all 1,000,000 rows are inserted
> and then rebuilds the indexes at the end? If it rebuilds the indexes upon
> each insert then I would probably drop the indexes first and then just add
> them at the end.
> Does anybody know?
> Thanks
> Richard Speiss
>

Efficiency of INSERT with multiple rows

Let's say that I am inserting rows into a table via a statement like this
INSERT INTO A SELECT * FROM B
And let's say that table B has 1,000,000 rows
I was just wondering if SQL Server rebuilds the indexes on table A after
each row is inserted or if it waits until all 1,000,000 rows are inserted
and then rebuilds the indexes at the end? If it rebuilds the indexes upon
each insert then I would probably drop the indexes first and then just add
them at the end.
Does anybody know?
Thanks
Richard Speiss"Richard Speiss" <rspeiss@.mtxinc.com> wrote in message
news:utEtTH6NEHA.2468@.TK2MSFTNGP11.phx.gbl...
> Let's say that I am inserting rows into a table via a statement like this
> INSERT INTO A SELECT * FROM B
> And let's say that table B has 1,000,000 rows
> I was just wondering if SQL Server rebuilds the indexes on table A after
> each row is inserted or if it waits until all 1,000,000 rows are inserted
> and then rebuilds the indexes at the end? If it rebuilds the indexes upon
> each insert then I would probably drop the indexes first and then just add
> them at the end.
THe index is not 'rebuild' upon each insert. It is updated on each insert.
Since 'table splits' on indexes can be costly, it is sometimes, a good idea
to remove the index.
But that depends, if you have a very large table with bilions of rows and
remove and reapply the index :<<
ps: For batch inserting and bulking rows, always initiate an explicit
transation!
> Does anybody know?
> Thanks
> Richard Speiss
>|||Oops, I did mean does the index get updated (bad wording on my part. I
didn't expect the entire thing to be rebuilt).
Thanks for the reply
Richard
> THe index is not 'rebuild' upon each insert. It is updated on each insert.
> Since 'table splits' on indexes can be costly, it is sometimes, a good
idea
> to remove the index.
> But that depends, if you have a very large table with bilions of rows and
> remove and reapply the index :<<
> ps: For batch inserting and bulking rows, always initiate an explicit
> transation!
>|||Richard,
SQL Server does not rebuild the indexes as data is inserted, rather the
indexes are "maintained". i.e. if you insert a new row into the table, then
all indexes on the table will need to have the index pages maintained at the
same time.
Sometimes you will find that dropping all indexes, doing a large insert, and
then creating the index after the load is quicker than doing the load with
the indexes defined. Sometimes you won't. Only testing will reveal which way
will be quick for your environment.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
"Richard Speiss" <rspeiss@.mtxinc.com> wrote in message
news:utEtTH6NEHA.2468@.TK2MSFTNGP11.phx.gbl...
> Let's say that I am inserting rows into a table via a statement like this
> INSERT INTO A SELECT * FROM B
> And let's say that table B has 1,000,000 rows
> I was just wondering if SQL Server rebuilds the indexes on table A after
> each row is inserted or if it waits until all 1,000,000 rows are inserted
> and then rebuilds the indexes at the end? If it rebuilds the indexes upon
> each insert then I would probably drop the indexes first and then just add
> them at the end.
> Does anybody know?
> Thanks
> Richard Speiss
>