Hi,
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
Thanks
Kate
Hey, i need some help with my query structure.. I have my zip code database, and then a listings table, with a zip code in one of the columns. I want to return all that data from both frields where the zip code radius results are. here's my stored procedure:*removed*Any ideas?
View 1 Replies View RelatedDoes anyone know how to call a SQL store procedure that return a value to the page?
I've a simple data entry aspx page with several textboxes and a save button. When user fill out the form and click save/submit, it calls a store procedure to insert a row into a SQL table and automatically generate an ID that need to return the the page to display for the user.
Are there a similar article somewhere?
Thank you all!
The problem is as subject line shown.
Please Help.
Thanks for all.
J.H.
I would like to know all the columns used in a store procedures.I have used dynamic sql to create these procedures .
View 4 Replies View RelatedI have a code that is working, and want to create a store procedure in order to put the information of the running code into a new table.
View 7 Replies View Relatedhi friends
i want to know how to calculate year ranging from 1900 to 2000 using store procedure in sql server
Just getting started with SQL Server CLR integration. Any suggestions on how to organize code? Should I keep my CLR Stored Procedures in a separate project than the code that will call them? Should each DB have its own project containing all the CLR code?
Maybe there is no right way...I'm just looking for a good way.
Thanks!
I know I can call store procedure from store procedure but i want to take the value that the store procedure returned and to use it:
I want to create a table and to insert the result of the store procedure to it.
This is the code: Pay attention to the underlined sentence!
ALTER PROCEDURE [dbo].[test]
AS
BEGIN
SET NOCOUNT ON;
DROP TABLE tbl1
CREATE TABLE tbl1 (first_name int ,last_name nvarchar(10) )
INSERT INTO tbl1 (first_name,last_name)
VALUES (exec total_cash '8/12/2006 12:00:00 AM' '8/12/2006 12:00:00 AM' 'gilad' ,'cohen')
END
PLEASE HELP!!!! and God will repay you in kind!
Thanks!
Hello Frdz, I have doubt regarding the datatypes fields used in SQL SERVER 2005.I want to use the field to store the mobile number of the user .Which datatype should i used ?? The value for bigint Int64 is 18The value of int Int32 is 9/10Now,if in int i write : 1234567890 (accepted)This gives error : 9874565656 (not accepted..........why is it so ??? )Why is it so ??I want to know the perfect size of all the datatypes used in SQLSERVER 2005.There are also smallint,tinyint.....What's the main difference with all of them ??Can anyone provide me the nice links which can explain me what m i asking in this post...Please help me....I want to know all the datatypes used differences...
View 1 Replies View Related
I have latitude and longitude in my database, can anyone give me an sql query how I can make radius search based on that?
Please help!
Please look at the sample data below; I need help to change the value of data from (Before) - (After)
Before
Station Cycle_Index
D_CC 1
C_CC 1
C_CV 1
REST 1
D_CC 1
C_CC 1
C_CV 1
REST 1
D_CC 1
When Station = 'D_CC' then Cycle_Index suppose to start a new cycle. So my problem is trying to find the next 'D_CC' and store the incremental cycle # in a New_Cycle column.
After
Station Cycle_Index New_Cycle
D_CC 1 1
C_CC 1 1
C_CV 1 1
REST 1 1
D_CC 1 2
C_CC 1 2
C_CV 1 2
REST 1 2
D_CC 1 3
Thanks mucho for your help!
Updated:
Sorry to confuse you by changing the text color!
Case when Station = 'D_CC' then Cycle_Index = Cycle_Index + 1; based on the prior cycle # and continuos to find next 'D_CC' until the end; regardles whatever in between the prior D_CC to next D_CC.
Bottom line is searching the next value from Station = D_CC then cycle # in Cycle_Index column need to be incremented by 1 and stored the new cycle # in New_Cycle column as the actual cycle.
Hi - I know this isn't really a specific SQL Server question but Ican't think of a better forum.I want to implement a more sophisticated UK postcode search on a site- "LIKE 'NW%'" etc, is not bringing back good enough results.A feature that would allow me to ask: "give me all outward postcodesin a 30 mile radius of NW10" would be ideal.Has anyone had to do this before? - any advice on how muchappreciated. Some of the postcode products available don't reallyseem to offer this feature.NH
View 2 Replies View RelatedHello,
I'm using ASP.Net to update a table which include a lot of fields may be around 30 fields, I used stored procedure to update these fields. Unfortunatily I had to use a FormView to handle some TextBoxes and RadioButtonLists which are about 30 web controls.
I 've built and tested my stored procedure, and it worked successfully thru the SQL Builder.The problem I faced that I have to define the variable in the stored procedure and define it again the code behind againALTER PROCEDURE dbo.UpdateItems
(
@eName nvarchar, @ePRN nvarchar, @cID nvarchar, @eCC nvarchar,@sDate nvarchar,@eLOC nvarchar, @eTEL nvarchar, @ePhone nvarchar,
@eMobile nvarchar, @q1 bit, @inMDDmn nvarchar, @inMDDyr nvarchar, @inMDDRetIns nvarchar,
@outMDDmn nvarchar, @outMDDyr nvarchar, @outMDDRetIns nvarchar, @insNo nvarchar,@q2 bit, @qper2 nvarchar, @qplc2 nvarchar, @q3 bit, @qper3 nvarchar, @qplc3 nvarchar,
@q4 bit, @qper4 nvarchar, @pic1 nvarchar, @pic2 nvarchar, @pic3 nvarchar, @esigdt nvarchar, @CCHName nvarchar, @CCHTitle nvarchar, @CCHsigdt nvarchar, @username nvarchar,
@levent nvarchar, @eventdate nvarchar, @eventtime nvarchar
)
AS
UPDATE iTrnsSET eName = @eName, cID = @cID, eCC = @eCC, sDate = @sDate, eLOC = @eLOC, eTel = @eTEL, ePhone = @ePhone, eMobile = @eMobile,
q1 = @q1, inMDDmn = @inMDDmn, inMDDyr = @inMDDyr, inMDDRetIns = @inMDDRetIns, outMDDmn = @outMDDmn,
outMDDyr = @outMDDyr, outMDDRetIns = @outMDDRetIns, insNo = @insNo, q2 = @q2, qper2 = @qper2, qplc2 = @qplc2, q3 = @q3, qper3 = @qper3,
qplc3 = @qplc3, q4 = @q4, qper4 = @qper4, pic1 = @pic1, pic2 = @pic2, pic3 = @pic3, esigdt = @esigdt, CCHName = @CCHName,
CCHTitle = @CCHTitle, CCHsigdt = @CCHsigdt, username = @username, levent = @levent, eventdate = @eventdate, eventtime = @eventtime
WHERE (ePRN = @ePRN)
and the code behind which i have to write will be something like thiscmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@eName", ((TextBox)FormView1.FindControl("TextBox1")).Text);cmd.Parameters.AddWithValue("@ePRN", ((TextBox)FormView1.FindControl("TextBox2")).Text);
cmd.Parameters.AddWithValue("@cID", ((TextBox)FormView1.FindControl("TextBox3")).Text);cmd.Parameters.AddWithValue("@eCC", ((TextBox)FormView1.FindControl("TextBox4")).Text);
((TextBox)FormView1.FindControl("TextBox7")).Text = ((TextBox)FormView1.FindControl("TextBox7")).Text + ((TextBox)FormView1.FindControl("TextBox6")).Text + ((TextBox)FormView1.FindControl("TextBox5")).Text;cmd.Parameters.AddWithValue("@sDate", ((TextBox)FormView1.FindControl("TextBox7")).Text);
cmd.Parameters.AddWithValue("@eLOC", ((TextBox)FormView1.FindControl("TextBox8")).Text);cmd.Parameters.AddWithValue("@eTel", ((TextBox)FormView1.FindControl("TextBox9")).Text);
cmd.Parameters.AddWithValue("@ePhone", ((TextBox)FormView1.FindControl("TextBox10")).Text);
cmd.Parameters.AddWithValue("@eMobile", ((TextBox)FormView1.FindControl("TextBox11")).Text);
So is there any way to do it better than this way ??
Thank you
hi
I am trying to store the output of sp-executesql into a variable to implement it as a user defined function later
The function is
ALTER function [dbo].[UnitsAvailable] (@id int)
returns int
as
begin
declare @sql nvarchar(100)
declare @params nvarchar(500)
declare @count nvarchar(10)
set @sql = N'Select count(*) from units where projectid=' + convert(varchar,@id) + 'and sold=0 and displayunit=1'
set @params = N'@countOUT nvarchar(10) OUTPUT';
exec sp_executesql @sql, @params, @countOUT=@count OUTPUT;
return @count
end
The result is that I am able to parameterize the sql end execute with the right result. The only problem is that the value is not stored in the variable @count. I could get to the same result using managed code in sql 2005 but still I am curious to find out where the problem is ....
Can you please help?
Thanks Alex
Our company purchased a app. Is there any way to find out which store procedures were fired once a button was clicked?
View 3 Replies View Related
hi,
when i tried to run the following code , the code keeps on running without creating the required table...............
pls can anyone help me to figure out the problem........
waiting for your replies...
int row_head = 3;
int col_head = 3;
TextBox[] columns1=new TextBox[5];
TextBox[] rows = new TextBox[2];
for (int i = 0; i < 3; i++)
columns1[ i ] = new TextBox();
for (int i = 0; i < 2; i++)
rows[ i ] = new TextBox();
columns1[0].Text = "col1";
columns1[1].Text = "col2";
columns1[2].Text = "col3";
rows[0].Text = "rowh1";
rows[1].Text = "rowh2";
String[] description=new string[10];
for (int k = 0; k < 10; k++)
description[k] = k.ToString();
int index_value = 0;
int des_index = 0;
string datatype = "";
string command = "";
SqlConnection sqlCN = new SqlConnection(connectionString);
for (int i = 0; i < col_head + 1; i++)
{
if (i == 0)
datatype += "(col " + i + " varchar(20),";
else if (i + 1 == col_head+1)
datatype += "col" + i + " varchar(20))";
else
datatype += "col" + i + " varchar(20),";
}
MessageBox.Show(datatype);
MessageBox.Show("create table " + textBox1.Text + " " + datatype);
string selectCommandText = "create table " + textBox1.Text +datatype;
MessageBox.Show("executed");
try
{
SqlCommand sqlCmd = new SqlCommand(selectCommandText, sqlCN);
sqlCN.Open();
MessageBox.Show("open ");
// the following command is not executed.....
sqlCmd.ExecuteNonQuery();
MessageBox.Show("open and execu");
for (int i = 0; i < row_head + 1; i++)
{
command="";
if (i == 0)
{
command="' ',";
for(int j=0;j<col_head;j++)
{
if(j+1==col_head)
command+="'"+columns1[j].Text+"'";
else
command+="'"+columns1[j].Text+"',";
}
}
else
{
command = "'"+rows[index_value++].Text+"',";
for (int j = des_index; j < des_index + col_head; j++)
{
if (j +1== des_index+col_head)
command += "'" + description[j] + "'";
else
command += "'" + description[j] + "',";
}
des_index = des_index + col_head;
}
MessageBox.Show("INSERT INTO " + textBox1.Text + " values(" + command + ")");
sqlCmd.CommandText = "INSERT INTO " + textBox1.Text + " values("+command+")";
sqlCmd.ExecuteNonQuery();
}
MessageBox.Show("fini");
selectCommandText = "SELECT * FROM " + textBox1.Text;
sqlCmd = new SqlCommand(selectCommandText, sqlCN);
sqlCmd.ExecuteNonQuery();
SqlDataAdapter sqlDA = new SqlDataAdapter(selectCommandText, sqlCN);
DataSet DS = new DataSet();
sqlDA.Fill(DS);
MessageBox.Show("fini 2");
DataGridView1.DataSource = DS.Tables[0].DefaultView;
}
catch (Exception eio)
{ }}
Sweety
Hi, I’ve developed the following stored procedure to query a table of services following a user search. The user can specify a radius from a certain location(UK postcode) in which to search, and its with this Im having a few problems.
Here’s my method:
STEP1 - I first check the table of services and insert all services matching the specified criteria (regardless of location) into a temporary table (#tMatches).
STEP 2 - I get the postcode entered by the user and retrieve its co-ordinates.
STEP 3 – I open a cursor and loop through the records in #tMatches. For each record, I first retrieve their co-ordinates and then use a bit of Pythagoras to work out whether its location falls within the radius specified by the user.
STEP 4 – If the record falls outside the range specified by the user, I deleted from #tMatches.
STEP 5 – I bind the remaining (matching) records from the #tMatches to a dataset.
Now, I think that seems pretty simple, and the easiest way to achieve what I’m trying to do. How then when querying just 600 records am I finding it times out? I think I’ve pinned it down to the STEP 3 (cursor), but not being the SQL expert I’d like to be, I’ve no further ideas where I’m going wrong? Any ideas?
The following is an excerpt from the script - don't worry about the fact several parameters may not be declared.
Many thanks
======================================
--STEP 1
CREATE TABLE #tMatches
(
record_uid uniqueidentifier,
service_name varchar(500),
service_address varchar(500),
service_description varchar(500),
GRE integer,
GRN integer
)
BEGIN
INSERT INTO #tMatches
(record_uid,service_name,service_address,service_description,GRE,GRN)
SELECT service_loc_uid,title_return,service_address,service_overview,GRE,GRN FROM v_RecordsLive_short WHERE (title_return LIKE '%' + @lookingfor + '%' OR service_category LIKE '%' + @lookingfor + '%' OR service_overview LIKE '%' + @lookingfor + '%')
END
-STEP 2
DECLARE @UserCoordX integer
DECLARE @UserCoordY integer
--1) USE POSTCODE DATA FOR REGION ONLY
SELECT @UserCoordX = GRE,@UserCoordY = GRN FROM tPCD WHERE tPCD.PCD = @postcode
--STEP3
--open a cursor containing record ids with co-ordinates
DECLARE @record_uid uniqueidentifier
DECLARE @CoordX integer
DECLARE @CoordY integer
DECLARE @XLen integer
DECLARE @YLen integer
DECLARE @range real
DECLARE locs_cursor CURSOR FOR
SELECT record_uid,GRE,GRN FROM #tMatches
OPEN locs_cursor
FETCH NEXT FROM locs_cursor
INTO @record_uid,@CoordX,@CoordY
WHILE @@FETCH_STATUS = 0
BEGIN
--calculate range from entered postcode using pythagorus
SET @XLen = Abs(@UserCoordX - @CoordX)
SET @YLen = Abs(@UserCoordY - @CoordY)
SET @range = SQRT((@XLen * @XLen) + (@YLen * @YLen))
END
--convert range to miles
SET @range = COALESCE(@range,0)
IF (@range > 0)
SET @range = @range/160.9
--IF OUT OF RANGE, DELETE RECORD FROM TABLE STRAIGHTAWAY
IF @range > CAST(@miles as float)
DELETE FROM #tMatches WHERE record_uid = @record_uid
--get next record
FETCH NEXT FROM locs_cursor
INTO @record_uid,@CoordX,@CoordY
CLOSE locs_cursor
DEALLOCATE locs_cursor
--------------------RETURN ALL RESULTS-----------------------------------------------
SELECT record_uid,service_name,service_address,service_description FROM #tMatches
DROP Table #tMatches
I have zip code database and i am trying to get the records based on 100/80/60/40 miles radius. Below the sample structure of my table
Create table ZipCodes(ID bigint primary key identity(1,1),ZipCode varchar(10),
Address varchar(4000), city varchar(50),state varchar(50),Latitude float,Longitude float)If i pass the Zipcode as '10021'
I need to get zipcodes and other details of my table which is 20 mile radius circle.
I Google through and got couple of article explains about using Haversine Formula. Also, following link has a function to calculate the distance.
Hi!
How one can find out the character set/code page set during SQLServer installation?
Thanks,
Fabio
Hi,
Does anybody know where to find out the definition of all the SQL error code
For example, What does error code 4002 means ? What does error code 3146 means ? Thanks.
Hi all,
I spent about an hour searching the forums and the web but could not find a solution. Bob Bojanic, if you are around can you answer?
I have a DB that stores WW data for company names in a varchar
I don't have control over this DB, other than to pull data from it
I have an SSIS package that grabs WW data in a single pull using a system account
I have an Execute SQL task that runs a sproc to stage the data
I have a Data Flow Task that then copies the data to another server (where I need it)
The destination columns are nvarchar. The source columns are varchar.
Some countries (such as Korea and China) end up with ASCII gibberish (because of code page issues).
I have a solution that involves pulling the data, BCP the pulled data to a text file, then re-import with the correct code page, and delete the gibberish. BUT, I'd like to do this as part of the pull if possible (without any data duplication).
I've tried modifying the CodePage properties for both the Staging step (Execute SQL task running a sproc) and the Source & Dest columns for the Data Flow Task with no luck. Can anyone assist? Thanks much!
Brian
Hi...I want one listbox showing cities but I dont want to list a city more than one time....
I know that DISTINCT maybe could work...But I dont get it to work correctly....
The code:
<asp:ListBox ID="ListBox1" runat="server" AutoPostBack="True" DataSourceID="DataSource1" DataTextField="City" DataValueField="City"></asp:ListBox>
<asp:SqlDataSource ID="DataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
SelectCommand="SELECT DISTINCT [City] FROM [Location]">
</asp:SqlDataSource>
I got the message:
The text data type cannot be selected as DISTINCT because it is not comparable
The database have a table called "Location" with the column "ID" (int) as primary key and the column "City" (text)
Does anyone know where to find a simple sample showing you how to use asp.net 2.0 with the login control to direct you to another aspx page using SQL 2000 as the database on a hosted server? I have been beating my head against the wall trying to figure this out and would be very appreciative if anyone knows a place out there that shows this? I can find many sites that show you how to do this with SQL 2005, but not SQL 2000. Thanks - Chris
View 4 Replies View RelatedI have a table with code and description as below
create table isin_code
(
code varchar(5),
code_desc varchar(255)
)
go
insert into isin_code values ('aaa','aäsas')
go
insert into isin_code values ('aaa','asâ•šas')
go
insert into isin_code values ('aaa','aâsas')
go
insert into isin_code values ('aaa','asas')
go
I want to identify the list of alt codes available in the table.
We have a sql job type SSIS package that is not populating a database table as expected and I'm trying to figure what why not but I'm not sure where the "code" is. The person that created it said they copied a legacy package (sql 2000) and made modifications to it, then migrated it to the new version. When a legacy package is migrated is there a SSIS solution built? I see you can edit SSIS packages only in BI Development Studio but I'm not sure where to find package.
This post may answer my question but if you have any additional or updated information I'd like to hear it.
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=393634&SiteID=1
So, to view or edit the package you have to export it?
Package location
Select the type of storage location to export the package to. The following options are available:
SQL Server
File System
SSIS Package Storage
So do you pick "File System" in order to view it in BI?
Thanks for your help.
Dear all：
I have set the PrecompileScriptIntoBinaryCode property to true,but the Script Component show the message "can't find the binary code" after I finish the script language and save it . What's wrong with it ? I ever tried the same code, and it's OK with no problem. When did the problem happen ?
Please give me a help ....Thanks a lot!
Does anyone know how to query a field in a table where it contains anASCII code >= 128 - without looping through every field for everyrecord in table (using charindex)?Ex of char I would like to find: ü which is char(252)
View 1 Replies View RelatedGreetings:
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
I am building a stored procedure that changes based on the data that is available to the query. See below.
The query fails on line 24, I have the line highlighted like this.
Can anyone point out any problems with the sql?
------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------
This is the error...
Msg 8114, Level 16, State 5, Procedure sp_SearchCandidatesAdvanced, Line 24
Error converting data type varchar to numeric.
------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------
This is the exec point...
EXEC [dbo].[sp_SearchCandidatesAdvanced]
@LicenseType = 4,
@PositionType = 4,
@BeginAvailableDate = '10/10/2006',
@EndAvailableDate = '10/31/2007',
@EmployerLatitude = 29.346675,
@EmployerLongitude = -89.42251,
@Radius = 50
GO
------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------
This is the STORED PROCEDURE...
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[sp_SearchCandidatesAdvanced]
@LicenseType int = 0,
@PositionType int = 0,
@BeginAvailableDate DATETIME = NULL,
@EndAvailableDate DATETIME = NULL,
@EmployerLatitude DECIMAL(10, 6),
@EmployerLongitude DECIMAL(10, 6),
@Radius INT
AS
SET NOCOUNT ON
DECLARE @v_SQL NVARCHAR(2000)
DECLARE @v_RadiusMath NVARCHAR(1000)
DECLARE @earthRadius DECIMAL(10, 6)
SET @earthRadius = 3963.191
-- SET @EmployerLatitude = 29.346675
-- SET @EmployerLongitude = -89.42251
-- SET @radius = 50
SET @v_RadiusMath = 'ACOS((SIN(PI() * ' + @EmployerLatitude + ' / 180 ) * SIN(PI() * p.CurrentLatitude / 180)) + (COS(PI() * ' + @EmployerLatitude + ' / 180) * COS(PI() * p.CurrentLatitude / 180) * COS(PI()* p.CurrentLongitude / 180 - PI() * ' + @EmployerLongitude + ' / 180))) * ' + @earthRadius
SELECT @v_SQL = 'SELECT p.*, p.CurrentLatitude, p.CurrentLongitude, ' +
'Round(' + @v_RadiusMath + ', 0) AS Distance ' +
'FROM ProfileTable_1 p INNER JOIN CandidateSchedule c on p.UserId = c.UserId ' +
'WHERE ' + @v_RadiusMath + ' <= ' + @Radius
IF @LicenseType <> 0
BEGIN
SELECT @v_SQL = @v_SQL + ' AND LicenseTypeId = ' + @LicenseType
END
IF @PositionType <> 0
BEGIN
SELECT @v_SQL = @v_SQL + ' AND Position = ' + @PositionType
END
IF LEN(@BeginAvailableDate) > 0
BEGIN
SELECT @v_SQL = @v_SQL + ' AND Date BETWEEN ' + @BeginAvailableDate + ' AND ' + @EndAvailableDate
END
--SELECT @v_SQL = @v_SQL + 'ORDER BY CandidateSubscriptionEmployerId DESC, CandidateFavoritesEmployerId DESC, Distance'
PRINT(@v_SQL)
EXEC(@v_SQL)
-----------------------------------------------------------------------------------------------------------------
My store Procedure is not save in Strore Procedure folder at the time of saving it give me option to save in Project folder and file name default is SQLQuery6.sql when i save it after saving when i run my store procedure
exec [dbo].[SP_GetOrdersForCustomer] 'ALFKI'
I am getting below error :
Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'dbo.SP_GetOrdersForCustomer'.
Quick questiom, I need to Import a table with City and State Information
What are your recomendations? Should I create a Unique Key for them?
CityID, CityName, StateID?
StateID, StateName?
Or can I just store them in a table with one column?
CityName, State
Thanks for your help/
Hello I am new to SQL Server 2005 and am designing my first database.
In the AdventureWorks sample database the city field is included with the street address where it repeats for each row.
Why would you not put it in a separate table with state/province or a separate table all togeather?
Thanks for your help.