Embedding multiple .sql files

How do I write a .sql script that executes a number of other scripts in .sql
files? This has to be easy. I just can't find it.
Thanks,
JerryRYou can use oSql. See if this helps:
http://www.sql-server-performance.com/rd_osql.asp
Andrew J. Kelly SQL MVP
"JerryR" <JerryR@.discussions.microsoft.com> wrote in message
news:76549937-4F1E-41B5-B148-A548F0B8CA95@.microsoft.com...
> How do I write a .sql script that executes a number of other scripts in
> .sql
> files? This has to be easy. I just can't find it.
> Thanks,
> JerryR|||There is no such feature in SQL like this in Oracle @.Somefile.sql. You
have to put in in a OS batch to execute it via a cmdshell program like
SQLCMD or OSQL or ISQL.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--|||Yes, that's what I was looking for.
Thanks
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1143927815.456011.236990@.v46g2000cwv.googlegroups.com...
> There is no such feature in SQL like this in Oracle @.Somefile.sql. You
> have to put in in a OS batch to execute it via a cmdshell program like
> SQLCMD or OSQL or ISQL.
>
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>

Embed VBS or Call VBS from Job Script

How do you call an external VBS script from within a T-SQL Job?
At the end of a job I want a VBS to fire off in the last step.
Not sure how to do this. Looked everywhere ... :)Typically, T-SQL (Execute SQL Task) and VBS (ActiveX Script Task) are
included in a DTS Package, which is then scheduled for execution using a
job.
http://msdn.microsoft.com/library/d... />
x_06er.asp
"Whispering Leaf" <WhisperingLeaf@.discussions.microsoft.com> wrote in
message news:63FFAF27-72B1-45B6-A326-5DCDFED2735A@.microsoft.com...
> How do you call an external VBS script from within a T-SQL Job?
> At the end of a job I want a VBS to fire off in the last step.
> Not sure how to do this. Looked everywhere ... :)

emailsubscription - eventpolling, but no notificationpolling

