Showing posts with label application. Show all posts

Friday, March 30, 2012

Problems linking Access to SQL replicated tables

Hello!
I was wondering if anyone could help me.
I am running an Access application that uses linked tables to connect to a SQL server.
Ever since I turned on replication on those tables, I cannot add records to the table using regular Access forms (and, for that matter, I cannot do it by just double-clicking on the table from Access and trying to enter records).

Any help will be greatly appreciated!!!!!Have you also refreshed those links i.e.(deleted the links and recreated them in Access) since you "turned on replication on those tables" (or not)?|||Yes, I have deleted the link and recreated it after turning on replication, but I still get the same error every time I add a record.|||You are able to add a row via query analyzer or EM, and the table is not a read only subscribed table you are attempting to add rows to? (just asking the obvious things)
If all that is in order you might check to see if the generated link schemas include columns that the system automatically populates with data. If there are some (replication related columns) or others such as identity, GUID, timestamp, etc. columns (that are automatically populated), try removing them from the Access link schemas (not the base tables) then recreate your link(s) and attempt a test row insert. If that does not work try creating a view (test insertion in QA / EM), then create a linked table to the view and test its behavior.sql

Problems ith Connection string to SQL Express

My application is usningtraditional connection string:

addkey="DbConnection"value="driver={SQL Native Driver}; provider=SQLNCLI;server=localhost;trusted_connection=yes;database=ebay" />

It works fine with database attached to SQL Server, how do I make this working with SQL Express attachable file in data directory?

I have tried this, but it does not work:

Provider=SQLNCLI;Server=RAF001\SQLExpress;AttachDbFilename=..\TestSite\App_Data\ebay.mdf;Database=ebay;Trusted_Connection=Yes;

Problems installing MSDE with the Server Service disabled

Hello ;
We are having problems installing our desktop Application at a
customer's site where the policy is to disable the Server Service.
Being a desktop configuration, our installation places MSDE on the
same PC as the rest of the application. Unfortunately the
installation will not succeed unless the Server Service is enabled.
Interestingly, we can disable the Server Service after installation
and the Application will still function normally. However, enabling
the Server Service --even just during installation-- would violate our
customer's network policy.
It is my understanding that Server Service is not required if your
references are confined to the local system. Therefore my question is
: Is there a way to install MSDE or Standard Edition on the local
system when the Server Service is disabled?
Thanks in advance for any feedback,
BobThe following article has a fix for the problem you are experiencing:
http://support.microsoft.com/?id=829386.
You will need to contact Microsft support to get the fix.
Rand
This posting is provided "as is" with no warranties and confers no rights.sql

Wednesday, March 28, 2012

Problems in Search opertion using Like

Hi,
In our application we have a search page where people can search based on
different fields some of our fields support all chars like %, _ etc.,
We have to perform a like search on this field.
If the users typed % or _ in the text field then as usual its
considering it as wild card charecters.
Is there any way solve the problems like this.
Thanks & Regards,
Prasad DannaniTrap for searches on % and _. Replace these characters with a token like
PERCENT and UNDERSCORE. Modify your content so all instances of % and _ are
also replaced by PERCENT and UNDERSCORE. Then in your application have it
render PERCENT as % and UNDERSCORE as _.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Prasad Dannani" <prasad@.pennywise.com> wrote in message
news:eQAbRGwoFHA.1372@.TK2MSFTNGP10.phx.gbl...
> Hi,
> In our application we have a search page where people can search based
on
> different fields some of our fields support all chars like %, _ etc.,
> We have to perform a like search on this field.
> If the users typed % or _ in the text field then as usual its
> considering it as wild card charecters.
> Is there any way solve the problems like this.
> Thanks & Regards,
> Prasad Dannani
>
>
>|||Use the ESCAPE clause for LIKE. I.e.escape the special craracter with a spec
ial character (specified
in the ESCAPE clause). Find all rows with text containing string "30% cheap"
:
SELECT *
FROM
(
SELECT 'It is 30% cheaper' AS a
UNION
SELECT 'it costs 30 bucke'
UNION
SELECT 'it costs 50 bucke'
) AS i
WHERE a LIKE '%30!% cheap%' ESCAPE '!'
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Prasad Dannani" <prasad@.pennywise.com> wrote in message
news:eQAbRGwoFHA.1372@.TK2MSFTNGP10.phx.gbl...
> Hi,
> In our application we have a search page where people can search based o
n
> different fields some of our fields support all chars like %, _ etc.,
> We have to perform a like search on this field.
> If the users typed % or _ in the text field then as usual its
> considering it as wild card charecters.
> Is there any way solve the problems like this.
> Thanks & Regards,
> Prasad Dannani
>
>
>|||Hilary wrote on Wed, 17 Aug 2005 05:22:21 -0400:

> Trap for searches on % and _. Replace these characters with a token like
> PERCENT and UNDERSCORE. Modify your content so all instances of % and _
> are also replaced by PERCENT and UNDERSCORE. Then in your application have
> it render PERCENT as % and UNDERSCORE as _.
How about using the ESCAPE keyword or bracketing? eg.
SELECT * FROM MyTable WHERE MyColumn LIKE '45/%' ESCAPE '/'
SELECT * FROM MyTable WHERE MyColumn LIKE '45[%]'
Those will look for the string 45% rather than all strings starting with 45.
That way you don't have to mess around with token replacement.
Dan
[vbcol=seagreen]
> "Prasad Dannani" <prasad@.pennywise.com> wrote in message
> news:eQAbRGwoFHA.1372@.TK2MSFTNGP10.phx.gbl...
> on|||Hi Dan,
Your Query ( SELECT * FROM MyTable WHERE MyColumn LIKE '45[%]')
worked but it clearly says sql server like operator is always having
problems with one or the other charecter.
So, i was decided to make use of left function in the where clause
on for using Like which is not good performance wise.
We tries your first Query but its not worked for us.
In c or other languages we have proper escape charecters. I don't
why is it not there for sql server.
Once Again Thanks for Helping Us,
Prasad Dannani.
"Daniel Crichton" <msnews@.worldofspack.co.uk> wrote in message
news:%23%23umhYxoFHA.2580@.TK2MSFTNGP09.phx.gbl...
> Hilary wrote on Wed, 17 Aug 2005 05:22:21 -0400:
>
have[vbcol=seagreen]
> How about using the ESCAPE keyword or bracketing? eg.
> SELECT * FROM MyTable WHERE MyColumn LIKE '45/%' ESCAPE '/'
> SELECT * FROM MyTable WHERE MyColumn LIKE '45[%]'
> Those will look for the string 45% rather than all strings starting with
45.
> That way you don't have to mess around with token replacement.
> Dan
>
based[vbcol=seagreen]
>|||Did you read my post? Read it and you see how you escape a LIKE clause.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Prasad Dannani" <prasad@.pennywise.com> wrote in message
news:ejirWb8oFHA.1968@.TK2MSFTNGP14.phx.gbl...
> Hi Dan,
> Your Query ( SELECT * FROM MyTable WHERE MyColumn LIKE '45[%]')
> worked but it clearly says sql server like operator is always having
> problems with one or the other charecter.
> So, i was decided to make use of left function in the where clause
> on for using Like which is not good performance wise.
> We tries your first Query but its not worked for us.
> In c or other languages we have proper escape charecters. I don't
> why is it not there for sql server.
> Once Again Thanks for Helping Us,
> Prasad Dannani.
>sql

Problems in Search opertion using Like

