is there a function "IsType"?

Hi guys
I want to know how to write a function IsNumericType
IsNumericType(value)
so it returns: SmallInt, or Int, or TinyIntThere is a ISNUMERIC function, but in many cases it is of little utility.
One can create a scalar UDF with PATINDEX to determine if a given value is
numeric or not. Search the archives of this newsgroup and you'll find
several examples.
In t-SQL, you cannot distingush the exact type of a value, unless it is
assigned to a declared variable or belongs to a typed column.
Anith|||so let say it belongs to a typed column,
how to find out what type of column is it ?
I don't want to use sp_help, sp_help shows all the columns, but I only want
to show one.
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:eTR7xhEmFHA.1232@.TK2MSFTNGP15.phx.gbl...
> There is a ISNUMERIC function, but in many cases it is of little utility.
> One can create a scalar UDF with PATINDEX to determine if a given value is
> numeric or not. Search the archives of this newsgroup and you'll find
> several examples.
> In t-SQL, you cannot distingush the exact type of a value, unless it is
> assigned to a declared variable or belongs to a typed column.
> --
> Anith
>|||SELECT data_type
FROM information_schema.columns
WHERE table_schema = 'dbo'
AND table_name = 'table_name'
AND column_name = 'column_name' ;
David Portas
SQL Server MVP
--

Is there a correct syntax for writing a query?

