AUTOGROW info recorded: visual

Showing posts with label visual. Show all posts

Wednesday, March 28, 2012

Is it possible to link via ADODB from an Access 2K .mdb file?

Hi all,
I am a newbie to SQL server and I am trying to link via ADODB from an Access
2000 .mdb file in Visual Basic to SQL server but I receive an error during
compilation at the "Dim rs As ADODB.Recordset" statement already.
It works if I do the same from an Access project file.
I assume this is not possible and I need to connect via DAO.
Does this also mean that I do not have the option to lock records at all if
I work
with a .mdb file?
Please help - I am puzzled.
Thanks.
Oliver
Let me give this a try, assuming I understand your scenario correctly.
You have an Access .mdb front-end that you wish to link
programmatically to a SQL Server database. If that is correct, then
you can create the link using a DAO.TableDef, not a recordset. You set
the properties of the TableDef, which include the connection string,
name, etc. The linked table is a Jet object, and DAO is always the
best choice when working with Jet objects. If you wish to create a
recordset based on SQL Server data, then use an ADO recordset. To
summarize: Jet=DAO, SQL Server=ADO.
--Mary
On Fri, 3 Feb 2006 07:41:57 -0800, Oliver <iron@.programmer.com> wrote:

>Hi all,
>I am a newbie to SQL server and I am trying to link via ADODB from an Access
>2000 .mdb file in Visual Basic to SQL server but I receive an error during
>compilation at the "Dim rs As ADODB.Recordset" statement already.
>It works if I do the same from an Access project file.
>I assume this is not possible and I need to connect via DAO.
>Does this also mean that I do not have the option to lock records at all if
>I work
>with a .mdb file?
>Please help - I am puzzled.
>Thanks.
>Oliver
|||Thanks Mary, but is it possible to lock records on SQL server with DAO?
If not I will have to convert my .mdb into a project as I think ADO is only
possible if the Access client application is a project file (.adp extension).
I do not like to do this because I then have about 850 Queries that do not
work anymore! I would then need to convert all queries into stored procedures
and views - is that correct or is there a way around it?
Thanks.
Oliver
"Mary Chipman [MSFT]" wrote:

> Let me give this a try, assuming I understand your scenario correctly.
> You have an Access .mdb front-end that you wish to link
> programmatically to a SQL Server database. If that is correct, then
> you can create the link using a DAO.TableDef, not a recordset. You set
> the properties of the TableDef, which include the connection string,
> name, etc. The linked table is a Jet object, and DAO is always the
> best choice when working with Jet objects. If you wish to create a
> recordset based on SQL Server data, then use an ADO recordset. To
> summarize: Jet=DAO, SQL Server=ADO.
> --Mary
> On Fri, 3 Feb 2006 07:41:57 -0800, Oliver <iron@.programmer.com> wrote:
>
|||Locking records on SQL Server from any client is a BIG mistake. SQLS
is very efficient at holding locks for the minimum amount of time
required. Locking records on the client for long periods of time
causes blocking and deadlocks (scenario--user runs code that locks
records, goes to lunch, leaving records locked). Another process
cannot even SEE the data if you are using the default READ COMMITTED
isolation level (see SQL Books Online for more info).
You should use other methods to control concurrency violations, such
as designing table schema to partition tables so that users don't
access the same record at the same time, using timestamps to detect
concurrency problems, or creating a column in the table that
increments each time a record is updated (you check this value in your
code prior to updating and increment during the update). If you care
about efficiency and network traffic, don't use DAO. Using ADPs will
provide no benefits in your situation--rewriting your DAO as ADO will
be less work. Also, don't use any kind of recordset to update data
unless you are trying to slow your application down. Use UPDATE
statements instead.
--Mary
On Sat, 4 Feb 2006 10:50:11 -0800, Oliver <iron@.programmer.com> wrote:
[vbcol=seagreen]
>Thanks Mary, but is it possible to lock records on SQL server with DAO?
>If not I will have to convert my .mdb into a project as I think ADO is only
>possible if the Access client application is a project file (.adp extension).
>I do not like to do this because I then have about 850 Queries that do not
>work anymore! I would then need to convert all queries into stored procedures
>and views - is that correct or is there a way around it?
>Thanks.
>Oliver
>"Mary Chipman [MSFT]" wrote:
|||Hi Mary, thanks for the tips.
I just thought that it is too much work to convert all the DAO code and all
of the 600 queries that did not convert with the upsizing wizard. The views
are mostly not updateable after upsizing - it seems I will have to rewrite
the whole system and I think Microsoft should have left it to us programmers
to decide if we want to rewrite it all by just allowing record locking in DAO
ODBC links. I spent a whole day yesterday trying out if DAO allows record
locks but it does not (they could at least have mentioned this in the help
system).
After having tried this out I think you are right - there is not other way
than to convert all code into ADO in one go. You mentioned that I should use
UPDATEs instead of recordset updates - do you mean I should use ADO commands
executed from visual basic or should I write update procedures on the server
and call those stored procedures from the visual basic?
Thanks.
Oliver
"Mary Chipman [MSFT]" wrote:

