Is this possible ?

is it possible to have a select statement from a stored proceedure ? example ...

CREATE PROCEDURE spA
SELECT * FROM ( exec spB '1','1' )

go

the reason for doing this is becos spB does some data massaging and is used across many stored procedures, spB will be passing back a table if it is possible.

Thanks.I'd recommend that you rewrite spB as a User-defined table function. Then you can reference it in stored procedures, views, triggers, etc, just like any other table or subquery:

CREATE PROCEDURE spA
SELECT * FROM dbo.spB ('1','1')

blindman|||Or it that's something you would like to avoid, you could use

Insert Into #TmpSpB Exec SpB '1','1'

where #TmpSpB is a temporary table which has the structure of the result return by the stored procedure. The only problem with this, is that can not be nested, and I am not sure if it's working "below" SQL2000.

Best regards!|||thanks for the replies ... you've been a great help ...

Is this method possible?

Hi, I am just wondering is it possible to use report parameter values to create a view for the report?
For example I have 5 databases in SQL Server 2005: db1,db2,db3,db4 and db5. Each of the databases has the same table format but storing different datas. I need to create a report that will take in two parameters: parameter START and parameter END, lets say START=db1 and END=db4, then from these values it will create a View that contains all information from db1 up to db 5 and finally the report will be generated base on the values in the View table.
Or if anyone know of a better way to overcome this method, do let me know.
Thanks.
Hi,
I am just wondering if this question is not clear enough, there is just no answer to this particular question, or this question is being posted at the wrong section of the forum?
Please do reply something so that I know what to do.
Thanks.
|||You will need to create a stored proc that will form your desired data and output that to reporting services as a result set.|||Hi Joseph, thanks for replying. I know this method of yours will work if I only have to query from one database, but if my result set comes from a combination of a few databases, how can I create one general stored proc to do that? Thanks.
|||if the databases is on the same server, you can use the convention <database name>.dbo.<table name> for your query tables. If it's on another server, you might need to employ linked server functionalities of sql server.|||

I managed to come out with a stored proc to use the convention <database name>.dbo.<table name>. Below is the sample I used:
databases: db1, db2, db3
all has same table and column information as below:
table name: tabledb
column: data
the detail information for each of the databases are as follow:
db1: data values are 1,2,3
db2: data values are 4,5,6
db3: data values are 7,8,9
An example of the values I will have in the result set is 1,2,3,4,5,6,7,8,9 if I have parameters @.from = db1 and @.to = db3.
Below is the stored proc for it:

CREATE PROCEDURE [dbo].[GrabInfo]
@.from int, --1 as db1, 2 as db2, ...
@.to int
AS
BEGIN
SET NOCOUNT ON;

DECLARE @.sql nvarchar(MAX)
DECLARE @.temp nvarchar(17)
SET @.temp='db'+CAST(@.from AS nvarchar(1))+'.dbo.tabledb'
SELECT @.sql = 'SELECT data FROM '+@.temp
WHILE (@.from < @.to)
BEGIN
SET @.from = @.from + 1
SET @.temp = 'db' + CAST(@.from AS nvarchar(1)) + '.dbo.tabledb'
SELECT @.sql = @.sql + ' UNION SELECT data FROM '+@.temp
END
EXEC(@.sql)
RETURN
END

One more question I have, can it be done with View instead of Stored Proc?

Is this index redundant?

