|
hi to all,
i have a problem. i have "users" table with Id. now i have a function which acceprt user_id range and give output of all "Name" in one row.
ie if select * from users where iser_id in (1,2,3)
user_id Name
1 A
2 B
15 D
45 X
3 W ...
now i want query which give me output like
"A,B,w"
i.e coma seperated output.
can anybody help me for this?
p.s Not use cusor for this (to combine string)
thanks in advance
|
|
|
|
|
hi,
getting "unsupported data" in the column when selected Price from table field .
This field is defined to have "money" type in table using
Sql server 2005
Please guide.
yog
hui gfgh kgdgrt njjn hjgkn
|
|
|
|
|
Use a cast to get it into a different format.
CAST(Price as REAL)
Blog Have I http:\\www.frankkerrigan.com
|
|
|
|
|
Hi All,
I need to execute a command line executable from a stored procedure, any ideas on how I would do this?
Thanks
Kevin
|
|
|
|
|
It is an unwise thing to do because it is a security risk but you can use: xp_cmdshell[^]
|
|
|
|
|
Thanks, works great. Whats the security risk?
|
|
|
|
|
A sql injection attack may use that to run any code on your server. Read Colin's article on SQL Injection attacks.
Blog Have I http:\\www.frankkerrigan.com
|
|
|
|
|
Kevin Nicol wrote: Whats the security risk?
Allowing external commands to function outside of SQL Server opens some holes in a data based application. If a poorly written front appliction is attacked, or a firewall misconfigured, it is possible for an attacker to gain access to the SQL server and then from there launch commands on the server.
The problem is exacerbated by the fact that many SQL Server installations are runing in the System Account. (If I remember correctly, this was the default before Service Pack 3. After that Full SQL Server installations suggested you create a specific account for SQL Server process to run in, however they still kept a nice handy radio button to swap it back to the system account - many lazy DBAs install SQL Server to use the system account).
The system account has greater authority than the Admin account on the box. For example, did you ever notice in RegEdit that there are apparently empty folders called SECURITY in various locations? If you launch RegEdit in the system account you can see what's in there. But try it with an admin account and they look empty again.
In my SQL Injection Attack presentations I use xp_cmdshell to show how an attacker can go in through a web application and rip a full directory listing off the hard disk. I could continue the presentation and actually have it stream a file that has been routed through SQL Server into the web application then over HTTP. I don't go that far because the directory listing trick is enough to get people to sit up and consider security more seriously.
|
|
|
|
|
I need to archive the live database so that all the data from some specific date time to today(now) is copied to the other database which is infact the exact copy of the live/production database.
I can run a query which can read records, puts into new table(s) and deletes records from live database.
but
a. is there any way of doing it automatically
b. are there any tools available for this
c. What considerations should I keep in mind while doing this.
thanks
Imran
|
|
|
|
|
You are very likely going to have to learn some sql and do it your self.
Blog Have I http:\\www.frankkerrigan.com
|
|
|
|
|
SQL Packager - why don't we just use database backup/restore? (Don't get me wrong I like Red Gate's other product, like ANTS Profiler and SQL Compare, but SQL Packager...??? Why? Guess I were to ask same for ANTS Load where you could have used "Application Centre Test")
Norman Fung
|
|
|
|
|
Is this a question or a statement ?
Blog Have I http:\\www.frankkerrigan.com
|
|
|
|
|
Hi I was told that DTS - SQL Server suffers from Connection/memory leak (i.e. not closing down opened connection when done)? Making too many connection to his valued Sybase server? Transaction deadlocks?
Is there any truth in any of this? Way I see this, deadlocks are bad programming on part of developers, not SQL Server's flaw. As for connection leak, I really don't know how this happens.
Norman Fung
|
|
|
|
|
This was a problem until SP4 was released and particularlly on the 64bit version.
Blog Have I http:\\www.frankkerrigan.com
|
|
|
|
|
How can I specify temp table as Source/Destination table for DTS "Transform Data Task"? Is this allowed at all in the first place?
Norman Fung
|
|
|
|
|
You can use the DTS to create a TEMP db using sql object.
You can also dymanically create temp tables in SQL objects such as using SELECT INTO statement.
Blog Have I http:\\www.frankkerrigan.com
|
|
|
|
|
Problem is, I need to:
STEP 1: do an import in one DTS task, imported data saved in a real staging table (not #TempTable and apparently destination cannot be a temp table - DTS won't let you do this.)
STEP 2: then make transformation DTS "Transform Data Task" to compute a bunch of things, then calculated field is saved to the table created in STEP 1
STEP 3: Then result exported to a flat file, source table once, source table cannot be temp table apparently.
Any idea, I wish to use temp table because creating temp table in production machine may be out of the question as this requires going change request approval - and that's a manual process.
Thanks!
Norman Fung
|
|
|
|
|
Hi I want to export then import a DTS package. I first tried export to Visual Basic file, but then can you import VB files? How?
Thanks.
Norman Fung
|
|
|
|
|
This is remarkably easy if the box is on the same network
Go to the Package (DTS package that is)
Save as...
Then change the server name and it will save it to a different server.
I used this all the time between test and development.
Blog Have I http:\\www.frankkerrigan.com
|
|
|
|
|
Thanks Frank.
Norman Fung
|
|
|
|
|
Hi friends
I want to execute the procedure which is as shown below
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
CREATE procedure xyz(@refno as varchar(25),@Type as varchar(15))
as set nocount on
Create Table #tbl_xyz
(
'some fileds
)
Set @Tempcode=0
Declare xyz_cursor Cursor for
Set @sql1 = 'Select a,b c and d like ''' + @labrefno + ''' order by e ' + @Type
exec sp_executesql @sql1
Open xyz_cursor
Fetch next from xyz_cursor into some values
While @@Fetch_status=0
BEGIN
'''''''''
BEGIN
END
''''''''''''''
''''''''''''''
''''''''''''''
Close xyz_cursor
Deallocate xyz_cursor
select * from #tbl_xyz
'''''''''''''''''''''''''''''''''''''''''''''
In the execution of above procedure , it won't allow me to set any value under the cursor declaration and even if execute under the cusor and i want to perform this procedure by passing the '@refno' and '@Type' dynamically as a parameter .
Is there any solution regarding this , then please guide me.
Thanks
Param
param
|
|
|
|
|
Do you have to use cursors 99% of the time you don't. What are you trying to do ?
Blog Have I http:\\www.frankkerrigan.com
|
|
|
|
|
Hi ,
I am using the System.Data.Common.DBCommand to insert values into a table in a loop with each loop generating a SQL statement and inserting values into the table. The scenario is something like this.
foreach loop
{
DBCommand db = db.GetSqlStringCommand(loop generated string);
db.Connection = db.DbProviderFactory.CreateConnection();
db.Connection.ConnectionString = db.ConnectionStringWithoutCredentials;
db.Connection.Open();
db.ExecuteNonQuery();
db.Connection.Open();
}
the question is that should i open and close the connection everytime? if i dont open the connection in the loop and if i try something like this
DBCommand db = null;
db.Connection = db.DbProviderFactory.CreateConnection();
db.Connection.ConnectionString = db.ConnectionStringWithoutCredentials;
db.connection.open();
foreach loop
{
DBCommand db = db.GetSqlStringCommand(loop generated string);
db.Connection = db.DbProviderFactory.CreateConnection();
db.Connection.ConnectionString = db.ConnectionStringWithoutCredentials;
db.ExecuteNonQuery();
}
Then it throws an error telling that a connection does not exist. Is there anyother way to open the connection just once?
--Amrish
|
|
|
|
|
Amrish Deep wrote: Is there anyother way to open the connection just once?
Have your connection opened before the foreach, and then close it after the foreach loop is done...
I'd like to help but I don't feel like Googling it for you.
|
|
|
|
|
Hi,
It would be better if you open connection before the For loop and close when complete for loop is executed, as suggested by the other poster. But i would like to add that use try catch blocks for opening connections and executing queries. If any exception is thrown it would be handled then.
Also remember to close the connection in the finally block of the try catch statement. This is important since the finally block gets executed regardless of an error. For example, do something like this ....
declare connection here
Try
instantiate connection here
open connection
for loop starts here
statements in loop
for loop ends here
catch ex as Exception
any error handling goes here
finally
close and dispose connection here
end try
Hope i am clear enough.
"A good programmer is someone who looks both ways before crossing a one-way street." -- Doug Linder
coolestCoder
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
