|
|
I have a column in my database called SaleNumber which has a datatype numeric(18,0). One of the values I have stored in there is 11101930593 however when I retrieve this value from the database in my delphi application the number displayed is -1782971295.
Does anybody know what could possibly be going on here?
|
|
|
|
|
I don't know Delphi so this could be way off the mark. I'm guessing that it might be an overflow. Are you putting the column value in a 32bit integer? A 32bit integer has a range of rougly -2billion to +2billion. Your number is roughly 11 billion.
|
|
|
|
|
No I am parsing it in as an unsigned long.
|
|
|
|
|
It looks like Colin is right. You tried, somewhere along the line, to stuff a 64-bit number into a signed 32-bit variable. What you're seeing is a sure sign of an overflowed signed 32-bit value.
Step through your code and verify that the value you got from the database is what you expect, the instant you get that value. Then follow it from there. Perhaps the value IS an unsigned 64-bit value, but when you went to display it, it got truncated to a signed 32-bits.
Dave Kreskowiak
Microsoft MVP - Visual Basic
|
|
|
|
|
When I try to set up an email subscription for SSRS 2000, I get:
EXECUTE permission denied on object 'sp_add_category', database 'msdb', owner 'dbo'.
The dbo user for the msdb has admin priviledges. I'm stumped on what to do or look for.
Found this in the catalog.sql file:
GRANT EXECUTE ON msdb.dbo.sp_add_category TO RSExecRole
I ran this script for the msdb database via Query Analyzer, tried the subscription again, and got:
An internal error occurred on the report server. See the error log for more details. (rsInternalError) Get Online Help EXECUTE permission denied on object 'sp_add_category', database 'msdb', owner 'dbo'.
We're connecting to the db server from a shared datasource w/ a user, let's say "reportuser", that has only "Read Access" to pull the data.
Any suggestions? Thanks!
|
|
|
|
|
Does anyone know how to make a prepared statement for the code below so it will parse the query only once?
Dim myConnString As String = My.Settings.ConnectionString
Dim MyConnection As New MySqlConnection(myConnString)
MyConnection.Open()
Dim MyCommand As New MySqlCommand("INSERT INTO ls_partsprice (PartNum, PartRetailPrice, PartSalePrice) Values('" & PartNum & "','" & PartRetailPrice & "','" & PartSalePrice & "')")
MyCommand.Connection = MyConnection
MyCommand.ExecuteNonQuery()
MyConnection.Close()
|
|
|
|
|
If you want to parameterize the query, use the following syntax instead of concatenating strings:
Dim MyCommand As New MySqlCommand("INSERT INTO ls_partsprice (PartNum, PartRetailPrice, PartSalePrice) Values(@PartNum,@PartRetailPrice,@PartSalePrice)")
Then use MyCommand.Parameters.Add() method to create parameters which match the ones in the SQL string (@PartNum, @PartRetailPrice,@PartSalePrice). Make sure after you create the parameters to set the precision and scale for the 'Price' parameters otherwise the values will vary based on the value you pass. Which means that SQL server will reparse the query.
MyCommand.Parameters("@PartRetailPrice").Precision = 5
MyCommand.Parameters("@PartRetailPrice").Scale = 2
DISCLAIMER: I'm not a VB.Net programmer, so the actual syntax may be different than what I've put here, but you should get the idea.
Also, check out my article on parameterizing SQL queries: http://www.codeproject.com/cs/database/ParameterizingAdHocSQL.asp[^]
|
|
|
|
|
Hi Guys I need help with writing the following query
The Query
Select daydesc as description ,Features,<br> adfull, askprice, bedrooms as bedroom,rooms,<br> floorarea as sqm, (pADDRESS1 + ''+ PADDRESS2+ ''+PADDRESS2+ ''+PADDRESS3+ ''+ PADDRESS4) AS ADDRESS ,directions <br> <br>from dbo.PROPERTY as Property<br> where property_id = @id for xml auto,elements
Which Currently returns this
< Property><br> <description>Space and comfort set the tone of this stylish four bedroomed dormer bungalow on the Moynalty Road just outside the village of Mullagh. Situated just short stroll into Mullagh village and all it's amenities, this proeprty offers a purchaser a spacious family home with open-plan kitchen, dining and living areas. Althrough deceptive from the outside, this property is surprisingly spacious and well-laid out with three bedrooms (one en-suite) and family bathroom on the first floor and a very useful double bedroom with adjacent guest w.c, large sitting room, kitchen/dining, separate living/dining and utility room on the ground floor. Viewing by appointment with the Auctioneers is highly recommended.</description><br> <features>Deceptively spacious family home <br>Quality cream fitted kitchen <br>Two fine reception rooms <br>Slate-tiled kitchen/dining room <br>Television points in all rooms <br>Just a short stroll or minutes drive from the village of Mullagh <br>Close to local Primary School<br>Close to shops, school, church, hotel, playgroup etc.<br>On bus route to secondary schools</features><br> <askprice>3.7000000e+005</askprice><br> <bedroom>4</bedroom><br> <rooms>Entrance Hall (5.38m x 1.78m)<br>Guest W.C. (2.39m x 1.40m)<br>Bedroom (4.35m x 2.51m)<br>Sitting Room (5.98m x 3.62m)<br>Kitchen/Dining Room (8.22m x 3.68m)<br>Utility Room (2.84m x 2.42m)<br>Dining/Living Room (5.38m x 2.84m)<br>Landing ()<br>Bedroom (5.16m x 2.38m)<br>En-Suite Shower Room (1.92m x 1.87m)<br>Bedroom (4.91m x 2.83m)<br>Bedroom (4.17m x 2.88m)<br>Family Bathroom (3.44m x 2.19m)<br></rooms><br> <sqm>166</sqm><br> <directions>From Kells, travel about 4.5 miles into Moynalty village, taking left at the RC church and travel almost two miles. The property is just outside the village of Mullagh on the right hand side.</directions><br></Property>
It works as expected However, what I would like to achieve is that within the Features field, which is held as a text field in the db, contains the features separated by carriage return statements. I would like to extract features XML so that it looks like
<Features><br><feature>blah</feature><br><feature>blah blah</feature><br></Features>
Is this possible ?
I thought about using something like
CHARINDEX(features,CHAR(13)+CHAR(10))as feature
But a little unsure of how to make it work? Any tips welcome
|
|
|
|
|
Some of the fomatting didn't appear to come through - you may like to modify your post.
Either remove the HTML from the post and click the "Ignore HTML tags in this message (good for code snippets)" or ensure that all opening angle brackets are properly defined as <
|
|
|
|
|
I don't understand, The post seems to be ok on my side?
|
|
|
|
|
cykophysh39 wrote: The post seems to be ok on my side?
There is a set of pre tags with nothing apparently in between.
|
|
|
|
|
Thanks for your time but I managed to find a solution
it goes something like this
select daydesc as description ,
cast('<feature>'
+ replace(cast(Features as varchar(max)), char(13)+char(10), '<feature>')
+ '' as xml) AS Features,
adfull, askprice, bedrooms as bedroom,rooms,
floorarea as sqm, (pADDRESS1 + ''+ PADDRESS2+ ''+PADDRESS2
+ ''+PADDRESS3+ ''+ PADDRESS4) AS ADDRESS ,directions
from dbo.PROPERTY as Property
where property_id = @id
for xml auto,elements
|
|
|
|
|
Fair enough. 
|
|
|
|
|
I am using ado from within an excel to update a table in a remote sqlsvr 2005 database. This is fairly knew for me. It works, but the creation of the recordset object takes about 15 seconds. I don't know if the delay is something I can do on my end (excel/vba/ado) or if it's a sql server issue. The table I am adding the record to is fairly large (at least to me it's big) at 1.6 million rows. All other ado statements run blindly fast. Below is my vba code.
Sub SendSchChg()
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim sConnString As String
sConnString = "Provider=SQLOLEDB;server=XXXXXX-SQL02;UID=xxxxx;PWD=xxxxx;DATABASE=datalog"
cn.Open sConnString
rs.Open "Select LineID, ProcessValueID, dts, PointValue from datalog", _
cn, adOpenKeyset, adLockOptimistic, adCmdText
With rs
.AddNew
!LineID = 12
!ProcessValueID = 60
!dts = Now()
!PointValue = 1
.Update
End With
rs.Close
cn.Close
Set rs = Nothing
Set cn = Nothing
End Sub
|
|
|
|
|
If you are just adding new records then you might want to try adding "where 0 = 1" to your select statement. That way it won't try to read the full contents of the SQL-Server table into your PC's memory.
|
|
|
|
|
Perfect! It flies now! Thanks!
|
|
|
|
|
I am trying to take Backup and Restore Using VB.NET or C#.NET
Can you please help me how to do.
With code snippet.
Yugan
|
|
|
|
|
There are no ADO.Net commands/methods which perform a backup. But you can submit a query using an ADO.Net command to perform a backup or restore the same way you would submit a SELECT, INSERT, UPDATE or DELETE query to the server. Your best source for syntax would be SQL Server Books Online (aka BOL). Here's a quick snippet to get you started (C#):
<br />
using(SqlConnection conn = new SqlConnection("connection string here")){<br />
SqlCommand cmd = new SqlCommand();<br />
cmd.Connection = conn;<br />
cmd.CommandText = "BACKUP DATABASE [datbase name here] TO DISK = N'file path and name of backup here'";<br />
cmd.CommandType = CommandType.Text;<br />
<br />
conn.Open();<br />
<br />
cmd.ExecuteNonQuery();<br />
}<br />
Just make sure that the file path of the backup is a path which exists on the server where SQL Server is installed. You may also want to set the CommandTimeout property if the database is very large.
|
|
|
|
|
Hi
How to find foreign key for a given table in sql server 2005.
exec sp_fkeys @pktable_name='authors' //It is working fine in sql 2000
But the same is not working for 2005.
exec sp_fkeys @pktable_name='[Production].[Product]'
also I tried
exec sp_fkeys @pktable_name='[Test].[Production].[Product]'
Can any one help me please
kesavan
|
|
|
|
|
Try this:
sp_fkeys @PKTable_name = N'Product', @PKTable_owner = N'Sales'
(This, of course, assumes that you have a product table in a Sales schema).
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Thanks a lot pete
kesavan
|
|
|
|
|
hi,
i have a trigger on a table and in the trigger i want to RAISE an ERROR and also log the error into a table..
The error works fine (it is raised) but it seems there is no record in my logTable. i suppose the complete transaction is rolled back, including my insert into my logtable.
any idea how i can solve this problem can i exclude my insert into my logtable from the roll back ?
thx
Kurt
|
|
|
|
|
The normal way to do this is to insert the item into the log table inside a different transaction.
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Thx for the response,
so this means i can't do it inside the trigger ?
if the [After] trigger raises an error it rolles back al the previous actions. including the insert in the table (A) ... and including the insert in the error log table (B).
i suppose there must be a way to log errors in triggers on tables... without using an application but straight from the database server.. our i must use a stored proc to insert into my table (A) (the one that has a trigger) by using begin try and begin catch
Thx
Kurt
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
