Embedded Code Executing SQL

I'm trying to execute a stored procedure in the header section of a report
(so I can't use a dataset in the data tab) using the shared datasource of the
report. Does anyone have a simple example executing SQL from the embedded
code of a report?Bryan,
You can use the objects from the detail section to show in the header
or...try to have a select statement in the code behind function.
if u are okay with the first line, just pop up again, i will check and give
u the code
"Bryan" wrote:
> I'm trying to execute a stored procedure in the header section of a report
> (so I can't use a dataset in the data tab) using the shared datasource of the
> report. Does anyone have a simple example executing SQL from the embedded
> code of a report?

Embarrassingly simple question about using stored proceedures

The stored procedure sp_columns returns several fields but i want just the
COLUMN_NAME
if the stored procedure was a table I would write the query
Select COLUMN_NAME from sp_columns
Whats the simplest way of doing this for a stored proceedure
many thanks
David HEither re-write the procedure or use below technique:
CREATE TABLE #tmp...
INSERT #tmp
EXEC sp_columns
SELECT ... FROM #tmp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"David Hills" <dhills@.pcfe.ac.uk> wrote in message
news:BEEA10DA-869E-4A9B-B884-56B6070B6725@.microsoft.com...
> The stored procedure sp_columns returns several fields but i want just the
COLUMN_NAME
> if the stored procedure was a table I would write the query
> Select COLUMN_NAME from sp_columns
> Whats the simplest way of doing this for a stored proceedure
> many thanks
> David H
>|||An even simpler approach would be to ditch sp_columns and use the
INFORMATION_SCHEMA views instead. For example, the get the columns of the
Authors table in the pubs database use:
USE pubs
GO
SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.Columns
WHERE TABLE_CATALOG = 'pubs'
AND TABLE_NAME = 'authors'
or
SELECT COLUMN_NAME
FROM pubs.INFORMATION_SCHEMA.Columns
WHERE TABLE_CATALOG = 'pubs'
AND TABLE_NAME = 'authors'
You can get more information INFORMATION_SCHEMA views in BOL:
http://msdn.microsoft.com/library/d...br />
4pbn.asp
Cheers,
Stefan
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OmygI4YDEHA.628@.TK2MSFTNGP10.phx.gbl...
> Either re-write the procedure or use below technique:
> CREATE TABLE #tmp...
> INSERT #tmp
> EXEC sp_columns
> SELECT ... FROM #tmp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "David Hills" <dhills@.pcfe.ac.uk> wrote in message
> news:BEEA10DA-869E-4A9B-B884-56B6070B6725@.microsoft.com...
the
> COLUMN_NAME
>|||That's a good point, Stefan!
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Stefan Delmarco [MSFT]" <StefanDe@.online.microsoft.com> wrote in messag
e
news:eAuYCTZDEHA.3240@.TK2MSFTNGP10.phx.gbl...
> An even simpler approach would be to ditch sp_columns and use the
> INFORMATION_SCHEMA views instead. For example, the get the columns of the
> Authors table in the pubs database use:
> USE pubs
> GO
> SELECT COLUMN_NAME
> FROM INFORMATION_SCHEMA.Columns
> WHERE TABLE_CATALOG = 'pubs'
> AND TABLE_NAME = 'authors'
> or
> SELECT COLUMN_NAME
> FROM pubs.INFORMATION_SCHEMA.Columns
> WHERE TABLE_CATALOG = 'pubs'
> AND TABLE_NAME = 'authors'
> You can get more information INFORMATION_SCHEMA views in BOL:
>
http://msdn.microsoft.com/library/d..._ia-iz_4pbn.asp[
color=darkred]
> Cheers,
> Stefan
> --
> This posting is provided "AS IS" with no warranties, and confers no[/color]
rights.
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> message news:OmygI4YDEHA.628@.TK2MSFTNGP10.phx.gbl...
> the
>|||Many thanks, that's just what I needed to know to do this
SELECT TABLE_NAME,COLUMN_NAME
FROM INFORMATION_SCHEMA.Columns
WHERE TABLE_CATALOG = 'sipr'
AND COLUMN_NAME LIKE '%rlg%'
TABLE_NAME COLUMN_NAME
--
d21_eed44 rlg_code
D04_RLG rlg_code
D04_RLG rlg_name
D04_RLG rlg_snam
SRS_RLG rlg_code
SRS_RLG rlg_name
SRS_RLG rlg_snam
D04_STU stu_rlgc
D04_CONTCT stu_rlg
D51_STU_CTS stu_rlgc
D51_STU_V26 stu_rlgc
INS_STU stu_rlgc

email using

hi
can any one guide me, how to send email using stored procedure in sql server
2000 in windows 2003 o/s without using mail client installed on server.
Thanks
KalyanUse xp_smtp_sendmail, all you need is the ability to install an extended
procedure, and an SMTP server you can control.
http://www.aspfaq.com/2403
"Kalyan" <Kalyan@.discussions.microsoft.com> wrote in message
news:6CF632BE-9936-4E86-8477-297EBEFC952A@.microsoft.com...
> hi
> can any one guide me, how to send email using stored procedure in sql
> server
> 2000 in windows 2003 o/s without using mail client installed on server.
> Thanks
> Kalyan
>|||Aaron
My operating system is windows 2003, Is smtp comes with IIS?
Thanks
Kalyan
"Aaron Bertrand [SQL Server MVP]" wrote:

> Use xp_smtp_sendmail, all you need is the ability to install an extended
> procedure, and an SMTP server you can control.
> http://www.aspfaq.com/2403
>
> "Kalyan" <Kalyan@.discussions.microsoft.com> wrote in message
> news:6CF632BE-9936-4E86-8477-297EBEFC952A@.microsoft.com...
>
>|||> My operating system is windows 2003, Is smtp comes with IIS?
IIS has its own SMTP server, so yes, I guess it is compatible. However, you
do not have to have SMTP running on the same server as SQL Server, in fact I
recommend it be separate.

Email Notifications

Hi All,
I have a sql2k running on w2k adv. svr and have a stored procedure which
sends email notifications to clients using xp_sendmail.
Out of the 25 email notifications 1 or 2 fails every other day which is
unacceptable to my clients.
Checking all my logs, I see that the xp_sendmail query runs successfully all
the time.
I need to understand why the query runs successfully all the time and yet
get 1 or 2 email failures?
ThanksIt can a problem with the mail system. Since SQL Server using MAPI to send a
mail, you should check your mail transport.
There are a lot of issues can happen during mail delivery. First of all try
to check is there everything is Ok with
your MS Outlook during the failure time. Then you can check MS Exchange
Server logs if you are using it.
It also can be network issue.
Regards
---
All information provided above AS IS.
"caddo65590" <caddo65590@.hotmail.com> wrote in message
news:%23tKMf4JpDHA.1096@.TK2MSFTNGP11.phx.gbl...
> Hi All,
> I have a sql2k running on w2k adv. svr and have a stored procedure which
> sends email notifications to clients using xp_sendmail.
> Out of the 25 email notifications 1 or 2 fails every other day which is
> unacceptable to my clients.
> Checking all my logs, I see that the xp_sendmail query runs successfully
all
> the time.
> I need to understand why the query runs successfully all the time and yet
> get 1 or 2 email failures?
> Thanks
>|||Thanks Sky,
I also forgot to add the error generated by sql, it might help.
The message below is generated anytime the email fails.
Msg 18025, Sev 16: xp_sendmail: failed with mail error 0x80004005
"SkyWalker" <tcp_43@.hotmail.com_TAKETHISOFF> wrote in message
news:Or72cUKpDHA.2444@.TK2MSFTNGP09.phx.gbl...
> It can a problem with the mail system. Since SQL Server using MAPI to send
a
> mail, you should check your mail transport.
> There are a lot of issues can happen during mail delivery. First of all
try
> to check is there everything is Ok with
> your MS Outlook during the failure time. Then you can check MS Exchange
> Server logs if you are using it.
> It also can be network issue.
>
> Regards
> ---
> All information provided above AS IS.
>
> "caddo65590" <caddo65590@.hotmail.com> wrote in message
> news:%23tKMf4JpDHA.1096@.TK2MSFTNGP11.phx.gbl...
> > Hi All,
> > I have a sql2k running on w2k adv. svr and have a stored procedure which
> > sends email notifications to clients using xp_sendmail.
> > Out of the 25 email notifications 1 or 2 fails every other day which is
> > unacceptable to my clients.
> > Checking all my logs, I see that the xp_sendmail query runs successfully
> all
> > the time.
> > I need to understand why the query runs successfully all the time and
yet
> > get 1 or 2 email failures?
> > Thanks
> >
> >
>|||Did you search KB for that return code. I found at least two articles for
that return code that referred to xp_sendmail.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"caddo65590" <caddo65590@.hotmail.com> wrote in message
news:OKuLYdKpDHA.2776@.tk2msftngp13.phx.gbl...
> Thanks Sky,
> I also forgot to add the error generated by sql, it might help.
> The message below is generated anytime the email fails.
> Msg 18025, Sev 16: xp_sendmail: failed with mail error 0x80004005
> "SkyWalker" <tcp_43@.hotmail.com_TAKETHISOFF> wrote in message
> news:Or72cUKpDHA.2444@.TK2MSFTNGP09.phx.gbl...
> > It can a problem with the mail system. Since SQL Server using MAPI to
send
> a
> > mail, you should check your mail transport.
> > There are a lot of issues can happen during mail delivery. First of all
> try
> > to check is there everything is Ok with
> > your MS Outlook during the failure time. Then you can check MS Exchange
> > Server logs if you are using it.
> > It also can be network issue.
> >
> >
> >
> > Regards
> > ---
> > All information provided above AS IS.
> >
> >
> > "caddo65590" <caddo65590@.hotmail.com> wrote in message
> > news:%23tKMf4JpDHA.1096@.TK2MSFTNGP11.phx.gbl...
> > > Hi All,
> > > I have a sql2k running on w2k adv. svr and have a stored procedure
which
> > > sends email notifications to clients using xp_sendmail.
> > > Out of the 25 email notifications 1 or 2 fails every other day which
is
> > > unacceptable to my clients.
> > > Checking all my logs, I see that the xp_sendmail query runs
successfully
> > all
> > > the time.
> > > I need to understand why the query runs successfully all the time and
> yet
> > > get 1 or 2 email failures?
> > > Thanks
> > >
> > >
> >
> >
>

email from stored procedure

i want to know how can i send emails from SQL stored procedure? is it possible?

Yes.

If you are using SQL 2000, you should investigate xp_smtpmail. Check these sources:

http://www.sqldev.net/xp/xpsmtp.htm
http://www.aspfaq.com/show.asp?id=2403

If you are using SQL 2005, refer to Books Online, Topic: 'Database Mail'

|||

Hi Jassim,

I useed this script for SQL 2000. Take care of the parameters inside the procedure, such as server name or user account.

Code Snippet

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE [dbo].[spSendMail]

@.From varchar(150) ,

@.To varchar(150) ,

@.Bcc varchar(500) = null,

@.Subject varchar(400)=" ",

@.Body ntext =" "

--WITH ENCRYPTION

AS

Declare @.object int

Declare @.hr int

EXEC @.hr = sp_OACreate 'CDO.Message', @.object OUT

EXEC @.hr = sp_OASetProperty @.object, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2'

EXEC @.hr = sp_OASetProperty @.object, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value', 'mailserver.domain.com'

--BodyFormat = cdoBodyFormatHTML

EXEC @.hr = sp_OASetProperty @.object, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate").Value','1'

EXEC @.hr = sp_OASetProperty @.object, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusername").Value','DOMAIN\user'

EXEC @.hr = sp_OASetProperty @.object, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendpassword").Value','Pa$$w0rd'

EXEC @.hr = sp_OAMethod @.object, 'Configuration.Fields.Update', null

EXEC @.hr = sp_OASetProperty @.object, 'To', @.To

EXEC @.hr = sp_OASetProperty @.object, 'Bcc', @.Bcc

EXEC @.hr = sp_OASetProperty @.object, 'From', @.From

EXEC @.hr = sp_OASetProperty @.object, 'Subject', @.Subject

--use TextBody for plain text

EXEC @.hr = sp_OASetProperty @.object, 'HTMLBody', @.Body

EXEC @.hr = sp_OAMethod @.object, 'Send', NULL

--?

IF @.hr <> 0

BEGIN

EXEC sp_OAGetErrorInfo @.object

RETURN @.object

END

PRINT 'success'

EXEC @.hr = sp_OADestroy @.object

GO

and also you can use Arnie's links or in case of SQL 2005, use sp_send_dbmail.

Regards,

Janos

Email address in stored Procedure parameters

Hi, i'm writing a simple sp that check if an email address already exist in
an AddressBook table.
This sp expect a parameters called @.Address.
When i call this sp and pass to it an email address which contains an at
character (@.) the stored procedure will return no values.
i think the @. alter the parameters value.
Whitout the @. char the sp works fine .
Can someone help me?
ThanksIf you post the stored procedure the chances of getting help will be
much, much better.
Roy Harvey
Beacon Falls, CT
On Wed, 3 May 2006 14:59:01 -0700, Pantano Antonio
<PantanoAntonio@.discussions.microsoft.com> wrote:

>Hi, i'm writing a simple sp that check if an email address already exist in
>an AddressBook table.
>This sp expect a parameters called @.Address.
>When i call this sp and pass to it an email address which contains an at
>character (@.) the stored procedure will return no values.
>i think the @. alter the parameters value.
>Whitout the @. char the sp works fine .
>Can someone help me?
>Thanks|||How does your routine differ from the following?
create table address_book
(
address varchar(64)
)
insert address_book values ('address@.google.com')
create procedure add_check
@.address varchar(64)
as
if exists (select 1 from address_book where address = @.address)
print 'exists'
else
print 'not exists'
declare @.address varchar(64)
set @.address = 'address@.google.com'
exec add_check @.address|||Thanks. I solved. The error was that the parameter i declared was nvarchar
without the size.
When i changed it whit the same size of the table column, nvarchar(70), the
stored procedure works well and return me the right result.
Thanks to all.

Else statement is not working in the stored procedure

I have a stored procedure that needs to populate the fields of records for tblBag_data from the tblshipping_sched if not found it looks in the tblshipment_history. But it not checking the history table(2nd table). Please help!

CREATE Procedure spUpdate_bag_data
@.t1 int OUT
AS

declare @.work_ord_num char(9), @.two char(7), @.work_ord_line_num char(3), @.cust_num char(5), @.cust_name char(50), @.apple_part_num char(12), @.apple_catalog_num char(28);

Declare update_bag CURSOR
FOR
SELECT work_ord_num, work_ord_line_num
FROM tblBag_data
WHERE cust_num IS NULL;

OPEN update_bag
FETCH NEXT FROM update_bag INTO @.work_ord_num, @.work_ord_line_num

WHILE @.@.FETCH_STATUS = 0 --and @.counter<30
BEGIN
--set @.counter = @.counter + 1
SET @.two = LEFT(@.work_ord_num,6) + '%'
set @.cust_num = '';

SELECT @.cust_num = cust_num, @.cust_name = cust_name, @.apple_part_num = apple_part_num, @.apple_catalog_num = apple_catalog_num
FROM tblShipping_sched
WHERE work_ord_num like @.two AND work_ord_line_num = @.work_ord_line_num;



IF @.@.RowCount > 0
BEGIN

UPDATE tblBag_data
SET cust_num = @.cust_num, cust_name = @.cust_name, apple_part_num = @.apple_part_num, apple_catalog_num = @.apple_catalog_num
WHERE work_ord_num like @.two AND work_ord_line_num = @.work_ord_line_num;
END

ELSE
BEGIN

SELECT cust_num = @.cust_num, cust_name =@.cust_name, apple_part_num =@.apple_part_num, apple_catalog_num = @.apple_catalog_num FROM tblShipment_history
WHERE work_ord_num like @.two AND work_ord_line_num = @.work_ord_line_num;

IF @.cust_num IS NOT NULL and len(@.cust_num)= 5
UPDATE tblBag_data SET cust_num = @.cust_num, cust_name = @.cust_name, apple_part_num = @.apple_part_num, apple_catalog_num = @.apple_catalog_num
WHERE work_ord_num like @.two AND work_ord_line_num = @.work_ord_line_num;

END

FETCH NEXT FROM update_bag INTO @.work_ord_num, @.work_ord_line_num
END

close update_bag
deallocate update_bag

return(1)Why are you using a cursor? There's no need.

Also if
work_ord_num AND wrk_ord_line_num

are not the primary or a unique constraint to
FROM tblShipping_sched

Then you can get back multiple rows...and your assingment to the variables will be the last one returned...

And since there is no input variable to the sproc, it means you'll be doing every row in the table every time you run it...
well where cust_num is null

Need to see the DDL for the three tables...(sample data wouldn't hurt either)|||Originally posted by Brett Kaiser
Why are you using a cursor? There's no need.

Also if
work_ord_num AND wrk_ord_line_num

are not the primary or a unique constraint to
FROM tblShipping_sched

Then you can get back multiple rows...and your assingment to the variables will be the last one returned...

And since there is no input variable to the sproc, it means you'll be doing every row in the table every time you run it...
well where cust_num is null

Need to see the DDL for the three tables...(sample data wouldn't hurt either)

Hi Brett,

I have a tblBag_data that needs four fields populated from the tblshippping_sched or tblShipment_history. The stored procedure is takes the work_ord_num and work_ord_line_num in tblBag_data and match them to the tblshipping_sched if the cust_num is null. It loops thru the tblshipping_sched for that record if it finds the record it populate the four fields(cust_name, cust_num..)in the tblBag_data. But if it doesn't find it it suppose to go to tblShipment_history table and loops thru for the same record and populates the tblBag_data once it finds it.

The If statement seems to be working fine. But else is definitely not working. If there is better way to write this without cursor please provide some sample code.

Thank you.
I hope it|||Sorry...work got in the way...

How about:

UPDATE l
SET cust_num = r.cust_num
, cust_name = r.cust_name
, apple_part_num = r.apple_part_num
, apple_catalog_num = r.apple_catalog_num
FROM tblBagData l
INNER JOIN tblBagData r
ON r.work_ord_num like LEFT(l.work_ord_num,6) + '%'
AND r.work_ord_line_num = l.work_ord_line_num
WHERE cust_num IS NULL

And you don't even have to worry if it finds it ot not because youcan then just do the second query, because the cust_num will still be null|||Originally posted by Brett Kaiser
Sorry...work got in the way...

How about:

UPDATE l
SET cust_num = r.cust_num
, cust_name = r.cust_name
, apple_part_num = r.apple_part_num
, apple_catalog_num = r.apple_catalog_num
FROM tblBagData l
INNER JOIN tblBagData r
ON r.work_ord_num like LEFT(l.work_ord_num,6) + '%'
AND r.work_ord_line_num = l.work_ord_line_num
WHERE cust_num IS NULL

And you don't even have to worry if it finds it ot not because youcan then just do the second query, because the cust_num will still be null

Hmm... This might be a solution.
I'll give it a try.
Thanks!

Eliminate Duplicate ID's in this StoredProcedure

I have a stored procedure that I use for Monthly Billing

delete from BillingCurrent

insert into BillingCurrent([Name],Address,City,State,Zip,InvoiceID,CustomerID,[Date],InvoiceTotal)

SELECT Customers.Name,Customers.Address,Customers.City,Customers.State,Customers.Zip,Invoices.InvoiceID,Customers.CustomerID,Invoices.Date,Invoices.InvoiceTotal

FROM Invoices INNER JOIN

Customers ON Invoices.CustomerID = Customers.CustomerID

WHERE CONVERT(varchar(15), Invoices.Date, 112) Between CONVERT(varchar(15),dateadd (d,-30,GETDATE()), 112)and CONVERT(varchar(15), GETDATE(), 112)

This works great, but if a customer has more than one invoice open it adds that Customer again (for each invoice that is not 0.00. How can I change this SP to only add each Customer once regardless of how many invoices they have?

In your query you tried to work with Invoice.Date and Invoice.InvoiceTotal fields.

If you have following data in your Invoices table for CustomerId=1:

Date InvoiceTotal

1.1.2006 1000

1.1.2007 2000

What do you want to save into BillingCurrent?

Which SQL Server version do you use?

|||

Cammyr:

You state that you want to reduce the number of rows to 1 whenever a customer has more than one invoice during a month. However, what I don't understand is what we are supposed to put in these three fields if we are returning only one row: (1) InvoiceID, (2) Date, and (3) InvoiceTotal. I have therefore put together two methods to return only one row per customer. The first method simply returns this information for the last invoice posted for a given customer. There is a good chance that this is not what you want but would rather have the total of all invoices summed into the InvoiceTotal column:

--
-- This method uses uses (1) CROSS APPLY and (2) ROW_NUMBER() to
-- find the "last" invoice posted to a particular customers account to
-- display that information with the customer information.
--

SELECT c.Name,
c.Address,
c.City,
c.State,
c.Zip,
i.InvoiceID,
c.CustomerID,
i.Date,
i.InvoiceTotal
FROM Customers c
cross apply
( select invoiceId,
row_number ()
over ( order by Date desc,
invoiceId desc
)
as seq,
Date,
InvoiceTotal
from Invoices p
where p.customerId = c.customerId
and CONVERT(varchar(15), p.Date, 112)
Between CONVERT(varchar(15),dateadd (d,-30,GETDATE()), 112)
and CONVERT(varchar(15), GETDATE(), 112)
) i
where i.seq = 1

-- Name Address City State Zip InvoiceID CustomerID Date InvoiceTotal
-- -- -- -- -- --
-- Dave Mugambo 1318 Mockingbird Lane Munster In 46321 4 1 2007-02-28 00:00:00.000 14.92

--
-- This method instead posts the SUM of all invoices into the InvoiceTotal
-- column and chooses the highest InvoiceID for the InvoiceID column and
-- the highest Date for the Date column. It is not clear what is requried
-- for these fields
--

SELECT c.Name,
c.Address,
c.City,
c.State,
c.Zip,
max (i.InvoiceID) as InvoiceID,
c.CustomerID,
max (i.Date) as Date,
sum (i.InvoiceTotal) as InvoiceTotal
FROM Invoices i
INNER JOIN Customers c
ON i.CustomerID = c.CustomerID
WHERE CONVERT(varchar(15), i.Date, 112)
Between CONVERT(varchar(15),dateadd (d,-30,GETDATE()), 112)
and CONVERT(varchar(15), GETDATE(), 112)
group by c.CustomerId,
c.Name,
c.Address,
c.City,
c.State,
c.Zip

-- Name Address City State Zip InvoiceID CustomerID Date InvoiceTotal
-- -- -- -- -- --
-- Dave Mugambo 1318 Mockingbird Lane Munster In 46321 4 1 2007-02-28 00:00:00.000 94.76

( I completely agree with Konstantin's questions. )

|||

I want to save Name,Address,City,State,Zip,InvoiceID,CustomerID in BillingCurrent

But really I guess I don't need InvoiceID or InvoiceTotal as I have a relationship between BillingCurrent and InvoiceDetails On CustomerID Column that will get all the InvoiceDetails for each CustomerID.

I'm using vb.express with sql.express

|||

You are both right there were quite a few fields in my insert statement that were not needed. All I really needed was CustomerID,Name,Address,City and Zip because I can use the getchildrows method to get all of the corresponding invoicedetails for each customerID.

This is what I came up with, it seems to be working fine.

insert into BillingCurrent (CustomerID,[Name],Address,City,State,Zip)

SELECT Distinct Customers.CustomerID, Customers.Name,Customers.Address, Customers.City, Customers.State, Customers.Zip

FROM Invoices INNER JOIN

Customers ON Invoices.CustomerID = Customers.CustomerID

WHERE (CONVERT(varchar(15), Invoices.Date, 112) BETWEEN CONVERT(varchar(15), DATEADD(d, - 30, GETDATE()), 112) AND CONVERT(varchar(15),

GETDATE(), 112))

I'm sure yours works for that situation Kent, so I'm marking yours as the answer.

Thanks

Effect of a SELECT Stored Procedure on tables or database

Hi all,

I have a few stored procedures which all perfom SELECT queries on a table in the database. Do these kind of stored procedures affect any other processes or procedures working on that table. I am talking about locks, blocks etc.

For example, the database has a table which gets updated periodically by some process which I don't know. Now I wrote some stored procedures just to do the SQL SELECT with some conditions in WHERE clause. Is there any possibility that my stored procedure failed and the because of this, the process that runs on the table was not executed?

No, SELECT statements don't cause any kind of locking.|||

And if I don't talk about locking, can I be rest assured that there won't be anything else that can have an effect?

|||

Selects are pretty unobtrusive. I can't think of anything you'd need to worry about.

|||

gt1329a:

No, SELECT statements don't cause any kind of locking.

If your isolation level is read committed, SELECTs do put a shared resource lock but it doesnt block any UPDATEs. Locking is different from Blocking. If reading to-the-minute committed data is not important you can explicitly use NOLOCK.

Editing stored procedures...

I'm not a "real" programmer. Excuse my ignorance.

I need to edit a stored procedure. I simply want to change the text that it displays. I do not intend to change its function.

I am using SQL Express and SQL Server Management Studio Express. I can select Modify for my stored procedure, but when I save it, it saves it as an SQL file to my hard disk. How do I affect the edits to the actual stored procedure?

Hi,

When you save the stored procedure you just save the sql statement (.sql file).
You have to execute the statement.

So : go to your Stored procedure
Select 'Modify'
Make the necessary changes
Execute the code ( press F5)

Succes,

Jef