Multiuser Databinding in SQL Server 2000?

Hi

I am developing one multi user application in windows environment using VC++ .NET with SQL Server 2000. How Can I create dataset for multi user data updating in my SQL Server 2000. Where I can get help and sample code?

Thanks

Jose

Jose,

The DataSet is an off-line data cache. The DataAdapter is used to submit the pending changes stored in the DataSet. In the DataAdapter's updating logic, you can specify your own concurrency options to control whether you want to use a "last in wins" approach with only the primary key column(s) in the WHERE clause, or use other columns in the WHERE clause to ensure you don't overwrite changes made by another user.

I hope this information proves helpful.

David Sceppa
ADO.NET Program Manager
Microsoft|||Hi

Thankyou for the nice explanation. One more question how can I write custom primary key value. for example (50710000001) like this. 5 is for year, 07 is for month, 10 is for place, and remaining is unique automatic generating number. How can I write program in SQL Server 2000.

Regards,
Jose

Multi-statement Table-valued Functions

Hello

I am trying to do the following:

1. Create a Multi-statement Table-valued Functions, say mstvF1, with 1 parameter.
2. Then, use it like this: "Select * from table T, mstvF1( T.id )"

It gives me Line 100: Incorrect syntax near 'T', referring to the T of T.id.

If I do
Select * from table T, mstvF1( 5 ), then it works.

Is there any way to do a select from a table T combined with an MSTV function and passing in as a parameter a field from T?

Thanks for any help.The function can only take in 1 parameter...

The table you're referencing is many rows...

What does the function do?

Can you post that code?

I don't think you can do it that way...

What about

Select *, mstvF1( T.id ) from table T

??|||this will not work either. a table-valued function cannot be used with joined select having one of the fields to be used as a parameter. it does not matter how many parameters, as long as they are fed from the participant of the select.|||Why don't you post some sample ddl and data, then show us what you are trying to accomplish. It sounds like you're confused on what different types of functions do. If you think about what you're trying to do there, it really doesn't make sense as it would make a table for every single T.id which would be a big mess.

Multiselect list box width and other things

When I create a multivalue report parameter I have noticed that the multi-select list box that is created is never really wide enough for the data that I am listing in the box. Is there a way to control the width and possibly the height, of this list box either staticly or dynamicly?

Another problem that I am having is with the way a report displays from within the web browser. It seems that when a report will need to scroll to the right that it gets either cut off or there isn't a scroll bar on the first page of the report. If there is more than one page to the report I can go to the second page and then come back to the first page and the scroll bar appears.

Are there any fixes or workarounds for these problems? Or have I just not read far enough into the documentation?

Wayne E. Pfeffer

I'm having the same problem with the width of the multivalue report parameter list box. Does anyone know of a way to increase the width?

Many thanks.

Chris

|||

i have not tried this but it may assist.

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

|||

After digging through SSRS HTML and JavaScript source code I have found the following:

A textbox

Multiselect list box width and other things

When I create a multivalue report parameter I have noticed that the multi-select list box that is created is never really wide enough for the data that I am listing in the box. Is there a way to control the width and possibly the height, of this list box either staticly or dynamicly?

Another problem that I am having is with the way a report displays from within the web browser. It seems that when a report will need to scroll to the right that it gets either cut off or there isn't a scroll bar on the first page of the report. If there is more than one page to the report I can go to the second page and then come back to the first page and the scroll bar appears.

Are there any fixes or workarounds for these problems? Or have I just not read far enough into the documentation?

Wayne E. Pfeffer

I'm having the same problem with the width of the multivalue report parameter list box. Does anyone know of a way to increase the width?

Many thanks.

Chris

|||

i have not tried this but it may assist.

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

|||

After digging through SSRS HTML and JavaScript source code I have found the following:

A textbox

Multiselect list box width and other things

When I create a multivalue report parameter I have noticed that the multi-select list box that is created is never really wide enough for the data that I am listing in the box. Is there a way to control the width and possibly the height, of this list box either staticly or dynamicly?

Another problem that I am having is with the way a report displays from within the web browser. It seems that when a report will need to scroll to the right that it gets either cut off or there isn't a scroll bar on the first page of the report. If there is more than one page to the report I can go to the second page and then come back to the first page and the scroll bar appears.

Are there any fixes or workarounds for these problems? Or have I just not read far enough into the documentation?

Wayne E. Pfeffer

I'm having the same problem with the width of the multivalue report parameter list box. Does anyone know of a way to increase the width?

Many thanks.

Chris

|||

i have not tried this but it may assist.

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

|||

After digging through SSRS HTML and JavaScript source code I have found the following:

A textbox

multiquery in a store procedure...

Hi, here is the problem...

I must create a store procedure that do the following...

1. select id_name,...'some other field' from table 'data' where 'criteria' =
@.parameter

2. use the result of the first selection to create another selection
combining that results with the table 'name' where id_name is null in the
result selection...

In other word I have a first query that use a passed parameter, the result
of that query should be combined this another query, and the result should
be return from the store procedure...

I can't realize how to write this 'simple' problem... can anyone help me?

Many thanks,
AlexAlessandro (giumalex@.tiscali.it) writes:
> I must create a store procedure that do the following...
> 1. select id_name,...'some other field' from table 'data' where
> 'criteria' = @.parameter
> 2. use the result of the first selection to create another selection
> combining that results with the table 'name' where id_name is null in the
> result selection...
> In other word I have a first query that use a passed parameter, the result
> of that query should be combined this another query, and the result should
> be return from the store procedure...

A simple solution is to use temp tables or table variables.

However, of you often can do this with a derived table, which conceptually
can be seen as a temp table, but physically it is never materialized.

Here is an example:

SELECT C.*
FROM Northwind..Customers C
JOIN (SELECT CustomerID
FROM Northwind..Orders
GROUP BY CustomerID
HAVING COUNT(*) > 20) O ON C.CustomerID = O.CustomerID

Lists all customer informations about customers that have placed more than
20 orders.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Alessandro" <giumalex@.tiscali.it> wrote in message news:<g8tkc.30640$eR5.16238@.tornado.fastwebnet.it>...
> Hi, here is the problem...
>...
> I can't realize how to write this 'simple' problem... can anyone help me?
> Many thanks,
> Alex

Hi Alex.. to work with SQL , query and paramter look at this site :

Http://www.RealTimeInformatica.it/Stany

Bye Bye ...

ps: anche in Italiano !;)

multiquery in a store procedure...

Hi, here is the problem...

I must create a store procedure that do the following...

1. select id_name,...'some other field' from table 'data' where 'criteria' =
@.parameter

2. use the result of the first selection to create another selection
combining that results with the table 'name' where id_name is null in the
result selection...