Hi,
In our application we have a search page where people can search based on
different fields some of our fields support all chars like %, _ etc.,
We have to perform a like search on this field.
If the users typed % or _ in the text field then as usual its
considering it as wild card charecters.
Is there any way solve the problems like this.
Thanks & Regards,
Prasad DannaniTrap for searches on % and _. Replace these characters with a token like
PERCENT and UNDERSCORE. Modify your content so all instances of % and _ are
also replaced by PERCENT and UNDERSCORE. Then in your application have it
render PERCENT as % and UNDERSCORE as _.
--
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Prasad Dannani" <prasad@.pennywise.com> wrote in message
news:eQAbRGwoFHA.1372@.TK2MSFTNGP10.phx.gbl...
> Hi,
> In our application we have a search page where people can search based
on
> different fields some of our fields support all chars like %, _ etc.,
> We have to perform a like search on this field.
> If the users typed % or _ in the text field then as usual its
> considering it as wild card charecters.
> Is there any way solve the problems like this.
> Thanks & Regards,
> Prasad Dannani
>
>
>|||Use the ESCAPE clause for LIKE. I.e.escape the special craracter with a special character (specified
in the ESCAPE clause). Find all rows with text containing string "30% cheap":
SELECT *
FROM
(
SELECT 'It is 30% cheaper' AS a
UNION
SELECT 'it costs 30 bucke'
UNION
SELECT 'it costs 50 bucke'
) AS i
WHERE a LIKE '%30!% cheap%' ESCAPE '!'
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Prasad Dannani" <prasad@.pennywise.com> wrote in message
news:eQAbRGwoFHA.1372@.TK2MSFTNGP10.phx.gbl...
> Hi,
> In our application we have a search page where people can search based on
> different fields some of our fields support all chars like %, _ etc.,
> We have to perform a like search on this field.
> If the users typed % or _ in the text field then as usual its
> considering it as wild card charecters.
> Is there any way solve the problems like this.
> Thanks & Regards,
> Prasad Dannani
>
>
>|||Hilary wrote on Wed, 17 Aug 2005 05:22:21 -0400:
> Trap for searches on % and _. Replace these characters with a token like
> PERCENT and UNDERSCORE. Modify your content so all instances of % and _
> are also replaced by PERCENT and UNDERSCORE. Then in your application have
> it render PERCENT as % and UNDERSCORE as _.
How about using the ESCAPE keyword or bracketing? eg.
SELECT * FROM MyTable WHERE MyColumn LIKE '45/%' ESCAPE '/'
SELECT * FROM MyTable WHERE MyColumn LIKE '45[%]'
Those will look for the string 45% rather than all strings starting with 45.
That way you don't have to mess around with token replacement.
Dan
> "Prasad Dannani" <prasad@.pennywise.com> wrote in message
> news:eQAbRGwoFHA.1372@.TK2MSFTNGP10.phx.gbl...
>> Hi,
>> In our application we have a search page where people can search based
> on
>> different fields some of our fields support all chars like %, _ etc.,
>> We have to perform a like search on this field.
>> If the users typed % or _ in the text field then as usual its
>> considering it as wild card charecters.
>> Is there any way solve the problems like this.
>> Thanks & Regards,
>> Prasad Dannani|||Hi Dan,
Your Query ( SELECT * FROM MyTable WHERE MyColumn LIKE '45[%]')
worked but it clearly says sql server like operator is always having
problems with one or the other charecter.
So, i was decided to make use of left function in the where clause
on for using Like which is not good performance wise.
We tries your first Query but its not worked for us.
In c or other languages we have proper escape charecters. I don't
why is it not there for sql server.
Once Again Thanks for Helping Us,
Prasad Dannani.
"Daniel Crichton" <msnews@.worldofspack.co.uk> wrote in message
news:%23%23umhYxoFHA.2580@.TK2MSFTNGP09.phx.gbl...
> Hilary wrote on Wed, 17 Aug 2005 05:22:21 -0400:
> > Trap for searches on % and _. Replace these characters with a token like
> > PERCENT and UNDERSCORE. Modify your content so all instances of % and _
> > are also replaced by PERCENT and UNDERSCORE. Then in your application
have
> > it render PERCENT as % and UNDERSCORE as _.
> How about using the ESCAPE keyword or bracketing? eg.
> SELECT * FROM MyTable WHERE MyColumn LIKE '45/%' ESCAPE '/'
> SELECT * FROM MyTable WHERE MyColumn LIKE '45[%]'
> Those will look for the string 45% rather than all strings starting with
45.
> That way you don't have to mess around with token replacement.
> Dan
> > "Prasad Dannani" <prasad@.pennywise.com> wrote in message
> > news:eQAbRGwoFHA.1372@.TK2MSFTNGP10.phx.gbl...
> >> Hi,
> >>
> >> In our application we have a search page where people can search
based
> > on
> >> different fields some of our fields support all chars like %, _ etc.,
> >>
> >> We have to perform a like search on this field.
> >>
> >> If the users typed % or _ in the text field then as usual its
> >> considering it as wild card charecters.
> >>
> >> Is there any way solve the problems like this.
> >>
> >> Thanks & Regards,
> >>
> >> Prasad Dannani
> >>
>|||Did you read my post? Read it and you see how you escape a LIKE clause.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Prasad Dannani" <prasad@.pennywise.com> wrote in message
news:ejirWb8oFHA.1968@.TK2MSFTNGP14.phx.gbl...
> Hi Dan,
> Your Query ( SELECT * FROM MyTable WHERE MyColumn LIKE '45[%]')
> worked but it clearly says sql server like operator is always having
> problems with one or the other charecter.
> So, i was decided to make use of left function in the where clause
> on for using Like which is not good performance wise.
> We tries your first Query but its not worked for us.
> In c or other languages we have proper escape charecters. I don't
> why is it not there for sql server.
> Once Again Thanks for Helping Us,
> Prasad Dannani.
>

Problems in Search opertion using Like

Hi,
In our application we have a search page where people can search based on
different fields some of our fields support all chars like %, _ etc.,
We have to perform a like search on this field.
If the users typed % or _ in the text field then as usual its
considering it as wild card charecters.
Is there any way solve the problems like this.
Thanks & Regards,
Prasad Dannani
Trap for searches on % and _. Replace these characters with a token like
PERCENT and UNDERSCORE. Modify your content so all instances of % and _ are
also replaced by PERCENT and UNDERSCORE. Then in your application have it
render PERCENT as % and UNDERSCORE as _.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Prasad Dannani" <prasad@.pennywise.com> wrote in message
news:eQAbRGwoFHA.1372@.TK2MSFTNGP10.phx.gbl...
> Hi,
> In our application we have a search page where people can search based
on
> different fields some of our fields support all chars like %, _ etc.,
> We have to perform a like search on this field.
> If the users typed % or _ in the text field then as usual its
> considering it as wild card charecters.
> Is there any way solve the problems like this.
> Thanks & Regards,
> Prasad Dannani
>
>
>
|||Use the ESCAPE clause for LIKE. I.e.escape the special craracter with a special character (specified
in the ESCAPE clause). Find all rows with text containing string "30% cheap":
SELECT *
FROM
(
SELECT 'It is 30% cheaper' AS a
UNION
SELECT 'it costs 30 bucke'
UNION
SELECT 'it costs 50 bucke'
) AS i
WHERE a LIKE '%30!% cheap%' ESCAPE '!'
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Prasad Dannani" <prasad@.pennywise.com> wrote in message
news:eQAbRGwoFHA.1372@.TK2MSFTNGP10.phx.gbl...
> Hi,
> In our application we have a search page where people can search based on
> different fields some of our fields support all chars like %, _ etc.,
> We have to perform a like search on this field.
> If the users typed % or _ in the text field then as usual its
> considering it as wild card charecters.
> Is there any way solve the problems like this.
> Thanks & Regards,
> Prasad Dannani
>
>
>
|||Hilary wrote on Wed, 17 Aug 2005 05:22:21 -0400:

