embedded code to check parameter

Hi..
i have 2 parameters, start_date and end_date. is it possible to use
embedded code to prompt a message box if start_date is later than end
date ?
so far from what i've read, embedded code are called using the globally
defined 'Code' member. but in my case where i need to check the
parameters, how do i call the embedded code when user click the View
Report button ?
thanksss...The first thing that came to my mind is this:
1) Are you creating this report to run off a .NET webform? If you are,
you can make the check there and bring up the appropriate UI
2) The store procedure/or database call can also check the dates and
then either rerturn results, or one result saying "Error: Begin date is
after the end date" or if thta happens use default paramteres.
3) Also, you can edit report code to check the dates and either
hide/show (using the visible properties) , and display proper error
handling there.|||thanks Sorcerdon..
1) yes, the reports will run on .net webform. but where do i check?
2) the stored procedure will return a dataset.. so if i were to put a
one-line error message, the columns for table referencing the dataset
will be missing and hence caused an error when i preview report. or
have i done it incorrectly?
3) but the stored procedure will also be run if use the visibility
properties, no?
thanks again|||i manage to get the msg box out but without the caption.. i tried the
following code:
System.Windows.Forms.MessageBox.Show("Date Range From must be earlier
or the same as Date Range To","Error","OK","","")
but i received this error:
Overload resolution failed because no accessible 'Show' can be called
without a narrowing conversion:
i checked in msdn website and don't see what's wrong with my syntax...
anyhow, is there a way not to run the query for dataset since the dates
will not return result.
thanks..|||ops... i have another problem... the messagebox won't show when i view
it after deployment.

Emailing long messages from SQL2000

Hello!
The problem is this.

I have many short messages in a table. I need to tigh them together in one long email message and email to the users.
But xp_sendmail is limited to 7,790.
How could I send longer messages? Or how could I devide the long message and send it in parts (i.e. separate consequtive emails)?

This doesn't work, it still cuts the messages off at around 7,790:

E. Send messages longer than 7,990 characters

This example shows how to send a message longer than 7,990 characters. Because message is limited to the length of a varchar (less row overhead, as are all stored procedure parameters), this example writes the long message into a global temporary table consisting of a single text column. The contents of this temporary table are then sent in mail using the @.query parameter.

CREATE TABLE ##texttab (c1 text)

INSERT ##texttab values ('Put your long message here.')

DECLARE @.cmd varchar(56)

SET @.cmd = 'SELECT c1 FROM ##texttab'

EXEC master.dbo.xp_sendmail 'robertk',

@.query = @.cmd, @.no_header= 'TRUE'

DROP TABLE ##texttab

you can output the query to a file

then have it as an attachment

|||

Thank you very much for the quick response!
I just tried using @.attach_results with xp_sendmail, it properly created a file and emailed it, but the text in the file was still cut off at the same point as before.
Is there any any method of outputting a query to a file?

Thanks again!

|||

hi,

im referrring to this not the other one

xp_sendmail {[@.recipients =] 'recipients [;...n]'}
[,[@.message =] 'message']
[,[@.query =] 'query']
[,[@.attachments =] 'attachments [;...n]']
[,[@.copy_recipients =] 'copy_recipients [;...n]'
[,[@.blind_copy_recipients =] 'blind_copy_recipients [;...n]'
[,[@.subject =] 'subject']
[,[@.type =] 'type']
[,[@.attach_results =] 'attach_value']
[,[@.no_output =] 'output_value']
[,[@.no_header =] 'header_value']
[,[@.width =] width]
[,[@.separator =] 'separator']
[,[@.echo_error =] 'echo_value']
[,[@.set_user =] 'user']
[,[@.dbuse =] 'database']

Arguments

[@.attachments =] 'attachments [;...n]'

Is a semicolon-separated list of files to attach to the mail message.

hope it helps.

regards,

joey

|||Yes, the problem is I don't know how to output data from a stored procedure to a file...|||

use bcp.

call bcp from xp_cmdshell

|||

you could use a Job to move your email into a file and later, send your email.

is it work for you ?

Regards.

|||Did you try some code similar to the one in the sample? Can you check the actual datalength of the value you are trying to return in your query? Using above example, you can do "select datalength(c1) from ##texttab" to verify the length. The query functionality supports text/ntext data so you should be able to create email message >8000 characters.sql

Email subscriptions

Hello all,
I am trying to get some email subscriptions setup and I keep getting the
message "The e-mail address of one or more recipients is not valid". The
email address is valid and I am not sure what the problem is. If I set the
email address to a local account name (i.e Administrator) the subscription
works and e-mail shows up in the drop folder of the smtp server. Does anyone
know how I can get my subscriptions working to real email addresses and not
just local accounts? Any help would be appreciated.
Thanks,
JeffI have solved my own issue. All I needed to do was edit the SMTP server
settings to grant access to the server to relay through its own IP address.
Jeff
"Jeff McIntire" wrote:
> Hello all,
> I am trying to get some email subscriptions setup and I keep getting the
> message "The e-mail address of one or more recipients is not valid". The
> email address is valid and I am not sure what the problem is. If I set the
> email address to a local account name (i.e Administrator) the subscription
> works and e-mail shows up in the drop folder of the smtp server. Does anyone
> know how I can get my subscriptions working to real email addresses and not
> just local accounts? Any help would be appreciated.
> Thanks,
> Jeff|||How exactly did you do that? I have looked around for hours, you're the only
that has my exact same problem.
"Jeff McIntire" wrote:
> I have solved my own issue. All I needed to do was edit the SMTP server
> settings to grant access to the server to relay through its own IP address.
> Jeff
> "Jeff McIntire" wrote:
> > Hello all,
> >
> > I am trying to get some email subscriptions setup and I keep getting the
> > message "The e-mail address of one or more recipients is not valid". The
> > email address is valid and I am not sure what the problem is. If I set the
> > email address to a local account name (i.e Administrator) the subscription
> > works and e-mail shows up in the drop folder of the smtp server. Does anyone
> > know how I can get my subscriptions working to real email addresses and not
> > just local accounts? Any help would be appreciated.
> >
> > Thanks,
> >
> > Jeff|||All I did was go to the SMTP server properties and click on the access tab.
Then I clicked the relay button at the bottom of the page. Next I clicked on
add and inputted the IP address of my server. Then I clicked ok and made
sure the radio button labeled â'Only the list belowâ' was selected. That
solved my problem and I hope it solves yours as well.
Thanks,
Jeff
"DougM" wrote:
> How exactly did you do that? I have looked around for hours, you're the only
> that has my exact same problem.
> "Jeff McIntire" wrote:
> > I have solved my own issue. All I needed to do was edit the SMTP server
> > settings to grant access to the server to relay through its own IP address.
> >
> > Jeff
> >
> > "Jeff McIntire" wrote:
> >
> > > Hello all,
> > >
> > > I am trying to get some email subscriptions setup and I keep getting the
> > > message "The e-mail address of one or more recipients is not valid". The
> > > email address is valid and I am not sure what the problem is. If I set the
> > > email address to a local account name (i.e Administrator) the subscription
> > > works and e-mail shows up in the drop folder of the smtp server. Does anyone
> > > know how I can get my subscriptions working to real email addresses and not
> > > just local accounts? Any help would be appreciated.
> > >
> > > Thanks,
> > >
> > > Jeff|||Thanks, I tried re-installing Reporting Services to see if I could see that
screen but after choosing to modify components (as opposed to uninstall) it
went right through to finish...How did you get to the SMTP properties?. I
appreciate your help very much.
Regards,
DougM
"Jeff McIntire" wrote:
> All I did was go to the SMTP server properties and click on the access tab.
> Then I clicked the relay button at the bottom of the page. Next I clicked on
> add and inputted the IP address of my server. Then I clicked ok and made
> sure the radio button labeled â'Only the list belowâ' was selected. That
> solved my problem and I hope it solves yours as well.
> Thanks,
> Jeff
>
> "DougM" wrote:
> > How exactly did you do that? I have looked around for hours, you're the only
> > that has my exact same problem.
> >
> > "Jeff McIntire" wrote:
> >
> > > I have solved my own issue. All I needed to do was edit the SMTP server
> > > settings to grant access to the server to relay through its own IP address.
> > >
> > > Jeff
> > >
> > > "Jeff McIntire" wrote:
> > >
> > > > Hello all,
> > > >
> > > > I am trying to get some email subscriptions setup and I keep getting the
> > > > message "The e-mail address of one or more recipients is not valid". The
> > > > email address is valid and I am not sure what the problem is. If I set the
> > > > email address to a local account name (i.e Administrator) the subscription
> > > > works and e-mail shows up in the drop folder of the smtp server. Does anyone
> > > > know how I can get my subscriptions working to real email addresses and not
> > > > just local accounts? Any help would be appreciated.
> > > >
> > > > Thanks,
> > > >
> > > > Jeff|||I am sorry. Perhaps I should have clarified. The SMTP properties that I was
referring to were in the SMTP server in IIS itself. If you open up IIS and
navigate to the SMTP server and then right click on it and go to properties
you should find the screens that I was referring to.
Thanks,
Jeff
"DougM" wrote:
> Thanks, I tried re-installing Reporting Services to see if I could see that
> screen but after choosing to modify components (as opposed to uninstall) it
> went right through to finish...How did you get to the SMTP properties?. I
> appreciate your help very much.
> Regards,
> DougM
> "Jeff McIntire" wrote:
> > All I did was go to the SMTP server properties and click on the access tab.
> > Then I clicked the relay button at the bottom of the page. Next I clicked on
> > add and inputted the IP address of my server. Then I clicked ok and made
> > sure the radio button labeled â'Only the list belowâ' was selected. That
> > solved my problem and I hope it solves yours as well.
> >
> > Thanks,
> >
> > Jeff
> >
> >
> > "DougM" wrote:
> >
> > > How exactly did you do that? I have looked around for hours, you're the only
> > > that has my exact same problem.
> > >
> > > "Jeff McIntire" wrote:
> > >
> > > > I have solved my own issue. All I needed to do was edit the SMTP server
> > > > settings to grant access to the server to relay through its own IP address.
> > > >
> > > > Jeff
> > > >
> > > > "Jeff McIntire" wrote:
> > > >
> > > > > Hello all,
> > > > >
> > > > > I am trying to get some email subscriptions setup and I keep getting the
> > > > > message "The e-mail address of one or more recipients is not valid". The
> > > > > email address is valid and I am not sure what the problem is. If I set the
> > > > > email address to a local account name (i.e Administrator) the subscription
> > > > > works and e-mail shows up in the drop folder of the smtp server. Does anyone
> > > > > know how I can get my subscriptions working to real email addresses and not
> > > > > just local accounts? Any help would be appreciated.
> > > > >
> > > > > Thanks,
> > > > >
> > > > > Jeff

email invalid

I can send email to internal email accounts successfully. However, I'm
receiving the following message when attempting to send an email to an
account located outside of our network:
The e-mail address of one or more recipients is not valid.
Here are my email settings from the config file:
<Extension Name="Report Server Email"
Type="Microsoft.ReportingServices.EmailDeliveryProvider.EmailProvider,Report
ingServicesEmailDeliveryProvider">
<MaxRetries>3</MaxRetries>
<SecondsBeforeRetry>900</SecondsBeforeRetry>
<Configuration>
<RSEmailDPConfiguration>
<SMTPServer>MAIL1</SMTPServer>
<SMTPServerPort></SMTPServerPort>
<SMTPAccountName></SMTPAccountName>
<SMTPConnectionTimeout></SMTPConnectionTimeout>
<SMTPServerPickupDirectory></SMTPServerPickupDirectory>
<SMTPUseSSL></SMTPUseSSL>
<SendUsing></SendUsing>
<SMTPAuthenticate></SMTPAuthenticate>
<From>itsupport@.management.com</From>
<EmbeddedRenderFormats>
<RenderingExtension>MHTML</RenderingExtension>
</EmbeddedRenderFormats>
<PrivilegedUserRenderFormats></PrivilegedUserRenderFormats>
<ExcludedRenderFormats>
<RenderingExtension>HTMLOWC</RenderingExtension>
<RenderingExtension>NULL</RenderingExtension>
</ExcludedRenderFormats>
<SendEmailToUserAlias>False</SendEmailToUserAlias>
<DefaultHostName></DefaultHostName>
<PermittedHosts></PermittedHosts>
</RSEmailDPConfiguration>
</Configuration>
</Extension>More than likely, the mail server you're using "MAIL1" is not configured to
relay messages outside your network. You'll need to point it to a smarthost
that can relay messages outside your network.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Matt Temple" <mtemple@.dslextreme.com> wrote in message
news:10dk900t4j2lk8b@.corp.supernews.com...
> I can send email to internal email accounts successfully. However, I'm
> receiving the following message when attempting to send an email to an
> account located outside of our network:
> The e-mail address of one or more recipients is not valid.
> Here are my email settings from the config file:
> <Extension Name="Report Server Email"
>
Type="Microsoft.ReportingServices.EmailDeliveryProvider.EmailProvider,Report
> ingServicesEmailDeliveryProvider">
> <MaxRetries>3</MaxRetries>
> <SecondsBeforeRetry>900</SecondsBeforeRetry>
> <Configuration>
> <RSEmailDPConfiguration>
> <SMTPServer>MAIL1</SMTPServer>
> <SMTPServerPort></SMTPServerPort>
> <SMTPAccountName></SMTPAccountName>
> <SMTPConnectionTimeout></SMTPConnectionTimeout>
> <SMTPServerPickupDirectory></SMTPServerPickupDirectory>
> <SMTPUseSSL></SMTPUseSSL>
> <SendUsing></SendUsing>
> <SMTPAuthenticate></SMTPAuthenticate>
> <From>itsupport@.management.com</From>
> <EmbeddedRenderFormats>
> <RenderingExtension>MHTML</RenderingExtension>
> </EmbeddedRenderFormats>
> <PrivilegedUserRenderFormats></PrivilegedUserRenderFormats>
> <ExcludedRenderFormats>
> <RenderingExtension>HTMLOWC</RenderingExtension>
> <RenderingExtension>NULL</RenderingExtension>
> </ExcludedRenderFormats>
> <SendEmailToUserAlias>False</SendEmailToUserAlias>
> <DefaultHostName></DefaultHostName>
> <PermittedHosts></PermittedHosts>
> </RSEmailDPConfiguration>
> </Configuration>
> </Extension>
>

email erroe

Hello,
I get this error message and nothing gets emailed to me. Help??
Emailing report to operator
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 18025: [Microsoft][ODBC SQL Server Driver][SQL Server]xp_sendmail: failed with mail error 0x80004005
Thanks:)I get the same errror from every couple of days. Do you run GENTRAN software on the computer you are having the problem with?

[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 18025: [Microsoft][ODBC SQL Server Driver][SQL Server]xp_sendmail: failed with mail error 0x80004005|||Do you receive the error every time ?|||No we did not get the error every time, but almost exactly every 3 days or third time the XP_SENDMAIL function was invoked during auto-notification of backups completeing.

I found the solution earlier today the technet article is at:
http://support.microsoft.com/default.aspx?scid=kb;en-us;320407

There is a hotfix for it to replace the Sqlmap70.dll with this version:
Version File name
----------
8.00.603 Sqlmap70.dll

I called Microsoft support and they emailed me a link to a hotfix file. I used the 1-800-936-4900 number from:
http://support.microsoft.com/default.aspx?scid=fh;en-us;Prodoffer41a

This hotfix has not been fully regression tested so they are only handing it out to customers with this exact problem.

All the is needed is to stop SQL Server backup the old dll, copy the new dll to the same location, and restart SQL Server.

I will know if it is fixed if I do not have any email problems/errors this week.|||jcreed - thanks for posting the solution to your problem with xp_sendmail (which is the same problem that I had a few months ago and fixed with the same methods that you did) - however, there is an intermittent problem and a steady problem with xp_sendmail.

Anu - If your problem is every x days then I would pursue the listing by jcreed; otherwise, let me know.|||xp_sendmail: failed with mail error 0x80004005
I had the same problem a while ago after System Administartor added NT login restriction on the SQL 2000 server. This has been resolved by stop and restart the services of SQLSERVERAGENT and MSSQLSERVER. I do not know if this is the case for you but it worked with me. So try it if this happens again.

email being sent, but no message

I have setup an email notifications system, that basically takes each
row from a table and sents out an email according to the data in that
row. The emails get sent, with the subject being filled as expected.
Only problem is that sometimes there is no message.

Here is the stored procedure that is being called every hour to send
the emails:

CREATE PROCEDURE dbo.RemindersSendEmails AS

--Cursor
DECLARE RemindersCursor CURSOR FOR
SELECT *
FROM RemindersTodaysAndUnsent

--Values for cursor
DECLARE
@.I_Reminder_ID bigint,
@.I_Notice_ID bigint,
@.V_Reminder_Text varchar(250),
@.SDT_Reminder_Date smalldatetime,
@.V_Email varchar(50),
@.I_Reminder_Type bigint,
@.SDT_Reminder_Sent smalldatetime,
@.I_Attempts_Made int,
@.V_Notice_Type varchar(50),
@.I_Notice_Period int,
@.V_Period_Description varchar(50),
@.I_Project_ID bigint,
@.V_Notice_Ref varchar(10)

--values for sending the mail
DECLARE @.NEWLINE varchar(2)

OPEN RemindersCursor

FETCH NEXT FROM RemindersCursor
INTO @.I_Reminder_ID, @.I_Notice_ID, @.V_Reminder_Text,
@.SDT_Reminder_Date, @.V_Email, @.I_Reminder_Type,
@.SDT_Reminder_Sent, @.I_Attempts_Made, @.V_Notice_Type,
@.I_Notice_Period, @.V_Period_Description,
@.I_Project_ID, @.V_Notice_Ref
--INTO @.I_Reminder_ID, @.I_Notice_ID, @.V_Reminder_Text,
@.SDT_Reminder_Date, @.V_Email, @.I_Reminder_Type,
--@.SDT_Reminder_Sent, @.I_Attempts_Made, @.V_Notice_Type,
@.I_Notice_Period, @.V_Period_Description,
--@.I_Project_ID, @.V_Notice_Ref

SET @.NEWLINE = char(10)

--PRINT 'start'

WHILE @.@.FETCH_STATUS = 0
BEGIN

DECLARE @.EmailMessage varchar(6000), @.Subject varchar(100), @.Status
int

SET @.Subject = RTRIM(CONVERT(varchar(8), @.I_Reminder_ID)) + ' Notice
Alert - Project ' + RTRIM(CONVERT(varchar(8), @.I_Project_ID)) + '
Notice Ref ' + RTRIM(@.V_Notice_Ref)

SET @.EmailMessage = 'Project: ' + RTRIM(CONVERT(varchar(8),
@.I_Project_ID)) + @.NEWLINE +
'Notice: ' + RTRIM(@.V_Notice_Ref) + @.NEWLINE +
'Notice Type: ' + RTRIM(@.V_Notice_Type) + ' - ' +
RTRIM(@.V_Period_Description) + @.NEWLINE +
'Reminder: ' + RTRIM(@.V_Reminder_Text) + @.NEWLINE + @.NEWLINE +
'Reminder date: ' + CONVERT(varchar(11), @.SDT_Reminder_Date) +
@.NEWLINE +
'Reminder sent: ' + CONVERT(varchar(11), GETDATE()) + @.NEWLINE +
'Email sent to: ' + @.V_Email + @.NEWLINE +
'Number of attempts made at sending this email (once every hour): '
+ CONVERT(varchar(4), @.I_Attempts_Made)
--@.I_Reminder_ID, @.I_Notice_ID, @.V_Email, @.I_Reminder_Type,
@.I_Notice_Period,

PRINT 'subject = ' + @.Subject
PRINT 'message = ' + @.EmailMessage

SET @.V_Email = LTRIM(RTRIM(@.V_Email))

EXEC @.Status = master..xp_sendmail @.recipients = @.V_Email,
@.message = @.EmailMessage,
@.subject = @.Subject

--PRINT 'XXXXXXXXXXXXXXXXXXXXXX status = ' + CONVERT(varchar(2),
@.Status)

--If send mail is a success
IF (@.Status = 0)
BEGIN
UPDATE Reminders
SET SDT_Reminder_Sent = GETDATE(), I_Attempts_Made =
@.I_Attempts_Made + 1
WHERE I_Reminder_ID = @.I_Reminder_ID
END
--Else send mail failed
ELSE
BEGIN
UPDATE Reminders
SET I_Attempts_Made = @.I_Attempts_Made + 1
WHERE I_Reminder_ID = @.I_Reminder_ID
END

-- Get the next reminder
FETCH NEXT FROM RemindersCursor
INTO @.I_Reminder_ID, @.I_Notice_ID, @.V_Reminder_Text,
@.SDT_Reminder_Date, @.V_Email, @.I_Reminder_Type,
@.SDT_Reminder_Sent, @.I_Attempts_Made, @.V_Notice_Type,
@.I_Notice_Period, @.V_Period_Description,
@.I_Project_ID, @.V_Notice_Ref
END

--PRINT 'End'

CLOSE RemindersCursor
DEALLOCATE RemindersCursor
GOJust a guess-- I'd check to see if any of your variables are NULL the SET
statement that builds your @.EmailMessage variable. In that case maybe your
entire @.EmailMessage variable is getting set to NULL?

"Jagdip Singh Ajimal" <jsa1981@.hotmail.com> wrote in message
news:c84eb1b0.0411290218.6a8a5eb1@.posting.google.c om...
> I have setup an email notifications system, that basically takes each
> row from a table and sents out an email according to the data in that
> row. The emails get sent, with the subject being filled as expected.
> Only problem is that sometimes there is no message.
> Here is the stored procedure that is being called every hour to send
> the emails:
> CREATE PROCEDURE dbo.RemindersSendEmails AS
> --Cursor
> DECLARE RemindersCursor CURSOR FOR
> SELECT *
> FROM RemindersTodaysAndUnsent
> --Values for cursor
> DECLARE
> @.I_Reminder_ID bigint,
> @.I_Notice_ID bigint,
> @.V_Reminder_Text varchar(250),
> @.SDT_Reminder_Date smalldatetime,
> @.V_Email varchar(50),
> @.I_Reminder_Type bigint,
> @.SDT_Reminder_Sent smalldatetime,
> @.I_Attempts_Made int,
> @.V_Notice_Type varchar(50),
> @.I_Notice_Period int,
> @.V_Period_Description varchar(50),
> @.I_Project_ID bigint,
> @.V_Notice_Ref varchar(10)
> --values for sending the mail
> DECLARE @.NEWLINE varchar(2)
> OPEN RemindersCursor
> FETCH NEXT FROM RemindersCursor
> INTO @.I_Reminder_ID, @.I_Notice_ID, @.V_Reminder_Text,
> @.SDT_Reminder_Date, @.V_Email, @.I_Reminder_Type,
> @.SDT_Reminder_Sent, @.I_Attempts_Made, @.V_Notice_Type,
> @.I_Notice_Period, @.V_Period_Description,
> @.I_Project_ID, @.V_Notice_Ref
> --INTO @.I_Reminder_ID, @.I_Notice_ID, @.V_Reminder_Text,
> @.SDT_Reminder_Date, @.V_Email, @.I_Reminder_Type,
> -- @.SDT_Reminder_Sent, @.I_Attempts_Made, @.V_Notice_Type,
> @.I_Notice_Period, @.V_Period_Description,
> -- @.I_Project_ID, @.V_Notice_Ref
> SET @.NEWLINE = char(10)
> --PRINT 'start'
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> DECLARE @.EmailMessage varchar(6000), @.Subject varchar(100), @.Status
> int
> SET @.Subject = RTRIM(CONVERT(varchar(8), @.I_Reminder_ID)) + ' Notice
> Alert - Project ' + RTRIM(CONVERT(varchar(8), @.I_Project_ID)) + '
> Notice Ref ' + RTRIM(@.V_Notice_Ref)
> SET @.EmailMessage = 'Project: ' + RTRIM(CONVERT(varchar(8),
> @.I_Project_ID)) + @.NEWLINE +
> 'Notice: ' + RTRIM(@.V_Notice_Ref) + @.NEWLINE +
> 'Notice Type: ' + RTRIM(@.V_Notice_Type) + ' - ' +
> RTRIM(@.V_Period_Description) + @.NEWLINE +
> 'Reminder: ' + RTRIM(@.V_Reminder_Text) + @.NEWLINE + @.NEWLINE +
> 'Reminder date: ' + CONVERT(varchar(11), @.SDT_Reminder_Date) +
> @.NEWLINE +
> 'Reminder sent: ' + CONVERT(varchar(11), GETDATE()) + @.NEWLINE +
> 'Email sent to: ' + @.V_Email + @.NEWLINE +
> 'Number of attempts made at sending this email (once every hour): '
> + CONVERT(varchar(4), @.I_Attempts_Made)
> --@.I_Reminder_ID, @.I_Notice_ID, @.V_Email, @.I_Reminder_Type,
> @.I_Notice_Period,
> PRINT 'subject = ' + @.Subject
> PRINT 'message = ' + @.EmailMessage
> SET @.V_Email = LTRIM(RTRIM(@.V_Email))
> EXEC @.Status = master..xp_sendmail @.recipients = @.V_Email,
> @.message = @.EmailMessage,
> @.subject = @.Subject
> --PRINT 'XXXXXXXXXXXXXXXXXXXXXX status = ' + CONVERT(varchar(2),
> @.Status)
> --If send mail is a success
> IF (@.Status = 0)
> BEGIN
> UPDATE Reminders
> SET SDT_Reminder_Sent = GETDATE(), I_Attempts_Made =
> @.I_Attempts_Made + 1
> WHERE I_Reminder_ID = @.I_Reminder_ID
> END
> --Else send mail failed
> ELSE
> BEGIN
> UPDATE Reminders
> SET I_Attempts_Made = @.I_Attempts_Made + 1
> WHERE I_Reminder_ID = @.I_Reminder_ID
> END
>
> -- Get the next reminder
> FETCH NEXT FROM RemindersCursor
> INTO @.I_Reminder_ID, @.I_Notice_ID, @.V_Reminder_Text,
> @.SDT_Reminder_Date, @.V_Email, @.I_Reminder_Type,
> @.SDT_Reminder_Sent, @.I_Attempts_Made, @.V_Notice_Type,
> @.I_Notice_Period, @.V_Period_Description,
> @.I_Project_ID, @.V_Notice_Ref
> END
> --PRINT 'End'
> CLOSE RemindersCursor
> DEALLOCATE RemindersCursor
> GO|||"MissLivvy" <XeveryidiwantistakenX@.yahoo.com> wrote in message
news:jeRqd.3625$u81.3215@.newsread3.news.pas.earthl ink.net...
> Just a guess-- I'd check to see if any of your variables are NULL the SET
> statement that builds your @.EmailMessage variable. In that case maybe your
> entire @.EmailMessage variable is getting set to NULL?

Ah good one.

This got me once. I forgot abuot it.

> "Jagdip Singh Ajimal" <jsa1981@.hotmail.com> wrote in message
> news:c84eb1b0.0411290218.6a8a5eb1@.posting.google.c om...
> > I have setup an email notifications system, that basically takes each
> > row from a table and sents out an email according to the data in that
> > row. The emails get sent, with the subject being filled as expected.
> > Only problem is that sometimes there is no message.
> > Here is the stored procedure that is being called every hour to send
> > the emails:
> > CREATE PROCEDURE dbo.RemindersSendEmails AS
> > --Cursor
> > DECLARE RemindersCursor CURSOR FOR
> > SELECT *
> > FROM RemindersTodaysAndUnsent
> > --Values for cursor
> > DECLARE
> > @.I_Reminder_ID bigint,
> > @.I_Notice_ID bigint,
> > @.V_Reminder_Text varchar(250),
> > @.SDT_Reminder_Date smalldatetime,
> > @.V_Email varchar(50),
> > @.I_Reminder_Type bigint,
> > @.SDT_Reminder_Sent smalldatetime,
> > @.I_Attempts_Made int,
> > @.V_Notice_Type varchar(50),
> > @.I_Notice_Period int,
> > @.V_Period_Description varchar(50),
> > @.I_Project_ID bigint,
> > @.V_Notice_Ref varchar(10)
> > --values for sending the mail
> > DECLARE @.NEWLINE varchar(2)
> > OPEN RemindersCursor
> > FETCH NEXT FROM RemindersCursor
> > INTO @.I_Reminder_ID, @.I_Notice_ID, @.V_Reminder_Text,
> > @.SDT_Reminder_Date, @.V_Email, @.I_Reminder_Type,
> > @.SDT_Reminder_Sent, @.I_Attempts_Made, @.V_Notice_Type,
> > @.I_Notice_Period, @.V_Period_Description,
> > @.I_Project_ID, @.V_Notice_Ref
> > --INTO @.I_Reminder_ID, @.I_Notice_ID, @.V_Reminder_Text,
> > @.SDT_Reminder_Date, @.V_Email, @.I_Reminder_Type,
> > -- @.SDT_Reminder_Sent, @.I_Attempts_Made, @.V_Notice_Type,
> > @.I_Notice_Period, @.V_Period_Description,
> > -- @.I_Project_ID, @.V_Notice_Ref
> > SET @.NEWLINE = char(10)
> > --PRINT 'start'
> > WHILE @.@.FETCH_STATUS = 0
> > BEGIN
> > DECLARE @.EmailMessage varchar(6000), @.Subject varchar(100), @.Status
> > int
> > SET @.Subject = RTRIM(CONVERT(varchar(8), @.I_Reminder_ID)) + ' Notice
> > Alert - Project ' + RTRIM(CONVERT(varchar(8), @.I_Project_ID)) + '
> > Notice Ref ' + RTRIM(@.V_Notice_Ref)
> > SET @.EmailMessage = 'Project: ' + RTRIM(CONVERT(varchar(8),
> > @.I_Project_ID)) + @.NEWLINE +
> > 'Notice: ' + RTRIM(@.V_Notice_Ref) + @.NEWLINE +
> > 'Notice Type: ' + RTRIM(@.V_Notice_Type) + ' - ' +
> > RTRIM(@.V_Period_Description) + @.NEWLINE +
> > 'Reminder: ' + RTRIM(@.V_Reminder_Text) + @.NEWLINE + @.NEWLINE +
> > 'Reminder date: ' + CONVERT(varchar(11), @.SDT_Reminder_Date) +
> > @.NEWLINE +
> > 'Reminder sent: ' + CONVERT(varchar(11), GETDATE()) + @.NEWLINE +
> > 'Email sent to: ' + @.V_Email + @.NEWLINE +
> > 'Number of attempts made at sending this email (once every hour): '
> > + CONVERT(varchar(4), @.I_Attempts_Made)
> > --@.I_Reminder_ID, @.I_Notice_ID, @.V_Email, @.I_Reminder_Type,
> > @.I_Notice_Period,
> > PRINT 'subject = ' + @.Subject
> > PRINT 'message = ' + @.EmailMessage
> > SET @.V_Email = LTRIM(RTRIM(@.V_Email))
> > EXEC @.Status = master..xp_sendmail @.recipients = @.V_Email,
> > @.message = @.EmailMessage,
> > @.subject = @.Subject
> > --PRINT 'XXXXXXXXXXXXXXXXXXXXXX status = ' + CONVERT(varchar(2),
> > @.Status)
> > --If send mail is a success
> > IF (@.Status = 0)
> > BEGIN
> > UPDATE Reminders
> > SET SDT_Reminder_Sent = GETDATE(), I_Attempts_Made =
> > @.I_Attempts_Made + 1
> > WHERE I_Reminder_ID = @.I_Reminder_ID
> > END
> > --Else send mail failed
> > ELSE
> > BEGIN
> > UPDATE Reminders
> > SET I_Attempts_Made = @.I_Attempts_Made + 1
> > WHERE I_Reminder_ID = @.I_Reminder_ID
> > END
> > -- Get the next reminder
> > FETCH NEXT FROM RemindersCursor
> > INTO @.I_Reminder_ID, @.I_Notice_ID, @.V_Reminder_Text,
> > @.SDT_Reminder_Date, @.V_Email, @.I_Reminder_Type,
> > @.SDT_Reminder_Sent, @.I_Attempts_Made, @.V_Notice_Type,
> > @.I_Notice_Period, @.V_Period_Description,
> > @.I_Project_ID, @.V_Notice_Ref
> > END
> > --PRINT 'End'
> > CLOSE RemindersCursor
> > DEALLOCATE RemindersCursor
> > GO

EM: Getting rid of "do you want to continue..." message

Hi,
I use the Enterprise Manager frequently and then have quite a few tables
opened. I spend half my day (well, it feels like that anyway...) clicking
away the messageboxes "Do you want to continue working with this resultset"
and "the pane has been cleared to save resources".
I understand why SQLServer 2000 wants to do this, I just don't want those
messageboxes... is there a way to tell the EM that for the rest of my life
I don't want to continue working with any resultset until I run the query
again? It would be a lifesaver for me!
Thanks!I recommend you avoid editing tables in EM. Occassionaly opening a table for
review in EM is reasonable enough but Query Analyzer is actually a much more
powerful tool for querying and updating data. QA doesn't cause the same
problems. See:
http://www.aspfaq.com/show.asp?id=2455
David Portas
SQL Server MVP
--

Eliminating (x row(s) affected) message

I am sure this has been asked before. But, in searching for this message I
get a number of returns.
How do I eliminate the (x row(s) affected) message from queries in a stored
procedure?
Thank you.Kevin wrote on Fri, 2 Jun 2006 07:39:02 -0700:

> I am sure this has been asked before. But, in searching for this message I
> get a number of returns.
> How do I eliminate the (x row(s) affected) message from queries in a
> stored procedure?
> Thank you.
Use SET NOCOUNT ON at the start of the stored proc.
Dan|||Try:
SET NOCOUNT ON
... at the beginning of each proc.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Kevin Burton" <KevinBurton@.discussions.microsoft.com> wrote in message
news:741BD8D2-F958-471B-95FD-F694ACFDD0AD@.microsoft.com...
I am sure this has been asked before. But, in searching for this message I
get a number of returns.
How do I eliminate the (x row(s) affected) message from queries in a stored
procedure?
Thank you.

Eliminating (x row(s) affected) message

I am sure this has been asked before. But, in searching for this message I
get a number of returns.
How do I eliminate the (x row(s) affected) message from queries in a stored
procedure?
Thank you.Kevin wrote on Fri, 2 Jun 2006 07:39:02 -0700:
> I am sure this has been asked before. But, in searching for this message I
> get a number of returns.
> How do I eliminate the (x row(s) affected) message from queries in a
> stored procedure?
> Thank you.
Use SET NOCOUNT ON at the start of the stored proc.
Dan|||Try:
SET NOCOUNT ON
... at the beginning of each proc.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Kevin Burton" <KevinBurton@.discussions.microsoft.com> wrote in message
news:741BD8D2-F958-471B-95FD-F694ACFDD0AD@.microsoft.com...
I am sure this has been asked before. But, in searching for this message I
get a number of returns.
How do I eliminate the (x row(s) affected) message from queries in a stored
procedure?
Thank you.

Eliminating "Nan" in reports

I'd like to eliminate "Nan" in my reports and I've tried various versions of
IIF, but keep getting an error message.
This is what I want/have:
= Sum(Fields!SubWin.Value, "WinRate1_All")+ Sum(Fields!PrimeWin.Value,
"WinRate1_All")/ Sum(Fields!SubWin.Value, "WinRate1_All")+
Sum(Fields!PrimeWin.Value, "WinRate1_All")+ Sum(Fields!SubLoss.Value,
"WinRate1_All")+ Sum(Fields!PrimeLoss.Value, "WinRate1_All")
Thanks for your help!You are probably dividing by zero. Try something like
=IIF(Sum(Fields!SubWin.Value, "WinRate1_All")+
Sum(Fields!PrimeWin.Value, "WinRate1_All")+ Sum(Fields!SubLoss.Value,
"WinRate1_All")+ Sum(Fields!PrimeLoss.Value, "WinRate1_All") <> 0,
Sum(Fields!SubWin.Value, "WinRate1_All")+ Sum(Fields!PrimeWin.Value,
"WinRate1_All")/ Sum(Fields!SubWin.Value, "WinRate1_All")+
Sum(Fields!PrimeWin.Value, "WinRate1_All")+ Sum(Fields!SubLoss.Value,
"WinRate1_All")+ Sum(Fields!PrimeLoss.Value, "WinRate1_All"),0)
This way you are only performing the division if the denominator is not
zero. If the denominator is zero, then return a zero.|||That worked perfectly!
Thank you so very much for your response!
"dba56" wrote:
> You are probably dividing by zero. Try something like
> =IIF(Sum(Fields!SubWin.Value, "WinRate1_All")+
> Sum(Fields!PrimeWin.Value, "WinRate1_All")+ Sum(Fields!SubLoss.Value,
> "WinRate1_All")+ Sum(Fields!PrimeLoss.Value, "WinRate1_All") <> 0,
> Sum(Fields!SubWin.Value, "WinRate1_All")+ Sum(Fields!PrimeWin.Value,
> "WinRate1_All")/ Sum(Fields!SubWin.Value, "WinRate1_All")+
> Sum(Fields!PrimeWin.Value, "WinRate1_All")+ Sum(Fields!SubLoss.Value,
> "WinRate1_All")+ Sum(Fields!PrimeLoss.Value, "WinRate1_All"),0)
> This way you are only performing the division if the denominator is not
> zero. If the denominator is zero, then return a zero.
>|||That was perfect!
Thank you very much for your response!
"dba56" wrote:
> You are probably dividing by zero. Try something like
> =IIF(Sum(Fields!SubWin.Value, "WinRate1_All")+
> Sum(Fields!PrimeWin.Value, "WinRate1_All")+ Sum(Fields!SubLoss.Value,
> "WinRate1_All")+ Sum(Fields!PrimeLoss.Value, "WinRate1_All") <> 0,
> Sum(Fields!SubWin.Value, "WinRate1_All")+ Sum(Fields!PrimeWin.Value,
> "WinRate1_All")/ Sum(Fields!SubWin.Value, "WinRate1_All")+
> Sum(Fields!PrimeWin.Value, "WinRate1_All")+ Sum(Fields!SubLoss.Value,
> "WinRate1_All")+ Sum(Fields!PrimeLoss.Value, "WinRate1_All"),0)
> This way you are only performing the division if the denominator is not
> zero. If the denominator is zero, then return a zero.
>

Eliminate a table

I want to eliminate a table but this it marks the message to me:
Server: Msg 3724, Level 16, State 2, Line 1
Cannot drop the table 'NOM_TABLE' because it is being used for replication.
This pertenecia to one, but no longer exists the replication, now which I want to eliminate it leaves to me in error, already tries many things but I cannot even eliminate it, I want to think that the SQL I leave to this table with some estatus on the replication that pertenecia, but as I can eliminate it, some reference.
bye
try
sp_removedbreplication <dbname>
if that doesn't work try
Exec sp_configure 'allow updates', 1
Reconfigure With Override
go
update sysobjects set replinfo = 0 where replinfo <> 0
go
Exec sp_configure 'allow updates', 0
Reconfigure With Override
go

eliechucrallah@hotmail.com

i was trying to register a new server in enterprise manager i got the
following message : "the client and server codepages do not match. some
ansi character mappings may not be possible" but under sql server
properties,general tab i have the same code page, just different product
version because of the installed service pack. i've used other then the
default collation and i am using replication between these two server.
the application is working fine not sure if the replication will work
fine. maybe other other settings in the same page where i choose
collation during setup don't match how can i check these settings after
setup is done ? i am using sql 7
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
elie,
What service pack levels are you running on the client and the server?
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
elie chucrallah wrote:
> i was trying to register a new server in enterprise manager i got the
> following message : "the client and server codepages do not match. some
> ansi character mappings may not be possible" but under sql server
> properties,general tab i have the same code page, just different product
> version because of the installed service pack. i've used other then the
> default collation and i am using replication between these two server.
> the application is working fine not sure if the replication will work
> fine. maybe other other settings in the same page where i choose
> collation during setup don't match how can i check these settings after
> setup is done ? i am using sql 7
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
|||select @.@.version
gave me this
Microsoft SQL Server 7.00 - 7.00.623 (Intel X86)
Nov 27 1998 22:20:07
Copyright (c) 1988-1998 Microsoft Corporation
Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
on 1 server and this
Microsoft SQL Server 7.00 - 7.00.842 (Intel X86)
Mar 2 2000 06:49:37
Copyright (c) 1988-1998 Microsoft Corporation
Desktop Edition on Windows NT 5.0 (Build 2195: Service Pack 2)
on the other
and sp_helpsort gave me the same result on both servers
i just downloaded sql 7 sp4 should i install it ?
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||elie,
You should install SP4 on both machines.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
elie chucrallah wrote:
> select @.@.version
> gave me this
> Microsoft SQL Server 7.00 - 7.00.623 (Intel X86)
> Nov 27 1998 22:20:07
> Copyright (c) 1988-1998 Microsoft Corporation
> Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
> on 1 server and this
> Microsoft SQL Server 7.00 - 7.00.842 (Intel X86)
> Mar 2 2000 06:49:37
> Copyright (c) 1988-1998 Microsoft Corporation
> Desktop Edition on Windows NT 5.0 (Build 2195: Service Pack 2)
> on the other
> and sp_helpsort gave me the same result on both servers
> i just downloaded sql 7 sp4 should i install it ?
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
>
>

eliechucrallah@hotmail.com

i was trying to register a new server in enterprise manager i got the
following message : "the client and server codepages do not match. some
ansi character mappings may not be possible" but under sql server
properties,general tab i have the same code page, just different product
version because of the installed service pack. i've used other then the
default collation and i am using replication between these two server.
the application is working fine not sure if the replication will work
fine. maybe other other settings in the same page where i choose
collation during setup don't match how can i check these settings after
setup is done ? i am using sql 7
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!elie,
What service pack levels are you running on the client and the server?
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
elie chucrallah wrote:
> i was trying to register a new server in enterprise manager i got the
> following message : "the client and server codepages do not match. some
> ansi character mappings may not be possible" but under sql server
> properties,general tab i have the same code page, just different product
> version because of the installed service pack. i've used other then the
> default collation and i am using replication between these two server.
> the application is working fine not sure if the replication will work
> fine. maybe other other settings in the same page where i choose
> collation during setup don't match how can i check these settings after
> setup is done ? i am using sql 7
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

Edward, any PM possible?

Edward, Is there a way to get in touch with you on a private message basis?

I'd like to discuss your coming book and other mentoring questions.

Thanks,

Philippe

pcand at you guesswhat cox net

Sure. Just remove "noreply.online." from my dispay e-mail.

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

Edition of Report Server DB not supported

I'm receiving the following message when trying to install the
Reporting Services:
"This Edition of the Report Server Database is not supported on the
edition of SQL Server 2000 you have chosen. Please choose another SQL
Server that matches the requirements."
Any suggestions appreciated.
Thank you.Here are some good links for you. Most likely you are using SQL Server
Personal Edition or MSDE or something like that. The sysreqs page tells you
what versions of SQL Server you can use.
http://www.microsoft.com/sql/reporting/productinfo/features.asp
http://www.microsoft.com/sql/reporting/productinfo/sysreqs.asp
http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/rsdepgd.mspx
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Scott" <sjd0103@.comcast.net> wrote in message
news:1102517304.544992.239530@.f14g2000cwb.googlegroups.com...
> I'm receiving the following message when trying to install the
> Reporting Services:
> "This Edition of the Report Server Database is not supported on the
> edition of SQL Server 2000 you have chosen. Please choose another SQL
> Server that matches the requirements."
> Any suggestions appreciated.
> Thank you.
>|||Are you trying to use SQL Server MSDE or Personal edition? Reporting Services
can only use Standard or Enterprise Edition of SQL Server 2000. Developer
edition works, but is not licensed for production environment.
Charles Kangai, MCT, MCDBA
"Scott" wrote:
> I'm receiving the following message when trying to install the
> Reporting Services:
> "This Edition of the Report Server Database is not supported on the
> edition of SQL Server 2000 you have chosen. Please choose another SQL
> Server that matches the requirements."
> Any suggestions appreciated.
> Thank you.
>|||Thanks everyone...It was an issue of different versions...I should have
know better.

Editing Table in SQL Server from Access 2002

Hi,
I am posting this message in the hope that somebody will be able to assist me.
I have a user who has the following setup:
We have a server running SQL Server 2000 SP3 (version 8.00.818) running on Windows 2000 Server SP3 and this server has a database which the user used to access and edit using his locally installed copy of Access 2002.
We have recently transfered this database to this server using the DTS wizard, which successfully moved the database. I have now configured a System DSN on the user's PC to connect to this database using SQL authentication and the user can successfully c
onnect to this database, as the DSN has demonstrated. The user has created an Access database and has links to each of the tables in the SQL database, using the 'Get External Data' component, selecting the System DSN as the connection object. The user i
s able to open the tables in Access, but he is unable to edit the data.
When this database was hosted on the previous SQL server, the user was able to edit the data in the tables. On the previous server, this user's Windows user account was a member of the local Administratos group on the server, but this database used SQL a
uthentication. To increase the security on this database, it was moved to the new SQL server, where the user's Windows account is not a member of the local Administrators group.
I have managed to replicate this on my PC, where I have an installation of Access 2003, but I have been specifiying that the database files should either be '2000' or '2002 or 2003' version. I have used SQL and Windows authentication and I in both config
urations I am unable to edit any of the tables in Access. I have given my SQL or Windows account the 'Select', 'Update', 'Delete' permissions on the table - but this has not worked at all. Does anybody have any ideas?
Many thanks in advance.
Neil
If you've verified that it's not a permissions issue, then does the
table have a primary key or unique index?
--Mary
On Thu, 8 Jul 2004 07:53:01 -0700, "NeilH"
<NeilH@.discussions.microsoft.com> wrote:

>We have a server running SQL Server 2000 SP3 (version 8.00.818) running on Windows 2000 Server SP3 and this server has a database which the user used to access and edit using his locally installed copy of Access 2002.
>We have recently transfered this database to this server using the DTS wizard, which successfully moved the database. I have now configured a System DSN on the user's PC to connect to this database using SQL authentication and the user can successfully
connect to this database, as the DSN has demonstrated. The user has created an Access database and has links to each of the tables in the SQL database, using the 'Get External Data' component, selecting the System DSN as the connection object. The user
is able to open the tables in Access, but he is unable to edit the data.
>When this database was hosted on the previous SQL server, the user was able to edit the data in the tables. On the previous server, this user's Windows user account was a member of the local Administratos group on the server, but this database used SQL
authentication. To increase the security on this database, it was moved to the new SQL server, where the user's Windows account is not a member of the local Administrators group.
>I have managed to replicate this on my PC, where I have an installation of Access 2003, but I have been specifiying that the database files should either be '2000' or '2002 or 2003' version. I have used SQL and Windows authentication and I in both confi
gurations I am unable to edit any of the tables in Access. I have given my SQL or Windows account the 'Select', 'Update', 'Delete' permissions on the table - but this has not worked at all. Does anybody have any ideas?
|||Hi Mary,
Thankyou for replying to my posting. Could I ask you to explain what terms
'Primary Key' and 'Unique Index', as I do not understand databases?
Neil
"Mary Chipman" <mchip@.online.microsoft.com> wrote in message
news:ietqe0dd6osr6f812l6pgo8gjtrete0uke@.4ax.com... [vbcol=seagreen]
> If you've verified that it's not a permissions issue, then does the
> table have a primary key or unique index?
> --Mary
> On Thu, 8 Jul 2004 07:53:01 -0700, "NeilH"
> <NeilH@.discussions.microsoft.com> wrote:
on Windows 2000 Server SP3 and this server has a database which the user
used to access and edit using his locally installed copy of Access 2002.[vbcol=seagreen]
wizard, which successfully moved the database. I have now configured a
System DSN on the user's PC to connect to this database using SQL
authentication and the user can successfully connect to this database, as
the DSN has demonstrated. The user has created an Access database and has
links to each of the tables in the SQL database, using the 'Get External
Data' component, selecting the System DSN as the connection object. The
user is able to open the tables in Access, but he is unable to edit the
data.[vbcol=seagreen]
able to edit the data in the tables. On the previous server, this user's
Windows user account was a member of the local Administratos group on the
server, but this database used SQL authentication. To increase the security
on this database, it was moved to the new SQL server, where the user's
Windows account is not a member of the local Administrators group.[vbcol=seagreen]
of Access 2003, but I have been specifiying that the database files should
either be '2000' or '2002 or 2003' version. I have used SQL and Windows
authentication and I in both configurations I am unable to edit any of the
tables in Access. I have given my SQL or Windows account the 'Select',
'Update', 'Delete' permissions on the table - but this has not worked at
all. Does anybody have any ideas?
>
|||I think understanding databases is going to be necessary in order for
you to succeed in your work with Access and SQL Server. I'd highly
recmmend Mike Hernandez book, Database Design for Mere Mortals, which
explains the basic concepts in an easy-to-understand way. Anyway, a
primary key or unique index uniquely identifies each row so that the
server knows which one to update. If you don't know what they are,
then you probably don't have one defined on the SQLS table. Access
will refuse to update a SQLS table that doesn't have a PK or unique
index.
--Mary
On Thu, 15 Jul 2004 09:34:39 +0100, "Neil Hobbs" <nhobbs@.sgb.co.uk>
wrote:

>Hi Mary,
>Thankyou for replying to my posting. Could I ask you to explain what terms
>'Primary Key' and 'Unique Index', as I do not understand databases?
>Neil
>
>"Mary Chipman" <mchip@.online.microsoft.com> wrote in message
>news:ietqe0dd6osr6f812l6pgo8gjtrete0uke@.4ax.com.. .
>on Windows 2000 Server SP3 and this server has a database which the user
>used to access and edit using his locally installed copy of Access 2002.
>wizard, which successfully moved the database. I have now configured a
>System DSN on the user's PC to connect to this database using SQL
>authentication and the user can successfully connect to this database, as
>the DSN has demonstrated. The user has created an Access database and has
>links to each of the tables in the SQL database, using the 'Get External
>Data' component, selecting the System DSN as the connection object. The
>user is able to open the tables in Access, but he is unable to edit the
>data.
>able to edit the data in the tables. On the previous server, this user's
>Windows user account was a member of the local Administratos group on the
>server, but this database used SQL authentication. To increase the security
>on this database, it was moved to the new SQL server, where the user's
>Windows account is not a member of the local Administrators group.
>of Access 2003, but I have been specifiying that the database files should
>either be '2000' or '2002 or 2003' version. I have used SQL and Windows
>authentication and I in both configurations I am unable to edit any of the
>tables in Access. I have given my SQL or Windows account the 'Select',
>'Update', 'Delete' permissions on the table - but this has not worked at
>all. Does anybody have any ideas?
>