I upsized an Access database into MSDE to begin scalling it up to SQL Server.
I have a curious problem: my queries don't return any results if the table has a Access primary key before they are upsized.
I've deleted and re-created the database to check this, and if I use Access to remove the primary key before upsizing, the queries will return a result; if I re-instate the primary key and upsize again, the queries return nothing.
(This is true regardless of using VS Net, Web Matrix, or by hand...)
I'm a newbie with MSDE and with upsizing from Access. Is this expected -- or have I maybe done something wrong, again?
- Tinker
Nevermind... The whole MSDE-Access-Upsizing thing just got to be too much and I switched over to SQL Server 2000. That solved EVERYthing.
Tinkersql
Showing posts with label access. Show all posts
Showing posts with label access. Show all posts
Monday, March 26, 2012
Friday, March 23, 2012
Question about msde
Hello,
We have an application that collects data into and access db. Then the
user connects to the internet and the access db syncs with our sql using a
vb.net application. We have some issues at times with this vb.net
application. I am wondering if we use msde on the client machine and they
connect to the internet if there is a way to auto sync the data through sql
thus removing the vb.net application. Is this possible could someone give me
the pros and cons? We are also hoping to add photos into msde if possible.
Thanks in advance.
Jake
Sure, MSDE and SQL Server can replicate to each other (if that's what you
mean). It works fine according to most reports.
No, I would not store BLOBs (pictures) in the database--even SQL Server
Standard that has a lot more capacity than MSDE (which is limited to 2 GB).
Store the path to the picture file in the database.
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
"Jake" <rondican@.hotmail.com> wrote in message
news:ubKUFJ5jEHA.2544@.TK2MSFTNGP10.phx.gbl...
> Hello,
> We have an application that collects data into and access db. Then the
> user connects to the internet and the access db syncs with our sql using a
> vb.net application. We have some issues at times with this vb.net
> application. I am wondering if we use msde on the client machine and they
> connect to the internet if there is a way to auto sync the data through
> sql
> thus removing the vb.net application. Is this possible could someone give
> me
> the pros and cons? We are also hoping to add photos into msde if possible.
> Thanks in advance.
> Jake
>
|||Bill,
Thanks for the suggestions. Is there some documentation on how to setup
an MSDE to sync with SQL2k when a laptop is connected to the internet? I
would like this automatic if possible.
For example:
1. user collects data
2. user connects laptop to internet
3. System sees an internet connection and start syncing between MSDE and SQL
server.
Thanks in advance.
Jake
"William (Bill) Vaughn" <billvaRemoveThis@.nwlink.com> wrote in message
news:e1EeSP6jEHA.3664@.TK2MSFTNGP11.phx.gbl...
> Sure, MSDE and SQL Server can replicate to each other (if that's what you
> mean). It works fine according to most reports.
> No, I would not store BLOBs (pictures) in the database--even SQL Server
> Standard that has a lot more capacity than MSDE (which is limited to 2
GB).
> Store the path to the picture file in the database.
> --
> ____________________________________
> William (Bill) Vaughn
> Author, Mentor, Consultant
> Microsoft MVP
> www.betav.com
> Please reply only to the newsgroup so that others can benefit.
> This posting is provided "AS IS" with no warranties, and confers no
rights.[vbcol=seagreen]
> __________________________________
> "Jake" <rondican@.hotmail.com> wrote in message
> news:ubKUFJ5jEHA.2544@.TK2MSFTNGP10.phx.gbl...
the[vbcol=seagreen]
a[vbcol=seagreen]
they[vbcol=seagreen]
give[vbcol=seagreen]
possible.
>
We have an application that collects data into and access db. Then the
user connects to the internet and the access db syncs with our sql using a
vb.net application. We have some issues at times with this vb.net
application. I am wondering if we use msde on the client machine and they
connect to the internet if there is a way to auto sync the data through sql
thus removing the vb.net application. Is this possible could someone give me
the pros and cons? We are also hoping to add photos into msde if possible.
Thanks in advance.
Jake
Sure, MSDE and SQL Server can replicate to each other (if that's what you
mean). It works fine according to most reports.
No, I would not store BLOBs (pictures) in the database--even SQL Server
Standard that has a lot more capacity than MSDE (which is limited to 2 GB).
Store the path to the picture file in the database.
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
"Jake" <rondican@.hotmail.com> wrote in message
news:ubKUFJ5jEHA.2544@.TK2MSFTNGP10.phx.gbl...
> Hello,
> We have an application that collects data into and access db. Then the
> user connects to the internet and the access db syncs with our sql using a
> vb.net application. We have some issues at times with this vb.net
> application. I am wondering if we use msde on the client machine and they
> connect to the internet if there is a way to auto sync the data through
> sql
> thus removing the vb.net application. Is this possible could someone give
> me
> the pros and cons? We are also hoping to add photos into msde if possible.
> Thanks in advance.
> Jake
>
|||Bill,
Thanks for the suggestions. Is there some documentation on how to setup
an MSDE to sync with SQL2k when a laptop is connected to the internet? I
would like this automatic if possible.
For example:
1. user collects data
2. user connects laptop to internet
3. System sees an internet connection and start syncing between MSDE and SQL
server.
Thanks in advance.
Jake
"William (Bill) Vaughn" <billvaRemoveThis@.nwlink.com> wrote in message
news:e1EeSP6jEHA.3664@.TK2MSFTNGP11.phx.gbl...
> Sure, MSDE and SQL Server can replicate to each other (if that's what you
> mean). It works fine according to most reports.
> No, I would not store BLOBs (pictures) in the database--even SQL Server
> Standard that has a lot more capacity than MSDE (which is limited to 2
GB).
> Store the path to the picture file in the database.
> --
> ____________________________________
> William (Bill) Vaughn
> Author, Mentor, Consultant
> Microsoft MVP
> www.betav.com
> Please reply only to the newsgroup so that others can benefit.
> This posting is provided "AS IS" with no warranties, and confers no
rights.[vbcol=seagreen]
> __________________________________
> "Jake" <rondican@.hotmail.com> wrote in message
> news:ubKUFJ5jEHA.2544@.TK2MSFTNGP10.phx.gbl...
the[vbcol=seagreen]
a[vbcol=seagreen]
they[vbcol=seagreen]
give[vbcol=seagreen]
possible.
>
question about linked servers and Access tables
Having a little problem with linking to Access tables. I have an Access database that has in it, some linked tables. After setting up the linked server in SQL server, none of the linked tables in Access show up, only the tables that were created or imported show up.
Is there anything you can do to get the linked tables in Access to show up under the tables list in the SQL linked server?Can you query the Access table(s) using the 4 part naming convention?
select * from <server>.<db>.<owner>.<table>|||haven't tried that, didn't consider it since the tables do not show up. but will give it a shot.|||Got an error saying the file was open exclusively by another or i dont have permissions, which is not the case. The linked tables are from an Oracle database. So the permission problem might be with it.
Tuesday, March 20, 2012
question about how to access column/field information
Hi,
I am just starting out in SQL Server and, as always, I am throwing myself in
at the deep end by trying more advanced things. I understand the basics like
INSERT, DELETE, UPDATE, SELECT etc What I am trying to do is pass some kind
of SQL object to a function which will then extract the field names, field
types, and field sizes from that object.
I have tried using DataSet and DataTable but I cannot see how I can extract
all three things I need from the column information. I'd also rather do what
I am doing on the actual table structure itself, rather than on a returned
set of records from the table (dataset).
Any help would be appreciated.
Thanks in advance,
RichardAs far as you are writing in the SQL Server group I think that you want to
get this information via TSQL ?!
If so, just look at this Query where #Orders can be replaced with the Name
of your Object to be queried. The Best way woud be to solve the Object name
to a ObjectID and to search for then, just for easy understanding it is
filtered via the object name here. :-)
Select sc.name,st.name,st.length from syscolumns sc
Inner join sysobjects so on
sc.id = so.id
Inner join systypes st on
sc.xtype = st.xtype
Where so.Xtype = 'U' And
so.name like 'Orders'
That will give you all the information youll wanted. If you want the
information via C# oder ADO.NET in common you could get i with FILLSCHEMA:
http://msdn.microsoft.com/library/d...r />
atopic.asp
HTH, Jens Smeyer.
"Richard" <acorn@.acorn.net> schrieb im Newsbeitrag
news:425cb509$1_3@.news.melbourne.pipenetworks.com...
> Hi,
> I am just starting out in SQL Server and, as always, I am throwing myself
> in at the deep end by trying more advanced things. I understand the basics
> like INSERT, DELETE, UPDATE, SELECT etc What I am trying to do is pass
> some kind of SQL object to a function which will then extract the field
> names, field types, and field sizes from that object.
> I have tried using DataSet and DataTable but I cannot see how I can
> extract all three things I need from the column information. I'd also
> rather do what I am doing on the actual table structure itself, rather
> than on a returned set of records from the table (dataset).
> Any help would be appreciated.
> Thanks in advance,
> Richard
>|||Richard wrote:
> Hi,
> I am just starting out in SQL Server and, as always, I am throwing
> myself in at the deep end by trying more advanced things. I
> understand the basics like INSERT, DELETE, UPDATE, SELECT etc What I
> am trying to do is pass some kind of SQL object to a function which
> will then extract the field names, field types, and field sizes from
> that object.
> I have tried using DataSet and DataTable but I cannot see how I can
> extract all three things I need from the column information. I'd also
> rather do what I am doing on the actual table structure itself,
> rather than on a returned set of records from the table (dataset).
> Any help would be appreciated.
> Thanks in advance,
> Richard
You can interrogate the system tables or ideally use the
INFORMATION_SCHEMA views. See BOL for more information.
I'm not sure what you mean by "pass a SQL object to a function", unless
what you're saying is that you want a stored procedure that returns some
meta data for a table.
If you're executing a query and want the result set details, you can do
that easily from client in ADO or ADO.Net.
Can you provide some additional detail about what you're trying to do?
David Gugick
Imceda Software
www.imceda.com|||What I'd like to be able to do is, given the name of a table or a table
object of some description, interrogate that table for all the fields in
that table, and also information about those fields, so that I can process
that information.
As much information as possible about the fields would be nice, but at the
minimum I need the field name, type (char, bigint etc) and size of the
field.
Thanks again,
Richard
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:%23UAyWB$PFHA.2972@.TK2MSFTNGP14.phx.gbl...
> Richard wrote:
> You can interrogate the system tables or ideally use the
> INFORMATION_SCHEMA views. See BOL for more information.
> I'm not sure what you mean by "pass a SQL object to a function", unless
> what you're saying is that you want a stored procedure that returns some
> meta data for a table.
> If you're executing a query and want the result set details, you can do
> that easily from client in ADO or ADO.Net.
> Can you provide some additional detail about what you're trying to do?
>
> --
> David Gugick
> Imceda Software
> www.imceda.com|||Richard
Read up this article
http://www.sommarskog.se/dynamic_sql.html
"Richard" <acorn@.acorn.net> wrote in message
news:425cc012_1@.news.melbourne.pipenetworks.com...
> What I'd like to be able to do is, given the name of a table or a table
> object of some description, interrogate that table for all the fields in
> that table, and also information about those fields, so that I can process
> that information.
> As much information as possible about the fields would be nice, but at the
> minimum I need the field name, type (char, bigint etc) and size of the
> field.
> Thanks again,
> Richard
> "David Gugick" <davidg-nospam@.imceda.com> wrote in message
> news:%23UAyWB$PFHA.2972@.TK2MSFTNGP14.phx.gbl...
>|||Use the INFORMATION_SCHEMA views as suggested.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Richard" <acorn@.acorn.net> wrote in message news:425cc012_1@.news.melbourne.pipenetworks.co
m...
> What I'd like to be able to do is, given the name of a table or a table ob
ject of some
> description, interrogate that table for all the fields in that table, and
also information about
> those fields, so that I can process that information.
> As much information as possible about the fields would be nice, but at the
minimum I need the
> field name, type (char, bigint etc) and size of the field.
> Thanks again,
> Richard
> "David Gugick" <davidg-nospam@.imceda.com> wrote in message
> news:%23UAyWB$PFHA.2972@.TK2MSFTNGP14.phx.gbl...
>
I am just starting out in SQL Server and, as always, I am throwing myself in
at the deep end by trying more advanced things. I understand the basics like
INSERT, DELETE, UPDATE, SELECT etc What I am trying to do is pass some kind
of SQL object to a function which will then extract the field names, field
types, and field sizes from that object.
I have tried using DataSet and DataTable but I cannot see how I can extract
all three things I need from the column information. I'd also rather do what
I am doing on the actual table structure itself, rather than on a returned
set of records from the table (dataset).
Any help would be appreciated.
Thanks in advance,
RichardAs far as you are writing in the SQL Server group I think that you want to
get this information via TSQL ?!
If so, just look at this Query where #Orders can be replaced with the Name
of your Object to be queried. The Best way woud be to solve the Object name
to a ObjectID and to search for then, just for easy understanding it is
filtered via the object name here. :-)
Select sc.name,st.name,st.length from syscolumns sc
Inner join sysobjects so on
sc.id = so.id
Inner join systypes st on
sc.xtype = st.xtype
Where so.Xtype = 'U' And
so.name like 'Orders'
That will give you all the information youll wanted. If you want the
information via C# oder ADO.NET in common you could get i with FILLSCHEMA:
http://msdn.microsoft.com/library/d...r />
atopic.asp
HTH, Jens Smeyer.
"Richard" <acorn@.acorn.net> schrieb im Newsbeitrag
news:425cb509$1_3@.news.melbourne.pipenetworks.com...
> Hi,
> I am just starting out in SQL Server and, as always, I am throwing myself
> in at the deep end by trying more advanced things. I understand the basics
> like INSERT, DELETE, UPDATE, SELECT etc What I am trying to do is pass
> some kind of SQL object to a function which will then extract the field
> names, field types, and field sizes from that object.
> I have tried using DataSet and DataTable but I cannot see how I can
> extract all three things I need from the column information. I'd also
> rather do what I am doing on the actual table structure itself, rather
> than on a returned set of records from the table (dataset).
> Any help would be appreciated.
> Thanks in advance,
> Richard
>|||Richard wrote:
> Hi,
> I am just starting out in SQL Server and, as always, I am throwing
> myself in at the deep end by trying more advanced things. I
> understand the basics like INSERT, DELETE, UPDATE, SELECT etc What I
> am trying to do is pass some kind of SQL object to a function which
> will then extract the field names, field types, and field sizes from
> that object.
> I have tried using DataSet and DataTable but I cannot see how I can
> extract all three things I need from the column information. I'd also
> rather do what I am doing on the actual table structure itself,
> rather than on a returned set of records from the table (dataset).
> Any help would be appreciated.
> Thanks in advance,
> Richard
You can interrogate the system tables or ideally use the
INFORMATION_SCHEMA views. See BOL for more information.
I'm not sure what you mean by "pass a SQL object to a function", unless
what you're saying is that you want a stored procedure that returns some
meta data for a table.
If you're executing a query and want the result set details, you can do
that easily from client in ADO or ADO.Net.
Can you provide some additional detail about what you're trying to do?
David Gugick
Imceda Software
www.imceda.com|||What I'd like to be able to do is, given the name of a table or a table
object of some description, interrogate that table for all the fields in
that table, and also information about those fields, so that I can process
that information.
As much information as possible about the fields would be nice, but at the
minimum I need the field name, type (char, bigint etc) and size of the
field.
Thanks again,
Richard
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:%23UAyWB$PFHA.2972@.TK2MSFTNGP14.phx.gbl...
> Richard wrote:
> You can interrogate the system tables or ideally use the
> INFORMATION_SCHEMA views. See BOL for more information.
> I'm not sure what you mean by "pass a SQL object to a function", unless
> what you're saying is that you want a stored procedure that returns some
> meta data for a table.
> If you're executing a query and want the result set details, you can do
> that easily from client in ADO or ADO.Net.
> Can you provide some additional detail about what you're trying to do?
>
> --
> David Gugick
> Imceda Software
> www.imceda.com|||Richard
Read up this article
http://www.sommarskog.se/dynamic_sql.html
"Richard" <acorn@.acorn.net> wrote in message
news:425cc012_1@.news.melbourne.pipenetworks.com...
> What I'd like to be able to do is, given the name of a table or a table
> object of some description, interrogate that table for all the fields in
> that table, and also information about those fields, so that I can process
> that information.
> As much information as possible about the fields would be nice, but at the
> minimum I need the field name, type (char, bigint etc) and size of the
> field.
> Thanks again,
> Richard
> "David Gugick" <davidg-nospam@.imceda.com> wrote in message
> news:%23UAyWB$PFHA.2972@.TK2MSFTNGP14.phx.gbl...
>|||Use the INFORMATION_SCHEMA views as suggested.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Richard" <acorn@.acorn.net> wrote in message news:425cc012_1@.news.melbourne.pipenetworks.co
m...
> What I'd like to be able to do is, given the name of a table or a table ob
ject of some
> description, interrogate that table for all the fields in that table, and
also information about
> those fields, so that I can process that information.
> As much information as possible about the fields would be nice, but at the
minimum I need the
> field name, type (char, bigint etc) and size of the field.
> Thanks again,
> Richard
> "David Gugick" <davidg-nospam@.imceda.com> wrote in message
> news:%23UAyWB$PFHA.2972@.TK2MSFTNGP14.phx.gbl...
>
Question about forms in SQL
At first this seemed like a ridiculous question...but I thought I would go
ahead and ask it as I am new to SQL and experienced in MS Access.
Is there a way to have "forms" in SQL similar to the forms in MS Access? I
realize that I could just write code, i.e. ASP, and create forms, but is
there a way to have forms within SQL? How about in the newer versions?
Thanks!!Hi,
No, SQL Server do not have any forms. But you can use the forms available in
Access / VB and other development tools to
structure the front end.
Thanks
Hari
MCDBA
"michaaal" <res0gyio@.verizon.net> wrote in message
news:ebj0ye8EEHA.3336@.TK2MSFTNGP12.phx.gbl...
> At first this seemed like a ridiculous question...but I thought I would
go
> ahead and ask it as I am new to SQL and experienced in MS Access.
> Is there a way to have "forms" in SQL similar to the forms in MS Access?
I
> realize that I could just write code, i.e. ASP, and create forms, but is
> there a way to have forms within SQL? How about in the newer versions?
> Thanks!!
>|||How do you connect Access to SQL? Is it easy or do I need to do a bunch of
coding?|||Hi,
It's not that difficult but, yes, you have to code it in vb or vb .net or
any other high level language.
HTH,
Bernie Yaeger
"michaaal" <res0gyio@.verizon.net> wrote in message
news:%23zlhu7AFEHA.2628@.TK2MSFTNGP11.phx.gbl...
> How do you connect Access to SQL? Is it easy or do I need to do a bunch
of
> coding?
>|||"michaaal" <res0gyio@.verizon.net> wrote in message
news:%23zlhu7AFEHA.2628@.TK2MSFTNGP11.phx.gbl...
> How do you connect Access to SQL? Is it easy or do I need to do a bunch
of
> coding?
First create a DSN for your SQL server in Data Sources (ODBC) in Windows
File > Get External Data > Link Tables...
In the dialog box go to Files of type: and choose ODBC. Select the DSN you
have created. And Bob's your uncle
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.644 / Virus Database: 412 - Release Date: 26/03/2004|||It depends on the version of Access. From Access 2000 on it is pretty easy,
situation was further improved with Access 2002. Usage of SQL Server
doesn't usually require any more coding than is required for the use of
Access' default JET database.
For example (using Access 2003, but I think 2002 is the same) you just say
File->New and then select "Project using existing data...". Specify the
project name, select create, and it leaves you in the Data Link properties
page. Set up the properties to allow connection to your existing SQL Server
database and you are ready to go with normal Access form/report development!
Hal Berenson, SQL Server MVP
VP, Yukon Readiness
Scalability Experts, Inc.
"michaaal" <res0gyio@.verizon.net> wrote in message
news:%23zlhu7AFEHA.2628@.TK2MSFTNGP11.phx.gbl...
> How do you connect Access to SQL? Is it easy or do I need to do a bunch
of
> coding?
>
ahead and ask it as I am new to SQL and experienced in MS Access.
Is there a way to have "forms" in SQL similar to the forms in MS Access? I
realize that I could just write code, i.e. ASP, and create forms, but is
there a way to have forms within SQL? How about in the newer versions?
Thanks!!Hi,
No, SQL Server do not have any forms. But you can use the forms available in
Access / VB and other development tools to
structure the front end.
Thanks
Hari
MCDBA
"michaaal" <res0gyio@.verizon.net> wrote in message
news:ebj0ye8EEHA.3336@.TK2MSFTNGP12.phx.gbl...
> At first this seemed like a ridiculous question...but I thought I would
go
> ahead and ask it as I am new to SQL and experienced in MS Access.
> Is there a way to have "forms" in SQL similar to the forms in MS Access?
I
> realize that I could just write code, i.e. ASP, and create forms, but is
> there a way to have forms within SQL? How about in the newer versions?
> Thanks!!
>|||How do you connect Access to SQL? Is it easy or do I need to do a bunch of
coding?|||Hi,
It's not that difficult but, yes, you have to code it in vb or vb .net or
any other high level language.
HTH,
Bernie Yaeger
"michaaal" <res0gyio@.verizon.net> wrote in message
news:%23zlhu7AFEHA.2628@.TK2MSFTNGP11.phx.gbl...
> How do you connect Access to SQL? Is it easy or do I need to do a bunch
of
> coding?
>|||"michaaal" <res0gyio@.verizon.net> wrote in message
news:%23zlhu7AFEHA.2628@.TK2MSFTNGP11.phx.gbl...
> How do you connect Access to SQL? Is it easy or do I need to do a bunch
of
> coding?
First create a DSN for your SQL server in Data Sources (ODBC) in Windows
File > Get External Data > Link Tables...
In the dialog box go to Files of type: and choose ODBC. Select the DSN you
have created. And Bob's your uncle
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.644 / Virus Database: 412 - Release Date: 26/03/2004|||It depends on the version of Access. From Access 2000 on it is pretty easy,
situation was further improved with Access 2002. Usage of SQL Server
doesn't usually require any more coding than is required for the use of
Access' default JET database.
For example (using Access 2003, but I think 2002 is the same) you just say
File->New and then select "Project using existing data...". Specify the
project name, select create, and it leaves you in the Data Link properties
page. Set up the properties to allow connection to your existing SQL Server
database and you are ready to go with normal Access form/report development!
Hal Berenson, SQL Server MVP
VP, Yukon Readiness
Scalability Experts, Inc.
"michaaal" <res0gyio@.verizon.net> wrote in message
news:%23zlhu7AFEHA.2628@.TK2MSFTNGP11.phx.gbl...
> How do you connect Access to SQL? Is it easy or do I need to do a bunch
of
> coding?
>
Friday, March 9, 2012
Question about data access to SQL Compact DB / Server
I am used to work with relational SQL Server databases in other editions and am fairly new to SQL compact. I have installed the compact edition and successfully opened a connection and worked with DB objects in the Compact edition, using Server Management Studio.
I have 2-3 questions:
- Are there any means / drivers / data access installations that would allow me to connect to a SQL Compact database using the data access means that I am used to in other editions of SQL Server? In other words, can I install a data-access "converter" that would know how to talk to the SQL Compact databases over the usual TCP/1434 protocol (or any other port)? I am looking for this feature in order to connect to the SQL Compact the same way I connect to any other relational database. Right now, I am using Toad for SQL Server and it would be nice if I could connect to a SQL Compact.
- With other (non-Compact) editions, I can create an alias to a SQL Server and then specify the means to connect to the server. Can I create an alias to a SQL Compact database/server? I tried but unsuccessfully. This relates to my previous question- If I could create an alias, then I could (I imagine) connect to the Compact databases/server the same way I connect to any relational database using the alias.
- Are there any tutorials for people who are used to working with other (non-Compact) editions that are starting to work with Compact? Any BOL sections that explain the differences between other editions and Compact, from a management-tools/connectivity standpoint? I found some literature; wondering if there's something more detailed out there.
Thank you in advance.
Please be aware that SQL Server CE is a completely different beast from SQL Server. It is a in-process, embedded database, for use by a single user/application locally, and has options for synchronising with the full SQL Server product. Therefore, you caanot connect to SQL SErver CE over the network, but must load the engine, and open the SDF files of SQL CE directly from within your tool/app. A good starting point for SQL CE is in my opinion the book by Bill Vaughn, which you can get from here: http://www.hitchhikerguides.net/EBooks/default.aspx|||Erik,
Thank you for your reply.
Theoretically speaking, I imagine that I could load the engine over 1434 TCP. Do you know whether there are any such providers that would translate between the loading of the engine to the standard communication protocol?
I will look at the link you provided. Thank you again.
|||Revising my original question a little- is there something equivalent to the SMO that allows one to interface with SQL Compact?
|||No, you must use SQL Server Management Studio Express SP2 (free) or Visual Studio 2005 to manage SQL CE files.
Question about Converting Crystal syntax into SQL or VB
I'm having an issue with a date field in Access. I have imported data
from a database and the date field is a HUGE number rather than an
actual date. I have a formula from crystal reports to convert the
number into a date, but I;m not sure how to convert the crystal report
formula into a SQL or VB formula. the formula is below.
NumberVar Yyyy ;
NumberVar Mm;
NumberVar Dd;
Yyyy := Truncate ({CALL_HDW.CALLDAT_HDW} / 65536) ;
Mm := Truncate (({CALL_HDW.CALLDAT_HDW} - (Yyyy * 65536)) / 256);
Dd := Truncate ({CALL_HDW.CALLDAT_HDW} - (Yyyy * 65536) - (Mm * 256)) ;
Date (Yyyy, Mm, Dd)
I would appreciate any help!
Thanks!
JROn 3 Feb 2006 12:23:12 -0800, jennifer.rodgers@.fidessa.com wrote:
>I'm having an issue with a date field in Access. I have imported data
>from a database and the date field is a HUGE number rather than an
>actual date. I have a formula from crystal reports to convert the
>number into a date, but I;m not sure how to convert the crystal report
>formula into a SQL or VB formula. the formula is below.
>
>NumberVar Yyyy ;
>NumberVar Mm;
>NumberVar Dd;
>Yyyy := Truncate ({CALL_HDW.CALLDAT_HDW} / 65536) ;
>Mm := Truncate (({CALL_HDW.CALLDAT_HDW} - (Yyyy * 65536)) / 256);
>Dd := Truncate ({CALL_HDW.CALLDAT_HDW} - (Yyyy * 65536) - (Mm * 256)) ;
>Date (Yyyy, Mm, Dd)
>I would appreciate any help!
>Thanks!
>JR
Hi JR,
Try if this works for you:
SELECT DATEADD(year,
(CALLDAT_HDW / 65536) - 2000,
DATEADD(month,
(CALLDAT_HDW % 65536) / 256,
DATEADD(day,
CALLDAT_HDW % 256,
'19991231')))
Hugo Kornelis, SQL Server MVP
from a database and the date field is a HUGE number rather than an
actual date. I have a formula from crystal reports to convert the
number into a date, but I;m not sure how to convert the crystal report
formula into a SQL or VB formula. the formula is below.
NumberVar Yyyy ;
NumberVar Mm;
NumberVar Dd;
Yyyy := Truncate ({CALL_HDW.CALLDAT_HDW} / 65536) ;
Mm := Truncate (({CALL_HDW.CALLDAT_HDW} - (Yyyy * 65536)) / 256);
Dd := Truncate ({CALL_HDW.CALLDAT_HDW} - (Yyyy * 65536) - (Mm * 256)) ;
Date (Yyyy, Mm, Dd)
I would appreciate any help!
Thanks!
JROn 3 Feb 2006 12:23:12 -0800, jennifer.rodgers@.fidessa.com wrote:
>I'm having an issue with a date field in Access. I have imported data
>from a database and the date field is a HUGE number rather than an
>actual date. I have a formula from crystal reports to convert the
>number into a date, but I;m not sure how to convert the crystal report
>formula into a SQL or VB formula. the formula is below.
>
>NumberVar Yyyy ;
>NumberVar Mm;
>NumberVar Dd;
>Yyyy := Truncate ({CALL_HDW.CALLDAT_HDW} / 65536) ;
>Mm := Truncate (({CALL_HDW.CALLDAT_HDW} - (Yyyy * 65536)) / 256);
>Dd := Truncate ({CALL_HDW.CALLDAT_HDW} - (Yyyy * 65536) - (Mm * 256)) ;
>Date (Yyyy, Mm, Dd)
>I would appreciate any help!
>Thanks!
>JR
Hi JR,
Try if this works for you:
SELECT DATEADD(year,
(CALLDAT_HDW / 65536) - 2000,
DATEADD(month,
(CALLDAT_HDW % 65536) / 256,
DATEADD(day,
CALLDAT_HDW % 256,
'19991231')))
Hugo Kornelis, SQL Server MVP
Question about connection pooling
If I have two ASP.NET applications installed on the same machine, and both are using the same connection string to access SQL Server 2005. Are they share the same pool or use separated pools?
I need to be sure that how many connections would be connected to my database server. For example, the max. connection pool size is 100, if both Web app share the same pool, then there're may be maximum 100 connections to database. It they use separated pools, then connections may grow to 200.
Hi,
per documentation: "Connections are pooled per process, per application domain, per connection string and when using integrated security, per Windows identity."
Source:http://msdn2.microsoft.com/en-us/library/8xx3tyca.aspx (using Connection Pooling)
It means your application would have separate pools, since they are different applications and therefore have their own app domain.
Another thing is that maximum connection pool size is configurable (within connectionstring), 100 is the default maximum limit.
|||
joteke:
per documentation: "Connections are pooled per process, per application domain, per connection string and when using integrated security, per Windows identity."
Source:http://msdn2.microsoft.com/en-us/library/8xx3tyca.aspx (using Connection Pooling)It means your application would have separate pools, since they are different applications and therefore have their own app domain.
What if my ASP.NET applications all use the same application pool? In this way, are they still have separate pools?
Thank you!
|||They don't because they have different appdomains. Every ASP.NEt application has its own AppDomain. Only connections in same application, using exactly same connection string, would use the same connection pool.|||Ok! Now I got it.
Thank your for your help :)
Monday, February 20, 2012
Question
Hi
Can sql server jobs we viewed by account without sa access? If so what
permissions need to be granted?
Answered here:
http://groups.google.de/group/micros...3ff63979b36397
AND here:
http://groups.google.de/group/micros...aba889e67fe33c
"bjones" wrote:
> Hi
> Can sql server jobs we viewed by account without sa access? If so what
> permissions need to be granted?
|||Hi
granted access to the RepositoryUser role in msdb and still can't see the
jobs. Tried explicitly granting exec to some job related procedures and
select on tables in msdb and still no luck. Don't want to grant sa. There
must be an alternative?
"Jens Sü?meyer" wrote:
[vbcol=seagreen]
> Answered here:
> http://groups.google.de/group/micros...3ff63979b36397
>
> AND here:
> http://groups.google.de/group/micros...aba889e67fe33c
>
> "bjones" wrote:
Can sql server jobs we viewed by account without sa access? If so what
permissions need to be granted?
Answered here:
http://groups.google.de/group/micros...3ff63979b36397
AND here:
http://groups.google.de/group/micros...aba889e67fe33c
"bjones" wrote:
> Hi
> Can sql server jobs we viewed by account without sa access? If so what
> permissions need to be granted?
|||Hi
granted access to the RepositoryUser role in msdb and still can't see the
jobs. Tried explicitly granting exec to some job related procedures and
select on tables in msdb and still no luck. Don't want to grant sa. There
must be an alternative?
"Jens Sü?meyer" wrote:
[vbcol=seagreen]
> Answered here:
> http://groups.google.de/group/micros...3ff63979b36397
>
> AND here:
> http://groups.google.de/group/micros...aba889e67fe33c
>
> "bjones" wrote:
Question
Hi
Can sql server jobs we viewed by account without sa access? If so what
permissions need to be granted?Answered here:
http://groups.google.de/group/microsoft.public.sqlserver.security/browse_thread/thread/1f7ed5fedba0a853/3c3ff63979b36397?q=jens+S%C3%BC%C3%9Fmeyer+sql+server+jobs&rnum=2&hl=de#3c3ff63979b36397
AND here:
http://groups.google.de/group/microsoft.public.sqlserver.server/browse_thread/thread/795ec20703b7b535/06aba889e67fe33c?q=jens+S%C3%BC%C3%9Fmeyer+sql+server+jobs&rnum=3&hl=de#06aba889e67fe33c
"bjones" wrote:
> Hi
> Can sql server jobs we viewed by account without sa access? If so what
> permissions need to be granted?|||Hi
granted access to the RepositoryUser role in msdb and still can't see the
jobs. Tried explicitly granting exec to some job related procedures and
select on tables in msdb and still no luck. Don't want to grant sa. There
must be an alternative?
"Jens Sü�meyer" wrote:
> Answered here:
> http://groups.google.de/group/microsoft.public.sqlserver.security/browse_thread/thread/1f7ed5fedba0a853/3c3ff63979b36397?q=jens+S%C3%BC%C3%9Fmeyer+sql+server+jobs&rnum=2&hl=de#3c3ff63979b36397
>
> AND here:
> http://groups.google.de/group/microsoft.public.sqlserver.server/browse_thread/thread/795ec20703b7b535/06aba889e67fe33c?q=jens+S%C3%BC%C3%9Fmeyer+sql+server+jobs&rnum=3&hl=de#06aba889e67fe33c
>
> "bjones" wrote:
> > Hi
> >
> > Can sql server jobs we viewed by account without sa access? If so what
> > permissions need to be granted?
Can sql server jobs we viewed by account without sa access? If so what
permissions need to be granted?Answered here:
http://groups.google.de/group/microsoft.public.sqlserver.security/browse_thread/thread/1f7ed5fedba0a853/3c3ff63979b36397?q=jens+S%C3%BC%C3%9Fmeyer+sql+server+jobs&rnum=2&hl=de#3c3ff63979b36397
AND here:
http://groups.google.de/group/microsoft.public.sqlserver.server/browse_thread/thread/795ec20703b7b535/06aba889e67fe33c?q=jens+S%C3%BC%C3%9Fmeyer+sql+server+jobs&rnum=3&hl=de#06aba889e67fe33c
"bjones" wrote:
> Hi
> Can sql server jobs we viewed by account without sa access? If so what
> permissions need to be granted?|||Hi
granted access to the RepositoryUser role in msdb and still can't see the
jobs. Tried explicitly granting exec to some job related procedures and
select on tables in msdb and still no luck. Don't want to grant sa. There
must be an alternative?
"Jens Sü�meyer" wrote:
> Answered here:
> http://groups.google.de/group/microsoft.public.sqlserver.security/browse_thread/thread/1f7ed5fedba0a853/3c3ff63979b36397?q=jens+S%C3%BC%C3%9Fmeyer+sql+server+jobs&rnum=2&hl=de#3c3ff63979b36397
>
> AND here:
> http://groups.google.de/group/microsoft.public.sqlserver.server/browse_thread/thread/795ec20703b7b535/06aba889e67fe33c?q=jens+S%C3%BC%C3%9Fmeyer+sql+server+jobs&rnum=3&hl=de#06aba889e67fe33c
>
> "bjones" wrote:
> > Hi
> >
> > Can sql server jobs we viewed by account without sa access? If so what
> > permissions need to be granted?
Question
Hi
Can sql server jobs we viewed by account without sa access? If so what
permissions need to be granted?Answered here:
http://groups.google.de/group/micro...c3ff63979b36397
AND here:
http://groups.google.de/group/micro...6aba889e67fe33c
"bjones" wrote:
> Hi
> Can sql server jobs we viewed by account without sa access? If so what
> permissions need to be granted?|||Hi
granted access to the RepositoryUser role in msdb and still can't see the
jobs. Tried explicitly granting exec to some job related procedures and
select on tables in msdb and still no luck. Don't want to grant sa. There
must be an alternative?
"Jens Sü?meyer" wrote:
[vbcol=seagreen]
> Answered here:
> http://groups.google.de/group/micro...c3ff63979b36397
>
> AND here:
> http://groups.google.de/group/micro...6aba889e67fe33c
>
> "bjones" wrote:
>
Can sql server jobs we viewed by account without sa access? If so what
permissions need to be granted?Answered here:
http://groups.google.de/group/micro...c3ff63979b36397
AND here:
http://groups.google.de/group/micro...6aba889e67fe33c
"bjones" wrote:
> Hi
> Can sql server jobs we viewed by account without sa access? If so what
> permissions need to be granted?|||Hi
granted access to the RepositoryUser role in msdb and still can't see the
jobs. Tried explicitly granting exec to some job related procedures and
select on tables in msdb and still no luck. Don't want to grant sa. There
must be an alternative?
"Jens Sü?meyer" wrote:
[vbcol=seagreen]
> Answered here:
> http://groups.google.de/group/micro...c3ff63979b36397
>
> AND here:
> http://groups.google.de/group/micro...6aba889e67fe33c
>
> "bjones" wrote:
>
Subscribe to:
Posts (Atom)