Is there a best syntax for writing a query (specifically in sql server with
t-sql)? I've written a few with the JOIN keywords but I mostly write them
using '=', '*=', etc... Is there a more 'sql-compliant' way or is it just a
matter of preference?
THanks.*= and =* are old syntaxes and might lead to ambiguous queries.
SQL Server 2005 does not support this syntax.
Use the ansi-92 syntax of left outer and right outer join.
You can refer to BOL for more information on this.
Hope this helps.|||Use the Join keyword.
JOIN is part of the ANSI syntax.
BOL 2005 says:
The outer join operators (*= and =*) are not supported when the
compatibility level of the database is set to 90.
David Lundell
Principal Consultant and Trainer
www.MutuallyBeneficial.com
David@.MutuallyBeneficial.com
"VMI" <VMI@.discussions.microsoft.com> wrote in message
news:243B3E9F-5935-46BE-A6A6-A1C51DF6B1F7@.microsoft.com...
> Is there a best syntax for writing a query (specifically in sql server
> with
> t-sql)? I've written a few with the JOIN keywords but I mostly write them
> using '=', '*=', etc... Is there a more 'sql-compliant' way or is it just
> a
> matter of preference?
> THanks.|||> Is there a best syntax for writing a query (specifically in sql server with
> t-sql)? I've written a few with the JOIN keywords but I mostly write them
> using '=', '*=', etc... Is there a more 'sql-compliant' way or is it just
a
> matter of preference?
>
There are situations where *= can actually give you bad results. Stay away
from it.|||Can you give an example. Say you have the same value repeated in 3 rows for
a
column, if you want to remove duplicates and still get 3 rows. Then what
value do you want to have in that column?|||Oops.. wrong post :(

is there a collection of all database objects

i want to write a tool to make scripting of databases easy. the user will select the server and dababase, then they will select what they want to script: eg tables, sprocs, permissions, scheemas etc etc. currently i have a load of check boxes , one for each type of object in the db ( one for tables, one for sprocs etc). this is very long winded,and if there was a collection of the db objects i could call into a list box it would be a lot tidier and easier.

There are a number of collections within the Database object in SMO which you can use, such as Tables, Triggers, StoredProcedures, Users and Views. Each of these is a collection of the named objects within the database. (At the Server level of course, there's a Databases collection which will get you to each Database object on the server.)

Hope that helps.

sql

Is there a better way to write this?

I have a set base query which is causing me problems... the query look like
this
insert into TableA
(columnA1)
select
columnB1 = (select columnB1 from TableB where columnB2 = a.columnC2 and
columnB3 = a.columnC3)
from TableC as a
It's actaully population of a fact table from dimension tables. My problem
is that the subquery is failing because it is returning more than 1 value
from TableB, but I'm unable to pull out the records that are really
returning me the error. Anyone got any suggestions?Why the subquery? Why not use something like this (untested):
insert into TableA (columnA1)
select b.columnB1 from TableB b
inner join TableC c
on b.columnB2 = c.columnC2
and b.columnB3 = c.columnC3
mike hodgson
http://sqlnerd.blogspot.com
"Nestor" <n3570r@.yahoo.com> wrote in message
news:%23rxneaJHGHA.344@.TK2MSFTNGP09.phx.gbl...
>I have a set base query which is causing me problems... the query look
>like this
> insert into TableA
> (columnA1)
> select
> columnB1 = (select columnB1 from TableB where columnB2 = a.columnC2 and
> columnB3 = a.columnC3)
> from TableC as a
> It's actaully population of a fact table from dimension tables. My problem
> is that the subquery is failing because it is returning more than 1 value
> from TableB, but I'm unable to pull out the records that are really
> returning me the error. Anyone got any suggestions?
>|||Thanks for the quick respond.. primarily the reason is that the actual query
is actaully a lot more complicated than the example, something like this
insert into TableA
(columnA1, columnA2, columnA3, ...)
select
columnB1 = (select columnB1 from TableB where columnB2 = a.columnC2 and
columnB3 = a.columnC3)
columnB2 = (select columnB2 from TableD where ...)
columnB3 = (other conditions)
from TableC as a
where ...
and not exists (...)
I don't think I can really write it using join statements considering the
tables and conditions involved?
"Mike Hodgson" <e1minst3r@.gmail.com> wrote in message
news:OzbZFlJHGHA.2444@.TK2MSFTNGP11.phx.gbl...
> Why the subquery? Why not use something like this (untested):
> insert into TableA (columnA1)
> select b.columnB1 from TableB b
> inner join TableC c
> on b.columnB2 = c.columnC2
> and b.columnB3 = c.columnC3
>
> --
> mike hodgson
> http://sqlnerd.blogspot.com
>
> "Nestor" <n3570r@.yahoo.com> wrote in message
> news:%23rxneaJHGHA.344@.TK2MSFTNGP09.phx.gbl...
>|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications.
A wild guess not supported in any way by your posting:
INSERT INTO TableA (columnA1)
SELECT B.columnB1
FROM TableB AS B
WHERE B.columnB2 = A.columnC2
AND B.columnB3 = A.columnC3);
The syntax was wrong afte that point. This can still blow up since we
have no DDL.|||to find the rows that are causing you fits, try something like
SELECT B2, B3, COUNT(*)
FROM TableB
GROUP BY B2, B3
HAVING COUNT(*) > 1
See if that points you in the right direction.
Stu|||Surely you can still do it without all the subqueries (unless the values in
different columns for a single row are unrelated), you just need to figure
out the appropriate SELECT statement. It's hard to offer helpful
suggestions in your case as you've provided us with very little info -
posting some relevant schema & test data, as Joe suggested, would make it
easier for others to help you.
mike hodgson
http://sqlnerd.blogspot.com
"Nestor" <n3570r@.yahoo.com> wrote in message
news:%23d$1SwJHGHA.3036@.tk2msftngp13.phx.gbl...
> Thanks for the quick respond.. primarily the reason is that the actual
> query is actaully a lot more complicated than the example, something like
> this
> insert into TableA
> (columnA1, columnA2, columnA3, ...)
> select
> columnB1 = (select columnB1 from TableB where columnB2 = a.columnC2 and
> columnB3 = a.columnC3)
> columnB2 = (select columnB2 from TableD where ...)
> columnB3 = (other conditions)
> from TableC as a
> where ...
> and not exists (...)
> I don't think I can really write it using join statements considering the
> tables and conditions involved?
> "Mike Hodgson" <e1minst3r@.gmail.com> wrote in message
> news:OzbZFlJHGHA.2444@.TK2MSFTNGP11.phx.gbl...
>|||Is the problem that you have duplicate rows which you need to clean up?
Stu suggested and approach to identify the duplicates. A unique constraint
will prevent them from occurring in the future.
Or do you want to have the SQL ignore these duplicates, and just take the
first value that it finds?
Try using max(columnB1) or Min(columnB1) in your sub select
OR try this...
columnB1 = (select top 1 columnB1 from TableB where columnB2 =
a.columnC2 and columnB3 = a.columnC3)
Or is your join incorrect in the subquery and you need another key field in
there?
Maybe the data is perfect and you are just not being specific enough?
As others have said, the DDL will help make some of this clear, as will a
more in depth explanation.
"Nestor" <n3570r@.yahoo.com> wrote in message
news:%23rxneaJHGHA.344@.TK2MSFTNGP09.phx.gbl...
> I have a set base query which is causing me problems... the query look
like
> this
> insert into TableA
> (columnA1)
> select
> columnB1 = (select columnB1 from TableB where columnB2 = a.columnC2 and
> columnB3 = a.columnC3)
> from TableC as a
> It's actaully population of a fact table from dimension tables. My problem
> is that the subquery is failing because it is returning more than 1 value
> from TableB, but I'm unable to pull out the records that are really
> returning me the error. Anyone got any suggestions?
>

Is there a better way to write this SQL?

I don't like the idea of using a cursor and then also using a while
loop for each row. Is there a better way to do the same thing?
The problem is that I have a table called TimePeriods that is the
following:
CompanyID
TimePeriodID
BeginDate
EndDate
I need a table that has each date of the time period so I can look up
other data that is associated to a date.
---
CREATE FUNCTION dbo.f_GetDaysInTimePeriod()
RETURNS @.Results TABLE
(
CompanyID VARCHAR(5),
TimePeriodID INTEGER,
SearchDate SMALLDATETIME
)
AS
BEGIN
DECLARE @.CompanyID AS VARCHAR(5)
DECLARE @.TimePeriodID AS INTEGER
DECLARE @.InsertDate AS SMALLDATETIME
DECLARE @.ThruDate AS SMALLDATETIME
DECLARE TimePeriod_Cursor CURSOR FOR SELECT CompanyID, TimePeriodID,
BeginDate, ThruDate from TimePeriods
OPEN TimePeriod_Cursor
-- Perform the first fetch.
FETCH NEXT FROM TimePeriod_Cursor INTO @.CompanyID, @.TimePeriodID,
@.InsertDate, @.ThruDate
-- Check @.@.FETCH_STATUS to see if there are any more rows to fetch.
WHILE @.@.FETCH_STATUS = 0
BEGIN
-- This is executed as long as the previous fetch succeeds.
WHILE @.InsertDate <= @.ThruDate
BEGIN
INSERT INTO @.Results values(@.CompanyID, @.TimePeriodID, @.InsertDate)
SELECT @.InsertDate = DATEADD(d,1,@.InsertDate)
END
FETCH NEXT FROM TimePeriod_Cursor INTO @.CompanyID, @.TimePeriodID,
@.InsertDate, @.ThruDate
END
CLOSE TimePeriod_Cursor
DEALLOCATE TimePeriod_Cursor
RETURN
END
---
Thanks for any insight...Could you copy a small sample of records from the TimePeriods table and also
an example of your required output?
<standish22@.hotmail.com> wrote in message
news:1121881564.693074.9270@.o13g2000cwo.googlegroups.com...
>I don't like the idea of using a cursor and then also using a while
> loop for each row. Is there a better way to do the same thing?
> The problem is that I have a table called TimePeriods that is the
> following:
> CompanyID
> TimePeriodID
> BeginDate
> EndDate
> I need a table that has each date of the time period so I can look up
> other data that is associated to a date.
> ---
> CREATE FUNCTION dbo.f_GetDaysInTimePeriod()
> RETURNS @.Results TABLE
> (
> CompanyID VARCHAR(5),
> TimePeriodID INTEGER,
> SearchDate SMALLDATETIME
> )
> AS
> BEGIN
> DECLARE @.CompanyID AS VARCHAR(5)
> DECLARE @.TimePeriodID AS INTEGER
> DECLARE @.InsertDate AS SMALLDATETIME
> DECLARE @.ThruDate AS SMALLDATETIME
> DECLARE TimePeriod_Cursor CURSOR FOR SELECT CompanyID, TimePeriodID,
> BeginDate, ThruDate from TimePeriods
> OPEN TimePeriod_Cursor
> -- Perform the first fetch.
> FETCH NEXT FROM TimePeriod_Cursor INTO @.CompanyID, @.TimePeriodID,
> @.InsertDate, @.ThruDate
>
> -- Check @.@.FETCH_STATUS to see if there are any more rows to fetch.
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> -- This is executed as long as the previous fetch succeeds.
> WHILE @.InsertDate <= @.ThruDate
> BEGIN
> INSERT INTO @.Results values(@.CompanyID, @.TimePeriodID, @.InsertDate)
> SELECT @.InsertDate = DATEADD(d,1,@.InsertDate)
> END
> FETCH NEXT FROM TimePeriod_Cursor INTO @.CompanyID, @.TimePeriodID,
> @.InsertDate, @.ThruDate
> END
> CLOSE TimePeriod_Cursor
> DEALLOCATE TimePeriod_Cursor
> RETURN
> END
> ---
> Thanks for any insight...
>|||I don't know what's in "TimePeriods" in your example but I don't see
why you can't just use a Calendar table containing as many dates as
you'll ever need:
CREATE TABLE Calendar (dt DATETIME PRIMARY KEY)
SELECT dt, ...
FROM Calendar
WHERE dt >= '20050101'
AND dt < '20060101'
Just join the Calendar table into your queries as required.
If you need mroe help, please post DDL, sample data INSERTs and show us
your required end result so that we can understand you better. I think
your cursor won't work anyway because you would need to specify ORDER
BY - cursors aren't ordered by default.
David Portas
SQL Server MVP
--|||>> I need a table that has each date of the time period so I can look up oth
er data that is associated to a date. <<
Build a Calendar table with all your temporal data. SQL likes tables
and not procedural code. You then use:
WHERE C.cal_date BETWEEN F.start_date AND F.end_date|||Sample data in the TimePeriods:
CompanyID TimePeriodID Descr BeginDate ThruDate
----
1 1 W1 5/30/2005 6/5/2005
1 2 W2 6/6/2005 6/12/2005
1 3 W3 6/13/2005 6/19/2005
I'm looking to return a table from the UDF that would be:
CompanyID TimePeriodID TimecardDate
1 1 5/30/2005
1 1 5/31/2005
1 1 6/1/2005
1 1 6/2/2005
1 1 6/3/2005
1 1 6/4/2005
1 1 6/5/2005
1 2 6/6/2005
1 2 6/7/2005
1 2 6/8/2005
1 2 6/9/2005
1 2 6/10/2005
1 2 6/11/2005
1 2 6/12/2005
1 3 6/13/2005
1 3 6/14/2005
1 3 6/15/2005
1 3 6/16/2005
1 3 6/17/2005
1 3 6/18/2005
1 3 6/19/2005|||Calendar table. Put the w number in there as well if it helps. Why
make life difficult by only storing the beginning and ending dates? You
can create the Periods table as an indexed view.
David Portas
SQL Server MVP
--|||to make it easier for the user.
They could have a time period be a w, a month, a year, or anything.
Having them enter all the days of each time period would be
unacceptable. This calendar table will probably work quite nicely,
however. I've never seen that technique.
Thanks...|||No-one says the user has to enter the days individually. Generate all
the rows once only, at install time. The user changes are just:
UPDATE ...
WHERE dt BETWEEN ...
David Portas
SQL Server MVP
--|||>> I've never seen that technique. <<
Get a copy of SQL FOR SMARTIES; you are in for a lot of surprises :)

