Hi everyone. I am having difficulty with the following stored procedure:
CREATE procedure A_sp_UpdatePricing_R_parts_test as
/* Declare variables */
Begin
Declare @.SellingPrice decimal(9),
@.StockCode varchar(30)
/* Declare cursor and open for processing */
Declare System_Cursor Cursor for Select StockCode from InvMaster where StockCode in ('03-18320-00')
Open System_Cursor
/* Fetch value into cursor */
Fetch Next from System_Cursor into @.StockCode
While @.@.Fetch_Status = 0
Begin
Set @.SellingPrice = Null
Select @.SellingPrice = SellingPrice from InvPrice where StockCode = @.StockCode
If @.SellingPrice >0
Update InvPrice set SellingPrice = 222
Where (StockCode = @.StockCode and StockCode like '%R')
Fetch Next from System_Cursor into @.StockCode
End
/* Close cursor */
Close System_Cursor
Deallocate System_Cursor
End
GO
What it should do is we have part numbers that end with -R or R for refurbished and most of these parts have regular part numbers that don't end in R. I am supposed to get the price for the regular one, discount it by 15% and use that to set the price for the refurbished part.
I don't know how to set the price i get for the part that is equal to the part in question but has an R in the end, which stands for refurbished.
Any help would be much appreciated.This is a multi-part message in MIME format.
--=_NextPart_000_0028_01C3C8D7.32408980
Content-Type: text/plain;
charset="Utf-8"
Content-Transfer-Encoding: 7bit
You don't need a cursor for this. Try:
update InvPrice
set
SellingPrice = (select SellingPrice
from InvPrice
where StockCode = '03-18320-00') * 0.85
where
StockCode like '03-18320-00%R'
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
.
"Andrei" <anonymous@.discussions.microsoft.com> wrote in message
news:42E31D22-31B6-4BE5-AEBF-5DC5B34BD0A9@.microsoft.com...
Hi everyone. I am having difficulty with the following stored procedure:
CREATE procedure A_sp_UpdatePricing_R_parts_test as
/* Declare variables */
Begin
Declare @.SellingPrice decimal(9),
@.StockCode varchar(30)
/* Declare cursor and open for processing */
Declare System_Cursor Cursor for Select StockCode from InvMaster where
StockCode in ('03-18320-00')
Open System_Cursor
/* Fetch value into cursor */
Fetch Next from System_Cursor into @.StockCode
While @.@.Fetch_Status = 0
Begin
Set @.SellingPrice = Null
Select @.SellingPrice = SellingPrice from InvPrice where StockCode =@.StockCode
If @.SellingPrice >0
Update InvPrice set SellingPrice = 222
Where (StockCode = @.StockCode and StockCode
like '%R')
Fetch Next from System_Cursor into @.StockCode
End
/* Close cursor */
Close System_Cursor
Deallocate System_Cursor
End
GO
What it should do is we have part numbers that end with -R or R for
refurbished and most of these parts have regular part numbers that don't end
in R. I am supposed to get the price for the regular one, discount it by 15%
and use that to set the price for the refurbished part.
I don't know how to set the price i get for the part that is equal to the
part in question but has an R in the end, which stands for refurbished.
Any help would be much appreciated.
--=_NextPart_000_0028_01C3C8D7.32408980
Content-Type: text/html;
charset="Utf-8"
Content-Transfer-Encoding: quoted-printable
=EF=BB=BF<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
You don't need a cursor for =this. Try:
update InvPrice
set
SellingPrice ==3D (select SellingPrice
= from InvPrice
= where StockCode =3D '03-18320-00') * 0.85
where
=StockCode like '03-18320-00%R'
-- =Tom
----Thomas A. =Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql.
"Andrei" wrote in message news:42E=31D22-31B6-4BE5-AEBF-5DC5B34BD0A9@.microsoft.com...Hi everyone. I am having difficulty with the following stored procedure:CREATE procedure A_sp_UpdatePricing_R_parts_test as/* Declare variables */BeginDeclare @.SellingPrice =decimal(9),@.StockCode varchar(30) /* Declare cursor and open for processing */Declare System_Cursor Cursor for Select StockCode from =InvMaster where StockCode in ('03-18320-00')Open System_Cursor/* Fetch =value into cursor */Fetch Next from System_Cursor into =@.StockCodeWhile @.@.Fetch_Status =3D 0BeginSet @.SellingPrice =3D NullSelect @.SellingPrice =3D SellingPrice from =InvPrice where StockCode =3D @.StockCodeIf @.SellingPrice >0 =; = Update InvPrice set SellingPrice =3D 222 &nbs=p; =; = &nb=sp; &nbs=p; Where (StockCode =3D @.StockCode and StockCode like '%R') Fetch Next from System_Cursor into @.StockCodeEnd/* Close cursor */Close System_CursorDeallocate System_CursorEndGOWhat =it should do is we have part numbers that end with -R or R for refurbished and =most of these parts have regular part numbers that don't end in R. I am supposed =to get the price for the regular one, discount it by 15% and use that to set =the price for the refurbished part.I don't know how to set the price i get =for the part that is equal to the part in question but has an R in the end, =which stands for refurbished. Any help would be much =appreciated.
--=_NextPart_000_0028_01C3C8D7.32408980--
Showing posts with label create. Show all posts
Showing posts with label create. Show all posts
Friday, March 30, 2012
Need help bracketing counts
I need to create a view that shows the number of times that clients made payments, how many clients, and how much they paid over a period of time. I'm not sure if I can use a case for this. How can I put something like this together?
I have a tblClients with a clientid field
I have a tblPayments with the clientid, pmtdate, and pmtamount
For example:
1 Payment -- 23 Clients -- $16000
2 Payments -- 12 Clients -- $32000
3 Payments -- 4 Clients -- $13000
etc...
If you the the "how many clients" value you must also have such a field in the tblPayments table, or is it one purchased client per payment? If so, then
SELECT c.clientid, count(p.clientid) as clientCount,sum(p.pmtamount) as pmtSum
FROM tblClients c
LEFT JOIN tblPayments p ON c.clientid=p.clientid
WHERE p.pmtdate BETWEEN @.startdate AND @.enddate
GROUP BY c.clientid
SELECTCOUNT(t.clientid)as clients,t.tblPaymentsCount,SUM(t.pmtamount)as pmtamountFROM(SELECT clientidas clientid,COUNT(tblPaymentsID)as tblPaymentsCount,SUM(pmtamount)as pmtamountFROM tblPaymentsGROUP BY clientid) tGROUP BY tblPaymentsCountORDER BY tblPaymentsCount|||
Now that I saw BradFMJ's suggestion I realize that I quite possibly misunderstood the task
Maybe this is what you're after:
SELECTcount(*)as paymentCount,count(DISTINCT clientid)as clientCount,SUM(pmtamount)as pmtSumWHERE pmtdateBETWEEN @.startDateAND @.endDate
NEED HELP and GUIDANCE!
I have a HUGE project (at least for me) and need some guidance.
I am trying to create a database for a local university movie club
that allows users to input there basic personal information (name,
address, telephone number) as well as movies in there collection. The
movies will be categorized by genre (comedy, romance, horror, etc.)
and title. I want to be able to let the users add and remove movies
to their list of movies they own, I'll call it "MOVIES I OWN".
The user will also need to be able to create a SECOND list of movies
they would like to see, again they can choose by genre and title.
They need to also be able to add and remove from this list also, i'll
call it "MOVIES I WANT TO SEE".
The last part of the project will be to match the users of "movies i
want to see" with "movies I own" users. It will be displayed on the
local university website when the user logs in and will alert the user
to the match. If there is a match at a later time, maybe the user can
be emailed? Also if there is a match, perhaps the two movies can be
taken off record after the user acknowledges the match. I would need
it to be able to handle a small amount of users now logged in at the
same time , but would like for it to eventually handle several hundred
users logged on at the same time in the distant future with out
performance problems.
I am not sure if sql 2000 is the best to get this done or perhaps
oracle. I am currently trying this on mySQL with PHP but currently am
lost in a forest of data. Any guidance suggestions will be greatly
appreciated. I am pretty new to this so please be kind...Somthing like this maybe:
CREATE TABLE Members (login VARCHAR(10) NOT NULL PRIMARY KEY, membername
VARCHAR(30) NOT NULL UNIQUE);
CREATE TABLE Movies (cat_no CHAR(10) NOT NULL PRIMARY KEY, title VARCHAR(30)
NOT NULL, released INTEGER NOT NULL, UNIQUE (title,released));
CREATE TABLE MembersMovies (login VARCHAR(10) NOT NULL REFERENCES Members
(login), cat_no CHAR(10) REFERENCES Movies (cat_no), media CHAR(3) NOT NULL
CHECK (media IN ('DVD','VHS')), Memberstatus CHAR(1) NOT NULL CHECK
(Memberstatus IN ('H','W') /* "Has" or "Wants" */), PRIMARY KEY
(login,cat_no,Memberstatus));
Query: Who has the movies that member 'X' wants?:
SELECT U.membername, M.title, M.released
FROM MembersMovies AS H,
MembersMovies AS W,
Movies AS M,
Members AS U
WHERE H.cat_no = W.cat_no
AND H.media = W.media
AND H.Memberstatus = 'H'
AND W.Memberstatus = 'W'
AND W.login = 'X'
AND W.cat_no = M.cat_no
AND H.login = U.login ;
> I am not sure if sql 2000 is the best to get this done or perhaps
> oracle. I am currently trying this on mySQL with PHP but currently am
Any of those products should be capable of your specified requirements. I
expect your choice will be guided mostly by what you know and the existing
environment you have to work with.
--
David Portas
SQL Server MVP
--|||dahct@.yahoo.com (new_GUY) wrote in message news:<c978d8cf.0406131603.34edddc9@.posting.google.com>...
> I have a HUGE project (at least for me) and need some guidance.
> I am trying to create a database for a local university movie club
> that allows users to input there basic personal information (name,
> address, telephone number) as well as movies in there collection. The
> movies will be categorized by genre (comedy, romance, horror, etc.)
> and title. I want to be able to let the users add and remove movies
> to their list of movies they own, I'll call it "MOVIES I OWN".
> The user will also need to be able to create a SECOND list of movies
> they would like to see, again they can choose by genre and title.
> They need to also be able to add and remove from this list also, i'll
> call it "MOVIES I WANT TO SEE".
> The last part of the project will be to match the users of "movies i
> want to see" with "movies I own" users. It will be displayed on the
> local university website when the user logs in and will alert the user
> to the match. If there is a match at a later time, maybe the user can
> be emailed? Also if there is a match, perhaps the two movies can be
> taken off record after the user acknowledges the match. I would need
> it to be able to handle a small amount of users now logged in at the
> same time , but would like for it to eventually handle several hundred
> users logged on at the same time in the distant future with out
> performance problems.
> I am not sure if sql 2000 is the best to get this done or perhaps
> oracle. I am currently trying this on mySQL with PHP but currently am
> lost in a forest of data. Any guidance suggestions will be greatly
> appreciated. I am pretty new to this so please be kind...
From the information you've given, it sounds like the database
platform is more or less irrelevant - if you're "lost in a forest of
data", then you probably need to spend some time looking at your
requirements and your data model. You might want to start by searching
Google for existing data models for CD or movie collections, then look
at extending them to allow for multiple users.
I wouldn't worry too much about the database - your application sounds
like many other PHP/MySQL ones (web-based, low budget, few users, no
critical data etc.). Unless you have some clear and compelling reason
for moving to a different database, that's likely to be a distraction
from addressing your key issues.
Simon|||In article <c978d8cf.0406131603.34edddc9@.posting.google.com>,
dahct@.yahoo.com says...
> I am not sure if sql 2000 is the best to get this done or perhaps
> oracle. I am currently trying this on mySQL with PHP but currently am
> lost in a forest of data. Any guidance suggestions will be greatly
> appreciated. I am pretty new to this so please be kind...
If your project is going be connected to a web server, then you are
going to need to stick with something like MySQL - Both Oracle and MS
SQL require licenses, MS SQL requires a CPU license for anything used by
a web server to present data. A CPU license is about $4900 for MS SQL, I
have no idea what Oracle licenses run today.
If this is a class project, that won't hit the web for anyone but
yourself, then you can use the 120 Day eval version without limitation.
Now, to answer your question, based on the small size of your database,
I would say that ANY of the databases you've mentioned should be more
than enough to handle it.
Things to consider when building a database:
1) Database and web server belong on different machines
2) The OS is installed on one set of drives, the database log files on
another set that is mirrored, and the data files are installed on yet
another set of drives configured as RAID 5 or RAID 0+1. For personal use
a single drive with multiple volumes will also do, but the multi-drive
methods is the best option.
3) Size your database ahead of time - meaning if you think it's going to
need 1GB of space, go ahead and make it 1GB, saves grow time later.
4) Make sure that you build proper indexes and clustered indexes
5) Make sure that you don't over-normalize the database, but make sure
it's easy to expand your tables.
6) Memory - make sure you have enough, how much is enough, well, as much
as the OS can handle :) In most cases, based on what you seem to be
building, if your testing platform has 1GB of RAM you should be in good
shape. If you are testing and running the web server and database server
on the same machine, make sure (for MS SQL) that you limit the SQL
Server to 65% of the memory - this saves room for the OS and IIS/PHP so
that the SQL Server doesn't have to spend time releasing memory to the
system.
To handle several hundred users, at the same time, you should consider a
small Dual CPU server with 3GB of RAM for the database and another small
Dual CPU server with 2GB of RAM for the web server. If you don't have
funding, the ASUS PC-DL Deluxe motherboard allows Dual Xeon CPU's, up to
4GB of RAM, and lets you use up to 6 IDE (4 SATA) devices. You could go
cheap and buy 4 120GB SATA drives and use the PC-DL Onboard RAID
controller to build one (4 drive) RAID 5 array, and then partition it
for OS, LOGS, DATA.
One other thing, if you are using a MS platform for the web server,
don't install your web site on the "C" drive, make sure that you have a
"D" drive and install your site there - to many people fail to secure
their systems and it's a lot easier to secure if you have the web site
on the NON-OS drives.
--
--
spamfree999@.rrohio.com
(Remove 999 to reply to me)
Wednesday, March 28, 2012
Need Help : Constraint
Dear All,
I want to create contraint for a column in a table with some formula
like this:
- First Character must be 'A'..'E'
- Second and third characters must be '00' .. '20'
How to make constaint as formula above?
Thanks
Robert LieHi
Posting your current DDL and example data will make your requirements less
ambiguous. but something like this may help
CREATE TABLE [MyTable] (
[col1] [varchar] (10) COLLATE Latin1_General_CS_AS NOT NULL ,
CONSTRAINT [CK_Col1] CHECK ([col1] like '[A-E][0-2][0-9]
%')
)
GO
John
"Robert Lie" wrote:
> Dear All,
> I want to create contraint for a column in a table with some formula
> like this:
> - First Character must be 'A'..'E'
> - Second and third characters must be '00' .. '20'
> How to make constaint as formula above?
> Thanks
> Robert Lie
>
I want to create contraint for a column in a table with some formula
like this:
- First Character must be 'A'..'E'
- Second and third characters must be '00' .. '20'
How to make constaint as formula above?
Thanks
Robert LieHi
Posting your current DDL and example data will make your requirements less
ambiguous. but something like this may help
CREATE TABLE [MyTable] (
[col1] [varchar] (10) COLLATE Latin1_General_CS_AS NOT NULL ,
CONSTRAINT [CK_Col1] CHECK ([col1] like '[A-E][0-2][0-9]
%')
)
GO
John
"Robert Lie" wrote:
> Dear All,
> I want to create contraint for a column in a table with some formula
> like this:
> - First Character must be 'A'..'E'
> - Second and third characters must be '00' .. '20'
> How to make constaint as formula above?
> Thanks
> Robert Lie
>
Need Help : Constraint
Dear All,
I want to create contraint for a column in a table with some formula
like this:
- First Character must be 'A'..'E'
- Second and third characters must be '00' .. '20'
How to make constaint as formula above?
Thanks
Robert LieHi
Posting your current DDL and example data will make your requirements less
ambiguous. but something like this may help
CREATE TABLE [MyTable] (
[col1] [varchar] (10) COLLATE Latin1_General_CS_AS NOT NULL ,
CONSTRAINT [CK_Col1] CHECK ([col1] like '[A-E][0-2][0-9]%')
)
GO
John
"Robert Lie" wrote:
> Dear All,
> I want to create contraint for a column in a table with some formula
> like this:
> - First Character must be 'A'..'E'
> - Second and third characters must be '00' .. '20'
> How to make constaint as formula above?
> Thanks
> Robert Lie
>
I want to create contraint for a column in a table with some formula
like this:
- First Character must be 'A'..'E'
- Second and third characters must be '00' .. '20'
How to make constaint as formula above?
Thanks
Robert LieHi
Posting your current DDL and example data will make your requirements less
ambiguous. but something like this may help
CREATE TABLE [MyTable] (
[col1] [varchar] (10) COLLATE Latin1_General_CS_AS NOT NULL ,
CONSTRAINT [CK_Col1] CHECK ([col1] like '[A-E][0-2][0-9]%')
)
GO
John
"Robert Lie" wrote:
> Dear All,
> I want to create contraint for a column in a table with some formula
> like this:
> - First Character must be 'A'..'E'
> - Second and third characters must be '00' .. '20'
> How to make constaint as formula above?
> Thanks
> Robert Lie
>
Need Help : Constraint
Dear All,
I want to create contraint for a column in a table with some formula
like this:
- First Character must be 'A'..'E'
- Second and third characters must be '00' .. '20'
How to make constaint as formula above?
Thanks
Robert Lie
Hi
Posting your current DDL and example data will make your requirements less
ambiguous. but something like this may help
CREATE TABLE [MyTable] (
[col1] [varchar] (10) COLLATE Latin1_General_CS_AS NOT NULL ,
CONSTRAINT [CK_Col1] CHECK ([col1] like '[A-E][0-2][0-9]%')
)
GO
John
"Robert Lie" wrote:
> Dear All,
> I want to create contraint for a column in a table with some formula
> like this:
> - First Character must be 'A'..'E'
> - Second and third characters must be '00' .. '20'
> How to make constaint as formula above?
> Thanks
> Robert Lie
>
sql
I want to create contraint for a column in a table with some formula
like this:
- First Character must be 'A'..'E'
- Second and third characters must be '00' .. '20'
How to make constaint as formula above?
Thanks
Robert Lie
Hi
Posting your current DDL and example data will make your requirements less
ambiguous. but something like this may help
CREATE TABLE [MyTable] (
[col1] [varchar] (10) COLLATE Latin1_General_CS_AS NOT NULL ,
CONSTRAINT [CK_Col1] CHECK ([col1] like '[A-E][0-2][0-9]%')
)
GO
John
"Robert Lie" wrote:
> Dear All,
> I want to create contraint for a column in a table with some formula
> like this:
> - First Character must be 'A'..'E'
> - Second and third characters must be '00' .. '20'
> How to make constaint as formula above?
> Thanks
> Robert Lie
>
sql
Need help / suggestion
Hey guys
I have to implement a dynamic Parent - > Child Scenario ... but the catch is
as Follows :
I need to create this Table Design so that I can have multiple Parent ->
child - > parent Relationships
(ie Db driven "Window Explorer - feel". 1 Folder that holds another Folder
that holds another Folder etc to Infinite )
So if the Above makes any sense ... Suggestions would be Welcome
Thanx1) Get a copy of TREES & HIERARCHIES IN SQL for several different
methods
2) Google for "nested sets", "path enumeration" and "adjacency list"
models in SQL|||Thanx !!!
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1109335451.349754.115330@.l41g2000cwc.googlegr oups.com...
> 1) Get a copy of TREES & HIERARCHIES IN SQL for several different
> methods
> 2) Google for "nested sets", "path enumeration" and "adjacency list"
> models in SQL|||Nice 1 celko !!! :P
http://www.intelligententerprise.co...equestid=315563
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1109335451.349754.115330@.l41g2000cwc.googlegr oups.com...
> 1) Get a copy of TREES & HIERARCHIES IN SQL for several different
> methods
> 2) Google for "nested sets", "path enumeration" and "adjacency list"
> models in SQL
need help - SP to move data
Hello
I am trying to create a stored procedure to move a ROW from Table1 to Table
based on a coditon.
Eg. I want to transfer an item with ITEMNO = '001202' FROM currentstock TO
old_stock table.
I want to be able to pass the value for ITEMNO from my code which calls the
SP.
Please help me
TIA
Mackok, I have done myself.
Thanks
"MackS" <mackS@.yahooa.com> wrote in message
news:OWiNi8H$FHA.228@.TK2MSFTNGP12.phx.gbl...
> Hello
> I am trying to create a stored procedure to move a ROW from Table1 to
> Table based on a coditon.
> Eg. I want to transfer an item with ITEMNO = '001202' FROM currentstock TO
> old_stock table.
> I want to be able to pass the value for ITEMNO from my code which calls
> the SP.
> Please help me
> TIA
> Mack
>
I am trying to create a stored procedure to move a ROW from Table1 to Table
based on a coditon.
Eg. I want to transfer an item with ITEMNO = '001202' FROM currentstock TO
old_stock table.
I want to be able to pass the value for ITEMNO from my code which calls the
SP.
Please help me
TIA
Mackok, I have done myself.
Thanks
"MackS" <mackS@.yahooa.com> wrote in message
news:OWiNi8H$FHA.228@.TK2MSFTNGP12.phx.gbl...
> Hello
> I am trying to create a stored procedure to move a ROW from Table1 to
> Table based on a coditon.
> Eg. I want to transfer an item with ITEMNO = '001202' FROM currentstock TO
> old_stock table.
> I want to be able to pass the value for ITEMNO from my code which calls
> the SP.
> Please help me
> TIA
> Mack
>
need help
CREATE TABLE [dbo].[TP_Large_Void_Rawdata_794981854_2007319_10613_7944] ([Rec_Date_Tm] [datetime] NULL, [QCluster_Count_1_SA2] [int] NULL , [QCluster_Count_2_SA2] [int] NULL , [QCluster_Count_3_SA2] [int] NULL , [QCluster_Count_4_SA2] [int] NULL , [QCluster_Count_5_SA2] [int] NULL , [QCluster_Count_1_SB2] [int] NULL , [QCluster_Count_2_SB2] [int] NULL , [QCluster_Count_3_SB2] [int] NULL , [QCluster_Count_4_SB2] [int] NULL , [QCluster_Count_5_SB2] [int] NULL) ON [PRIMARY]
GRANT REFERENCES , SELECT , UPDATE , INSERT , DELETE ON [dbo].[TP_Large_Void_Rawdata_794981854_2007319_10613_7944] TO [irisaoidb]
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TP_Grade_Distribution_Linklot__794981854_2007319_10613_7944]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) CREATE TABLE [dbo].[TP_Grade_Distribution_Linklot__794981854_2007319_10613_7944] ([lm0805] [varchar] (50) NULL, [rsc725] [varchar] (50) NULL) ON [PRIMARY]
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TP_Grade_Distribution_Linklot__794981854_2007319_10613_7944]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) GRANT REFERENCES , SELECT , UPDATE , INSERT , DELETE ON [dbo].[TP_Grade_Distribution_Linklot__794981854_2007319_10613_7944] TO [irisaoidb]
INSERT INTO TP_Large_Void_Rawdata_794981854_2007319_10613_7944 SELECT Rec_Date_Tm, QCluster_Count_1_SA2, QCluster_Count_2_SA2, QCluster_Count_3_SA2, QCluster_Count_4_SA2, QCluster_Count_5_SA2, QCluster_Count_1_SB2, QCluster_Count_2_SB2, QCluster_Count_3_SB2, QCluster_Count_4_SB2, QCluster_Count_5_SB2 FROM tbl_IRISAOI_Rawdata_KM1 (nolock),TP_DefaultCharts_Store_LotNo__794981854_2007319_10613_7944 (nolock),TP_DefaultCharts_Store_Product__794981854_2007319_10613_7944 (nolock),TP_DefaultCharts_Store_Testcell__794981854_2007319_10613_7944 (nolock) WHERE Rec_Date_Tm >= '2007-03-19 00:00:00' AND Rec_Date_Tm <= '2007-03-19 20:00:00' AND Testcell = TP_DefaultCharts_Store_Testcell__794981854_2007319_10613_7944.Selected_Value AND Product = TP_DefaultCharts_Store_Product__794981854_2007319_10613_7944.Selected_Value AND Lot_No = TP_DefaultCharts_Store_LotNo__794981854_2007319_10613_7944.Selected_Value and tester in (ALL) and upper(Lot_Id_Code) in (ALL) and upper(Bin_No) in (ALL) and upper(Grade_No) in (ALL)
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'ALL'.
can anyone point out what mistake that i've made here?
The ALL is a parameter that passed to the stored procedure when invoked.
|||oops, in fact i've left out the CHAR(39) on both side of the parameter...
now the problem is solved.
sqlNeed Help
I have inbuilt SQL server express database with VS 2005. I can able to create the databases but i don't know how to use those databases in my application.
That's a pretty big question without some context! 
Let's start out with some basics:
SQL Express learning videos: http://msdn.microsoft.com/vstudio/express/sql/learning/default.aspx VS 2005 training for database usage: http://msdn.microsoft.com/vstudio/express/beginner/windows/tier3/Need Help
I have inbuilt SQL server express database with VS 2005. I can able to create the databases but i don't know how to use those databases in my application.
That's a pretty big question without some context!
Let's start out with some basics:
SQL Express learning videos: http://msdn.microsoft.com/vstudio/express/sql/learning/default.aspx VS 2005 training for database usage: http://msdn.microsoft.com/vstudio/express/beginner/windows/tier3/Friday, March 23, 2012
Need distributed service broker sample
I'm working with the April CTP of SQL Server and I'm trying to create a proof of concept using service broker. I'm struggling with the "abc's" of it. If anyone has or can point me to a distributed "Hello, World" for service broker between SQL Server 2005 and SQL Express instances it would save me some time and trouble.
I have uploaded a zip file containing two SQL projects with scripts for sending messages between two SQL Server 2005 instances. You probably have to create your own certificates, but at least the code is there how to catalogue them etc. You can get the file from here.
Niels
Try this link, those scripts "should" have correct syntax etc. For updates etc, check my blog.
Niels|||If you do setup the broker endpoint successfully, it is time to take the BrokerChallenge:
thx!
Christopher Yager wrote:
I'm working with the April CTP of SQL Server and I'm trying to create a proof of concept using service broker. I'm struggling with the "abc's" of it. If anyone has or can point me to a distributed "Hello, World" for service broker between SQL Server 2005 and SQL Express instances it would save me some time and trouble.
I have uploaded a zip file containing two SQL projects with scripts for sending messages between two SQL Server 2005 instances. You probably have to create your own certificates, but at least the code is there how to catalogue them etc. You can get the file from here.
Niels
I don't think this is Service Broker related but I can't create a master key in the master db of "instance 1" (using the sample code example). I get the error:
Msg 15466, Level 16, State 1, Line 1
An error occurred during decryption.
when executing the statement:
create master key encryption by password = 'Hello1234'
I'm logged in to the computer as domain administrator and using windows security to connect to SQL Server. I was having the same trouble before posting to this forum in the first place. I don't know what I did but I was able to create a key before. I've since deleted, re-created, and deleted. Now I can't seem to create it again.
any thoughts?
thx.
|||I found the error of my ways. I changed the logon account for the SQL Server service. That invalidated the Service Master Key apparently. After re-generating the Service Master Key (using the FORCE option) I was able to successfully create a master key.Oh - the things we learn!|||OK - I've run through the sample. I am much more enlightened but still frustrated. I have everything set up - certificates created - routes set up, bindings created - but messages are not moving from instance1 to instance2. Messages are staying in the sys.transmission_queue on instance1.
I appreciate the assistance thus far.
My configuration is as follows:
VPC1 - running Win2K3(Active Directory), SQL2005 April CTP, VS2005, Team Foundation
VPC2 - running XP(domain member), SQL2005 Express April CTP, VS2005 Team Suite
Both VPC's running on host Win2K3 system. Networking and sharing between the systems is working fine. I'm able to connect from SQL MGMT Studio to the SQL Express instance without trouble.|||OK - sorry for not answering straight away; I'm out of the office this week, and my internet connection is not the best.
A couple of things:
1. In sys,transmission_queue, you should have a column saying why the message is not sent (I don't have a SSB machine up and running at the moment, so I don't remember the name of the column). See what the reason is.
2. Are endpoints enabled on both macines?
3. Is the database on Instance2 SSB enabled?
4. have you created a route on Instance2 for the service on Instance1?
5. Run SQL profiler on Instance2 and see if there are anything happening on that machine when you send a message from Instance1.
Niels
|||Thanks for all your help.
I saw a message in the event log about connect permission on the endpoint.
I granted connect on the service broker endpoint on the initiator (and target although that one was already done) to the remcert user and the messages just started flowing!
Great technology and very timely for my organization.
Thanks again.
For anyone else struggling - here are a few other tips:
The syntax has changed for the certificate related objects -DECRYPTION_PASSWORD change to DECRYPTION BY PASSWORD;
ENCRYPTION_PASSWORD change to ENCRYPTION BY PASSWORD;
PRIVATE_KEY change to PRIVATE KEY (no underscore);
there are a few others but I don't remember where I found the list I used.
If you change the login account for the SQL Server service, you'll need to regenerate the Service Master Key. All other keys and certs are based on this so be aware before changing the login for any of your services.|||You can actually save the service master key before changing the SQL Server service login account, using BACKUP SERVICE MASTER KEY..., and then restore it under the new login account, using RESTORE SERVICE MASTER KEY...|||Ni Niels,
the link u gave for the zip file download does not work anymore. if u can provide me the zip file with code that would definitely help me getting started with service broker.
Thanks in advance.|||
the link u gave for the zip file download does not work anymore. if u can provide me the zip file with code that would definitely help me getting started with service broker.
Try this link, those scripts "should" have correct syntax etc. For updates etc, check my blog.
Niels|||If you do setup the broker endpoint successfully, it is time to take the BrokerChallenge:
http://blogs.msdn.com/rushidesai/archive/2005/06/15/429649.aspx
Thank you for participating,
Rushi|||Niels,
The two link
http://staff.develop.om/nielsb/code/routing2.zip
http://staff.develop.com/nielsb/code/routing-aprilctp.zip
still do not work (a connection with the server could not be established). I do not know if the server is down, or is the document no longer for pulic domain?
Appreciate your reply
NEED DIRECTION and suggestions!
I have a HUGE project and need some guidance.
I am trying to create a database for a local university movie club that allows users to input there basic personal information (name, address, telephone number) as well as movies in there collection. The movies will be categorized by genre (comedy, roman
ce, horror, etc.) and title. I want to be able to let the users add and remove movies to their list of movies they own, I'll call it "MOVIES I OWN".
The user will also need to be able to create a SECOND list of movies they would like to see, again they can choose by genre and title. They need to also be able to add and remove from this list also, i'll call it "MOVIES I WANT TO SEE".
The last part of the project will be to match the users of "movies i want to see" with "movies I own". It will be displayed on the local university website when the user logs in and will alert the user to the match. If there is a match at a later time,
maybe the user can be emailed? Also if there is a match, perhaps the two movies can be taken off record. I would need it to be able to handle a small amount of users now logged in at the same time , but would like for it to handle several hundred to thou
sand users logged on at the same time in the distant future with out performance problems.
I am not sure if sql server 2000 is the best or perhaps oracle. I am currently trying this on mySQL but currently lost in a forest of data. Any guidance suggestions will be greatly appreciated. I am pretty new to this so please be kind...
Most databases can do what you need, in terms of your database design. I
feel you should research more on the front-end, to address the 'several
hundred to thousand users' issue. A database can handle that load, given
the right hardware (and budget). But for cost and efficiency purposes, your
application layer should be capable of creating and managing a pool of
transactions so that connnections can be shared. It should also allow you
to specify the transaction isolation level to deal with concurrency issues.
Peter Yeoh
http://www.yohz.com
Need smaller backup files? Try MiniSQLBackup
"new_GUY" <new_GUY@.discussions.microsoft.com> wrote in message
news:51334783-729B-4E94-A7B8-D72E922DEAF4@.microsoft.com...
> I have a HUGE project and need some guidance.
> I am trying to create a database for a local university movie club that
allows users to input there basic personal information (name, address,
telephone number) as well as movies in there collection. The movies will be
categorized by genre (comedy, romance, horror, etc.) and title. I want to
be able to let the users add and remove movies to their list of movies they
own, I'll call it "MOVIES I OWN".
> The user will also need to be able to create a SECOND list of movies they
would like to see, again they can choose by genre and title. They need to
also be able to add and remove from this list also, i'll call it "MOVIES I
WANT TO SEE".
> The last part of the project will be to match the users of "movies i want
to see" with "movies I own". It will be displayed on the local university
website when the user logs in and will alert the user to the match. If
there is a match at a later time, maybe the user can be emailed? Also if
there is a match, perhaps the two movies can be taken off record. I would
need it to be able to handle a small amount of users now logged in at the
same time , but would like for it to handle several hundred to thousand
users logged on at the same time in the distant future with out performance
problems.
> I am not sure if sql server 2000 is the best or perhaps oracle. I am
currently trying this on mySQL but currently lost in a forest of data. Any
guidance suggestions will be greatly appreciated. I am pretty new to this
so please be kind...
>
I am trying to create a database for a local university movie club that allows users to input there basic personal information (name, address, telephone number) as well as movies in there collection. The movies will be categorized by genre (comedy, roman
ce, horror, etc.) and title. I want to be able to let the users add and remove movies to their list of movies they own, I'll call it "MOVIES I OWN".
The user will also need to be able to create a SECOND list of movies they would like to see, again they can choose by genre and title. They need to also be able to add and remove from this list also, i'll call it "MOVIES I WANT TO SEE".
The last part of the project will be to match the users of "movies i want to see" with "movies I own". It will be displayed on the local university website when the user logs in and will alert the user to the match. If there is a match at a later time,
maybe the user can be emailed? Also if there is a match, perhaps the two movies can be taken off record. I would need it to be able to handle a small amount of users now logged in at the same time , but would like for it to handle several hundred to thou
sand users logged on at the same time in the distant future with out performance problems.
I am not sure if sql server 2000 is the best or perhaps oracle. I am currently trying this on mySQL but currently lost in a forest of data. Any guidance suggestions will be greatly appreciated. I am pretty new to this so please be kind...
Most databases can do what you need, in terms of your database design. I
feel you should research more on the front-end, to address the 'several
hundred to thousand users' issue. A database can handle that load, given
the right hardware (and budget). But for cost and efficiency purposes, your
application layer should be capable of creating and managing a pool of
transactions so that connnections can be shared. It should also allow you
to specify the transaction isolation level to deal with concurrency issues.
Peter Yeoh
http://www.yohz.com
Need smaller backup files? Try MiniSQLBackup
"new_GUY" <new_GUY@.discussions.microsoft.com> wrote in message
news:51334783-729B-4E94-A7B8-D72E922DEAF4@.microsoft.com...
> I have a HUGE project and need some guidance.
> I am trying to create a database for a local university movie club that
allows users to input there basic personal information (name, address,
telephone number) as well as movies in there collection. The movies will be
categorized by genre (comedy, romance, horror, etc.) and title. I want to
be able to let the users add and remove movies to their list of movies they
own, I'll call it "MOVIES I OWN".
> The user will also need to be able to create a SECOND list of movies they
would like to see, again they can choose by genre and title. They need to
also be able to add and remove from this list also, i'll call it "MOVIES I
WANT TO SEE".
> The last part of the project will be to match the users of "movies i want
to see" with "movies I own". It will be displayed on the local university
website when the user logs in and will alert the user to the match. If
there is a match at a later time, maybe the user can be emailed? Also if
there is a match, perhaps the two movies can be taken off record. I would
need it to be able to handle a small amount of users now logged in at the
same time , but would like for it to handle several hundred to thousand
users logged on at the same time in the distant future with out performance
problems.
> I am not sure if sql server 2000 is the best or perhaps oracle. I am
currently trying this on mySQL but currently lost in a forest of data. Any
guidance suggestions will be greatly appreciated. I am pretty new to this
so please be kind...
>
NEED DIRECTION and suggestions!
I have a HUGE project and need some guidance.
I am trying to create a database for a local university movie club that allo
ws users to input there basic personal information (name, address, telephone
number) as well as movies in there collection. The movies will be categori
zed by genre (comedy, roman
ce, horror, etc.) and title. I want to be able to let the users add and rem
ove movies to their list of movies they own, I'll call it "MOVIES I OWN".
The user will also need to be able to create a SECOND list of movies they wo
uld like to see, again they can choose by genre and title. They need to als
o be able to add and remove from this list also, i'll call it "MOVIES I WANT
TO SEE".
The last part of the project will be to match the users of "movies i want to
see" with "movies I own". It will be displayed on the local university web
site when the user logs in and will alert the user to the match. If there i
s a match at a later time,
maybe the user can be emailed? Also if there is a match, perhaps the two mo
vies can be taken off record. I would need it to be able to handle a small a
mount of users now logged in at the same time , but would like for it to han
dle several hundred to thou
sand users logged on at the same time in the distant future with out perform
ance problems.
I am not sure if sql server 2000 is the best or perhaps oracle. I am curren
tly trying this on mysql but currently lost in a forest of data. Any guidanc
e suggestions will be greatly appreciated. I am pretty new to this so pleas
e be kind...Most databases can do what you need, in terms of your database design. I
feel you should research more on the front-end, to address the 'several
hundred to thousand users' issue. A database can handle that load, given
the right hardware (and budget). But for cost and efficiency purposes, your
application layer should be capable of creating and managing a pool of
transactions so that connnections can be shared. It should also allow you
to specify the transaction isolation level to deal with concurrency issues.
Peter Yeoh
http://www.yohz.com
Need smaller backup files? Try MiniSQLBackup
"new_GUY" <new_GUY@.discussions.microsoft.com> wrote in message
news:51334783-729B-4E94-A7B8-D72E922DEAF4@.microsoft.com...
> I have a HUGE project and need some guidance.
> I am trying to create a database for a local university movie club that
allows users to input there basic personal information (name, address,
telephone number) as well as movies in there collection. The movies will be
categorized by genre (comedy, romance, horror, etc.) and title. I want to
be able to let the users add and remove movies to their list of movies they
own, I'll call it "MOVIES I OWN".
> The user will also need to be able to create a SECOND list of movies they
would like to see, again they can choose by genre and title. They need to
also be able to add and remove from this list also, i'll call it "MOVIES I
WANT TO SEE".
> The last part of the project will be to match the users of "movies i want
to see" with "movies I own". It will be displayed on the local university
website when the user logs in and will alert the user to the match. If
there is a match at a later time, maybe the user can be emailed? Also if
there is a match, perhaps the two movies can be taken off record. I would
need it to be able to handle a small amount of users now logged in at the
same time , but would like for it to handle several hundred to thousand
users logged on at the same time in the distant future with out performance
problems.
> I am not sure if sql server 2000 is the best or perhaps oracle. I am
currently trying this on mysql but currently lost in a forest of data. Any
guidance suggestions will be greatly appreciated. I am pretty new to this
so please be kind...
>
I am trying to create a database for a local university movie club that allo
ws users to input there basic personal information (name, address, telephone
number) as well as movies in there collection. The movies will be categori
zed by genre (comedy, roman
ce, horror, etc.) and title. I want to be able to let the users add and rem
ove movies to their list of movies they own, I'll call it "MOVIES I OWN".
The user will also need to be able to create a SECOND list of movies they wo
uld like to see, again they can choose by genre and title. They need to als
o be able to add and remove from this list also, i'll call it "MOVIES I WANT
TO SEE".
The last part of the project will be to match the users of "movies i want to
see" with "movies I own". It will be displayed on the local university web
site when the user logs in and will alert the user to the match. If there i
s a match at a later time,
maybe the user can be emailed? Also if there is a match, perhaps the two mo
vies can be taken off record. I would need it to be able to handle a small a
mount of users now logged in at the same time , but would like for it to han
dle several hundred to thou
sand users logged on at the same time in the distant future with out perform
ance problems.
I am not sure if sql server 2000 is the best or perhaps oracle. I am curren
tly trying this on mysql but currently lost in a forest of data. Any guidanc
e suggestions will be greatly appreciated. I am pretty new to this so pleas
e be kind...Most databases can do what you need, in terms of your database design. I
feel you should research more on the front-end, to address the 'several
hundred to thousand users' issue. A database can handle that load, given
the right hardware (and budget). But for cost and efficiency purposes, your
application layer should be capable of creating and managing a pool of
transactions so that connnections can be shared. It should also allow you
to specify the transaction isolation level to deal with concurrency issues.
Peter Yeoh
http://www.yohz.com
Need smaller backup files? Try MiniSQLBackup
"new_GUY" <new_GUY@.discussions.microsoft.com> wrote in message
news:51334783-729B-4E94-A7B8-D72E922DEAF4@.microsoft.com...
> I have a HUGE project and need some guidance.
> I am trying to create a database for a local university movie club that
allows users to input there basic personal information (name, address,
telephone number) as well as movies in there collection. The movies will be
categorized by genre (comedy, romance, horror, etc.) and title. I want to
be able to let the users add and remove movies to their list of movies they
own, I'll call it "MOVIES I OWN".
> The user will also need to be able to create a SECOND list of movies they
would like to see, again they can choose by genre and title. They need to
also be able to add and remove from this list also, i'll call it "MOVIES I
WANT TO SEE".
> The last part of the project will be to match the users of "movies i want
to see" with "movies I own". It will be displayed on the local university
website when the user logs in and will alert the user to the match. If
there is a match at a later time, maybe the user can be emailed? Also if
there is a match, perhaps the two movies can be taken off record. I would
need it to be able to handle a small amount of users now logged in at the
same time , but would like for it to handle several hundred to thousand
users logged on at the same time in the distant future with out performance
problems.
> I am not sure if sql server 2000 is the best or perhaps oracle. I am
currently trying this on mysql but currently lost in a forest of data. Any
guidance suggestions will be greatly appreciated. I am pretty new to this
so please be kind...
>
NEED DIRECTION and suggestions!
I have a HUGE project and need some guidance.
I am trying to create a database for a local university movie club that allows users to input there basic personal information (name, address, telephone number) as well as movies in there collection. The movies will be categorized by genre (comedy, romance, horror, etc.) and title. I want to be able to let the users add and remove movies to their list of movies they own, I'll call it "MOVIES I OWN".
The user will also need to be able to create a SECOND list of movies they would like to see, again they can choose by genre and title. They need to also be able to add and remove from this list also, i'll call it "MOVIES I WANT TO SEE".
The last part of the project will be to match the users of "movies i want to see" with "movies I own". It will be displayed on the local university website when the user logs in and will alert the user to the match. If there is a match at a later time, maybe the user can be emailed? Also if there is a match, perhaps the two movies can be taken off record. I would need it to be able to handle a small amount of users now logged in at the same time , but would like for it to handle several hundred to thousand users logged on at the same time in the distant future with out performance problems.
I am not sure if sql server 2000 is the best or perhaps oracle. I am currently trying this on mySQL but currently lost in a forest of data. Any guidance suggestions will be greatly appreciated. I am pretty new to this so please be kind...Most databases can do what you need, in terms of your database design. I
feel you should research more on the front-end, to address the 'several
hundred to thousand users' issue. A database can handle that load, given
the right hardware (and budget). But for cost and efficiency purposes, your
application layer should be capable of creating and managing a pool of
transactions so that connnections can be shared. It should also allow you
to specify the transaction isolation level to deal with concurrency issues.
Peter Yeoh
http://www.yohz.com
Need smaller backup files? Try MiniSQLBackup
"new_GUY" <new_GUY@.discussions.microsoft.com> wrote in message
news:51334783-729B-4E94-A7B8-D72E922DEAF4@.microsoft.com...
> I have a HUGE project and need some guidance.
> I am trying to create a database for a local university movie club that
allows users to input there basic personal information (name, address,
telephone number) as well as movies in there collection. The movies will be
categorized by genre (comedy, romance, horror, etc.) and title. I want to
be able to let the users add and remove movies to their list of movies they
own, I'll call it "MOVIES I OWN".
> The user will also need to be able to create a SECOND list of movies they
would like to see, again they can choose by genre and title. They need to
also be able to add and remove from this list also, i'll call it "MOVIES I
WANT TO SEE".
> The last part of the project will be to match the users of "movies i want
to see" with "movies I own". It will be displayed on the local university
website when the user logs in and will alert the user to the match. If
there is a match at a later time, maybe the user can be emailed? Also if
there is a match, perhaps the two movies can be taken off record. I would
need it to be able to handle a small amount of users now logged in at the
same time , but would like for it to handle several hundred to thousand
users logged on at the same time in the distant future with out performance
problems.
> I am not sure if sql server 2000 is the best or perhaps oracle. I am
currently trying this on mySQL but currently lost in a forest of data. Any
guidance suggestions will be greatly appreciated. I am pretty new to this
so please be kind...
>sql
I am trying to create a database for a local university movie club that allows users to input there basic personal information (name, address, telephone number) as well as movies in there collection. The movies will be categorized by genre (comedy, romance, horror, etc.) and title. I want to be able to let the users add and remove movies to their list of movies they own, I'll call it "MOVIES I OWN".
The user will also need to be able to create a SECOND list of movies they would like to see, again they can choose by genre and title. They need to also be able to add and remove from this list also, i'll call it "MOVIES I WANT TO SEE".
The last part of the project will be to match the users of "movies i want to see" with "movies I own". It will be displayed on the local university website when the user logs in and will alert the user to the match. If there is a match at a later time, maybe the user can be emailed? Also if there is a match, perhaps the two movies can be taken off record. I would need it to be able to handle a small amount of users now logged in at the same time , but would like for it to handle several hundred to thousand users logged on at the same time in the distant future with out performance problems.
I am not sure if sql server 2000 is the best or perhaps oracle. I am currently trying this on mySQL but currently lost in a forest of data. Any guidance suggestions will be greatly appreciated. I am pretty new to this so please be kind...Most databases can do what you need, in terms of your database design. I
feel you should research more on the front-end, to address the 'several
hundred to thousand users' issue. A database can handle that load, given
the right hardware (and budget). But for cost and efficiency purposes, your
application layer should be capable of creating and managing a pool of
transactions so that connnections can be shared. It should also allow you
to specify the transaction isolation level to deal with concurrency issues.
Peter Yeoh
http://www.yohz.com
Need smaller backup files? Try MiniSQLBackup
"new_GUY" <new_GUY@.discussions.microsoft.com> wrote in message
news:51334783-729B-4E94-A7B8-D72E922DEAF4@.microsoft.com...
> I have a HUGE project and need some guidance.
> I am trying to create a database for a local university movie club that
allows users to input there basic personal information (name, address,
telephone number) as well as movies in there collection. The movies will be
categorized by genre (comedy, romance, horror, etc.) and title. I want to
be able to let the users add and remove movies to their list of movies they
own, I'll call it "MOVIES I OWN".
> The user will also need to be able to create a SECOND list of movies they
would like to see, again they can choose by genre and title. They need to
also be able to add and remove from this list also, i'll call it "MOVIES I
WANT TO SEE".
> The last part of the project will be to match the users of "movies i want
to see" with "movies I own". It will be displayed on the local university
website when the user logs in and will alert the user to the match. If
there is a match at a later time, maybe the user can be emailed? Also if
there is a match, perhaps the two movies can be taken off record. I would
need it to be able to handle a small amount of users now logged in at the
same time , but would like for it to handle several hundred to thousand
users logged on at the same time in the distant future with out performance
problems.
> I am not sure if sql server 2000 is the best or perhaps oracle. I am
currently trying this on mySQL but currently lost in a forest of data. Any
guidance suggestions will be greatly appreciated. I am pretty new to this
so please be kind...
>sql
Wednesday, March 21, 2012
need cursor help
hi
i want to create the sp to recreate the indexes of all the table. here
is what i need:
CREATE UNIQUE CLUSTERED INDEX
[table_name$32$0_idx] ON
[dbo].[table_name$32$0] ([bucket], [f2], [f5402], [f47], [f8])
WITH DROP_EXISTING
in order to get all the <table name> (columns)
i use sp_pkeys:
exec sp_pkeys [table_name$32$0]
here is the table where i insertet sp_pkeys data.
create table keys (
table_qualifier sysname,
table_owner sysname,
table_name sysname,
column_name sysname,
key_seq smallint,
pk_name sysname
)
i think i need to create a temp table and insert the data into it and
then fetch all i need into cursor ant exec CREATE INDEX WITH
DROP_EXISTING statment, but
i don't know how to convert column into a row (as sp_pkeys returns all
the index's keys into 1 column) .
thanxHi,
You could use DBCC DBREINDEX command to Reindex all indexes.
Use the below script to reindex all tables in a database:-
EXEC sp_MSforeachtable @.command1 = 'DBCC DBREINDEX ("?")'
Thanks
HARI
SQL Server MVP
"benamis" <nera@.meilo.lt> wrote in message
news:urRVtnFnFHA.3316@.TK2MSFTNGP14.phx.gbl...
> hi
> i want to create the sp to recreate the indexes of all the table. here is
> what i need:
> CREATE UNIQUE CLUSTERED INDEX
> [table_name$32$0_idx] ON
> [dbo].[table_name$32$0] ([bucket], [f2], [f5402], [f47], [f8])
> WITH DROP_EXISTING
> in order to get all the <table name> (columns)
> i use sp_pkeys:
> exec sp_pkeys [table_name$32$0]
> here is the table where i insertet sp_pkeys data.
> create table keys (
> table_qualifier sysname,
> table_owner sysname,
> table_name sysname,
> column_name sysname,
> key_seq smallint,
> pk_name sysname
> )
> i think i need to create a temp table and insert the data into it and
> then fetch all i need into cursor ant exec CREATE INDEX WITH DROP_EXISTING
> statment, but
> i don't know how to convert column into a row (as sp_pkeys returns all the
> index's keys into 1 column) .
> thanx|||Maybe this might help:
http://milambda.blogspot.com/2005/0...in-current.html
ML|||yes i know about DBCC DBREINDEX , but CREATE INDEX WITH DROP_EXISTING
is more efective [~2x times] (at least for navision db).
Hari Pra
wrote:
> Hi,
> You could use DBCC DBREINDEX command to Reindex all indexes.
> Use the below script to reindex all tables in a database:-
> EXEC sp_MSforeachtable @.command1 = 'DBCC DBREINDEX ("?")'
> Thanks
> HARI
> SQL Server MVP
> "benamis" <nera@.meilo.lt> wrote in message
> news:urRVtnFnFHA.3316@.TK2MSFTNGP14.phx.gbl...
>
>
>|||If you want to rebuild indexes without using the DBCC command, you can scrip
t
your the indexes and maybe create a job using this script.
You can script any object easily in the Enterprise Manager.
ML
i want to create the sp to recreate the indexes of all the table. here
is what i need:
CREATE UNIQUE CLUSTERED INDEX
[table_name$32$0_idx] ON
[dbo].[table_name$32$0] ([bucket], [f2], [f5402], [f47], [f8])
WITH DROP_EXISTING
in order to get all the <table name> (columns)
i use sp_pkeys:
exec sp_pkeys [table_name$32$0]
here is the table where i insertet sp_pkeys data.
create table keys (
table_qualifier sysname,
table_owner sysname,
table_name sysname,
column_name sysname,
key_seq smallint,
pk_name sysname
)
i think i need to create a temp table and insert the data into it and
then fetch all i need into cursor ant exec CREATE INDEX WITH
DROP_EXISTING statment, but
i don't know how to convert column into a row (as sp_pkeys returns all
the index's keys into 1 column) .
thanxHi,
You could use DBCC DBREINDEX command to Reindex all indexes.
Use the below script to reindex all tables in a database:-
EXEC sp_MSforeachtable @.command1 = 'DBCC DBREINDEX ("?")'
Thanks
HARI
SQL Server MVP
"benamis" <nera@.meilo.lt> wrote in message
news:urRVtnFnFHA.3316@.TK2MSFTNGP14.phx.gbl...
> hi
> i want to create the sp to recreate the indexes of all the table. here is
> what i need:
> CREATE UNIQUE CLUSTERED INDEX
> [table_name$32$0_idx] ON
> [dbo].[table_name$32$0] ([bucket], [f2], [f5402], [f47], [f8])
> WITH DROP_EXISTING
> in order to get all the <table name> (columns)
> i use sp_pkeys:
> exec sp_pkeys [table_name$32$0]
> here is the table where i insertet sp_pkeys data.
> create table keys (
> table_qualifier sysname,
> table_owner sysname,
> table_name sysname,
> column_name sysname,
> key_seq smallint,
> pk_name sysname
> )
> i think i need to create a temp table and insert the data into it and
> then fetch all i need into cursor ant exec CREATE INDEX WITH DROP_EXISTING
> statment, but
> i don't know how to convert column into a row (as sp_pkeys returns all the
> index's keys into 1 column) .
> thanx|||Maybe this might help:
http://milambda.blogspot.com/2005/0...in-current.html
ML|||yes i know about DBCC DBREINDEX , but CREATE INDEX WITH DROP_EXISTING
is more efective [~2x times] (at least for navision db).
Hari Pra
wrote:> Hi,
> You could use DBCC DBREINDEX command to Reindex all indexes.
> Use the below script to reindex all tables in a database:-
> EXEC sp_MSforeachtable @.command1 = 'DBCC DBREINDEX ("?")'
> Thanks
> HARI
> SQL Server MVP
> "benamis" <nera@.meilo.lt> wrote in message
> news:urRVtnFnFHA.3316@.TK2MSFTNGP14.phx.gbl...
>
>
>|||If you want to rebuild indexes without using the DBCC command, you can scrip
t
your the indexes and maybe create a job using this script.
You can script any object easily in the Enterprise Manager.
ML
Need current date in ssrs expression
I have a report which has a date like 01/05/2007 for a bill date. I need to create and expression which will take the current date and subtract the number of days from todays date and give a total number of days. How do I get thew current date in the expression.
the billed date expression is =FormatDateTime(Today()) - FormatDateTime(Fields!BILLDATE.value,2) I can not get this to work
Any help would be appreciated
You can execute code within a Report. You may need to do this although I think the reporting expressions should support what you are trying to accomplish. This is a good resource for you anyway though:http://support.microsoft.com/kb/920769
Hope this helps!
Matt
|||Thanks for the link but my expression isnt working in the report can you tell me what is wrong with it.
|||HI,cpowers:
You can try this expression:
= DateDiff("d",Fields!ShippedDate.Value ,Today() )
If i misunderstand you about your question, please feel free to correct me and i will try to help you with more information.
I hope the above information will be helpful. If you have any issues or concerns, please let me know. It's my pleasure to be of assistance
|||Rex Lin,
That works great thanks.. What I am tring to accomplish is a 1-30 31-60 61-90 days aging
I tried to di the following expression-
=DateDiff("d",Fields!ShippedDate.Value,Today()) < 30 for the 1-30 day
=DateDiff('d".Fields!ShippedDate.Value, Today()) >30<61 for the 31-60 day I am getting a true or false instead of the days is there away to modify the expression to give only the number of days that fall within the range
|||
HI,cpowers:
Hope this will meet your requirement:
=iif( DateDiff("d",Fields!ShippedDate.Value,Today())<30,DateDiff("d",Fields!ShippedDate.Value,Today()),"out of range")
|||I am tring to get a total for the amount owed that is over 30days but under 60days. My expression just gives me the word True does not give me the total MASRCVAMOUNT $$ in that date range. Can come one explain or help with expression...=FormatCurrency(Sum(Fields!MASRCVAMOUNT.Value))=DateDiff("d",Fields!Expr1.Value ,Today())>31<60
I also need to no how to get total MASRCVAMOUNT over 120 days
=iif( DateDiff("d",Fields!ShippedDate.Value,Today())<30,DateDiff("d",Fields!ShippedDate.Value,Today()),"out of range") How would I sum the MASCVAMOUNT in this date range and how can I select a between 61-91days these are my two real issues. I have the dates working.
Thanks for all the help I do appreciate it
|||HI，cpowers：
It seems that the goal you need to achieve is not easily for expression. How about deal with it in the sql statement?
|||Thanks for the help Rex Lin,
Ok I added the daging date to my sql script. Every thing saves ok and in my parameters there is now an new entry filter. Before I only had a StartDate, Enddate and Company. Now I have the Startdate , Enddate Company and dAging. I have tried to enter a date in to the dAging but it keeps giving me and error.This script was added in the creation of the ssrs report in the ssrs query builder. Can you explain to me what I may be missing or need to do to get my 1-30 , 31 60, 61-90 and 91 and over amounts.... The field I am collecting the amounts from is MASRECEIVL.MASRCVAMOUNT I am really stuck with this and need any help availible. Thanks
SELECT ' Language Detail by Company' as 'qtr',
COUNT(JOB.JOBID) as 'transcount',
COUNT(DISTINCT JOB.PATIENTID) as 'patient count',
SUM(JOB.LANGUAGE_TCOST) as 'lcost',
SUM(JOB.LANGUAGE_DISC_COST) as 'dlcost',
AVG(JOB.LANGUAGE_DISC) as 'avgLDisc',
SUM(JOB.LANGUAGE_TCOST) + SUM(JOB.LANGUAGE_DISC_COST) as 'LGrossAmtBilled',
SUM(JOB.LANGUAGE_TCOST) / COUNT(DISTINCT JOB.PATIENTID) as 'PatAvgL',
SUM(JOB.LANGUAGE_TCOST) / COUNT(JOB.JOBID) as 'RefAvgL',
SUM(JOB.LANGUAGE_DISC) as 'avgPercentDiscL',
JOB.JURISDICTION,PAYER.PAY_GROUPNAME,PAYER.PAY_COMPANY,PAYER.PAY_CITY,PAYER.PAY_STATE,PAYER.PAY_SALES_STAFF_ID,
JOB.INVOICE_DATE,JOB.PATIENTID,JOB.JOBOUTCOMEID,JOB.SERVICEOUTCOME,INVOICE_AR.INVOICE_NO,INVOICE_AR.INVOICE_DATE AS EXPR1,
INVOICE_AR.AMOUNT_DUE,INVOICE_AR.CLAIMNUMBER,PATIENT.LASTNAME,PATIENT.FIRSTNAME,PATIENT.EMPLOYERNAME,JOB_OUTCOME.DESCRIPTION,
SERVICE_TYPE.DESCRIPTION,PAT_SERVICES_HISTORY.LANG_TYPE,MASRECEIVL.MASRVDATE,MASRECEIVL.MASRCVAMOUNT,REFERRAL_SOURCE.REF_LASTNAME,
REFERRAL_SOURCE.REF_FIRSTNAME,REFERRAL_SOURCE.REF_PHONE,REFERRAL_SOURCE.REF_PHONE_EXT,REFERRAL_SOURCE.REF_FAX,REFERRAL_SOURCE.REF_EMAIL,JOB.INJURYDATE,JOB.APPT_DATE,
SUM(CASE WHEN (JOB_OUTCOME.DESCRIPTION = 'Completed with complaint') THEN 1 ELSE 0 END) AS 'CompletedWithcomplaintItems',
SUM(CASE WHEN (JOB_OUTCOME.DESCRIPTION = 'Completed Successfully') THEN 1 ELSE 0 END) AS 'CompletedSuccessfullyItems',
SUM(CASE WHEN (JOB_OUTCOME.DESCRIPTION = 'Completed with No Charge') THEN 1 ELSE 0 END) AS 'CompletedwithNoChargeItems',
SUM(CASE WHEN (JOB_OUTCOME.DESCRIPTION = 'Completed with No Show') THEN 1 ELSE 0 END) AS 'CompletedwithNoShowItems',
SUM(CASE WHEN (JOB_OUTCOME.DESCRIPTION = 'Completed with Situation') THEN 1 ELSE 0 END) AS 'CompletedWithSituationItems',
SUM(CASE WHEN (JOB_OUTCOME.DESCRIPTION = 'Not Completed') THEN 1 ELSE 0 END) AS 'NotCompletedItems',
SUM(CASE WHEN (JOB_OUTCOME.DESCRIPTION = 'Cancelled Prior to service') THEN 1 ELSE 0 END) AS 'CancelledPriortoserviceItems',
SUM(CASE WHEN (JOB_OUTCOME.DESCRIPTION = 'Cancelled During Service') THEN 1 ELSE 0 END) AS 'CancelledDuringServiceItems',
SUM(CASE WHEN (JOB_OUTCOME.DESCRIPTION = 'Awaiting for completion') THEN 1 ELSE 0 END) AS 'AwaitingforcpmpletionItems',
SUM(CASE WHEN (JOB_OUTCOME.DESCRIPTION = 'Pending for review') THEN 1 ELSE 0 END) AS 'PendingforreviewItems',
SUM(CASE WHEN INVOICE_AR.INVOICE_DATE BETWEEN @.dAgingDate-30 and @.dAgingDate THEN MASRECEIVL.MASRCVAMOUNT Else 0 END) AS Age30,
SUM(CASE WHEN INVOICE_AR.INVOICE_DATE BETWEEN @.dAgingDate-60 and @.dAgingDate-31 THEN MASRECEIVL.MASRCVAMOUNT Else 0 END) AS Age60,
SUM(CASE WHEN INVOICE_AR.INVOICE_DATE BETWEEN @.dAgingDate-90 and @.dAgingDate-61 THEN MASRECEIVL.MASRCVAMOUNT Else 0 END) AS Age90,
SUM(CASE WHEN INVOICE_AR.INVOICE_DATE BETWEEN @.dAgingDate-120 and @.dAgingDate-91 THEN MASRECEIVL.MASRCVAMOUNT Else 0 END) AS Age120
FROM JOB
INNER JOIN INVOICE_AR ON JOB.JOBID = INVOICE_AR.JOBID
LEFT OUTER JOIN PAYER ON PAYER.PAYERID = JOB.PAYERID
LEFT OUTER JOIN MASRECEIVL ON MASRECEIVL.INVOICE_NO = INVOICE_AR.INVOICE_NO
LEFT OUTER JOIN REFERRAL_SOURCE ON REFERRAL_SOURCE.REFERRAL_ID = JOB.ADJUSTERID
LEFT OUTER JOIN STATES ON JOB.JURISDICTION = STATES.INITIALS
LEFT OUTER JOIN PATIENT ON PATIENT.PATIENTID = JOB.PATIENTID
LEFT OUTER JOIN JOB_OUTCOME ON JOB_OUTCOME.JOB_OUTCOME_ID = JOB.JOBOUTCOMEID
LEFT OUTER JOIN SERVICE_TYPE ON SERVICE_TYPE.DESCRIPTION = JOB.SERVICEOUTCOME
LEFT OUTER JOIN PAT_SERVICES_HISTORY ON PAT_SERVICES_HISTORY.PATIENTID = JOB.PATIENTID
WHERE
(INVOICE_AR.AMOUNT_DUE > 0) AND (INVOICE_AR.INVOICE_DATE BETWEEN @.startdate and @.enddate)AND
(MONTH(INVOICE_AR.INVOICE_DATE) IN (1,2,3,4,5,6,7,8,9,10,11,12)) AND (PAYER.PAY_Company like '%' + @.Company + '%') AND
(INVOICE_AR.INVOICE_NO like '%L') AND (MASRECEIVL.MASRCVAMOUNT > 0)
Group By
JOB.JURISDICTION,
PAYER.PAY_GROUPNAME,
PAYER.PAY_COMPANY,
PAYER.PAY_CITY,
PAYER.PAY_STATE,
PAYER.PAY_SALES_STAFF_ID,
JOB.PATIENTID,
JOB.INVOICE_DATE,
JOB.JOBOUTCOMEID,
JOB.SERVICEOUTCOME,
INVOICE_AR.INVOICE_NO,
INVOICE_AR.INVOICE_DATE,
INVOICE_AR.AMOUNT_DUE,
INVOICE_AR.CLAIMNUMBER,
PATIENT.LASTNAME,
PATIENT.FIRSTNAME,
PATIENT.EMPLOYERNAME,
JOB_OUTCOME.DESCRIPTION,
SERVICE_TYPE.DESCRIPTION,
PAT_SERVICES_HISTORY.LANG_TYPE,
MASRECEIVL.MASRVDATE,
MASRECEIVL.MASRCVAMOUNT,
REFERRAL_SOURCE.REF_LASTNAME,
REFERRAL_SOURCE.REF_FIRSTNAME,
REFERRAL_SOURCE.REF_PHONE,
REFERRAL_SOURCE.REF_PHONE_EXT,
REFERRAL_SOURCE.REF_FAX,
REFERRAL_SOURCE.REF_EMAIL,
JOB.INJURYDATE,
JOB.APPT_DATE
Order By 'QTR' asc
|||HI,cpowers:
Your sql statement is quite complicated than i expected. I have created a simple one by using Orders table in the Northwind. It brings out the sum value of 30-60 and 60-90 date intervals
select
sum(case when timespan between 0 and 29 then shipvia end )as zeroToThirty,
sum(case when timespan between 30 and 59 then shipvia end )as ThirtyToSixty
from
(select (datediff(dd,getdate(),orderdate)+4000)as timespan,*
from dbo.orders) as newtable
If i misunderstand you about your question, please feel free to correct me and i will try to help you with more information.
I hope the above information will be helpful. If you have any issues or concerns, please let me know. It's my pleasure to be of assistance
|||Rex Lin,
I am having similar problems, except all I want is to substract current date from the promised date (date that is was suppose to ship) and be able to view only the negetive data. I tried =GETDATE()-Fields!PROMISE.Value
but it would give me an error, then I tried the format you gave cpowers
=DateDiff("d",fields!Promise.Value, Today()) but all it did was give me one number. Am I doing it wrong? What do you recomend? Please advice
Thanks
Need assitance to Launch MSDE and create a new database.
Installed MSDE. Icon on system tray shows "not connected". Start, Stop &
Pause are greyed out. I am trying to connect to a database. Please help.
Regards,
Shah.
hi, Shah,
Shah wrote:
> Installed MSDE. Icon on system tray shows "not connected". Start,
> Stop & Pause are greyed out. I am trying to connect to a database.
> Please help.
the "problem" with the SQL Server Service Manager can be related with
http://support.microsoft.com/default...;EN-US;814132, but you
should be able to connect to your MSDE instance anyway...
please verify in the service management applet your instance is (correctly)
installed and running...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
sql
Pause are greyed out. I am trying to connect to a database. Please help.
Regards,
Shah.
hi, Shah,
Shah wrote:
> Installed MSDE. Icon on system tray shows "not connected". Start,
> Stop & Pause are greyed out. I am trying to connect to a database.
> Please help.
the "problem" with the SQL Server Service Manager can be related with
http://support.microsoft.com/default...;EN-US;814132, but you
should be able to connect to your MSDE instance anyway...
please verify in the service management applet your instance is (correctly)
installed and running...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
sql
Monday, March 19, 2012
Need an example of failed CREATE PROC...
This is an example of a working CREATE PROCEDURE.
USE Northwind
GO
CREATE PROC dbo.OverdueOrders
AS
SELECT *
FROM dbo.Orders
WHERE RequiredDate < GETDATE() AND ShippedDate IS Null
What does it mean that "a CREATE PROCEDURE statement cannot be combined with
other TSQL statements in a single batch?" I see a TSQL stmt in this working
one.
Would someone provide me an example, please?
Thanks.
USE Northwind
GO
SELECT *
FROM dbo.Orders
CREATE PROC dbo.OverdueOrders
AS
SELECT *
FROM dbo.Orders
WHERE RequiredDate < GETDATE() AND ShippedDate IS Null
GO
"light_wt" <lightwt@.discussions.microsoft.com> wrote in message
news:8E891A71-BE7D-495A-B134-CC3C1D1E2367@.microsoft.com...
> This is an example of a working CREATE PROCEDURE.
> USE Northwind
> GO
> CREATE PROC dbo.OverdueOrders
> AS
> SELECT *
> FROM dbo.Orders
> WHERE RequiredDate < GETDATE() AND ShippedDate IS Null
> What does it mean that "a CREATE PROCEDURE statement cannot be combined
with
> other TSQL statements in a single batch?" I see a TSQL stmt in this
working
> one.
> Would someone provide me an example, please?
> Thanks.
|||Here you go:
USE Northwind
GO
SELECT * FROM Orders
CREATE PROC dbo.OverdueOrders
AS
SELECT *
FROM dbo.Orders
WHERE RequiredDate < GETDATE()
AND ShippedDate IS NULL
GO
Anith
|||A batch is a set of TSQL statements that are submitted together for
processing. You designate the end of a batch of commands in SQL Server
using the GO statement.
There are only two CREATE statements that can be combined in a single batch.
CREATE DATBASE and CREATE TABLE.
All of the other CREATE statements must be submitted as separate batches.
So:
CREATE DATABASE Frog (....)
CREATE TABLE LilyPad (...)
CREATE TABLE Swim(...)
GO
Would work, but
CREATE TABLE LilyPad(...)
CREATE PROC Croak ...
Would not.
You would have to submit them as:
CREATE TABLE LilyPad...
GO
CREATE PROC Croak...
GO
HTH
Rick Sawtell
MCT, MCSD, MCDBA
"light_wt" <lightwt@.discussions.microsoft.com> wrote in message
news:8E891A71-BE7D-495A-B134-CC3C1D1E2367@.microsoft.com...
> This is an example of a working CREATE PROCEDURE.
> USE Northwind
> GO
> CREATE PROC dbo.OverdueOrders
> AS
> SELECT *
> FROM dbo.Orders
> WHERE RequiredDate < GETDATE() AND ShippedDate IS Null
> What does it mean that "a CREATE PROCEDURE statement cannot be combined
with
> other TSQL statements in a single batch?" I see a TSQL stmt in this
working
> one.
> Would someone provide me an example, please?
> Thanks.
|||Thank you, all.
I appreciate your detailed and good explaination, Rick.
"Rick Sawtell" wrote:
<snip>
USE Northwind
GO
CREATE PROC dbo.OverdueOrders
AS
SELECT *
FROM dbo.Orders
WHERE RequiredDate < GETDATE() AND ShippedDate IS Null
What does it mean that "a CREATE PROCEDURE statement cannot be combined with
other TSQL statements in a single batch?" I see a TSQL stmt in this working
one.
Would someone provide me an example, please?
Thanks.
USE Northwind
GO
SELECT *
FROM dbo.Orders
CREATE PROC dbo.OverdueOrders
AS
SELECT *
FROM dbo.Orders
WHERE RequiredDate < GETDATE() AND ShippedDate IS Null
GO
"light_wt" <lightwt@.discussions.microsoft.com> wrote in message
news:8E891A71-BE7D-495A-B134-CC3C1D1E2367@.microsoft.com...
> This is an example of a working CREATE PROCEDURE.
> USE Northwind
> GO
> CREATE PROC dbo.OverdueOrders
> AS
> SELECT *
> FROM dbo.Orders
> WHERE RequiredDate < GETDATE() AND ShippedDate IS Null
> What does it mean that "a CREATE PROCEDURE statement cannot be combined
with
> other TSQL statements in a single batch?" I see a TSQL stmt in this
working
> one.
> Would someone provide me an example, please?
> Thanks.
|||Here you go:
USE Northwind
GO
SELECT * FROM Orders
CREATE PROC dbo.OverdueOrders
AS
SELECT *
FROM dbo.Orders
WHERE RequiredDate < GETDATE()
AND ShippedDate IS NULL
GO
Anith
|||A batch is a set of TSQL statements that are submitted together for
processing. You designate the end of a batch of commands in SQL Server
using the GO statement.
There are only two CREATE statements that can be combined in a single batch.
CREATE DATBASE and CREATE TABLE.
All of the other CREATE statements must be submitted as separate batches.
So:
CREATE DATABASE Frog (....)
CREATE TABLE LilyPad (...)
CREATE TABLE Swim(...)
GO
Would work, but
CREATE TABLE LilyPad(...)
CREATE PROC Croak ...
Would not.
You would have to submit them as:
CREATE TABLE LilyPad...
GO
CREATE PROC Croak...
GO
HTH
Rick Sawtell
MCT, MCSD, MCDBA
"light_wt" <lightwt@.discussions.microsoft.com> wrote in message
news:8E891A71-BE7D-495A-B134-CC3C1D1E2367@.microsoft.com...
> This is an example of a working CREATE PROCEDURE.
> USE Northwind
> GO
> CREATE PROC dbo.OverdueOrders
> AS
> SELECT *
> FROM dbo.Orders
> WHERE RequiredDate < GETDATE() AND ShippedDate IS Null
> What does it mean that "a CREATE PROCEDURE statement cannot be combined
with
> other TSQL statements in a single batch?" I see a TSQL stmt in this
working
> one.
> Would someone provide me an example, please?
> Thanks.
|||Thank you, all.
I appreciate your detailed and good explaination, Rick.
"Rick Sawtell" wrote:
<snip>
Need an example of a trigger.
Hello,
I'm a newbie to the MS SQL db engine, and I'd like to have some help with Transact-SQL. I'd like to know the code to create a trigger to do the following. I have two tables, one references the other. I'd like the database to automatically delete the records from table2 when the record that they reference in table1 is deleted. Much thanks.Try this
create trigger dbo.trigger_deleterecs on table1
for delete
as
begin
declare @.refcolumn datatypehere
select @.refcolumn = deleted.refcolumn from deleted
delete from table2 where table2refcolumn = @.refcolumn
end|||how about:
create trigger dbo.trigger_deleterecs on table1
for delete
as
begin
delete from table2 where refcolumn in (select refcolumn from deleted)
end
or
create trigger dbo.trigger_deleterecs on table1
for delete
as
begin
delete t2
from table2 t2
join deleted d on t2.refcolumn = d.refcolumn
end
by using either of these you will delete all records when multipule records are deleted.
I'm a newbie to the MS SQL db engine, and I'd like to have some help with Transact-SQL. I'd like to know the code to create a trigger to do the following. I have two tables, one references the other. I'd like the database to automatically delete the records from table2 when the record that they reference in table1 is deleted. Much thanks.Try this
create trigger dbo.trigger_deleterecs on table1
for delete
as
begin
declare @.refcolumn datatypehere
select @.refcolumn = deleted.refcolumn from deleted
delete from table2 where table2refcolumn = @.refcolumn
end|||how about:
create trigger dbo.trigger_deleterecs on table1
for delete
as
begin
delete from table2 where refcolumn in (select refcolumn from deleted)
end
or
create trigger dbo.trigger_deleterecs on table1
for delete
as
begin
delete t2
from table2 t2
join deleted d on t2.refcolumn = d.refcolumn
end
by using either of these you will delete all records when multipule records are deleted.
Subscribe to:
Comments (Atom)