I read with great interest a post on getting hte top 75 per cent by month, but still get through this problem. I have a table containing addresses, city names and state names, Each row has a unique address. I want to get the first ten addresses for each city in each state. I've tried something like the following which didn't work. Suggestions on how to solve the (simple) problem would be appreciated. Select top (10) city,state,address from address_list alan
In the database, most of our cities are stored in all upper case. For reporting purposes, I need to have them returned as upper/lower. I used the below function, which works great for one word cities. However I can’t figure out how to get it to capitalize the 1st letter of each word, for addresses containing multiple names such as Rancho Santa Margarita.
I installed a script that is suppose to accept paypal, however on trying totest a payment, I get this error msg:ErrorDatabase access errorand I get this error msg emailed to me:Unknown column 'State' in 'field list'Query: 'INSERT INTO `TransactionsMembership` (ID, Sum, State ) VALUESwhat could be swrong, importantly how do I fix it?
Hi!How I can know the "Country version" of SQL Server using VB.NET(perhaps with the SQLDMO)?I mean: if the SQL Server is in "English version" or in "Italianversion" or ...Thank you so much!!Bye
The 2.0 version of ASPSTATE is slightly different than the 1.1 version in that one table has one additional column and another table uses a different data type and size for the key. The 2.0 version also has a couple additional stored procedures.
We'd like to manage just one session state database if possible so we're trying to figure out if Microsoft supports using the new schema for 1.1 session state access (it seems to work, but our testing has been very light).
Is there any official support line on this? If not, can anyone comment on whether or not you'd expect it to work and why?
Hello everybody. When I start to test SQL 2008 Febuary CTP I detect that sume of new options work incorrectly. This is description of this bug from Microsoft Connect:"I don`t know, may be somebody detect this issue too. Sorry If I create duplicate. I start SQL Server Managment Studio & click new query. After that I try to create simple query like this Uses Master Select "column name" from "table". I was surprised because when start write column name I saw the pop-up objects list. But this was very strange list, because I didn`t find necessary columns. This list show only "common" objects(such as database nemes, functions & ect). I think that such list(after first select) should show only children objects(columns, keys, triggers, indexes & ect.), after that is you write "from" such list should show only parents objects (Such as table name, database name & ect). PS. Sorry for my english." After that(today) I get the answer from MS SQL Team "Hi,
Your observation is correct. Selection list (column names) can suggests correct candidates once user specifies FROM clause.
Regrads, Eric Kang SQL Server Program Manager" & bug was closed as "By Design". Then I reopen this bug with such comments "
But It`s lokks very strange. If I want to select column name from the list I should: 1. write SELECT 2. Press Space 2. Write FROM "Table name" 3. Returt to free space after SELECT 4. Start Write "Column name". 5. Choose the necessary Column name from the list. There are too much for such simple operation. Posted by stanisluv on 3/19/2008 at 11:45 AM
Too much steps." Sorry I make a typo. I mean: "looks". If anybody else argee with me please validate & vote the feedback. This is the link for it: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=333047
I need to retrieve list of all databases and the size occupied by them in sql server. As of now am pulling data for each database using sys.dqatabase_files which is hard for me as there are around 40-50 db on SQL Server.
I have [TableAccount.AccountType] field which can store these values:
Value C S E
How do I list all available values for a field? Using Distinct can only list all entries in the db, but does not list all values available for the field.
the field "Expertise" contains available options "Accounting", "Language", "Engineering" and so on. How do I list all the available options for the field "Expertise" in ms sql 2014?
i have a table like below CREATE TABLE #Test (FromDate DATE,ToDate DATE) insert into #Test VALUES ('2015-08-08','2015-08-11') insert into #Test VALUES ('2015-08-13','2015-08-16') insert into #Test VALUES ('2015-08-19','2015-08-21') SELECT * from #Test drop TABLE #Test
i need to display the dates as single column between from and todate.my expected result is like below
CREATE TABLE #Result (ResDate DATE) insert into #Result VALUES ('2015-08-08') insert into #Result VALUES ('2015-08-09') insert into #Result VALUES ('2015-08-10') insert into #Result VALUES ('2015-08-11')
I am trying to determine the existence of at least one row in my Detail table using EXISTS in my SELECT list from my Main table.SELECT M.ID,EXISTS(SELECT 1 FROM Detail D WHERE D.ID = M.ID) as HasDataFROM Main MCan this be done this way?I was hoping that using EXISTS would find a row and move on thus increasing performance.
Hello, I'm new to the SQL world and want to know if this is the site for a newbie or would you recommend another site more my speed. Thanks for the input.
I need to build a reference table that will contain the time zone, offset value and DST changes for all time zones. Is there a way I can pull this from the windows server hosting sql server?
I have created some statistics for my site based on the ip of the visitors. For that reason I have created two tables 1. One table with a column which hold the ip number of the visitor Table: AccessLog Column: IP
2. One table with the ip range and the countries Table: Countries Columns: ip_to, ip_from, country_name
I would like to create a query which will bring the country name of the visitor IP which IP will be like "ip_to <= ip <= ip_from"
What I'm trying to select is the closest value from a list given by a parameter or select the matched value.
declare @compare as int set @compare = 8 declare @table table ( Number int ) insert into @table values(1), (2), (3), (4), (5), (10)
If the parameter value match one of the values from the table list, select that matched one.If the value does not exist in the table list, select the closest lower value from the table list, in this case, it would be value 5.
I want generating Valid date ranges from any list of dates.
The List of Dates could be generated from the below TSQL -
SELECT '2015-06-02' [Date] UNION ALL SELECT '2015-06-13' UNION ALL SELECT '2015-06-14' UNION ALL SELECT '2015-06-15' UNION ALL SELECT '2015-06-16' UNION ALL SELECT '2015-06-22' UNION ALL SELECT '2015-06-23' UNION ALL SELECT '2015-06-24'
And the expected output should look like -
SELECT '2015-06-02' FromDate, '2015-06-02' ToDate UNION ALL SELECT '2015-06-13' FromDate, '2015-06-16' ToDate UNION ALL SELECT '2015-06-22' FromDate, '2015-06-24' ToDate
-Shop : list of all shops with an ID_SHOP -Contract : list of all contract with a FK to the shop FK_SHOP. A shop can have multiple contracts
I have a cte where I calculate for each FK_SHOP the maximum contract start date. I now want to join my shop table with the contract table but only for the contracts >= their maximum contract start date.
WITH cteContracts AS ( SELECT FK_SHOP ,ID_CONTRACT ,MAX(cont.DTT_START_DATE) DT_STARTDATE
Is anyone has store procedure that find city and zipcode by radius (10 miles, 30 miles, 50 miles.......) and also table that has all zipcodes. I finded one, but it's look like very old one, not updated since 2005
I have the following code, which returns a list of nearby cities based on a city name as input. Most cities have multiple zipcodes per city name, thus it can list multiple rows with the same city name, but with different zipcodes like below:
Zip | Cityname 111 belmont 112 belmont 113 belmont 114 san francisco 115 san francisco
---------------- etc----------------
I do not really care about each group of zipcodes. I only need one pair of zipcode/city name like the following:
ZIP | City name 111 belmont 114 San Francisco
How do I change my select to only return a distinct city name. I do not care which if the city/zipcodes it returns from the similar city.
The select statement is below:
CREATE PROCEDURE ZipSearchByCity @city varchar(40), @State varchar(5), @distance int
AS
SELECT distinct o.City AS City, o.zip_code, o.State AS State, (3956 * (2 * ASIN(SQRT( POWER(SIN((z.RADlatitude-o.RADlatitude)/2),2) + COS(z.RADlatitude) * COS(o.RADlatitude) * POWER(SIN((z.RADlongitude-o.RADlongitude)/2),2) )))) dist
FROM zipcodes z, zipcodes o, zipcodes a
WHERE z.city = @city AND z.State = @State AND z.zip_code=a.zip_code AND (3956 * (2 * ASIN(SQRT( POWER(SIN((z.RADlatitude-o.RADlatitude)/2),2) + COS(z.RADlatitude) * COS(o.RADlatitude) * POWER(SIN((z.RADlongitude-o.RADlongitude)/2),2) )))) < @distance GO
I am using SQL Server 2005 and trying to create a linked server on Oracle 10. I used the commands below: EXEC sp_addlinkedserver @server = 'test1', @srvproduct = 'Oracle', @provider = 'MSDAORA', @datasrc = 'testsource' exec sp_addlinkedsrvlogin @rmtsrvname = 'test1', @useself = 'false', @rmtuser='sp', @rmtpassword='sp'
When I execute select * from test1...COUNTRY I get the error. "The OLE DB provider "MSDAORA" for linked server "...." does not contain the table "COUNTRY". The table either does not exist or the current user does not have permissions on that table." The 'sp' user I am connecting is the owner of the table. What could be the problem ? Thanks a lot.