> Trap for searches on % and _. Replace these characters with a token like
> PERCENT and UNDERSCORE. Modify your content so all instances of % and _
> are also replaced by PERCENT and UNDERSCORE. Then in your application have
> it render PERCENT as % and UNDERSCORE as _.
How about using the ESCAPE keyword or bracketing? eg.
SELECT * FROM MyTable WHERE MyColumn LIKE '45/%' ESCAPE '/'
SELECT * FROM MyTable WHERE MyColumn LIKE '45[%]'
Those will look for the string 45% rather than all strings starting with 45.
That way you don't have to mess around with token replacement.
Dan
[vbcol=seagreen]
> "Prasad Dannani" <prasad@.pennywise.com> wrote in message
> news:eQAbRGwoFHA.1372@.TK2MSFTNGP10.phx.gbl...
> on
|||Hi Dan,
Your Query ( SELECT * FROM MyTable WHERE MyColumn LIKE '45[%]')
worked but it clearly says sql server like operator is always having
problems with one or the other charecter.
So, i was decided to make use of left function in the where clause
on for using Like which is not good performance wise.
We tries your first Query but its not worked for us.
In c or other languages we have proper escape charecters. I don't
why is it not there for sql server.
Once Again Thanks for Helping Us,
Prasad Dannani.
"Daniel Crichton" <msnews@.worldofspack.co.uk> wrote in message
news:%23%23umhYxoFHA.2580@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Hilary wrote on Wed, 17 Aug 2005 05:22:21 -0400:
have
> How about using the ESCAPE keyword or bracketing? eg.
> SELECT * FROM MyTable WHERE MyColumn LIKE '45/%' ESCAPE '/'
> SELECT * FROM MyTable WHERE MyColumn LIKE '45[%]'
> Those will look for the string 45% rather than all strings starting with
45.[vbcol=seagreen]
> That way you don't have to mess around with token replacement.
> Dan
based
>
|||Did you read my post? Read it and you see how you escape a LIKE clause.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Prasad Dannani" <prasad@.pennywise.com> wrote in message
news:ejirWb8oFHA.1968@.TK2MSFTNGP14.phx.gbl...
> Hi Dan,
> Your Query ( SELECT * FROM MyTable WHERE MyColumn LIKE '45[%]')
> worked but it clearly says sql server like operator is always having
> problems with one or the other charecter.
> So, i was decided to make use of left function in the where clause
> on for using Like which is not good performance wise.
> We tries your first Query but its not worked for us.
> In c or other languages we have proper escape charecters. I don't
> why is it not there for sql server.
> Once Again Thanks for Helping Us,
> Prasad Dannani.
>

problems in Hands-On-Lab, MED302

Hi,

I am new to Windows Mobile. I am trying to follow the expample, MED302: Developing an SQL Mobile Application with Visual Studio 2005 and SQL Server 2005. The problem happend when I reached Exercise 4: Create a Subscription. After task 1 and 2, I typed http://yunan/sqlmobile/sqlcesa30.dll in IE. But I got "The page cannot be displayed" message and "HTTP 500 Internal server error". I am sure the IIS is running. Is there any other settings I need to do? If I continue to create subscription, I will get the following error message.

"
TITLE: Microsoft SQL Server Management Studio

A call to SQL Server Reconciler failed. Try to resynchronize.
HRESULT 0x80004005 (29006)

The schema script 'C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\ReplData\unc\YUNAN_SQLMOBILE_SQLMOBILE\20061018144997\CustomerData_2.sch' could not be propagated to the subscriber.
HRESULT 0x80070005 (0)

The merge process was unable to deliver the snapshot to the Subscriber. If using Web synchronization, the merge process may have been unable to create or write to the message file. When troubleshooting, restart the synchronization with verbose history logging and specify an output file to which to write.
HRESULT 0x80045017 (0)
"
Many thanks in advance.

Yunan

Yunan,

Have you got this solved? I'm having the same error message and couldn't find a solution.

Eva

|||You must use a unc path, not c:\... but \\server\repldata. Look at this: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1648231&SiteID=1|||

Hello,

Thank you very much for your reply. I am using \\server\\snapshot which is a unc path. This is a shared folder with Share Name as snapshot. It is physically under c:\\snapshot.

Any other suggestions?

Eva

|||Make sure access permissions are set properly for the share/file system folder, in order for the IIS merge agent to access the path.|||

Eric,

The permissions are set. The snapshot folder allows snapshotagent, internet agent account (IUSR_computer) to access. Those users also have access right (owner) to database and publication access list. Basically, I followed the steps in the following article:

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

I just can not create a subscription. I’ve exhausted the possible techniques I could find from forums. I don’t know if I need to re-install my existing software such as SQL server 2005, SQL 2005 CE (sqlcesa30.dll).

Thank you very much for your time. If you have new suggestions, please let me know.

Eva

|||

You can troubleshoot further and post your error stack, then it will be possible for other forum user to assist. Please enable SQL Server agent logging as per http://msdn2.microsoft.com/en-us/library/ms173017.aspx

|||

Eric,

Thanks again!

The problem was solved by move sqlcesa30.dll to my snapshot folder and modified my IIS virtue directory for SQLCE point to this folder. Now, I can move to next step of building applications.

Now, I continue to follow the sample in

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

When I try to do step 6 of Add a data connection, in the list of data providers, Microsoft SQL Server Compact Edition is not available. Any idea on how to add it?

Eva

|||

You must be using Visual Studio standard (or higher) SP1 for Microsoft SQL Server Compact Edition to be available.

problems in Hands-On-Lab, MED302

Hi,

"
TITLE: Microsoft SQL Server Management Studio

A call to SQL Server Reconciler failed. Try to resynchronize.
HRESULT 0x80004005 (29006)

Yunan

Yunan,

Have you got this solved? I'm having the same error message and couldn't find a solution.

Eva

|||You must use a unc path, not c:\... but \\server\repldata. Look at this: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1648231&SiteID=1|||

Hello,

Thank you very much for your reply. I am using \\server\\snapshot which is a unc path. This is a shared folder with Share Name as snapshot. It is physically under c:\\snapshot.

Any other suggestions?

Eva

|||Make sure access permissions are set properly for the share/file system folder, in order for the IIS merge agent to access the path.|||

Eric,

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

Thank you very much for your time. If you have new suggestions, please let me know.

Eva

|||

Eric,

Thanks again!

The problem was solved by move sqlcesa30.dll to my snapshot folder and modified my IIS virtue directory for SQLCE point to this folder. Now, I can move to next step of building applications.

Now, I continue to follow the sample in

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

When I try to do step 6 of Add a data connection, in the list of data providers, Microsoft SQL Server Compact Edition is not available. Any idea on how to add it?

Eva

|||

You must be using Visual Studio standard (or higher) SP1 for Microsoft SQL Server Compact Edition to be available.

Problems in executing SQL or Stored Procs with ASP

Hi experts,

I m working with MS SQL Server 2000 with ASP for my application. There're some Stored Procedures created for the new functions but seems I can't run these new SPs with my ASP pages. When I load that ASP page, it shows error message that can't find my SP. I've execute the SP alone in SQL Enterprise Manager and it works.

When I work with the SPs, I can connect with the DB with Enterprise Manager only without administrator right. As my SPs I suppose they should work with ASP but I just worry would it be the problem with my right granted in SQL server?

There are some other SPs running fine created by dbo but for my SPs not by dbo. Would there be any differences? Does it mean my SPs need to be granted by dbo instead of my current role? I m sure if I use the same ASP page then running another existing SPs, it works really smooth.

I also tried to make a SQL statement in my ASP page (e.g. an insert statement) but it seems nothing can be inserted. I got really screwed up!!!

Thanks in advance!!
Manfred

Manfred:

First, how are your ASP pages connecting to your database? Do you know they login, etc.? How do you give this login/user permissions to execute your stored procedure? Also, you might have an "owner" problem with the stored procedure object. Try running this query and posting the results:

select type,
uid,
left ([name], 40) as [name]
from sysobjects
where type = 'P'
and name = 'yourProcName'
-- - Sample Query Results: --
-- type uid name
-- - -
-- P 1 myProcName

Dave

|||

Thanks a lot for your advice Dave!

I got a dbo login name/password from my colleague and I've created a new SP under dbo login. It works! I can execute the stuff I want with the SP.

Here 's the query result

SELECT type, uid, LEFT(name, 40) AS name
FROM dbo.sysobjects
WHERE (type = 'P') AND (name = 'PROC_TESTDEPT')

-- type uid name
-- - -
-- P 1 proc_TestDept

Problems hitting SqlServer from Global.asax Application_Start

Hi,

In my application I Application_Start event in Global.asax I am wanting to populate an Application state variable to hold data from a database.

I can use the exact code elsewhere in the application but when I try to open my SqlConnection object I get the error "SQL Server does not exist or access denied."

I'm using NT security and impersonation.

I've put a trace on the server and my user name isn't appearing as trying anything so it is as if it cannot find the server.

Are there any issues with trying to open a connection from Application_Start?

Thanks.