> Locking records on SQL Server from any client is a BIG mistake. SQLS
> is very efficient at holding locks for the minimum amount of time
> required. Locking records on the client for long periods of time
> causes blocking and deadlocks (scenario--user runs code that locks
> records, goes to lunch, leaving records locked). Another process
> cannot even SEE the data if you are using the default READ COMMITTED
> isolation level (see SQL Books Online for more info).
> You should use other methods to control concurrency violations, such
> as designing table schema to partition tables so that users don't
> access the same record at the same time, using timestamps to detect
> concurrency problems, or creating a column in the table that
> increments each time a record is updated (you check this value in your
> code prior to updating and increment during the update). If you care
> about efficiency and network traffic, don't use DAO. Using ADPs will
> provide no benefits in your situation--rewriting your DAO as ADO will
> be less work. Also, don't use any kind of recordset to update data
> unless you are trying to slow your application down. Use UPDATE
> statements instead.
> --Mary
> On Sat, 4 Feb 2006 10:50:11 -0800, Oliver <iron@.programmer.com> wrote:
>
|||I think the reason you may have had trouble discovering how DAO works
with SQL Server in the help files is that there is an assumption that
you will use it only with Jet. It is not intended to work with SQL
Server, so nobody thought to document it. However, you can still use
DAO to execute pass-through queries, which are quite efficient. You
can use existing QueryDef objects and set the .SQL property in DAO
code to a SQL statement or to execute a stored procedure. Or you can
create dynamic pass-through queries that are not persisted in the mdb.
The syntax you use in the .SQL property is T-SQL, not Access SQL. The
reason they are called pass-through queries is that the SQL is not
parsed by Access--it is sent directly to the server. You can also use
ADO commands to execute SQL statements or parameterized stored
procedures. HTH,
--Mary
On Wed, 8 Feb 2006 01:13:27 -0800, Oliver <iron@.programmer.com> wrote:
[vbcol=seagreen]
>Hi Mary, thanks for the tips.
>I just thought that it is too much work to convert all the DAO code and all
>of the 600 queries that did not convert with the upsizing wizard. The views
>are mostly not updateable after upsizing - it seems I will have to rewrite
>the whole system and I think Microsoft should have left it to us programmers
>to decide if we want to rewrite it all by just allowing record locking in DAO
>ODBC links. I spent a whole day yesterday trying out if DAO allows record
>locks but it does not (they could at least have mentioned this in the help
>system).
>After having tried this out I think you are right - there is not other way
>than to convert all code into ADO in one go. You mentioned that I should use
>UPDATEs instead of recordset updates - do you mean I should use ADO commands
>executed from visual basic or should I write update procedures on the server
>and call those stored procedures from the visual basic?
>Thanks.
>Oliver
>"Mary Chipman [MSFT]" wrote:
|||Thanks Mary, in the meantime I found a good link to an old documentation
about the use of ODBCDirect,
http://msdn.microsoft.com/archive/de...l/web/001.asp.
This gives me even the option of pessimistic record locking (I need this
sometimes). I already tried to convert everything into ADO but this is an
endless job with the amount of code and queries I have (I gave up!). Now I
can program new queries as stored procedures and views on the server but
still keep the old queries in Access functional. If a query is too slow I
just convert it as needed. This is a much better way of migration into SQL
server.
Oliver
"Mary Chipman [MSFT]" wrote:

> I think the reason you may have had trouble discovering how DAO works
> with SQL Server in the help files is that there is an assumption that
> you will use it only with Jet. It is not intended to work with SQL
> Server, so nobody thought to document it. However, you can still use
> DAO to execute pass-through queries, which are quite efficient. You
> can use existing QueryDef objects and set the .SQL property in DAO
> code to a SQL statement or to execute a stored procedure. Or you can
> create dynamic pass-through queries that are not persisted in the mdb.
> The syntax you use in the .SQL property is T-SQL, not Access SQL. The
> reason they are called pass-through queries is that the SQL is not
> parsed by Access--it is sent directly to the server. You can also use
> ADO commands to execute SQL statements or parameterized stored
> procedures. HTH,
> --Mary
> On Wed, 8 Feb 2006 01:13:27 -0800, Oliver <iron@.programmer.com> wrote:
>

Is it possible to integrate with VSS?

Hi all,
Is there a way to integrate with Visual SourceSafe? Since i want to keep
track the changes of functions, sp, schema... etc.
Thanks
Martin
Hi
You may want to use SMO (SS2005),DMO(SS2000) objects library to keep track
the changes.
"Atenza" <Atenza@.mail.hongkong.com> wrote in message
news:eQnEm4GMGHA.2916@.tk2msftngp13.phx.gbl...
> Hi all,
> Is there a way to integrate with Visual SourceSafe? Since i want to keep
> track the changes of functions, sp, schema... etc.
> Thanks
> Martin
>
|||this means that i have to write my own program to integrate with VSS, rite?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:Okuj4FHMGHA.2828@.TK2MSFTNGP12.phx.gbl...
> Hi
> You may want to use SMO (SS2005),DMO(SS2000) objects library to keep track
> the changes.
>
>
> "Atenza" <Atenza@.mail.hongkong.com> wrote in message
> news:eQnEm4GMGHA.2916@.tk2msftngp13.phx.gbl...
>

Is it possible to integrate with VSS?

Hi all,
Is there a way to integrate with Visual SourceSafe? Since i want to keep
track the changes of functions, sp, schema... etc.
Thanks
MartinHi
You may want to use SMO (SS2005),DMO(SS2000) objects library to keep track
the changes.
"Atenza" <Atenza@.mail.hongkong.com> wrote in message
news:eQnEm4GMGHA.2916@.tk2msftngp13.phx.gbl...
> Hi all,
> Is there a way to integrate with Visual SourceSafe? Since i want to keep
> track the changes of functions, sp, schema... etc.
> Thanks
> Martin
>|||this means that i have to write my own program to integrate with VSS, rite?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:Okuj4FHMGHA.2828@.TK2MSFTNGP12.phx.gbl...
> Hi
> You may want to use SMO (SS2005),DMO(SS2000) objects library to keep track
> the changes.
>
>
> "Atenza" <Atenza@.mail.hongkong.com> wrote in message
> news:eQnEm4GMGHA.2916@.tk2msftngp13.phx.gbl...
>

