|
That would work also. Just create the procedure and schedule a batch job which will use sqlcmd and execute the procedure. There's a bit more work since you need to take care of logging, alarming etc. but it's not complicated.
|
|
|
|
|
Hello,
I store a lot of data in a table. An ordered subset of these data are to be displayed on a remote application. In order to minimize the network load and display latency I'd like to retrieve only the row that are really displayed. The remote application will request additional rows when the user scroll.
First I though I could use views and cursor to achieve this. It works well except when I need to display data add after the view creation. In this case I have to recreate the view which is quite long.
I'm connected through ODBC to SQL Server or MySQL. Is there an ODBC way to retrieve result set rows on demand (I don't want all the rows to be retrieved by the ODBC driver) ? So maybe I could use a simple SELECT request and get only the rows I need (assuming the SELECT will be fast enough).
Thanks
|
|
|
|
|
You could look at SQLFetchScroll[^].
The downside is that the cursor must be open all the time, which can lead to several problems. If you want to close the cursor between calls then you have ty dynamically modify the sql statement so that you define new "offset" every time.
|
|
|
|
|
What is the possible problems caused by keeping the cursor open all the time ?
By "offset" do you mean I need to memorize the last position and use it again the next call as a "bookmark" for the SQLFetchScroll method ?
|
|
|
|
|
20c wrote: What is the possible problems caused by keeping the cursor open all the time
Some issues that come immediately to my mind:
- the connection to the db must be open all the time so it's vulnerable to any possible network failures or degraded performance may cause timeouts thus closing the connection (don't know your environment so this may not be an issue for you)
- When you hold the cursor open you use several resources on the database side since the database must hold the result set for you.
- possible locking problems if the query holds S-locks, also degraded performance for the same reason
20c wrote: By "offset" do you mean I need to memorize the last position and use it again the next call as a "bookmark" for the SQLFetchScroll method
Not actually the position as a row, since it may vary between calls. For example:
- you have 100 customers ordered by name
- you fetch in pages of 10 rows- you're on page 50-59
- the next page would be customers 60-69.
But what happens if let's say 20 customers have been added and they all start with letter A? If you remember the row position you would actually get the previous page where you've already been if you go forward.
What I meant is that you remember for example the key and the values in the ordering columns. If the customer has for example CustomerID as a primary key, next time when fetching you would query for customers in the same order but only rows starting the last customer you fetched previously.
|
|
|
|
|
Hi All,
I have VS2008 professional edition, I want to add a connection to SQL server compact edition, but in the Server explorer, in Data Sources, I don't see Sql Server Compact edition. Is there any VS2008 Setting I have to change?
With VS2008 Express edition, everything is working fine, I could able to access .sdf files.
I know that with VS2008, SSCE is shipped and available right away, but it is not working with me.
Thanks in advance for your help,
Veena
|
|
|
|
|
Did you add the local database (from Add/New Item...) into your project? After that it should automatically be listed in the server explorer.
|
|
|
|
|
Thanks Mika,
I added local database into my project. I got following warning
"The data provider required to connect to the local data file could not be found. The file will be added to the project but the typed DataSet associated with the file will not be generated."
I checked in server explorer, no SSCE is listed. When I tried to access the newly added .sdf local database, I got an error "Operation could not be completed. Unspecified error.". This is the case in VS2008 professional edition.
For VS2008 express edition , everything is working fine.
Veena
|
|
|
|
|
Sounds like you have an installation issue. SqlCe library isn't installed for some reason.
Visit: SQL Server Compact 3.5[^] and download the latest version and try to install that. Hope it helps.
|
|
|
|
|
hellow can any one help me in getting week number (sunday - sunday) as week in sql server query.
i am using this but its not giving as sunday to sunday
SELECT StartTime,DATEPART(DAY, StartTime - 1) / 7 + 1 AS theWeekWithinMonth from TT where cardid = '3344'
|
|
|
|
|
Do you mean week numbers as 1-52 (or 53). In that case try:
SELECT DATEPART(WEEK, StartTime)...
If you mean day numbers 1-7 starting from sunday try:
SELECT DATEPART(WEEKDAY, StartTime)...
modified on Thursday, January 8, 2009 7:55 AM
|
|
|
|
|
Kindly help me out.performance of my sql server degrades after power failure.
i ran dbcc checkdb command , but it shows no error.
|
|
|
|
|
This could be a normal situation. When the SQL Server is shut down, it's memory manager is destroyed so there are no more block in the buffer. It takes some time for the database to gather a good mru list of necessary buffers. So basically before the power failure many operations were done in-memory, but now the data must be fetched from the disk. Also what takes time is that if your memory settings are dynamic, SQL Server must increase memory usage as needed. Just make sure that the operating system isn't swapping.
You can use performance manager to monitor if this is the case (since you're not facing any errors). You can also use activity monitor to see if this is disk I/O related.
Also check that there are no problems in errorlog.
|
|
|
|
|
Good Evening,
I'm new to access besides what i did in highschool  , My issue is i have to crosstab's in which i want to make a report out of, to look something like this.
Report 1.
Heading.query1 heading.query1 heading.query1 heading.fromquery2 headingfromquery2
name value value value value
name value value value value
name value value value value
My issues is, in design view, i can create a report only with the first query, If i choose the data itms from the 2nd query i lose the first items i already put, with errors saying the item does not exist.
Is there an easyerway?
I appreciate the assistance,
Thanks,
|
|
|
|
|
This possibly is a join but based on the info it's hard to say. Could you give few example rows from the tables and an example what the report would look like with that data.
|
|
|
|
|
Someone else asked this quesion, I can't remember where to reply to his post. But, since I wrote the script already, wanted to post to this good site. The code is written in biterscripting. (http://www.biterscripting.com)
I would like to split a csv file into secondary csv files on a key field. Ex :
all.csv (1st column is the key field for splitting action)
P1;X;10
P1;Y;5
P2;Z;10
P2;A;5
P3;B;30
P3;C;60
P1.csv P2.csv P3.csv
P1;X;10 P2;Z;10 P3;B;30
P1;Y;5 P2;A;5 P3;B;60
Here is the script.
Patrick
# START OF SCRIPT
var str saved_wsep
set $saved_wsep = $wsep
set $wsep=";"
var str output_file
var str input, output, row_str, c1, prev_c1 # c1 for column 1
cat "all.csv" >$input
var int row, rows
set $rows = { len $input }
set $row = 1
while ($row <= $rows)
do
lex -p $row $input >$row_str
wex -p "1" $row_str >$c1
if ($c1 <> $prev_c1)
do
# new output file begins. Is there anything in the previous file.
if ($prev_c1 <> "")
do
echo $output > { echo $prev_c1 ".csv" }
done
endif
set $prev_c1 = $c1
done
endif
set $output = $output + $row_str + "\n"
set $row = $row + 1
done
echo $output > { echo $prev_c1 ".csv" }
set $wsep = $saved_wsep
|
|
|
|
|
Instead of posting this to a forum, you could write an article of it. Articles are the intended way to share solutions and forums for questions.
|
|
|
|
|
Hi friends,
I am working with SQLServer 2005 database (Collation set as Chinese) using odbc api SQLExecDirect and SQLExecute.
Some of my SELECT queries containing some chinese characters works fine with SQLExecute and SQLExecDirectW but NOT with SQLExecDirect. My gut feeling is SQLExecute is converting the query into unicode before it executes. Is this assumption right ?
thank you
cheers
Varghese Paul
|
|
|
|
|
This would depend on the driver and it's implementation. One problem you may encounter is: http://support.microsoft.com/kb/271198[^].
However, if SQLExecDirectW is working, why not use it instead of SQLExecDirect.
|
|
|
|
|
Hi to all,
I have several tables in which all the column names are same.
for example I have 4 tables.
1.glassware
2.barware
3.utensils
In all the three tables I have same columns.Suppose one of the column is "product_id".
Now in my UI I am having a search control based on product_id.
How I will search in all the three tables.
I dont know much abt database.Please help me out.
I used:"select product_id, sh_desc,image,price from Glassware,Barware,Utensils where product_id='"+textbox1.text+"'"
Thanks in advance.
|
|
|
|
|
Hi,
you could use a union to get the rows matching in all three tables:
<br />
SELECT Glassware.product_id, Glassware.sh_desc, glassware.image, glassware.price from glassware where glassware.product_id = '" + textbox1.text + "'<br />
<br />
UNION<br />
<br />
SELECT Barware.product_id, Barware.sh_desc, Barware.image, Barware.price from Barware where barware.product_id = '" + textbox1.text + "'<br />
<br />
UNION<br />
<br />
SELECT Utensils .product_id, Utensils .sh_desc, Utensils .image, Utensils .price from Utensils where Utensils .product_id = '" + textbox1.text + "'<br />
<br />
By the way, please don't attach the value textbox1.text to the sql query directly. Use parameters to do this, because it could be a possible security hole (sql injection).
If all three tables have the same columns why don't you use one table with an additional column, saying whether the row is a glassware, barware or utensils? Makes your queries much simpler.
Hope this helps.
Regards
Sebastian
|
|
|
|
|
Hi,
Thanks a lot.It worked.
I will rather use one table.
cheers,
nirpendra
|
|
|
|
|
Is it possible connect to a remote sql server for test a program on the net or know?
|
|
|
|
|
Programmer. wrote: Is it possible connect to a remote sql server for test a program on the net or know?
Yes, it it possible until you have server IP, Username, Password and Database
|
|
|
|
|
If there are firewalls between the client and the server (and most likely there is), you must have an open port for the conversation.
It may be that you have to have a secure connection to the firewall first and after that the port the sql server is listening is seen as open. After that, it's just a normal connection.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