Can you post your source code here?

|||There was an atricle about this in the latest MSDN magazine. Basically, asp.net won't do impersonation in the application start event. Your best bet is to grab the data the first time that you need it in regular code.

Monday, March 26, 2012

Problems deploying website

My ASP.NET application runs fine within the VS2005 IDE; but, when I deploy to my local machine to try to run it under IIS and calling it up in a browser, I'm getting the following exception:

Exception Details: System.Data.SqlClient.SqlException: Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[SqlException (0x80131904): Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.]
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +734979
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1838
System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK) +33
System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +628
System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +170
System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +359
System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) +28
System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) +424
System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) +66
System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +496
System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +82
System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +105
System.Data.SqlClient.SqlConnection.Open() +111
System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +121
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +137
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) +83
System.Web.UI.WebControls.SqlDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +1770
System.Web.UI.WebControls.ListControl.OnDataBinding(EventArgs e) +92
System.Web.UI.WebControls.ListControl.PerformSelect() +31
System.Web.UI.WebControls.BaseDataBoundControl.DataBind() +70
System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound() +82
System.Web.UI.WebControls.ListControl.OnPreRender(EventArgs e) +26
System.Web.UI.Control.PreRenderRecursiveInternal() +77
System.Web.UI.Control.PreRenderRecursiveInternal() +161
System.Web.UI.Control.PreRenderRecursiveInternal() +161
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1360

I'm a novice at this and really need some help. What stupid newbie mistake am I making here? Thanks in advance for any guidance/assistance rendered.

When you deplyed the app you left the same SQL trusted connection, and most probably there is no local SQL server running in the same machine. Make sure to change the SQL string to the IP, username and password of the database and will work really well

|||

I remember I got this problem too when I did my first try. It seems that your are using trusted connection, you need assign the domain user in your database with appropriate access right. Or you can create a database user in the database to use standard User/password connection method in your database is in mixed mode..

You can easily find the syntax for the connection string. As to create user in database, you need to create a login account under Security on the Server, in the database, you assign this user for access under database security tab.

Limno

Friday, March 23, 2012

Problems connection the SQL Server database

I build a small application using VS 2005. The application just builds a report using basic Select statement from SQL Server db. Here is my problem:

When I tried to run the application using VS 2005 by pressing F5 . The applicaiton launches using VS webserver and everything test fine. But when I put the site in IIS created website . I get an error "Login failed for user '<serverName>\<userName>

In my connection string I have Integrated Security = SSPI

Please suggest.

Thanks

Thismight be the problem (if the IIS created website you mentioned is on another machine).

On your machine, the "user" that logs into SQL Server is MYMACHINE\ASPNET.

When you deploy to another machine, the "user" that is trying to log into SQL Server is OTHERMACHINE\ASPNET. Maybe the other machine isn't aware of that "user"

Wednesday, March 21, 2012

problems connecting to DB.

Hi,

thats what i get when running the app:

Server Error in '/Jobs5' Application.

Unable to open the physical file "C:\MPDB\ManPower.mdf". Operating system error 32: "32(The process cannot access the file because it is being used by another process.)".
An attempt to attach an auto-named database for file C:\MPDB\ManPower.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.

Nothing seems to be using that file. So what is the problem?

Thanks.

The problem is that the DB is being used by the default web server of VS2005 whule i am trying to access using IIS.

the qu. is how do i make my application use IIS insted?

Thanks.

Problems connecting thru firewall

I have developed an application that handles data from a SQL server runnig inside my main domain.

When i connect with the SQL server from the inside i have no problems at all.

However we opened up the application (smart client application) for some beta testers and i bumped into some problems. i changed the connectionstring to use the IP for the server. I also tried a registered dns name, but i got the following

The aspnetdb i use for authentication is refusing to run the stored procedures and i get the famous disconnected due to the remote server does not accept remote calls, or something like that.

If i disable the Authentication in the application and just run the application with the same connectionstring IP or DNS, parts of the application works. Some of the stored procedures get executed , and the others does not get executed.

I have looked at the owner of the items but they all have the same owner and exec rights. I have checked my C# code and they are all using the patterns to execute the calls the same way.

Does anyone have any idea whats going on here ?

I also confirmed that the correct SQL ports has been opened in the PIX.
I ran home and tried to connect the sql server managment studio from home and i got access , the application still behaves like before

how about using a sql server mixed mode for authentication.|||

The SQL server is set to mixed mode security and i'm using a SQL login i created with dbo schema.

|||

is the server configured to accept local and remote connection

in the surface configuration

start>programs> sqlserver2005>
configuration tools>surface area configuration>
configuration for service and connection>datbase engine
>remote connection> check local and remote connection

|||

Thanks for your answer

I found the problem last night. In the solution someone had added a SQL report that had a hardcoded connectionstring. The try catch found the connection issue and then skipped parts of the load of the application.

Once i removed the conn string everything is working fine...

Problems calling a stored procedures depending on parameters

Hi guys, hoping one of you may be able to help me out. I am using VS 2005, and VB.net for a Windows application.

I have a table in SQL that has a list of Storedprocedures: Sprocs Table: SPID - PK (int), ID (int), NAME (string), TYPE (string)
The ID is a Foreign key (corresponding to a Company ID), the name is the stored procedure name, and Type (is the type of SP).

On my application I need to a certain SP depending on the company selected and what page you are on. I have a seperate SP that passes in parameters for both Company, and Type and should output the Name value:

ALTERPROCEDURE [dbo].[S_SPROC]
(@.IDint,@.TYPECHAR(10),@.NAMECHAR(20) OUTPUT)
AS

SELECT @.NAME= NAME
FROM SPROCS
WHERE [ID]= @.ID
AND [TYPE]= @.TYPE

Unfortunately I dont seem to be able to get the output in .Net, or then be able to fill my dataset with the Stored Procedure.
Has anyone done something similar before, or could point me in the right direction to solving this problem.

Thanks
Phil

Since @.NAME is an output parameter, you need to indicate that in your Command object (ParameterDirection.InputOutput or ParameterDirection.Output). That allows the parameter's value to be retrieved after the command has been executed.

Alternatively, you could select the data like you would in a normal data retrieval, and not worry about using an output parameter.

|||

Thanks for your reply Mark, I will try adding the ParameterDirection part.

If I use normal data retrieval how can I select the appropriate stored procedure when I try filling my table adapter from the dataset?

Thanks

|||

I assumed you would be performing an operation to select the stored proc name, then another operation to execute that stored proc.

|||

Yes that is what I am trying to do, but not so sure on how to go about it. Do you have any code examples?

Thanks

|||

hi mate,

Here is a sample

Dim cmd_ObjectpathAsNew SqlCommand("Select * from [" & tabelName &"]", sqlCon)

Dim adapterAsNew SqlDataAdapter(cmd_Objectpath)

Dim resultAsNew DataTable

adapter.Fill(result)

ForEach rowAs DataRowIn result.Rows

////do the process u want

Smile

|||

The code I have so far is:

Dim IDAs Int32
Dim TypeAsString

PrivateSub SimpleButton1_Click(ByVal senderAs System.Object,ByVal eAs System.EventArgs)Handles SimpleButton1.Click

ID =Me.TextBox1.Text
Type =Me.TextBox2.Text

Me.SPROCSTableAdapter.Fill(Me.DataSet1.SPROCS, ID, Type)

GetSprocName("AUMS_VALID")

Try

'Logic is a seperate VB file containing further code
Logic.run_SQL_fill_dataset(Me.sqlDataAdapter1, DataSet1.GEN_VALID)
Catch exAs Exception

EndTry

EndSub

PrivateFunction GetSprocName(ByVal st1)AsString
' Gets the names for the sprocs so each table can be filled with differant data. Using value 1 for param 1 just to test
Me.SQLCommand_GetSprocName.Parameters(1).Value = 1
Me.SQLCommand_GetSprocName.Parameters(2).Value = st1.ToString()
Logic.run_SQL_command(Me.sqlConnection1,Me.SQLCommand_GetSprocName)
' This is is where the app seems to fail
ReturnMe.SQLCommand_GetSprocName.Parameters(3).Value.ToString()

EndFunction

**** Code in Logic File: *****