Is it possible to integrate with VSS?

Hi all,
Is there a way to integrate with Visual SourceSafe? Since i want to keep
track the changes of functions, sp, schema... etc.
Thanks
MartinHi
You may want to use SMO (SS2005),DMO(SS2000) objects library to keep track
the changes.
"Atenza" <Atenza@.mail.hongkong.com> wrote in message
news:eQnEm4GMGHA.2916@.tk2msftngp13.phx.gbl...
> Hi all,
> Is there a way to integrate with Visual SourceSafe? Since i want to keep
> track the changes of functions, sp, schema... etc.
> Thanks
> Martin
>|||this means that i have to write my own program to integrate with VSS, rite?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:Okuj4FHMGHA.2828@.TK2MSFTNGP12.phx.gbl...
> Hi
> You may want to use SMO (SS2005),DMO(SS2000) objects library to keep track
> the changes.
>
>
> "Atenza" <Atenza@.mail.hongkong.com> wrote in message
> news:eQnEm4GMGHA.2916@.tk2msftngp13.phx.gbl...
>> Hi all,
>> Is there a way to integrate with Visual SourceSafe? Since i want to keep
>> track the changes of functions, sp, schema... etc.
>> Thanks
>> Martin
>sql

Monday, March 12, 2012

Is it possible that I change the data's type when I create the DSV in the Visual Studio 2005?

Hello everyone:

Recently, I was responsible for creating a analysis services project with the SQL Server Business Intelligence Development Studio in Visual studio 2005. I encountered a error that the data type is not supported when I tried to add the table from a remote Oracle server. I can not modify the data type in the oracle server. Is it possible that I change the data's type when I create the DSV in the Visual Studio 2005? (if I have known the table's name)

Help me Please! tell me the steps or some references if it is possible

Thanks

While I don't think you can change the data type of the column within the DSV, what you can do is add a named calculation to the table within the DSV and use a snippet of Oracle to cast the underlying column to whatever data type you want. The new named calculation would then inherit that data type.

For example (using SQL Server here, not Oracle), if I had a table with a column named ZIPCODE of type INT but I wanted to treat the column as VARCHAR within my SSAS solution, I could add a new named calculation to the table within the DSV (right-click on the table in the DSV and select New Named Calculation), call it something like ZIPCODE_STR and then use the T-SQL snippet CONVERT(VARCHAR(10), ZIPCODE). This new column then behaves like any other within the DSV and within the rest of my solution -- and has a datatype of System.String with a length of 10.

HTH,

Dave Fackler

|||

Thank you very much

Now I did not install SQL Server 2005 and VS on current computer, I will try it later. I think you suggest is very well. But I have a question: you said I could add a new name calculation to the table within the DSV. Does it equal that the table (at least one column's datatype doesn't not match the SSAS's rule) have been added into the DSV?

The problem I encounter is that I can not add that table to the DSV because of the unsuited the datatype for the column. Is there any other suggestion to me?

Thanks^_^

|||

Sorry, I missed the point that you can't add the table to the DSV because of the data type...

In that case, instead of adding a named calculation to the table (as you are correct in thinking that the table must be present within the DSV to do this), add a named query instead. Just right-click on the design surface of the DSV and select New Named Query.

This will bring up a dialog box that you can use to enter any PL/SQL select statement (given you are using Oracle) returning whatever columns (converted to whatever datatypes you need) from one or more tables. Very similar to creating a view in the Oracle database. Just give the named query a name and it will then appear as a table within the DSV. So, in your case, you'd likely want to select all the relevant columns that you need, converting the one with the unsupported datatype to another datatype (just be sure to alias the column once you convert it as a named query must return named columns so that the DSV can understand the metadata returned by the query).

HTH,

Dave Fackler

|||

I think this response is I need. Thank~ you very much.

I will try it later.

Thanks^_^

Winnie

Is it possible that I change the data's type when I create the DSV in the Visual Studio 200

Hello everyone:

Help me Please! tell me the steps or some references if it is possible

Thanks

HTH,

Dave Fackler

|||

Thank you very much

The problem I encounter is that I can not add that table to the DSV because of the unsuited the datatype for the column. Is there any other suggestion to me?

Thanks^_^

|||

Sorry, I missed the point that you can't add the table to the DSV because of the data type...

HTH,

Dave Fackler

|||

I think this response is I need. Thank~ you very much.

I will try it later.

Thanks^_^

Winnie

Is it Possible ?

Creating website in C# using Visual Studio 2005

Hi want to have something done, just to make sure if a such a thing can be done or not. I have a table in the Sql server 2005 called users. This contains all the users with usernames and passwords.

Now have another table called absent. In it there are 3 fields ID (autonumber),Username and Date.

Say if a user is absent. it will check the table user for their respected username, next it will go to another table called attendance in which consists of the username date, time in and timeout.

If the user has not signed in for today at 3pm in the afternoon then it should save the details in the absent table.

--------------

User table has user - Kevin

Now if kevin does notsign in the webform, after 3pm in the afternoon, the sql server must save the entry kevin with the date in the absent column.

The part in bold basically means to check if the user details are present for todays date in another table called Attendance for that user

Similarly if there are other users then same steps

--------------

I hope i clarified it

Thanks for your help

Sheldon

Create a job in SqlServer Agent, set up runtime at 3:00 PM every day. The job updates the tables based on your conditions.

|||Hi again thanks but you could post some more help or some links which deals with this|||

1). create a SP to carry out your task -- Update tables