Let's say I have a table:
create table example (
id int,
createtime datetime,
name varchar(20))
go
create index ix_createtime on example(createtime)
go
create index ix_createtime_name on example(createtime, name)
go
Is the index ix_createtime redundant because the createtime column is
part of the composite index ix_createtime_name. If someone whee to
seach based only on the createtime column would the composite index be
as useful as the index on just the createtime column?
Thanks<pshroads@.gmail.com> wrote in message
news:1148056676.401254.153400@.y43g2000cwc.googlegroups.com...
> Is the index ix_createtime redundant because the createtime column is
> part of the composite index ix_createtime_name. If someone whee to
> seach based only on the createtime column would the composite index be
> as useful as the index on just the createtime column?
Yes, it probably is redundant. There are a few very limited edge cases
where it might not be -- for instance, if you're doing a lot of analysis
based on only a single column and every I/O counts -- but they are very few
and very far between. The composite index will certainly work for a query
based only on the createtime column.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--|||Index ix_createtime is leaner than index ix_createtime_name. As such,
it is slightly more useful. However, its advantage is not too
noticeable, and under most circumstances you can safely drop it. you
can figure it out yourself, run your own tests, such as
select * from example where createtime between '20060101' and
'20060202'
there might be ranges for which access via ix_createtime is still
cheaper than scanning the whole clustered index, but access via
ix_createtime_name is already more expensive than table scan.
Good luck!|||Unless the table is read-only, you also need to consider maintenance costs.
With the redundant index, you will have extra work to do for almost every
modification to the table, and you'll need to determine if that cost is
worth the small benefit of having the narrower index for some queries.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
<pshroads@.gmail.com> wrote in message
news:1148056676.401254.153400@.y43g2000cwc.googlegroups.com...
> Let's say I have a table:
> create table example (
> id int,
> createtime datetime,
> name varchar(20))
> go
> create index ix_createtime on example(createtime)
> go
> create index ix_createtime_name on example(createtime, name)
> go
> Is the index ix_createtime redundant because the createtime column is
> part of the composite index ix_createtime_name. If someone whee to
> seach based only on the createtime column would the composite index be
> as useful as the index on just the createtime column?
> Thanks
>|||Kalen,
I agree maintenance costs need to be taken in account. Yet I don't see
why you are calling the benefit of having a lean index "small" without
knowing much about the OP's system. I think it depends on the workload.
Suppose there is a query that runs very frequently. As such, small 10%
savings in its real execution costs may result in, say, 6% reduction of
overall workload for the whole system...|||Thanks. Am I correct in thinking that it's only redundant because
statistics are maintained on only the first column of an index? So if
the composite index had been on (name, createtime) then it would not be
redundant because the optimizer would be unlikely to choose that index
when searching on createtime because there would be no statistics?|||How will said "lean" index be used? I don't think dropping 20 bytes from an
index like this will result in "10% savings" in 99% of the cases, because
most likely a query will involve more than just the datetime column. Keep
in mind that bookmark lookups are much more expensive, in most cases, than
the additional I/Os that would be incurred reading the larger index pages.
We of course don't know what the clustering key is, or any other information
about the use, so this is all speculation, but unless this is a very
specialized situation I'd venture a guess that having both indexes is not
beneficial.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Alexander Kuznetsov" <AK_TIREDOFSPAM@.hotmail.COM> wrote in message
news:1148061719.429023.48180@.i40g2000cwc.googlegroups.com...
> Kalen,
> I agree maintenance costs need to be taken in account. Yet I don't see
> why you are calling the benefit of having a lean index "small" without
> knowing much about the OP's system. I think it depends on the workload.
> Suppose there is a query that runs very frequently. As such, small 10%
> savings in its real execution costs may result in, say, 6% reduction of
> overall workload for the whole system...
>|||well I'm not speaking in terms of 99% of the cases or 99.999% of the
case or whatever else. I am speaking about one particular system we
know almost nothing about - the OP's system. Suppose that system is
very simple and the primary goal of that simple system is to run
something like this:
select count(distinct id) from example where createtime between
@.fromdate and @.todate
as fast as possible. In that case the savings would be substantial, as
there would be no bookmark lookups. However rare, such cases do exist.
That's why I was trying to encourage the original poster to benchmark
by himself rather than rely on our advices. I think in our trade all
rules have exceptions.
That's all I was trying to say...|||On 19 May 2006 09:37:56 -0700, pshroads@.gmail.com wrote:

>Let's say I have a table:
>create table example (
> id int,
> createtime datetime,
> name varchar(20))
>go
>create index ix_createtime on example(createtime)
>go
>create index ix_createtime_name on example(createtime, name)
>go
>Is the index ix_createtime redundant because the createtime column is
>part of the composite index ix_createtime_name. If someone whee to
>seach based only on the createtime column would the composite index be
>as useful as the index on just the createtime column?
If there are a million rows with the same time, and you might have a
query "select * from example where createtime = @.t and name like
'foo%', then, um, is SQLServer smart enough to do its work on the
index in a case like that?
Only reason I can think of you might want that second.
Josh|||On 19 May 2006 11:06:19 -0700, pshroads@.gmail.com wrote:

>Thanks. Am I correct in thinking that it's only redundant because
>statistics are maintained on only the first column of an index? So if
>the composite index had been on (name, createtime) then it would not be
>redundant because the optimizer would be unlikely to choose that index
>when searching on createtime because there would be no statistics?
Hi pshroads,
You are right that the index on (createtime) would not be redundant if
the composite index had been on (name, createtime), but you're wrong
about the reason.
Suppose I give you a stack of paper with one-paper sized biographies of
all famous people of the last millenium, ordered by birthdate. I'll also
give you a much smaller stack of paper that includes just the name and
the page number(s) where a person with that name is described, sorted by
last name, then first name. And I give you another index that holds just
the first name and the pages where someone with that first name is
found.
What would your strategy be if I asked you for some details about
someone with first name "Kathlyn"?
Hugo Kornelis, SQL Server MVP

