|
Can you give a better description of what you are trying to do?
Use some pseudo-code if necessary.
Also, you listed your inputs as @schema, @source_name, @source_name (@source_name twice).
Tim
|
|
|
|
|
Hi, I have a list of table that I need to make them available for auditing by using that particular procedure. I had in mind to call that procedure in a cursor which identifies the table names, builds the script for that procedure by passing the table name and the source to the procedure and executes it. This procedure will be repeated for all the tables. This will avoid hard coding the procedure for all the tables (which I have over 150).
Yes yu're right they should be as listed below;
DECLARE Alter_tables_cursor CURSOR FAST_FORWARD
FOR
select table_name from information_schema.tables where table_name<>'dtProperties' and table_type<>'VIEW'
OPEN Alter_tables_cursor
DECLARE @tablename sysname
FETCH NEXT FROM Alter_tables_cursor INTO @tablename
WHILE ( @@FETCH_STATUS = 0 )
BEGIN
EXECUTE 'sys.sp_cdc_enable_table
@source_schema = N''dbo'',
@source_name = N''TBL_ACTIVITY_ACT'',
@role_name = NULL'
FETCH NEXT FROM Alter_tables_cursor INTO @tablename
END
DEALLOCATE Alter_tables_cursor
Thanks for your reply.
|
|
|
|
|
This is my store procedure but where i am doing wrong
ALTER PROCEDURE [dbo].[Engagement_Get_StandardYears]
(
@clientName NVARCHAR(255)=NULL
)
AS
BEGIN
SET NOCOUNT ON;
SELECT Distinct [Year]
FROM Engagement_Standards
WHERE [Year] IS NOT NULL AND [Client Name] like
CASE
WHEN @clientName IS NULL THEN %
ELSE @clientName
END
ORDER BY [Year];
END
|
|
|
|
|
@clientName NVARCHAR(255)=NULL
Unless you are using unicode characters change all your nvarchar to varchar (non critical but I would do this early)
WHERE [Year] IS NOT NULL
I prefer yo use
WHERE Inull([Year],0) > 0
Also note you are using a reserved word in as a column name - not recommended. You should NEVER need square brackets
Your killer though is this one
WHEN @clientName IS NULL THEN %
ELSE @clientName
change the % to ''
[edit]No wait that is in the where clause
I'm scratching my head trying to understand what you are trying to achieve! Ah got it dynamic where clause, here is one I use all the time
Where (IsNull(@clientName , '') = '' OR clientName = @clientName )
[/edit]
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I use some thing like this Mycroft Holmes.
USE [INVENTORY_ MANAGEMENT]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
Create PROCEDURE [dbo].[Engagement_Get_StandardsByCriteria ]
-- Add the parameters for the stored procedure here
(
@ClientName NVARCHAR(255)=NULL,
@Year FLOAT=NULL,
@Application NVARCHAR(255) =NULL,
@Database NVARCHAR(255)=NULL,
@OS NVARCHAR(255)=NULL,
@QuickName NVARCHAR(MAX)=NULL
)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
SELECT [Client Name] AS Client,[Year],[Application],
[Database],OS,[MLP Heading] As Heading,
[Standard Observation] AS Observation,[Standard Impact]AS Impact,
[Standard Recommendation] AS Recommendation
FROM Engagement_Standards
WHERE (@ClientName IS NULL OR [Client Name]=@ClientName)
And
(@Year IS NULL OR [Year]=@Year)
And
(@Application IS NULL OR [Application]=@Application)
And
(@Database IS NULL OR [Database]=@Database)
And
(@OS IS NULL OR OS=@OS)
And
(@QuickName IS NULL OR [Quick Name]=@QuickName);
END
|
|
|
|
|
Eeuww - how horrible, working with your database would drive me nuts, who decided to use descriptors as column names. However your where construction is fine.
I have found that you hit a limit on the number of where filters like this you can use, after about 6 or 7 performance can be affected, I have no idea why is I have only seen it a couple of times.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
|
Hello guys...im new to database administration. What does it mean when we say that we are running two instances of SQL Sever on my computer?? Does this mean, installng sql server two times with different serial numbers??
|
|
|
|
|
|
|
Microsoft® SQL Server™ 2000 supports multiple instances of the SQL Server database engine running concurrently on the same computer. Each instance of the SQL Server database engine has its own set of system and user databases that are not shared between instances. Applications can connect to each SQL Server database engine instance on a computer in much the same way they connect to SQL Server database engines running on different computers.
For more information about this visit http://msdn.microsoft.com/en-us/library/aa174516%28SQL.80%29.aspx[
|
|
|
|
|
Why point to a version 2 releases back?
Your link really screwed up the formatting 
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I have a SQL Server 2000 program which has logging sp which writes a record to a table and also print of that record. i.e. we can run the program in QA and see what its doing.
However QA only updates every so often...is there a way we can make it update more regularly?
It just to help with testing/development. In production a GUI sits on top.
|
|
|
|
|
Instead of the print try populating a table that a second QA session can access and query that while the sp is running.
You could try linking SQL 2008 Express and running from there as 2008 reports back more often.
Another trick is to put in "timestamps" on your print so you can tell where things are taking so long.
As you can tell tuning is not my specialty, so I hope one of these helps or someone comes up with a better idea.
Good Luck.
|
|
|
|
|
Hi
I have 2 tables Table A and Table B
I want to join these tables.
The relation between these tables is RegNo
The first table stores the Trudent details and second table is a history table
The regNo in the first table is primary key and second table is forign key
I want to Join all the data in the these two tables based on Date and Last updated time
I want the result like the following
pls help me to find a soln
RegNo Name Class Date
12ABC ABC 10 3/2/2010
13ABC DCB 1 3/2/2010
14DFG DFG 12 3/2/2010
15FHG FHG 5 3/2/2010
16RTY RTY 8 3/3/2010
Table B
Id RegNo LastUpdated
1 12ABC 3/2/2010 12:30
2 12ABC 3/2/2010 13:00
3 12ABC 3/2/2010 16:00
4 13BC 3/2/2010 12:00
Result
RegNo Name Class LastUpdated
12ABC ABC 10 3/2/2010 16:00
13ABC DCB 1 3/2/2010 12:00
14DFG DFG 12 NULL
15FHG FHG 5 NULL
|
|
|
|
|
Here is an article [^]that will help you understand what you want
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hope below code will resolve you problem
select a.regno, a.name , a.class, b.date from table a
left outer join
( select * from tableb wehre id = (select max(id) from tablb group by regno)) b
on a.regno = b.regno
|
|
|
|
|
Hi,
This query dose not answer because RegNo in Table_B in Id 4 dose not equal
with RegNo with Table_A,
But if it's correct this is answer
select a.RegNo,a.Name,count(a.Class),b.LastUpdated from Table_A as a
left join Table_B as b on a.RegNo=b.RegNo
where (b.Id between 3 and 4 ) or (a.RegNo Not In('16RTY')and a.RegNo In('14DFG','15FHG'))
group by a.RegNo,a.Name,b.LastUpdated
|
|
|
|
|
Hi Friends,
I have to create a view using a few tables. One table has a column having four values (A,B,C,D), I want the view have a column only have two values (say for A,B,C I want the value to be "INTERNAL", and for D the value is "EXTERNAL")
How can I solve this problem? Do I have to use a procedure? can anyone post a sample for me?
Many thanks...
^_^
|
|
|
|
|
I would assume Oracle has a case statement, something like the following works in SQL Server.
case where colA = 'D' then 'External' else 'internal' end
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
|
Dear All,
I am looking to query followings in Sql 2005 table as
Table A
ID Name
1 A
1 B
1 C
2 D
2 E
2 F
I want the result as
ID Name
1 A,B,C
2 D,E,F
any ideas
Abdul Rahaman Hamidy
Database Developer
Kabul, Afghanistan
|
|
|
|
|
You have to use cursor. check this.[^]
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
www.aktualiteti.com
|
|
|
|
|
Thanks. I have used for Xml Path and I have done it in SQl 2005.
but i want to do it only with pure T-Sql which I need the same thing in Ms Access also.
any ideas for Ms Access 2003/2007
Abdul Rahaman Hamidy
Database Developer
Kabul, Afghanistan
|
|
|
|
|
Following one of the solution work for you but there must be better solution than this
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date, ,>
-- Description: <Description, ,>
-- =============================================
CREATE FUNCTION tableaValue
(
@id int
)
RETURNS varchar(50)
AS
BEGIN
declare @name varchar(50)
declare @tempString varchar(50)
DECLARE tablea_cursor CURSOR FOR
select name from tablea where id = @id -- Variable value from the outer cursor
OPEN tablea_cursor
FETCH NEXT FROM tablea_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @tempString= isnull(@tempString,'') + ',' + @name
FETCH NEXT FROM tablea_cursor INTO @name
END
CLOSE tablea_cursor
DEALLOCATE tablea_cursor
-- Return the result of the function
RETURN @tempString
END
GO
select dbo.tableaValue(id),id from tablea group by id
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