Hi,
>From script i'm sending a few emails with pdf attachments.
One report is mailed, two others not. The script for creating the
subscriptions is equal, only different parameters.
When I look in the logfiles I don't see any errors, however only the
succeeded report has both event- and notificationpolling logging.
So the logging for the succeeded one is:
ReportingServicesService!dbpolling!1240!04/30/2005-05:49:52::
EventPolling processing item f21fb887-9e00-40c5-8fac-f246ad14bd4f
ReportingServicesService!dbpolling!194!30.04.2005-05:49:52::
EventPolling processing 1 more items. 1 Total items in internal queue.
ReportingServicesService!library!1240!04/30/2005-05:49:53:: Schedule
3461442c-d51f-4701-9413-a5bef0d283a0 executed at 04/30/2005 05:49:53.
ReportingServicesService!schedule!1240!04/30/2005-05:49:53:: Creating
Time based subscription notification for subscription:
3424547a-9e5a-4e74-8bd1-78af62a38490
ReportingServicesService!library!1240!04/30/2005-05:49:53:: Schedule
3461442c-d51f-4701-9413-a5bef0d283a0 execution completed at 04/30/2005
05:49:53.
ReportingServicesService!dbpolling!1240!04/30/2005-05:49:53::
EventPolling finished processing item
f21fb887-9e00-40c5-8fac-f246ad14bd4f
ReportingServicesService!dbpolling!194!30.04.2005-05:49:53::
EventPolling processing 1 more items. 1 Total items in internal queue.
ReportingServicesService!dbpolling!1240!04/30/2005-05:49:53::
EventPolling processing item 351c51cd-2274-4218-b92e-f05ebdcc0bcb
ReportingServicesService!dbpolling!1240!04/30/2005-05:49:53::
EventPolling finished processing item
351c51cd-2274-4218-b92e-f05ebdcc0bcb
ReportingServicesService!dbpolling!194!30.04.2005-05:49:54::
NotificationPolling processing 1 more items. 1 Total items in internal
queue.
ReportingServicesService!dbpolling!1240!04/30/2005-05:49:54::
NotificationPolling processing item
72b719c2-1ae2-4e7a-a096-b15364e8ebd4
ReportingServicesService!library!1240!04/30/2005-05:49:55:: i INFO:
Call to RenderFirst( '/KEAS/KEASReports/Analyse' )
ReportingServicesService!library!1240!04/30/2005-05:49:56:: i INFO:
Initializing EnableExecutionLogging to 'True' as specified in Server
system properties.
ReportingServicesService!emailextension!1240!04/30/2005-05:49:57::
Email successfully sent to "spitz@.tiscali.nl" <spitz@.tiscali.nl>
ReportingServicesService!library!1240!04/30/2005-05:49:57:: Data Driven
Notification for activation id eff41ff5-c76d-4ced-be63-377e82daf4bb was
saved.
ReportingServicesService!library!1240!04/30/2005-05:49:57:: Status:
Mail sent to spitz@.tiscali.nl;
ReportingServicesService!notification!1240!04/30/2005-05:49:57::
Notification 72b719c2-1ae2-4e7a-a096-b15364e8ebd4 completed. Success:
True, Status: Mail sent to spitz@.tiscali.nl;, DeliveryExtension: Report
Server Email, Report: Analyse, Attempt 0
ReportingServicesService!dbpolling!1240!04/30/2005-05:49:57::
NotificationPolling finished processing item
72b719c2-1ae2-4e7a-a096-b15364e8ebd4
The logging for the ones that are not mailed (the subscriptions are
created):
ReportingServicesService!dbpolling!194!30.04.2005-05:48:52::
EventPolling processing 1 more items. 1 Total items in internal queue.
ReportingServicesService!dbpolling!1240!04/30/2005-05:48:52::
EventPolling processing item a3d3a5e0-4634-4f32-ab10-8564fa09be40
ReportingServicesService!library!1240!04/30/2005-05:48:52:: Schedule
54043fb7-5eec-4063-ae6e-a0ed054e538c executed at 04/30/2005 05:48:52.
ReportingServicesService!schedule!1240!04/30/2005-05:48:52:: Creating
Time based subscription notification for subscription:
9e5701ab-2fc5-48ab-8290-ae05f55fb29f
ReportingServicesService!library!1240!04/30/2005-05:48:53:: Schedule
54043fb7-5eec-4063-ae6e-a0ed054e538c execution completed at 04/30/2005
05:48:53.
ReportingServicesService!dbpolling!194!30.04.2005-05:48:53::
EventPolling processing 1 more items. 1 Total items in internal queue.
ReportingServicesService!dbpolling!1240!04/30/2005-05:48:53::
EventPolling finished processing item
a3d3a5e0-4634-4f32-ab10-8564fa09be40
ReportingServicesService!dbpolling!1240!04/30/2005-05:48:53::
EventPolling processing item 7efce5e2-8cca-4e87-9ac9-f2bac7ad68ff
ReportingServicesService!dbpolling!1240!04/30/2005-05:48:53::
EventPolling finished processing item
7efce5e2-8cca-4e87-9ac9-f2bac7ad68ff
The securitysettings for all reports are equal.
The failed jobs are also not mailed if started from sqlagent (although
they succeed as sqlagentjob)
Any suggestions are more than welcome, since i have no clue how to
solve this!
Regards jespHave you tried viewing the subscriptions through Report Manager? Perhaps
something is not set up correctly causing the subscription to not fire
correctly.
Have you tried creating the same subscriptions through Report Manager?
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"jesp" <spitz@.tiscali.nl> wrote in message
news:1115007058.978980.232660@.o13g2000cwo.googlegroups.com...
> Hi,
>>From script i'm sending a few emails with pdf attachments.
> One report is mailed, two others not. The script for creating the
> subscriptions is equal, only different parameters.
> When I look in the logfiles I don't see any errors, however only the
> succeeded report has both event- and notificationpolling logging.
> So the logging for the succeeded one is:
> ReportingServicesService!dbpolling!1240!04/30/2005-05:49:52::
> EventPolling processing item f21fb887-9e00-40c5-8fac-f246ad14bd4f
> ReportingServicesService!dbpolling!194!30.04.2005-05:49:52::
> EventPolling processing 1 more items. 1 Total items in internal queue.
> ReportingServicesService!library!1240!04/30/2005-05:49:53:: Schedule
> 3461442c-d51f-4701-9413-a5bef0d283a0 executed at 04/30/2005 05:49:53.
> ReportingServicesService!schedule!1240!04/30/2005-05:49:53:: Creating
> Time based subscription notification for subscription:
> 3424547a-9e5a-4e74-8bd1-78af62a38490
> ReportingServicesService!library!1240!04/30/2005-05:49:53:: Schedule
> 3461442c-d51f-4701-9413-a5bef0d283a0 execution completed at 04/30/2005
> 05:49:53.
> ReportingServicesService!dbpolling!1240!04/30/2005-05:49:53::
> EventPolling finished processing item
> f21fb887-9e00-40c5-8fac-f246ad14bd4f
> ReportingServicesService!dbpolling!194!30.04.2005-05:49:53::
> EventPolling processing 1 more items. 1 Total items in internal queue.
> ReportingServicesService!dbpolling!1240!04/30/2005-05:49:53::
> EventPolling processing item 351c51cd-2274-4218-b92e-f05ebdcc0bcb
> ReportingServicesService!dbpolling!1240!04/30/2005-05:49:53::
> EventPolling finished processing item
> 351c51cd-2274-4218-b92e-f05ebdcc0bcb
> ReportingServicesService!dbpolling!194!30.04.2005-05:49:54::
> NotificationPolling processing 1 more items. 1 Total items in internal
> queue.
> ReportingServicesService!dbpolling!1240!04/30/2005-05:49:54::
> NotificationPolling processing item
> 72b719c2-1ae2-4e7a-a096-b15364e8ebd4
> ReportingServicesService!library!1240!04/30/2005-05:49:55:: i INFO:
> Call to RenderFirst( '/KEAS/KEASReports/Analyse' )
> ReportingServicesService!library!1240!04/30/2005-05:49:56:: i INFO:
> Initializing EnableExecutionLogging to 'True' as specified in Server
> system properties.
> ReportingServicesService!emailextension!1240!04/30/2005-05:49:57::
> Email successfully sent to "spitz@.tiscali.nl" <spitz@.tiscali.nl>
> ReportingServicesService!library!1240!04/30/2005-05:49:57:: Data Driven
> Notification for activation id eff41ff5-c76d-4ced-be63-377e82daf4bb was
> saved.
> ReportingServicesService!library!1240!04/30/2005-05:49:57:: Status:
> Mail sent to spitz@.tiscali.nl;
> ReportingServicesService!notification!1240!04/30/2005-05:49:57::
> Notification 72b719c2-1ae2-4e7a-a096-b15364e8ebd4 completed. Success:
> True, Status: Mail sent to spitz@.tiscali.nl;, DeliveryExtension: Report
> Server Email, Report: Analyse, Attempt 0
> ReportingServicesService!dbpolling!1240!04/30/2005-05:49:57::
> NotificationPolling finished processing item
> 72b719c2-1ae2-4e7a-a096-b15364e8ebd4
> The logging for the ones that are not mailed (the subscriptions are
> created):
> ReportingServicesService!dbpolling!194!30.04.2005-05:48:52::
> EventPolling processing 1 more items. 1 Total items in internal queue.
> ReportingServicesService!dbpolling!1240!04/30/2005-05:48:52::
> EventPolling processing item a3d3a5e0-4634-4f32-ab10-8564fa09be40
> ReportingServicesService!library!1240!04/30/2005-05:48:52:: Schedule
> 54043fb7-5eec-4063-ae6e-a0ed054e538c executed at 04/30/2005 05:48:52.
> ReportingServicesService!schedule!1240!04/30/2005-05:48:52:: Creating
> Time based subscription notification for subscription:
> 9e5701ab-2fc5-48ab-8290-ae05f55fb29f
> ReportingServicesService!library!1240!04/30/2005-05:48:53:: Schedule
> 54043fb7-5eec-4063-ae6e-a0ed054e538c execution completed at 04/30/2005
> 05:48:53.
> ReportingServicesService!dbpolling!194!30.04.2005-05:48:53::
> EventPolling processing 1 more items. 1 Total items in internal queue.
> ReportingServicesService!dbpolling!1240!04/30/2005-05:48:53::
> EventPolling finished processing item
> a3d3a5e0-4634-4f32-ab10-8564fa09be40
> ReportingServicesService!dbpolling!1240!04/30/2005-05:48:53::
> EventPolling processing item 7efce5e2-8cca-4e87-9ac9-f2bac7ad68ff
> ReportingServicesService!dbpolling!1240!04/30/2005-05:48:53::
> EventPolling finished processing item
> 7efce5e2-8cca-4e87-9ac9-f2bac7ad68ff
>
> The securitysettings for all reports are equal.
> The failed jobs are also not mailed if started from sqlagent (although
> they succeed as sqlagentjob)
> Any suggestions are more than welcome, since i have no clue how to
> solve this!
> Regards jesp
>sql