'Sub to run SQLcommand, checks the connection and haddles errors

PublicSharedSub run_SQL_command(ByVal sqlcon1As SqlClient.SqlConnection,ByVal sqlcom1As SqlClient.SqlCommand)

Try
If sqlcon1.State <> ConnectionState.ClosedThen' connection check
sqlcon1.Close()
EndIf

If sqlcon1.State = ConnectionState.ClosedThen' connection check

sqlcon1.Open()

EndIf

sqlcom1.ExecuteNonQuery()

If sqlcon1.State = ConnectionState.OpenThen

sqlcon1.Close()

EndIf

Catch exAs Exception

If sqlcon1.State = ConnectionState.OpenThen

sqlcon1.Close()

EndIf

Error_box(ex,"Error on Running SQL Command")'Can place more better code here later

MsgBox(sqlcom1.CommandText.ToString)

EndTry

EndSub

PublicSharedSub run_SQL_fill_dataset(ByVal sqladapterAs SqlClient.SqlDataAdapter,ByVal datatableAs Data.DataTable)

Try

datatable.Clear()

sqladapter.Fill(datatable)

Catch exAs Exception

Error_box(ex,"Error on fill on dataset.")

EndTry

|||

Hi,

I'm afraid that there's something wrong in your code. What we can provide is a general process of communicating with a stored procedure from a .NET application.

Let's take the stored procedure you provided as the sample.

ALTER PROCEDURE [dbo].[S_SPROC]
( @.ID int, @.TYPE CHAR(10), @.NAME CHAR(20) OUTPUT )
AS
SELECT @.NAME = NAME
FROM SPROCS
WHERE [ID] = @.ID
AND [TYPE] = @.TYPE

In your procs, there are 2 input parameters and an output parameter. Then in your application, you should following the steps below:

1. Create the connection which links to the database.
a) Dim myconn As New SqlConnection(ConnectionString)

2. Create the SqlCommand object which execute the procs.
Dim sc As New SqlCommand()
sc.CommandType = CommandType.StoredProcedure
sc.CommandText = "YourProcsName"
sc.Connection = myconn

3. Setting your parameters and add them to SqlCommand object.

Dim sp1 As New SqlParameter()
sp1.ParameterName = "Parameter1"
sp1.Value = ""

Dim sp2 As New SqlParameter()
sp2.ParameterName = "Parameter2"
sp2.Value = ""

Dim sp3 As New SqlParameter()
sp3.ParameterName = "Parameter3"
sp3.Size = 10
sp3.Direction = ParameterDirection.Output

sc.Parameters.Add(sp1)
sc.Parameters.Add(sp2)
sc.Parameters.Add(sp3)

4. Open the connection, execute the process, and get the output parameter.

myconn.Open()
sc.ExecuteNonQuery()
myconn.Close()
Dim c As String = sp.Value.ToString()

After all, you can get the output parameter from the variable C.

Besides, this is a WebForm support forum, if you are developing WindowForm application, it would be better for you to go to MSDN forum where you can get more help.

Thanks.

|||

Thanks for your reply - it has been a big help.

Phil

Tuesday, March 20, 2012

Problems backing up SQL Express Database...

Hello,

I found some code below that works fine when I run it in my vb.net code in Design mode. When I publish the application, run the app and then execute the sub MyDbBackup() I get the following error: Cannot open backup device 'C:\dbBackup'. Operating system error 5(Access is denied). BACKUP DATABASE is terminating abnormally.

Here is the code that runs:

Public Sub MyDbBackup()
Dim sqlconn As New SqlClient.SqlConnection(MyConnectionStringdb)
sqlconn.Open()
Dim Backupcommand As SqlClient.SqlCommand = New SqlClient.SqlCommand("BACKUP DATABASE [" & sqlconn.Database.ToString & "] TO DISK = 'c:\myDataBaseBackup.bak'")
Backupcommand.CommandType = CommandType.Text
Backupcommand.Connection = sqlconn
Backupcommand.ExecuteNonQuery()
sqlconn.Close()
End Sub

2 Questions: Why does it backup fine in design mode, but not after it is published? Also, in design mode, my database is called TAP_Master.mdf but when I look under C:\Program Files\Microsoft\ SQL Server\MSSQL.1\MSSQL\Data, I do not see that database name... all I see is master.mdf, model, msdbdata etc... Is that database stored anywhere else?

I have set the MyConnectionStringdb variable to = "Data Source =.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\TAP_Master.mdf;Integrated Security=True;User Instance=True"

Thanks in advance.

You don't see your database in the Data directory because you embedded it in your application. The database is installed to the same location where you application is installed, which is an obfuscated path that is generated during ClickOnce installation. ClickOnce applications use a special type of instances of SQL Server called a User Instance to allow the database to be attached at runtime.

I'm not sure about the backup failure, I'll have to do a little more research. Is your published application on a different computer than the Dev box? I'm thinking it has something to do with a backup device that was created on the Dev box, but does not exist on the test box.

Mike

|||

Mike Wachal - MSFT wrote:

You don't see your database in the Data directory because you embedded it in your application. The database is installed to the same location where you application is installed, which is an obfuscated path that is generated during ClickOnce installation. ClickOnce applications use a special type of instances of SQL Server called a User Instance to allow the database to be attached at runtime.
I'm not sure about the backup failure, I'll have to do a little more research. Is your published application on a different computer than the Dev box? I'm thinking it has something to do with a backup device that was created on the Dev box, but does not exist on the test box.
Mike

Thanks for your reply. You are correct... My published application is on a different computer than my Dev box. If you can find out any additional info as to why I'm getting that error that would be great.

Also, I'll ask this question as well, Once the bak up file is created, what would be the best method to restore the .bak file to the machine that runs the published application.? I thought I could use the following code, but I receive the message, "Restore cannot process the database C"\Visual Studio\Projects\Prog\BIN\DEBUG\TAP_Master.MDF beause it is in use by this session. It is recommended that the master database be used when performing this operation":

Public Sub MyDbBackup()
Dim sqlconn As New SqlClient.SqlConnection(MyConnectionStringdb)
sqlconn.Open()
Dim Backupcommand As SqlClient.SqlCommand = New SqlClient.SqlCommand("RESTORE DATABASE [" & sqlconn.Database.ToString & "] FROM DISK = 'c:\myDataBaseBackup.bak'")
Backupcommand.CommandType = CommandType.Text
Backupcommand.Connection = sqlconn
Backupcommand.ExecuteNonQuery()
sqlconn.Close()
End Sub

Thanks in advance.

|||

Hi,

I guess that the user instance feature is not made for Backup and restore (with the emphasize on restore) because a restore will do a server attaching after the restoring which is not the sense of a user attached instance. Why don′t you take the MDF as a backup only ?

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Jens K. Suessmeyer wrote:

Hi,
... Why don′t you take the MDF as a backup only ?
HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

Hello, can you elaborate on that quote? How can I take only the MDF as a backup? Basically I want to be able to back up any saved data, and then be able to restore it in case of a system failure or a corrupted operating system.

Thanks for your replies all.

|||Just do a normal file copy when the database connection is closed, then you can easily attach it to a user instance again when the data is corrupt.

HTH, Jens K. Suessmeyer.

htttp://www.sqlserver2005.de

|||

Ok. This is what I have done. I opened my published app on my laptop, and then entered in some data. I closed my app, and then attempted to copy the master.mdf file from the C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data directory. At first I couldn't copy because SQL server express was still running. So I killed the services and copied the data.

I reinstalled my app, copied back the master.mdf into the C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data directory, and then launched the app. Now I do not see the data that I previously entered.

Am I understanding the File Copy / Attach to user instance concept correctly?

Thanks again for all your help.

|||No actually not, the database which has to be backup is your own database not the SQL Server internal ones. YOu will have to backup the MDF file of the database with the reference TAP_master.mdf you mentioned above.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

The reason you're seeing the backup error is that the user context running your app on the deployed system doesn't have Windows permissions to create a file in the root of the local C: drive. That's why it's different from your dev environment: Different machine, different permissions.

So, either point the backup to a known directory that you're guaranteed to have permission to write to on machines where you're deployed, or find a way to make sure that you have appropriate permissions to the root of C:.