2). In SqlServer Management-Jobs, create a job to execute this SP

Here is a reference that show you how to create a job.http://www.quackit.com/sql_server/tutorial/sql_server_agent_jobs.cfm

Google SqlServer Agent Jobs for help.

Wednesday, March 7, 2012

Is it a complex sql query?

Hello

I am using stored procedure with sql 2005 (with Visual studio 2005)

I have two tables .. TABLE1 And TABLE2

>From TABLE1 i need to retrive the OrderID's of the 4 most top rows. so
i did:
SELECT TOP 4 OrderID FROM TABLE1 order by OrderID desc

Now what i am trying to do is take the 4 row results (4 OrderID's) i
got from
TABLE1 and check if the 4 rows (4 OrderID's) exist in TABLE2 for a
specific
userID i get by INPUT varible (@.UserId)..

What i want to return is only which OrderID'S existed in TABLE2 for the

specific user.

If only 2 OrderID'S i retrived from TABLE1 exist in TABLE2 i will
return only 2 OrderID's (so i can do my output in visual studio 2005
using the reader())

I would appreciate this if anyone knows how to do this sql query , is
it possible to do this in 1 query? i want to put it in a stored
procedure.I tried to use this query-
SELECT TOP 4 OrderID FROM TABLE1 WHERE exists (SELECT * From TABLE2
where @.UserId=TABLE2.UserID)

But this query shows me the all 4 OrderID's if it finds the USERID in
TABLE2..

What i want to return is only which OrderID'S existes in TABLE2 for the
specific user.

if i have in TABLE1:
OrderID
1
2
3
4

TABLE2:
OrderID UserId
1 1001
2 1002

I want it to return only "2" if the INPUT Parameter of @.UserID is 1002|||Another example..

if i have in TABLE1:
OrderID
1
2
3
4

TABLE2:
OrderID UserId
1 1001
2 1002
3 1002

I want it to return only "2" and "3" if the INPUT Parameter of @.UserID
is 1002|||Hi

Check out http://www.aspfaq.com/etiquette.asp?id=5006 on how to post DDL and
sample data in a usable form

You can use something like:
SELECT TOP 4 t1.OrderID
FROM TABLE1 t1
WHERE exists (SELECT * FROM TABLE2 t2
WHERE @.UserId=T2.UserId
AND T1.OrderId = T2.OrderId )

Or (better!)

SELECT TOP 4 t1.OrderID
FROM TABLE1 t1
JOIN TABLE2 t2 ON T1.OrderId = T2.OrderId AND @.UserId=T2.UserId

Check out the topics "Using Joins" and "Join Fundamentals" in books online

John

<stockblaster@.gmail.com> wrote in message
news:1137312110.273304.240990@.g47g2000cwa.googlegr oups.com...
> Another example..
> if i have in TABLE1:
> OrderID
> 1
> 2
> 3
> 4
>
> TABLE2:
> OrderID UserId
> 1 1001
> 2 1002
> 3 1002
>
> I want it to return only "2" and "3" if the INPUT Parameter of @.UserID
> is 1002|||Excellent!

Thanks a lot John, it seems to work just fine.. i used the second
example.|||Hello again

Finally i decieded to use SELECT TOP 4 t1.OrderID
FROM TABLE1 t1
WHERE exists (SELECT * FROM TABLE2 t2
WHERE @.UserId=T2.UserId
AND T1.OrderId = T2.OrderId )

and modifed it to:
SELECT TOP 4 t1.OrderID
FROM TABLE1 t1
WHERE NOT exists (SELECT * FROM TABLE2 t2
WHERE @.UserId=T2.UserId
AND T1.OrderId = T2.OrderId )

(Notice the "NOT")
Because i wanted it to return me the OrderID's (from the top 4 of
course) that does not exist in TABLE2 ..

I couldn't do it with the JOIN thingy even if i changed OrderId <>
T2.OrderId ..|||I tried to find this in the documents on the web ..I couldn't find a
way of how to perform this only for the TOP 4 of TABLE1.

Now what happenes:
stockblas...@.gmail.com
Jan 15, 10:01 am show options

Another example..

TABLE1:
OrderID
1
2
3
4
5
6
7
8
9
10
TABLE2:
OrderID UserId
1 1001
2 1002
3 1002

It will return me: 4 5 6 7 (the top 4 of what it finds)
i use now:
SELECT TOP 4 t1.OrderID
FROM TABLE1 t1
WHERE NOT exists (SELECT * FROM TABLE2 t2
WHERE @.UserId=T2.UserId
AND T1.OrderId = T2.OrderId )

Any ideas?|||Hi

I should have said that TOP without and ORDER BY clause is a bit
meaningless.

SELECT TOP 4 t1.OrderID
FROM TABLE1 t1
WHERE NOT EXISTS (SELECT * FROM TABLE2 t2
WHERE @.UserId=T2.UserId
AND T1.OrderId = T2.OrderId )
ORDER BY t1.OrderID

