Unable To Execute Below Mentioned Queries Against SqlCe 3.1
Apr 10, 2007
Hi,
When I run the following queries they are working fine with MS Access but not working with SqlCe 3.1 on my desk top.
I am using Sql Server 2005 management studio to connect to SqlCe (.sdf) file and running the queries with in that.
Please guide me what is wrong here:
1)
select count(*) FROM (SELECT DISTINCT TIDVALUE FROM cdirmen)
Error Message:
Major Error 0x80040E14, Minor Error 25501
> select count(*) FROM (SELECT DISTINCT TIDVALUE FROM cdirmen)
There was an error parsing the query. [ Token line number = 1,Token line offset = 23,Token in error = SELECT ]
2)
SELECT distinct tidvalue FROM cdirmen WHERE objname='I_MAP' AND menuid<>0
AND 0<(SELECT COUNT(*) FROM cmenu WHERE cmenu.menuid=cdirmen.menuid)
AND 0=(SELECT COUNT(*) FROM cmenu WHERE cmenu.menuid=cdirmen.menuid AND cmenu.item like 'CL%')
Error Message:
Major Error 0x80040E14, Minor Error 25501
> SELECT distinct tidvalue FROM cdirmen WHERE objname='I_MAP' AND menuid<>0
AND 0<(SELECT COUNT(*) FROM cmenu WHERE cmenu.menuid=cdirmen.menuid)
AND 0=(SELECT COUNT(*) FROM cmenu WHERE cmenu.menuid=cdirmen.menuid AND cmenu.item like 'CL%')
There was an error parsing the query. [ Token line number = 2,Token line offset = 8,Token in error = SELECT ]
I have a very simple SSIS package with one connection and one Execute SQL Task.
The connection is a Native OLE DB for Analysis Services 9.0
The execute SQL task fires off this XMLA query created through the syncronize database wizard from MSAS2005.
All I want to do is dynmaicaly replace the Source on the connection String in this script to a local variable. I have tried the @variable and the ? syntax as I found some good info in BOL. However neither of these work.
I supposed I could use scripting to handle this but I didn't want to add that layer and feel that SSIS should be able to handle this.
Here is the raw script which works fine withing SSIS:
I have to create a SQL job which will run around 50 queries and email results when the query gets some results.These are like quality checks which run to check errors in the system so if any query(out of 50 queries) returns some results an email with the details will be sent .So if 5 queries return results 5 emails with the details will be sent .
I think of something like A table which has one column as the query .What will be the best way of handling such a scenario , may be need an SSIS package with steps ?
I am currently upgrading our ppc app (written in .net 2003) to .net 2005 and from sqlce 2.0 to 3.0. The new application runs out memory(storage) when handling lots of data transactions (both in case of using sqlcedatareader, and dataset ). There is no memory leak issue here, sqlce 3.0 simply uses a lot more (3 times more) memory than sqlce 2.0.
Our applicaton runs fine using sqlce 2.0 and .net 2003, but fail due to memory shortage
with the upgrades(which has the same code). Anyone can shed some lights on this issue?
I fighting with a strange problem with a DTS package, we had a DTS package under a name of user who left the company and his account was deactivated, i openned that DTS package and saved it under my name and my user is part of sysadmin role, when i am trying to execute the DTS i gets the error message permission denied unable to create table (database name) while i can crete/delete tables in that particular database manualy via query analyser as well as through enterprise manager but when i am trying to do that through a DTS as mentioned above i always gets the permission denied message any help will be highly appreciated thanks
I have a pretty simple lookup query that returns an error that I can't figure out. It states that the query has too few parameters (2), but only 1 is required. I've investigated every component and everything seems correct. I have another lookup just like this and it works fine. Here is a simple description:
I have an Access table with values that have to be loaded into a SQL Server table. I defined a Row Transform task with an ActiveX script.
My lookup query is called Test1 and looks like this:
SELECT ConveyanceInstrumentId FROM ConveyanceInstrumentMapping WHERE (conveyanceinstrument = ?)
' Copy each source column to the destination column Function Main() dtsdestination("ConveyanceInstrumentId") = DTSLookups("test1").Execute(1) Main = DTSTransformStat_OK End Function
The Execute(1) is just there for testing purposes, but returns the same error as when I reference the field I want to look up.
When I test this, an error is returned: [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 2
Here's a little SP to break up those long-running, massively-locking, bring-app-to-a-halt queries. By default it does 500 rows at a time and allows for a maximum SQL query size of 4000 characters; it should be trivial to adjust those.
Cheers -b
CREATE PROCEDURE p_BatchExecute (@vcSQL varchar(4000)) AS set nocount on DECLARE @iRows int select @iRows=1 SET ROWCOUNT 500 WHILE @iRows>0 BEGIN print 'Executing batch of 500...' exec (@vcSQL) set @iRows=@@ROWCOUNT END GO
I have created a logging test package that I am attempting to execute in SQLAgent. It uses an environment variable in Package Configurations to set a variable which is the expression value for the ConnectionManager's 'ole_src_Admin' ConnectionString. The next three Package Configurations use that connection to retrieve SQL Server configuration types/
Using the same account I can execute the package succesfully from cmd line The execution string is dtexec /SQL "Parent Package" /SERVER "BLAHSQL-DEV" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING V
Attempting to execute this via SQLAgent (type CmdExec) I receive the following error: Date 9/26/2007 9:47:45 AM Log Job History (Logging Test) Step ID 3 Server QRISQL-DEV Job Name Logging Test Step Name Execute 2005 Package Duration 00:00:01 Sql Severity 0 Sql Message ID 0 Operator Emailed Operator Net sent Operator Paged Retries Attempted 0 Message Microsoft (R) SQL Server Execute Package Utility Version 9.00.3042.00 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 9:47:45 AM Info: 2007-09-26 09:47:46.28 Code: 0x40016038 Source: Parent Package Description: The package is attempting to configure from the environment variable "SQL_Parameter_Connect_String". End Info Info: 2007-09-26 09:47:46.28 Code: 0x40016040 Source: Parent Package Description: The package is attempting to configure from SQL Server using the configuration string ""ole_src_Admin";"[dbo].[Parameter]";"sv_ssis_package_store_connectionstring";". End Info Error: 2007-09-26 09:47:46.30 Code: 0xC0202009 Source: Parent Package Connection manager "ole_src_Admin" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E21. End Error Info: 2007-09-26 09:47:46.30 Code: 0x40016040 Source: Parent Package Description: The package is attempting to configure from SQL
I have also succeffuly executed this package in the same manner locally using both the cmd line and SQLAgent.
I have a periodic backup task, and when I need a development copy on which to test code, I restore my most recent backup to a different name, switch the ODBC link to that name on my development machine and have a current copy of the database to play with.
I've been doing this for years, and it works great. Just now I did it, and suddenly my development machine is unable to run any stored procedures that have the 'Execute as owner' clause in their definition. I'm using domain accounts, my personal account is the owner of the database, and everything works on the production copy, which is in the same instance on the same machine.
The test copy is identical to the production copy, which continues to work fine - it was just created using by restoring the backup of the production copy, but I can't run anything with this clause. As soon as I delete the 'Execute as owner' line, the procedure is suddenly available. If I put it back, I'm locked out again.
The error message is: The server principal “sa” is not able to access the database “WhateverDB” under the current security context
We have a request to build a report based on user input from an excel spreadsheet. We have a SSIS package that imports the data from Excel. This is run by a sql server agent job. Our stored procedure executes this job and runs this whole process just fine but when we execute the stored procedure from reporting services we get errors. Has anyone done this type of thing before and do you have any working solutions for how to get this reporting methodology to function?
I'm using a form that has a dropdown control. This dropdown control has items that can be selected that serves as a filter for displaying results on the page that is returned from a stored procedure call. I'm having trouble finding a way to execute the stored procedure to display the filtered results everytime a different item in the dropdown gets selected. Currently, the form does get submitted and the selected item does get saved, but the stored procedure never gets executed on a postback. Any ideas on resolving this issure? Your help is much appreciated.
I am trying to programmatically execute a package that contains an Execute SQL Task component bound to a variable for its "SqlStatementSource" property (via an expression). The variable is of type String and contains a simple value of "SELECT 1". The Execute SQL Task contains an expression that sets the SqlStatementSource property to the value of this variable.
The package runs fine when I execute it via dtexec or BIDS, but when I attempt to run it via the object model, I receive the following error message:
The result of the expression ""@[User::Sql]"" on property "SqlStatementSource" cannot be written to the property. The expression was evaluated, but cannot be set on the property.
I did a search on this forum and noticed quite a few threads about this same issue, but no explanation/solution. We have quite a few packages that have dynamically constructed SQL statements for Execute SQL Tasks, and they are all failing to run via the object model. Is there something that I am missing?
Need some help... When we tried to run mulitple packages one after the other from a windows service, first one succeeds but later ones are throwing below error : "The script threw an exception: The element cannot be found in a collection. This error happens when you try to retrieve an element from a collection on a container during execution of the package and the element is not there. A deadlock was detected while trying to lock variable "System:ackageName, User::BusinessDate, User::Environment, User:ortfolioName" for read access. A lock could not be acquired after 16 attempts and timed out."
Later, we tried to create separate AppDomains for each package and execute via console application, but ended up with below error (The below expressions were defined in OnError Event) : "The result of the expression "@[User::ReportErrorFrom]" on property "FromLine" cannot be written to the property. The expression was evaluated, but cannot be set on the property. The result of the expression ""Error At :" + @[System:ourceName] + "" + "Error Description : "+ @[System::ErrorDescription] + "" " on property "MessageSource" cannot be written to the property. The expression was evaluated, but cannot be set on the property."
At last, we tried to span a separate process (System.Diagnostics.Process) for each package. this seems working but taking very long time: A package that normally takes 2 min, is taking 60 min.
We also tried creating an SSIS Package that executes mulitple packages. But only first package is getting executed, and second one is throwing below error (Here the variable it is trying to lock is of first package): "Failed to lock variable "UniqueInstrumentsQuery1" for read access with error 0xC0010001 "The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created.".
Please help us with some work around for this. Thanking you in advance,
Does anyone have any instructions on how to go about uprading a .sdf file from sqlce 2.0 to sqlce 3.0?
I found this on an msdn website (link: http://www.microsoft.com/sql/editions/sqlmobile/upgrading.mspx) but as you can see its very lacking on detail. (I included the link and content referring to upgrading the database files so you won't have to go to the link to see the part referring to upgrading the database.) I tried to run the upgrade.exe on my device but it doesn't explain how you're supposed to point it to the .sdf that you are trying to update. Here is the content on the above web page.
Upgrading Database Files You can upgrade a database created with an earlier version of SQL Server CE 2.0 to a database created with SQL Server Mobile by using the SQL Server Mobile Database Upgrade tool (upgrade.exe). The Database Upgrade tool runs on a smart device.
When you run the SQL Server Mobile Database Upgrade tool, the new SQL Server Mobile database is created on the smart device. The new database, with the file name extension .sdf, contains all the data that was in the source database. You need to reinitialize the upgraded database to continue using it for replication.
Note: If you are using replication or remote data access (RDA) as a connectivity solution before upgrading to SQL Server Mobile, you must synchronize the source SQL Server CE database (subscription database) with the SQL Server database (Publisher). The reason for synchronizing before upgrading to SQL Server Mobile is to ensure that any changes that exist in the tables on the SQL Server CE database are updated on the SQL Server database, because after the upgrade you must resubscribe or repull using the new database.
My app is working on my local system with the vs2008 development server. It's an asp.net app and I added the line: AppDomain.CurrentDomain.SetData("SQLServerCompactEditionUnderWebHosting", true);
So it works fine on my system. But I can't get it to work on shared hosting. I have uploaded all the sqlce 3.5 dlls to the bin directory. I am stuck at the error:
Access to the database file is not allowed. [ File name = ]
There actually is a file name in the connection string, and I set the permissions wide open on App_Data to test this.
What are the .net framework requirements for sqlce 3.5? I can't find this info anywhere.
I know sqlce 3.5 is not recommended for asp.net, but it is not the main site database but is used for a setup database utility to be installed for a windows app.
We have over 1000 terminals running CE 4.2 on an x86 cpu currently using SQLCE 3.1.
We have just upgraded to VS 2008 and found the projects have been upgraded to reference SQL CE 3.5 - this is great, as we wish to use things like TOP etc.
However, all the distributable packages only include CAB files for CE 4.2 for the ARM Processor, or CE 5.0 for the x86. Is there a CAB package available for CE 4.2 for the x86 CPU? (please...)
Even just the relevant .DLL files would be fine as we repackage them ourselves anyway.
Hi i developed Wm5 PPC application with sqlce database. i Create Cab file when i install in my HP WM5 PPC that will showe the Message sqlcewm.dll missed. Please tell me 1)sqlce30.ppc.wce5.armv4i 2)sqlce30.wce5.armv4i 3)sqlce20.ppc.wce5.armv4i..Please Help me
In my homegrown data layer, I added the ability to do Order By Clauses.
I do one query with an Order By clause (all 6 records in the table retrieved). Then I do a 2nd query without an Order By (36 of around 200 records in the table retrieved)
And I'm getting Native Exceptions.
ExceptionCode: 0xc00000000c (Null Pointer I believe) ExceptionAddresss: 0x00c3703c (looks to be in sqlcese30.dll) Reading: 0x00000005
I ran thru with the debugger, and am finding the error happening inside of a SQLDataReader.Read() call. It's trying to read the 14th of the 36 records that the query has found.
I am all but positive I'm not closing this database connection. The problem is happening while I'm looping off the reader data I just retrieved and in this query there was no ORDER BY clause.
I'm at a loss as to what to do about this. I'm not explicitly closing any database connections, and I'm getting the data right after performing the query.
I wrote a very simple C# console application which was designed to give us a rough idea on the performance and reliability of SQLCE (the source code without metrics has been posted to this group recently). What I've found is that there are major performance and reliability issues with SQLCE 3.0 as opposed to SQLCE 2.0. Whereas SQLCE 2.0 seems to be fairly consistent in terms of it's memory usage and the amount of time taken to insert rows into the table regardless of the size of the table (to a limit, after about 300,000 records it too has major performance issues), SQLCE 3.0 seems to slow down noticably and continually use more memory even after a few thousand records are inserted and actually crashes (consistently) after 25592 records are inserted!
What gives? Based on this it would seem that SQLCE 2.0 is far preferable for an unattended device that needs an uptime of over 30 days whilst constantly (several times per minute) inserting into the database.
Last month , I have made a utility named sqlce viewer to help me manage sqlce 3.5 beta version databases . The location is https://sourceforge.net/projects/sqlceviewer/
And now , I encounter a strange problem , need some help .
After user install this utility or run it standalone execute file .There has a error message poped before see the login in form . Even user has installed .net framework version 3.5 . And if user has installed visual studio 2008 beta2 ,this problem will not existed .
This utility depend on these components : 1)WPF 2).net framework 3.5 beta
I don't know what's the reason clearly . Anyone could give some advice are warm welcome.
I'm still having trouble deploying my Compact Edition application. The installation on other computers works, but if the program tries to start, i get the error message (translated from german):
"This Version of SQL Server 2005 Mobile Edition is not licencensed for productive use"
I always thought that there are no licence restriction with SQL Compact Edition. Strangely the the programm works on some computers... as I think on all computers where Visual Studio is installed.