On the restore, it's simply a matter that you can't overwrite a database while you have an active session attached to it. Your connection is that session. By setting focus to Master (use master) you're taking focus away from the database you're trying to restore, and it should work fine.

|||

Kevin Farlee wrote:

On the restore, it's simply a matter that you can't overwrite a database while you have an active session attached to it. Your connection is that session. By setting focus to Master (use master) you're taking focus away from the database you're trying to restore, and it should work fine.

Ok thanks...First issue resolved... Now with regards to the second issue, Where am I setting focus to the master from? Is it within Visual Studio design mode, or under SQL Server 2005 Surface Area Configuration?

Thanks.

|||

Rashar wrote:

Kevin Farlee wrote:
On the restore, it's simply a matter that you can't overwrite a database while you have an active session attached to it. Your connection is that session. By setting focus to Master (use master) you're taking focus away from the database you're trying to restore, and it should work fine.
Ok thanks...First issue resolved... Now with regards to the second issue, Where am I setting focus to the master from? Is it within Visual Studio design mode, or under SQL Server 2005 Surface Area Configuration?
Thanks.

The context information is session based, you can change the context from within your script (if you are using one, I am not sure from your explnation using the USE <DatabaseName> command. If you are in any GUI of SQL Management Studio, make sure that you are not residing on a database node in the treeview that you want to restore.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

Problems adding MS access datasource in VB studios 2005 enterprise

I am running Visual Studios 2005 enterprise edition and I am creating a mobile application to keep track of some receipts. The problem I'm currently having is that even though I can connect to the MS Access Database with Server Explorer that I'll be aquiring data from; Everytime I attempt to use the Data Configuration Wizard to add a new data source that namely the database I get this error message:

The new connection uses a data provider that is not supported. (end of message)

There is no username or password necessary to access this database and it is an Access 2003 database.

Any help would be greatly appreciated.

Just to make sure...

You did change the Data Source to:

Microsoft Access Database File (OLE DB)

Which will use the following .NET Data Provider:

.NET Framework Data Provider for OLE DB

I assume you did in order to select the database, but I just want to make sure.

And when you click the button "Test Connection", it gives you that error?

Regards,

Dave

|||

Well that is the default settings so I just left those as they were and once I had figured out how to connect to the database thru server explorer, whenever I hit the test connection it said Connection ok. However the error message pops up when I attempt to add the database thru the add new datasource wizard.

|||Perhaps I'm missing something, but didn't you say this was a mobile app? Isn't the reason that VS is refusing to use the data source simply because the JET database engine doesn't run on mobile devices? -- Brendan Reynolds Access MVP wrote in message news:b986531d-b456-4194-af38-649f7db26f0d@.discussions.microsoft.com... >I am running Visual Studios 2005 enterprise edition and I am creating a
> mobile application to keep track of some receipts. The problem I'm
> currently having is that even though I can connect to the MS Access
> Database with Server Explorer that I'll be aquiring data from; Everytime
> I attempt to use the Data Configuration Wizard to add a new data source
> that namely the database I get this error message: >
> The new connection uses a data provider that is not supported. (end of
> message) >
> There is no username or password necessary to access this database and
> it is an Access 2003 database. >
> Any help would be greatly appreciated. >
>|||

To: Mr. Reynolds
I have no idea sir, I started the project as a smart device app, and in all the walkthroughs I have viewed nothing has been said about the Jet database engine being incompatible with mobile devices. If this is the case I'd like to know because this project is currently dependent on VS being able to connect with our Access database.

|||

I'm not sure of the limitations, you're best asking at the SQL Mobile Forum.

http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=152&SiteID=1

Am moving this thread to there.

Here's some info on SQL Server 2005 Mobile Edition

http://msdn.microsoft.com/sql/mobile/default.aspx

Alex.

|||The JET database engine runs only on desktop and server versions of Windows. It's certainly possible to develop a mobile web app that talks to a JET database on the server, but if you need a local data store on the mobile device that will remain available when the device is not connected to the network, then you will not be able to use JET. Your alternatives - as I understand it - include SQL Server CE and XML. There is a database engine on the Pocket PC called 'Pocket Access' but it has very little to do with the desktop product, and there is no built-in support for 'Pocket Access' in the .NET framework. There is, however, a third-party component you could look at. There's an article on this at the URL below, and there's a link from the article to the vendor of the third-party component. Note that I haven't personally used this, and I don't know what plans, if any, Microsoft has to continue to support 'Pocket Access'. So this isn't a recommendation, just something that may be worth further investigation. http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnroad/html/road10222003.asp There's an interesting article on 'Data Access Strategies for the Microsoft ..NET Compact Framework' at the following URL. Although the article dates back to the .NET 1.0 timeframe, I believe it is still relevant. http://msdn.microsoft.com/mobility/understanding/articles/default.aspx?pull=/library/en-us/dnnetcomp/html/dataaccessstrategy.asp -- Brendan Reynolds Access MVP wrote in message news:703e94a5-811d-4486-927c-34c62b34dc36_WBRev1_@.discussions.microsoft.com...
> This post has been edited either by the author or a moderator in the
> Microsoft Forums: http://forums.microsoft.com
> To: Mr. Reynolds
> I have no idea sir, I started the project as a smart device app, and in
> all the walkthroughs I have viewed nothing has been said about the Jet
> database engine being incompatible with mobile devices. If this is the
> case I'd like to know because this project is currently dependent on VS
> being able to connect with our Access database. > > > >
>|||Thank you for the information. Unfortunately I have a follow up question. If I were to make an intermediate SQL database to function as the go between for a SQL Mobile database and a Microsoft access database, is such a solution even possible? Our organization recently purchased VS 2005 on the assumption that it would be easy to use the .NET framework to develop a mobile app that would easily transfer data to an access database. Now with this new information its quite possible the data will go SQL Mobile -> SQL Database -> MS Access database.
Footnote:
The SQL Database would just function as a temporary store to check data generated from receipts made in the mobile database. Once the checks are done the corrected data would be passed to the MS Access database. Then the SQL database would wipe itself clean.
|||

Yes, but it makes no sense. To accomplish that you would have to purchase SQL 2000/2005 and pay CALs per device (or per CPU license). If that’s acceptable, simply get rid of Access and use much more powerful SQL you've paid for.

Alternative solution is to use Web Service to talk to Access database. Create couple WEB methods to talk to access and call them from device. Something like this should do:

DataSet GetAccessData(String command, …)

{

// Connect to Access DB here, execute command and populate data set with returned data.

return dataSet;

}

Bool UpdateAccessData(DataSet dataSet, … )

{

// Connect to Access DB here and update DB with data from Data Set.

return success;

}

|||To: Ilya
The enterprise edition that we purchased of VBS 2005 came with SQL server 2005 and I just installed it upon learning that I can't communicate directly with the Access database. I'm not sure if we still have to pay the CALS per device though.
If I used these web service methods where would I write them? In the upload and download portion of the code for the mobile app?
|||

I believe it comes with 5 CALs (to be verified).

These WS methods would run on desktop via IIS which would host WS. To create them, please click ‘Create/Web Site’ and choose "ASP.Net Web Service". You'll get project skeleton, simply add code you need and run it. Decorate added methods with WebMethod attribute. This is desktop project, so you could use anything desktop has to offer.

Calling WS from device pretty much looks like a normal function call. All necessary code would be created for you automatically as soon as you add Web Reference to your project.

|||i want to connecting Access database on PDA ?
i am setting Access database on PDA..
can i do ?
and how i doing?|||How would one connect to an access database inside the web method?
|||

while the .NET Compact Framework has no APIs to leverage Access databases, there is an open source set of APIs that support Access at www.opennetcf.org

there are also some commercial libraries available - use Google Advanced Groups search on microsoft.public.dotnet.framework.compactframework and look for "Access Database"

Darren

Problems adding MS access datasource in VB studios 2005 enterprise

The new connection uses a data provider that is not supported. (end of message)

There is no username or password necessary to access this database and it is an Access 2003 database.

Any help would be greatly appreciated.

Just to make sure...

You did change the Data Source to:

Microsoft Access Database File (OLE DB)

Which will use the following .NET Data Provider:

.NET Framework Data Provider for OLE DB

I assume you did in order to select the database, but I just want to make sure.

And when you click the button "Test Connection", it gives you that error?

Regards,

Dave

|||

I'm not sure of the limitations, you're best asking at the SQL Mobile Forum.

http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=152&SiteID=1

Am moving this thread to there.

Here's some info on SQL Server 2005 Mobile Edition

http://msdn.microsoft.com/sql/mobile/default.aspx

Alex.

Alternative solution is to use Web Service to talk to Access database. Create couple WEB methods to talk to access and call them from device. Something like this should do:

DataSet GetAccessData(String command, …)

{

// Connect to Access DB here, execute command and populate data set with returned data.

return dataSet;

}

Bool UpdateAccessData(DataSet dataSet, … )

{

// Connect to Access DB here and update DB with data from Data Set.

return success;

}

I believe it comes with 5 CALs (to be verified).

Calling WS from device pretty much looks like a normal function call. All necessary code would be created for you automatically as soon as you add Web Reference to your project.

|||i want to connecting Access database on PDA ?
i am setting Access database on PDA..
can i do ?
and how i doing?|||How would one connect to an access database inside the web method?
|||

while the .NET Compact Framework has no APIs to leverage Access databases, there is an open source set of APIs that support Access at www.opennetcf.org

there are also some commercial libraries available - use Google Advanced Groups search on microsoft.public.dotnet.framework.compactframework and look for "Access Database"

Darren

Monday, March 12, 2012

Problem: too many mark fields in the table

Many applications retrieve information from the same table. Each application
needs to remember the records it has retrieved before, so that it does not
retrieve these records in the following run any more.
The current solution is to put the different "flag" field for each
application on the table. The flag field for each application will be marked
by the application after it retrieves the records.
The problem is that the table becomes extremely wide and keep widening. The
table needs to be changed each time a new appliction comes up.
I am thinking about keep a list for each application which stores the key
values of the records the application has retrieved, instead of marking the
original table.
Is there any better way to do it? What is the workaround you recommend?
Thanks,
LixinThat is a strange requirement, that I haven't come across myself so far.
Yes, the workaround you have seems sensible.
Are your keys incrementing/follow a logical order? If so, your applications
could remember the last key retrieved and next time, look for keys greater
than the saved key.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"FLX" <nospam@.hotmail.com> wrote in message
news:uS2YuPvTEHA.3336@.TK2MSFTNGP10.phx.gbl...
Many applications retrieve information from the same table. Each application
needs to remember the records it has retrieved before, so that it does not
retrieve these records in the following run any more.
The current solution is to put the different "flag" field for each
application on the table. The flag field for each application will be marked
by the application after it retrieves the records.
The problem is that the table becomes extremely wide and keep widening. The
table needs to be changed each time a new appliction comes up.
I am thinking about keep a list for each application which stores the key
values of the records the application has retrieved, instead of marking the
original table.
Is there any better way to do it? What is the workaround you recommend?
Thanks,
Lixin|||If rows may be retrieved by multiple applications and you need to keep a
history of this just put the data in a table:
CREATE TABLE ApplicationHistory (keycol INTEGER NOT NULL REFERENCES
YourTable (keycol), application CHAR(10) NOT NULL REFERENCES Applications
(application), appdate DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY
KEY(keycol, application))
--
David Portas
SQL Server MVP
--|||Why can't the applications themselves internally store sets (or arrays, or
dictionarys, or whatever your favorite container is) containing identifiers
for rows they've already retrieved?
"FLX" <nospam@.hotmail.com> wrote in message
news:uS2YuPvTEHA.3336@.TK2MSFTNGP10.phx.gbl...
> Many applications retrieve information from the same table. Each
application
> needs to remember the records it has retrieved before, so that it does not
> retrieve these records in the following run any more.
> The current solution is to put the different "flag" field for each
> application on the table. The flag field for each application will be
marked
> by the application after it retrieves the records.
> The problem is that the table becomes extremely wide and keep widening.
The
> table needs to be changed each time a new appliction comes up.
> I am thinking about keep a list for each application which stores the key
> values of the records the application has retrieved, instead of marking
the
> original table.
> Is there any better way to do it? What is the workaround you recommend?
> Thanks,
> Lixin
>|||Unfortunately, the key values are not in the logic order.
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:u0w0VWvTEHA.808@.tk2msftngp13.phx.gbl...
> That is a strange requirement, that I haven't come across myself so far.
> Yes, the workaround you have seems sensible.
> Are your keys incrementing/follow a logical order? If so, your
applications
> could remember the last key retrieved and next time, look for keys greater
> than the saved key.
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
> Is .NET important for a database professional?
> http://vyaskn.tripod.com/poll.htm
>
> "FLX" <nospam@.hotmail.com> wrote in message
> news:uS2YuPvTEHA.3336@.TK2MSFTNGP10.phx.gbl...
> Many applications retrieve information from the same table. Each
application
> needs to remember the records it has retrieved before, so that it does not
> retrieve these records in the following run any more.
> The current solution is to put the different "flag" field for each
> application on the table. The flag field for each application will be
marked
> by the application after it retrieves the records.
> The problem is that the table becomes extremely wide and keep widening.
The
> table needs to be changed each time a new appliction comes up.
> I am thinking about keep a list for each application which stores the key
> values of the records the application has retrieved, instead of marking
the
> original table.
> Is there any better way to do it? What is the workaround you recommend?
> Thanks,
> Lixin
>
>|||Thanks. This is what I am going to do.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:w_CdnY17nKuK81XdRVn_iw@.giganews.com...
> If rows may be retrieved by multiple applications and you need to keep a
> history of this just put the data in a table:
> CREATE TABLE ApplicationHistory (keycol INTEGER NOT NULL REFERENCES
> YourTable (keycol), application CHAR(10) NOT NULL REFERENCES Applications
> (application), appdate DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY
> KEY(keycol, application))
> --
> David Portas
> SQL Server MVP
> --
>|||The application runs and exits, therefore I can't use array or any other
internal data structures.
I think I can store the key values in the text file or in a database table.
The latter might be more convenient and efficient.
Thanks a lot.
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23Ko2BZvTEHA.3476@.tk2msftngp13.phx.gbl...
> Why can't the applications themselves internally store sets (or arrays, or
> dictionarys, or whatever your favorite container is) containing
identifiers
> for rows they've already retrieved?
>
> "FLX" <nospam@.hotmail.com> wrote in message
> news:uS2YuPvTEHA.3336@.TK2MSFTNGP10.phx.gbl...
> > Many applications retrieve information from the same table. Each
> application
> > needs to remember the records it has retrieved before, so that it does
not
> > retrieve these records in the following run any more.
> >
> > The current solution is to put the different "flag" field for each
> > application on the table. The flag field for each application will be
> marked
> > by the application after it retrieves the records.
> >
> > The problem is that the table becomes extremely wide and keep widening.
> The
> > table needs to be changed each time a new appliction comes up.
> >
> > I am thinking about keep a list for each application which stores the
key
> > values of the records the application has retrieved, instead of marking
> the
> > original table.
> >
> > Is there any better way to do it? What is the workaround you recommend?
> > Thanks,
> >
> > Lixin
> >
> >
>|||I think David Portas' solution is probably best; you can JOIN to the table
in order to filter for future retrievals of data, without reading from an
external source.
"FLX" <nospam@.hotmail.com> wrote in message
news:%23PkxrmvTEHA.544@.TK2MSFTNGP11.phx.gbl...
> The application runs and exits, therefore I can't use array or any other
> internal data structures.
> I think I can store the key values in the text file or in a database
table.
> The latter might be more convenient and efficient.
>|||Instead of separate 'mark' columns use single column (SelectedFlags int),
storing a bit mask of applications marking the row.
Ramon @. Havana Club
"FLX" <nospam@.hotmail.com> ÓÏÏÂÝÉÌ/ÓÏÏÂÝÉÌÁ × ÎÏ×ÏÓÔÑÈ ÓÌÅÄÕÀÝÅÅ:
news:uS2YuPvTEHA.3336@.TK2MSFTNGP10.phx.gbl...
> Many applications retrieve information from the same table. Each
application
> needs to remember the records it has retrieved before, so that it does not
> retrieve these records in the following run any more.
> The current solution is to put the different "flag" field for each
> application on the table. The flag field for each application will be
marked
> by the application after it retrieves the records.
> The problem is that the table becomes extremely wide and keep widening.
The
> table needs to be changed each time a new appliction comes up.
> I am thinking about keep a list for each application which stores the key
> values of the records the application has retrieved, instead of marking
the
> original table.
> Is there any better way to do it? What is the workaround you recommend?
> Thanks,
> Lixin
>