Is this index redundant?

Let's say I have a table:
create table example (
id int,
createtime datetime,
name varchar(20))
go
create index ix_createtime on example(createtime)
go
create index ix_createtime_name on example(createtime, name)
go
Is the index ix_createtime redundant because the createtime column is
part of the composite index ix_createtime_name. If someone whee to
seach based only on the createtime column would the composite index be
as useful as the index on just the createtime column?
Thanks<pshroads@.gmail.com> wrote in message
news:1148056676.401254.153400@.y43g2000cwc.googlegroups.com...
> Is the index ix_createtime redundant because the createtime column is
> part of the composite index ix_createtime_name. If someone whee to
> seach based only on the createtime column would the composite index be
> as useful as the index on just the createtime column?
Yes, it probably is redundant. There are a few very limited edge cases
where it might not be -- for instance, if you're doing a lot of analysis
based on only a single column and every I/O counts -- but they are very few
and very far between. The composite index will certainly work for a query
based only on the createtime column.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--|||Index ix_createtime is leaner than index ix_createtime_name. As such,
it is slightly more useful. However, its advantage is not too
noticeable, and under most circumstances you can safely drop it. you
can figure it out yourself, run your own tests, such as
select * from example where createtime between '20060101' and
'20060202'
there might be ranges for which access via ix_createtime is still
cheaper than scanning the whole clustered index, but access via
ix_createtime_name is already more expensive than table scan.
Good luck!|||Unless the table is read-only, you also need to consider maintenance costs.
With the redundant index, you will have extra work to do for almost every
modification to the table, and you'll need to determine if that cost is
worth the small benefit of having the narrower index for some queries.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
<pshroads@.gmail.com> wrote in message
news:1148056676.401254.153400@.y43g2000cwc.googlegroups.com...
> Let's say I have a table:
> create table example (
> id int,
> createtime datetime,
> name varchar(20))
> go
> create index ix_createtime on example(createtime)
> go
> create index ix_createtime_name on example(createtime, name)
> go
> Is the index ix_createtime redundant because the createtime column is
> part of the composite index ix_createtime_name. If someone whee to
> seach based only on the createtime column would the composite index be
> as useful as the index on just the createtime column?
> Thanks
>|||Kalen,
I agree maintenance costs need to be taken in account. Yet I don't see
why you are calling the benefit of having a lean index "small" without
knowing much about the OP's system. I think it depends on the workload.
Suppose there is a query that runs very frequently. As such, small 10%
savings in its real execution costs may result in, say, 6% reduction of
overall workload for the whole system...|||Thanks. Am I correct in thinking that it's only redundant because
statistics are maintained on only the first column of an index? So if
the composite index had been on (name, createtime) then it would not be
redundant because the optimizer would be unlikely to choose that index
when searching on createtime because there would be no statistics?|||How will said "lean" index be used? I don't think dropping 20 bytes from an
index like this will result in "10% savings" in 99% of the cases, because
most likely a query will involve more than just the datetime column. Keep
in mind that bookmark lookups are much more expensive, in most cases, than
the additional I/Os that would be incurred reading the larger index pages.
We of course don't know what the clustering key is, or any other information
about the use, so this is all speculation, but unless this is a very
specialized situation I'd venture a guess that having both indexes is not
beneficial.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Alexander Kuznetsov" <AK_TIREDOFSPAM@.hotmail.COM> wrote in message
news:1148061719.429023.48180@.i40g2000cwc.googlegroups.com...
> Kalen,
> I agree maintenance costs need to be taken in account. Yet I don't see
> why you are calling the benefit of having a lean index "small" without
> knowing much about the OP's system. I think it depends on the workload.
> Suppose there is a query that runs very frequently. As such, small 10%
> savings in its real execution costs may result in, say, 6% reduction of
> overall workload for the whole system...
>|||well I'm not speaking in terms of 99% of the cases or 99.999% of the
case or whatever else. I am speaking about one particular system we
know almost nothing about - the OP's system. Suppose that system is
very simple and the primary goal of that simple system is to run
something like this:
select count(distinct id) from example where createtime between
@.fromdate and @.todate
as fast as possible. In that case the savings would be substantial, as
there would be no bookmark lookups. However rare, such cases do exist.
That's why I was trying to encourage the original poster to benchmark
by himself rather than rely on our advices. I think in our trade all
rules have exceptions.
That's all I was trying to say...|||On 19 May 2006 09:37:56 -0700, pshroads@.gmail.com wrote:

