|
Hi
Is there any way within T-SQL to access another database
for example if i was writing a stored procedure to access some records in Northwind, but also wanted to update a table
in Pubs
Is this possible ??
i wanted to do something like
Select @rec = recVal from [dbo.northwind].Sometable
update [dbo.pubs].Sometable
set myvalue = @rec
thanks
sI
|
|
|
|
|
si_69 wrote: Select @rec = recVal from [dbo.northwind].Sometable
update [dbo.pubs].Sometable
try this:
<br />
Select @rec = recVal from northwind..Sometable<br />
<br />
update pubs..Sometable
you can't forget something you never knew...
|
|
|
|
|
Quick Guide.
From different database db.owner.table
eg SELECT * from northwind.dbo.Customers
From another box
SELECT * from server.db.owner.table
eg SQLserver.NorthWind.dbo.Customers
|
|
|
|
|
Hi..
I am trying to make a full text search through all indexes(tables) in a full text catalog.So i wrote the code:
CREATE PROCEDURE SearchProject
(
@SearchString varchar(500)
)
AS
SELECT FT.Rank,PL.ProjectNO,ProjectImage,FieldNO,PL.ProjectName,PL.ProjectDescription,PL.CultureID
FROM FREETEXTTABLE(Project_Locale,*,@SearchString) AS FT JOIN Project_Locale AS PL JOIN FREETEXTTABLE(ProjectField_Locale,*,@SearchString) AS FT2
JOIN FREETEXTTABLE(Feature_Locale,*,@SearchString) AS FT3 JOIN
ProjectFeature AS PFEA JOIN Project AS P
ON FT.[KEY]=PL.ProjectCultureID
OR FT2.[KEY]=P.FieldNO OR FT3.[KEY]=PFEA.FeatureNO
RETURN
but i got the error :
incorrect syntax near the word 'RETURN'
How could i solve this problem? and Could this code make the search as i wanted?
-- modified at 10:15 Tuesday 11th April, 2006
|
|
|
|
|
Hi,
Can anyone tell me the diff. betn. nvarchar & varchar , and int & number datatypes in SQL 2000 Server database.
Also I want to change the length of the data field of int datatype from 8 to 10, but I'm not able to change it.
Thanx & Regards.
Success is not a Destination,
... But a Journey !!
|
|
|
|
|
The difference in varchar and nvarchar datatypes is simple. Nvarchar stores UNICODE data. If you have requirements to store UNICODE or multilingual data, nvarchar is your choice. Varchar stores ASCII data and should be your data type of choice for normal use.
UNICODE requires 2 bytes for each character you store. ASCII only requires 1 byte for each character. This is important because of the row size limitations of Sql Server is the same as the page size limit, which is 8060 bytes. This means a single row of a single varchar column can be varchar(8000), but a single row of a single nvarchar column can only be nvarchar (4000).
check this link
http://www.aspfaq.com/show.asp?id=2214[^]
|
|
|
|
|
Length of int type is fixed.
|
|
|
|
|
Vikrant Badhai wrote: Also I want to change the length of the data field of int datatype from 8 to 10, but I'm not able to change it.
An int is a 32bit integer. Range -2billion to +2billion (roughly). If you want anything larger then use a bigint
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)
My: Website | Blog
|
|
|
|
|
Mixing nvarchar and varchar on the same table / view can produce some strange results with matching strings in TSQL as the length is different in the database. If your only using your db locally use varchar if you have international client / users and want to use multiple languages use nvarchar.
|
|
|
|
|
Hey guys.
Got a little question I could use some help.
Suppose I have a table with 2 fields : Date and Ammount.
I need to write a query that displays each date and the sum of ammounts to that date.
I got a bit messed up with it...
Isaac
|
|
|
|
|
You could try joining the table onto itself
SELECT d.[Date], SUM(a.Amount)
FROM MyTable AS d
INNER JOIN MyTable AS a ON a.[Date] <= d.[Date]
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)
My: Website | Blog
|
|
|
|
|
Well you forgot the GROUP BY d.[Date] but it's genious none the less!
Thanks a lot!
Isaac
|
|
|
|
|
Well, I guess that happens from time to time when I type directly into Code Project rather than conduct a test first. Which is a good thing to learn: Always test code before releasing it.
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)
My: Website | Blog
|
|
|
|
|
Hi,
im not the best with SQL statements and have quite a tricky one to work out. If anyone could help me out id be extremely grateful.
I have a database of orders placed by customers, which need to be returned and counted in groups so for example the customer has ordered the following:
Car Tyre @ £50
Car Tyre @ £50
Car Door @ £25
Car Ariel @ £5
Car Tyre @ £50
So i need to return this information grouped as such:
3 * Car Tyre @ £150
1 * Car Door @ £25
1 * Car Ariel @ £5
it would also be useful to have a statement which gives me the same detail but with the price of a single item eg:
3 * Car Tyre @ £50
1 * Car Door @ £25
1 * Car Ariel @ £5
Thanks in advance!
munklefish
|
|
|
|
|
Try something like this:
SELECT COUNT(*) AS Quantity,
CarPart,
CarPartPrice,
SUM(CarPartPrice) AS CarPartTotalPrice
FROM Orders O
JOIN Customers C
ON C.CustomerID = O.CustomerID
WHERE C.CustomerID = 1
GROUP BY CarPart,
CarPartPrice
WITH ROLLUP
|
|
|
|
|
|
I learnt everything the hard way, using Notepad and IIS. I am used to SqlCommand, SqlDataReader, SqlConnection but I realized that these are no longer used in Visual Studio 2005.
Even when I type my code as below:
SqlCommand objCmd;
SqlDataReader objRdr;
void Page_Load(){
if(!IsPostBack){
BindData();
}
}
void BindData(){
objCmd = new SqlCommand("SELECT * FROM Warehouses", objConn);
objConn.Open();
objRdr = objCmd.ExecuteReader();
ddlWarehouses.DataSource = objRdr;
ddlWarehouses.DataValueField = "WarehouseID";
ddlWarehouses.DataTextField = "WarehouseName";
ddlWarehouses.DataBind();
objRdr.Close();
objRdr = objCmd.ExecuteReader();
lbWarehouses.DataSource = objRdr;
lbWarehouses.DataValueField = "WarehouseID";
lbWarehouses.DataTextField = "WarehouseName";
lbWarehouses.DataBind();
objRdr.Close();
objConn.Close();
}
The code above no longer work. Is there anyone that can teach me or give me a URL to where it teaches data binding of ADO.NET on Visual Studio 2005, along with how to Insert Update, Delete and Edit data to a database?
There are a lot of people who ended up doing things wrongly, even if they had the best of intentions at the beginning. Then again, there is no guarantee the words they said were conveyed as they originally were intended to be. That's because those on the receiving end only hear what they want to hear.
|
|
|
|
|
DataReader, command etc. are all still used in 2005. What is the error that you get?
|
|
|
|
|
I am using Visual Studio .NET 2003. I am getting a failed build of my solution that only contains one project called Unliquidated Obligations (It is a Report Project). No build errors are shown on the task list. I can right-click on any report, select build, and get the same error. I know there is nothing wrong with the reports themselves as they work fine in preview mode. For some reason it still says 1 failed at the bottom of the output as shown below. If I try and deploy the report, it will show the dialog box "There were build errors. Continue?".
Any help is appreciated.
------ Build started: Project: UnliquidatedObligations, Configuration: Debug ------
Build complete -- 0 errors, 0 warnings
---------------------- Done ----------------------
Build: 0 succeeded, 1 failed, 0 skipped
|
|
|
|
|
|
I downloaded and installed SQL 2005 Express so as i could use it in my Visual Studio... When i go to Add Item, and SQL Database, it is supposed to make a blank SQL DB which i can add my info into... When i click add i get the following message: An error has occurred while establishing a connection to the server. When connecting to the SQL Server 2005, This failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (Provider: SQL Network Interfaces, Error: 26 - Error Locating server/instance specified)
While this was true, i opened the settings for my connection and enabled remote connections and then restarted it. Yet the same error and problem still exist... Anyone with any more usful ideas on how i can get his up and running?
"Love, Life and Option Explicit"
|
|
|
|
|
I had similar issues with getting connection strings to work with VS2002 /VS2003 and VS2005 express with SQL server express 2005.
Solution: You need the machine and the instance name when speficifing a connection to the server MYPC\SQLSERVEREXPRESS
I also never got (local)\SQLSERVEREXPRESS to work.
I also had to setup a specific SQL user as well as windows authenication with SQL Express didn't work too well.
Hope this helps
|
|
|
|
|
Frank Kerrigan wrote: Solution: You need the machine and the instance name when speficifing a connection to the server MYPC\SQLSERVEREXPRESS
I also had to setup a specific SQL user as well as windows authenication with SQL Express didn't work too well.
Not really sure what you mean by setting up specific SQL users.. is that in SQL or do you mean another windows profile. im runnig XP HOME, which is why i had to use express as the full version is not compatable... might this be hindering it?
As for the server, i know the server name, and that doesnt seem to be helping. Any idea where i could find a nice trouble shooting guide perhaps? The SQL makes my head hurt. lol
"Love, Life and Option Explicit"
|
|
|
|
|
SQL accounts can be based on Windows accounts (or domain accounts) or SQL account that have no existence outside SQL.
Open SQL stidio manager and have a look at users.
|
|
|
|
|
I just tried to recreate your error and the closest I could get was when the SQLEXPRESS instance was not started.
Visual Studio and SQL are dead. Long live Visual Studio and SQL!
-- modified at 13:42 Monday 10th April, 2006
|
|
|
|