|
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.
|
|
|
|
|
mail is configured in sql server and it sends mail properly but sometime when i send mail from sql server then i saw the message like "Mail queued." but i dont get the mail. so then i check the mail status by a sp like EXEC msdb.dbo.sysmail_help_queue_sp @queue_type = 'mail'; then i see the mail state is NOTIFIED. then i just stop and start the mail queue using sp called sysmail_stop_sp and sysmail_start_sp. it is often happening in my sql server mail is getting queued but not sending. i am not being able to catch the problem why often my sql server mail state becomes Notified. please help me that how could get come out from this problem.
thanks
tbhattacharjee
|
|
|
|
|
Hey guys, i have wroted some sp's on some server and they used to work well on that server. then i took a backup of the database and restored it on another server so when i tried to run my sp's i got the exception "Must Declare Scalar Variable".
for instance:
Declare @BankName int;
Set @bankname = 1;
Results in:
Must Declare Scalar Variable @bankname.
any help. thank you.
|
|
|
|
|
The SQL Server instance you restored to is set to case sensitive, therefore the you have not declared @bankname
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
ok Sir, i understand that but i want a solution.
|
|
|
|
|
So think about what I said, your sql server settings need to be changed, go find the setting and change it. Note depending on what database you have installed you may need to reinstall the sql server instance to change that setting.
I suggest you prove that I have identified the problem first!
Never underestimate the power of human stupidity
RAH
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
