PAGE HEADER: March 2012

Friday, March 30, 2012

problem with SSIS package

Hi there,

I have a weird problem with SSIS package. Package is deployed and save on SQLServer2k5. It runs no problem if I start from Integration services. It fails every time when it is scheduled as a job.

The error message is: The package execution failed.The step failed.

I will appreciate any advice.

Thanks a lot.

When it's run from a job it executes under the account sql agent is running as. Are you using a data source that is sql authentication? If so you need to set the protection level when you deploy the package to something other than encrypt with user key or else it probably won't work when run as a job. There's a couple of options but if you just want it to work set it to rely on server.|||

Thank you Brent,

Unfortunately I use only Windows security. But I will try to set protection any way.

Cheers.

Michael

|||Make sure the account sql agent is running under has access to the db's you're trying to connect to. If that's a security concern, you can add a proxy account to execute the job under.|||

how can i check if this sql agent is running?

ihave this error when running my package after deploying it

Error: 0xC0202009 at Package, Connection manager "Presup Dev sql_prov": An OLE DB error has occurred. Error code: 0x80040E4D.

An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E4D Description: "Communication link failure".

An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E4D Description: "TCP Provider: Se ha forzado la interrupción de una conexión existente por el host remoto.

|||

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=237837&SiteID=1

|||

ruk_walled wrote:

how can i check if this sql agent is running?
ihave this error when running my package after deploying it
Error: 0xC0202009 at Package, Connection manager "Presup Dev sql_prov": An OLE DB error has occurred. Error code: 0x80040E4D.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E4D Description: "Communication link failure".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E4D Description: "TCP Provider: Se ha forzado la interrupción de una conexión existente por el host remoto.

this might help: http://support.microsoft.com/kb/918760/en-us|||

Brent,