In other word I have a first query that use a passed parameter, the result
of that query should be combined this another query, and the result should
be return from the store procedure...

I can't realize how to write this 'simple' problem... can anyone help me?

Many thanks,
AlexAlessandro (giumalex@.tiscali.it) writes:
> I must create a store procedure that do the following...
> 1. select id_name,...'some other field' from table 'data' where
> 'criteria' = @.parameter
> 2. use the result of the first selection to create another selection
> combining that results with the table 'name' where id_name is null in the
> result selection...
> In other word I have a first query that use a passed parameter, the result
> of that query should be combined this another query, and the result should
> be return from the store procedure...

A simple solution is to use temp tables or table variables.

However, of you often can do this with a derived table, which conceptually
can be seen as a temp table, but physically it is never materialized.

Here is an example:

SELECT C.*
FROM Northwind..Customers C
JOIN (SELECT CustomerID
FROM Northwind..Orders
GROUP BY CustomerID
HAVING COUNT(*) > 20) O ON C.CustomerID = O.CustomerID

Lists all customer informations about customers that have placed more than
20 orders.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql

Multipls cursors help

Hello,
I am trying to create multiple cursors and update 2 separate tables. But the
data tables seem to be locking after I run them. Is my logic off? My code is
below -
set xact_abort on
declare @.user_id int, @.topic_name varchar(20), @.key_seq_num int
declare GetUserID cursor for
select [user_id] from users where [user_id] not in (select [user_id] from
userdata)
open GetUserID
fetch next from GetUserID into @.user_id
while @.@.fetch_status = 0
begin
begin tran
--new cursor
declare GetUserData cursor for
select topic_name from userdata where [user_id] = 327
open GetUserData
fetch next from GetUserData into @.topic_name
while @.@.fetch_status = 0
begin
begin tran
--new cursor
declare GetNextID cursor for
select key_seq_num from nextkey where key_name = 'userdata_user_data_id'
open GetNextID
fetch next from GetNextID into @.key_seq_num
while @.@.fetch_status = 0
begin
begin tran
insert into UserData(user_data_id, [user_id], topic_name, update_date,
update_user, create_date, create_user)
select @.key_seq_num, @.user_id, @.topic_name, getdate(), 'Default',
getdate(), 'Default'
update nextkey set key_seq_num = @.key_seq_num + 1
from nextkey where key_name = 'userdata_user_data_id'
commit tran
print @.key_seq_num
fetch next from GetNextID into @.key_seq_num
end
close GetNextID
deallocate GetNextID
fetch next from GetUserData into @.topic_name
end
close GetUserData
deallocate GetUserData
fetch next from GetUserID into @.user_id
end
close GetUserID
deallocate GetUserIDAnil,
Can you explain what are you trying to accomplish?
AMB
"Anil" wrote:

> Hello,
> I am trying to create multiple cursors and update 2 separate tables. But t
he
> data tables seem to be locking after I run them. Is my logic off? My code
is
> below -
> set xact_abort on
> declare @.user_id int, @.topic_name varchar(20), @.key_seq_num int
> declare GetUserID cursor for
> select [user_id] from users where [user_id] not in (select [user_id] from
> userdata)
> open GetUserID
> fetch next from GetUserID into @.user_id
> while @.@.fetch_status = 0
> begin
> begin tran
> --new cursor
> declare GetUserData cursor for
> select topic_name from userdata where [user_id] = 327
> open GetUserData
> fetch next from GetUserData into @.topic_name
> while @.@.fetch_status = 0
> begin
> begin tran
> --new cursor
> declare GetNextID cursor for
> select key_seq_num from nextkey where key_name = 'userdata_user_data_id'
> open GetNextID
> fetch next from GetNextID into @.key_seq_num
> while @.@.fetch_status = 0
> begin
> begin tran
> insert into UserData(user_data_id, [user_id], topic_name, update_date,
> update_user, create_date, create_user)
> select @.key_seq_num, @.user_id, @.topic_name, getdate(), 'Default',
> getdate(), 'Default'
> update nextkey set key_seq_num = @.key_seq_num + 1
> from nextkey where key_name = 'userdata_user_data_id'
> commit tran
> print @.key_seq_num
> fetch next from GetNextID into @.key_seq_num
> end
> close GetNextID
> deallocate GetNextID
> fetch next from GetUserData into @.topic_name
> end
> close GetUserData
> deallocate GetUserData
> fetch next from GetUserID into @.user_id
> end
> close GetUserID
> deallocate GetUserID|||I am trying to take all the user id's from cursor 1, some value from cursor
2
and 3 and update them into a table. Cursor 3 has an id that needs to be
incremented each time as it is not an auto number. The way I have laid out m
y
cursors, is that the correct way?
Thanks.
Anil
"Alejandro Mesa" wrote:
> Anil,
> Can you explain what are you trying to accomplish?
>
> AMB
>
> "Anil" wrote:
>|||Anil,
Can you explain it without referencing the cursors?
AMB
"Anil" wrote:
> I am trying to take all the user id's from cursor 1, some value from curso
r 2
> and 3 and update them into a table. Cursor 3 has an id that needs to be
> incremented each time as it is not an auto number. The way I have laid out
my
> cursors, is that the correct way?
> Thanks.
> Anil
> "Alejandro Mesa" wrote:
>|||Please provide DDL and sample data.
http://www.aspfaq.com/etiquette.asp?id=5006
AMB
"Alejandro Mesa" wrote:
> Anil,
> Can you explain it without referencing the cursors?
>
> AMB
>
> "Anil" wrote:
>|||Is the syntax correct for multiple cursors? Is it possible to do what I am
trying to do with cursors?
"Alejandro Mesa" wrote:
> Anil,
> Can you explain what are you trying to accomplish?
>
> AMB
>
> "Anil" wrote:
>|||Is the syntax correct? Is it possible to do what I am trying to do?
"Alejandro Mesa" wrote:
> Anil,
> Can you explain what are you trying to accomplish?
>
> AMB
>
> "Anil" wrote:
>|||Hi Anil,
Looks like the tables which you are accessing is getting locked since you do
all your stuff within a transaction.
You can do 2 things to see if the issue is happening because of a lock
1. Remove all transaction (begin/commit tran) and see if your code actually
works.
2. If your code works after step 1 then use WITH (NOLOCK) clause in your
select statements.
Regards,
Joe.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:47D3D1AF-4E14-4C0B-80B9-66ADCCF91254@.microsoft.com...
> Please provide DDL and sample data.
> http://www.aspfaq.com/etiquette.asp?id=5006
>
> AMB
> "Alejandro Mesa" wrote:
>
cursor 2
be
out my
tables. But the
My code is
[user_id] from
'userdata_user_data_id'
update_date,|||Hey Joe,
Thanks for the help. Worked like a charm!!
Anil
"Joe" wrote:

