Interesting Requirement! How To Query Port Numbers From 120 Servers!
Dec 7, 2004I need to document the port numbers from nearly 120 sql servers in the network. Is there any query that I can use to get this info??
Thanks,
ADVERTISEMENT
Interesting SQL Query Requirement For &<SELECT&> Menu
Nov 7, 2005Hi AllWondered if you could help me with the below query.I have 1 simple table called STOCKCATS that consists of 2 fields.These fields are called CATID and LEVEL.The contents of this table are as follows:CATID LEVELcat01 <nothing>cat02 <nothing>cat03 cat01cat04 <nothing>cat05 cat01cat06 cat02cat07 cat04etc.. etc...The way this table works is that I have an ASP page that allows the user tocreate a stock category at 2 levels, category level and sub-category level.When I file the entered data into the table, if the user has chosen tocreate a category level stock category then the LEVEL field is left blankand if they chose to create a sub-category level category then I post therelevant category level stock category code in the LEVEL field. Forexample, in the above list cat01 is a category level stock category andcat05 is a sub-category as it is a sub-category of cat01.My query is that I want to populate a simple HTML <SELECT> menu (using ASP),but instead of it being a straightforward 'select catid from stockcats orderby catid', I want to group this list into some kind of order, eg:instead of:cat01 <nothing> << I need to bring back this 2nd column so that I cando a simple IF THEN in asp to indent sub-catscat02 <nothing>cat03 cat01cat04 <nothing>cat05 cat01cat06 cat02cat07 cat04I would likecat01 <nothing> << dittocat03 cat01cat05 cat01cat02 <nothing>cat06 cat02cat04 <nothing>cat07 cat04Do you know if this is possible in pure SQL (I must confess that I'm usingMySQL, but I would have thought the SQL syntax would be the same if it ispossible) or a combo of ASP & SQL?ThanksRobbie
View 3 Replies View RelatedPort Numbers
Jun 2, 2008Hi Gurus,
Iwant to know PORT number of B on A server.
Iam following the following process to get B PORT number.
1) Loging to server A.
2)Opened SQL Server Configuration Manager.
3)Opened SQL Server 2005 Network Configuration.
4)Opened TCPIP properties of Protocol for B.
5)On that opened IP Address TAB
6)Got PORT number from TCP Dynamic Ports
Am i going n right way.
If not please advice me the right way to get port numbers.
Thanks,
ServerTeam
Can't Map Other Sql Servers Without Creating Alias With Port Number On Client Network
Aug 24, 2005I can't map other sql servers without creating alias with proper port number on client network utility. Other users using same version of the client tools, MMC, SQL DMF etc. I need to map 70 sql servers on using my client tools.
Any help is appreciated.
thanks,
Query Analyzer Shows Negative Numbers As Positive Numbers
Jul 20, 2005Why does M$ Query Analyzer display all numbers as positive, no matterwhether they are truly positive or negative ?I am having to cast each column to varchar to find out if there areany negative numbers being hidden from me :(I tried checking Tools/Options/Connections/Use Regional Settings bothon and off, stopping and restarting M$ Query Analyer in betwixt, butno improvement.Am I missing some other option somewhere ?
View 7 Replies View RelatedHow To Write Query For This Requirement
Oct 26, 2007Hi friends
this is the Orders to rise the publisheres (Titles or magzines).
I have two tables
1) orders Tables the columns are like orderno int , titleId int ,ItContaincomb bit, combinationID int.
Values(1,2,null,null)
values(1,3,null,null)
values(2,4,null,null)
Values(2,3,null,null)
values(2,2,null,null)
values(3,4,null,null)
2) CombinationTable containg columns like CombId int , Titleid int.
values(101,2)
values(101,3)
values(102,4)
values(102,5)
valus(103,2)
values(103,4)
my requirement is in orders table the last twocolumns should be filled by checking the combination table
1)
take 101 combination containg 2,3 titles check these all titles are containg or not . if Compulsory all titles containg means in orders table we put
ItContaincomb true, combinationID 101 for all containg titles.
if not containg single title in orders table for combination 101 means
we put that titleid rows should containg column of ItContaincomb is false and combid is 0.
i want below out put for above two tables how to write a query or how to use all function.
the output is (in orders table)
Values(1,2,true,101)
values(1,3,true,101)
values(2,4,false,0)
Values(2,3,false,0)
values(2,2,true,103)
values(3,4,true,103
please help me .
thanks regards
venkat
Interesting Math In A T-SQL Query
Oct 2, 2007
The value in the table of one DB is 17869 sq. ft. Now to insert this value in a new table of other database the reporting basis is 1000 i.e I need to do 17869/1000 = 17.8 so I have to take the value as 18. Another thing to be kept in mind is the value in the new table should have leading Zeroes. If the value is 18 it should be displayed as 0000018 ( data type in new table is Varchar(7) and in old table char (9) ). What can be the best way to implement this??
Help Writing A SELECT Query, Please. (Should Be Interesting For You!!!)
May 28, 2008All- I would greatly appreciate some assistance in creating a SELECT statement for table headcount in the following problem:
First, find the schema here: http://home.pacbell.net/eulimi/schema_headcount.GIF
The problem:
Given a person_id (a record in the person table), list all headcount records WHERE:1) headcount.person_id = person.person_id AND2) the headcount.act_session_id belongs to a activity (from the activity_session table) that the person is allowed to attend by virture of the person's membership in a subexpedition.
Note that:
- A person can be a member of one or more circles (via the circle_person table)- Each circle belongs to exactly one subexpedition.- A persons who is a member of a subexpedition (via one or more circles) is entitled to sign up for one or more activities (activity_sessions) associated with the subexpedition.
So, put yet another way, the SELECT command should show, for a given person_id, the list of records in headcount that includes the activities (act_session_id) that the person is entitled to join by virtue of the persons membership in the subexpedition via his circle. (Whew!)
I hope this is enough information. I think the problem would be fun if I knew what I was doing. (SQL newbie here.) I suspect I need several INNER JOINs and a compound WHERE clause.
Thanks in advance!!!
Interesting ! Update Query With Alias ?
Jul 29, 2004Hi,
I have a below Oracle query :
UPDATE test1 a SETa.sno = 3
I need the equivalent SQL Server query for the above along with with alias name 'a' set for the table test1. Please advise.
Thanks,
Sam
Formatting Numbers In A Mixed Column (numbers In Some Cells Strings In Other Cells) In Excel As Numbers
Feb 1, 2007I have a report with a column which contains either a string such as "N/A" or a number such as 12. A user exports the report to Excel. In Excel the numbers are formatted as text.
I already tried to set the value as CDbl which returns error for the cells containing a string.
The requirement is to export the column to Excel with the numbers formatted as numbers and the strings such as "N/A' in the same column as string.
Any suggestions?
Adding Numbers While Doing Query
Mar 28, 1999I used this with an access DB and would like to re-write it to work with
sql.
sql = "SELECT funds.itemdisc, Sum(IIf(funds.status='Credit'"
sql = sql & ",CDbl(funds.fundamount),0,)) AS income, Sum(IIf funds.status='Debt'"
sql = sql & ",-1*CDbl(funds.fundamount),0)) AS outgo"
sql = sql & " FROM funds "
sql = sql & "GROUP BY funds.itemdisc"
What I am doing here is collecting the columns and using status as
credit or debt, if its debt I am subtracting from outgo if it is credit
I am adding to credit. This also will only select records so that
there are not any double listings. Here is an example.
DB
desc status fundamount
bill debt 10.00
bill debt 15.00
in credit 10.00
bills debt 5.00
in credit 5.00
paper debt 5.00
When I do the query I should have this
desc fundamount
bill -25.00
bills - 5.00
paper - 5.00
in 15.00
Something like that...
What do you think?
Thanks in advance.
Query Help - Sequential Numbers
Apr 27, 1999I have a query which returns information about transactions similar to this:
Select account, trans_code, quantity
from ledger_table
This returns something like:
acct trans_code quantity
----- ---------- ------------
2 2 1000
2 3 500
2 3 300
3 2 100
3 2 500
etc.
What I need to do is add a lot number for each acct/trans code type. This is merely a sequential number for the transaction. This changes the output as follows:
acct trans_code quantity lot
----- ---------- ------------ ---
2 2 1000 1
2 3 500 1
2 3 300 2
3 2 100 1
3 2 500 2
3 3 1000 3
3 5 200 1
etc.
The lot number is reset for each grouping.
Does anyone have a way to do this outside of a cursor or temp table?
TIA, any help greatly appreciated.
How To Get Row Numbers From SQL Express 2005 Query?
Nov 11, 2007Hi, I'm using SQL Express 2005 and VIsual Studio 2005, and this sounds like it should be easy. I'm trying to return the row numbers of my queries, but if I use the Row_Number() command I get the following error: "The OVER SQL construct or statement is not supported." So, is Row_Number() not supported in SQL Express 2005? If not, how can I return row numbers with my queries? Or, more specifically, how can I return a limited result set from a query (i.e. Return only row number 10-20)? My current command is as follows: SELECT (SELECT Row_Number() OVER (ORDER BY UserName) As RowNumber), *FROM UsersORDER BY UserName Thanks for any advice you can offer!
View 8 Replies View RelatedAutomatically Adding Numbers To Query
Jun 4, 2008Hello everyone!
I have a question for you. I have a product database with a lookup that pulls by product number. All my product numbers are made up the same way. IE. N59840, N00951, N00951. ect.
I have a stored procedure that looks up by that product number with a "LIKE" statement that looks like this.
WHERE ([Product#] LIKE '%' + @PRODUCTNUM + '%')
Which has this problem if someone types in "852" it returns
N00852
N05852
N98852
Is there anyway that I can have SQL put in zeros to fill up the 5 number spots so "852" brings up "00852" or "5852" brings up "05852"
I hope this makes sense.
Thank you for your help!!!
I Need To Update A Table With Random Numbers Or Sequential Numbers
Mar 11, 2008
I have a table with a column ID of ContentID. The ID in that column is all NULLs. I need a way to change those nulls to a number. It does not matter what type of number it is as long as they are different. Can someone point me somewhere with a piece of T-SQL that I could use to do that. There are over 24000 rows so cursor change will not be very efficient.
Thanks for any help
SQL Query - Find Block Of Sequential Numbers
Jul 20, 2005I have a database that is pre-populated with sequential part numbers.As people reserve the parts I update a flag to show the # is no longeravailable. Now they want the ability to take out a block of "x"number of sequential part numbers - say for example 5.If my database had the following numbers available:101104105110111112113114It should return 110 thru 114 and then I would write an update queryto change the flags to 1 (checked out).I have only been able to return the first "x" number of records - havenot been able to make sure they are stepped sequentially - with thefollowing:SELECT ID_ITEM From PARTNO_CHKOUT_SPECIAL M Where (Select Count(*)FROM PARTNO_CHKOUT_SPECIAL NWHERE N.ID_ITEM <= M.ID_ITEM) >= 0 AND TYPE_REC=1 ANDFLAG_CHECKED_OUT=0 {maxrows 5}The above would return 101, 104, 105, 110, 111I tried using an (N.ID_ITEM+1)-M.ID_ITEM=0 to try stepping and geterrors, probably incorrect syntax. Can I do this in an SQL statement?
View 6 Replies View RelatedSearch A Column Of Comma Delimited Numbers (was Help With Sql Query)
Nov 30, 2005Hi, I need to search a column in my database (varchar:50) that contains a comma delimited string of numbers (i.e. 1, 2, 3, 4, 5, 10). Currently, I am doing the following SQL query:
SELECT * FROM people WHERE clubs_belongs_to LIKE '%1%'
Where 1 is the number I'm searching for. The problem with the query above is that it returns records that contain 1, 10, 11, 12, 13, etc. in the clubs_belongs_to field. I want the query to only return those people who belong to club number 1, not 10, 11, 12, etc. Please help. Thanks in advance. I have tried using IN instead of LIKE, but that didn't seem to get the results I wanted either.
Simple Query To Create Column Of Sequential Numbers
May 2, 2006Hi,
Can anyone tell me offhand the simplest/most elegant way of updating an integer column to a sequential column of numbers with a query?
e.g given
intval | Description| Cost
0 | Descvalue0| 4.32
2 | Descvalue2| 4.33
3 | Descvalue3| 4.34
8 | Descvalue8| 4.35
change it to:
intval | Description| Cost
0 | Descvalue0| 4.32
1 | Descvalue2| 4.33
2 | Descvalue3| 4.34
3 | Descvalue8| 4.35
I think it might need a stored proc..
Many Thanks
greg
T-SQL (SS2K8) :: Select Query With Records And Sequential Numbers
Apr 5, 2014I have a problem. In my database I have the following numbers available:
101
104
105
110
111
112
113
114
What I need is to get a select query with records and sequentials numbers after it like:
101 0
104 1 (the number 105)
105 0
110 4 (the numbers 111,112,113,114)
111 3 (the numbers 112,113,114)
112 2 (the numbers 113,114)
113 1 (the numbers 114)
114 0
How can I do It?
SQL 2005 Management Studio: Line Numbers In Query Window.
Dec 19, 2005Can anyone tell me if you can display line numbers in the query windowof SQL 2005 Management Studio and if so how do I go about doing it?Thanks a bunch. TFD
View 2 Replies View RelatedGenerate List Of All Numbers (numbers Not In Use)
Feb 21, 2007I have an 'ID' column. I'm up to about ID number 40000, but not all are in use, so ID 4354 might not be in any row. I want a list of all numbers which aren't in use. I want to write something like this:
select [numbers from 0 to 40000] where <number> not in (select distinct id from mytable)
but don't know how. Any clues?
Dataflow To Excel - Convert Numbers Stored As Text To Numbers Excel Cell Error
Mar 27, 2007I'm trying to write data to excel from an ssis component to a excel destination.
Even thought I'm writing numerics, every cell gets this error with a green tag:
Convert numbers stored as text to numbers
Excel Cells were all pre-formated to accounting 2 decimal, and if i manually type the exact data Im sending it formats just fine.
I'm hearing this a common problem -
On another project I was able to find a workaround for the web based version of excel, by writing this to the top of the file:
<style>.text { mso-number-format:@; } </style>
is there anything I can pre-set in excel (cells are already formated) or write to my file so that numerics are seen as numerics and not text.
Maybe some setting in my write drivers - using sql servers excel destination.
So close.. Thanks for any help or information.
Query Across Servers
Jan 25, 2005I'm new to SQL and I'm trying to write a stored procdeure in my current database that queries a different database on a different server. What is the best way to do this?
Thanks and sorry if it's too remedial a question.
SQL Query Over 2 Sql Servers
Jan 24, 2008Hi,
I am a newbie to SQL and learning as I go.
I am currently trying to write a SELECT that returns the difference between 2 identical tables in 2 different SQL databases on 2 different SQL servers.
I believe I have setup the linked servers properly.
Here is the select I wrote;
select *
from [dbnameA].dbo.Item
where not exists
(select * from [dbnameB].dbo.ItemSD
Where [dbnameA].dbo.Item.IMA_ItemID = [dbnameB].dbo.ItemSD.IMA_ItemID)
The error I receive is;
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'dbnameB.dbo.ItemSD'.
Any suggestions are greatly appreciated!!
Thanks,
Michiel
Query Between Different Servers
Feb 4, 2008i have two servers where i have logged in, hr and transport
i want to write a query in query analyser of the hr server to retrieve information in the transport server. how will i do it
i tried with
select * from servername.databasename.permission.tablename
i get a message
Server: Msg 7202, Level 11, State 2, Line 1
Could not find server 'transport' in sysservers. Execute sp_addlinkedserver to add the server to sysservers.
How Can I Query This On Linked Servers...
Nov 13, 2006For example I have 15 records in which accountID is a PK. AccountID 1,2,3,4,5 is linked on server1. I want to find out records that are not linked. So its record 6 - 15. Can someone show the stored procs for this scenario? Or the query statement to do this... Im new to linked servers. Thanks alot.
View 1 Replies View RelatedUpdate Query Across Servers
Jul 10, 2007Hi,
I'm trying to write an update query across two servers but getting getting two errors.
The first server is called: blatweb2
The second server is called: blbr-teamserv
This is the query I'm trying to use:1 update blatweb2.dbo.knousefoods.products a
2 Set LuckyLeafPieFillingSort =
3 (Select LuckyLeafPieFillingSort from blbr-teamserv.dbo.knouse.products b where a.productname = b.productname) I'm getting the following errors. Do you know what could be wrong? ThanksMsg 102, Level 15, State 1, Line 1Incorrect syntax near 'a'.Msg 102, Level 15, State 1, Line 3Incorrect syntax near '-'.
Query From Multiple Servers
Jun 3, 2008hi
Is there a way to have a select query on multiple servers ? (2 databases , each one located on a sql server)
How To Query Data From To Servers
Aug 25, 2000hi,
I have a question. how do query data between the two servers.
thanks
kumar
Query Remote Servers On 6.5?
Jul 23, 2002Hi, all -
I know you can execute stored procedures on remote servers in SQL Server 6.5, but can you run regular queries as well using the 4 part name qualification?
Example: select * from server1.database1.dbo.table1
If not, is there another way I can manipulate data on remoter servers?
Two Servers Same Query Different Timings
Apr 25, 2008
Hi,
Please could I have some advice I am running a query on the live node of a cluster it takes well over an hour to run even at a quite ish time when CPU is bellow 50% memory disk queues all normal. But when I run it the other node it take less than a minute.
I know it would probably take longer on the live due to having users on but not that much longer, there is no blocking why is there such a huge differences.
Many thanks
2 Databases On 2 Servers, 1 Query?
Jan 25, 2008Hello,
We have adedicated MS-SQL Server for our CRM database and we have another MS-SQL database server for in house applications, written in c#.Net 2.0. To avoid duplicating information already in our CRM database, it would be nice if we could build a query that combined columns from both data sources.Example
Code Snippet
CRM database on Server1
Table Accounts
Columns
AccountID UniqueIdentifier Primary Key
Name NvarChar(50)
Application Database on Server2
Table Transaction
Columns
TransactionID UniqueIdentifier Primary Key
AccountID UniqueIdentifier
Amount Money
Would it be possible to create a T-SQL statement that will select
Code Snippet
Select
Server1.CRM.Accounts.Name,
Server2.Application.Transaction.TransactionID,
Server2.Application.Transaction.AccountID,
Server2.Application.Transaction.Amount
From Server1.CRM.Accounts INNER JOIN Server2.Application.Transaction
ON (Server1.CRM.Accounts.AccountID = Server2.CRM.Transaction.AccountID)
I appreciate there will need to be 2 connections and therefore some credential information would need to be passed too.
Is anything like this possible? I do not wish to copy the Name field from Server1.CRM.Accounts to Server2..Application.Transaction as this is duplication and any change to the value of Server1.CRM.Accounts.Name would not be reflected in the results.
Many thanks for any information provided.
Transact SQL :: Run Query On 2 Servers?
Sep 24, 2015i need to run query that will run on two servers.
one is local (Server1), and one on 192.144.22.22 (Server2)
i try this:
SELECT *
FROM [Db1].[dbo].table1 A
INNER JOIN [Server2].[Db1].[dbo].table2 B
ON A.Id = B.Id
but this work fine because the two database is on the local machine.
how to do it ?