|
|
Hello!
I am writing a stored procedure that should insert a row in a table that it's name is sent to it as a parameter.
I do it this way:
CREATE PROCEDURE myProcedure
@tablename ntext,
AS
INSERT INTO @tablename(...)
and I get an error "Must declare the table variable "@tablename"".
What should I do?
Thanks in advance!
Have a nice day!
|
|
|
|
|
Solved the problem. I'm using EXEC instead of an explicit INSERT.
Have a nice day!
|
|
|
|
|
Which one is feasible to store data?
Sutheesh Ramachandran
Cybernet Software Systems,
T Nagar, Chennai 17.
|
|
|
|
|
That's something of a broad question there. XML doesn't store anything - it's a mark up language. You store your data as XML, and you could do this in a file or a database or something.
SQL is a query language for querying relational databases. Typically databases are geared to storing things in a tabular fashion.
If the data you want to store is hierarchial in nature then XML is a good choice, if tabular then a database table is a good choice.
Sql Server 2005 has quite good support for XML and you can use XQuery within stored procs to query stuff stored as XML.
Regards,
Rob Philpott.
|
|
|
|
|
hi there,
i have a column contain some values..i just want to copy the data of column to same column after orginal data.
means if the column contains 1,2,3 and i copy the same column in it then it will be 1,2,3,1,2,3
Hope u understand
what can do ???
Regards and thankx in advnace
Koolprasad2003 
Be a good listener...Because Opprtunity knoughts softly...N-Joy
|
|
|
|
|
What datatype is the column?
Regards,
Rob Philpott.
|
|
|
|
|
koolprasad2003 wrote: means if the column contains 1,2,3 and i copy the same column in it then it will be 1,2,3,1,2,3
I assume from this the column is a varchar or char, so
update table1<br />
set col1 = col1 + ',' + col1
will do the job. Watch out for nulls though.
Hope this helps
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Hi Guys!
I am using the following Statements:
Select min(ItemPrice) as MinUniquePrice, max(ItemPrice) as MaxUniquePrice
From ( select ItemPrice, Count(*) as ItemPriceCount
from #Mytable
group by ItemPrice
Having Count(*) = 1) as MyTableWithUniqueItemPrices
which returns me the unique value in the table. The only problem is, how do I also get the itemID without the SQL Server listing all the values in the Table. My Original Question is pasted below for reference.
Thanks once again to all the guys who had helped me earlier.
Ali.
-----------------------------------------------------------------------------------------------
ORIGINAL QUESTION:
=================
I tried to look around but I could not find an answer for this question. I need to run an SQl Query which retuns me the highest Unique Value and the lowest Unique value from the same column. For example. Lets say the column name is itemPrice, and has the following values: 10, 10, 11, 12, 12, 13, 14, 14 respectively. How can I get the 11 and 13 as the result of my query. I have tried DISTINCT, MIN, MAX and HAVING but to no avail. I am using SQL Server 2000.
|
|
|
|
|
did you check my answer in your original posted message?
Hope it helps.
I Love SQL
|
|
|
|
|
Hey Blue Boy,
Thanks for the reply. I did and it returns the min and max value properly. But I am not getting the ID for the item.
Thanks for the help.
|
|
|
|
|
Hey Blue Boy,
Sorry, wrote it the wrong way, it returns the id if I add it but it did not return the unique values I was looking for. The query given by Syed returns the values I want but it does not return the ID for the product.
Thanks.
|
|
|
|
|
tell me the columns of the table.
Does your table have columns like this:
ID itemPrice <br />
-----------------------<br />
1 | 10<br />
2 | 10<br />
3 | 11<br />
4 | 12<br />
5 | 12<br />
6 | 13<br />
7 | 14<br />
8 | 14
I Love SQL
|
|
|
|
|
Hey Blue Boy! 
Thanks for the help mate. Yes the columns and the values that you are displaying are correct. But from the resultset, what I need is ID: 3 and value 11 as Min(itemPrice), and ID: 6 and value 13 as Max(itemPrice). What I need is the lowest unique or distinct (whatever you would like to call it) and the maximum value which does not have similar values (e.g 11 and 13 have only one entry where as others have more than one).
The other query that I mentioned in the last post returns me the values 11 and 13 but it is not returning the ID's (3 and 6) respectively.
I hope I make some sense now. Sorry if I failed to explain it properly earlier.
Thanks and kind regards,
Ali
|
|
|
|
|
I guess you are looking for this
select id,itemprice from Table1 <br />
where <br />
(select count(i1.itemprice) from Table1 as i1 where i1.itemprice=Table1.itemprice)=1<br />
<br />
and id in( (select top 1 id from Table1 where <br />
(select count(i1.itemprice) from Table1 as i1 where i1.itemprice=Table1.itemprice)=1 order by id asc ) <br />
, (select top 1 id from Table1 <br />
where <br />
(select count(i1.itemprice) from Table1 as i1 where i1.itemprice=Table1.itemprice)=1 order by id desc))
I Love SQL
|
|
|
|
|
Since we are sure that our Min/Max prices will be unique so we can write a subquery for retrieving the IDs, here's an addition to my original query.
Select MinUniquePrice, ( Select Top(1) ID from #MyTable T where T.ItemPrice=MinUniquePrice ) as MinPriceID,
MaxUniquePrice, ( Select Top(1) ID from #MyTable T where T.ItemPrice=MaxUniquePrice ) as MaxPriceID
From
(
Select min(ItemPrice) as MinUniquePrice, max(ItemPrice) as MaxUniquePrice
From
( select ItemPrice, Count(*) as ItemPriceCount
from #Mytable
group by ItemPrice
Having Count(*) = 1
) as MyTableWithUniqueItemPrices
) As MyTableWithMinMaxItemPrices
But Blue_boy's approach was better. I really liked it.
Regards,
Mehroz
modified on Wednesday, April 2, 2008 3:55 PM
|
|
|
|
|
thanks dude 
I Love SQL
|
|
|
|
|
-- Solution 1
-- Say There are products in a table name products
-- with a column named proPrice.
-- Now insert your data 10, 10, 11, 12, 12, 13, 14, 14
-- Now run the scripts
-- Keep practicing. Take care mushfiqcs@yahoo.com
select proPrice, count(proPrice) from products
group by proPrice
Having count(proPrice)<2
S M Mushfiqur Rahman
|
|
|
|
|
-- Solution 1
-- Say There are products in a table name products
-- with a column named proPrice.
-- Now insert your data 10, 10, 11, 12, 12, 13, 14, 14
-- Now run the scripts
-- Keep practicing. Take care mushfiqcs@yahoo.com
select proPrice, count(proPrice) from products
group by proPrice
Having count(proPrice)<2
S M Mushfiqur Rahman
|
|
|
|
|
Hello.
What I'd like to ask is if there is a way to configure SQL Server 2005 to use only a certain amount of RAM. I have a machine with 4GB RAM, and there are a lot of apps running on it. It is when SQL Server starts executing a query over a relatively large database (e.g. 20 - 30 GB)that things go bad for all other apps - SQL Server uses almost all of the available RAM, leaving only a very limited amount for all other applications to use.
Any suggestions?
|
|
|
|
|
Those are fairly large databases and depending on your queries you could be hitting the processor heavily - as you are discovering.
The only solution I can think of is to remove all those extra applications.
Good practise dictates that your SQL Server is a machine that is primarily a dedicated server i.e you have no other applications on it (other than an email client, backup application etc)
If you are using databases of the size you mention I suggest you get another machine on which to run the other applications.
Some more tips:
Keep your databases and transaction logs on separate disks.
Make sure that your applications are not on either of these disks.
I know this doesn't answer you question directly...
You always pass failure on the way to success.
|
|
|
|
|
You cannot explicitly set the amount of RAM that the system should use (as far as my little knowledge serves me). You can however, set the priority of the SQL Server to low as compared to other applications (Press ALT+CTRL+DEL > Click task Manager > Applications (TAB) > Select Image Name sqlmangr.exe > Right click on it and select option: set priority). That would mean that you would have more processing preference for other applications. But remember, with the size of the databases you are talking about, you would still have issues with the processor getting hit hard. If you cannot redice the DB size, then increase the RAM, much better get dual processors and set one processor to solely work on SQL Server. This is what a Hardware guy can set for you.
Regards.
|
|
|
|
|
Open up Management Studio, connect to your server. In Object Explorer, right click on the Server Root and select "Properties". Under the "Memory" page, enter a value in the "Maximum Server Memory" textbox.
The change will take effect when SQL Server is under lesser load and can shed some memory or when you restart the service.
SG
Cause is effect concealed. Effect is cause revealed.
|
|
|
|
|
I am using the Query Builder in Visual Studio to fill my Dataset but one of the field/column names is unknown until runtime; therefore I wish to specify this on the fly.
My current query is as follows:
SELECT Cards.Id, Cards.Card, Cards.FirstName, Cards.LastName, Cards.AccessLevel, Cards.StartDate, Cards.EndDate, Cards.fTitle, ?,<br />
Photo.Id AS Expr1, Photo.Photo,<br />
AccLevel.AccessLevel AS Expr2, AccLevel.Name AS AccessName<br />
<br />
FROM (Cards<br />
LEFT JOIN Photo ON<br />
Cards.Card = Photo.Id)<br />
LEFT JOIN AccLevel ON<br />
Cards.AccessLevel = AccLevel.AccessLevel
The question mark represents a variable field name. Below is the code which fills the dataset by calling the query above - although for now, I have passed it a fixed string which represents one of the fields in the ‘Cards’ table and should replace the question mark in the query:
cards_Photo_AccessLevelTableAdapter.Fill(MyDataSet.Cards_Photo_AccessLevel, "Cards.fDepartment");
Although my code builds ok, an exception is caused when the Dataset is filled as above.
Any help would be much appreciated!!
Regards,
Christian
|
|
|
|
|
hi
i want my sql server database save the data like
12:12:12 PM..Database must contain only time value nt default date value.
currently it saved data like 1/1/1900 12:12:12 PM and i want it save as 12:12:12 PM
plz give me solution to do this.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
