retriving data from a temporal table

Hi ,
I've created a stored procedure wich creates a temporal table (called
#results) , then i fill the table with data and finally at the end of the
procedure i make a "Select * FROM #Results" .
When i execute the procedure from the query analizer i can get the data
without any problem. But if i try to get the data into a visual basic ADO
recordset it always fails. I think the problem is because i'm using a
temporal table , but i need to use that solution.
If someone could give me one solution to get the data of a temporal table
into a recordset i'd been thankful.
Thanks in advance for you answers and pardon for my bad english.hi
u can use temp table when u call from VB program, but u need to do everythin
g
1. Create Table
2. Insert Data
3. Retrive data
in the same SP. else the data will be deleted / scope is lost
please let me know if u have any questions
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"Jorge Lozano" wrote:

> Hi ,
> I've created a stored procedure wich creates a temporal table (called
> #results) , then i fill the table with data and finally at the end of the
> procedure i make a "Select * FROM #Results" .
> When i execute the procedure from the query analizer i can get the data
> without any problem. But if i try to get the data into a visual basic ADO
> recordset it always fails. I think the problem is because i'm using a
> temporal table , but i need to use that solution.
> If someone could give me one solution to get the data of a temporal table
> into a recordset i'd been thankful.
> Thanks in advance for you answers and pardon for my bad english.|||My guess is that it will work if you add SET NOCOUNT ON in the beginning of
your stored procedure.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Jorge Lozano" <JorgeLozano@.discussions.microsoft.com> wrote in message
news:FED3495C-D379-457D-AE02-D9B188B156BC@.microsoft.com...
> Hi ,
> I've created a stored procedure wich creates a temporal table (called
> #results) , then i fill the table with data and finally at the end of the
> procedure i make a "Select * FROM #Results" .
> When i execute the procedure from the query analizer i can get the data
> without any problem. But if i try to get the data into a visual basic ADO
> recordset it always fails. I think the problem is because i'm using a
> temporal table , but i need to use that solution.
> If someone could give me one solution to get the data of a temporal table
> into a recordset i'd been thankful.
> Thanks in advance for you answers and pardon for my bad english.|||It worked Perfect!!!
Thanks a loot Tibor i owe you a very big beer.
"Tibor Karaszi" wrote:

> My guess is that it will work if you add SET NOCOUNT ON in the beginning o
f your stored procedure.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Jorge Lozano" <JorgeLozano@.discussions.microsoft.com> wrote in message
> news:FED3495C-D379-457D-AE02-D9B188B156BC@.microsoft.com...
>|||Watch out. I'm an excellent beer drinker ;-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Jorge Lozano" <JorgeLozano@.discussions.microsoft.com> wrote in message
news:02F4D367-6A89-49C9-9971-BE5BA4927491@.microsoft.com...
> It worked Perfect!!!
> Thanks a loot Tibor i owe you a very big beer.
> "Tibor Karaszi" wrote:
>

retrieving agent id?

Hi all,

I have an application that creates a publication on the server, and
have multiple mobile devices creating annonymous subscriptions to that
publications. I need to write a report that checks if each device have
the replication synchronized successfully. I can run
distribution.dbo.sp_MSenum_merge or look into
distribution.dbo.MSmerge_history to get at the data for _all_
subscriptions to a given publication, but to look at a particular
subscription, I need to filter by either the subscriber_db or agent_id
column. The problem is, how do I get either one of these information
from the device? Or is there other way of retrieving the merge history
for a particular device/annonymous subscription?

Thanks in advance,
Harold<haroldsphsu@.gmail.com> wrote in message
news:1119475787.108503.99500@.g44g2000cwa.googlegro ups.com...
> Hi all,
> I have an application that creates a publication on the server, and
> have multiple mobile devices creating annonymous subscriptions to that
> publications. I need to write a report that checks if each device have
> the replication synchronized successfully. I can run
> distribution.dbo.sp_MSenum_merge or look into
> distribution.dbo.MSmerge_history to get at the data for _all_
> subscriptions to a given publication, but to look at a particular
> subscription, I need to filter by either the subscriber_db or agent_id
> column. The problem is, how do I get either one of these information
> from the device? Or is there other way of retrieving the merge history
> for a particular device/annonymous subscription?
> Thanks in advance,
> Harold

I don't know much about merge replication, but BOL suggests that you should
consider the SQL-DMO COM interface instead of using system stored
procedures - see "Introducing Replication Programming". In your specific
case, you might try the MergePublication object's EnumSubscriptions() and
EnumMergeAgentSessions() methods, although that's just a guess - your best
option might be to post in microsoft.public.sqlserver.replication

Simon

Retrieve the default data folder path

Is it possible to find out the default path SQL Server creates databases in
depending on what instance you are connected to. E.g. My default instance
default data path is C:\Program Files\Microsoft SQL Server\MSSQL\Data. My
named instance path I also have is C:\Program Files\Microsoft SQL
Server\MSSQL$Testing\Data and my SQL Express data path is C:\Program
Files\Microsoft SQL Server\MSSQL.3\MSSQL\Data.
Can I find out the default data path for the current instance I am connected
to using a query?
ThanksYou can probably use the Model database's SysDatabases.FileName to get the
path.
"Chris" wrote:

> Is it possible to find out the default path SQL Server creates databases i
n
> depending on what instance you are connected to. E.g. My default instance
> default data path is C:\Program Files\Microsoft SQL Server\MSSQL\Data. My
> named instance path I also have is C:\Program Files\Microsoft SQL
> Server\MSSQL$Testing\Data and my SQL Express data path is C:\Program
> Files\Microsoft SQL Server\MSSQL.3\MSSQL\Data.
> Can I find out the default data path for the current instance I am connect
ed
> to using a query?
> Thanks
>
>|||Thanks Mike,
If I do the following it seems to return the path.
SELECT REPLACE(filename, 'model.mdf', '') AS DataPath FROM SysDatabases
WHERE name = 'model'
Chris
"Mike K" <MikeK@.discussions.microsoft.com> wrote in message
news:E3722206-05B2-4026-9E59-9F863C75B73E@.microsoft.com...
> You can probably use the Model database's SysDatabases.FileName to get the
> path.
> "Chris" wrote:
>|||You might want to research what database you are currently connected to.
Just checking a different database might not get you really what you want.
set nocount on
create table #output
(
spid int,
status varchar(100),
login varchar(100),
hostname varchar(100),
blkby varchar(10),
dbname varchar(30),
command varchar(50),
cputime int,
diskio int,
lastbatch varchar(20),
programname varchar(200),
spid2 int
)
insert into #output exec sp_who2
set nocount off
select
ltrim(rtrim(dbname))
from
#output
where
spid = @.@.spid
drop table #output