>Let's say I have a table:
>create table example (
> id int,
> createtime datetime,
> name varchar(20))
>go
>create index ix_createtime on example(createtime)
>go
>create index ix_createtime_name on example(createtime, name)
>go
>Is the index ix_createtime redundant because the createtime column is
>part of the composite index ix_createtime_name. If someone whee to
>seach based only on the createtime column would the composite index be
>as useful as the index on just the createtime column?
If there are a million rows with the same time, and you might have a
query "select * from example where createtime = @.t and name like
'foo%', then, um, is SQLServer smart enough to do its work on the
index in a case like that?
Only reason I can think of you might want that second.
Josh|||On 19 May 2006 11:06:19 -0700, pshroads@.gmail.com wrote:

>Thanks. Am I correct in thinking that it's only redundant because
>statistics are maintained on only the first column of an index? So if
>the composite index had been on (name, createtime) then it would not be
>redundant because the optimizer would be unlikely to choose that index
>when searching on createtime because there would be no statistics?
Hi pshroads,
You are right that the index on (createtime) would not be redundant if
the composite index had been on (name, createtime), but you're wrong
about the reason.
Suppose I give you a stack of paper with one-paper sized biographies of
all famous people of the last millenium, ordered by birthdate. I'll also
give you a much smaller stack of paper that includes just the name and
the page number(s) where a person with that name is described, sorted by
last name, then first name. And I give you another index that holds just
the first name and the pages where someone with that first name is
found.
What would your strategy be if I asked you for some details about
someone with first name "Kathlyn"?
Hugo Kornelis, SQL Server MVPsql

Is this index redundant?