e-mailing users in SQL 2000

Hello:
Two questions.
First, generally speaking, how do you configure SQL 2000 to e-mail end
users? I have a SQL script that shows, in an accounting database, inventory
items that have not been purchased from a vendor within the last year. I
want to e-mail that list of items to end users on a monthly basis. That
list of items amounts to a report.
Secondly, my script has a specific date. It, basically, says < 06/01/06. I
don't want the IT staff to have to come in and update the date parameter. Is
there a way to place syntax in the script to pull the date from the operating
system?
Thanks!
childofthe1980s> First, generally speaking, how do you configure SQL 2000 to e-mail end
> users?
I suggest you use xp_smtp from www.sqldev.net. The built-in email support in 2000 is based on MAPI
and is a hassle to get working.
> Secondly, my script has a specific date. It, basically, says < 06/01/06. I
> don't want the IT staff to have to come in and update the date parameter. Is
> there a way to place syntax in the script to pull the date from the operating
> system?
Most probably (can't say for sure without knowing anything about your code). Check out the GETDATE()
function.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in message
news:273474B7-38E8-435F-835A-123502316C07@.microsoft.com...
> Hello:
> Two questions.
> First, generally speaking, how do you configure SQL 2000 to e-mail end
> users? I have a SQL script that shows, in an accounting database, inventory
> items that have not been purchased from a vendor within the last year. I
> want to e-mail that list of items to end users on a monthly basis. That
> list of items amounts to a report.
> Secondly, my script has a specific date. It, basically, says < 06/01/06. I
> don't want the IT staff to have to come in and update the date parameter. Is
> there a way to place syntax in the script to pull the date from the operating
> system?
> Thanks!
> childofthe1980s|||Please don't post independently in separate newsgroups. You can add
multiple newsgroups to the header and then all the answers appear as one.
See my reply in the other newsgroup.
Andrew J. Kelly SQL MVP
"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
message news:273474B7-38E8-435F-835A-123502316C07@.microsoft.com...
> Hello:
> Two questions.
> First, generally speaking, how do you configure SQL 2000 to e-mail end
> users? I have a SQL script that shows, in an accounting database,
> inventory
> items that have not been purchased from a vendor within the last year. I
> want to e-mail that list of items to end users on a monthly basis. That
> list of items amounts to a report.
> Secondly, my script has a specific date. It, basically, says < 06/01/06.
> I
> don't want the IT staff to have to come in and update the date parameter.
> Is
> there a way to place syntax in the script to pull the date from the
> operating
> system?
> Thanks!
> childofthe1980s

e-mailing users in SQL 2000

Hello:
Two questions.
First, generally speaking, how do you configure SQL 2000 to e-mail end
users? I have a SQL script that shows, in an accounting database, inventory
items that have not been purchased from a vendor within the last year. I
want to e-mail that list of items to end users on a monthly basis. That
list of items amounts to a report.
Secondly, my script has a specific date. It, basically, says < 06/01/06. I
don't want the IT staff to have to come in and update the date parameter. Is
there a way to place syntax in the script to pull the date from the operating
system?
Thanks!
childofthe1980s
> First, generally speaking, how do you configure SQL 2000 to e-mail end
> users?
I suggest you use xp_smtp from www.sqldev.net. The built-in email support in 2000 is based on MAPI
and is a hassle to get working.

> Secondly, my script has a specific date. It, basically, says < 06/01/06. I
> don't want the IT staff to have to come in and update the date parameter. Is
> there a way to place syntax in the script to pull the date from the operating
> system?
Most probably (can't say for sure without knowing anything about your code). Check out the GETDATE()
function.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in message
news:273474B7-38E8-435F-835A-123502316C07@.microsoft.com...
> Hello:
> Two questions.
> First, generally speaking, how do you configure SQL 2000 to e-mail end
> users? I have a SQL script that shows, in an accounting database, inventory
> items that have not been purchased from a vendor within the last year. I
> want to e-mail that list of items to end users on a monthly basis. That
> list of items amounts to a report.
> Secondly, my script has a specific date. It, basically, says < 06/01/06. I
> don't want the IT staff to have to come in and update the date parameter. Is
> there a way to place syntax in the script to pull the date from the operating
> system?
> Thanks!
> childofthe1980s
|||Please don't post independently in separate newsgroups. You can add
multiple newsgroups to the header and then all the answers appear as one.
See my reply in the other newsgroup.
Andrew J. Kelly SQL MVP
"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
message news:273474B7-38E8-435F-835A-123502316C07@.microsoft.com...
> Hello:
> Two questions.
> First, generally speaking, how do you configure SQL 2000 to e-mail end
> users? I have a SQL script that shows, in an accounting database,
> inventory
> items that have not been purchased from a vendor within the last year. I
> want to e-mail that list of items to end users on a monthly basis. That
> list of items amounts to a report.
> Secondly, my script has a specific date. It, basically, says < 06/01/06.
> I
> don't want the IT staff to have to come in and update the date parameter.
> Is
> there a way to place syntax in the script to pull the date from the
> operating
> system?
> Thanks!
> childofthe1980s

e-mailing users in SQL 2000

Hello:
Two questions.
First, generally speaking, how do you configure SQL 2000 to e-mail end
users? I have a SQL script that shows, in an accounting database, inventory
items that have not been purchased from a vendor within the last year. I
want to e-mail that list of items to end users on a monthly basis. That
list of items amounts to a report.
Secondly, my script has a specific date. It, basically, says < 06/01/06. I
don't want the IT staff to have to come in and update the date parameter. I
s
there a way to place syntax in the script to pull the date from the operatin
g
system?
Thanks!
childofthe1980s> First, generally speaking, how do you configure SQL 2000 to e-mail end
> users?
I suggest you use xp_smtp from www.sqldev.net. The built-in email support in
2000 is based on MAPI
and is a hassle to get working.

> Secondly, my script has a specific date. It, basically, says < 06/01/06.
I
> don't want the IT staff to have to come in and update the date parameter.
Is
> there a way to place syntax in the script to pull the date from the operat
ing
> system?
Most probably (can't say for sure without knowing anything about your code).
Check out the GETDATE()
function.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in messa
ge
news:273474B7-38E8-435F-835A-123502316C07@.microsoft.com...
> Hello:
> Two questions.
> First, generally speaking, how do you configure SQL 2000 to e-mail end
> users? I have a SQL script that shows, in an accounting database, invento
ry
> items that have not been purchased from a vendor within the last year. I
> want to e-mail that list of items to end users on a monthly basis. That
> list of items amounts to a report.
> Secondly, my script has a specific date. It, basically, says < 06/01/06.
I
> don't want the IT staff to have to come in and update the date parameter.
Is
> there a way to place syntax in the script to pull the date from the operat
ing
> system?
> Thanks!
> childofthe1980s|||Please don't post independently in separate newsgroups. You can add
multiple newsgroups to the header and then all the answers appear as one.
See my reply in the other newsgroup.
Andrew J. Kelly SQL MVP
"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
message news:273474B7-38E8-435F-835A-123502316C07@.microsoft.com...
> Hello:
> Two questions.
> First, generally speaking, how do you configure SQL 2000 to e-mail end
> users? I have a SQL script that shows, in an accounting database,
> inventory
> items that have not been purchased from a vendor within the last year. I
> want to e-mail that list of items to end users on a monthly basis. That
> list of items amounts to a report.
> Secondly, my script has a specific date. It, basically, says < 06/01/06.
> I
> don't want the IT staff to have to come in and update the date parameter.
> Is
> there a way to place syntax in the script to pull the date from the
> operating
> system?
> Thanks!
> childofthe1980s

email notification on job status

In MSDE, since there is no sql mail, I am thinking of doing the following:
1. write a shell script which will query the sysjobhistory table to
determine if any job failed. If so, then an email about the failure is sent
out.
Question: What should my sql command and my mail command look like. I dont
have much experience in shell scripting or am not very familiar with MSDB
database either.
hi,
inquisite wrote:
> In MSDE, since there is no sql mail, I am thinking of doing the
> following:
> 1. write a shell script which will query the sysjobhistory table to
> determine if any job failed. If so, then an email about the failure
> is sent out.
> Question: What should my sql command and my mail command look like. I
> dont have much experience in shell scripting or am not very familiar
> with MSDB database either.
if an SMPT solution is viable, I'd resort on xp_smpt extended stored
procedure provided at http://sqldev.net/xp/xpsmtp.htm
you can then code your task's step to send a mail on success or failure..
SQL Server MVP Tibor Karaszi presents and describes such a feature at
http://www.karaszi.com/sqlserver/info_no_mapi.asp
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||SMTP solution is not available. That is why I wanted to send an email through
some script running on the server.
"Andrea Montanari" wrote:

> hi,
> inquisite wrote:
> if an SMPT solution is viable, I'd resort on xp_smpt extended stored
> procedure provided at http://sqldev.net/xp/xpsmtp.htm
> you can then code your task's step to send a mail on success or failure..
> SQL Server MVP Tibor Karaszi presents and describes such a feature at
> http://www.karaszi.com/sqlserver/info_no_mapi.asp
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
>
|||If an SMTP server is not available then how were you planing to send email?
Jim
"inquisite" <inquisite@.discussions.microsoft.com> wrote in message
news:D7FE12AC-C359-4B74-A8C6-E497686705CB@.microsoft.com...[vbcol=seagreen]
> SMTP solution is not available. That is why I wanted to send an email
> through
> some script running on the server.
> "Andrea Montanari" wrote:

E-mail address of one or more recipients is not valid

We have setup Reporting Services on a Server 2003 box with SMTP intalled on
it. I am able to write a simple VB Script that uses CDO to send a e-mail out
to a certain address (let's say to john@.test.com). When I setup a
subscription using the same e-mail address I get the error "The e-mail
address of one or more recipients is not valid." Does anyone know why - if I
can write a 6 line CDO vbscript shouldn't SMTP be working?I've figured out what my problem is - apparently SQL Server Reporting
Services is not authenticating correctly. I had to change the settings on
the Relay Restictions on the Access tab for the SMTP Server Properties to be
allow relay through the virtual server with "All except the list below"
instead of the default "Only the list below". Seems like it should work with
the default setting since there is no authentication (anonymous access) but
looks like RS doesn't like that.
"John R" wrote:
> We have setup Reporting Services on a Server 2003 box with SMTP intalled on
> it. I am able to write a simple VB Script that uses CDO to send a e-mail out
> to a certain address (let's say to john@.test.com). When I setup a
> subscription using the same e-mail address I get the error "The e-mail
> address of one or more recipients is not valid." Does anyone know why - if I
> can write a 6 line CDO vbscript shouldn't SMTP be working?

EM SQL Script generation BUG?

I'm using Enterprise Manager to generate a SQL script from a database, the
intent being that I can use that script to populate a new "empty" database
instance, duplicating the schema of the source database. My source database
has a table whose name contains a space; e.g., [tblFoo Bar].
There is a relationship between this table and another and so its name is
incorporated in the constraint name; e.g., [FK ...tblFoo Bar...]. The
problem is that the EM generated DROP statement doesn't use brackets to
delimit the object name; thus an error is thrown when the generated script
is executed in OA. Note that brackets *are* used in the generated script at
the point where the constraint is (re)created.
This appears to be a bug in the script generator. Surely the generator
should generate valid SQL!!
Is this a known problem? Is there a fix?
Thanks in advance,
Bill
Oops, forgot to mention SQL Server 2000 SP3
"Bill Cohagan" <bill@.teraXNOSPAMXquest.com> wrote in message
news:%23GkQs4DjEHA.556@.tk2msftngp13.phx.gbl...
> I'm using Enterprise Manager to generate a SQL script from a database, the
> intent being that I can use that script to populate a new "empty" database
> instance, duplicating the schema of the source database. My source
database
> has a table whose name contains a space; e.g., [tblFoo Bar].
> There is a relationship between this table and another and so its name is
> incorporated in the constraint name; e.g., [FK ...tblFoo Bar...]. The
> problem is that the EM generated DROP statement doesn't use brackets to
> delimit the object name; thus an error is thrown when the generated script
> is executed in OA. Note that brackets *are* used in the generated script
at
> the point where the constraint is (re)created.
> This appears to be a bug in the script generator. Surely the generator
> should generate valid SQL!!
> Is this a known problem? Is there a fix?
> Thanks in advance,
> Bill
>
|||Hi Bill,
Thanks for sharing your pains with us!
Based on my scope, I noticed there is an known issue like this, however, I
need more information to confirm it. Would you please so kind as to provide
more information about this?
1. What's the owner of your 2 tables? All are [dbo]?
2. Could you show me an example for me how to make this issue reporduce?
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Microsoft Developer Community Support
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||The answer to #1 is yes. I'll try to get some code to you to repro the
problem, but it'll be a couple of days.
Bill
""Mingqing Cheng [MSFT]"" <v-mingqc@.online.microsoft.com> wrote in message
news:6MqG4RkjEHA.2860@.cpmsftngxa10.phx.gbl...
> Hi Bill,
> Thanks for sharing your pains with us!
> Based on my scope, I noticed there is an known issue like this, however, I
> need more information to confirm it. Would you please so kind as to
> provide
> more information about this?
> 1. What's the owner of your 2 tables? All are [dbo]?
> 2. Could you show me an example for me how to make this issue reporduce?
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are here to be of assistance!
>
> Sincerely yours,
> Mingqing Cheng
> Microsoft Developer Community Support
> Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>
|||Cheng
Following is a SQL generated script for a simple database containing two
tables, [Table A] and [Table B] and a one to many relationship between A and
B. Note the first SQL statement containing the DROP CONSTRAINT. The
constraint name should be enclosed in [...] but is not.
If you'd like to recreated the database, wrap this name in [ ], then
execute the entire script to populate an empty database with the two tables
and relationship. Finally do a script generation and you should see the same
DROP CONSTRAINT name problem. Let me know if you have any questions.
==========================================
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_Table B_Table A]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Table B] DROP CONSTRAINT FK_Table B_Table A
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Table
A]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Table A]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Table
B]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Table B]
GO
CREATE TABLE [dbo].[Table A] (
[AID] [uniqueidentifier] NOT NULL ,
[SomeText] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Table B] (
[BID] [uniqueidentifier] NOT NULL ,
[AID] [uniqueidentifier] NOT NULL ,
[SomeInfo] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Table A] ADD
CONSTRAINT [PK_Table A] PRIMARY KEY CLUSTERED
(
[AID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Table B] ADD
CONSTRAINT [PK_Table B] PRIMARY KEY CLUSTERED
(
[BID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Table B] ADD
CONSTRAINT [FK_Table B_Table A] FOREIGN KEY
(
[AID]
) REFERENCES [dbo].[Table A] (
[AID]
)
GO
==========================================
""Mingqing Cheng [MSFT]"" <v-mingqc@.online.microsoft.com> wrote in message
news:6MqG4RkjEHA.2860@.cpmsftngxa10.phx.gbl...
> Hi Bill,
> Thanks for sharing your pains with us!
> Based on my scope, I noticed there is an known issue like this, however, I
> need more information to confirm it. Would you please so kind as to
> provide
> more information about this?
> 1. What's the owner of your 2 tables? All are [dbo]?
> 2. Could you show me an example for me how to make this issue reporduce?
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are here to be of assistance!
>
> Sincerely yours,
> Mingqing Cheng
> Microsoft Developer Community Support
> Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>
|||Hi Bill,
Thanks for your posting to let me reproduce it!
Admittedly, it is an known issue for us. There is no public document
released for this issue. Bad news from SQL Server development Team is they
won't fix this issue for SQL Server 2000 as the fix will induce rewrite of
SQL Server Enterprise Manager.
The only workaround for this is not using space in constraint name or add
the brackets manually. I am sincerely sorry for the inconvenience you may
meet from this issue.
Thanks again for your kindest providing repo scenario for us and your
corpoerations.
If you have any questions or concerns, don't hesitate to let me know. We
are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||Thanks for looking into this; though I can hardly imagine fixing the problem
would require a "rewrite of SQL Server Enterprise Manager" -- since the tool
already does the right thing when *creating* the constraint; i.e., the
failure is only when DROPping the old constraint.
Unfortunately in my case I have no control over the database schema as I'm
supplying a tool to generate code using the MS generated SQL code as input.
I suppose I can just tell my customers that SQL Server 2000 simply does not
support blanks in table names -- at least if you want to generate scripts,
but I doubt their reaction will be pleasant.
I would appreciate it if you'd get another reading from the Enterprise
Manager folks because I really think this fix is probably pretty simple. The
only reason I can imagine it to be a "rewrite" would be if Microsoft has
misplaced the source code!
Also, until/unless this gets fixed it'd save everyone time were it
documented in the KB.
Regards,
Bill
PS - Perhaps I should have targeted my product at Oracle! ;-)
""Mingqing Cheng [MSFT]"" <v-mingqc@.online.microsoft.com> wrote in message
news:tTueemxmEHA.752@.cpmsftngxa06.phx.gbl...
> Hi Bill,
> Thanks for your posting to let me reproduce it!
> Admittedly, it is an known issue for us. There is no public document
> released for this issue. Bad news from SQL Server development Team is they
> won't fix this issue for SQL Server 2000 as the fix will induce rewrite of
> SQL Server Enterprise Manager.
> The only workaround for this is not using space in constraint name or add
> the brackets manually. I am sincerely sorry for the inconvenience you may
> meet from this issue.
> Thanks again for your kindest providing repo scenario for us and your
> corpoerations.
> If you have any questions or concerns, don't hesitate to let me know. We
> are here to be of assistance!
>
> Sincerely yours,
> Mingqing Cheng
> Online Partner Support Specialist
> Partner Support Group
> Microsoft Global Technical Support Center
> Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>
|||Hi Bill,
I understood that this issue may make a big business impact for you. If so,
I would like to suggest you ask for a direct assistance from a Microsoft
Support Professional through Microsoft Product Support Services. They may
find a better workaround for you. You can contact Microsoft Product Support
directly to discuss additional support options you may have available, by
contacting us at 1-(800)936-5800 or by choosing one of the options listed
at http://support.microsoft.com/default...=sz;en-us;top.
I am afraid the development of SQL Server Enterprise Manager might be
easier to think than to do. As I had said, the workaround for this is not
using space in constraint name or add the brackets manually. I am sincerely
sorry again for the inconvenience you may meet from this issue.
Thank you for your patience and cooperation.
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||OK. Thanks for working with me on this problem.
Regards,
Bill
""Mingqing Cheng [MSFT]"" <v-mingqc@.online.microsoft.com> wrote in message
news:B9wHeN8mEHA.752@.cpmsftngxa06.phx.gbl...
> Hi Bill,
> I understood that this issue may make a big business impact for you. If
so,
> I would like to suggest you ask for a direct assistance from a Microsoft
> Support Professional through Microsoft Product Support Services. They may
> find a better workaround for you. You can contact Microsoft Product
Support
> directly to discuss additional support options you may have available, by
> contacting us at 1-(800)936-5800 or by choosing one of the options listed
> at http://support.microsoft.com/default...=sz;en-us;top.
> I am afraid the development of SQL Server Enterprise Manager might be
> easier to think than to do. As I had said, the workaround for this is not
> using space in constraint name or add the brackets manually. I am
sincerely
> sorry again for the inconvenience you may meet from this issue.
> Thank you for your patience and cooperation.
>
> Sincerely yours,
> Mingqing Cheng
> Online Partner Support Specialist
> Partner Support Group
> Microsoft Global Technical Support Center
> Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>

EM Script Error - Permanent Fix?

I know that you can make the IE script error go away by changing the view of
a database to something other than taskpad, then change it back, but this is
temporary--the error always comes back. Is there a permanent fix?
Unfortunately there is none that I know of short of SQL 2005<g>.
Andrew J. Kelly SQL MVP
"salty" <salty@.discussions.microsoft.com> wrote in message
news:9DA719CA-07DF-49C6-BF62-147D59A7CED3@.microsoft.com...
> I know that you can make the IE script error go away by changing the view
of
> a database to something other than taskpad, then change it back, but this
is
> temporary--the error always comes back. Is there a permanent fix?
>
|||That wasn't the answer I was hoping for... :^(
Thanks for the reply, though.
"Andrew J. Kelly" wrote:

> Unfortunately there is none that I know of short of SQL 2005<g>.

EM Script Error

I've have a minor problem with my enterprise manager. Whenever I try to look at the database screen I get the following pop-up, and then it just leaves the screen saying "Loading Database Information"

I tried a ground up re-install of the EM, no joy. I'm running a Dell GX260 with WinXP SP1.

Any ideas. It's just an annoyance.Think there is a problem with SQL installation. Try re-installing the SQL.|||I did re-install the SQL. Still no luck.|||do onething, open any other MMC and add SQL Server in that.|||Same error. :(|||Then there is a problem with your service pack.

Editing USers in Enterprise Manager

Help!
We use application scripts to set up and modify users in
SQL. We are using SQL and Windows authentication. Very
occasionally, a script will fail and I will have to go in
and modify a user account directly from EM. I have done
this many times over the last 18 months and never had an
issue with it. I use the SQL Server Login Properties
dialogue box accessed from Security/Logins in EM.
Today, after I had made my changes and clicked okay, I was
immediately prompted with the "Confirm Password Change"
box. I double and triple checked (by re-executing the
task) that I had NOT in fact changed the password; I still
get the warning box. Even when I enter the CORRECT
password for the user, SQL EM warns me that the passwords
do not match and refuses to update the user account.
When I deliberately change the password and confirm the
change at the prompt, then EM accepts the changes and
proceeds.
What's going on? I tested this on three identically
configured servers and had the same results each time.
I am running:
1. SQL Server 2000 (SP3, with the latest security patch
released in July)
2. Windows 2000 Server (SP4, with the latest security
patches release in July)
3. I tested and found the problem with both Developer
Edition and Enterprise edition SQL Server.
The work-around that I have found was to go into the
Logins section for the database and make the updates
there. Note, however, that I cannot edit certain server-
wide roles in that section.
Regards,
Hugh ScottFIX: You Are Prompted for Password Confirmation After You Change a Standard
SQL Server Login
http://support.microsoft.com/?kbid=826161
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Hugh Scott" <hugh.scott@.exel.com> wrote in message
news:05e901c365ce$7ef1a5a0$a101280a@.phx.gbl...
Help!
We use application scripts to set up and modify users in
SQL. We are using SQL and Windows authentication. Very
occasionally, a script will fail and I will have to go in
and modify a user account directly from EM. I have done
this many times over the last 18 months and never had an
issue with it. I use the SQL Server Login Properties
dialogue box accessed from Security/Logins in EM.
Today, after I had made my changes and clicked okay, I was
immediately prompted with the "Confirm Password Change"
box. I double and triple checked (by re-executing the
task) that I had NOT in fact changed the password; I still
get the warning box. Even when I enter the CORRECT
password for the user, SQL EM warns me that the passwords
do not match and refuses to update the user account.
When I deliberately change the password and confirm the
change at the prompt, then EM accepts the changes and
proceeds.
What's going on? I tested this on three identically
configured servers and had the same results each time.
I am running:
1. SQL Server 2000 (SP3, with the latest security patch
released in July)
2. Windows 2000 Server (SP4, with the latest security
patches release in July)
3. I tested and found the problem with both Developer
Edition and Enterprise edition SQL Server.
The work-around that I have found was to go into the
Logins section for the database and make the updates
there. Note, however, that I cannot edit certain server-
wide roles in that section.
Regards,
Hugh Scott