Connecting To A SQLServerCE Database Through Vbscript
Apr 24, 2007Is it possible to talk to a SQLServerCE database from a vbs script? If so, does anyone have an example?
Thanks,
Mike
ADVERTISEMENT
Handling SQLServerCE DataBase By Windows Application ?
Oct 20, 2006Hi
i have the following problem :
i attempted to connect with a SQLServerCE DataBase
to Insert and update its rows, but i noticed that i want the reference :
System.Data.SqlServerCe
i went to (Add references) but i didn't find it ..
what should i do to break this problem ?
please help me !
Performance SqlServerCE 3.1 Vs. SqlServerCE 3.5
Mar 18, 2008Hi,
we just migrated to SqlServerMobile 3.5. My first experience is that Version 3.5 is much slower than 3.1.
I upgraded and compacted an existing Database. Is it recommended to create a new database, instead?
Has anybody else the feeling that Version 3.5 is slower?
Are there any settings to speed up the database.
Many thanks,
Stefan
Could Not Load Type 'System.Data.SqlServerCe.SqlCeDataAdapter' From Assembly 'System.Data.SqlServerCe'
May 10, 2006
Hi,
I am trying to make a mobile application work, but I get the following error. The operating system on Pocket PC is Microsoft® Windows Mobile„¢ 2003 Second Edition. Any ideas? Thanks in advance.
The followings are the error and my codes:
System.TypeLoadException was unhandled
Message="Could not load type 'System.Data.SqlServerCe.SqlCeDataAdapter' from assembly 'System.Data.SqlServerCe, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845DCD8080CC91'."
StackTrace:
at SQLMobile.Form1.Form1_Load()
at System.Windows.Forms.Form.OnLoad()
at System.Windows.Forms.Form._SetVisibleNotify()
at System.Windows.Forms.Control.set_Visible()
at System.Windows.Forms.Application.Run()
at SQLMobile.Form1.Main()
Codes:
Private Sub FillGrid()
Dim filename As New String _
("Program FilesSQLMobilesqlmobile.sdf")
Dim conn As New SqlCeConnection("Data Source=" + filename)
Dim selectCmd As SqlCeCommand = conn.CreateCommand()
selectCmd.CommandText = "select Destination from flightdata"
Dim adp As New SqlCeDataAdapter(selectCmd)
Dim ds As New DataSet()
adp.Fill(ds)
DataGrid1.DataSource = ds
End sub
Right Code Statements Of SqlConnection && ConnectionString For Connecting A Database In Database Explorer Of VB 2005 Express?
Feb 14, 2008Hi all,
In the VB 2005 Express, I can get the SqlConnection and ConnectionString of a Database "shcDB" in the Object Explorer of SQL Server Management Studio Express (SSMSE) by the following set of code:
///--CallshcSpAdoNetVB2005.vb--////
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Public Class Form1
Public Sub InsertNewFriend()
Dim connectionString As String = "Data Source=.SQLEXPRESS;Initial Catalog=shcDB;Integrated Security=SSPI;"
Dim connection As SqlConnection = New SqlConnection(connectionString)
Try
connection.Open()
Dim command As SqlCommand = New SqlCommand("sp_insertNewRecord", connection)
command.CommandType = CommandType.StoredProcedure
.......................................
etc.
///////////////////////////////////////////////////////
If the Database "shcDB" and the Stored Procedure "sp_inertNewRecord" are in the Database Explorer of VB 2005 Express, I plan to use "Data Source=local" in the following code statements to get the SqlConnection and ConnectionString:
.........................
........................
Dim connectionString As String = "Data Source=local;Initial Catalog=shcDB;Integrated Security=SSPI;"
Dim connection As SqlConnection = New SqlConnection(connectionString)
Try
connection.Open()
Dim command As SqlCommand = New SqlCommand("sp_insertNewRecord", connection)
command.CommandType = CommandType.StoredProcedure
........................
etc.
Is the "Data Source=local" statement right for this case? If not, what is the right code statement for my case?
Please help and advise.
Thanks,
Scott Chang
SqlServerCe.dll
Oct 4, 2006Hello, Iam devloping a product for a mobile unit using Windows mobile database. (*.sdf)
Is it possible from other form applictions, such as ordernary Windows forms ?
Have a wonderful day!
SqlServerCE Vs VB.Net
Apr 11, 2007hi,
i was facing problem in sqlceserver connection, the coding i had code as below, it's return an error message "Procedure Call or Argument is not valid". i was study this issue for 2 days, but i am still can't found out the cause of issue, can anyone please point out the mistake i had made? Thank.
Module Module1
Public Const local_DatabaseFile As String = "My Documents esting.sdf"
Public conn As SqlCeConnection
Dim cmd As SqlCeCommand
Public myReader As SqlCeDataReader
Public mySelectQuery As String
-----------------------------------------------------------------------
Form1
Function Check_Code()
Try
' On Error Resume Next
builtConnStr()
If conn.State = ConnectionState.Open Then
conn.Close()
End If
mySelectQuery = "SELECT * FROM Product " & _
"WHERE Item_Code = '" + txtCode.Text + "' "
conn.Open()
Dim myCommand As New SqlCeCommand(mySelectQuery, conn)
myReader = myCommand.ExecuteReader()
' Always call Read before accessing data.
If myReader.Read() Then
txtName.Text = myReader.GetString(1)
End If
myReader.Close()
conn.Close()
Catch ex As Exception
MsgBox(Err.Description)
End Try
End Function
------------------------------------------------------------------------------
Public Sub builtConnStr()
Try
Dim local_ConnString As String
local_ConnString = "Data Source= " & local_DatabaseFile
If conn Is Nothing Then
conn = New SqlCeConnection(local_ConnString)
conn.Open()
cmd = conn.CreateCommand()
End If
Catch ex As Exception
MsgBox(Err.Description)
End Try
End Sub
Update In SqlServerCE 2.0
Jan 29, 2004Hello,
I'have this SyBase and SQL Server example update query:
UPDATE Table1 SET
Table1.Col1 = Table2.Col1 + Table2.Col2
Table1.Col2 = -1
FROM Table2
WHERE Table1.Id = Table2.Id
If i try to execute this query under SqlServerCE 2.0 i get this error message:
Native Error:(25501)
Description: There was an error parsing the query.
How can i write this query for running it under SqlServerCE 2.0?
Many Thanks in advance!!!
Mauro
Deployment Of Sqlserverce.dll
Sep 4, 2007I registered to redistribute the runtime for SQL Server Compact edition.
I have a desktop application which uses SDF files as the database.
In VS 2008 - when I set System.Data.SqlServerCe configured to Copy Local as TRUE - sqlserverce.dll does not get inserted to my bin directory. I am assuming if I manually put the DLL file into my bin directory (and deploy it) - the DLL file will automatically be used by my app?
License SqlServerCe
Mar 6, 2008 Does anybody know if sqlserverce's license is freeware or shareware??
thanks a lot!
Connecting To A Database Twice Fails Because Database Is Uses By An Other Process
Apr 23, 2008Hi all,I come up with a problem aleady discussed in some posts especially in the post http://forums.asp.net/t/1235761.aspx. but I got in not finally solved.The main problem is connecting to a database twice which causes errors. So I think this might be the right place to ask my questions. Here ist the problem in short:I'm using VWD on XP-Professional with SQL-Server Express and Reporting Services Express. All with Windwos Integrated Security and User Instance (SQLExpress). In my web-application I had for all Datasources the connect string:Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|mydatabase.mdf;Integrated Security=True;User Instance=True This works fine so far. I also have Reports designed with the Report designer using the same connection string. I can preview the reports and I deployd thre reports successfully. I want to call these report using a report viewer web control.Now the problem:when I call the remote remote report from my web-application, the database cannot be opened because an other process is using the database (see post mentioned above). It seems that this is a problem with attaching the database twice: from the sql-server and from the report-server.So I used now a new connection string without "attaching" the database (in vwd and in report designer):Data Source=.SQLExpress;Initial Catalog=C:inetpubwwwrootmyappAPP_DATAmydatabase;Integrated Security=True Now everything works...but ... only on my machine. On the production machine this does not work. I wonder anyway, why its working on my machine, because I never "attach" the database (there is no connection string with AttachDbFilename and I have not opend the developer and have not opened the sql management studio).On the production machine the concurrent (SQL-Server and Report-Server) connection to database does not work with either connection strings, no matter if i attach the database with sql server manager or not).This all drives me crazy for days now. All I want is to use SQL-server and Reporting- Services with its nice features and not repairing my tools. I thought I have a standard Installation, nothing special, but the standard obviously makes problems.Here finally my question:Does this environment ( web-application with remote reports) with Express edition cause normally no problems?Can I "attach" a database only once? How should I connect with my web application that uses the sql-server connections and also the report-server-connections (attaching twice does not work).What connection strings to use and when (AttachDbFilename or Initial Catalog).Are these problems specific to the Express Editions? I thank you in advance for any help Dieter
View 2 Replies View RelatedDTS And VBScript
Dec 21, 2004I have an application written in C#, which allows the user to load their destination schema (in xml format) and writer their transformation expressions either in VBScript or in T-SQL. It uses DTS to do the ETL job.
Everything works great except with one problem. If there is a hypen ("-") in any of the destination attributes, the transformation fails with syntax error. It happens only with VBScript.
Here is my transformation:
"Function Main()
DTSDestination("Field-1") = #01/01/2004#
End Function"
If I replace the hypen with underscore, it works without any problem.
Does anyone have any idea about this problem?
Replication Monitor SqlServerCE
Aug 16, 2006I have developed a smart device application using SqlServerCE that replicates data to the backend server. I need to provide to the user a status of this replication process. I read about the "MergeSubscriberMonitor" in SQL Server but can't find it in SqlServerCe. Is this possible? Can someone please point me in the right direction?
View 6 Replies View RelatedSQLServerCe Engine Error
Sep 21, 2007I have successfully converted a MSVS 2005 Winforms Application into the MSVS 2008 beta 2. I am however having trouble getting the winmobile data tables to fill. The ODBC Paradox tables fill fine. When I converted the application I had to re create the win mobile datasource for the project. So I used the datasource wizard in 2008, it opened the winmobile5 database and told me that I would need to convert the database to 3.5. I let it overwrite the existing database and it created a new .xsd file for me for the project. When I try to load data into the database tables
this.proceduresTableAdapter.Fill(this.fB7MobileDBDataSet.Procedures);
I get the native error 28609
You are trying to access an older version of a SQL Server Compact 3.5 database. If this is a SQL Server CE 1.0 or 2.0 database, run upgrade.exe. If this is a SQL Server Compact 3.5 database, run Compact/Repair.
I have run Compact but not repair, and still get the same error.
1. What could be left in the project that ismaking it expect to open the 3.0 database instead of the 3.5 that was converted in the datasource wizard?(If I substitute an older database file I get the native error 25010 Invalid File Name. Check the database filename. )
2. Is there a way after you convert a project to then add a datasource to the project that would use the same xsd file and datasetDesigner.cs from before the conversion ?
Thanks Jon Stroh
System.Data.SqlServerCe
Aug 8, 2007Sorry being a little vague but if you can point me in the right direction.
I have just created a new VB €“ Windows Mobile 5.0 pocket PC €“ Device Application. (which will be connected to a PDA)
Do i have to use System.Data.SqlServerCe or System.Data.SqlClient to connect to my db.
Using Up Memory With Insert On SQLServerCE
Oct 23, 2007I have an open connection object in my mobile app. I noticed that whenever I use this to do Insert into my DB the memory in use by the application increase everytime and does not get released until I close down the app (or if I close and dispose the connection object = which I dont want to do really). Just 40 inserts will take up serveral MB of memory.
Any ideas on this?
Using version 3.0.3600.0
Some sample code (cnn is a public variable SqlCeConnetion):
Dim cmdText As String = "INSERT INTO MyTable (theName, theValue) VALUES ( 'Name','Value')"
Try
Dim cmd As SqlCeCommand = New SqlCeCommand
cmd.Connection = cnn
cmd.CommandText = cmdText
cmd.ExecuteNonQuery()
Catch
End Try
cmdText = "SELECT @@Identity"
Try
Dim cmd As SqlCeCommand = New SqlCeCommand
cmd.Connection = objCnn
cmd.CommandText = strCmd
.Id = cmd.ExecuteScalar
Catch
End Try
Triggers / VBScript
Aug 25, 2004How do i get my server to run a vbscript (yet to be created but i guess it will be in a DTS package???) when an insert runs on a table?
We need to analyse the browser versions hitting our intranet and catch the ones that are too old (earlier than IE5.5). I have the asp inserting a row with the IP address of the browser but i need to run a command line query to get the machine name from DNS (nslookup 111.222.333.444). This nslookup can be run from a vbscript.
Do i need to used a stored procedure in the process somewhere?
any thoughts welcomed
Need DTS/VBScript Guru
Nov 9, 2005I am trying to write a DTS package at work that uses a loop to fire a query off against a different server/database at each loop iteration. Prior to the execute SQL task , I use ActiveX (VBScript) to change the Catalog and DataSource of the connection (created by drag and drop if icon). My Execute SQL task selects @@servername and getdate() just as a dummy query to make sure I am actually pointed at a different server.
While testing the properties of the connection using global variables and msgbox shows that the database (catalog) and server (data source) ip address is being changed, the result returned is always from the first server. I feel like I need to disconnect the connection object, change the parameters and then reconnect to the new server but there doesn't appear to be anyway to do this.
Anyone out there able to successfully change a connection object inthis manner??
Calling Vbscript From SP
Apr 21, 2004I am trying to call a VB script file with the following command,
xp_cmdshell 'cscript c:file.vbs'
But its giving the following output -
Microsoft (R) Windows Script Host Version 5.1 for Windows
Copyright (C) Microsoft Corporation 1996-1999. All rights reserved.
NULL
Input Error: Can not find script file "c:file.vbs".
NULL
------------
If i changed the command to
xp_cmdshell 'cscript c:file.vbs'
it gives the folowing error -
Input Error: Can not find script file "C:WINNTsystem32file.vbs".
Actually my main intention is to call a url from the sql server where '
i can pass some value. Plz let me know if its the right step and whts
going wrong.
Thanks & regards,
-dkoushik
VBscript Sleep
Apr 28, 2004im writing a DTS package and i have am using vbscript to check if a file exists on an ftp server.
I can't figure out how to make my process sleep b/t checks on if the file exists.
in vb u can just
Declare Sub Sleep Lib "kernel32.dll" (ByVal dwMilliseconds As Long)
but i cant seem to find a counterpart in vbscript.
thanks in advance,
pete
Disabling Job Using VBScript
Jun 6, 2008I have an SQL job that runs a .vbs script. It has the following code:
Cmd2.ActiveConnection = Conn2
Cmd2.CommandText = "USE msdb EXEC sp_update_job @job_name = 'Email Download',@enabled = 0"
Cmd2.CommandType = 1
Cmd2.Execute()
It's not disabling the job. If I execute in Query Analyzer, it works. I'm thinking it might be a permissions issue. Any suggestions?
TIA
Using SQL -DMO And Vbscript Against SQL Server
Jun 12, 2003I'm trying to run a TQSL script against a SQL Server 2000 database using SQL-DMO and Vbscript. The output of this query would be use to populate a table in another SQL Server database on a different server. Has anyone use SQL-DMO to connect SQL Server and run a t-sql script against a database and use the output to populate a table on a different server? Any informatiom would be helpfull.
Regards
Magictech
Problem With System.Data.SqlServerCe.dll
Oct 11, 2006I believe I've encountered a compatability problems with Visual Studion CF 2, specifically with the System.Data.SQLServerCE reference when trying to run (in debug mode - Pocket PC 2003 emulation). The environment is configured as follows:
Visual Studio 2005 - Compact Framework 2.0.50727
Microsoft SQL Server 2005 Mobile Edition
The (VB) project includes the following references:
System.Data.dll V2.0.000 from Program FilesMicrosoft Visual Studio 8Smart DevicesSDKCompact Framework2.0V2.0WindowsCE
System.Data.SqlServerCE.dll V3.0.5214.0 from Program FilesMicrosoft SQL Server Mobile EditionDeviceMobileV3.0
The IDE does not sense any problems with the following code until I try to run it in debug mode using the Pocket PC 2003 emulator. The code :
Imports System
Imports System.IO
Imports System.text
Imports System.Data
Imports System.Data.SqlServerCe
Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
MsgBox("Will Test SQL")
Call testSQL()
End Sub
Private Sub testSQL()
Dim strConn As String = "Data Source = Test.sdf; Password = <password>"
Dim CEEngine As New SqlCeEngine(strConn)
CEEngine.CreateDatabase()
CEEngine.Dispose()
Dim Conn As SqlCeConnection = Nothing
Conn = New SqlCeConnection(strConn)
Conn.Open()
Dim cmd As SqlCeCommand = Conn.CreateCommand
cmd.CommandText = "CREATE TABLE MyTable (col1 int, col2 ntext)"
cmd.ExecuteNonQuery()
Conn.Close()
Exit Sub
ErrHandler:
MsgBox(Err.Number.ToString)
MsgBox(Err.Description.ToString)
End Sub
In the debugger, the program terminates at Dim CEEngine As New SqlCeEngine(strConn). The message displayed is
Can't find PInvode DLL 'sqlceme30.dll'
I've tried referencing a different version of System.Data.SqlServerCE.dll (from Program FilesMicrosoft SQL Server90ToolsBinnVSShellCommon7IDE but the program will not even deploy to the emulator with this reference.
I encounter a Deployment/and or registration failed wit error : 0x8973190e - There is not enough space on the disk.
What versions of the following should be used?
System.dll
System.data.dll
System.data.sqlserverce.dll
VB.Net CF App Falls Over On SqlServerCe ExecuteNonQuery Under WM5 But Not PC2003
Jun 1, 2006I have a VB.net 2005 application that uses both System.Data.SqlClient and System.Data.SqlServerCe which has worked on PC2003 handhelds but falls over in Windows Mobile 5. It seems to be when I have used a SqlClient connection and then use ExecuteNonQuery on the local sdf file - the application just bombs out - no error message, nothing.
Weirdly, if I am connected to a CE database through Query Analyzer 3.0 at the same time as running this application, everything works fine. Do I need to 'initialize' something that Query Analyzer seems to be doing in order to make this work?
Most grateful for any ideas.
Thanks
Chris
System.Data.SqlServerCe Versions
Oct 2, 2007
Hello,
I've inherited a .NET CF 2.0 application at work that uses SQL CE, and as I'm digging into the build process, I'm getting confused about which version of SQL CE that is being used.
The project references System.Data.SqlServerCe (no hint path, just from wherever VS 2005 has it). When I look at the assembly in my project, the version shows up as 3.0.3600.0, but when I browse to the DLL's location on my hard drive (C:Program Files (x86)Microsoft Visual Studio 8SmartDevicesSDK\SQL ServerMobilev3.0System.Data.SqlServerCe.dll), right click the DLL and check the properties, the version shows up as 3.0.3600.0.
In our installation package, we install three SQL CE Cabs:
sqlce30.dev.ENU.ppc.wce5.armv4i.CAB
sqlce30.ppc.wce5.armv4i.CAB
sqlce30.repl.ppc.wce5.armv4i.CAB
When installed on the device, the Remove Programs dialog shows as "Microsoft SQL Mobile 2005..." is installed. We keep the cabs in a seperate location so they are not just pulled from anywhere on the hard drive.
However, when I install the same CAB files located at:
C:Program Files (x86)Microsoft Visual Studio 8SmartDevicesSDKSQL ServerMobilev3.0wce500armv4i, the device shows "Microsoft SQL Mobile 2005 Compa...." is installed.
In my Add References dialog, System.Data.SqlServerCe has version 3.0.3600.0, runtime v2.0.50727
Some questions:
1. Is the version 3.0.3600.0 the same between 3.0 and 3.1?
2. Given a System.Data.SqlServerCe DLL, how can I tell if it is the 3.0 or 3.1 version?
3. When upgrading my project to use 3.1, is it enough simply to upgrade my build environment? Or should I need to change the references in my project? (It appears that the 3.1 DLLs have been placed into the default locations where VS 2005 is pulling them from). I'm using VS 2005 SP1 on Vista x64.
Thanks
How To Schedule Job To Run Vbscript File
Aug 30, 2005Hi All,I wrote a vbscript file which copies one table records to another database table. I wrote error handling to send an email if error occurs when copying records from one table to another. I want to schedule a sql job and give this file to execute. Could anyone plz tell me how to schedule sql job which runs vbscript file. i selected ActiveXScript and choose vbscript and gave path. I'm not understanding what the command should be given to run vbscript file. The result of giving path is job is failing.Any help would be appreciated...plzzzzzzzthankscarol
View 1 Replies View RelatedIs There AT-SQL Function Like VBScript STRING() ?
Aug 11, 2000This function is useful for fixed width output. E.g.
String(expr,10-len(expr),'0') will give you a number with leading zeros so it is always 10 characters long.
Thanks in advance for your time.
Auto Increment In VBScript?
Aug 31, 2006Ok, I've been charged with the task of setting up an IVR(interactive voice response) system for remote data entry. It was all going great till I got to the utility's Database connector. It wouldn't work, but I saw that the program also supported VBScript, great! I know VBScript.
Simple task, setup a simple flow where data can be entered. I setup a DSN for the access database(not quite MS SQL, but close enough) I'd created. It gave me a syntax error when I tried to use '' in an auto increment field. I think the exact query was:
"INSERT INTO test_table VALUES('',$RV[Store Number],'$RV[Objective]','question1','question2',question3');"
The database setup was:
id(AutoNumber),store_number(number),objective(text),q1(text),q2(text),q3(text)
Changed the auto number in the query to a 0 and it worked fine, but of course it was only to test if it would work. It would create a duplicate row if it went further than the first row.
I don't understand what my syntax error was. The $RV[*] are variables retrieved by procedures in the IVR scripting program. Need this for work so I can setup a demo of the system to present to my boss' bosses' bosses
Stored Procedure From VBScript
Oct 7, 2004Hello,
I am trying to call a Stored Procedure from VBScript. It worked fine the first time but each time after it doesn't seem to be returning any data is there any special command I need to send?
Thanks in Advance,
John
DTS / VBScript Date Format
Mar 31, 2008Hey guys,
I have a database import that I'm doing and I'm using DTS/VBscript to manipulate data.
I created a varchar field to import a date in the following format 030906.
Does anyone have a suggestion on making it 03/09/06?
Or just the best way to handle this type of data
thanks,
Jonathan
VBScript Error In SQL2000
Mar 28, 2004hi
I have a VBScript converted from an AccessDB Macro to VB.
Option Compare Database
'------------------------------------------------------------
' Macro2_Export2Text5
'
'------------------------------------------------------------
Function Macro2_Export2Text5()
On Error GoTo Macro2_Export2Text5_Err
DoCmd.TransferText acExportDelim, "", "MTHCrosstabCountingUnits", "c:IMSMthCountingUnit.txt", True, ""
DoCmd.TransferText acExportDelim, "", "MTHCrosstabValue", "c:IMSMTHValue.txt", True, ""
DoCmd.TransferText acExportDelim, "", "MATCrosstabValue", "c:IMSMatValue.txt", True, ""
DoCmd.TransferText acExportDelim, "", "MATCrosstabCountingUnits", "c:IMSMATCountingUnit.txt", True, ""
Macro2_Export2Text5_Exit:
Exit Function
Macro2_Export2Text5_Err:
MsgBox Error$
Resume Macro2_Export2Text5_Exit
End Function
What I am trying to do is instead of Opening the Access application and run the macro manually, i converted the macro to VB and created an ActiveX Script Task package in SQL2000 and Copy and Paste the VB Code to ActiveX Script Task. But it didn't work. Is there a work around for this?
Many thanks in advance.
How To Run Stored Proc From Vbscript
Oct 1, 2013I have a stored procedure that I run in Management studio just fine. I need to provide 2 integer variables @id and @version.How do I run the stored proc from vbscript and reading the output?
SP:
USE [ProjectBudget]
GO
DECLARE @return_value int,
@name varchar(500),
@maconomy varchar(100),
@creator varchar(100),
@created datetime,
@active bit
[code]....
Compare Two Databases Using Vbscript
Sep 9, 2004How to compare db2 and sql7 database using VB script
View 2 Replies View Related