> Hi Anil,
> Looks like the tables which you are accessing is getting locked since you
do
> all your stuff within a transaction.
> You can do 2 things to see if the issue is happening because of a lock
> 1. Remove all transaction (begin/commit tran) and see if your code actuall
y
> works.
> 2. If your code works after step 1 then use WITH (NOLOCK) clause in your
> select statements.
>
> Regards,
> Joe.
>
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in messag
e
> news:47D3D1AF-4E14-4C0B-80B9-66ADCCF91254@.microsoft.com...
> cursor 2
> be
> out my
> tables. But the
> My code is
> [user_id] from
> 'userdata_user_data_id'
> update_date,
>
>|||Hey Joe,
Thanks for the help. Worked like a charm!!
Anil
"Joe" wrote:

> Hi Anil,
> Looks like the tables which you are accessing is getting locked since you
do
> all your stuff within a transaction.
> You can do 2 things to see if the issue is happening because of a lock
> 1. Remove all transaction (begin/commit tran) and see if your code actuall
y
> works.
> 2. If your code works after step 1 then use WITH (NOLOCK) clause in your
> select statements.
>
> Regards,
> Joe.
>
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in messag
e
> news:47D3D1AF-4E14-4C0B-80B9-66ADCCF91254@.microsoft.com...
> cursor 2
> be
> out my
> tables. But the
> My code is
> [user_id] from
> 'userdata_user_data_id'
> update_date,
>
>sql

multiples charts and tables on the same report from multiple datasets

Hi,
I am trying to use Reporting services toi create a report with a number of
charts and tables from the same datasource but multiple datasets. When I
created a table and dragged the data fields into the column it is
=First(Fields!OrderID.Value, "DataSet1")
It keeps repeating until the end of rows where in the dataset I used top5
SQL what should be the correct way to do it?
Also I am wondering if I can modify the color of and space between bar chart
items.
Thank you in advance,
SunnyRemove "First" from your code, so you end up with =Fields!OrderID.Value.
Also, in the charts, you might have to change your values. At least for me
the values shows up as =Count(Fields!OrderID.Value), and I usually don't
want to have "Count" at all.
You can change the background color of a chart, just right click and choose
properties. (DOn't remeber where, but you'll find it if you look for it.)
DOn't think you can change the spacing, except with just changing the size
of the chart.
Kaisa M. LIndahl
"Sansanee" <sansanee@.nospam.com> wrote in message
news:ufPXgHlNFHA.3704@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I am trying to use Reporting services toi create a report with a number of
> charts and tables from the same datasource but multiple datasets. When I
> created a table and dragged the data fields into the column it is
> =First(Fields!OrderID.Value, "DataSet1")
> It keeps repeating until the end of rows where in the dataset I used top5
> SQL what should be the correct way to do it?
> Also I am wondering if I can modify the color of and space between bar
chart
> items.
>
> Thank you in advance,
> Sunny
>

Multiple/optional parameters

Can I create a report that offers users a choice for the parameter. I want to show a sales report based on either Fiscal year or Calender Year. Can I do that with one report that allows an option on which parameter to choose or do I need two reports.

Thanks.

Hello John,

Yes it can be done in a single report but you would have to create an extra parameter and do all the logic in the Stored procedure.

1.) Create an extra parameter in the Report Parameters called ParamYear as Datatype string.

2.) In the available values select Non-Queried and enter FisacalYear in Label and Value and also the CalenderYear.

3.) In the stored Procedure the logic would be like this:

If @.Parm = fisicalyear

Begin

End

Else

Begin

End

Hope this helps..

|||

I'm running all my queries against the cube but I guess the logic would be similar.

I should include another parameter and the first thing they select is Fiscal or Calender and then the proper hirarchies are offered to pick from?

|||It depends on the hirarchies. You can set the default value which one you want first and follow on. In the SP it gets the value for the one it selected. so hirarchies doesn't matter I believe.|||Can you hide a parameter list and then display one parameter or the other depending on the data from the first parameter?|||

Yes , it is possible. I personally did not do it but this link might be helpful.

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

John, I have this saved in my Code-Sample database and this was answered in the same forum.

This is possible. I'll give an example using the AdventureWorks db. Suppose you want to have a report with all the contacts. The first filter on your report will filter on the Title column, the second will filter on LastName. When a user selected a Title in the dropdownlist, the dropdownlist will be filled with all the LastName's that have the same Title as the one selected.

Here is how to do this:

1.) create a dataset 'TITLES' with the following sql statement:
SELECT DISTINCT Title FROM Person.Contact ORDER BY Title

2.) create a second dataset 'LASTNAMES' with the following sql statement:
SELECT DISTINCT LastName FROM Person.Contact WHERE Title = @.Title

3.)create a third dataset 'CONTACTDETAILS' with the following sql statement:
SELECT * FROM Person.Contact WHERE Title = @.Title AND LastName = @.LastName

4.) Go to the Layout tab and open the \Report\Report Parameters menu

Normally you will see two parameters: Title and LastName. Make sure that Title is the first parameter.

Select the Title parameter and set the following settings:
Available values\From Query\DataSet = TITLES
Available values\From Query\Value Field = Title
Available values\From Query\Label Field = Title

Select the LastName parameter and set the following settings:
Available values\From Query\DataSet = LASTNAMES
Available values\From Query\Value Field = LastName
Available values\From Query\Label Field = LastName

Click OK to leave the Report Parameters menu.

5.)Drag a table on your report and add fields from the CONTACTDETAILS dataset to it.

If everything went well and you run your report, you will first need to select a value for the Title filter. After that, the LastName filter will be filled based on the value selected from the Title filter. When selecting a name in there, the report will be shown.

|||

I'm not quite doing the same thing.

I want a report that will drop down a list of fiscal year/months or calender year/months depending upon user input. I've thought about creating a report with 2 subreports, one a fiscal year report and one a calender year report. The subreports will be toggled from the main report. I can default to one and let the user toggle between reports. No parameters will be passed, the user will make appropriate selection in the subreport.

multiple view create

Hi
How can I create multiple views in a single SQL script ?
(I get an error: 'CREATE VIEW' must be the first statement in a query
batch.)
thanksYou can use the batch terminator, GO, to seperate the batches:
CREATE VIEW xyz
AS
SELECT
..
GO
CREATE VIEW abc
AS
SELECT
..
GO
... etc
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"romy" <romy1000@.hotpop.com> wrote in message
news:e6ml7BM5FHA.636@.TK2MSFTNGP10.phx.gbl...
> Hi
> How can I create multiple views in a single SQL script ?
> (I get an error: 'CREATE VIEW' must be the first statement in a query
> batch.)
>
> thanks
>sql