Will return you all rows OrderIds from Table1 where a row in Table2 does not
exist for that OrderId AND has a UserId of @.UserId. With the ORDER BY means
1, 4, 5 and 6 are returned.

To do this using a JOIN, an OUTER JOIN is required.

SELECT TOP 4 t1.OrderID
FROM TABLE1 t1
LEFT JOIN TABLE2 t2 ON t1.OrderId = t2.OrderId AND @.UserId = t2.UserId
WHERE t2.OrderID IS NULL
ORDER BY t1.OrderID

John

<stockblaster@.gmail.com> wrote in message
news:1137325768.003889.45140@.g47g2000cwa.googlegro ups.com...
> Hello again
> Finally i decieded to use SELECT TOP 4 t1.OrderID
> FROM TABLE1 t1
> WHERE exists (SELECT * FROM TABLE2 t2
> WHERE @.UserId=T2.UserId
> AND T1.OrderId = T2.OrderId )

> and modifed it to:
> SELECT TOP 4 t1.OrderID
> FROM TABLE1 t1
> WHERE NOT exists (SELECT * FROM TABLE2 t2
> WHERE @.UserId=T2.UserId
> AND T1.OrderId = T2.OrderId )

> (Notice the "NOT")
> Because i wanted it to return me the OrderID's (from the top 4 of
> course) that does not exist in TABLE2 ..
> I couldn't do it with the JOIN thingy even if i changed OrderId <>
> T2.OrderId ..|||Hello

I am sorry, i didn't explain my self what i wanted to acchive exactly.
For this query:
SELECT TOP 4 t1.OrderID
FROM TABLE1 t1
LEFT JOIN TABLE2 t2 ON t1.OrderId = t2.OrderId AND @.UserId = t2.UserId
WHERE t2.OrderID IS NULL
ORDER BY t1.OrderID DESC

There is a problem with that..
For example:
Table1:
OrderID
1
2
3
4
5
6
7
8
Table2:
OrderID UserID
6 1001
7 1001
3 1002
4 1002
the result will be:
for user 1001
8
5
4
3

I only need to get 8 and 5 which are the two orderID's the user didn't
have from the top 4 in table 1 ..

can't figure that out :(|||On 15 Jan 2006 10:09:18 -0800, stockblaster@.gmail.com wrote:

>Hello
>I am sorry, i didn't explain my self what i wanted to acchive exactly.
(snip)

Hi Stockblaster,

That's exactly the reason why John suggested you to read the information
at www.aspfaq.com/5006 in his first post to you - posting CREATE TABLE
and INSERT statements and expected output is a much better way to
explain your needs than pure narrative.

If I understand your requirements correctly, then maybe something like
this will work:

SELECT t1.OrderId
FROM (SELECT TOP 4 OrderID
FROM Table1
ORDER BY OrderID DESC) AS t1
LEFT JOIN Table2 AS t2
ON t2.OrderId = t1.OrderId
AND t2.UserId = @.UserId
WHERE t2.OrderID IS NULL

(untested - see www.aspfafq.com/5006 if you prefer a tested reply)

--
Hugo Kornelis, SQL Server MVP|||Hello Hugo..

Very nice! i believe this 1 did the work..

Thanks a lot.. this 1 was stiff.

Is there any good book you can recommend me for sql 2005 (with SQL
Server Management Studio) ... How to upload to a shared web hosting,
when to use relationships, some sql querys examples? all the basics.|||Hi

I am not sure if my interpretation is the same as Hugos!
If all 4 rows returned are below the maximum what should happen?

SELECT TOP 4 t1a.OrderID
FROM TABLE1 t1a
LEFT JOIN TABLE2 t2a ON t1.OrderId = t2a.OrderId AND @.UserId =
t2a.UserId
WHERE t2.OrderID IS NULL
AND t1a.OrderID > ( SELECT MAX(t1b.OrderID) FROM TABLE1 t1b
JOIN TABLE2 t2b ON t1b.OrderId = t2b.OrderId AND @.UserId = t2b.UserId )

ORDER BY t1a.OrderID DESC

John

stockblaster@.gmail.com wrote:
> Hello
> I am sorry, i didn't explain my self what i wanted to acchive exactly.
> For this query:
> SELECT TOP 4 t1.OrderID
> FROM TABLE1 t1
> LEFT JOIN TABLE2 t2 ON t1.OrderId = t2.OrderId AND @.UserId = t2.UserId
> WHERE t2.OrderID IS NULL
> ORDER BY t1.OrderID DESC
>
> There is a problem with that..
> For example:
> Table1:
> OrderID
> 1
> 2
> 3
> 4
> 5
> 6
> 7
> 8
> Table2:
> OrderID UserID
> 6 1001
> 7 1001
> 3 1002
> 4 1002
> the result will be:
> for user 1001
> 8
> 5
> 4
> 3
>
> I only need to get 8 and 5 which are the two orderID's the user didn't
> have from the top 4 in table 1 ..
> can't figure that out :(|||Hi

I don't think you will get a single books to cover all these topics,
and you will have to be careful of books based on the pre-release
versions. You may want to check out THe Microsoft SQL Server 2005
Administrator's Pocket Consultant ISDN 0735621071 for configuration
information, and there is always books online. Also check out SQL
Server magazine which has many articles that will be benificial
http://www.windowsitpro.com/SQLServer/

John|||On 15 Jan 2006 15:27:13 -0800, stockblaster@.gmail.com wrote:

>Hello Hugo..
>Very nice! i believe this 1 did the work..
>Thanks a lot.. this 1 was stiff.
>Is there any good book you can recommend me for sql 2005 (with SQL
>Server Management Studio) ... How to upload to a shared web hosting,
>when to use relationships, some sql querys examples? all the basics.

Hi Stockblaster,

I'm sorry, I can't help you here.

Personally, I'm going to wait for Inside SQL Server 2005, that Kalen
Delaney is (hopefully) working on right now. However, the "Inside..."
series are "how does it work" kind of books; you seem to be seeking the
"how do I operate it" kind of books.

--
Hugo Kornelis, SQL Server MVP|||Hello John.

I am not sure i understand, do you mean if table1 contains only two
records? so the top 4 will not work?|||Hi

Sorry for the delayed reply, this one slipped through the net.

My question was related to

There is a problem with that..
For example:
Table1:
OrderID
1
2
3
4
5
6
7
8
Table2:
OrderID UserID
6 1001
7 1001
3 1002
4 1002
the result will be:
for user 1001
8
5
4
3

Do you actually want 3,4,5 as this is less than the maximum for 1001 which
is already 7?

John
<stockblaster@.gmail.com> wrote in message
news:1137449155.173528.136550@.g14g2000cwa.googlegr oups.com...
> Hello John.
> I am not sure i understand, do you mean if table1 contains only two
> records? so the top 4 will not work?

Is Installing SQL05dev and VS05pro on the same machine supported?

Hello,

When SQL05dev is installed a SQL05 version of Visual Studio (SQL05-VS) is also installed (called the “SQL Server Business Intelligence Development Studio”). The SQL05-VS installs files into a “C:\Program Files\Microsoft SQL Server\90\Tools\binn\VSShell\Common7\IDE” directory (and other directories - not described here).

When the full version VS05pro is installed it installs/uses files in a “C:\Program Files\Microsoft Visual Studio 8\Common7\IDE” directory.

The number of files in these two directories is different – the directory for VS05pro has (of course) many many more files than the directory for SQL05-VS. On the surface this seems ok since the install of VS05pro should supersede the install of the SQL05-VS and when VS05 is started it utilizes the files from the VS05pro directory and picks up the functions from the SQL05-VS (like SSIS, SSAS, SSRS and such).

But what about the SQL05-VS Common7/IDE directory? In this directory are 64 files that exist in both directores (556 files are not found in the VS05pro Common7/IDE directory). Additionally, there are 10 files that have different versions (mostly having to do with SQL-CE) – newer versions are in the SQL05-VS directory.

I’m confused. Is Microsoft going to support revision changes (Service Packs and such) to both directories? I suppose so, but this introduces possible versioning problems when SQL is updated but VS is not or VS is updated but SQL is not.

I guess I do not understand the rational for not maintaining a single VS directory structure and the problems that this creates. Microsoft is utilizing VS as the IDE for SQL but to not have a really good matching installation layout with the full version of VS is wacky – to say the least. Maybe M$ considers maintaing the versions of 64 files to be unimportant for the correct functioning of VS?

Is Installing SQL05dev and VS05pro on the same machine supported?

MikeC

Installing VS 2005 (and flavor) and SQL 2005 on the same machine is supported.

SQL installs what we call the Premier Partner Edition, which is just the basic IDE with no langauges. SQL then installs its own packages on top of the IDE.

The additional files are SQL specific. The Premier Partner Edition and any VS flavor will install to one location. If you install SQL first, when installing VS, VS will "lock" you to install to a specific directory and won't allow you to install anywhere you want.

That way, you only have one version of the file, its serviced in one place, and is properly ref counted.

|||

QuanT,

You said, "Installing VS 2005 (and flavor) and SQL 2005 on the same machine is supported.”

If this is the case, then I would like to see Microsoft fix the problems with SQL05 when VS and SQL05 are installed on the same machine – *but not to the C: volume*. That is - installing full VS05 (without SQL Express) and SQL05 to a non-C: volume on a freshly formatted/installed version of Windows 2003R2.

You said “If you install SQL first, when installing VS, VS will "lock" you to install to a specific directory and won't allow you to install anywhere you want."

It does not matter which you install first, installing VS to a non-C: volume after SQL is installed to a non-C: volume broke SSIS and the copy db wizard (I did not do any checking beyond these show-stoppers…)

Installing VS first (to a non-C: volume) allows the SQL install to install more components to a non-C: volume, but the problems still arise. Installing in this order should also be supported.

Installing VS to C: and SQL to E: works, but then RS has issues...

It appears that very little SQL05 installation testing was done to anything other than a C: volume - when VS05 is also installed on the same machine - as would be the case on development boxes. On a production db server it is unlikly that I would install the full VS, but who knows, it might be necessary, and should be possible without worry that SQL05 will be broken.

MikeC

Error from create SSIS package (save to server):
===================================

No description found (Microsoft Visual Studio)
Program Location: at Microsoft.SqlServer.Dts.Runtime.Application.SaveToSqlServerAs(Package package, IDTSEvents events, String packagePath, String serverName, String serverUserName, String serverPassword) at Microsoft.DataTransformationServices.Controls.PackageLocationControl.SavePackage(Package package) at Microsoft.DataTransformationServices.Design.Controls.PackageSaveCopyForm.PackageSaveCopyForm_FormClosing(Object sender, FormClosingEventArgs e)

===================================

Error from copy db wizard:
===================================