Problem: Stored Procedures not completing from Web Application

I have several stored procedures that run fine from my SQL Server
database (via the exec command.), though when I call these procedures
from my web application, they do not complete. I have other
procedures that in fact do run fine through my web application though,
so I do not believe its a front-end problem. The procedures only take
about 30 seconds to run from the back-end, so I know its not a time
out issue as well. Does anyone have any ideas?

Thanks in advance."Mike J" <mjewett_2000@.yahoo.com> wrote in message
news:1a9d60fa.0404261152.322ed838@.posting.google.c om...
> I have several stored procedures that run fine from my SQL Server
> database (via the exec command.), though when I call these procedures
> from my web application, they do not complete. I have other
> procedures that in fact do run fine through my web application though,
> so I do not believe its a front-end problem. The procedures only take
> about 30 seconds to run from the back-end, so I know its not a time
> out issue as well. Does anyone have any ideas?
> Thanks in advance.

Can you clarify what you mean by "does not complete"? Do you get an error
message? What version of MSSQL? What's the web platform, eg. ASP, PHP etc.?
If it works fine from Query Analyzer then that suggests an issue on the web
side, but without more information it's hard to say.

Simon|||Mike J (mjewett_2000@.yahoo.com) writes:
> I have several stored procedures that run fine from my SQL Server
> database (via the exec command.), though when I call these procedures
> from my web application, they do not complete. I have other
> procedures that in fact do run fine through my web application though,
> so I do not believe its a front-end problem. The procedures only take
> about 30 seconds to run from the back-end, so I know its not a time
> out issue as well. Does anyone have any ideas?