Let's say I have a table:
create table example (
id int,
createtime datetime,
name varchar(20))
go
create index ix_createtime on example(createtime)
go
create index ix_createtime_name on example(createtime, name)
go
Is the index ix_createtime redundant because the createtime column is
part of the composite index ix_createtime_name. If someone whee to
seach based only on the createtime column would the composite index be
as useful as the index on just the createtime column?
Thanks<pshroads@.gmail.com> wrote in message
news:1148056676.401254.153400@.y43g2000cwc.googlegroups.com...
> Is the index ix_createtime redundant because the createtime column is
> part of the composite index ix_createtime_name. If someone whee to
> seach based only on the createtime column would the composite index be
> as useful as the index on just the createtime column?
Yes, it probably is redundant. There are a few very limited edge cases
where it might not be -- for instance, if you're doing a lot of analysis
based on only a single column and every I/O counts -- but they are very few
and very far between. The composite index will certainly work for a query
based only on the createtime column.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--|||Index ix_createtime is leaner than index ix_createtime_name. As such,
it is slightly more useful. However, its advantage is not too
noticeable, and under most circumstances you can safely drop it. you
can figure it out yourself, run your own tests, such as
select * from example where createtime between '20060101' and
'20060202'
there might be ranges for which access via ix_createtime is still
cheaper than scanning the whole clustered index, but access via
ix_createtime_name is already more expensive than table scan.
Good luck!|||Unless the table is read-only, you also need to consider maintenance costs.
With the redundant index, you will have extra work to do for almost every
modification to the table, and you'll need to determine if that cost is
worth the small benefit of having the narrower index for some queries.
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
<pshroads@.gmail.com> wrote in message
news:1148056676.401254.153400@.y43g2000cwc.googlegroups.com...
> Let's say I have a table:
> create table example (
> id int,
> createtime datetime,
> name varchar(20))
> go
> create index ix_createtime on example(createtime)
> go
> create index ix_createtime_name on example(createtime, name)
> go
> Is the index ix_createtime redundant because the createtime column is
> part of the composite index ix_createtime_name. If someone whee to
> seach based only on the createtime column would the composite index be
> as useful as the index on just the createtime column?
> Thanks
>|||Kalen,
I agree maintenance costs need to be taken in account. Yet I don't see
why you are calling the benefit of having a lean index "small" without
knowing much about the OP's system. I think it depends on the workload.
Suppose there is a query that runs very frequently. As such, small 10%
savings in its real execution costs may result in, say, 6% reduction of
overall workload for the whole system...|||Thanks. Am I correct in thinking that it's only redundant because
statistics are maintained on only the first column of an index? So if
the composite index had been on (name, createtime) then it would not be
redundant because the optimizer would be unlikely to choose that index
when searching on createtime because there would be no statistics?|||How will said "lean" index be used? I don't think dropping 20 bytes from an
index like this will result in "10% savings" in 99% of the cases, because
most likely a query will involve more than just the datetime column. Keep
in mind that bookmark lookups are much more expensive, in most cases, than
the additional I/Os that would be incurred reading the larger index pages.
We of course don't know what the clustering key is, or any other information
about the use, so this is all speculation, but unless this is a very
specialized situation I'd venture a guess that having both indexes is not
beneficial.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Alexander Kuznetsov" <AK_TIREDOFSPAM@.hotmail.COM> wrote in message
news:1148061719.429023.48180@.i40g2000cwc.googlegroups.com...
> Kalen,
> I agree maintenance costs need to be taken in account. Yet I don't see
> why you are calling the benefit of having a lean index "small" without
> knowing much about the OP's system. I think it depends on the workload.
> Suppose there is a query that runs very frequently. As such, small 10%
> savings in its real execution costs may result in, say, 6% reduction of
> overall workload for the whole system...
>|||well I'm not speaking in terms of 99% of the cases or 99.999% of the
case or whatever else. I am speaking about one particular system we
know almost nothing about - the OP's system. Suppose that system is
very simple and the primary goal of that simple system is to run
something like this:
select count(distinct id) from example where createtime between
@.fromdate and @.todate
as fast as possible. In that case the savings would be substantial, as
there would be no bookmark lookups. However rare, such cases do exist.
That's why I was trying to encourage the original poster to benchmark
by himself rather than rely on our advices. I think in our trade all
rules have exceptions.
That's all I was trying to say...|||On 19 May 2006 09:37:56 -0700, pshroads@.gmail.com wrote:
>Let's say I have a table:
>create table example (
> id int,
> createtime datetime,
> name varchar(20))
>go
>create index ix_createtime on example(createtime)
>go
>create index ix_createtime_name on example(createtime, name)
>go
>Is the index ix_createtime redundant because the createtime column is
>part of the composite index ix_createtime_name. If someone whee to
>seach based only on the createtime column would the composite index be
>as useful as the index on just the createtime column?
If there are a million rows with the same time, and you might have a
query "select * from example where createtime = @.t and name like
'foo%', then, um, is SQLServer smart enough to do its work on the
index in a case like that?
Only reason I can think of you might want that second.
Josh|||On 19 May 2006 11:06:19 -0700, pshroads@.gmail.com wrote:
>Thanks. Am I correct in thinking that it's only redundant because
>statistics are maintained on only the first column of an index? So if
>the composite index had been on (name, createtime) then it would not be
>redundant because the optimizer would be unlikely to choose that index
>when searching on createtime because there would be no statistics?
Hi pshroads,
You are right that the index on (createtime) would not be redundant if
the composite index had been on (name, createtime), but you're wrong
about the reason.
Suppose I give you a stack of paper with one-paper sized biographies of
all famous people of the last millenium, ordered by birthdate. I'll also
give you a much smaller stack of paper that includes just the name and
the page number(s) where a person with that name is described, sorted by
last name, then first name. And I give you another index that holds just
the first name and the pages where someone with that first name is
found.
What would your strategy be if I asked you for some details about
someone with first name "Kathlyn"?
--
Hugo Kornelis, SQL Server MVP|||I suppose it would be to look up the name Kathlyn in the third index
and get the page number and then go to that page number in the large
stack. Perhaps I'm being dense but I still need the reason explained :)
Thanks!|||On 19 May 2006 16:12:32 -0700, pshroads@.gmail.com wrote:
>I suppose it would be to look up the name Kathlyn in the third index
>and get the page number and then go to that page number in the large
>stack. Perhaps I'm being dense but I still need the reason explained :)
>Thanks!
Hi pshroads,
The first stack of paper equates to the table. The second stack equates
to an index on (last name, first name). And the third stack equates to
an index on only (first name). You'll use the index on (first name) to
find all people with first name Kathlyn, and you'll use the index on
(last name, first name) to find all people with last name Harrison, or
all people named George Washington. Both indexes are useful, without
being redundant.
Now replace the index on (first name) with an index on (last name) [i.e.
a list of last names and page numbers where these last names are
mentioned]. You'll still use the (last name, first name) index for
finding George Washington. You could use the new (last name) index to
locate every Mr or Mrs Harrison, but you can still do that with the
(last name, first name) index as well. And when looking for Kathlyn,
your only option is to sit down and flip each and every page of the
biggest stack of papers (the infamous table scan). So now, you have an
index that is only usable for a kind of search that can also be done
with the other index - clearly the (last name) index is redundant.
Database searches work exactly the same as yoour searches in those
stacks of paper. The only difference is that the computer turns the
pages somewhat faster. :-)
--
Hugo Kornelis, SQL Server MVP