Is subquery bad?

Hi,
I got sceamed at by a lead developerfor using subquery. That'sOK, but I want
to make sure he was right.
Here was the story:
I was asked to write statments that return customers from a table excluding
duplicated records based on email.
My statement:
SELECT
c.*
FROM
customers c
JOIN
(
SELECT
MAX(CustomerID) AS CustomerID
FROM
customers
GROUP BY
email
) s
ON
c.CustomerID = s.CustomerID
The statment worked, but the lead developer said I shouldn't use subquery in
any circumstances, use temp table instead.
In this example, if there are 1 million records in the customers table, the
subquery will get executed 1 million times. Was he right?
TIAThere is nothing at all inherently wrong with subqueries. And in MANY cases,
subqueries are BETTER than temp tables. Temp tables are good when you need
the data in multiple queries, but if you're only going to use it in a single
query, your derived table is probably as good or better than a temp table.
The subquery will not be 'executed' a million times. It will be treated as
if it's a table and joined to the outer query exactly as if it were a temp
table, without all the overhead of creating a new object.
You can try writing this query with a temp table, and measuring the
performance of each, and showing your lead developer that he still has
things to learn, as we all do.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Raymond Du" <rdrd@.yahoo.com> wrote in message
news:%23eNzccaPGHA.720@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I got sceamed at by a lead developerfor using subquery. That'sOK, but I
> want to make sure he was right.
> Here was the story:
> I was asked to write statments that return customers from a table
> excluding duplicated records based on email.
> My statement:
> SELECT
> c.*
> FROM
> customers c
> JOIN
> (
> SELECT
> MAX(CustomerID) AS CustomerID
> FROM
> customers
> GROUP BY
> email
> ) s
> ON
> c.CustomerID = s.CustomerID
> The statment worked, but the lead developer said I shouldn't use subquery
> in any circumstances, use temp table instead.
> In this example, if there are 1 million records in the customers table,
> the subquery will get executed 1 million times. Was he right?
> TIA
>
>
>|||--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
Well, your solution could have been done like this:
SELECT CustomerID
FROM Customers
GROUP BY CustomerID
HAVING COUNT(DISTINCT email) = 1
But, he was wrong about your "subquery" 'cuz it is a derived table. The
optimizer should generate this derived table before running the rest of
the query - IOW, that "subquery" runs once. A temp table is created for
the derived table in the tempdb DB and the join is between the regular
table Customers and the temporary, derived table in tempdb.
He was thinking about correlated subqueries. Something like this:
SELECT *
FROM Orders As O
WHERE OrderDate = (SELECT MAX(OrderDAte)
FROM Orders
WHERE CustomerID = O.CustomerID)
This subquery will run for each CustomerID in Orders table.
He's wrong about never using subqueries. That's like saying never use
reverse in your car 'cuz it will reverse the odometer. Crikey!
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/ AwUBRAZqp4echKqOuFEgEQLF6QCgni0awZ4bUAKb
0jaksFEj/lgoFwsAoNMv
FDzir29jPID44xdI+H42ERoj
=JcMp
--END PGP SIGNATURE--
Raymond Du wrote:
> Hi,
> I got sceamed at by a lead developerfor using subquery. That'sOK, but I wa
nt
> to make sure he was right.
> Here was the story:
> I was asked to write statments that return customers from a table excludi
ng
> duplicated records based on email.
> My statement:
> SELECT
> c.*
> FROM
> customers c
> JOIN
> (
> SELECT
> MAX(CustomerID) AS CustomerID
> FROM
> customers
> GROUP BY
> email
> ) s
> ON
> c.CustomerID = s.CustomerID
> The statment worked, but the lead developer said I shouldn't use subquery
in
> any circumstances, use temp table instead.
> In this example, if there are 1 million records in the customers table, th
e
> subquery will get executed 1 million times. Was he right?|||Fear of Subqueries is amazingly common, and while they can be misused,
I find temp tables to be misused far more often.
Yesterday I got a desperate call about a stored procedure that was
timing out. Since it is part of the system for all managers in the
company to enter the data for their staff's annual raises this was
considered a Very Bad Thing.
I made many changes to the proc, but one change in particular cut the
execution time by about two thirds. I removed one temp table (there
were two) and replaced it with a derived table in the one place it was
referenced. By putting all the information in the one query the
optimizer had a better picture of the data, and did a much better job.
SQL is a wonderfully powerful language, and most of that power is
reached by using subqueries. If you have not used them, and learned
from experience what they can do and how well they can do it, it is
easy to reason your way into believing they can not be efficient. But
nobody with actual experience would make such a blanket statement.
Roy Harvey
Beacon Falls, CT
On Wed, 1 Mar 2006 19:12:05 -0800, "Raymond Du" <rdrd@.yahoo.com>
wrote:

>Hi,
>I got sceamed at by a lead developerfor using subquery. That'sOK, but I wan
t
>to make sure he was right.
>Here was the story:
>I was asked to write statments that return customers from a table excludin
g
>duplicated records based on email.
>My statement:
>SELECT
> c.*
>FROM
> customers c
>JOIN
> (
> SELECT
> MAX(CustomerID) AS CustomerID
> FROM
> customers
> GROUP BY
> email
> ) s
>ON
> c.CustomerID = s.CustomerID
>The statment worked, but the lead developer said I shouldn't use subquery i
n
>any circumstances, use temp table instead.
>In this example, if there are 1 million records in the customers table, the
>subquery will get executed 1 million times. Was he right?
>TIA
>
>|||just wanted to add that in some cases temp tables have one clear
advantage: you can build indexes/statistics on them. That done, the
optimizer might have a better estimate, and the query might run much
faster.
But such cases are not frequent, and they are definitely less frequent
now than with SQL Server 7.|||In your case, if CustomerID is an indexed column then the subquery is
not bad. The join will be faster because it can use the CustomerID
index to s matching columns. In case of temp table an index s may
not have been possible.
Depending on how you use them, subqueries can be bad or good.|||The SQL Server query optimizer will not execute this subquery N times.
Subqueries are not bad (quite the opposite - they are very powerful).
In far more complex queries, the optimizer may not be able to determine the
proper join orders to use and temporary tables could be useful. I wouldn't
use this as a default option - usually only as a workaround for specific
queries where the estimated rows vs. actual rows are not anywhere close (one
reason that this could happen is that there is data correlation between
columns used in predicates in the query).
I'll suggest that you try to run each and see how much faster/slower your
original query is to the temporary table path.
Thanks,
Conor Cunningham
SQL Server Query Optimization Development Lead
"Raymond Du" <rdrd@.yahoo.com> wrote in message
news:%23eNzccaPGHA.720@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I got sceamed at by a lead developerfor using subquery. That'sOK, but I
> want to make sure he was right.
> Here was the story:
> I was asked to write statments that return customers from a table
> excluding duplicated records based on email.
> My statement:
> SELECT
> c.*
> FROM
> customers c
> JOIN
> (
> SELECT
> MAX(CustomerID) AS CustomerID
> FROM
> customers
> GROUP BY
> email
> ) s
> ON
> c.CustomerID = s.CustomerID
> The statment worked, but the lead developer said I shouldn't use subquery
> in any circumstances, use temp table instead.
> In this example, if there are 1 million records in the customers table,
> the subquery will get executed 1 million times. Was he right?
> TIA
>
>|||The advantage is not so clear when temp tables are in a stored
procedure, as the index does not exist when the execution plans are
formulated. I believe 2000 took care of this in part by adding extra
overhead that caused the plan to be re-evaluated as the proc executed.
Roy Harvey
Beacon Falls, CT
On 2 Mar 2006 06:40:06 -0800, "Alexander Kuznetsov"
<AK_TIREDOFSPAM@.hotmail.COM> wrote:

>just wanted to add that in some cases temp tables have one clear
>advantage: you can build indexes/statistics on them. That done, the
>optimizer might have a better estimate, and the query might run much
>faster.
>But such cases are not frequent, and they are definitely less frequent
>now than with SQL Server 7.|||in 2000, the plans are recompiled when necessary.|||On 2 Mar 2006 10:15:54 -0800, "Alexander Kuznetsov"
<AK_TIREDOFSPAM@.hotmail.COM> wrote:

>in 2000, the plans are recompiled when necessary.
Which is overhead, possible considerable.
Roy

Is Sql Mobile a generic database engine?

Hi,

I am about to write an application of the Pocket PC 2003 SE. I plan on using CF2.0 and VS 2005. The application will need to store several different peices of information, I immediately thought of using a database.

I have read somewhere that Sql Mobile 05 will only allow databases on the device via replication with Sql 2005. I don't want to use replication. I just want to store several diffent peices of information for use with my mobile app.

Would Sql mobile be suitable or should I stick to something like persisted ADO Data tables?

Thanks for your help

GrahamWell, so far it seems to be!

I created a CF2.0 PPC 2003 project and inserted a Sql Mobile database. I could then define tables and insert data from the mobile app at runtime.
Graham|||

The answer to your question in the subject is yes.

There are few things to keep in mind. SQL Server 2005 Mobile or it's earlier version SQL Server CE have simpler engine and thus have simpler set of features and somewhat more restrictions.

E.g. a recent post on this forum was about using semicolon to separate statements. It is not strictly required on full version but it is on Mobile.

You can find some additional information here

http://msdn.microsoft.com/SQL/2005/mobile/default.aspx

and by searching MSDN for SQL Mobile specific topics. You can also download Books Online only for SQL Server 2005 and search there. Check this URL (it is referred from the general SQL Server page):

http://www.microsoft.com/downloads/details.aspx?FamilyId=ADC75E35-7245-4038-9B8A-B8FABAEC16DA&displaylang=en

In addition to that – may be a useful feature in the development – if you have SQL Server 2005 (not Mobile version) Management Studio installed, you can create a SQL Server Mobile database (select SQL Server Mobile in server types when connecting in Object Explorer). It may help you to “play with” queries, syntax, etc. It is very likely that you can do the same from VS2005. I haven’t tried that myself.

Regards,
Boris.

|||Yet another URL of interest:

http://www.microsoft.com/sql/ce/productinfo/SQLMobile.mspx