READTEXT Not Returning All Data
Apr 12, 2004
SELECT @@TEXTSIZE
SET TEXTSIZE 4096
SELECT @@TEXTSIZE
GO
DECLARE @ptrval varbinary(16)
SELECT @ptrval = TEXTPTR(emailTemplates.Data)
FROM emailTemplates WHERE [Name] = 'AccountInfoReceipt1'
READTEXT emailTemplates.Data @ptrval 0 2000
GO
Thats my code. and it never returns the entire email template. why?
thx in adv
View 2 Replies
ADVERTISEMENT
Aug 16, 1999
I am having issues READING an entire text field.
Also the SET TEXT command is returning only 255, too! Help!
My code:
DECLARE @ptrval binary(16)
, @TextSize int
SELECT @TextSize = (Select dataLength(pr_info)
FROM pub_info pr INNER JOIN publishers p
ON pr.pub_id = p.pub_id
AND p.pub_name = 'New Moon Books')
SELECT @ptrval = TEXTPTR(pr_info)
FROM pub_info pr INNER JOIN publishers p
ON pr.pub_id = p.pub_id
AND p.pub_name = 'New Moon Books'
READTEXT pub_info.pr_info @ptrval 0 @TextSize
View 2 Replies
View Related
May 6, 1999
I am working on a SQL 6.5 application that contains several tables with text fields. I would like to write a single stored procedure that could build the READTEXT statement for the text field on the fly based on parameters I pass in for the table name, column name, etc.
The procedure I want to write would look something like:
declare @txtpnt varbinary(16)
declare @column varchar(15)
declare @table varchar(15)
declare @idfield varchar(15)
declare @idval varchar(15)
EXECUTE ('select @txtpnt = textptr(' + @column + ')from ' +
@table + ' where ' + @idfield + ' = ' + @idval)
EXECUTE ('READTEXT ' + @table + '.' + @column + ' ' + @txtpnt + ' 0 10000')
Pretty as this looks, if you run this code, you will find that it doesn't work. The EXECUTE statement will not allow you to use a variable in the statement that is not declared as part of the statement. However, if I declare the variable as part of the EXECUTE statement, it is not recognized at compile time and I get an error when I create the stored procedure.
Is this the brick wall it seems to be, or am I missing something obvious here? I am using Access 97 as my front end, if that helps at all.
Any insight would be appreciated!
View 1 Replies
View Related
May 2, 2008
hi, has anyone ever tried saving the result of 'readtext' (ofcourse, not the entire text - just some of it usnig offsets in the readtext as : readtext texttab.textcolumn @ptr 0 100 ) into a 'varchar' variable ? if so, can you give me an example pls ?
thanks.
View 1 Replies
View Related
Apr 29, 2004
First question :
How can I set the return value of READTEXT to a variable of type nvarchar.
Second question :
I have a table t1 with a ntext column n1.
The ntext column has has words separated by empty space.
Each word can be assumed to be of size <= 255 characters.
How can I extract all the keywords in the ntext column to a table t2 with a column word nvarchar(255).
Assume that the text in column n1 is big enough so that it cannot be cast into a nvarchar or any other simpler type.
Any help on this is greatly appreciated.
Please do provide a sample code.
Alok.
View 1 Replies
View Related
Aug 31, 2007
In my stored procedure I need to do a WRITETEXT on some text data which is being returned from a READTEXT on other text data. I thought that this sort of thing would work:
DECLARE @comment varbinary(16)
DECLARE @comment_len int
DECLARE @comment_new varbinary(16)
...
SET @comment = TEXTPTR(atextcolumn) from atable where ...
SET @comment_len = DATALENGTH(atextcolumn) from atable where ...
SET @comment_new = TEXTPTR(anothertextcolumn) from anothertable where ...
WRITETEXT anothertextcolumn @comment_new READTEXT atextcolumn @comment 0 @comment_len
but I get the message from SQL Query Analyzer:
"Data stream missing from WRITETEXT statement."
I thought the READTEXT returned a data stream.
There must be a way to do what I want. Does anybody know what it is ?
View 3 Replies
View Related
May 7, 2014
I think I am definitely thrashing and am not getting anywhere on something I think should be pretty simple to accomplish: I need to pull the total amounts for compartments with different products which are under the same manifest and the same document number conditionally based on if the document types are "Starting" or "Ending" but the values come from the "Adjust" records.
So here is the DDL, sample data, and the ideal return rows
CREATE TABLE #InvLogData
(
Id BIGINT, --is actually an identity column
Manifest_Id BIGINT,
Doc_Num BIGINT,
Doc_Type CHAR(1), -- S = Starting, E = Ending, A = Adjust
Compart_Id TINYINT,
[Code] ....
I have tried a combination of the below statements but I keep coming back to not being able to actually grab the correct rows.
SELECT DISTINCT(column X)
FROM #InvLogData
GROUP BY X
HAVING COUNT(DISTINCT X) > 1
One further minor problem: I need to make this a set-based solution. This table grows by a couple hundred thousand rows a week, a co-worker suggested using a <shudder/> cursor to do the work but it would never be performant.
View 9 Replies
View Related
Feb 20, 2007
Hello,
Im currently working on a asp.net file hosting wesite, and im being faced with some problems
I currently have 4 sql tables. DownloadTable, MusicTable, ImageTable and VideoTable. Each of those tables contain a UserName column, a fileSize column and sme other columns. What i want to do is add up all the values in the fileSize column, for each table, and then add them up with the tables, and return one value, and all this happens where the UserName column corresponds to the UserName of the currently logged on User.
I already have an sql statement that performs this exact thing. It is as follow
select TotalDownLoadSize = sum(DownloadSize) + (select sum(VideoSize) from VideoTable where ([UserName] = @UserName ) ) + (select sum(ImageSize) from Images where ([UserName] = @UserName) ) + (select sum(MusicSize) from MusicTable where ([UserName] = @UserName) ) from DownloadTable where ([UserName] = @UserName)
But the problem is that all of the tables have to have a value in there size columns for the corresponding user, for this sql statement to return something.
For example, lets say i logged in as jon. If, for the UserName jon, the sum of DownloadTable returned 200, the sum of VideoTable returned 300, the sum of MusicTable returned 100. The sql statement i stated above will return 4 instead of 600, if the sum of ImageTable returned zero.
Is there way around this?
Im not sure if ive been cleared enough, please feel free to request more info as needed.
Thank you very much, and thx in advance.
View 5 Replies
View Related
Feb 22, 2007
I have a number of tables, and I need to create a summary of the data.Now I have two choices. I could create a stored procedure that creates a temp table with the filters applied. This would require 12 selects for each year (we have 3 years of data so far). This means 36 selects so far that fill a temp table.The second choice is to create a table with the required columns and rows. As I am esspecially cross-joining 4 tables, the table would have about 10 x 10 x 12 x A rows, where A is a variable that will grow quickly. I can then keep this table up-to-date using triggers for each insert. Then all I need to do is use an aggregate funtion on the relevant filter.My question is, which is more efficient. Creating a stored procedure that creates the table dynamically, or have a table with thousands of rows and using an aggregate function on these.PS I am using SQL Server 2000Jag
View 1 Replies
View Related
Jun 25, 2007
say i have some columns and a person can vote either once, twice or 3 times ( checkbox style control ) and in the database it looks something like this: eg: ID.... vote1..... vote2.... vote3 1 0 0 1 2 1 0 1for ID = 2, what type of query would i have to write to return the column names vote1 & vote3 ( vote2 is not null ) Cheers!!!
View 1 Replies
View Related
Oct 21, 2007
Hi! I have a sql query in stored procedure: SELECT Salutation + ' ' + FirstName + ' ' + LastName AS fullname
Ok, this returns a value if salutation is not null, but if the salutation is null it doesn't return any value, I was thinking if the saluation is null then I would atleast get the firstname and last name. Any help appreciated on this.
View 4 Replies
View Related
Apr 2, 2006
I have a stored procedure (in SQL Server 2005 Express) that returns a string. The problem is when I call it from my web page I get only the first character of the string. This is my SP:
ALTER PROCEDURE dbo.usp_CalcDeliveryCharge
@mintDistance int,
@mintCustomer_ID int = 0,
@mintRate int = 0 OUTPUT,
@mstrZone nchar(10) = null OUTPUT
AS
/* SET NOCOUNT ON */
SELECT @mintRate=RATE,
@mstrZone=ZONE
FROM tblRates
WHERE Customer_ID=@mintCustomer_ID
AND Mile_Range_Min <= @mintDistance
AND Mile_Range_Max >= @mintDistance
And this is my code:
sql_Command.CommandText = "usp_CalcDeliveryCharge"
sql_Command.CommandType = CommandType.StoredProcedure
sql_Command.Parameters.Clear()
sql_Command.Parameters.AddWithValue("@mintDistance", intApproxMiles)
sql_Command.Parameters.AddWithValue("@mintCustomer_ID", Profile.CompanyID)
sql_Conn.Open()
sql_Reader = sql_Command.ExecuteReader()
While (sql_Reader.Read())
Me.lblZone.Text = sql_Reader.Item(0).ToString
End While
sql_Conn.Close()
sql_Reader.Close()
sql_Command.Dispose()
View 1 Replies
View Related
Apr 17, 2008
I have two databases with table structures exactly the same. The query in one database works and returns correctly. The same query (copied and pasted) in another database returns the correct records but completely ignores the order by. Even stranger, when you are creating the query under modify and run it, it is fine. But when I right-click on the query and go to 'Open View' then the query does not return correctly. Below is the query:
SELECT TOP (100) PERCENT pf.description AS incentive, CAST(i.targetc AS FLOAT) AS targetc, i.targetp, b.basis, d.description, i.iSelect, i.direction, pf.pValue,
pf.pFigure
FROM dbo.PerfFactorWeights AS pfw INNER JOIN
dbo.PerfFactors AS pf ON pfw.incentiveID = pf.incentiveID INNER JOIN
dbo.Incentive AS i ON pfw.incentiveID = i.incentiveID AND pfw.basis = i.basisID INNER JOIN
dbo.Basis AS b ON i.basisID = b.basisID INNER JOIN
dbo.Division AS d ON pfw.division = d.uniqueID
ORDER BY incentive, d.uniqueID, b.basis
View 4 Replies
View Related
May 29, 2008
Hello everyone,
I set up a View in SQL Server 2005. The syntax checks ok, however, when I execute it, it doesn't return any data.
This is my Query:
SELECT DATEPART(hh, Time) AS Time, COUNT([Recipient-Address]) AS [CountOfRecipient-Address], ROUND(SUM([Total-bytes]) / 1048576, 2)
AS [SumOfTotal-Bytes]
FROM dbo.TrackingLog
WHERE (RIGHT([Recipient-Address], LEN([Recipient-Address]) - PATINDEX([Recipient-Address], '@')) IN
(SELECT Domains
FROM dbo.Domains))
GROUP BY DATEPART(hh, Time)
The part that I am most concerned about is the WHERE section. If I remove it, I get some data returned. If I don't, obviously I don't get anything back.
Any suggestion would be great.
Thanks
View 4 Replies
View Related
Apr 9, 2007
I'm having trouble with the display of data when I test my asp page. I basically retrieves or pulls data from different tables in a database. Based on an ID number (which I can change manually by typing in a value and appending it to the URL), it shows GuestName, GuestDescription and URL as text link. I can get it to do that much. But for some reason it's not showing the URL associated with the Guest Name. I dont think it's the display section of my code. I think it's the Select statement.
Here is what the output looks like in my Firefox and IE browsers when I test. Some data has been changed to protect it's integrity:
(Name in bold) Mark Crisp's author of The Bush Lexicon. His new book...
Related Links:
John Doe interviewed by Susan Smith, 123 News
What it should say in the link is this: Mark Crisp's blog (as text link)
When I test for different data I just change the end of the URL like this:
defaultprogram4.asp?ID=1234 (this number represents an ID column in a table)
Code Snippet
<%
set con = Server.CreateObject("ADODB.Connection")
con.Open "File Name=E:webserviceCompanyCompany.UDL"
set rs = Server.CreateObject("ADODB.Recordset")
id=request.querystring("id") 'If this line is commented out the page will be blank.
'However you can still append a record number to the end of the URL and display that one.
IF id <> "" then id=id else id="1234" end if 'This line shows the default record of 1234. If this line is commented out the page will ONLY show the default record but will NOT allow you
'to append a different number
strSQL = "SELECT *, T_Programs.ID AS Expr1, T_ProgramGuests.ProgramID AS Expr2, T_ProgramGuests.GuestName AS Expr3, T_ProgramGuests.GuestDescription AS Expr4, T_ProgramLinks.URL AS Expr5, T_ProgramLinks.Description AS Expr6 FROM T_ProgramGuests CROSS JOIN T_Programs CROSS JOIN T_ProgramLinks WHERE (T_ProgramGuests.ProgramID = '" & id & "')"
rs.Open strSQL,con 'open a connection to the database
%>
<br />
<strong><% Response.Write RS ("GuestName") %> </strong> <% Response.Write RS("GuestDescription") %><br /><br />
Related Links:<br />
<li class='basic'><A HREF="<%= RS("URL") %>"><%= RS("Description") %></A></li>
<!-- END OF THE GUESTS AND LINKS SECTION -->
<%
recProgram.Close
con.Close
set recProgram = nothing
set con = nothing
%>
Bottom line here is this: Why would I be seeing one name but a link not associated with that name? It's as though it's reading a name from the ProgramGuests table and a URL from the
ProgramLinks table (Except that: it shows a completely different unrelated URL to that name).
What am I missing?
View 1 Replies
View Related
Apr 19, 2006
i want to return certain number of columns frm table using store procedure .and i hav to load those returned data into text boxes which i created in asp.net page.is there a way
View 2 Replies
View Related
Jul 14, 2007
Hello,
I am trying to use the data returned by a webservice that our application team has built.
What method should they use to return the data to the caller ?
If they try to retun a data set the SSIS is saying that it is not a well formated XML.
The webservice is using an API call to HIS, can I make this call using C# or some other mechanisam directly from SSIS?
View 3 Replies
View Related
Jan 12, 2008
Hello,
I recently created a linked server to db2. When I do a join on the linked server it is returning to much data.
select * from table1 left join [linkedServer]..Schema.Table as tb1 on table1.id=tb1.id
For now, I had to first get the ids I wanted from table1 and then use a subquery to join table1 on the a result set produced from a query to the linked server that only specified those ids. Below is an example
select * from table1 left join (select col1 from [linkedServer]..Schema.table where id=1 or id=2) as tb1 on table1.id=tb1.id
I'm guessing I have some setting wrong. One of the biggest reasons for me to use the four part name is so I can do joins like the first example.
Please let me know if you have any ideas.
Thanks
View 4 Replies
View Related
Jan 18, 2008
Hi all,
I have a simple query like this:
SELECT crop, ItemNo, LotNo, Site, Process, IntID, ObsDetail, ObsValue,
(CASE WHEN (rtrim(crop) = '010' OR
rtrim(crop) = '010W' OR
rtrim(crop) = '019' OR
rtrim(crop) = '019W' OR
rtrim(crop) = '018') THEN 'CORN' WHEN (rtrim(crop) = '0150' OR
rtrim(crop) = '0159' OR
rtrim(crop) = '0150W' OR
rtrim(crop) = '159W' OR
rtrim(crop) = '158') THEN 'RICE' ELSE '' END) AS Product, TestStartDate, TestEndDate, ObsString
FROM V_LAB
WHERE (LotNo >= @lotStart) AND (LotNo <= @lotEnd)
If I run this query and filled the lotStart and lotEnd parameter with: G0424MK
Then, below's the data returned. (I run the query in management studio)
010W
30A97-CLN-UNT-BULK
G0424MK
IDPMLG01
AFTER CLEANER
4982
PRCT MOIST
10.35
CORN
NULL
010W
30A97-CLN-UNT-BULK
G0424MK
IDPMLG01
AFTER CLEANER
4982
PRCT GOOD
97.5
CORN
NULL
010W
30A97-CLN-UNT-BULK
G0424MK
IDPMLG01
AFTER CLEANER
4982
PRCENTMEAN
97.75
CORN
NULL
I'd only going to retrive 3 data that i'd like to show on the report:
1. Moisture Content, with formula:
=IIf(RTRIM(Fields!ObsDetail.Value) = "PRCT MOIST", Fields!ObsValue.Value, cdec(0.00))
the ObsValue value after i run on the report preview with LotStart and LotEnd G0424MK is: 10.35 (this is correct)
<!--[if !supportLists]-->2. Physical Damage, with formula:
=Iif(rtrim(Fields!ObsDetail.Value) = "PRCT GOOD", Fields!ObsValue.Value, cdec(0.00))
the ObsValue value after i run on the report preview with LotStart and LotEnd G0424MK sebesar: 97.50 (this is correct)
<!--[if !supportLists]-->3. Warm Test, with formula:
=Iif(rtrim(Fields!ObsDetail.Value) = "PRCENTMEAN", Fields!ObsValue.Value, cdec(0.00))
the ObsValue value after i run on the report preview with LotStart and LotEnd G0424MK sebesar: 0.00 (this is wrong) because it should be shown 97.75
anybody knows which part i made my mistake?
Thanks!
Addin
View 6 Replies
View Related
Feb 26, 2007
Hi
I have coded the simple login page in vb .net which calls the stored proc to verify whether the user login details exists in the database. The stored procudure returns data back when I execute it in the SQL SERVER Management studio. But when I execute the stored proc in the 'Run stored Proc' wizard' , it is not retuning any data back. Connection string works fine as another SQL select command returns data in the same page.. I have included the VB code . Please help me to sort out this problem.Thank you.
If Not ((txtuser.Text = "") Or (txtpassword.Text = "")) Then
Dim conn As New SqlConnection()
conn.ConnectionString = Session("constr")
conn.Open()
Dim cmd As New SqlCommand("dbo.CheckLogin", conn)
cmd.CommandType = CommandType.StoredProcedure
' Create a SqlParameter for each parameter in the stored procedure.
Dim usernameParam As New SqlParameter("@userName", SqlDbType.VarChar, 10)
usernameParam.Value = Trim(txtuser.Text)
Dim pswdParam As New SqlParameter("@password", SqlDbType.NVarChar, 10)
pswdParam.Value = Trim(txtpassword.Text)
Dim useridParam As New SqlParameter("@userid", SqlDbType.NChar, 5)
Dim usercodeParam As New SqlParameter("@usercode", SqlDbType.VarChar, 10)
Dim levelParam As New SqlParameter("@levelname", SqlDbType.VarChar, 50)
'IMPORTANT - must set Direction as Output
useridParam.Direction = ParameterDirection.Output
usercodeParam.Direction = ParameterDirection.Output
levelParam.Direction = ParameterDirection.Output
'Finally, add the parameter to the Command's Parameters collection
cmd.Parameters.Add(usernameParam)
cmd.Parameters.Add(pswdParam)
cmd.Parameters.Add(useridParam)
cmd.Parameters.Add(usercodeParam)
cmd.Parameters.Add(levelParam)
Dim reader1 As SqlDataReader
Try
If conn.State = ConnectionState.Closed Then
conn.Open()
End If
Try
reader1 = cmd.ExecuteReader
Using reader1
If reader1.Read Then
Response.Write(CStr(reader1.Read))
Session("userid") = reader1.GetValue(0)
Session("usercode") = CStr(usercodeParam.Value)
Session("level") = CStr(levelParam.Value)
Server.Transfer("home.aspx")
Else
ErrorLbl.Text = "Inavlid Login. Please Try logging again" & Session("userid") & Session("usercode") & Session("level")
End If
End Using
Catch ex As InvalidOperationException
ErrorLbl.Text = ex.ToString()
End Try
Finally
If conn.State <> ConnectionState.Closed Then
conn.Close()
End If
End Try
Else
ErrorLbl.Text = "Please enter you username and password"
End If
View 5 Replies
View Related
Jul 23, 2005
I am using SQL7 Query Analyzer. A simple select * from myMLSview andthen I save results as a .csv file has the commas messed up in quite afew places. The data is messed up before I save it to the .csv file. Sothere are blank spaces being added here and there causing them to readas 'add comma' when saving to .csv format. Even the column data ismessed up... example: ,photo_mod_time,photo_mo,d_date, should read,photo_mod_time,photo_mod_date,I have a view on a remote MS-SQL7 Server, the view has about 200columns and the data returned from the my querys can be from 10 to 25MBin size. Connection is via Roadrunner/cable. Should I use anothermethod, maybe command line? Any suggestions?
View 3 Replies
View Related
Feb 12, 2008
Hello,
I am trying to get records related to a Device_ID with the following conditions:
IF LastModified_Date BETWEEN (@p_datetime_StartDate AND @p_datetime_EndDate) OUTPUT
ELSE OUTPUT ALL.
I want records between the range of date selected, or all the records if no date entered. Can somebody help me ASAP. Here is what I have so far. I just don't know where to put the IF...THEN ELSE.
SELECT Device_ID,
Action,
Username,
LastModified_Date,
FROM ActivityMonitorActionLogs
WHERE Device_ID = ISNULL (@p_int_Device_ID, AL.Device_ID)
AND LastModified_Date BETWEEN (@p_datetime_StartDate AND @p_datetime_EndDate)
Thanks a lot!!!
Mylene
View 3 Replies
View Related
Mar 28, 2006
I have a .rdl file that was exported out of ProClarity's Desktop Professional 6.1 using their RS plug-in. I uploaded the file into Report Manager and when I execute it, I get the following error:
An error has occurred during report processing.
Query execution failed for data set 'Three_Month_Funding_Trend'.
Unable to recognize the requested property ID 'ReturnCellProperties'.
Does anyone have any idea what this is referring to? Does it have something to do with my configuration, connection or the report definition? Other reports such as DBMS based reports work fine.
Thanks for your assistance
View 11 Replies
View Related
Nov 20, 2007
I've built a simple VS2005 ASP.Net web app that uses Crystal Reports for generating assorted reports. More specifically, when a report is called it executes the correct SQL Server Stored Procedure and returns a Data Table populated with with appropriate data. In my testing, everything seemed to be working fine.But I just did a test where I pressed the "Submit" button on two different client browsers at almost the same time. Without fail, one browser returns the report as it should but the other one returns an empty report; all of the Crystal Reports template info is there but the report is just empty of data. Considering that each browser is running in its own session, I'm confused about why this is happening.One thing: I did login as the same user in both cases. Might this be causing the problem?Robert W.Vancouver, BC
View 7 Replies
View Related
Oct 16, 2007
SQL 2005 9.00.3402.00 (x64) As Above really when I select * OR select a single column from the view the wrong column data is returned. in SQL Management Studio when I expand the Columns of the view it reflects the old table structure not the new table structure. I can easily fix by compiling the view again but this would mean I would have to recompile all referencing views when I make a change to table structures. I've tried various DBCC Clean Buffers & drop cache with no effect. Is there a command to recompile all views & poss stored procs in a database. Any help or explanation would be appreciated GW
View 12 Replies
View Related
Mar 23, 2014
I have a datagridview bound to a table that is part of an Entity Framework model. A user can edit data in the datagridview and save the changes back to SQL. But, there is a stored procedure that can also change the data, in SQL, not in the datagridview. When I try to "refresh" the datagridview the linq query always returned the older cached data. Here's the code that I have tried using to force EF to pull retrieve new data:
// now refresh the maintenance datagridview data source
using (var context = new spdwEntities())
{
var maintData =
from o in spdwContext.MR_EquipmentCheck
where o.ProdDate == editDate
orderby o.Caster, o.Strand
select o;
mnt_DGV.DataSource = maintData;
}
When I debug, I can see that the SQL table has the updated data in it, but when this snippet of code runs, maintData has the old data in it.
View 0 Replies
View Related
Aug 1, 2006
Hi all--I've got a derived column transformation where I am adding a field called Import_Date. I'm telling it to add as a new column and use the function "GetDate()" to populate the field. When I run the package, it returns NULL as the data value for all rows. Any idea why this might be happening?
View 5 Replies
View Related
Aug 3, 2015
I am writing a stored procedure to prepare some reports.
The issue is that I am summing up the combined bid, 'QuoteTotal' + Sum(InvoiceItemAmount) (eg, quote add ons).
When part of the larger query it returns a different, and incorrect amount. The query listed after the main query is just that line and it's appropriate parts and it returns the correct amount. What can I do to correct this and where lies the problem so I can learn from this situation?
alter PROCEDURE [dbo].[Select_Quote_Info_By_Salesmen_By_Status]
@Salesmen nvarchar(50),
@QuoteStatus nvarchar(50)
AS
BEGIN
SET NOCOUNT ON;
[code]...
View 5 Replies
View Related
Sep 16, 2015
I have a xml file downloaded from webAPI which i want to import into my database.There are several sub childnodes and need to combine data between nodes.
<?xml version="1.0" encoding="UTF-8"?>
<classes type="array">
<class>
<class_id>3S</class_id>
<class_pk type="integer">3900</class_pk>
[code]....
View 4 Replies
View Related
Oct 13, 2014
A DB2 store procedure returns two data sets, when executed from SSMS, using linked server. Do we have any simple way to save the two data sets in two different tables ?
View 1 Replies
View Related
Apr 29, 2015
I have the two following locations.
They're both towns in Australia , State of Victoria
Fitzroy,-37.798701, 144.978687
Footscray,-37.799736, 144.899734
After running geography::Point(Latitude, Longitude , 4326) on the latitude and longitude provided for each location, my Geography column for each row is populated with the following:
Fitzroy, 0xE6100000010C292499D53BE642C0A7406667511F6240
Footscray, 0xE6100000010C89B7CEBF5DE642C02D23F59ECA1C6240
In my SQL Query, I have the following which works out the distance between both towns. Geo being my Geography column
DECLARE @s geography = 0xE6100000010C292499D53BE642C0A7406667511F6240 -- Fitzroy
DECLARE @t geography = 0xE6100000010C89B7CEBF5DE642C02D23F59ECA1C6240 -- Footscray
SELECT @s.STDistance(@t)
The result I get is
6954.44911927616
I then looked at formatting this as in Australia we go by KM so after some searching I found two solutions one for Miles and the other KM
So I changed Select statement to look like this
select @s.STDistance(@t)/1000 -- format to KM
My result is then
6.95444911927616
When I go to google maps and do a direction request between the locations provided above it says 10.2km (depending on traffic)
Now I'm new to this spatial data within SQL, why would I get a different result from google maps?
Also I would like to round this number so its easier to use within my where statement so I'm using Ceiling as shown here:
SELECT CEILING(@s.STDistance(@t)/1000)
Is ceiling the correct way to go?
Reason I need to round this is because we are allowing the end user to search by radius so if they pass in 50km I will then say
Where CEILING(@s.STDistance(@t)/1000) < 50
View 2 Replies
View Related
May 2, 2008
Hello:
Here is the scenario: I have a report in SSRS 2005 that uses an OLE DB connection to send a MDX query to an AS 2000 cube. The report has been working fine for weeks. When the report ran today, it returned the row and column metadata, but not the cell values.
I ran the MDX query separately in the SSRS query editor pane, and it returns all values properly. I was also able to perform the query using ProClarity, so it appears that the issue is between the result set and SSRS.
What would cause this to happen?
Thanks for your help!
Tim
View 4 Replies
View Related
Jan 14, 2008
All,
I'm having an issue with the Flat File Data Flow Source returning only a limited set of the rows that are in the flat file. Basically, I connect to the flat file fine, it goes to retrieve the data (tab delimited file) and only returns 190 of 392 rows. Is there a limitation on the # of rows this data flow source can retrieve or something? I've look all through the settings and properties of the task as well as the connection manager and nothing is obvious as to what is causing this. Hopefully someone ou tthere has run into this before and can help me retrieve all rows. Thanks in advance!
bakerz
View 4 Replies
View Related