Is there such a tool?

> Is there a tool, which could register all changes in a database? For
> example, a program connects to the database and makes some changes. The
tool
> would give as the results: what tables were affected and even what rows
were
> inserted, modified or deleted.
Have a look on our MSSQL LogManager.
http://www.upscene.com/products/audit/mssqllm_main.htm
Best Regards,
Thomas Steinmaurer
LogManager Serie - Logging/Auditing Suites supporting
InterBase, Firebird, Advantage Database and MS SQL Server
Upscene Productions
http://www.upscene.com
Vycka wrote:
> Is there a tool, which could register all changes in a database? For
> example, a program connects to the database and makes some changes.
> The tool would give as the results: what tables were affected and
> even what rows were inserted, modified or deleted.
OmniAudit does exactly that. http://www.krell-software.com
Steve Troxell
Krell Software
|||This tool is pretty handy, but...
I installed it to my computer, then I did "prepare database for logging".
After that my program that uses the database got out of order. It started to
behave unpredictably so I decided not to use the tool and I did "unprepare
database from logging". In my opinion after doing this the database should
become clean of this program. But unfortunately now my database program
stopped working at all throwing error messages saying that "could not find
stored procedure dbo.P_INSERTINTOLOGTABLE". What is this? As far as I can
see it is related with triggers that were not deleted by that tool (MS SQL
LogManager).
Please Explain me, how to make my program (and database) work smoothly
exactly as it was?
Thank you for your help
"Thomas Steinmaurer" <t.steinmaurer@._no_upscene_spam_.com> wrote in message
news:#$Vsc2THEHA.1944@.TK2MSFTNGP11.phx.gbl...
> tool
> were
> Have a look on our MSSQL LogManager.
> http://www.upscene.com/products/audit/mssqllm_main.htm
>
> Best Regards,
> Thomas Steinmaurer
> LogManager Serie - Logging/Auditing Suites supporting
> InterBase, Firebird, Advantage Database and MS SQL Server
> Upscene Productions
> http://www.upscene.com
>
|||Hi,

> This tool is pretty handy, but...
> I installed it to my computer, then I did "prepare database for logging".
> After that my program that uses the database got out of order. It started
to
> behave unpredictably so I decided not to use the tool and I did "unprepare
> database from logging".
Can you elaborate what you mean with "got out of order", please?

> In my opinion after doing this the database should
> become clean of this program.
Right.