I am having the same problem. I have thirteen SSIS packages and each one connects to an Oracle Database that uses SQL Authentication. Could you elaborate on the options you mentioned? And how would I set it to rely on the server, server Storage? (if so, doesn't work for me)

Thanks.

|||

Binh Cao wrote:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=237837&SiteID=1

Not sure if that was for me Binh, but that also did not work. My problem persists.

|||

What is the exact error you are receiving? It only happens when using SQL Agent?

Rafael

|||

I am receiving an 'AcquireConnection Error' that it cannot connect to the Oracle db. And yes, it only occurs when I try to schedule a job using SQL Agent, it runs in BIDS just fine.

Exact Error:

OnError,EESDCSDS522, [domain]\[login],Data Flow Task 1,{DF1DCB8E-74E7-4C5D-A639-7BAED849AAD7},{72E82E9B-7620-424E-A4E3-70E601497B54},8/10/2006 10:31:08 AM,8/10/2006 10:31:08 AM,-1071611876,0x,The AcquireConnection method call to the connection manager "[Remote Oracle Database]" failed with error code 0xC0202009.

EESDCSDS522 is the local db

I have taken out my domain\login and the name of the Remote Oracle DB for security reasons.

|||

_Phil_,

Is the box where you are running the package a 64-bit machine? SQL Agent will run the 64-bit version of DTSexec and all your 32-bit drivers won't be available for the package. The Work around is to schedule as command line that will call the 32-bit version of dtsexec.

Other thing to check is the credentials of the account used to run the SQL agent service; make sure it has the required permissions.

I hope this helps.

|||

Dear Rafael Salas,

This is the message we are getting when checking the job run history.

Message
The job failed. The Job was invoked by Schedule 4 (Package packageName). The last step to run was step 1 (Package packageName).

and on expanding it shows:

Executed as user: Servername\SYSTEM. The package execution failed. The step failed.

|||

Still facing the same problem.

The system account as well as the account specified to run the agen also fails.

thanks,

|||Try running DTEXEC from the command line under the account that you are attempting to use. That should give you a better error message.

Problem with SSIS and auto-increment of an ID

Hello,
I have a little problem with SQL Server 2005. I have chosen the option "auto-increment" for a column which includes the primary key. Moreover, I turned the option "Ignore double values" on. I get the datasets from a SSIS-Import-project. Unfortunately, the ID is getting incremented even if there are double values.
For example:
The first entries in the table have the IDs: 1, 2, 3, 4, 5
Then I start the SSIS-project which tries to write the first 5 entries again in the database. Of course, the entries do not appear again in the db but a new entry gets the ID 11 instead of 6. Is there a setting, that the ID won't get incremented if there double values?
Thanks
M-l-GI have not used the "auto-increment" function but the only time I had to increment a value was when the field was a key in my db. I set the key as an Identity key and so the db takes care of the incrementing. Is this something you are able to do? Just a thought!sql

problem with srever 2005 and Oracle

I am creating report model witch have connection to Oracle database. I was
created the connection and data source view, but then I want to create
Report model with wizard I am geting the error: ORA-02179: valid options:
ISOLATION LEVEL {SERIAZABLE | READ COMMITTED}.
how to change isolation level to valid oracle connection isolation level.It is my understanding that the model part of RS in 2005 is not yet
compatible with Oracle. I am tied to find where I read that on technet
but I can't. Maybe someone else can elaborate.
Thanks!|||Work-around for Report Builder:
1. Create linked server connection to Oracle database using the
OraOLEDB.Oracle provider(more up-to-date than Microsoft's).
2. Create a Data Source using the native SQL provider to the SQL Server
where you created in step 1.
3. Create a data source view; do not select objects.
4. Right-click in the DSV designer pane and create a New Named Query. Build
your query against the linked server (i.e. use 4-part names: select * from
linkedservername..schema.object). Repeat step 4 for each object you wish to
add to your model.
5. Add logical keys where applicable.
6. Build your model.
7. Deploy & build reports using Report Builder :-)
X

Problem with SQLXML BulkLoad from .NET Application

I am using BulkLoad from .NET application as follows

SQLXMLBULKLOADLib.SQLXMLBulkLoad4Class obj = new SQLXMLBULKLOADLib.SQLXMLBulkLoad4Class();

obj.ConnectionString = "Provider=SQLOLEDB;Server=serverName;Database=dbName;Integrated Security=SSPI";

obj.ErrorLogFile = Application.StartupPath + @."\Error.log";

obj.IgnoreDuplicateKeys = true;

try

{

obj.Execute(Application.StartupPath + @."\Schema1.xsd", Application.StartupPath + @."\Data1.xml");

obj.Execute(Application.StartupPath + @."\Schema2.xsd", Application.StartupPath + @."\Data2.xml");

}

catch (Exception ex)

{

throw ex;

}

I get following error when I run my application:

InvalidCastException was unhandled by user code.

Unable to cast COM object of type 'SQLXMLBULKLOADLib.SQLXMLBulkLoad4Class' to interface type 'SQLXMLBULKLOADLib.ISQLXMLBulkLoad4'. This operation failed because the QueryInterface call on the COM component for the interface with IID '{88465BA7-AEEE-49A1-9499-4416287A0160}' failed due to the following error: No such interface supported (Exception from HRESULT: 0x80004002 (E_NOINTERFACE)).

When I run the same code in a Console application it works alright. In my application the code is executed on a Background thread, could that be cause of exception? Please help.

Thanks

From SQL Server Books Online :

Using SQLXML Bulk Load in the .NET Environment

The reference to the Bulk Load component (xblkld4.dll) can also be added using the tlbimp.exe tool, which is available as part of .NET framework. This tool creates a managed wrapper for the native DLL (xblkld4.dll), which can then be used in any .NET project. For example:

c:\>tlbimp xblkld4.dll

This creates the managed wrapper DLL (SQLXMLBULKLOADLib.dll) that you can use in the .NET Framework project. In the .NET Framework, you add project reference to the newly created DLL.

Please follow the link for more info :

http://msdn2.microsoft.com/en-us/library/ms171878.aspx.

This will solve your issue.

Thanks

Naras.

|||

I think you should use

[STAThread]

staticvoid Main(string[] args)

{

//your code

}

|||I am having the same problem, however, running tlbimp does not fix it. I have to run tlbimp on the full path to the DLL because it can't find it unless I specify it. I get the same error. Please help!

Problem with SQLXML BulkLoad from .NET Application

I am using BulkLoad from .NET application as follows

SQLXMLBULKLOADLib.SQLXMLBulkLoad4Class obj = new SQLXMLBULKLOADLib.SQLXMLBulkLoad4Class();

obj.ConnectionString = "Provider=SQLOLEDB;Server=serverName;Database=dbName;Integrated Security=SSPI";

obj.ErrorLogFile = Application.StartupPath + @."\Error.log";

obj.IgnoreDuplicateKeys = true;

try

{

obj.Execute(Application.StartupPath + @."\Schema1.xsd", Application.StartupPath + @."\Data1.xml");

obj.Execute(Application.StartupPath + @."\Schema2.xsd", Application.StartupPath + @."\Data2.xml");

}

catch (Exception ex)

{

throw ex;

}

I get following error when I run my application:

InvalidCastException was unhandled by user code.

When I run the same code in a Console application it works alright. In my application the code is executed on a Background thread, could that be cause of exception? Please help.

Thanks

From SQL Server Books Online :

Using SQLXML Bulk Load in the .NET Environment

c:\>tlbimp xblkld4.dll

This creates the managed wrapper DLL (SQLXMLBULKLOADLib.dll) that you can use in the .NET Framework project. In the .NET Framework, you add project reference to the newly created DLL.

Please follow the link for more info :

http://msdn2.microsoft.com/en-us/library/ms171878.aspx.

This will solve your issue.

Thanks

Naras.

|||

I think you should use

[STAThread]

static void Main(string[] args)

{

//your code

}

Problem with SQLXML BulkLoad from .NET Application

I am using BulkLoad from .NET application as follows

SQLXMLBULKLOADLib.SQLXMLBulkLoad4Class obj = new SQLXMLBULKLOADLib.SQLXMLBulkLoad4Class();

obj.ConnectionString = "Provider=SQLOLEDB;Server=serverName;Database=dbName;Integrated Security=SSPI";

obj.ErrorLogFile = Application.StartupPath + @."\Error.log";

obj.IgnoreDuplicateKeys = true;

try

{

obj.Execute(Application.StartupPath + @."\Schema1.xsd", Application.StartupPath + @."\Data1.xml");

obj.Execute(Application.StartupPath + @."\Schema2.xsd", Application.StartupPath + @."\Data2.xml");

}

catch (Exception ex)

{

throw ex;

}

I get following error when I run my application:

InvalidCastException was unhandled by user code.

When I run the same code in a Console application it works alright. In my application the code is executed on a Background thread, could that be cause of exception? Please help.

Thanks

From SQL Server Books Online :

Using SQLXML Bulk Load in the .NET Environment

c:\>tlbimp xblkld4.dll

This creates the managed wrapper DLL (SQLXMLBULKLOADLib.dll) that you can use in the .NET Framework project. In the .NET Framework, you add project reference to the newly created DLL.

Please follow the link for more info :

http://msdn2.microsoft.com/en-us/library/ms171878.aspx.

This will solve your issue.

Thanks

Naras.

|||

I think you should use

[STAThread]

static void Main(string[] args)

{

//your code

}

problem with SQLSTATE

create function
"DBO".sf_GetStateID( @.Abbr char(2))
returns integer
begin
declare @.StateID integer;
set @.Abbr=UPPER(@.Abbr);
if @.Abbr is null
set @.Abbr=''
set @.StateID=53;

select MIN(lngStateID) into StateID from "DBA".States where strAbbr=@.Abbr;

if @.StateID is null
insert into States(strAbbr,strName) values(@.Abbr,@.Abbr)

if SQLSTATE = '00000'

set @.StateID=@.@.IDENTITY
return(@.StateID)
end

In this function, I am getting error at SQLSTATE = '00000'. I saw in books online and it says that SQLSTATE is a keyword. It is not recognizing SQLSTATE as a keyword in the function.
Can you help in that?

2nd Error: It is not accepting the statement
select MIN(lngStateID) into StateID from "DBO".States where strAbbr=@.Abbr;

I don't see any problem in the above statement. Do help in solving these two problems.

Tks
K.The 2 problems is that sqlstate is used for odbc/embedded sql - not for udfs. Also, you are returning a scalar in the udf and not a table (for the 2nd error).sql

problem with sqlserver 2005 express

I am using VS2005 with sql server 2005 express.
I have database with atached datafile. when I am using Select command from
created table, there is not case sensitive in the result. I mean I have in
the table the row with value "Disp1" in col1, and when I am selecting with
filter Where col1="disp1" , I amd getting this one row where value is
"Disp1".
I saw in server explorer window, that connection have parameter Case
Sensitive, which value is False, but it is desabled and I can't change.
How to solve this problem.
Thanks
Collation is an attribute of the column in the table. A database has a default collation which is
applied if you don't specify a collation in the CREATE TABLE statement. Also, if you want a query to
be resolved with a different collation than the data is stored with, you can use COLLATE keyword in
the query.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Vaidas" <Vaidas@.discussions.microsoft.com> wrote in message
news:8E0BF60C-1A43-49F3-A41B-E1E05E30BE23@.microsoft.com...
>I am using VS2005 with sql server 2005 express.
> I have database with atached datafile. when I am using Select command from
> created table, there is not case sensitive in the result. I mean I have in
> the table the row with value "Disp1" in col1, and when I am selecting with
> filter Where col1="disp1" , I amd getting this one row where value is
> "Disp1".
> I saw in server explorer window, that connection have parameter Case
> Sensitive, which value is False, but it is desabled and I can't change.
> How to solve this problem.
> Thanks
>

problem with sqlserver 2005 express

I am using VS2005 with sql server 2005 express.
I have database with atached datafile. when I am using Select command from
created table, there is not case sensitive in the result. I mean I have in
the table the row with value "Disp1" in col1, and when I am selecting with
filter Where col1="disp1" , I amd getting this one row where value is
"Disp1".
I saw in server explorer window, that connection have parameter Case
Sensitive, which value is False, but it is desabled and I can't change.
How to solve this problem.
ThanksCollation is an attribute of the column in the table. A database has a default collation which is
applied if you don't specify a collation in the CREATE TABLE statement. Also, if you want a query to
be resolved with a different collation than the data is stored with, you can use COLLATE keyword in
the query.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Vaidas" <Vaidas@.discussions.microsoft.com> wrote in message
news:8E0BF60C-1A43-49F3-A41B-E1E05E30BE23@.microsoft.com...
>I am using VS2005 with sql server 2005 express.
> I have database with atached datafile. when I am using Select command from
> created table, there is not case sensitive in the result. I mean I have in
> the table the row with value "Disp1" in col1, and when I am selecting with
> filter Where col1="disp1" , I amd getting this one row where value is
> "Disp1".
> I saw in server explorer window, that connection have parameter Case
> Sensitive, which value is False, but it is desabled and I can't change.
> How to solve this problem.
> Thanks
>

problem with sqlserver 2005 express

Eric

problem with sqlserver 2005 express

problem with SQL-Server 2005 CTP

I have received visual studio 2005 beta experience kit. My kit included a version of MS SQL-Server 2005 Developer Edition CTP ( Developer Edition printed on the surface of my DVD but I don't know it's true or not ? Idea because I saw this page: http://www.microsoft.com/sql/2005/productinfo/sql2005features.mspx and there was not Developer Edition). I had a new and fresh Windows 2003 (without SP1) installed on one of my office PCs. So I decided to install visual studio 2005 beta 2 first and then SQL-Server 2005 CTP. But after I installed SQL-Server 2005 CTP I saw nothing in my start menu even Express Manager. My question is : how can I find SQL-Server Express Manager or Management Studio . is my problem related to installing Visual Studio 2005 Before SQL-Server like this topic: http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=17309

If it is how can I fix this problem?

Excuse me for my bad English writing!

thank you!

You should try posting this question to SQL Server Setup & Upgrade forum.

Thanks,
-Vineet Rao

Problem with SQLServer 2000

i installed the Visual Studio 2004+ Framework.NET 2

and SQLserver 2005

after those

i installed SQLserver 2000

and i got this :

http://img240.imageshack.us/img240/7392/sql2fk.jpg

thank you

This is the wrong forum. From your screenshot it looks like you should be checking the Express forum: http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=385&SiteID=1

In the meantime, have you checked in the Services panel that SQL Server is running?

Have you enabled remote conenctions using the Surface Area configuraiton tool?

-Jamie

|||what should i change here?

http://img86.imageshack.us/img86/746/remote6np.jpg

thank you|||

From the screenshot http://img240.imageshack.us/img240/7392/sql2fk.jpg - it looks like you are trying to access SQL Server 2005 Express Edition from SQL Server Enterprise Manager.

Try accessing SQL Server 2005 express edition from SQL Server Management Studio (which is equivalent of SQL Server 2000 Enterprise manager for SQL Server 2005.)

Thanks,
Loonysan

|||i dont know what do u mean...

i even dont need SQL server 2005 i need only the 2000...

but i have heard that removing SQL server 2005 from my computer wont work...

can someone give me step by step what to do to solve the problem...

thank you :D|||

deViance wrote:
i dont know what do u mean...
i even dont need SQL server 2005 i need only the 2000...
but i have heard that removing SQL server 2005 from my computer wont work...
can someone give me step by step what to do to solve the problem...
thank you :D

For a starter, you are in the wrong place.

If you don't need SQL2005 why did you install it? What exactly are you trying to do?

-Jamie

Problem with SqlParameterCollection - Looking for advice

I have a site which works fine with an Access DB, I now want to upsize it to use a SQL DB... I have changed my OleDB Commands etc... And Used SqlCommands and everything seems fine..

Only one problem I have and its really stumped me... I always used SQLDataSource for the app even with the AccessDB, as I knew I would be upsizing at some point. We I have the below SQLDataSource

1 <asp:SqlDataSource ID="RandomBusinessDataSource" runat="server"
2 ConnectionString="<%$ ConnectionStrings:SQLDataBaseConnectionString%>"
3 SelectCommand="SELECT TOP (1) intBusinessID, txtBusinessName, intSubCatID, intCatID, txtTelNo, txtPostCode, txtWebAdd, txtReferred, bitBusinessShow, txtLong, txtLat, memBusinessDesc, intIPAddress, bitBusinessPaid FROM tblBusiness WHERE (intBusinessID = @.Param1)" OnSelecting="RandomBusinessDataSource_Selecting">
4 <SelectParameters>
5 <asp:Parameter Name="Param1" Type="Int32" />
6 </SelectParameters>
7 </asp:SqlDataSource>

Very Simple... I have my function that is creating my random number and returning a VALID intbusinessID ... And now I have this OnSelecting event

1 Protected Sub RandomBusinessDataSource_Selecting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceSelectingEventArgs)
2 e.Command.Parameters("Param1").Value = MyNewRandomNum
3 End Sub

But when I try and run the page I get the following error (Don't forget this page IS working using the SQLDataSource and an Access DB??)

An SqlParameter with ParameterName 'Param1' is not contained by this SqlParameterCollection.

I am a bit confused?? why would it say this now... Yet it works fine if I just change the connectionString to the access DB?? Any helps very much appreciated... Thanks

Try changing "Param1" to "@.Param1" in the parameter declaration and in the code where you set the value.|||

Hi TGnat ... thanks for the help...

Unfortunately that gave the same error - Although I did manage get sort it, a chap I know at Cre8asites gave me the answer

( thread herehttp://www.cre8asiteforums.com/forums/index.php?showtopic=44722&hl= )

I had to use the following and it worked fine...

e.Command.Parameters(0).Value = MyNewRandomNum

|||Hey TGnat ... Actually you were pretty much right in the end - I printed out the parameter name and it was @.Param1 ... Although I just had the leave the selectparameter name as Param1 ... Weird hey!!... Thanks again

Problem with SQLH2

Hi all i have problem with SQLH2. SQLH2 installed on Windows XP Pro SP2
Russian.
Version SQLH2 is H2 Collector ver. V2 2.0.024.0 9/30/04 17:58
this is log
28.12.2004 15:00:16 Opening Log
28.12.2004 15:00:16 SQLH2 ver. V2 2.0.024.0 9/30/04 17:58
28.12.2004 15:00:16 Status: Init
28.12.2004 15:00:16 WARNING: There are no PerfProviders in the config file
28.12.2004 15:00:16 Status: Initializing repository: manushind
28.12.2004 15:00:16 Status: Repository Database: SQLH2Repository
28.12.2004 15:00:16 INFO: host_id = 1
28.12.2004 15:00:16 Status: Starting RUN
28.12.2004 15:00:16 Status: Registering Run
28.12.2004 15:00:16 INFO: run_id = 8
28.12.2004 15:00:16 Status: Starting Targets processing
28.12.2004 15:00:16 Status: Initializing Target: SRV-ELDDB2
28.12.2004 15:00:16 Status: registering Server
28.12.2004 15:00:16 INFO: srv_id = 2; signature =
aad193c2-d36c-4760-a312-57dc78d07944
28.12.2004 15:00:16 Status: Scanning Registry on SRV-ELDDB2
28.12.2004 15:00:16 Status: registering instance SRV-ELDDB2
28.12.2004 15:00:16 INFO: inst_id = 1
28.12.2004 15:00:16 INFO: version: 8.00.818
28.12.2004 15:00:16 Status: RUN
28.12.2004 15:00:16 Status: Initializing collector: WMI
28.12.2004 15:00:16 Status: Running WMI collector
28.12.2004 15:00:16 Status: Opening Unit 1 (OS/CS)
28.12.2004 15:00:16 Status: Collecting Win32_OperatingSystem
28.12.2004 15:00:17 Status: Closing Unit 1
28.12.2004 15:00:17 ERROR: NotFound
28.12.2004 15:00:17 WARNING: WMI Collector encountered critical error.
Abandoning Collector.
28.12.2004 15:00:17 Status: Initializing collector: EventLog
28.12.2004 15:00:17 Status: Initializing collector: SQL
28.12.2004 15:00:17 Status: Running SQL collector on SRV-ELDDB2
28.12.2004 15:00:17 Status: Opening Unit 5 (Server Properties)
28.12.2004 15:00:17 Status: Closing Unit 5
28.12.2004 15:00:17 Status: Opening Unit 1 (Databases)
28.12.2004 15:00:18 ERROR: There are fewer columns in the INSERT statement
than values specified in the VALUES clause. The number of values in the
VALUES clause must match the number of columns specified in the INSERT
statement.
28.12.2004 15:00:18 ERROR: Insert dbo.c_database
(run_id,srv_id,inst_id,dbid,name,CompatibilityLeve l,CreateDate,DataSpaceUsage,IndexSpaceUsage,dbSize ,SpaceAvailable,IsSystem,txSize,txSpaceAvailable,t xLastBackup,oRecoveryModel,oPageVerify,oUserAccess ,oAutoClose,oAutoShrink,oAutoCreateStat,oAutoUpdat eStat
,oOffline,oReadOnly,oSelectIntoBulkCopy,oTruncateL ogOnCheckpoint,oPublished,oSubscribed,oMergePublis hed,oMergeSubscribed,oFullTextEnabled,oDbChaining)
Values (8, 2, 1, 8, 'auxdb', 80, '15.12.2004 15:25:56', 15904, 368,
38,812500, 8,414063, 0, 1,250000, 0,820313, '18.12.2003 1:35:22', 3, 1, 0, 0,
0, 1, 1, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0)
28.12.2004 15:00:18 ERROR: Unit 1 failed
28.12.2004 15:00:18 Status: Closing Unit 1
28.12.2004 15:00:18 ERROR: Insert dbo.c_database
(run_id,srv_id,inst_id,dbid,name,CompatibilityLeve l,CreateDate,DataSpaceUsage,IndexSpaceUsage,dbSize ,SpaceAvailable,IsSystem,txSize,txSpaceAvailable,t xLastBackup,oRecoveryModel,oPageVerify,oUserAccess ,oAutoClose,oAutoShrink,oAutoCreateStat,oAutoUpdat eStat
,oOffline,oReadOnly,oSelectIntoBulkCopy,oTruncateL ogOnCheckpoint,oPublished,oSubscribed,oMergePublis hed,oMergeSubscribed,oFullTextEnabled,oDbChaining)
Values (8, 2, 1, 8, 'auxdb', 80, '15.12.2004 15:25:56', 15904, 368,
38,812500, 8,414063, 0, 1,250000, 0,820313, '18.12.2003 1:35:22', 3, 1, 0, 0,
0, 1, 1, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0)
28.12.2004 15:00:18 ERROR: SRV-ELDDB2: Collection failed; Error:104;
Provider Error: 110
28.12.2004 15:00:18 ERROR: Critical Repository Failure
28.12.2004 15:00:18 SQLH2 is shutting down
28.12.2004 15:00:18 Closing Log
P.S.
i have Domain admins right.
Thank you.
Dimitri,
I suspect this is a localisation issue. Is your decimal symbol a comma by
any chance?
If you look at the Appendix C of the SQLH2 Deployment Guide (this should be
in your root installation directory), you will see some useful hints on how
to get SQLH2 working on localised Windows platforms.
Looking at your log, I would also advise that your dates are not in the
expected format for SQLH2, so you will need to use the /D switch (again
please see Appendix C).
Hope this helps,
John Marsh
"dmitri" wrote:

> Hi all i have problem with SQLH2. SQLH2 installed on Windows XP Pro SP2
> Russian.
> Version SQLH2 is H2 Collector ver. V2 2.0.024.0 9/30/04 17:58
> this is log
> 28.12.2004 15:00:16 Opening Log
> 28.12.2004 15:00:16 SQLH2 ver. V2 2.0.024.0 9/30/04 17:58
> 28.12.2004 15:00:16 Status: Init
> 28.12.2004 15:00:16 WARNING: There are no PerfProviders in the config file
> 28.12.2004 15:00:16 Status: Initializing repository: manushind
> 28.12.2004 15:00:16 Status: Repository Database: SQLH2Repository
> 28.12.2004 15:00:16 INFO: host_id = 1
> 28.12.2004 15:00:16 Status: Starting RUN
> 28.12.2004 15:00:16 Status: Registering Run
> 28.12.2004 15:00:16 INFO: run_id = 8
> 28.12.2004 15:00:16 Status: Starting Targets processing
> 28.12.2004 15:00:16 Status: Initializing Target: SRV-ELDDB2
> 28.12.2004 15:00:16 Status: registering Server
> 28.12.2004 15:00:16 INFO: srv_id = 2; signature =
> aad193c2-d36c-4760-a312-57dc78d07944
> 28.12.2004 15:00:16 Status: Scanning Registry on SRV-ELDDB2
> 28.12.2004 15:00:16 Status: registering instance SRV-ELDDB2
> 28.12.2004 15:00:16 INFO: inst_id = 1
> 28.12.2004 15:00:16 INFO: version: 8.00.818
> 28.12.2004 15:00:16 Status: RUN
> 28.12.2004 15:00:16 Status: Initializing collector: WMI
> 28.12.2004 15:00:16 Status: Running WMI collector
> 28.12.2004 15:00:16 Status: Opening Unit 1 (OS/CS)
> 28.12.2004 15:00:16 Status: Collecting Win32_OperatingSystem
> 28.12.2004 15:00:17 Status: Closing Unit 1
> 28.12.2004 15:00:17 ERROR: NotFound
> 28.12.2004 15:00:17 WARNING: WMI Collector encountered critical error.
> Abandoning Collector.
> 28.12.2004 15:00:17 Status: Initializing collector: EventLog
> 28.12.2004 15:00:17 Status: Initializing collector: SQL
> 28.12.2004 15:00:17 Status: Running SQL collector on SRV-ELDDB2
> 28.12.2004 15:00:17 Status: Opening Unit 5 (Server Properties)
> 28.12.2004 15:00:17 Status: Closing Unit 5
> 28.12.2004 15:00:17 Status: Opening Unit 1 (Databases)
> 28.12.2004 15:00:18 ERROR: There are fewer columns in the INSERT statement
> than values specified in the VALUES clause. The number of values in the
> VALUES clause must match the number of columns specified in the INSERT
> statement.
> 28.12.2004 15:00:18 ERROR: Insert dbo.c_database
> (run_id,srv_id,inst_id,dbid,name,CompatibilityLeve l,CreateDate,DataSpaceUsage,IndexSpaceUsage,dbSize ,SpaceAvailable,IsSystem,txSize,txSpaceAvailable,t xLastBackup,oRecoveryModel,oPageVerify,oUserAccess ,oAutoClose,oAutoShrink,oAutoCreateStat,oAutoUpdat eSt
at,oOffline,oReadOnly,oSelectIntoBulkCopy,oTruncat eLogOnCheckpoint,oPublished,oSubscribed,oMergePubl ished,oMergeSubscribed,oFullTextEnabled,oDbChainin g)
> Values (8, 2, 1, 8, 'auxdb', 80, '15.12.2004 15:25:56', 15904, 368,
> 38,812500, 8,414063, 0, 1,250000, 0,820313, '18.12.2003 1:35:22', 3, 1, 0, 0,
> 0, 1, 1, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0)
> 28.12.2004 15:00:18 ERROR: Unit 1 failed
> 28.12.2004 15:00:18 Status: Closing Unit 1
> 28.12.2004 15:00:18 ERROR: Insert dbo.c_database
> (run_id,srv_id,inst_id,dbid,name,CompatibilityLeve l,CreateDate,DataSpaceUsage,IndexSpaceUsage,dbSize ,SpaceAvailable,IsSystem,txSize,txSpaceAvailable,t xLastBackup,oRecoveryModel,oPageVerify,oUserAccess ,oAutoClose,oAutoShrink,oAutoCreateStat,oAutoUpdat eSt
at,oOffline,oReadOnly,oSelectIntoBulkCopy,oTruncat eLogOnCheckpoint,oPublished,oSubscribed,oMergePubl ished,oMergeSubscribed,oFullTextEnabled,oDbChainin g)
> Values (8, 2, 1, 8, 'auxdb', 80, '15.12.2004 15:25:56', 15904, 368,
> 38,812500, 8,414063, 0, 1,250000, 0,820313, '18.12.2003 1:35:22', 3, 1, 0, 0,
> 0, 1, 1, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0)
> 28.12.2004 15:00:18 ERROR: SRV-ELDDB2: Collection failed; Error:104;
> Provider Error: 110
> 28.12.2004 15:00:18 ERROR: Critical Repository Failure
> 28.12.2004 15:00:18 SQLH2 is shutting down
> 28.12.2004 15:00:18 Closing Log
> P.S.
> i have Domain admins right.
> Thank you.
|||John, i see this hints but not try use it yet. I hope this help. thank you.
"John Marsh" wrote:
[vbcol=seagreen]
> Dimitri,
> I suspect this is a localisation issue. Is your decimal symbol a comma by
> any chance?
> If you look at the Appendix C of the SQLH2 Deployment Guide (this should be
> in your root installation directory), you will see some useful hints on how
> to get SQLH2 working on localised Windows platforms.
> Looking at your log, I would also advise that your dates are not in the
> expected format for SQLH2, so you will need to use the /D switch (again
> please see Appendix C).
> Hope this helps,
> John Marsh
>
> "dmitri" wrote:
Stat,oOffline,oReadOnly,oSelectIntoBulkCopy,oTrunc ateLogOnCheckpoint,oPublished,oSubscribed,oMergePu blished,oMergeSubscribed,oFullTextEnabled,oDbChain ing)[vbcol=seagreen]
Stat,oOffline,oReadOnly,oSelectIntoBulkCopy,oTrunc ateLogOnCheckpoint,oPublished,oSubscribed,oMergePu blished,oMergeSubscribed,oFullTextEnabled,oDbChain ing)[vbcol=seagreen]

Problem with sqlexpress

hi:

I set up a DSN via Adim tools, then I specify the connection string as "DSN=UserAppSample;Trusted_Connection=True".

When I run my aspx page, it says:

ERROR [42000] [Microsoft][ODBC SQL Server Driver][SQLServer]Cannot open database "UserAppSample" requested by the login. Thelogin failed.

It asks for login, but I use windows authentication. So what is wrong with this? :(

This is a SQL permission issue. You need to check the SQL Server logins, make sure the account which runs the application has proper permission on the UserAppSample database.

Problem with SqlDataSource.Insert() using MasterPage

Hi,

because I have had problems with transporting of data from TextBoxes to SQL database in my application using SqlDataSource.Insert(), I tried to analyze it using Microsoft's sample code located here:

http://msdn2.microsoft.com/en-us/library/system.web.ui.webcontrols.sqldatasource.insert.aspx

Everything worked well untill I have used masterpage. Since this moment my database table started receive only NULL values - the same problem, which I have in my application. Despite all data values were correct before calling SqlDataSource.Insert() method. Is it some bug or something else (my bug Smile ) and how can I resolve it? I'd like to use all advantages of masterpages and datasources together.

Here are non-working codes of spoken sample:

shelter3.aspx

<%@.PageLanguage="VB"MasterPageFile="~/MasterPage3.master"AutoEventWireup="false"CodeFile="shelter3.aspx.vb"Inherits="shelter3"Title="Untitled Page" %>
<asp:ContentID="Content1"ContentPlaceHolderID="Obsah"runat="Server">
<asp:DropDownListID="DropDownList1"runat="server"DataSourceID="SqlDataSource1"
DataTextField="nazev"DataValueField="code"/>
<asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:intranet %>"
SelectCommand="SELECT nazev,code FROM smenky"InsertCommand="INSERT INTO smenky (nazev,code) VALUES (@.nazev,@.code)">
<InsertParameters>
<asp:FormParameterName="nazev"FormField="NazevBox"/>
<asp:FormParameterName="code"FormField="CodeBox"/>
</InsertParameters>
</asp:SqlDataSource>
<p>
<asp:TextBoxID="NazevBox"runat="server"/>
<asp:RequiredFieldValidatorID="RequiredFieldValidator1"runat="server"ControlToValidate="NazevBox"
Display="Static"ErrorMessage="Please enter a company name."/>
<p>
<asp:TextBoxID="CodeBox"runat="server"/>
<asp:RequiredFieldValidatorID="RequiredFieldValidator2"runat="server"ControlToValidate="CodeBox"
Display="Static"ErrorMessage="Please enter a phone number."/>
<p>
<asp:ButtonID="Button1"runat="server"Text="Insert New Shipper"/>
</asp:Content>

shelter3.aspx.vb

PartialClass shelter3
Inherits System.Web.UI.Page
ProtectedSub InsertShipper(ByVal senderAsObject,ByVal eAs System.EventArgs)Handles Button1.Click
SqlDataSource1.Insert()
EndSub
EndClass
masterpage3.master

<%@.MasterLanguage="VB"CodeFile="MasterPage3.master.vb"Inherits="MasterPage3" %>
<!DOCTYPEhtmlPUBLIC"-//W3C//DTD XHTML 1.0 Transitional//EN""http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<htmlxmlns="http://www.w3.org/1999/xhtml">
<headrunat="server">
<title>Untitled Page</title>
</head>
<body>
<formid="form1"runat="server">
<div>
<asp:contentplaceholderid="Obsah"runat="server">
</asp:contentplaceholder>
</div>
</form>
</body>
</html>

masterpage3.master.vb

PartialClass MasterPage3
Inherits System.Web.UI.MasterPage
EndClass

Thanks for any idea

I reproduced your issue, not sure what's causing the problem. However we can use asp:ControlParameter in this case:

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:LocalSqlServer %>"

SelectCommand="SELECT id,name FROM test_MasterSqlDataSource" InsertCommand="insert into test_MasterSqlDataSource select @.id,@.name">

<asp:ControlParameter Name="name" PropertyName="Text" ControlID="CodeBox" />

</InsertParameters>

</asp:SqlDataSource>

I've tested and found the values can be inserted into database properly.

Problem with SqlDataSource using sub-query and date as parameters

I am creating a search page for master detail tables. The search criteria is mainly on the header table. However, there is also one criteria which is in detail table, let said product number.

In my SqlDataSource, I setup the SQL like this.

select fieldA, fieldB, ..., fieldZ from masterTable where (1 = 1)

Then, the additional search criteria is appended to the SqlDataSource select command once the user click the search button. If user wants to search product number, the following will be appended

and exists (select 1 from detailTable where pid = masterTable.id and productNo = @.productNo)

The problem is when I provides both the sub-query criteria and 2 date fields criteria. The page will raise an timeout exception. I don't have any clue on this as I can copy the SQL and run it inside the SQL Server Management Studio. The result come up in a second.

Any suggestion on tackling this problem? Thanks!

hi,

U can avoid this by diffarent options...By above information I can explain like this

1 use UNION

2 Use Primary Key in your every Subquery Query followed by the Search.

3 Use Joins with valid Key Elements.etc

or send the required result columnes and the Table Design

bye

murthy

|||

Hi Murthy,

1. use UNION

I don't know how should I use UNION in master-detail structure. Please give more detail.

2. The primary key is already used in the where clause of the subquery. The column pid means the primary key ID in master table.

3. Use Join

The use of join is not desirable. I have to group the records back together afterward. I just want to search the master table but use detail record as criteria. If I join them without group, the master record will repeat themselves in the result.

Actually, I'm strange about the performance difference by using ADO.NET and SQL Server management studio.

|||

Hi,

Do this Use "#' table with one primary key ID as the Column column in all the condictions

and finally join all the Table with the key Elements..

Ex:

select <Key>,<Search column 1 > into #TableA from <Master table> where <Condition>

select <Key>,<Search column 2 > into #TableB from <Master table> where <Condition>

.....

....

and Finally

select <Search Column1>,<Search Column2>.<Search Column3>,... from #TableA, #TableB, #TableC...where #TableA.Key=#TableB.Key,#TableA.Key=#TableC.key etc

drop all temp table

your result is ready now...

|||

This approach seems making the simple request into a complex one.

problem with SQLConnection

I am working on a set of webforms that insert user data into a set of db tables.

I set up a test of an approach using northwind and I'm having trouble getting the insert to work. When I open the form, input the name and phone, and submit there is no error, but no record inserted into the Shippers table.

You can see one of my approaches in the ASPX code. I don't like having to do the select in order to do the insert -- so that's commented off.

I'm stuck. Thoughts about what I'm missing appreciated...

Ray

ASPX code.

<%@.PageLanguage="C#"AutoEventWireup="true"CodeFile="Default66a.aspx.cs"Inherits="pages_audit_Default66a" %>

<!DOCTYPEhtmlPUBLIC"-//W3C//DTD XHTML 1.0 Transitional//EN""http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<htmlxmlns="http://www.w3.org/1999/xhtml">

<headid="Head1"runat="server">

<title>Untitled Page</title>

</head>

<body>

<formid="form1"runat="server">

CompanyName:

<asp:textboxid="txtCompanyName"runat="server"/><br/>

Phone:

<asp:textboxid="txtPhone"runat="server"/><br/>

<br/>

<asp:buttonid="btnSubmit"runat="server"text="Submit"onclick="btnSubmit_Click"/>

<br/>

<asp:LabelID="awesomelbl"runat="server"Text="Label"></asp:Label><br/>

<br/>

<!--

<asp:sqldatasource id="SqlDataSource1" runat="server" connectionstring="<%$ ConnectionStrings:NorthwindConnectionString %>"

insertcommand="INSERT INTO Shippers(CompanyName, Phone) VALUES (@.CompanyName, @.Phone)" ProviderName="System.Data.SqlClient" SelectCommand="SELECT * FROM [Shippers]">

<asp:controlparameter controlid="txtCompanyName" name="CompanyName" />

<asp:controlparameter controlid="txtPhone" name="Phone" />

</insertparameters>

</asp:sqldatasource>

-->

</form>

</body>

</html>

c Sharp code

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.Sql;

using System.Configuration;

using System.Collections;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Web.UI.HtmlControls;

publicpartialclasspages_audit_Default66a : System.Web.UI.Page

{

protectedvoid Page_Load(object sender,EventArgs e)

{

}

protectedvoid btnSubmit_Click(object sender,EventArgs e)

{

SqlConnection con =newSqlConnection("Data Source=Chilibowl;Trusted_Connection=yes;DataBase=Northwind");

SqlCommand cmd =newSqlCommand("INSERT INTO [Shippers] ([CompanyName], [Phone]) VALUES (@.CompanyName, @.Phone)");

SqlParameter cnameparam =newSqlParameter("@.CompanyName", txtCompanyName.Text);SqlParameter phnparam =newSqlParameter("@.Phone", txtPhone.Text);

cmd.Parameters.Add(cnameparam);

cmd.Parameters.Add(phnparam);

try

{

con.Open();

if (cmd.ExecuteNonQuery() > 0)awesomelbl.Text ="successful insert";

}

catch

{

//handel

}

finally

{

con.Close();

}

rkobs:
SqlCommand cmd =newSqlCommand("INSERT INTO [Shippers] ([CompanyName], [Phone]) VALUES (@.CompanyName, @.Phone)");

Try this:

SqlCommand cmd =newSqlCommand("INSERT INTO [Shippers] ([CompanyName], [Phone]) VALUES (@.CompanyName, @.Phone)",con);

|||

Worked. Thanks.

Ray

sql

Problem with SqlCeRemoteDataAccess.Pull() in Pocket PC 2003 application

hi
I am trying to make a Pocket PC 2003 application which will pull data from a SQL Server 2005 database and store in Mobile database. and Push the table from mobile databse to SQL server 2005 database

I also have a desktop application up and running with SQL server database in which I can enter records to the SQL server 2005 database

I get error when I trigger SqlCeRemoteDataAccess.Pull() method, which says "sql mobile encountered problems when opening the database", (its native error 28559)

when user access the database as anoymous they'll log in as my account
I am working on Windows Vista and have IIS and SQL Server 2005 running on the same machine same machine alongside Visual Studio SP1 for vista
I've also got Windows Mobile Device Centre installed with Virtual PC 2007 Installer which means when I cradle the emulator the Windows Mobile Device Centre detects and connects it and I can internet on that.
but I can't access http://IDVISTA/SQLCE/sqlcesa30.dll (HostURL in the code) in Pocket PC 2003 emulator's Internet explorer (not sure if it counts)

below is my code

Code Snippet

private String LocalConString = "Data Source=Program Files\\PDADairy\\Database\\diary.sdf;Persist Security Info=False;";

private String RemoteConString = "Data Source=IDVISTA;Initial Catalog=Dairy;Integrated Security=SSPI;";

private String HostURL = "http://IDVISTA/SQLCE/sqlcesa30.dll";

public void Pull()
{

// Create a remote data access object

SqlCeRemoteDataAccess RDA = new SqlCeRemoteDataAccess(HostURL, "", "", LocalConString);

try
{
RDA.Pull("Appointments", "SELECT * FROM Appointments ", RemoteConString, RdaTrackOption.TrackingOff, "ErrorPullingAppointments");
}
catch (SqlCeException sqle)
{
for (int i = 0; i < sqle.Errors.Count; i++)
MessageBox.Show(sqle.Errors[i].NativeError.ToString() + "\n" +
sqle.Errors[i].Source);
}
finally
{
RDA.Dispose();

}

Any help will be much appreciated
Thanks
hi
I am trying to make a Pocket PC 2003 application which will pull data from a SQL Server 2005 database and store in Mobile database. and Push the table from mobile databse to SQL server 2005 database

I also have a desktop application up and running with SQL server database in which I can enter records to the SQL server 2005 database

I get error when I trigger SqlCeRemoteDataAccess.Pull() method, which says "sql mobile encountered problems when opening the database", (its native error 28559)

below is my code

Code Snippet

private String LocalConString = "Data Source=Program Files\\PDADairy\\Database\\diary.sdf;Persist Security Info=False;";

private String RemoteConString = "Data Source=IDVISTA;Initial Catalog=Dairy;Integrated Security=SSPI;";

private String HostURL = "http://IDVISTA/SQLCE/sqlcesa30.dll";

Any help will be much appreciated
Thanks

|||

It is vital that you can access the SQL CE agent URL from IE on your device. Try using the IP address of your PC instead of host name. Also, change your connection string to:

"Data Source=\\Program Files\\PDADairy\\Database\\diary.sdf;Persist Security Info=False;";

Try connecting with SQL security instead of integrated security, to see if this is the issue.

Also, there may be problems related to Vista and integrated security, see this:

http://blogs.msdn.com/stevelasker/archive/2007/06/16/using-rda-with-integrated-authentication.aspx

|||

Thanks for the quick reply

I have now managed to access SQL CE agent URL from emulator's IE. (it was my windows firewall blocking incoming requests)

but the problem is still the same I still get the same error even after changing the connection string as you suggested

OK i gotta admit that I am fairly new to this whole thing and dont really know how to connect with sql security (could you specify a bit for me please)

i m reading the blog now, see if there is anything I am missing still out

Cheers

|||

still stuck

tried the idea in the blog too

need more help|||

You must specify a provider in your Remote connection string like this;

Provider=sqloledb;Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;

see http://www.connectionstrings.com/?carrier=sqlserver for more samples.

|||tried that too
still no avail

when it executes the pull(), in the output it says

A first chance exception of type 'System.Data.SqlServerCe.SqlCeException' occurred in System.Data.SqlServerCe.dll

any clues?

Problem with SqlCeRemoteDataAccess.Pull() in Pocket PC 2003 application

I also have a desktop application up and running with SQL server database in which I can enter records to the SQL server 2005 database

I get error when I trigger SqlCeRemoteDataAccess.Pull() method, which says "sql mobile encountered problems when opening the database", (its native error 28559)

below is my code

Code Snippet

private String LocalConString = "Data Source=Program Files\\PDADairy\\Database\\diary.sdf;Persist Security Info=False;";

private String RemoteConString = "Data Source=IDVISTA;Initial Catalog=Dairy;Integrated Security=SSPI;";

private String HostURL = "http://IDVISTA/SQLCE/sqlcesa30.dll";

I also have a desktop application up and running with SQL server database in which I can enter records to the SQL server 2005 database

I get error when I trigger SqlCeRemoteDataAccess.Pull() method, which says "sql mobile encountered problems when opening the database", (its native error 28559)

below is my code

Code Snippet

private String LocalConString = "Data Source=Program Files\\PDADairy\\Database\\diary.sdf;Persist Security Info=False;";

private String RemoteConString = "Data Source=IDVISTA;Initial Catalog=Dairy;Integrated Security=SSPI;";

private String HostURL = "http://IDVISTA/SQLCE/sqlcesa30.dll";

Any help will be much appreciated
Thanks

|||

It is vital that you can access the SQL CE agent URL from IE on your device. Try using the IP address of your PC instead of host name. Also, change your connection string to:

"Data Source=\\Program Files\\PDADairy\\Database\\diary.sdf;Persist Security Info=False;";

Try connecting with SQL security instead of integrated security, to see if this is the issue.

Also, there may be problems related to Vista and integrated security, see this:

http://blogs.msdn.com/stevelasker/archive/2007/06/16/using-rda-with-integrated-authentication.aspx

|||

Thanks for the quick reply

I have now managed to access SQL CE agent URL from emulator's IE. (it was my windows firewall blocking incoming requests)

but the problem is still the same I still get the same error even after changing the connection string as you suggested

OK i gotta admit that I am fairly new to this whole thing and dont really know how to connect with sql security (could you specify a bit for me please)

i m reading the blog now, see if there is anything I am missing still out

Cheers

|||

still stuck

tried the idea in the blog too

need more help|||

You must specify a provider in your Remote connection string like this;

Provider=sqloledb;Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;

see http://www.connectionstrings.com/?carrier=sqlserver for more samples.

|||tried that too
still no avail

when it executes the pull(), in the output it says

A first chance exception of type 'System.Data.SqlServerCe.SqlCeException' occurred in System.Data.SqlServerCe.dll

any clues?

Problem with SqlCeRemoteDataAccess Pull

I also have a desktop application up and running with SQL server database in which I can enter records to the SQL server 2005 database

I get error when I trigger SqlCeRemoteDataAccess.Pull() method, which says "sql mobile encountered problems when opening the database", (its native error 28559)

below is my code

Code Snippet

private String LocalConString = "Data Source=Program Files\\PDADairy\\Database\\diary.sdf;Persist Security Info=False;";

private String RemoteConString = "Data Source=IDVISTA;Initial Catalog=Dairy;Integrated Security=SSPI;";

private String HostURL = "http://IDVISTA/SQLCE/sqlcesa30.dll";

I also have a desktop application up and running with SQL server database in which I can enter records to the SQL server 2005 database

I get error when I trigger SqlCeRemoteDataAccess.Pull() method, which says "sql mobile encountered problems when opening the database", (its native error 28559)

below is my code

Code Snippet

private String LocalConString = "Data Source=Program Files\\PDADairy\\Database\\diary.sdf;Persist Security Info=False;";

private String RemoteConString = "Data Source=IDVISTA;Initial Catalog=Dairy;Integrated Security=SSPI;";

private String HostURL = "http://IDVISTA/SQLCE/sqlcesa30.dll";

Any help will be much appreciated
Thanks

|||

It is vital that you can access the SQL CE agent URL from IE on your device. Try using the IP address of your PC instead of host name. Also, change your connection string to:

"Data Source=\\Program Files\\PDADairy\\Database\\diary.sdf;Persist Security Info=False;";

Try connecting with SQL security instead of integrated security, to see if this is the issue.

Also, there may be problems related to Vista and integrated security, see this:

http://blogs.msdn.com/stevelasker/archive/2007/06/16/using-rda-with-integrated-authentication.aspx

|||

Thanks for the quick reply

I have now managed to access SQL CE agent URL from emulator's IE. (it was my windows firewall blocking incoming requests)

but the problem is still the same I still get the same error even after changing the connection string as you suggested

OK i gotta admit that I am fairly new to this whole thing and dont really know how to connect with sql security (could you specify a bit for me please)

i m reading the blog now, see if there is anything I am missing still out

Cheers

|||

still stuck

tried the idea in the blog too

need more help|||

You must specify a provider in your Remote connection string like this;

Provider=sqloledb;Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;

see http://www.connectionstrings.com/?carrier=sqlserver for more samples.

|||tried that too
still no avail

when it executes the pull(), in the output it says

A first chance exception of type 'System.Data.SqlServerCe.SqlCeException' occurred in System.Data.SqlServerCe.dll

any clues?

Problem with SqlCacheDependency

I've set up a SqlCacheDependency in my Asp.Net application, but the dependency invalidates the cache immediately every time the page is hit.

I think the problem may be with my SQL Query, but it seems to me to meet the requirements on the Special Considerations When Using Query Notifications page on MSDN. Could someone take a look at this query and tell me if I've done something wrong? Thank you.

Here's my query:

Code Snippet

USE [chameleon]

SET ANSI_NULLS ON

SET ANSI_PADDING ON

SET ANSI_WARNINGS ON

SET CONCAT_NULL_YIELDS_NULL ON

SET QUOTED_IDENTIFIER ON

SET NUMERIC_ROUNDABORT OFF

SET ARITHABORT ON

ALTER PROCEDURE [dbo].[usp_customers_by_site_id]

@.site_id INT

SELECT

customers.customer_id,

customers.name,

customers.po_prefix,

customers.dt_created,

customers.created_by AS auid,

customers.po_required

FROM dbo.customers

WHERE customers.site_id = @.site_id

AND customers.is_active = 1

and here's the code in my Asp.Net site where I try to use the SqlCacheDependency:

Code Snippet

public List<Customer> GetCustomersBySite(int siteID)

{

List<Customer> customers = new List<Customer>();

if (HttpRuntime.Cache["CustomersBySite" + siteID] != null){

customers = (List<Customer>)HttpRuntime.Cache["CustomersBySite" + siteID];

}

else

{

using (SqlCommand command = new SqlCommand("usp_customers_by_site_id", Connection)){

command.CommandType = CommandType.StoredProcedure;

command.Parameters.Add("@.site_id", SqlDbType.Int).Value = GetDBValue(siteID, false);

SqlCacheDependency dependency = new SqlCacheDependency(command);

try{

Connection.Open();

SqlDataReader reader;

reader = command.ExecuteReader(CommandBehavior.SingleResult);

while (reader.Read()){

customers.Add(PopulateCustomer(reader));

}

HttpRuntime.Cache.Insert("CustomersBySite" + siteID, customers, dependency, DateTime.Now.AddMinutes(Configuration.CacheDuration), System.Web.Caching.Cache.NoSlidingExpiration);

}

finally{

Connection.Close();

}

return customers;

}

My apologies. I posted this in the wrong forum.

-Brad

Problem with SQLCacheDependency

I am using a grid to display the data on my webpage, and I have 3 projects in my solution (UI, BLL, DAL). When I load the webpage I am creating the instance of a class written n my BLL project to populate the data, and in BLL I'm creating the object for the class in DAL and returning the datatable.

Now when I refresh the page using F5, the SQLDependency is working fine. But, when I use the Paging or Sorting option's on my grid I see in SQL Profiler that the query is posted back to the SQLServer to get the data.

If this is the senario then how can I make use of the cache object in .NET 2.0.

Please advice,
Ravi

I've posted a blog article that tries to explain some of the misteries behind Query Notifications here: http://blogs.msdn.com/remusrusanu/archive/2006/06/17/635608.aspx, maybe it can help you troubleshoot the issue.

I'm relly no expert in the SqlDependnecy functionality, but the explanation you give seems way too short to understand what the problem is.

HTH,
~ Remus

sql

Problem with SQLBindParameter

I am developing an application, using ODBC, that needs to call a stored procedure in an SQL Server DBMS that has a mix of INPUT and INPUT_OUTPUT parameters. I have the code so that there aren't any errors returned from the function calls but I do not see the bound data change after the SQLExecute() function.

Code snippets follow.

CHAR szStatement[256];

CHAR szBuf[256];

SQLINTEGER irval = 0, filenum = 808042, DoNotCall = 0;
SQLVARCHAR vcPhoneNumber[PHONE_LEN];
SQLVARCHAR vcInstanceCode[INSTANCE_LEN] = {0x20};
SQLVARCHAR vcReason[REASON_LEN] = {0x20};
SQLINTEGER rvalLen = 0, fileLen = 0, noCallLen = 10, phoneLen = SQL_NTS, instanceLen = SQL_NTS, reasonLen = SQL_NTS;

lstrcpy( szStatement, "exec ?= some_proc ?, ?, ?, ?, ?" );

sqlr = SQLPrepare( hStmt, szStatement, lstrlen( szStatement ) );
sqlr = SQLBindParameter( hStmt, 1, SQL_PARAM_OUTPUT, SQL_C_LONG, SQL_INTEGER, 10, 0, &irval, 0, &rvalLen );
sqlr = SQLBindParameter( hStmt, 2, SQL_PARAM_INPUT, SQL_C_LONG, SQL_INTEGER, 10, 0, &filenum, 0, &fileLen );
sqlr = SQLBindParameter( hStmt, 3, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, 10, 0, vcPhoneNumber, 10, &phoneLen );
sqlr = SQLBindParameter( hStmt, 4, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_VARCHAR, 50, 0, vcInstanceCode, 0, &instanceLen );
sqlr = SQLBindParameter( hStmt, 5, SQL_PARAM_INPUT_OUTPUT, SQL_C_LONG, SQL_INTEGER, 1, 0, &DoNotCall, 0, &noCallLen );
sqlr = SQLBindParameter( hStmt, 6, SQL_PARAM_INPUT_OUTPUT, SQL_C_CHAR, SQL_VARCHAR, 250, 0, vcReason, 0, &reasonLen );
sqlr = SQLExecute( hStmt );
if( sqlr == SQL_SUCCESS || sqlr == SQL_SUCCESS_WITH_INFO )

{
sprintf( szBuf, "The return value is %d", irval );
sprintf( szBuf, "The file number is %d", filenum );
sprintf( szBuf, "The phone number is %s", vcPhoneNumber );
sprintf( szBuf, "The instance code is %s", vcInstanceCode );
sprintf( szBuf, "The do not call value is %d", DoNotCall );
sprintf( szBuf, "The reason is %s", vcReason );
}

The problem is that in each of the function calls, the sqlr == SQL_SUCCESS but after the call to SQLExecute(), the data does not change for the output parameters. I can do something similar in other query tools that show the proper values but I am not getting the chanes in my bound variables. I tried adding a call to SQLParamData() but I was having a "Function sequence error" problem.

Any help would be very much appreciated. Thanks in advance.

Hi,

Here're a couple of ideas:

== The behavior of the output parameter may vary based on the actual stored proc, which you are trying to execute. To make sure your parameter description is precise, try using SQLProcedureColumns and examine the types.

== As per MSDN:

If the InputOutputType argument is SQL_PARAM_INPUT_OUTPUT or SQL_PARAM_OUTPUT, ParameterValuePtr points to a buffer in which the driver returns the output value. If the procedure returns one or more result sets, the *ParameterValuePtr buffer is not guaranteed to be set until all result sets/row counts have been processed. If the buffer is not set until processing is complete, the output parameters and return values are unavailable until SQLMoreResults returns SQL_NO_DATA. Calling SQLCloseCursor or SQLFreeStmt with an Option of SQL_CLOSE will cause these values to be discarded.

So what you could do is to fetch the results, call SQLMoreResults appropriately and see if the output parameter will be produced as expected. Also - don't forget the "SET NOCOUNT ON", you might get count tokens as separate resultsets.

HTH,

Jivko Dobrev - MSFT
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

At first I was having a problem with syntax errors and some other things that didn't make sense to me. I did some poking around and found that the signature I was given whas different from the signature that was defined in the data. I corrected this and was able to remove the errors. I tried calling SQLFetch() and was receiving an error of "Function sequense error." I can try again. The stored procedure does not return a result set. The return to me is throug a return code, the first parameter, and two in/out parameters, the last two.

I am connecting to someone else's system and they have defined the stored procedure and I don't have any control over how they've defined it.

The SQLExecute is returning SQL_SUCCESS_WITH_INFO and this message. The state is "01000" the native error is "0 The message is: [Microsoft][ODBC SQL Server Driver][SQL Server] The 01000 state is a generic message and that message sure looks generic. :)

I added a call to SQLFetch() after the SQLExecute() and the this is the result. The state is "HY010" the native error is "0 The message is: [Microsoft][ODBC SQL Server Driver]Function sequence error

I'm at a loss as to where to go now.

|||

Hi,

Would you be able to script the stored proc and the related tables/objects and post the script here? If it's not confidential or overly complicated, of course.

I have a suspicion about what you mentioned regarding the procedure not returning any rows. BTW - did you try the SQLMoreResults? Maybe you have multiple resultsets?

Thanks,

Jivko Dobrev - MSFT
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

The stored procedure does not return a result set. I don't think SQLMoreResults() would help.

The body, from a snippet I've been given looks something like this:

/*IsOKToCall Procedure*/
CREATE PROCEDURE IsOKToCall
@.FileNumber int,
@.PhoneNumber char(10),
@.Instance varchar(50),
@.DoNotCall int OUTPUT,
@.DoNotCallReason varchar(255) OUTPUT
AS

SELECT @.Instance = ISNULL(@.Instance, 'master')

-- TODO implement this method to return a non-zero in @.DoNotCall if the account should not be contacted.

SELECT @.DoNotCall = 0
SELECT @.DoNotCallReason = ''
Return @.@.Error

Note that this is not the actual procedure. The person that created it has some PRINT statements in the body to show some debug values and there is logic to return different results based on the value passed in the FileNumber parameter. If I change the first parameter to SQL_PARAMETER_RETURN, I get an error when trying to bind the column. The live procedure also defines the OUTPUT parameters as INPUT_OUTPUT.

|||

An update:

I installed SQL Express so I can see what is going on with both sides of the issue. Some of the error message I thought I had were messages from the "print ......" statements the developers put in thier stored procedures. If I call SQLFetch() after the statement execution, I always have an error of "Function Sequense Error" so I don't think that is why I am not getting values back.

Isn't SQLBindParameter() supposed to bind the parameter in the driver so that when the values are returned, they are also changed in the bound parameters? Is there something else I must do to the the correct values?

|||

Someone explain this one to me. The statement being called is "exec ?= IsOkToCall ?, ?, ?, ?, ?" as far and myself and the driver are concerned, there are six parameters. The first one is bound to an INTEGER to receive the @.@.Error value. the second is the ID to search for and is an input type. The third one is a char type and an input parameter. The fourth is a varchar input type. The fifth is an integer and is input_output type to get a result value for logic. And the sixth is a varchar input_output used to recieve the message string. After getting all of the messages from the driver, the sixth parameter is being updated with the value that was set for the fifth result code variable. Changing the parameter numbers to see if there is an off by one error does not fix it.

What should I be looking for for this issue?

|||

Robert,

Do not change the parameter numbers but you should try to call SQLMoreResults until you hit SQL_NO_DATA_FOUND and then see if the output parameters are updated or not. I believe print statements are treated as results and thus have to be flushed before getting to output parameters.

Thanks

Waseem

|||I found that out the hard way. It is after gathering all of the data that I am seeing the mixed up returns. The first parameter that is the return value from the procedure is never set and I still have the wrong value being updated with the integer value and it does not change even after I remove all of the print statements from the stored procedure.|||

Waseem Basheer - MSFT wrote:

Robert,
Do not change the parameter numbers but you should try to call SQLMoreResults until you hit SQL_NO_DATA_FOUND and then see if the output parameters are updated or not. I believe print statements are treated as results and thus have to be flushed before getting to output parameters.
Thanks
Waseem

I found this to be the case and was doing so. I also found where I was setting the destination to the address of a pointer to a character array rather than sending the pointer to the character array. I also found this whole thing to be increadably finicky and full of poorly/non documented quirks. I have the IN/OUT parmeters working but the return code fom a call of "exec ?= my_proc ?, ?, ?" still illudes me. I am still not getting the value for the ?= parameter but I don't think It is too important for me to get this value.

Problem with SQLAgent Running SSIS Package "...package execution failed..."

I am trying to run an SSIS package from the SQL Agent. I am able to execute the package manually from VS2005 and SQL Server. When I try to run the package from the agent, I get the error "The package execution failed. The step failed." VS2005, SQL Server, and the SQL Server Agent are all installed on my local machine and running from the same account. I only have one step in the job and it doesn't include any scripts. Are there any permissions I need to set for the agent? What am I missing?

-Stephen

Are you able to run the package successfully using DTExec ? If so , what is the context under which you are executing this . Please try with the same account for SQLAgent service and give a try, it should work. If not then you may want to use logging feature of SSIS package to get more appropriate error message, SQLAgent does not provide good error logging w.r.t to SSIS job steps.

Thanks,

Gops Dwarak

|||Delete the table you created with Import/Export utility and then run the job again. This worked for me.|||

Its seems like you haven't installed Integration services.

Install Integration services , restart the SQL agent and try to execute that package

Thanks,

Q_A

|||I am having the same problem. This did not work for me. the package runs for me manually but randomly fails when scheduled. Your solution did not work for me.|||

Hey Even I was facing the same problem but i could solve it .. Just with the few steps below:

1. Go to SQL server Management Studio. under Security ->Credentials->Create New Credential. Give any Credential Name e.g 'Job Account' . Fill your own Windows account in identity column. domain\account. Password Give ur own passowrd

2. After creating 'JobAccount' as Credential Goto SQLServerAgent->Proxies. Create a new proxy. Give any proxy name.e.g give 'JobProxy' . Credential should be the one which u created in the above step.Here in this case it is JobAccount'. In subsystems. Check Sql Server Integration Servive Package.

3. Now when you create a job it should run under 'Jobproxy' instead of 'SQL Agent Service account'.

Here U go... Your job is successful.

Any questions please let me know.

|||

I'm having the same problem, and I did what you suggesting above but it did not work. I still get the same error.

Please, help me.

|||

Does your window account have sufficient rights? If not try giving full rights to your account and try again.

|||

I added domain admin privilege to my account and still the same error. Oh the world of DTS was so much simpler...

|||

UPDATE... I did 2 things and my Packages magically started running properly... I set all of the SQL Server Services back to using the builtin Service accounts (they were set as a Domain User service account) and I installed SP2.

REM7600

|||I'm having the same issues as well, looks like we'll have to try SP2. We have one domain account that runs everything, Integration Services, SQL Agent, and SQL Server. We created the package with that user and the job is owned by that user as well and it still fails, I don't understand that at all. A great and simple product was unnecessarily complicated and now it's a mess.|||

I am getting the same error and cannot manage to get around it.

I have a job that simply imports data from an Informix Db using a ODBC connection string (I have also tried a simpler package, but am getting the same error).

The package runs fine when executed in SQL server BID and when executed manually.

I have created a proxy user and appropriate credentials however I still get the error >

The process could not be created for step 1 of job # (reason: A required privilege is not held by the client). The step failed.

The account in question is running as an uber-admin (literally every box is ticked), it is the dbo of the database I am trying to upload to and it has a role within the msdb database with appropriate permissions (it was also the creator of the package)

Looked through here > http://support.microsoft.com/kb/918760 without success.

EDIT - I have also tried executing the step with the job as the SQL agent service but I get the "The package execution failed. The step failed" error when you try execute a package as a different login to what the package was created under.

Any help would be much appreciated.

|||

I also have the same error and cannot find a solution. I have done a lot of searching, and have found that Microsoft has done as little as possible in the way of providing solid, step-by-step guides for creating identities, credentials, and proxies for use with SQLAgent.

My SQL services are running under the same active directory account. My credential uses a different ad account. I've even tried adding these accounts to the local admins group with no success.

Executed as user: ADS\hnetsql2. The process could not be created for step 1 of job 0x27F5B898EE348A4DB17838696B2E1CD0 (reason: A required privilege is not held by the client). The step failed.

Which privilege?

Could this have to do with Kerberos delegation? I do not manage the active directory. However, I can see that my server has "Do not trust this server for delegation" checked under the Properties | Delegation tab. I get an Access Denied error from AD if I try to change this setting.

-Tony

|||

Hi! If you follow Abha's instructions from 03-01-2007 you can fix this problem! I finally managed to get it to work after months of trying to fix it. By selecting SQL Server Agent->Jobs->[the job] open it's properties and find the steps that do not work. Edit the step and change where it says "run as" to "JobProxy" or whatever else you've called in "ON EVERY PACKAGE THAT WILL NOT SUCCESSFULLY COMPLETE UNDER THE SYSTEM LOGIN". Save outta everything and run your Job. It should work! Note: I when i first tried it, i did not change all the packages to run under the new proxy and it failed.

Friday, March 30, 2012

problem with SSIS package

Problem with SSIS and auto-increment of an ID

problem with srever 2005 and Oracle

Problem with SQLXML BulkLoad from .NET Application

Problem with SQLXML BulkLoad from .NET Application

Problem with SQLXML BulkLoad from .NET Application

problem with SQLSTATE

problem with sqlserver 2005 express

problem with sqlserver 2005 express

problem with sqlserver 2005 express

problem with sqlserver 2005 express

problem with sqlserver 2005 express

problem with SQL-Server 2005 CTP

Problem with SQLServer 2000

Problem with SqlParameterCollection - Looking for advice

Problem with SQLH2

Problem with sqlexpress

ERROR [42000] [Microsoft][ODBC SQL Server Driver][SQLServer]Cannot open database "UserAppSample" requested by the login. Thelogin failed.

Problem with SqlDataSource.Insert() using MasterPage

Problem with SqlDataSource using sub-query and date as parameters

problem with SQLConnection

Problem with SqlCeRemoteDataAccess.Pull() in Pocket PC 2003 application

Problem with SqlCeRemoteDataAccess.Pull() in Pocket PC 2003 application

Problem with SqlCeRemoteDataAccess Pull

Problem with SqlCacheDependency

Problem with SQLCacheDependency

Problem with SQLBindParameter

Problem with SQLAgent Running SSIS Package "...package execution failed..."

PAGE HEADER

Blog Archive

About Me