|
hi , i would like to fire set of queries when a select query is fired on to particular table, can any one help in this ..
|
|
|
|
|
As far as I know, this can't be done with triggers if that is what you are thinking.
Why don't you create a stored procedure which does your "queries" and have the applications use that stored procedure instead of directly accessing the table ?
Just a thought.
|
|
|
|
|
Triggers are valid for events, select is not considered an event. Besides triggers are EVIL  .
I'm curious as to the requirement for a trigger on a select, why do you need it
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi,
I have queries regarding Database design in sql server 2005.
I have Book Library. I have a master where i am storing different category of writers for eg. Story Writes, Poets, Technical writers, Fiction Writes etc.
For eg. I have stored Louies David who is a Story writer as well as Poet.
I want to develop a search for above category for selected name or populate a list box for single category.
I have also another processes like contract for Books which is in transit but need to store details in Database. for eg Book Contract. It is having status as 'Approved' or 'Transit'. But i Need to store the categories and name of the writes in the database as well.
I have created Writes Category Table with Metadata for eg,
1) Table - WriterCategory
WriterCatID int - not Null
WriteCategory - int - metadata value ( Binary number for eg. 2) then for next record it will be 4, next will be 16 .. as so on.
CategoryDescrition - Varchar(50) - for . eg. Story Writer
2) Table - WriterMaster
WriterMasterID - int
WriterName - varchar(50) for eg, Louies David
Metadata Total - metadata which is selected by user for eg. He is Storywriter + Poet then 2 + 4 = 6 will be stored in this table
3) WriterMasterDetail
WriterMasterDetailID - Int
WriterMasterID - FK of WriterMaster
Metadata - individual category Number stored in WriterCategory
From above Table i can populate list boxes for categories as well as from Name i can find the category of the selected name.
But when I want to this to be also get values for BooKContract then I should i stored the value. Should i go with same Masterdetails Table ?
The above design was to reduce table for each categories and redunant data. I want to maintain the same thorugh out the application ? I will have to store each CategoryID with WriterMasterID for each contract and will have to retrive with query which gives with multiple records along with metadata. I realised that i will have to first find the distinct name and then iterate for type of metadata to store BookContract.
Can u please suggest ? Do I need to stored each WriterMasterID for each category as column in table? Please suggest better solution.
Thanks in Advance.
|
|
|
|
|
meerabhuva wrote: The above design was to reduce table for each categories and redunant data.
Apply normalization techniques, and you'll get the correct design.
meerabhuva wrote: WriteCategory - int - metadata value ( Binary number for eg. 2) then for next record it will be 4, next will be 16 .. as so on.
Why did you skip 1 and 8? Your datatype says integer, how many items could you store in there? It looks like a running value btw, are you sure you don't want an Identity?
meerabhuva wrote: Metadata Total - metadata which is selected by user for eg. He is Storywriter + Poet then 2 + 4 = 6 will be stored in this table
Table: WriterKind
Id Caption
1 Storywriter
2 Poet
3 Scriptwriter
Table: WriterMaster
Id Name WriterKind
1 Berton Braley 2
It might be a flagged-enum in your code, but that doesn't make it a good idea to store it that way in a relational database.
I are Troll 
|
|
|
|
|
meerabhuva wrote: WriteCategory - int - metadata value ( Binary number for eg. 2)
That's one way, but it will limit you to thirty-two categories. Better to have a Category table and allow many-to-many relationships with Books and Authors.
Category
ID Description
1 Mystery
2 Romance
3 Sci-Fi
Author
ID Name
1 Douglas Adams
AuthorCategory
AuthorID CategoryID
1 3 <-- Douglas Adams + Sci-Fi
Book
ID Title
1 The HitchHikers...
BookCategory
BookID CategoryID
1 3 <-- The HitchHikers... + Sci-Fi
|
|
|
|
|
Here is my SQL
select
ID as [Emp ID],
Name as [Employee name],
Sal as [Salary]
from Emp
FOR XML AUTO, ELEMENTS, ROOT('customers')
When I issue this SQL then unknown data is added in xml with my field name.
I got the xml like
<customers>
<Emp>
<Emp_x0020_ID>1</Emp_x0020_ID>
<Employee_x0020_name>bob</Employee_x0020_name>
<Salary>2020</Salary>
</Emp>
<Emp>
<Emp_x0020_ID>2</Emp_x0020_ID>
<Employee_x0020_name>keith</Employee_x0020_name>
<Salary>6500</Salary>
</Emp>
<Emp>
<Emp_x0020_ID>3</Emp_x0020_ID>
<Employee_x0020_name>markc</Employee_x0020_name>
<Salary>5400</Salary>
</Emp>
</customers>
It shows <Emp_x0020_ID> instead of this <Emp ID>
because I mention column alias with space like "Emp ID". how could
modify my SQL as a result space will not be ignore I mean my xml would
<customers>
<Emp>
<Emp ID>1</Emp ID>
<Employee name>bob</Employee name>
<Salary>2020</Salary>
</Emp>
</customers>
please help me......urgent.
look like
tbhattacharjee
|
|
|
|
|
Tridip Bhattacharjee wrote: please help me......urgent.
I don't see anything urgent here.
However, if you read the XML specification you may notice that node names may not contain spaces, so your Emp ID label has been manipulated to make it comply with the rules.
I must get a clever new signature for 2011.
|
|
|
|
|
declare @SQL nvarchar(100) set @SQL = ''
select @SQL = @SQL + FieldName + ' as [' + Description + '], ' from FieldsInfo where TableName = 'Emp'
set @SQL = 'select '+ left(@SQL, len(@SQL)-1) + ' from Emp FOR XML AUTO, ELEMENTS, ROOT(''customers'')'
exec sp_executesql @SQL
above is dynamic sql and when it is executed by sp_executesql then i got output as as xml. if i want to store that xml in a variable. so then what i need to add in my tsql script....please help
tbhattacharjee
|
|
|
|
|
You would normally use a dynamic code like this for interface with a front end like .NET.
What you need to do is add INTO mytable before the FROM or define a table and use INSERT INTO mytable.
|
|
|
|
|
Replace "exec sp_executesql @SQL" with:
declare @xml xml
set @SQL = 'set @xml =('+@SQL+')' -- stores query result to @xml variable
exec sp_executesql @SQL,N'@SQL nvarchar(4000),@xml xml OUTPUT',@SQL,@xml OUTPUT
--- display result
print convert(varchar(MAX),@xml)
|
|
|
|
|
suppose i have two tables in sql server one is Emp and another is FieldsInfo. both table structure look like below
CREATE TABLE [dbo].[Emp](
[ID] [int] NOT NULL,
[EmpName] [varchar](50) NULL,
[Sal] [nchar](10) NULL,
)
CREATE TABLE [dbo].[FieldsInfo](
[TableName] [varchar](max) NULL,
[FieldName] [varchar](max) NULL,
[Description] [varchar](max) NULL
)
now i want to join both the table such way that sql should show data from emp table but field name will be shown from FieldsInfo table according to the relation. i mean Employee ID will be shown as Field name instead of ID and Salary will be shown as field name instead of Sal but value will be just like emp table. i am not being able to generate this type of output after joining. so please help with right sql script.
thanks
tbhattacharjee
|
|
|
|
|
|
I think Pvot will help you...
HELLO GUY
|
|
|
|
|
|
Dear All,
I need to create a procedure that creates an outlook task for a set of domain users when some condition in the data base is fired.
Is this possible?
I am using SQL Server 2008 R2, Exchange Outlook 2010
Regards
Kind Regards
OBarahmeh
|
|
|
|
|
|
Hi,
I need to create the tasks through an SQL Server procedure and not using .NET
Do u have any ideas about how to do this in a procedure inside the SQL Server?
Regards
Kind Regards
OBarahmeh
|
|
|
|
|
I'm afraid you won't be able to directly create a task through SQL Server, but I'm not an absolute expert at your requirement.
However, this is how I might approach the problem.
1) In TSQL, there is a command called, xp_cmdshell, which allows you to execute a command shell
2) Create a command-line application that would take as command line parameters the values you need to create a Task.
There are significant security issues related to enabling xp_cmdshell, so you may want to evaluate those issues first.
|
|
|
|
|
Hi,
I have a problem with strings containing ' (that is the character ').
The strings need to be saved to a MySQL database, but the MySQLCommand doesn't accept the ' character. It only accept it as \'
Problem is I can't make C# replace it properly (2nd line in the code):
string myString = "The world's largest program";
myString = myString.Replace("'", "\'");
string sql = "UPDATE myTable SET myColumn = '" + myString + "'";
MySQLCommand cmd = new MySQLCommand(sql, con);
int result = Convert.ToInt32(cmd.ExecuteNonQuery());
Any ideas?
Thanks a lot.
|
|
|
|
|
You can do it in the initial string thus:
string myString = "The world\'s largest program";
or in your replace call thus:
myString = myString.Replace("'", "\\'");
In case 1 you need to escape the single quote with a backslash, and in case 2 you need to escape the backslash.
I must get a clever new signature for 2011.
|
|
|
|
|
It's case 2 we need to go for (the myString is just an example that I created for my question, in reality it's a much more complex string)
But case 2 doesn't seem to work:
string myString = "The world's largest program";
myString = myString.Replace("'", "\\'");
string sql = "UPDATE myTable SET myColumn = '" + myString + "'";
MySQLCommand cmd = new MySQLCommand(sql, con);
int result = Convert.ToInt32(cmd.ExecuteNonQuery());
Gives us the SQL string:
UPDATE myTable SET myColumn = 'The world\\'s largest program'
It has to end up like this to work (I have checked it by typing it in the program SQLyog (which give direct SQL access to a MySQL database):
UPDATE myTable SET myColumn = 'The world\'s largest program'
|
|
|
|
|
|
Once you got the single quote in the database, it does not make sense to replace it by some escape sequence; escape sequences are used in a programming language (such as SQL), not in the data itself. So use \' in the queries when you have to. And it is always better to use SqlParameter, you won't need escape sequences then at all.
Luc Pattyn [Forum Guidelines] [My Articles] Nil Volentibus Arduum
Please use <PRE> tags for code snippets, they preserve indentation, improve readability, and make me actually look at the code.
|
|
|
|
|
Use parameters instead to prevent SQL Injection
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
