|
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
|
|
|
|
|
Dear ALl,
I want to append an execel sheet to certain table in my ACCESS 2007 databas (VBA), through this:
Dim exceltable1 As TableDef
Dim strsql As String
Set classdb = CurrentDb()
Set exceltable1 = classdb.CreateTableDef("Temp")
exceltable1.Connect = "Excel 8.0;DATABASE=Path\template.xlsx"
exceltable1.SourceTableName = "template_table" '************ name definition of my ecxel records
classdb.TableDefs.Append exceltable1
Set rst = classdb.OpenRecordset("select * from Temp")
If Not (rst.BOF And rst.EOF) Then
strsql = "insert into tb_contract_details select * from Temp"
classdb.Execute strsql
The problem is: If am running this code twice for same records of the excel sheet say (10 records) then the records will be the same 10 records in the database table not 20 records , ie: it is overwriting records.
but if i change some values for some records in the excel sheet, it only inserts those changed values , while the rest are not inserted.
Any help, i need to insert the records of the excel sheet even if they already exist in the table ??? i need to append them to the existing table records??
Keep in mind that no primary key is set in the table, no restriction on records.
0 will always beats the 1.
|
|
|
|
|
You have two data sources.
You cannot create SQL that references both data sources at the same time.
You must extract the data from one data source and then, in a different step, add it to the other data source.
|
|
|
|
|
Thanks Man , but i didnt got it at all....
jschell wrote: You have two data sources.
What are those two???
jschell wrote: You must extract the data from one data source and then, in a different step, add it to the other data source.
Can you be more clear, what should i do ?? detailed plz ??
Am sorry i couldnt get it  
0 will always beats the 1.
|
|
|
|
|
scorp_scorp wrote: What are those two???
You are getting the data from one place - that is data source one.
You are putting the data some place - that is data source two.
scorp_scorp wrote: Can you be more clear, what should i do ?? detailed plz ??
1. Get data - put it in a collection of some sort.
2. Write the data - by using the collection.
|
|
|
|
|
Thanks for your follow man, but seems that we r not on the same channel.. 
jschell wrote: You are putting the data some place - that is data source two.
Putting data in some place , could be considered as Data Source ??? i dont know 
jschell wrote: 1. Get data - put it in a collection of some sort.
2. Write the data - by using the collection.
U i tried to use 2 recordsets also didnt work, u mean that they should be done in two events ??
Any article?? any explanation ?? any examples ?? if you can provide .... most thanksfull
0 will always beats the 1.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