Multiple value parameters in SQL Server 2000 Reporting Services.

Hi all,

I need to know how, and if, possible to create a multiple value parameter in SQL Server 2000 Reporting Services. I need this for a client of mine. Any help/tips/etc will be greatly appreciated.

Thank you,

Hi,

Unfortunatelly, there is no official way to push multipy values fo parmeters in SSRS 2000, however, you can hardcode a list with multiply values behind eg: Sales Users as text and "Jim; Bob " ...etc as values and reuse them in the procedure or construct your t-sql string based on parameter with sp_executesql. There is a security risk with the last option.

Regards,

Janos

|||Thanks a lot Janos. I'll see what I can do. Maybe I can convince the client to go to SSRS 2005?

Let's hope.

Thanks anyway,

multiple transaction ?

I have a question regarding multiple transactions. I
would like to create a main stored procedure that calls
several other stored procedures. I would like to use
transactions when I write all of my SP's but am unsure
how it would works. I am running into problems when a
rollback occurs when i have multiple transactions. Is
there a standard way of setting up a commit/rollback
procedure in all SP's? I always use the following but it
does not seem to work correctly.
BEGIN TRANSACTION
IF @.ErrorCount = 0
COMMIT TRANSATION
ELSE
ROLLBACK TRANSACTION
Do i need something in here to look at the transaction
count?
Any help would be appreciated.
Thanks
B.A.I have to assume that @.ErrorCount is something of your doing so I will also
assume it is getting this correctly. When you issue a ROLLBACK, regardless
of where it's issued, all teh open transactions will be rolled back. So you
want to test to see if @.@.TRANCOUNT is greater than 0 before issuing the
rollbck otherwise you will get an error.
Andy
"B.A. Baracus" <JCoxEUP@.hotmail.com> wrote in message
news:014c01c34f12$4d4b6c20$a501280a@.phx.gbl...
> I have a question regarding multiple transactions. I
> would like to create a main stored procedure that calls
> several other stored procedures. I would like to use
> transactions when I write all of my SP's but am unsure
> how it would works. I am running into problems when a
> rollback occurs when i have multiple transactions. Is
> there a standard way of setting up a commit/rollback
> procedure in all SP's? I always use the following but it
> does not seem to work correctly.
> BEGIN TRANSACTION
> IF @.ErrorCount = 0
> COMMIT TRANSATION
> ELSE
> ROLLBACK TRANSACTION
> Do i need something in here to look at the transaction
> count?
> Any help would be appreciated.
> Thanks
> B.A.
>

multiple tempdb references in master sysaltfiles

Hi,
We tried to create a new database on an application server (Win 2003
server/SQL Server 2003) and got the following error.
error 945...
....
device activation error. The physical filename g:\mssqldata
\templog.ldf may be incorrect.
The problem is that tempdb is on f:\mssql as shown in the database
properties and with sp_dbhelp.
Poking around in master the real wierdness comes through. sysaltfiles
has 2 entries each for tempdb logs and data files. One of them is on
g: and one is on f:. The lower dbid is on f: and the higher one is on
g: (actually the last two rows in the table).
I hope that makes sense: there're 4 rows for tempdb. one pair points
the log and data files to the g: drive and one pair of rows points
them to the f: drive. The references to g: drive need to go away but
I've been googinling for a while now and haven't come up with much.
I tried running the alter database command normally used to move
tempdb and though it didn't fail, it didn't change anything.
Several months ago our software vendor moved tempdb from g: to f: to
try and speed it up a bit. Appearantly they messed it up and now have
written us off till WE fix it.
The entries in sysaltfiles were the only references to g: that turned
up (though we didn't look at every table and aren't even remotely sure
where other references might be located).
Any pointers on getting this corrected would be greatly apprecieated.
We thought about trying a reconfigure and restarting but I'm not real
hopeful. We also thought about just updating the wrong entries to
reflect the right locations but that smacks of kluge.
Tangential wierdness is that while trying to isolate the source of g:
\mssql in the error I found that in master.sysdevices the file
location is e:\Program Files\Microsoft SQL Server\MSSQL\data
\tempdb.mdf.
I believe this is from the initial install then while configuring the
server it got moved to g: then to f:.
HELP!!!
Thanks in advance for any input!
Rusty
I've been able to verify that sysdatabases reference to tempdb points
at dbid2 which in sysaltfiles is the dbid of the two rows that point
at the CORRECT file locations....
Im starting to think it might be okay to just remove the incorrect
rows out of sysaltfiles and restart. B
But the thought gives me the screaming heebie-jeebies!
|||Hi
"rnbwil@.gmail.com" wrote:

> Hi,
> We tried to create a new database on an application server (Win 2003
> server/SQL Server 2003) and got the following error.
>
> error 945...
> ....
> device activation error. The physical filename g:\mssqldata
> \templog.ldf may be incorrect.
> The problem is that tempdb is on f:\mssql as shown in the database
> properties and with sp_dbhelp.
> Poking around in master the real wierdness comes through. sysaltfiles
> has 2 entries each for tempdb logs and data files. One of them is on
> g: and one is on f:. The lower dbid is on f: and the higher one is on
> g: (actually the last two rows in the table).
> I hope that makes sense: there're 4 rows for tempdb. one pair points
> the log and data files to the g: drive and one pair of rows points
> them to the f: drive. The references to g: drive need to go away but
> I've been googinling for a while now and haven't come up with much.
> I tried running the alter database command normally used to move
> tempdb and though it didn't fail, it didn't change anything.
> Several months ago our software vendor moved tempdb from g: to f: to
> try and speed it up a bit. Appearantly they messed it up and now have
> written us off till WE fix it.
> The entries in sysaltfiles were the only references to g: that turned
> up (though we didn't look at every table and aren't even remotely sure
> where other references might be located).
> Any pointers on getting this corrected would be greatly apprecieated.
> We thought about trying a reconfigure and restarting but I'm not real
> hopeful. We also thought about just updating the wrong entries to
> reflect the right locations but that smacks of kluge.
> Tangential wierdness is that while trying to isolate the source of g:
> \mssql in the error I found that in master.sysdevices the file
> location is e:\Program Files\Microsoft SQL Server\MSSQL\data
> \tempdb.mdf.
> I believe this is from the initial install then while configuring the
> server it got moved to g: then to f:.
> HELP!!!
> Thanks in advance for any input!
> Rusty
>
When creating the database I would not expect tempdb to have anything to do
with it!
What command have you used to create this database? It seems to me that you
have specified g:\mssqldata\templog.ldf as the log file instead of
g:\mssql\data\NewDatabase_log.ldf? Check that the default database locations
have been correctly set.
What happens is you just use the T-SQL
CREATE DATABASE NewDatabase
John
|||On May 31, 11:52 pm, John Bell <jbellnewspo...@.hotmail.com> wrote:
> Hi
>
>
> "rnb...@.gmail.com" wrote:
>
>
>
>
>
>
>
>
> When creating the database I would not expect tempdb to have anything to do
> with it!
> What command have you used to create this database? It seems to me that you
> have specified g:\mssqldata\templog.ldf as the log file instead of
> g:\mssql\data\NewDatabase_log.ldf? Check that the default database locations
> have been correctly set.
> What happens is you just use the T-SQL
> CREATE DATABASE NewDatabase
> John- Hide quoted text -
> - Show quoted text -
John - thanks for the input. I wouldn't think it would either but...
This is the output from 'create database test123' in QA.
Server: Msg 945, Level 14, State 2, Line 1
Database 'test123' cannot be opened due to inaccessible files or
insufficient memory or disk space. See the SQL Server errorlog for
details.
The CREATE DATABASE process is allocating 0.63 MB on disk 'test123'.
The CREATE DATABASE process is allocating 0.49 MB on disk
'test123_log'.
Device activation error. The physical file name 'g:\Sqldata
\templog.ldf' may be incorrect.
|||Hi
"rnbwil@.gmail.com" wrote:

> On May 31, 11:52 pm, John Bell <jbellnewspo...@.hotmail.com> wrote:
> John - thanks for the input. I wouldn't think it would either but...
>
> This is the output from 'create database test123' in QA.
> Server: Msg 945, Level 14, State 2, Line 1
> Database 'test123' cannot be opened due to inaccessible files or
> insufficient memory or disk space. See the SQL Server errorlog for
> details.
> The CREATE DATABASE process is allocating 0.63 MB on disk 'test123'.
> The CREATE DATABASE process is allocating 0.49 MB on disk
> 'test123_log'.
> Device activation error. The physical file name 'g:\Sqldata
> \templog.ldf' may be incorrect.
>
That is a different directory on the G Drive to the one you initially posted!
CREATE DATABASE test123
ON ( NAME = Test123_dat,
FILENAME = 'F:\mssql\data\test123.mdf',
SIZE = 10,
FILEGROWTH = 5 )
LOG ON
( NAME = 'Test123_log',
FILENAME = 'F:\mssql\data\test123.ldf',
SIZE = 5MB,
FILEGROWTH = 5MB )
GO
See what sp_helpfiles returns when you are in tempdb and model. You may then
want to try moving tempdb using the ALTER DATABASE command as described in
http://support.microsoft.com/kb/224071/
John
|||Tried the above and received the same error.
I'm not sure moving tempdb will help.
I ran the alter database sql (per the ms kb on moving tempdb)
specifying the current location and it didn't error out but didn't do
anything to the extraneous entries.

multiple tempdb references in master sysaltfiles

Hi,
We tried to create a new database on an application server (Win 2003
server/SQL Server 2003) and got the following error.
error 945...
...
device activation error. The physical filename g:\mssqldata
\templog.ldf may be incorrect.
The problem is that tempdb is on f:\mssql as shown in the database
properties and with sp_dbhelp.
Poking around in master the real wierdness comes through. sysaltfiles
has 2 entries each for tempdb logs and data files. One of them is on
g: and one is on f:. The lower dbid is on f: and the higher one is on
g: (actually the last two rows in the table).
I hope that makes sense: there're 4 rows for tempdb. one pair points
the log and data files to the g: drive and one pair of rows points
them to the f: drive. The references to g: drive need to go away but
I've been googinling for a while now and haven't come up with much.
I tried running the alter database command normally used to move
tempdb and though it didn't fail, it didn't change anything.
Several months ago our software vendor moved tempdb from g: to f: to
try and speed it up a bit. Appearantly they messed it up and now have
written us off till WE fix it.
The entries in sysaltfiles were the only references to g: that turned
up (though we didn't look at every table and aren't even remotely sure
where other references might be located).
Any pointers on getting this corrected would be greatly apprecieated.
We thought about trying a reconfigure and restarting but I'm not real
hopeful. We also thought about just updating the wrong entries to
reflect the right locations but that smacks of kluge.
Tangential wierdness is that while trying to isolate the source of g:
\mssql in the error I found that in master.sysdevices the file
location is e:\Program Files\Microsoft SQL Server\MSSQL\data
\tempdb.mdf.
I believe this is from the initial install then while configuring the
server it got moved to g: then to f:.
HELP!!!
Thanks in advance for any input!
RustyI've been able to verify that sysdatabases reference to tempdb points
at dbid2 which in sysaltfiles is the dbid of the two rows that point
at the CORRECT file locations....
Im starting to think it might be okay to just remove the incorrect
rows out of sysaltfiles and restart. B
But the thought gives me the screaming heebie-jeebies!|||Hi
"rnbwil@.gmail.com" wrote:

> Hi,
> We tried to create a new database on an application server (Win 2003
> server/SQL Server 2003) and got the following error.
>
> error 945...
> ....
> device activation error. The physical filename g:\mssqldata
> \templog.ldf may be incorrect.
> The problem is that tempdb is on f:\mssql as shown in the database
> properties and with sp_dbhelp.
> Poking around in master the real wierdness comes through. sysaltfiles
> has 2 entries each for tempdb logs and data files. One of them is on
> g: and one is on f:. The lower dbid is on f: and the higher one is on
> g: (actually the last two rows in the table).
> I hope that makes sense: there're 4 rows for tempdb. one pair points
> the log and data files to the g: drive and one pair of rows points
> them to the f: drive. The references to g: drive need to go away but
> I've been googinling for a while now and haven't come up with much.
> I tried running the alter database command normally used to move
> tempdb and though it didn't fail, it didn't change anything.
> Several months ago our software vendor moved tempdb from g: to f: to
> try and speed it up a bit. Appearantly they messed it up and now have
> written us off till WE fix it.
> The entries in sysaltfiles were the only references to g: that turned
> up (though we didn't look at every table and aren't even remotely sure
> where other references might be located).
> Any pointers on getting this corrected would be greatly apprecieated.
> We thought about trying a reconfigure and restarting but I'm not real
> hopeful. We also thought about just updating the wrong entries to
> reflect the right locations but that smacks of kluge.
> Tangential wierdness is that while trying to isolate the source of g:
> \mssql in the error I found that in master.sysdevices the file
> location is e:\Program Files\Microsoft SQL Server\MSSQL\data
> \tempdb.mdf.
> I believe this is from the initial install then while configuring the
> server it got moved to g: then to f:.
> HELP!!!
> Thanks in advance for any input!
> Rusty
>
When creating the database I would not expect tempdb to have anything to do
with it!
What command have you used to create this database? It seems to me that you
have specified g:\mssqldata\templog.ldf as the log file instead of
g:\mssql\data\NewDatabase_log.ldf? Check that the default database locations
have been correctly set.
What happens is you just use the T-SQL
CREATE DATABASE NewDatabase
John|||On May 31, 11:52 pm, John Bell <jbellnewspo...@.hotmail.com> wrote:
> Hi
>
>
> "rnb...@.gmail.com" wrote:
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> When creating the database I would not expect tempdb to have anything to d
o
> with it!
> What command have you used to create this database? It seems to me that yo
u
> have specified g:\mssqldata\templog.ldf as the log file instead of
> g:\mssql\data\NewDatabase_log.ldf? Check that the default database locatio
ns
> have been correctly set.
> What happens is you just use the T-SQL
> CREATE DATABASE NewDatabase
> John- Hide quoted text -
> - Show quoted text -
John - thanks for the input. I wouldn't think it would either but...
This is the output from 'create database test123' in QA.
Server: Msg 945, Level 14, State 2, Line 1
Database 'test123' cannot be opened due to inaccessible files or
insufficient memory or disk space. See the SQL Server errorlog for
details.
The CREATE DATABASE process is allocating 0.63 MB on disk 'test123'.
The CREATE DATABASE process is allocating 0.49 MB on disk
'test123_log'.
Device activation error. The physical file name 'g:\Sqldata
\templog.ldf' may be incorrect.|||Hi
"rnbwil@.gmail.com" wrote:

> On May 31, 11:52 pm, John Bell <jbellnewspo...@.hotmail.com> wrote:
> John - thanks for the input. I wouldn't think it would either but...
>
> This is the output from 'create database test123' in QA.
> Server: Msg 945, Level 14, State 2, Line 1
> Database 'test123' cannot be opened due to inaccessible files or
> insufficient memory or disk space. See the SQL Server errorlog for
> details.
> The CREATE DATABASE process is allocating 0.63 MB on disk 'test123'.
> The CREATE DATABASE process is allocating 0.49 MB on disk
> 'test123_log'.
> Device activation error. The physical file name 'g:\Sqldata
> \templog.ldf' may be incorrect.
>
That is a different directory on the G Drive to the one you initially posted
!
CREATE DATABASE test123
ON ( NAME = Test123_dat,
FILENAME = 'F:\mssql\data\test123.mdf',
SIZE = 10,
FILEGROWTH = 5 )
LOG ON
( NAME = 'Test123_log',
FILENAME = 'F:\mssql\data\test123.ldf',
SIZE = 5MB,
FILEGROWTH = 5MB )
GO
See what sp_helpfiles returns when you are in tempdb and model. You may then
want to try moving tempdb using the ALTER DATABASE command as described in
http://support.microsoft.com/kb/224071/
John|||Tried the above and received the same error.
I'm not sure moving tempdb will help.
I ran the alter database sql (per the ms kb on moving tempdb)
specifying the current location and it didn't error out but didn't do
anything to the extraneous entries.

multiple tempdb references in master sysaltfiles

