|
i have a Query, below, i was to Display results based on the first results
select* from property where substring(func_key,1,5)='GEOSS'
(select * from property where
substring(func_key,6,8) <=2)
1)First Query will Display all records where in the Functional key column there is 'GEOOS' accordingly and it working, and i want to search again from this results.
2)I want to Search all records where Fun_key is <=2, based on the First one.
when i run this Query here is the Error i get.
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
(40569 row(s) affected)
Server: Msg 245, Level 16, State 1, Line 2
Syntax error converting the varchar value 'J00' to a column of data type int.
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Help
Vuyiswa
|
|
|
|
|
Can U try this?
select* from property where substring(func_key,1,5)='GEOSS'
AND substring(func_key,6,8) <=2)
or
Select * from
(select* from property where substring(func_key,1,5)='GEOSS')A
where substring(func_key,6,8) <=2
Regards,
Arun Kumar.A
|
|
|
|
|
/***Find All the GEOSS records that start from 1 ends with 2****/
select* from property where substring(func_key,1,5)='GEOSS'
AND substring(func_key,6,8) <=2
/*from the Above results i want to Select Distinct the Functional Key*/
select distinct fun_key from "(above results)"
Vuyiswa
|
|
|
|
|
Try this:
select distinct(func_key) from property where substring(func_key,1,5)='GEOSS'
AND substring(func_key,6,8) <=2
Regards,
Arun Kumar.A
|
|
|
|
|
Thank man it work.
Vuyiswa
|
|
|
|
|
Hello
I have a task coming up shortly which involves loading several BILLION rows of data from a text file into SQL 2000.
Wondering what the fastest way to do this is eg SQL BULK INSERT or code it up in C# executable etc.
Does anyone know any tips or tricks?
|
|
|
|
|
From one text file? Is it at least CSV or fixed-width?
The SQL Server bulk copy utility is bcp, but I wouldn't use it for such an extensive import, I'd use a program (written in C#, but that's just me).
I would have the program commit and checkpoint its position in the file once in a while (10000 rows?) so it can restart near where it left off after a failure. With bcp you'd probably have to start all over.
In my database library routines I also have the ability to make a log entry every so many inserts or seconds so I can watch the log and be sure it's actually still progressing, and get a feel for how long it's taking. With bcp you're flying blind.
I do occasionally use bcp for small imports, and it usually involves importing to a sort of "raw" table from whence I can then copy the data to the proper places, especially if data from each row has to be put into several tables.
|
|
|
|
|
Thanks for the info. Data is in several files.
I'll write some C# stuff to analyse it and make sure there are no errors before load. Tried a test on that yesterday with hundred million rows and only took a couple of minutes. Slow point is SQL import.
Commit at intervals is a good idea.
Testing 1 million rows import at the moment (C# and Stored Procedure) and getting about 7 minutes. Thats only five days processing for a billion records...
Might try SqlBulkCopy class next and see whether thats any faster.
Thanks
-- modified at 1:40 Friday 4th May, 2007
SqlBulkCopy - 14 seconds!
|
|
|
|
|
Wel wel,
I always new that stored procedures are faster then runtime build queries, but i bumped into something weird and now i have my doubts.
- Retrieving data with a normal query returns a table length 4000 bytes returned from server.
- Retrieving exactly the same data using a stored procedure returns a table length of 6800 bytes returned from server.
So if bandwidth is important for example on 128000 bits/sec connection, then i suppose i go for the Queries..
is there an expert who can explain this item?
Thx
Kurt
|
|
|
|
|
What is the difference when large result sets are returned. Is the difference a static 1600 bytes, or is it a percentage of the result set?
|
|
|
|
|
that i don't know, but i suppose it will be static information about the stored procedure. i'll have to test that.
well it takes a difference of nearly 200 ms on a 128000 b/s line
4000 bytes = 250 ms
6800 bytes = 425 ms
so retrieve 5 tables of that kind and you'll end up on 2.1 seconds waiting for data comparing to 1 second.
this can be important yes...
|
|
|
|
|
Iam Moving from vb6 to .Net, i need Advice what is the differences in syntax and where are the difference between the two. give me links to E-books or tutorials
Vuyiswa
|
|
|
|
|
SQL Syntax will remain the same between VB6 and VB.NET as the SQL code is parsed by the database engine.
|
|
|
|
|
thanks man thats good news, so do you have good link for newbies in Vb.net?"
Vuyiswa
|
|
|
|
|
Your best bet is to get a good book on Ado.Net. There is one by Sahil Malik(sp) that I would highly recommend. You should also google for Ado and Net and you will find plenty (try using www.searchdotnet.com instead of straight Google). Ado .Net is quite different from classic Ado and will require a little getting used to.
Aaron
_____________________________________________________________________
Our developers never release
code. Rather, it tends to escape, pillaging the countryside all around.
The Enlightenment Project (paraphrased comment)
Visit Me at GISDevCafe
|
|
|
|
|
Thank you very much Aaron
Vuyiswa
|
|
|
|
|
hi everybody..
anyone here knows how to use the BACKUP DATABASE statement in SQL
please tell me the correct syntax..
thanx in advance..
sandeep..
|
|
|
|
|
http://msdn2.microsoft.com/en-us/library/ms186865.aspx
|
|
|
|
|
hi all ,
problem as follows ,
i am using method called
public void ExecuteNonQuery(string sql) inside Database Class .functionality of this method is takes the sql query and execute by using sqlcommand class here each method for example AddSubscriber() method is for inserting values into Subscriber table and AddSubscriberLogin() this is for inserting values into Subscribelogin Table and we have a link table called Subscribrsubscribelogin()
wihich containes both Subscriber and Suscriberlogin Id's so Due to Some problem like internet the values are not stored into SubscriberSubscriberlogintable so clint canot see all logins of Subscribers i got an idea of using sqltransactions i used as follows
Code:
in subscriber class
class Subscriber
{
AddSubscriber()
{
string sql="";//insert query
ExecuteNonQuery(sql);
}
but this code is not working. i need like to add All methods like AddSubscribeLogin() And AddSubscriSubscribeLogin() After that only i have to Commit other wise rollback all table values.
}
public void ExecuteNonQuery(string sql)
{
SqlConnection connection = new SqlConnection(databaseConnection);
try
{
connection.Open();
transaction=connection.BeginTransaction();
SqlCommand command = new SqlCommand(sql, connection);
command.Transaction=transaction;
command.ExecuteNonQuery();
transaction.Commit();
}
catch(Exception ex)
{
transaction.Rollback();
}
finally
{
connection.Close();
}
}
but when i am calling in each method it is commit always can u suggest any solution please .
Thanks And Regards,
Kinnu
|
|
|
|
|
You want to execute both statements in the same try/catch, rioughly:
try
{
t=begintransaction
cmd1.t=t
cmd2.t=t
cmd1.execute
cmd2.execute
commit
}
catch
{
rollback
}
finally
{
close
}
|
|
|
|
|
my problem: i can't see tables or views of my database in Visual Studio when i export that database to another computer.
i make a sql database in SQL server management studio 2005, everything works fine, i make a connectionstring in my windows application project, also that works fine.
i export my database.. i import the database on another computer (with exactly same settings) but when i make a connectionstring, i don't see any tables or views in visual studio.. in SQL server management studio i do see the tables and vieuws
can anyone help me with this problem?
|
|
|
|
|
How do i set a column to Current time and date? SQl ofcause
Vuyiswa
|
|
|
|
|
UPDATE MyTable
SET SomeColumn = GETDATE()
|
|
|
|
|
Thank you Man
at first i used it like
update property
set archive_date = GETDATE() and ARCHIVE_USER_ID= '107'
where Num_key = '0063'
then i wasnt sure if there is something wrong with Getdate, so i removed the "And" and put a Comma and its working mam , i wanted to make sure that am using the correct function.
update property
set archive_date = GETDATE() ,ARCHIVE_USER_ID= '107'
where Num_key = '0063'
thanks![Rose | [Rose]](https://codeproject.global.ssl.fastly.net/script/Forums/Images/rose.gif)
Vuyiswa
|
|
|
|
|
UPDATE table SET column = GETDATE() WHERE condition
GETDATE() returns the current local date and time. If you're trying to perform calculations on the difference between two values, it may be better to use GETUTCDATE() (returns current UTC date and time) as that will not have the discontinuities of the local date and time if the local time zone uses daylight saving. It might also be better if you have users in multiple time zones and you want to display the time in the user's zone - it's easier to compute this from the UTC time.
Having said that, Windows (at least before Windows Vista) only stores the current time zone information. If the time zone data (e.g. daylight savings start date, end date) changes, the displayed local time for historical data, before the last change, could be incorrect if converting from UTC. You'll have to weigh these issues up against each other.
If you're trying to use the date and time for uniqueness, be aware that the system clock ticks in rather large intervals - normally around 15ms - and it's quite possible for multiple events to occur in one timer tick period.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
