|
I am using SQL Server 2005 Express, is there something similar that I can use to check if a table exists in the database? Thanks.
|
|
|
|
|
This will return all of the tables from the db the query is running under...
select * from sysobjects
where xtype = 'u'
|
|
|
|
|
Given a query like the following
select open_date, close_date
from view
where ( open_date between D1 and D2 or close_date between D1 and D2 )
and knowing that close_date may be NULL sometimes and that open_date is always <= to close_date, what kind of date manipulation could I do, so that I can add another field to the select list so that when both open_date and close_date are between D1 and D2, the value for the field would be 1, other wise it would be zero.
Thanks.
Chris Meech
I am Canadian. [heard in a local bar]
I agree with you that my argument is useless. [Red Stateler]
Hey, I am part of a special bread, we are called smart people [Captain See Sharp]
The zen of the soapbox is hard to attain...[Jörgen Sigvardsson]
I wish I could remember what it was like to only have a short term memory.[David Kentley]
|
|
|
|
|
I finally figured out to add two fields and the query looks like this
select open_date, close_date,
decode(sign(open_date-D1),-1,0,1) open_date_in_range,
decode(sign(nvl(close_date-D1,-1)),-1,0,1) close_date_not_null
...
Chris Meech
I am Canadian. [heard in a local bar]
I agree with you that my argument is useless. [Red Stateler]
Hey, I am part of a special bread, we are called smart people [Captain See Sharp]
The zen of the soapbox is hard to attain...[Jörgen Sigvardsson]
I wish I could remember what it was like to only have a short term memory.[David Kentley]
|
|
|
|
|
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
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