Hi,
We tried to create a new database on an application server (Win 2003
server/SQL Server 2003) and got the following error.
error 945...
...
device activation error. The physical filename g:\mssqldata
\templog.ldf may be incorrect.
The problem is that tempdb is on f:\mssql as shown in the database
properties and with sp_dbhelp.
Poking around in master the real wierdness comes through. sysaltfiles
has 2 entries each for tempdb logs and data files. One of them is on
g: and one is on f:. The lower dbid is on f: and the higher one is on
g: (actually the last two rows in the table).
I hope that makes sense: there're 4 rows for tempdb. one pair points
the log and data files to the g: drive and one pair of rows points
them to the f: drive. The references to g: drive need to go away but
I've been googinling for a while now and haven't come up with much.
I tried running the alter database command normally used to move
tempdb and though it didn't fail, it didn't change anything.
Several months ago our software vendor moved tempdb from g: to f: to
try and speed it up a bit. Appearantly they messed it up and now have
written us off till WE fix it.
The entries in sysaltfiles were the only references to g: that turned
up (though we didn't look at every table and aren't even remotely sure
where other references might be located).
Any pointers on getting this corrected would be greatly apprecieated.
We thought about trying a reconfigure and restarting but I'm not real
hopeful. We also thought about just updating the wrong entries to
reflect the right locations but that smacks of kluge.
Tangential wierdness is that while trying to isolate the source of g:
\mssql in the error I found that in master.sysdevices the file
location is e:\Program Files\Microsoft SQL Server\MSSQL\data
\tempdb.mdf.
I believe this is from the initial install then while configuring the
server it got moved to g: then to f:.
HELP!!!
Thanks in advance for any input!
RustyI've been able to verify that sysdatabases reference to tempdb points
at dbid2 which in sysaltfiles is the dbid of the two rows that point
at the CORRECT file locations....
Im starting to think it might be okay to just remove the incorrect
rows out of sysaltfiles and restart. B
But the thought gives me the screaming heebie-jeebies!|||Hi
"rnbwil@.gmail.com" wrote:
> Hi,
> We tried to create a new database on an application server (Win 2003
> server/SQL Server 2003) and got the following error.
>
> error 945...
> ....
> device activation error. The physical filename g:\mssqldata
> \templog.ldf may be incorrect.
> The problem is that tempdb is on f:\mssql as shown in the database
> properties and with sp_dbhelp.
> Poking around in master the real wierdness comes through. sysaltfiles
> has 2 entries each for tempdb logs and data files. One of them is on
> g: and one is on f:. The lower dbid is on f: and the higher one is on
> g: (actually the last two rows in the table).
> I hope that makes sense: there're 4 rows for tempdb. one pair points
> the log and data files to the g: drive and one pair of rows points
> them to the f: drive. The references to g: drive need to go away but
> I've been googinling for a while now and haven't come up with much.
> I tried running the alter database command normally used to move
> tempdb and though it didn't fail, it didn't change anything.
> Several months ago our software vendor moved tempdb from g: to f: to
> try and speed it up a bit. Appearantly they messed it up and now have
> written us off till WE fix it.
> The entries in sysaltfiles were the only references to g: that turned
> up (though we didn't look at every table and aren't even remotely sure
> where other references might be located).
> Any pointers on getting this corrected would be greatly apprecieated.
> We thought about trying a reconfigure and restarting but I'm not real
> hopeful. We also thought about just updating the wrong entries to
> reflect the right locations but that smacks of kluge.
> Tangential wierdness is that while trying to isolate the source of g:
> \mssql in the error I found that in master.sysdevices the file
> location is e:\Program Files\Microsoft SQL Server\MSSQL\data
> \tempdb.mdf.
> I believe this is from the initial install then while configuring the
> server it got moved to g: then to f:.
> HELP!!!
> Thanks in advance for any input!
> Rusty
>
When creating the database I would not expect tempdb to have anything to do
with it!
What command have you used to create this database? It seems to me that you
have specified g:\mssqldata\templog.ldf as the log file instead of
g:\mssql\data\NewDatabase_log.ldf? Check that the default database locations
have been correctly set.
What happens is you just use the T-SQL
CREATE DATABASE NewDatabase
John|||On May 31, 11:52 pm, John Bell <jbellnewspo...@.hotmail.com> wrote:
> Hi
>
>
> "rnb...@.gmail.com" wrote:
> > Hi,
> > We tried to create a new database on an application server (Win 2003
> > server/SQL Server 2003) and got the following error.
> > error 945...
> > ....
> > device activation error. The physical filename g:\mssqldata
> > \templog.ldf may be incorrect.
> > The problem is that tempdb is on f:\mssql as shown in the database
> > properties and with sp_dbhelp.
> > Poking around in master the real wierdness comes through. sysaltfiles
> > has 2 entries each for tempdb logs and data files. One of them is on
> > g: and one is on f:. The lower dbid is on f: and the higher one is on
> > g: (actually the last two rows in the table).
> > I hope that makes sense: there're 4 rows for tempdb. one pair points
> > the log and data files to the g: drive and one pair of rows points
> > them to the f: drive. The references to g: drive need to go away but
> > I've been googinling for a while now and haven't come up with much.
> > I tried running the alter database command normally used to move
> > tempdb and though it didn't fail, it didn't change anything.
> > Several months ago our software vendor moved tempdb from g: to f: to
> > try and speed it up a bit. Appearantly they messed it up and now have
> > written us off till WE fix it.
> > The entries in sysaltfiles were the only references to g: that turned
> > up (though we didn't look at every table and aren't even remotely sure
> > where other references might be located).
> > Any pointers on getting this corrected would be greatly apprecieated.
> > We thought about trying a reconfigure and restarting but I'm not real
> > hopeful. We also thought about just updating the wrong entries to
> > reflect the right locations but that smacks of kluge.
> > Tangential wierdness is that while trying to isolate the source of g:
> > \mssql in the error I found that in master.sysdevices the file
> > location is e:\Program Files\Microsoft SQL Server\MSSQL\data
> > \tempdb.mdf.
> > I believe this is from the initial install then while configuring the
> > server it got moved to g: then to f:.
> > HELP!!!
> > Thanks in advance for any input!
> > Rusty
> When creating the database I would not expect tempdb to have anything to do
> with it!
> What command have you used to create this database? It seems to me that you
> have specified g:\mssqldata\templog.ldf as the log file instead of
> g:\mssql\data\NewDatabase_log.ldf? Check that the default database locations
> have been correctly set.
> What happens is you just use the T-SQL
> CREATE DATABASE NewDatabase
> John- Hide quoted text -
> - Show quoted text -
John - thanks for the input. I wouldn't think it would either but...
This is the output from 'create database test123' in QA.
Server: Msg 945, Level 14, State 2, Line 1
Database 'test123' cannot be opened due to inaccessible files or
insufficient memory or disk space. See the SQL Server errorlog for
details.
The CREATE DATABASE process is allocating 0.63 MB on disk 'test123'.
The CREATE DATABASE process is allocating 0.49 MB on disk
'test123_log'.
Device activation error. The physical file name 'g:\Sqldata
\templog.ldf' may be incorrect.|||Hi
"rnbwil@.gmail.com" wrote:
> On May 31, 11:52 pm, John Bell <jbellnewspo...@.hotmail.com> wrote:
> > Hi
> > "rnb...@.gmail.com" wrote:
> >
> > > Hi,
> >
> > > We tried to create a new database on an application server (Win 2003
> > > server/SQL Server 2003) and got the following error.
> >
> > > error 945...
> >
> > > ....
> > > device activation error. The physical filename g:\mssqldata
> > > \templog.ldf may be incorrect.
> >
> > > The problem is that tempdb is on f:\mssql as shown in the database
> > > properties and with sp_dbhelp.
> >
> > > Poking around in master the real wierdness comes through. sysaltfiles
> > > has 2 entries each for tempdb logs and data files. One of them is on
> > > g: and one is on f:. The lower dbid is on f: and the higher one is on
> > > g: (actually the last two rows in the table).
> >
> > > I hope that makes sense: there're 4 rows for tempdb. one pair points
> > > the log and data files to the g: drive and one pair of rows points
> > > them to the f: drive. The references to g: drive need to go away but
> > > I've been googinling for a while now and haven't come up with much.
> >
> > > I tried running the alter database command normally used to move
> > > tempdb and though it didn't fail, it didn't change anything.
> >
> > > Several months ago our software vendor moved tempdb from g: to f: to
> > > try and speed it up a bit. Appearantly they messed it up and now have
> > > written us off till WE fix it.
> >
> > > The entries in sysaltfiles were the only references to g: that turned
> > > up (though we didn't look at every table and aren't even remotely sure
> > > where other references might be located).
> >
> > > Any pointers on getting this corrected would be greatly apprecieated.
> > > We thought about trying a reconfigure and restarting but I'm not real
> > > hopeful. We also thought about just updating the wrong entries to
> > > reflect the right locations but that smacks of kluge.
> >
> > > Tangential wierdness is that while trying to isolate the source of g:
> > > \mssql in the error I found that in master.sysdevices the file
> > > location is e:\Program Files\Microsoft SQL Server\MSSQL\data
> > > \tempdb.mdf.
> >
> > > I believe this is from the initial install then while configuring the
> > > server it got moved to g: then to f:.
> >
> > > HELP!!!
> >
> > > Thanks in advance for any input!
> > > Rusty
> >
> > When creating the database I would not expect tempdb to have anything to do
> > with it!
> >
> > What command have you used to create this database? It seems to me that you
> > have specified g:\mssqldata\templog.ldf as the log file instead of
> > g:\mssql\data\NewDatabase_log.ldf? Check that the default database locations
> > have been correctly set.
> >
> > What happens is you just use the T-SQL
> >
> > CREATE DATABASE NewDatabase
> >
> > John- Hide quoted text -
> >
> > - Show quoted text -
> John - thanks for the input. I wouldn't think it would either but...
>
> This is the output from 'create database test123' in QA.
> Server: Msg 945, Level 14, State 2, Line 1
> Database 'test123' cannot be opened due to inaccessible files or
> insufficient memory or disk space. See the SQL Server errorlog for
> details.
> The CREATE DATABASE process is allocating 0.63 MB on disk 'test123'.
> The CREATE DATABASE process is allocating 0.49 MB on disk
> 'test123_log'.
> Device activation error. The physical file name 'g:\Sqldata
> \templog.ldf' may be incorrect.
>
That is a different directory on the G Drive to the one you initially posted!
CREATE DATABASE test123
ON ( NAME = Test123_dat,
FILENAME = 'F:\mssql\data\test123.mdf',
SIZE = 10,
FILEGROWTH = 5 )
LOG ON
( NAME = 'Test123_log',
FILENAME = 'F:\mssql\data\test123.ldf',
SIZE = 5MB,
FILEGROWTH = 5MB )
GO
See what sp_helpfiles returns when you are in tempdb and model. You may then
want to try moving tempdb using the ALTER DATABASE command as described in
http://support.microsoft.com/kb/224071/
John|||Tried the above and received the same error.
I'm not sure moving tempdb will help.
I ran the alter database sql (per the ms kb on moving tempdb)
specifying the current location and it didn't error out but didn't do
anything to the extraneous entries.