30 seconds is the default time-out, so timeout problems cannot be ruled
out completely. Do you have proper error handling in your web app?

There are a couple of possible reasons for this, but since you provided
very little information about your code, I can only give some general
comments.

Do you use indexed views or indexes on computed columns? In such case,
you need to issue SET ARITHABORT ON when you connect from your web app
(or make this default for the database with ALTER DATABASE). This setting
is on by default when you run from Query Analyzer.

Even if you don't use indexed views or indexed computed columns, SET
ARITHABORT ON, can still be useful, as you now will get the same plan
as Query Analyzer does.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Simon Hayes" <sql@.hayes.ch> wrote in message news:<408d7223$1_3@.news.bluewin.ch>...
> "Mike J" <mjewett_2000@.yahoo.com> wrote in message
> news:1a9d60fa.0404261152.322ed838@.posting.google.c om...
> > I have several stored procedures that run fine from my SQL Server
> > database (via the exec command.), though when I call these procedures
> > from my web application, they do not complete. I have other
> > procedures that in fact do run fine through my web application though,
> > so I do not believe its a front-end problem. The procedures only take
> > about 30 seconds to run from the back-end, so I know its not a time
> > out issue as well. Does anyone have any ideas?
> > Thanks in advance.
> Can you clarify what you mean by "does not complete"? Do you get an error
> message? What version of MSSQL? What's the web platform, eg. ASP, PHP etc.?
> If it works fine from Query Analyzer then that suggests an issue on the web
> side, but without more information it's hard to say.
> Simon

Simon,

Here's some more elaboration. By "does not complete" I mean that the
procedures simply never finish running. They generate no error
messages, and do not lock any objects -- they just dont finish. I
verify this by manually running the procedures in query analyzer
(where they finish quickly) and viewing the results. The strange
thing is that we use over 50 stored procedures, and almost all of them
work -- from both the web application and query analyzer. The couple
procedures that are "not finishing" are not any more complex than the
others.

The web platform we are using is Microsoft asp .NET. We are using SQL
Server 2000 as well.
Any ideas? Thanks.|||Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns94D7EF10D40A3Yazorman@.127.0.0.1>...
> Mike J (mjewett_2000@.yahoo.com) writes:
> > I have several stored procedures that run fine from my SQL Server
> > database (via the exec command.), though when I call these procedures
> > from my web application, they do not complete. I have other
> > procedures that in fact do run fine through my web application though,
> > so I do not believe its a front-end problem. The procedures only take
> > about 30 seconds to run from the back-end, so I know its not a time
> > out issue as well. Does anyone have any ideas?
> 30 seconds is the default time-out, so timeout problems cannot be ruled
> out completely. Do you have proper error handling in your web app?
> There are a couple of possible reasons for this, but since you provided
> very little information about your code, I can only give some general
> comments.
> Do you use indexed views or indexes on computed columns? In such case,
> you need to issue SET ARITHABORT ON when you connect from your web app
> (or make this default for the database with ALTER DATABASE). This setting
> is on by default when you run from Query Analyzer.
> Even if you don't use indexed views or indexed computed columns, SET
> ARITHABORT ON, can still be useful, as you now will get the same plan
> as Query Analyzer does.

Erland, yes, we have proper error handling in our web application. We
in fact run many (around 50) stored procedures from our web
application and they complete just fine. The couple procedures that
do not run from the application are not any more complex than the
others.

To elaborate some more on our system, we are using Microsoft .NET, SQL
Server 2000. I have run many tests in query analyzer on our database
server with the procedures in question. They complete accurately and
quickly every time, with no errors. Its only when I call them from
the web app where do not finish. We do not use any indexed views or
computed columns, though I will try to ARITHABOR ON property anyhow.
Any other ideas?
Thanks.|||Mike J (mjewett_2000@.yahoo.com) writes:
> To elaborate some more on our system, we are using Microsoft .NET, SQL
> Server 2000. I have run many tests in query analyzer on our database
> server with the procedures in question. They complete accurately and
> quickly every time, with no errors. Its only when I call them from
> the web app where do not finish. We do not use any indexed views or
> computed columns, though I will try to ARITHABOR ON property anyhow.
> Any other ideas?

With that miniscule of information, no. Well, while you ruled out blocking
in another posting, one possibility is that you manage to block yourself
in the app. When you have a procedure which does not complete, execute
sp_who, and see if any process has a non-zero value in the Blk column.

If there is no blocking, use the Profiler to see where the process gets
stuck.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||I had experienced issues with a store proc executing very quickly in Query Analyzer, but not from my ASP app against a development database. I noticed recently the same issue with the procedure not executing quickly in either QA or the web app.

After checking my table indexes on the dev database I found one joined table that had no index for the fields I was joining on. The index existed in the production database. Once adding that index. the procedure executed in < 1 second in QA and only a couple seconds from the web app.

Hope this helps anyone who's frustrated with similar issues.

Dave

Friday, March 30, 2012

Problems linking Access to SQL replicated tables

Problems ith Connection string to SQL Express

Problems installing MSDE with the Server Service disabled

Wednesday, March 28, 2012

Problems in Search opertion using Like

Problems in Search opertion using Like

Problems in Search opertion using Like

problems in Hands-On-Lab, MED302

problems in Hands-On-Lab, MED302

Problems in executing SQL or Stored Procs with ASP

Problems hitting SqlServer from Global.asax Application_Start

Monday, March 26, 2012

Problems deploying website

Friday, March 23, 2012

Problems connection the SQL Server database

Wednesday, March 21, 2012

problems connecting to DB.

Server Error in '/Jobs5' Application.

Problems connecting thru firewall

Problems calling a stored procedures depending on parameters

Tuesday, March 20, 2012

Problems backing up SQL Express Database...

Problems adding MS access datasource in VB studios 2005 enterprise

Problems adding MS access datasource in VB studios 2005 enterprise

Monday, March 12, 2012

Problem: too many mark fields in the table

Problem: Stored Procedures not completing from Web Application

Sql Update command

Blog Archive

About Me