No description found (Copy Database Wizard)
Program Location: at Microsoft.SqlServer.Dts.Runtime.Application.SaveToSqlServerAs(Package package, IDTSEvents events, String packagePath, String serverName, String
serverUserName, String serverPassword) at Microsoft.SqlServer.Management.CopyDatabaseWizard.PackageCreator.SavePackage()

===================================

|||

Thanks for the feedback Mike.

I'm moving this to the SQL Setup alias to get comments from the SQL Team.

|||

Mike,

is your issue resolved? I would like to know what the resolution was if it has been resolved. Iam encountering the same issue now.

My setup is:

1. Installed SQL2000 (default instance)

2. Installed SQL2005 (named instance)

All on C:

I was able to save SSIS packages to the DB until recently using "Save Copy of <pkg> As".

Now I'm encountering the above error (No decription found etc..). I don't even remember anymore if I specified the <machine name> OR <machine name>\<Instance Name> for the "Server" when I had it working.

But I tried with both and I get different errors:

<machine name>\<Instance Name> : No decription found etc.

<machine name> :

======================
The ExistsOnSQLServer method has encountered OLE DB error code 0x80004005 (Client unable to establish connection). The SQL statement issued has failed.
(Microsoft Visual Studio)

=======================

For "Protection Level", I have tried different options, but it doesn't seem to matter.

Another thing is that I suspect this may have started happening after I was trying to install VS05 (other components) on top of the default version that was installed along with SQL05. I didn't go ahead with the VS install and canceled it half way through the initial install setup.

Any inputs are appreciated.

Thanks.

|||

Sql_Rv,

I have an open support call with M$ for the last week or so about the "can't save package" problem. This problem with saving packages absolutely has something to do with installing VS05 on the same machine that has SQL05 installed. Works without VS05, fails with VS05 installed.

To fix your installation (without a complete wipe of the house-of-cards that is Windows ), I suggest installing VS05 all the way! Then run the VS05 uninstall. This is the only way to get the VS05 install/uninstall program to run and remove the all the bits of VS05 that M$ stashes all over the place - especially the registry. I would hope that an aborted VS05 install would back-out any changes, but given the somewhat buggy state of Win03r2, MMCv3, VS05 and SQL05 tools… Really, I don’t think M$ has it altogether any more, the testers likely do not have initiative to try anything slightly out of line…

Lets see - ALL third party and M$ products like ISA04 that use MMC cause the new MMC v3 to crash on WinR2 - really - what kind of testing is M$ doing? I suppose we are doing the testing for them...

It would be nice if M$ (and other M$ app mfg) would wake up and see the simplicity of installing all executables of an application in ONE directory tree, and have a minimal registry footprint. A manifest of files would be nice also - but no, we get this horrific splat of install logs that lists what files were installed - and the number of registry entries that get added by SQL05 is obscene. Really, it is too much to ask for list of files and their versions needed to maintain a SQL05 installation? Tracking revisions must be a fuzzy concept at M$...

Anyway - M$ support is slow on the testing and the wonderfully informative error message "no description found" goes a long way in pointing to the problem - not.

Question: What version of Windows are you running? Is it a domain controller? Are SQL services running with a domain account?

MikeC

|||

Mike,

Thanks for taking the time to respond. I appreciate it.

This is on my dev box which is running XP Pro. No, it is not a domain controller. Yes, the services are running with a domain account that has admin privileges on this box.

btw, to get around this problem, I tried saving it from a different machine and it worked. This is what I did:

box1\2K5 - Unable to save the pkg to this DB from box1.

box2 - Has a 2K5 instance too. I used this to save it to box1\2K5 by doing a save as and specifying box1\2K5 as the SQLServer name - and it saved successfully.

Thanks,

|||

I have the same problem.

MS WinXP Pro sp2 (latest patches as of 9.26.2006)

SQL Server 2005

VS2005

error:TITLE: SQL Server Import and Export Wizard The operation could not be completed. ADDITIONAL INFORMATION: The ExistsOnSQLServer method has encountered OLE DB error code 0x80004005 (Client unable to establish connection). The SQL statement issued has failed. BUTTONS: OK

The error is generated when I try to save the SISS package to the SQL server instance mentioned above.

I was able to save the SISS package using the filesystem option.

Is Installing SQL05dev and VS05pro on the same machine supported?

Hello,

When the full version VS05pro is installed it installs/uses files in a “C:\Program Files\Microsoft Visual Studio 8\Common7\IDE” directory.

Is Installing SQL05dev and VS05pro on the same machine supported?

MikeC

Installing VS 2005 (and flavor) and SQL 2005 on the same machine is supported.

SQL installs what we call the Premier Partner Edition, which is just the basic IDE with no langauges. SQL then installs its own packages on top of the IDE.

That way, you only have one version of the file, its serviced in one place, and is properly ref counted.

|||

QuanT,

You said, "Installing VS 2005 (and flavor) and SQL 2005 on the same machine is supported.”

You said “If you install SQL first, when installing VS, VS will "lock" you to install to a specific directory and won't allow you to install anywhere you want."

Installing VS first (to a non-C: volume) allows the SQL install to install more components to a non-C: volume, but the problems still arise. Installing in this order should also be supported.

Installing VS to C: and SQL to E: works, but then RS has issues...

MikeC

Error from create SSIS package (save to server):
===================================

===================================

Error from copy db wizard:
===================================

===================================

|||

Thanks for the feedback Mike.

I'm moving this to the SQL Setup alias to get comments from the SQL Team.

|||

Mike,

is your issue resolved? I would like to know what the resolution was if it has been resolved. Iam encountering the same issue now.

