|
evilnoodle wrote: Our system runs multiple DB's on a single SQL2005 Server
I hope this is not the consequence of allowing your users to name the database when installing (question asked a few weeks ago). If Mika's suggestion does not help then you may need to go down the path of dynamic sql.
If the databases are identical (surely) then move the decision to the connection from the client not the procedure. Then you are executing the proc and the proc does no care what database/server it is on.
If the connection information needs to be within the proc I think you are screwed unless synonyms can help.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi,
id f_name l_name
1 dan silva
2 al gore
5 billy smith
6 jack wilder
9 ben douglas
11 chris ball
13 helen hunter
23 ruth south
24 sondra zelda
how can i get the row number, index for bill smith?
i dont want to get =5
i want to get =3
I created a extra column with 1,2,3,4...etc, but i don´t want to use that!!!
nelsonpaixao@yahoo.com.br
|
|
|
|
|
To get the record use and use the additional column
Select * from TableName where l_name = 'billy smith'
To get the rowindex in the table - what for! I can think of a number of reasons, most of which are in the UI.
Look into ROWNUMBER in BOL, this may help
[edit] forget rownumber look at RANK or ranking functions[edit]
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
That would depend on ordering as well.
I imagine a function could be written to accomplish it, but doing so after you retrieve the data may be best.
|
|
|
|
|
Hi,
Why not...
select row_number() over (order by id asc) as Rowindex, *
from [Table]
Ryan
|
|
|
|
|
<br />
ALTER TABLE tableName ADD value int NULL<br />
<br />
DECLARE @i AS INT<br />
DECLARE @j AS INT<br />
SET @i=0<br />
SET @j=0<br />
<br />
WHILE @j<=(SELECT MAX(id) FROM tableName )<br />
BEGIN<br />
SET @j=@j+1 <br />
IF(SELECT COUNT(id) FROM tableName WHERE id=@j)=0<br />
BEGIN<br />
SET @i=@i+1 <br />
UPDATE tableName SET value=@i WHERE id=@j<br />
END <br />
END<br />
<br />
SELECT * FROM tableName WHERE value =3<br />
ALTER TABLE tableName DROP COLUMN value<br />
<code><br />
<br />
Hope it helps... <br />
<br />
<div class="ForumSig"><hr><small> <font color="Brown" size="2" face="Curier New">I Love T-SQL</font></small><br />
<small> <font color="Brown" size="1" face="Curier New">"Don't torture yourself,let the life to do it for you."</font></small><br />
</hr></div>
|
|
|
|
|
SELECT ROW_NUMBER()
OVER (ORDER BY EmployeeName) AS Row,
EmployeeId, EmployeeName
FROM Employees
|
|
|
|
|
Hi
I am in the situation to write code to pull the data from a table which has 1 Million rows per day
what are the optimal ways to search the text ?
database : SQL Server 2000
I have index on the table
The table is portioned
nrk
|
|
|
|
|
Hi,
Not necessarily optimal but this may interest you Full-Text Indexes[^]
Another possibility is to create custom keyword based index table using triggers etc
Hope this helps,
MIka
|
|
|
|
|
Hi, thanks for looking
Im currently hosting my site with godaddy, but ive been having problems when uploading and modifying my DB, since it doesnt allow remote connections from SQL Server Management Studio it only has a webapp (SQL Server web Admin)
My app is a small site, i cant have a dedicated server with them since thats too expensive, i just have a medium plan
I need a hosting company that allows me to connect remotely
Im looking for something not expensive
Im using:
ASP.NET 2.0, AJAX and SQL Server 2005
Please give me some suggestions
Thanks in advance
Alexei Rodriguez
|
|
|
|
|
Check Fort Nocs Networks
I know for sure they allow remote connections, but I have no idea about pricing.
Please... SAVE my time by rating the posts that you read!
There are 10 kinds of people in the world: those who understand binary and those who don't.
|
|
|
|
|
Thanks for the reply
They dont show much info or pricings
It must be expensive, it looks like they offer something more than just sql server hosting
Alexei Rodriguez
|
|
|
|
|
what is the mean of WHILE (1=1) shown in the sample codes as below:
--------------------------
IF (A > B)
A := A + 1;
ELSE
B := B + 1;
WHILE (1 = 1) {
A := A + 1;
}
FOR (declare X any, X := 1; X <= 2 ; X := X + 1){
S := S + X;
}
FOR (declare X any, X := 1; X <= 2 ; ){
S := S + X;
X := X + 1;
}
---------------------------
thanks
|
|
|
|
|
It makes an infinite loop.
|
|
|
|
|
To define an infinite loop
|
|
|
|
|
First, this is the SQL and Database Discussion Forum, not a programming forum.
Second, what do YOU think the WHILE is doing?
|
|
|
|
|
quick infinite loop
|
|
|
|
|
Hi,
I am using sql 2005.I have a value -0.026880000000000 but I want the result as -0.0268 just 3 decimals.If I use select convert(decimal(18,3),-0.026880000000000) I am getting -0.027 with rounded.
can anybody help me out in this.
Thx
|
|
|
|
|
Try this:
a) Multiply the value by 1,000
b) Truncate the value
c) Divide the number by 1,000
David
|
|
|
|
|
HI,
if i do like that select convert(decimal(18,3),(-0.0268000000000000000)*1000/1000) I am getting -0.027 which is again rounding.can you help me how to truncate the value with out rounding.
|
|
|
|
|
I realize someone already replied, but here is my 2 cents ...
select convert(decimal(18,3),(convert(integer,(-0.0268000000000000000)*1000)))/1000
|
|
|
|
|
select convert(int,convert(decimal(18,5),-0.026880000000000)*100000.0)/100000.0
|
|
|
|
|
DECLARE @val AS VARCHAR(255)
SET @val='-0.026880000000000'
SELECT SUBSTRING(@val,1,CHARINDEX('.',@val)+4)
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
|
|
|
|
|
|
I have written one VIEW. it returns 10 rows.
Now I need to show only 3rd row data.
Please suggest me.
|
|
|
|