> But unfortunately now my database program
> stopped working at all throwing error messages saying that "could not find
> stored procedure dbo.P_INSERTINTOLOGTABLE". What is this? As far as I can
> see it is related with triggers that were not deleted by that tool (MS SQL
> LogManager).
The Unprepare-Wizard should take care of that. And so far, it did.
Do you see any tables/views starting with MSSQLLM$... at all?
If the logging trigger weren't remove, then you might find them via
the following name template (btw, that depends, what trigger naming
conventions you've used during the Prepare).
For example:
TRI_<table_name>_L_<event_type>
A concrete example would be:
TRI_ADDRESS_L_D or
TRI_ADDRESS_L_I or
TRI_ADDRESS_L_U
What Microsoft SQL Server are you using?
Best Regards,
Thomas Steinmaurer
LogManager Serie - Logging/Auditing Suites supporting
InterBase, Firebird, Advantage Database and MS SQL Server
Upscene Productions
http://www.upscene.com
|||"Thomas Steinmaurer" <t.steinmaurer@._no_upscene_spam_.com> wrote in message
news:OQOcEEZHEHA.3564@.TK2MSFTNGP09.phx.gbl...
> Hi,
logging".
started
> to
"unprepare
> Can you elaborate what you mean with "got out of order", please?
My program is a very complex document and contact managemet program. So when
I tried to add a new document, or add a new contact to the database in stead
of getting common notification window I got another program window
absolutely out of point. It is difficult to explain everything when you
don't know my program. I didn't made this program, it is a big commercial
program developed by a company. It was made on Visual Studio ASP.NET.
>
> Right.
find
can
SQL
> The Unprepare-Wizard should take care of that. And so far, it did.
> Do you see any tables/views starting with MSSQLLM$... at all?
Now three tables and one view was dropped after "unprepare" procedure.
But in SysObjects table I found six triggers related with two tables from my
DB. I Dropped them by writing seperate querries. Now everything works fine -
no error messages, no unaccountable program behaviour.
I appreciate your help, thank you.

> If the logging trigger weren't remove, then you might find them via
> the following name template (btw, that depends, what trigger naming
> conventions you've used during the Prepare).
> For example:
> TRI_<table_name>_L_<event_type>
> A concrete example would be:
> TRI_ADDRESS_L_D or
> TRI_ADDRESS_L_I or
> TRI_ADDRESS_L_U
> What Microsoft SQL Server are you using?
I'm Using MS SQL Server 2000 08.00.0760

>
> Best Regards,
> Thomas Steinmaurer
> LogManager Serie - Logging/Auditing Suites supporting
> InterBase, Firebird, Advantage Database and MS SQL Server
> Upscene Productions
> http://www.upscene.com
>
|||> > > This tool is pretty handy, but...
> logging".
> started
> "unprepare
> My program is a very complex document and contact managemet program. So
when
> I tried to add a new document, or add a new contact to the database in
stead
> of getting common notification window I got another program window
> absolutely out of point.
I guess, you don't have an error message or a screenshoot of that
window, do you? I'm just trying to fix a possible issue, if there is
anything to fix at all.

> Now three tables and one view was dropped after "unprepare" procedure.
I did some extensive testing, and I couldn't break the Unprepare
process in any way. Existing log trigger get removed automatically,
before the Unprepare wizard removes logging metadata. It works
here, as expected.

> But in SysObjects table I found six triggers related with two tables from
my
> DB. I Dropped them by writing seperate querries. Now everything works
fine -
> no error messages, no unaccountable program behaviour.
> I appreciate your help, thank you.
You are welcome!
Best Regards,
Thomas Steinmaurer
LogManager Serie - Logging/Auditing Suites supporting
InterBase, Firebird, Advantage Database and MS SQL Server
Upscene Productions
http://www.upscene.com

Is there something similar to SQL 2005 row_number() in sql mobile?

Hello,

I am wondering if somone could provide me with a sample SELECT for an
auto number query. For example I have a table called People with two columns first_name, and last_name. There isn't a unique id to correspond with the table but would like to dynamically make one during the return of the query.

I know this is possible in the full version of sql 2005 using

SELECT ROW_NUMBER() OVER (ORDER BY last_name), first_name, last_name from People

But within sql mobile is there a way?
So if there was 5 rows in the table it would return

1 John Alpha
2 John Beta
3 John Cat
4 John Delta
5 John Echo

John

No SQL Server Mobile/Everywhere Edition does not support Row_Number() and there is no similar function too :(

Thanks,

Laxmi

Is there anything similar in TSQL to oracle code "SEQUENCE.NEXTVAL

Hello,
I trying to do a SELECT that will show me the next value automatically
everytime.
for example in Oracle, I can do
Select SEQUENCE.NEXTVAL from Dual
1
Select SEQUENCE.NEXTVAL from Dual
2
Select SEQUENCE.NEXTVAL from Dual
3
with Oracle there is a function that allows me to use auto increment value
without specifying for insert into the table.
Can I do the same with a TSQL? Anything in TSQL close to this function.
Please help. Thanks again.No there isn't a function that can do this. But can you tell what is the
actual requirement, so that we can give you an alternative
--
"sqlapprentice" wrote:

> Hello,
> I trying to do a SELECT that will show me the next value automatically
> everytime.
> for example in Oracle, I can do
> Select SEQUENCE.NEXTVAL from Dual
> 1
> Select SEQUENCE.NEXTVAL from Dual
> 2
> Select SEQUENCE.NEXTVAL from Dual
> 3
> with Oracle there is a function that allows me to use auto increment value
> without specifying for insert into the table.
> Can I do the same with a TSQL? Anything in TSQL close to this function.
> Please help. Thanks again.|||You can look into using IDENTITY, but it really depends on what you are
trying to accomplish.
"sqlapprentice" <sqlapprentice@.discussions.microsoft.com> wrote in message
news:C5A27B55-64ED-468F-A61B-5DC4715687BE@.microsoft.com...
> Hello,
> I trying to do a SELECT that will show me the next value automatically
> everytime.
> for example in Oracle, I can do
> Select SEQUENCE.NEXTVAL from Dual
> 1
> Select SEQUENCE.NEXTVAL from Dual
> 2
> Select SEQUENCE.NEXTVAL from Dual
> 3
> with Oracle there is a function that allows me to use auto increment value
> without specifying for insert into the table.
> Can I do the same with a TSQL? Anything in TSQL close to this function.
> Please help. Thanks again.|||in SQL server, one uses the IDENTITY attribute of a column to specify
what would be similar to an Oracle Sequence.
CREATE TABLE mytable(
entryid INT IDENTITY(1,1),
entrydata VARCHAR(100)
)
Use the following to get the current value of the "Sequence"
DBCC CHECKIDENT ('owner.tablename')
in BOL... look at the CREATE TABLE SYNTAX for IDENTITY.
and also check out DBCC CHECKIDENT.|||"Johnny D" <john.dacosta@.gmail.com> wrote in message
news:1146499671.388074.220710@.i39g2000cwa.googlegroups.com...
> in SQL server, one uses the IDENTITY attribute of a column to specify
> what would be similar to an Oracle Sequence.
> CREATE TABLE mytable(
> entryid INT IDENTITY(1,1),
> entrydata VARCHAR(100)
> )
> Use the following to get the current value of the "Sequence"
> DBCC CHECKIDENT ('owner.tablename')
> in BOL... look at the CREATE TABLE SYNTAX for IDENTITY.
> and also check out DBCC CHECKIDENT.
>
besides DBCC CHECKINDENT, you can also use
select ident_current('table')
or
select max(identitycol) from table
ident_current() is the preferred method of the two.
dean

Is there any way better than XP_CMDSHELL?

Hi ..
I want to Write in files or read from files
for example i have My_File.txt . i need a syntax and i want to call this syntax in my Store procedure and this syntax write forexample " Hello Word " in My_File.txt .
and i want another syntax that read from My_File.txt forexample "Word" from My_File.txt . what are those syntaxes do that ??

Is there any way better than XP_CMDSHELL for writing in or reading from MyFile.txt ??

thanksYou could BCP the SQL file into a temporary table and then execute the code through dynamic SQL, but that is pretty round-a-bout.

Is there an example to accomplish the following?

I am new to SSIS and I have been playing with it for the last couple of days. I would like to know if anybody can give me some feedback or point me in the right direction ( best by looking at an example ) to acomplish the following :

I have a table called Policy ( free foxpro table ). This table contain policy insurance information and also customer information. I would like to upload and normalize this table to an existing "PolicyAttributes" SQL Server 2005 table.

Also I would like to create a new record in the Customer table if there exist a customer in the policy Table, thus separating the customer information from Policy table ( normalizing the table ).

The policy table has a field called CustomerName_1 if it is empty then this policy does not have any cutomer, but if it has a name then I would like to insert a new record into the SQL Server Customer table and then get the unique number from the newly created record in Customer table and update it in the SQL Server Policy table ( let say CustomerID ). This way I can keep the relationship PolicyAttibutes >Customer on the CustomerID field.

So basically here are the logical steps:

1.- Read Policy records from foxpro table

2.- insert record to SQL Server PolicyAttributes table

3.- for each customer name in the foxpro Policy table insert a new record in the SQL Server Customer table.

4.- Get the unique number from the new Customer record ( Primary Key of Customer record)

and update this number in the PolicyAttibutes field called CustomerID ( see above step 2 ).

This way I can keep my PolicyAttributes->Customer relationship.

Also the foxpro Policy table can contain more than one customer. It can have up to 4 customers.

So if there are 4 customers then I will have to insert 4 records in the customer table.

CustomerName_1

CustomerName_2

CustomerName_3

CustomerName_4

Any help is greatly appreciated!!!!!!!!!!!!!!!!!!!!!!!!!!!!.

Thanks,

Sergio

I yhink the simplest way you can resolve your problem is:
1. With Import/Export Wizard copy foxpro records in and SQL table named SQL_Policy. Save the procedure as a package in a job to run time to time.
2.Create an "on insert" trigger on SQL_Policy and do what you want to do. Here you can use try/catch for errors, cursors etc. all in T-SQL

This procedure thinking you are new in SSIS.|||

Sergio:

I am not sure that you are going to find an example specific to your situation. If you are new to SSIS but have done programming before I would start of by first creating a logical flow of your data in Visio. You would then know what kind of transforms you would need. You can then look at the SSIS pallette and choose the ones appropriate for your situation. If you need help in using a particular transform use Books Online to get more information.

I apologize if I am not giving you the specific solution you are looking for but, as I said, every flow is going to be different.

is there an error handler in sql stored procedures?

is there an error handler in sql stored procedures? For example if i want to do a drop something and there is a lock, i want to try return if it droped it or not and if not then try again latererror handling in sql 2000 is pretty poor. basically you have to check @.@.ERROR after every statement that could possibly fail. The situation in 2005 is much better since there you can use try/catch.

for error handling on 2000:
http://www.sommarskog.se/error-handling-I.html
http://www.sommarskog.se/error-handling-II.html

for try/catch:
http://msdn2.microsoft.com/en-us/library/ms175976.aspx