My setup is:

1. Installed SQL2000 (default instance)

2. Installed SQL2005 (named instance)

All on C:

I was able to save SSIS packages to the DB until recently using "Save Copy of <pkg> As".

But I tried with both and I get different errors:

<machine name>\<Instance Name> : No decription found etc.

<machine name> :

=======================

For "Protection Level", I have tried different options, but it doesn't seem to matter.

Any inputs are appreciated.

Thanks.

|||

Sql_Rv,

Lets see - ALL third party and M$ products like ISA04 that use MMC cause the new MMC v3 to crash on WinR2 - really - what kind of testing is M$ doing? I suppose we are doing the testing for them...

Anyway - M$ support is slow on the testing and the wonderfully informative error message "no description found" goes a long way in pointing to the problem - not.

Question: What version of Windows are you running? Is it a domain controller? Are SQL services running with a domain account?

MikeC

|||

Mike,

Thanks for taking the time to respond. I appreciate it.

This is on my dev box which is running XP Pro. No, it is not a domain controller. Yes, the services are running with a domain account that has admin privileges on this box.

btw, to get around this problem, I tried saving it from a different machine and it worked. This is what I did:

box1\2K5 - Unable to save the pkg to this DB from box1.

box2 - Has a 2K5 instance too. I used this to save it to box1\2K5 by doing a save as and specifying box1\2K5 as the SQLServer name - and it saved successfully.

Thanks,

|||

I have the same problem.

MS WinXP Pro sp2 (latest patches as of 9.26.2006)

SQL Server 2005

VS2005

The error is generated when I try to save the SISS package to the SQL server instance mentioned above.

I was able to save the SISS package using the filesystem option.

Is Installing SQL05dev and VS05pro on the same machine supported?

Hello,

When the full version VS05pro is installed it installs/uses files in a “C:\Program Files\Microsoft Visual Studio 8\Common7\IDE” directory.

Is Installing SQL05dev and VS05pro on the same machine supported?

MikeC

Installing VS 2005 (and flavor) and SQL 2005 on the same machine is supported.

SQL installs what we call the Premier Partner Edition, which is just the basic IDE with no langauges. SQL then installs its own packages on top of the IDE.

That way, you only have one version of the file, its serviced in one place, and is properly ref counted.

|||

QuanT,

You said, "Installing VS 2005 (and flavor) and SQL 2005 on the same machine is supported.”

You said “If you install SQL first, when installing VS, VS will "lock" you to install to a specific directory and won't allow you to install anywhere you want."

Installing VS first (to a non-C: volume) allows the SQL install to install more components to a non-C: volume, but the problems still arise. Installing in this order should also be supported.

Installing VS to C: and SQL to E: works, but then RS has issues...

MikeC

Error from create SSIS package (save to server):
===================================

===================================

Error from copy db wizard:
===================================

===================================

|||

Thanks for the feedback Mike.

I'm moving this to the SQL Setup alias to get comments from the SQL Team.

|||

Mike,

is your issue resolved? I would like to know what the resolution was if it has been resolved. Iam encountering the same issue now.

My setup is:

1. Installed SQL2000 (default instance)

2. Installed SQL2005 (named instance)

All on C:

I was able to save SSIS packages to the DB until recently using "Save Copy of <pkg> As".

But I tried with both and I get different errors:

<machine name>\<Instance Name> : No decription found etc.

<machine name> :

=======================

For "Protection Level", I have tried different options, but it doesn't seem to matter.

Any inputs are appreciated.

Thanks.

|||

Sql_Rv,

Lets see - ALL third party and M$ products like ISA04 that use MMC cause the new MMC v3 to crash on WinR2 - really - what kind of testing is M$ doing? I suppose we are doing the testing for them...

Anyway - M$ support is slow on the testing and the wonderfully informative error message "no description found" goes a long way in pointing to the problem - not.

Question: What version of Windows are you running? Is it a domain controller? Are SQL services running with a domain account?

MikeC

|||

Mike,

Thanks for taking the time to respond. I appreciate it.

This is on my dev box which is running XP Pro. No, it is not a domain controller. Yes, the services are running with a domain account that has admin privileges on this box.

btw, to get around this problem, I tried saving it from a different machine and it worked. This is what I did:

box1\2K5 - Unable to save the pkg to this DB from box1.

box2 - Has a 2K5 instance too. I used this to save it to box1\2K5 by doing a save as and specifying box1\2K5 as the SQLServer name - and it saved successfully.

Thanks,

|||

I have the same problem.

MS WinXP Pro sp2 (latest patches as of 9.26.2006)

SQL Server 2005

VS2005

The error is generated when I try to save the SISS package to the SQL server instance mentioned above.

I was able to save the SISS package using the filesystem option.

Wednesday, March 28, 2012

Is it possible to link via ADODB from an Access 2K .mdb file?

Is it possible to integrate with VSS?

Is it possible to integrate with VSS?

Is it possible to integrate with VSS?

Monday, March 12, 2012

Is it possible that I change the data's type when I create the DSV in the Visual Studio 2005?

Is it possible that I change the data's type when I create the DSV in the Visual Studio 200

Is it Possible ?

Wednesday, March 7, 2012

Is it a complex sql query?

Is Installing SQL05dev and VS05pro on the same machine supported?

Is Installing SQL05dev and VS05pro on the same machine supported?

Is Installing SQL05dev and VS05pro on the same machine supported?

AUTOGROW info recorded

Blog Archive

About Me