Multiple Table Combination

I would like to create a query that would generate the output table, but I'm not sure where to start.

[USER TABLE]

userID fName lName
1 Joe Smith
2 Mike Brown

[CATEGORY TABLE]

categoryID title
1 category1
2 category2
3 category3

[USERCATEGORy TABLE]

userID categoryID
1 1
1 3
2 2
2 3

[OUTPUT TABLE]
(comma seperate rows

fName lName category1 category2 category3
Joe,Smith,x, ,x
Mike,Brown, ,x,xThis is a classic cross-tab query. There is a generic solution, and there are database engine specific solutions. The generic solution is a lot more work, and it is vulnerable to some kinds of data errors.

Can you give us a bit more background so we can help you out?

-PatP|||The rdbms is ms sql 2000. I was debating wether to post it here or in that category. It will lead to an output as an excel file, that is why I want the x's in the category, but for now i'm just interesting the sql to generate this. If a sql solution is not advisable I welcome pointers in t-sql that might help me with this.|||Try doing a search for cross-tab in the SQL Server forum. I've answered this type of question at least a dozen times there, you should be able to find a decent hit or three!

-PatP|||Books On Line has an excellent explanation and example of Crosstab queries. Look it up.

Multiple Sort Order Specification in EDB database

Hi,

I am using the EDB as a database in smart phone applications.I can able to mount the database , create the tables , write the records into the tables and read from the table with single sort order specification.But if i am using more than one sort order specification seek database is throwing an error message "The drive cannot locate a specific area or track on the disk".

I created one table named as Icon with more than one sort order specification.In this table i am giving pageid and iconid as sort order specifications. While saving the data into the database i am opening the database with pageid sort order and storing into the table.while reading from the database i am opening the database with iconid as sortorder.So here while seeking the database the above error is displaying.And i tried to read the data from the table by using pagid as sortorder speicification.It is working fine.I am applying the 3 sort order specifications for another table named as contentTable. but it is working fine with 3 sort order specifications.I had done the samething for icon table .But no use.Please help me regarding this.

Thanks in Advance,

Thanks & Regards,

Prasanna Kumar

Moving to Sql Server compact edition forum where it has got better chances of being answered.

-Thanks,

Mohit

Multiple snapshots on the same database.

I'd like to create multiple "replication" on the same database in order
to sync only some tables in each replication. When I try to add a
second push replication to the subscriber i got the following error:
"The subscription already exists."
Is there a way to do this?
Christian - do you have multiple publications? It sounds as though you are
setting up multiple subscriptions but to the same publication, judging by
the error message.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

multiple select statements

Hi guys and gals,

I am trying to create a select statement that will return an INT that I will later have to use in another select statement. I have the following code, however, I keep getting an error that says:

'Error116: Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.'

My Code is below:

//Start of sql

CREATE PROCEDURE ADMIN_GetSingleUsers
(
@.userID int
)
AS

DECLARE @.userSQL int
SET @.userSQL = (SELECT User_ID, TITLE.TITLE AS TITLE,
Cast(Users.Active as varchar(50)) as Active,
Cast(Users.Approved as varchar(50)) as Approved,
Users.Unit_ID As usersUnitID,
*
From TITLE, Users
WHERE
User_ID = @.userID AND
TITLE.TITLE_ID = Users.Title_ID )

Select Unit_ID, Parent_ID, Unit_Name from UNITS WHERE Unit_ID = @.userSQL

//End of sql

Can you point to what I am doing wrong? Thanks in advance!

You are trying to SET @.userSQL to more than one value (User_ID, Title.Title, Users.Active, Users.Approved, and Users.Unit_ID).

Try it in one statement instead, something like this:

SELECT
Units.Unit_ID,
Units.Parent_ID,
Units.Unit_Name
FROM
Units
INNER JOIN
Users ON Units.Unit_ID = Users.Unit_ID AND Users.User_ID = @.UserID
INNER JOIN
Title ON Users.Title_ID = Title.Title_ID

|||

Depends on if you wanted both result sets to be returned or just the second one.

return both:

SELECT User_ID, TITLE.TITLE AS TITLE,
Cast(Users.Active as varchar(50)) as Active,
Cast(Users.Approved as varchar(50)) as Approved,
@.userSQL=Users.Unit_ID As usersUnitID,
*
From TITLE

JOIN USERS ON (TITLE.TITLE_ID = Users.Title_ID)

WHERE User_ID = @.userID

Select Unit_ID, Parent_ID, Unit_Name from UNITS WHERE Unit_ID = @.userSQL

return second one:

SELECT @.userSQL=Users.Unit_ID

From TITLE

JOIN USERS ON (TITLE.TITLE_ID = Users.Title_ID)

WHERE User_ID = @.userID

Select Unit_ID, Parent_ID, Unit_Name from UNITS WHERE Unit_ID = @.userSQL

Or if you don't need @.userSQL except for limiting the second query:

SELECT Unit_ID, Parent_ID, Unit_Name@.userSQL=Users.Unit_ID

From TITLE

JOIN USERS ON (TITLE.TITLE_ID = Users.Title_ID)

JOIN UNITS ON (Users.Unit_ID=UNITS.Unit_ID)

WHERE User_ID = @.userID

|||

Another Question.

Is there a way to do the following?

DECLARE unitID nVarChar(255)
SET @.unitID = (Select * From Units)

and give @.unitID the exact value from *?
Or loop throug the @.unitID and get the value I need?

|||Declare it as a table?