|
Thanks for your reply. I can not do that because the table is the result of a program and is created many times. As many as the program is used. I can not change the program so I need to change the table every time is ceated. Thats why I need to write code in visual studio visual basic to correct the table and eliminate the additional records. I assume there is not a SQL command to do that.
|
|
|
|
|
|
Don't cross post. It is considered rude.
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
|
|
|
|
|
An error has occured while establishing a connection
to the server. when connecting to sql server 2005,
this failure may be caused by the fact that under
the default settings sql server does not allow remote connections
|
|
|
|
|
- Open the SQL-Configuration Manager via 'Start->Programs->Microsoft SQL Server 2005->Configuration Tools->SQL Server Configuration Manager'
- Browse to 'SQL Server 2005 Network Configuration->Protocols for MSSQLSERVER' and enable the required protocols (e.g. the TCP/IP-Protocol)
This should solve your problem. If not, you can check the properties of the Server (Right-Click on the registered server in the SQL Management Studio). In the 'Connections' -Tab, check the option
'Allow remote connections to this server'.
|
|
|
|
|
Hi
I have 3 tables e.g A,B,C.
I am joining table A and table B on the bases of id
Select A.id,B.Name from A,B where A.id=B.id .
Now i want get records from table C basis on calculated A.id.
These all I want in One Query.
Is tht Possible?
Thanks
Care Career
|
|
|
|
|
U can use the following code
Select * from C where id in (Select A.id from A,B where A.id=B.id)
|
|
|
|
|
I want all the fields from 3 tables.
This query solves fethes data from C table only i want data From a or b also
SO can u help?
thnx
care career
|
|
|
|
|
Hi
try this query
Select A.id,B.Name,C.Calc From A,B,C Where A.id = B.id And A.id = C.Id
Or
Select A.Id, Name, Calc From A
Inner Join B On B.Id = A.Id
Inner Join C On C.Id = A.Id
Hope this will work but there should be relation between these tables
|
|
|
|
|
I want to use SQL 2005.after installing it on my system I wanted to create a table. How to do that?
How to fire a query?
I have used SQL 2000.
|
|
|
|
|
Master Mind`z wrote: I want to use SQL 2005.after installing it on my system I wanted to create a table. How to do that?
You need to create a database first. Then you can create the table in the database.
Master Mind`z wrote: How to fire a query?
Use the SQL Server Management Studio.
|
|
|
|
|
If you wish to become a great dba or even a good one then write your own scripts. To do this effectively you have to use "SQL SERVER BOOKS ONLINE" which are installed with SQLserver 2005. Learn or look at one new command a day and get feel for the commands.
First look up command : CREATE DATABASE
THEN look up command : CREATE TABLE
Have fun you have a good journey ahead of you.
|
|
|
|
|
Hi All
Iam getting error on executing this procedure
Alter procedure LogProc
(
@UserId varchar(500),
@Password varchar(500)
)
as
declare @str varchar(500)
set @str='select AstId from UserTable where UserId='+@UserId+'and
Password='+@Password
exec (@str);
exec LogProc 'uname','xyz';
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'Password'.
i want to join this group
|
|
|
|
|
Try something like this:
set @str =
' select AstId
from UserTable
where UserId = ''' + @UserId
+ ''' and Password = ''' + @Password + ''''
|
|
|
|
|
Hi,
Thanks sir.I got It.
Veeresh
i want to join this group
|
|
|
|
|
Hi Sir,
Once again iam getting this error.Iam very new to Sql .Can please tel me
how to use single quotes in query.Also giv me some help to this query
Alter procedure LogProc
(
@UserId varchar(500),
@Password varchar(500)
)
as
declare @str1 varchar(500)
declare @str2 varchar(500)
set @str1 = ' select AstId from UserTable where UserId = ''' + @UserId + ''' and Password = ''' + @Password + ''''
exec (@str1);
set @str2=' select RoleId from AstProf where AstId = ' + @str1
exec(@str2);
exec LogProc 'veeresh','veeresh'
Iam getting this error
(1 row(s) affected)
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'select'.
i want to join this group
|
|
|
|
|
Try the query within a bracket ()
while assigning it with set keyword.
i am not sure try it.
|
|
|
|
|
veereshIndia wrote: set @str='select AstId from UserTable where UserId='+@UserId+'and
Password='+@Password
exec (@str);
You can eliminate 'Set' and 'exec (@str)' statements by having the select query alone which will get the records of the appropriate user.
select AstID from userTable where userid = @userid and password = @password
|
|
|
|
|
I am trying to run a bulk update on a table by picking up the IDs that I've populated onto a table variable based on some criteria.
The update just works fine if the number of records in the table variable are < = 1000.
When this number increases to 5000, the update does'nt work. When I try to debug my code, I get the message box stating "Error while executing 'A Batch Cmd' at line 0; Sql Error: 0 "The statement has been terminated."
On catching this execption I get the message "Timeout expired.The Timeout period elasped prior to completion of the operation of the operation or the server is not responding.\r\n The statement has been terminated."
When I try to run the stored proc from the query analyzer, it keeps processing for a long time and that I've to manually stop the execution.
On trying to run the queries that I have in the stored proc individually did'nt yield me positive results either.
I've ensured that my stored proc meets the specifications listed here[^]
Can anyone throw some light on what can be tried to fix this?
Environment: VS 2005 Pro, Sql Server 2000
|
|
|
|
|
Can you post some of the code up here so we can take a look?
|
|
|
|
|
andyharman wrote: Can you post some of the code up here
Yes,sure. The following lists the logic and flow of my proc.
.... Proc declarations go here ....
Begin Transaction
Declare @tblCategory Table (CategoryID BigInt)
Insert into @tblCategory(CategoryID)
Select A.CategoryID from Shop A Where ProductName = @pProdName
If exists(select categoryId from @tblCategory)
begin
Declare @tblLocalities Table(CategoryID BigInt, LocalityID int)
Insert into @tblLocalities (categoryid,localityid)
select B.CategoryID, B.localityId from ShopGroup B, Shop A
where A.CategoryID = B.CategoryID and A.ProductName = @pProdName
If @@rowcount > 0
begin
Update Shop Set Ranking = 2 where categoryID in
(select t.CategoryID from @tblLocalities t)
end
end
If @@error > 0
Begin
rollback transaction
end
else
begin
commmit transaction
end
|
|
|
|
|
I'm trying to understand what your code is trying to achieve. As far as I can work out, the following would do the same:
UPDATE Shop SET Ranking = 2
WHERE CategoryID in (
SELECT B.CategoryID
FROM Shop A
INNER JOIN ShopGroup B
ON A.CategoryID = B.CategoryID
WHERE A.ProductName = @pProdName
)
Is this any faster? How many rows does it update? What is the purpose of linking to "ShopGroup"?
The next step would probably to look at the indexes.
Regards
Andy
|
|
|
|
|
Yes, inserting or updating a lot of records will take a lot of time, particularly if running other queries concurrently against the same records (which may temporarily block your updates due to locks). ADO.NET stops your application from being blocked forever by imposing a configurable command timeout, which you set in the SqlCommand's CommandTimeout property. It defaults to 30 seconds, so if your command takes more than 30 seconds to execute you get a SqlException.
Management Studio assumes you're more interested in the results than in a speedy response and doesn't time out.
DoEvents : Generating unexpected recursion since 1991
|
|
|
|
|
Mike Dimmick wrote: Yes, inserting or updating a lot of records will take a lot of time, particularly if running other queries concurrently against the same records
Thanks on your views Mike. I will now need to figure out a way to fine tune my proc.
Mike Dimmick wrote: Management Studio assumes you're more interested in the results than in a speedy response and doesn't time out
Do we have any settings / system procs that will take care of the time-out issue in Sql Server 2000?
|
|
|
|
|
Hi All
Background
----------
I have a database table namely tblSection that stores sections that have parent child relationships e.g.
Section 1
|
|--Section 2
|
|--Section 3
| |
| |--Section 4
|
|--Section 5
|
Section 6
The column structure for this table is:
SectionGUID uniqueidentifier PK
ParentGUID uniqueidentifier ALLOW NULL
Title nvarchar(100)
So my root level records are anything where ParentGUID IS NULL and the remaining records can have variable levels of nested children.
My Problem
----------
So what would people suggest with regards handling this structure within SQL for example a typical request is to return all sections below a specific node within the hierarchy? Currently I deal with the scenarios I have by extracting all folders into a single System.Data.DataTable object and then traverse the tree within my code which is OK for certain types of process but now I am looking at more number crunching type processes that approach is slow and cumbersome
Thanks in advance
Andy
|
|
|
|