From what I've read in the docs, ado.net currently supports opening sql
server ce tables in table-direct mode and performing Seek operations on them
(using SqlCeDataReader), but not on the full-blown sql server. Is this
(will this be) still true with ado.net 2.0 & sql server 2005?
Hello Everyone,
I'm trying to connect to Desktop SQL Server 2000 from Windows mobile PC Emulator (VS 2005). I need a direct connection using connection string to SQL Server 2000 through local wireless network without IIS.
Bellow is the code that I use. After executing this code I get an error on line Conn.Open(). Error says SQL Server does not exist or access denied.
SQL is un and running, and I can log in using SA username from the desktop. Even if I chance IP for another SQL server in my connection string I still get the same error. There is no firewall of any kind running.
Dim connectionSTR As String = "Persist Security Info=False;Integrated Security=False;Server=192.168.0.202,1433;initial catalog=MyDB;user id=sa;password=;"
Dim Conn As SqlConnection
Conn = New SqlConnection(connectionSTR)
Conn.Open()
If Conn.State = ConnectionState.Open Then
MessageBox.Show("Open")
End If
About my environment: SQL Server 2000 is running on Desktop PC with Windows XP SP2. Application which I need to connect to SQL Server is in Visual Studio 2005. I execute the application in Windows Mobile PC Emulator and try to connect to SQL Server from emulator.
Your advice and help is very appreciated
Thank you
Ika
Hi
I'm transfering legecy data to SQL Server.
Can anybody tell which method is best.
My boss wants cutome user interface to choose options and Need to update UI during processing.
Currently I'm using Direct INSERT Stmt (T-sql) Execution.
Can Anybody suggest the best.
Hello I want to seek and find the table named NewCustomer from the database so what would be the query ?
F16 LĂ?GHTĂ?NĂ?NNNG
I have an MS Access 2003 database from which I want to seek a specific record in a SQL Server Express 2005 database. I can connect to the table and get a recordcount but the recordset.supports (adseek) and recordset.Supports(adIndex) both return false. Any suggestions? Specific code I'm using is as follows:
Dim cnxn As ADODB.Connection
Dim strCnxn As String
Set cnxn = New ADODB.Connection
cnxn.Provider = "sqloledb"
strCnxn = "Data Source=SERVERSQLEXPRESS2005;Initial Catalog=RAMPSQL;Integrated Security='SSPI';"
cnxn.Open strCnxn
Set rsWSC = New ADODB.Recordset
rsWSC.CursorLocation = adUseServer
strSQL = "DailyData"
rsWSC.Open strSQL, cnxn, adOpenKeyset, adLockReadOnly, adCmdTableDirect
Thank you!
Hi All,
The script below may be use to find out what stored procedure uses a specified column from any of the table. This could be helpful in cases you have change a field name of a table and want to find out what stored procedure uses that column.
create procedure seek_sp_for_columns
@colname_para varchar(500)
as
begin
create table #temp_t
(
textcol varchar(1000)
)
create table #temp_t2
(
procname varchar(500)
)
declare @procname as varchar(500)
declare @found as int
declare @colname as varchar(500)
declare @valid_colname as int
select @valid_colname = count(id)
from syscolumns
where name = @colname_para
if (@valid_colname > 1)
begin
set @colname = '%' + @colname_para + '%'
declare sp_cursor cursor
for select name
from sysobjects
where xtype = 'P'
open sp_cursor
fetch next from sp_cursor
into @procname
while @@fetch_status = 0
begin
insert into #temp_t
exec sp_helptext @procname
set @found = 0
select @found = count(textcol)
from #temp_t
where textcol like @colname
if (@found > 0)
begin
insert #temp_t2 values(@procname)
end
delete #temp_t
fetch next from sp_cursor
into @procname
end
close sp_cursor
deallocate sp_cursor
select *
from #temp_t2
drop table #temp_t
drop table #temp_t2
end
else
begin
select 'Please verify column name'
end
end
I've been having some trouble getting a single-column "varchar(5)" field to reliably use a table seek instead of a table scan. The production table in this case contains 25 million rows. As impressive as it is to scan 25 million rows in 35 seconds, the query should run much faster.
Here's a partial table description:
CREATE TABLE [dbo].[Summaries_MO]
(
[SummaryId] [int] IDENTITY(1,1) NOT NULL,
[zipcode] [char](5) COLLATE Latin1_General_100_BIN2 NOT NULL,
[Golf] [bit] NULL,
[Homeowner] [bit] NULL,
[Code] .....
Typically, this table is accessed with a query that includes:
SELECT ...
FROM SummaryTable
WHERE ixZIP IN (SELECT ZipCode FROM @ZipCodesForMO)
This query insists on using a table scan. I've tried WITH (FORCESEEK) for example, but that just makes the query fail.
As I've investigated this issue I also tried:
SELECT * FROM Summaries WHERE ZipCode IN ('xxxxx', 'xxxxx', 'xxxxx')
When I run this query with 64 or fewer (actual, valid) ZIP codes, the query uses a table seek.But when I give it 65 or more ZIP codes it uses a table scan.
To summarize, the production query always uses a table scan, and when I specify 65 or more ZIP codes the query also uses a table scan. I'm wondering if the data type of the indexed column (Latin1_General_100_BIN2) is somehow the problem. I'll likely try converting the ZIP codes to an integer to see what happens.
Hi everyone,
I use sql 2005. What is the best practice for dealing with large table (more than million rows)? Table Partition, View or other?
Can you please give some suggestions? It will be very helpful if you can post some references or examples.
Thank you!
Hi,I am trying to write a method which needs to call a stored procedure and then needs to get the response of the stored procedure back to the variable i declared in the method. private string GetFromCode(string strWebVersionFromCode, string strWebVersionString) { //call stored procedure } strWebVersionFromCode = GetFromCode(strFromCode, "web_version"); // is the var which will store the response.how should I do this?Please assist.
View 3 Replies View RelatedHello,
I have a Sql Server 2005 database with many tables, each with millions of records within them.
They all have a Receive Date field, with records going back 10 years or so.
What would be the best way to partition it? I was thinking of partitioning them by years, but that would give me 10+ partitions -- would that be alot of overhead?
~Le
Hi,
Sorry if these is not the right forum for this:
I€™m using the data adapter method FillSchema works fine in MS Access but SQL Server Express Edition it doesn€™t get the primary key of the table. For example if I try to use the Find method of the dataset I get this error €śTable doesn't have a primary key€? but in MS Access these do not happen.
This is the code that I€™m using:
Dim sql As String = "SELECT admin.rents.id, admin.rents.transaction_id, admin.rents.item_id, admin.rents.description, admin.rents.due_date, admin.rents.return_date, "
sql &= "admin.rents.amount_to_pay, admin.rents.paid_amount, admin.rents.tax, admin.rents.type, admin.rents.deposit_id "
sql &= "FROM admin.rents INNER JOIN "
sql &= "admin.transactions ON admin.rents.transaction_id = admin.transactions.id "
sql &= "WHERE admin.transactions.customer_id=?; "
RentsDA = New OdbcDataAdapter(sql, cn)
With RentsDA.SelectCommand
.Parameters.Add(New OdbcParameter("@customer_id", OdbcType.Int)).Value = TextBox1.Text.Trim
End With
RentsDA.FillSchema(ds, SchemaType.Source, "Rents")
RentsDA.Fill(ds, "Rents")
It's because of the joins?
I need to configure something in the sql server?
What is the problem?
Dear All,
I'm working in eVC++3.0 with SQL CE2.0.
I've developed the application to fetch the data from the mdf file by passing the index field and using the command IRowsetIndex()::Seek.
Consider TableExample with Columns A,B,C,D and index Index1 with column A and Index2 with column B,C,D.
Its working fine when index1 is passed for the indexvalue.But when i pass index2 of the same table,its throwing the error as BADINDEX.
I'm passing the correct keyvalues in the Seek command also.
Kindly help me in this regard.
Regards,
Sasi.
Hi,
Can I use index seek and still keep 'like' 'or' functionality for the following statement?
--prepare test table
create table entity (Id int identity(1,1), FamName varchar(200), LastName varchar(200))
go
create clustered index entity_id on entity (id)
go
create nonclustered index entity_lastName on entity (lastName)
go
insert into entity select famName,FirstName from table
--test index
declare @LastName varchar(10)
set @LastName = 'a'
select FamName
from entity
where @LastName is null or LastName like @LastName --clustered index scan
I have a DataSet which I am holding in .NET and I would like to know the quickest way to get the DataSet in to a Table on SQL Server?
Any sample code would be great.
I want to pass the table name as a variable
Declare @tbl varchar(30)
set @tbl = 'DB.dbo.Set'
declare @sql nvarchar(200)
set @sql =
'select * from OPENXML(@xmldoc,'DB.dbo.Site',2) with' + @tbl + ''
exec(@sql)
Please help me with the correct syntax.
Mayank
i need to use inner join while updating..
im using only one table..
rajesh
Hi,
I just have a Dataset with my tables and thats it
I have a grid view with several datas on it
no problem to get the data or insert but as soon as I try to delete or update some records the local machine through the same error
Unable to find nongeneric method...
I've try to create an Update query into my table adapters but still not working with this one
Also, try to remove the original_{0} and got the same error...
Please help if anyone has a solution
Thanks
I have created a nolock view off a table to prevent locks. I have users coming in through MS Access that have switched their queries to run against the views. Now we are noticing that queries that used to run as a clustered index seek against the table are running as a clustered index scan against the table and performance in the queries has dropped.
Is there any way that the same query that hits the view instead of the table can be made to run faster or at least use the index seek?
Thanks,
Steve
Hi
I have a frontend Access and backend SQL
works fine but when i in my customer table seek in name
it takes very very long time . I use ODBC to my connection
is there anayway to this better.?
regards
alvin
I have a table that is corrupted and want to remove and add a backup version of it. How can i remove this table and add it again preserving all the foregin key restraints, permissions, dependencies, etc? Simply exporting and importing does not work. I could painfully remove the table and then painfully reconnect it again, recreating all the foreign key restraints, etc, by hand; but there has to be an easier way! What is the How-to?
Thank you!
Llyal
Is there a method to convert "Select * From Table" to "Select field1,field2,...fieldn From Table" ?
Thanks
Hi,
I am reading the book by Steven Roman:
"Access Database : Design and Programming" 3rd edition.
On page 120, Figure 7-2, he showed the the structure of the Jet Database Engine, which is very confusing to me.
According to this picture, I come to such an understanding:
1) VBA is just the hosting language for the Jet Database Engine;
2) Microsoft Visual Basic, Excel, Access, Word are hosting languages for VBA.
Isn't this weird? VB hosts VBA?
Thanks for any input!
Hi,I'm writing a telecom billing system where it is necessary to import csv delimited files into a sql server table.
1) The csv files to import differ in column arrangement i.e SQL server table arrangement = A, B, C, D whilst CDR = - , A, -, C, D2) The csv files are delimited via different characters i.e some are del.. by commas some by semi colons
I can import a CSV file from my application using a BULK INSERT query but only using CSV that resembles the structure of the table exactly
How can I pass a CSV to the table and state where I want each CSV column to fill a column in the SQL SERVER table
Below is how I am managing to pass a CDR to the SQL table that I have pre manufactured to resemble the structure of the SQL table Is there a way I can state which values from the CSV I want to pass to corresponding columns in the SQL table:
string conString = @"Provider=SQLOLEDB;Server=(local);Database=Billing;Uid=sa;Pwd=sa;";
for (int i = listBox1.Items.Count - 1; i >= 0; i--)
{
string strSQL;
OleDbConnection objConnection = null;
OleDbCommand objCmd = null;
objConnection = new OleDbConnection(conString);
string cdr = listBox1.GetItemText(listBox1.Items[i]);
strSQL = "BULK INSERT Campaign FROM '"+ cdr +"' WITH (FIELDTERMINATOR = ',', ROWTERMINATOR = '')";
OleDbDataReader objDataReader;
try
{
objConnection.Open();
objCmd = new OleDbCommand(strSQL, objConnection);
objCmd.ExecuteNonQuery();
objCmd = null;
objDataReader = null;
objConnection.Close();
listBox1.Items.Remove(listBox1.Items[i]);
}
catch (Exception ex)
{
objConnection.Close();
}
objCmd = null;
objConnection = null;
}
Regards
Simon
Anyone know if there is method that can insert all record from a tablein an MS Access 2000 database to a table in MS SQL Server 2000database by a SQL statement? (Therefore, I can execute the statementin my program)--Posted via http://dbforums.com
View 3 Replies View RelatedI am trying to use the ListChildren method to query the SSRS Catalog. I am using this as a dataset in a report. I'd like to see more columns, in particular the Description column, included in the dataset. Is there some way to tell ListChildren what columns should be included or is there some other method to obtain this information.
ListChildren returns ID, Name, Path, Type, CreationDate, ModifiedDate, CreatedBy, ModifiedBy, xmlns.
I'd prefer not to query the Catalog table directly.
Thanks
Tim
create table t1(a varchar(50) , b varchar(50))
create index i1 on t1(a)
create index i2 on t1(b)
create view v1
as
select * from t1 where isnull(a,b) = 'test'
select * from v1
The above SQL "select * from v1" is doing a table scan.
What do I do to make it perform an index seek ????
TIA
- ForXLDB
Is there any method to setup table replication between you sql server express?
View 3 Replies View RelatedWhat is the best approach to utilize a recursive CTE (Common Table Expression) to filter a resultset? The CTE function is used in all application queries to limit recursively the final resultset based on a provided hierarchical organization identifier. i.e. join from some point in the organization chart on down based on an organization id. I would prefer that the query could be run real-time. i.e. not having to persist the prediction portion of the results to a sql relational table and then limiting the persisted results based on the CTE function.
It appears that I can use a linked server to access the prediction queries directly from SQL Server (link below). I believe that I might also be able to deploy a CTE recursive function within a .net assembly to the Analysis Server but I doubt that recursive functionality is availalble without a linked SQL Server.
Executing prediction queries from the relational server
http://www.sqlserverdatamining.com/DMCommunity/TipsNTricks/3914.aspx
I have a database which will be generated via script. However there are some tables with default data that need to exist on the database.I was wondering if there could be a program that could generate inserts statments for the data in a table.I know about the import / export program that comes with SQL but it doesnt have an interface that lets you copy the insert script and values into an SQL file (or does it?)
View 7 Replies View RelatedI am getting error 0x80040E25 when I try to call seek after update on a Recordset opened as (adOpenStatic, adLockOptimistic, adCmdTableDirect)
9 - (13.250) - <2> - *** error in .DbRecordset.cpp, line 908
10 - (13.250) - <2> - ADO_ERRORS FOR pRs = 200a420, seek, err=-2147217883(80040e25)
11 - (13.250) - <2> - ADO_ERROR: E R R O R 1 of 1.
12 - (13.250) - <2> - ADO_ERROR: DESCRIPTION: All HROWs must be released before new ones can be obtained. [,,,,,].
13 - (13.250) - <2> - ADO_ERROR: NUMBER: 80040E25
14 - (13.250) - <2> - ADO_ERROR: NATIVE_ERROR: 0
15 - (13.250) - <2> - ADO_ERROR: SOURCE: Microsoft SQL Server 2005 Mobile Edition OLE DB Provider
I registered SQL Mobile 3.0 dlls using regsvr32.exe so now I can connect to SQLCE3.0 databases on desktop using plain ADO with such connection string _T("Provider=Microsoft.SQLSERVER.MOBILE.OLEDB.3.0; Data Source=") + name of the file
I have not asked this question before as it did not make sense -> there was no official SQLCE3.0 support on desktop. Now, since SQL CE is promiced to be supported on desktop as SQL/E I decided to ask.
Hey,
what is the difference between Table Scan und Index Scan?
I find no difitions in the internet
Finchen
Hi,
I want to know wht is a
TABLE SCAN
INDEX SCAN
INDEX SEEKand When they are used, Wht is the difference between all these.????
If you display the execution plan and run the following:SET STATISTICS IO ONgoSELECT ProductID, SupplierIDFROM ProductsWHERE SupplierID = 1I don't understand how come there is noBookmark Lookup operation happening to get theProductID?I only see an Index Seek happening on SupplierID.There is no composite index SupplierID + ProductIDso what am I not understanding here?Thank you
View 3 Replies View Related