Asp.net / sql help: create

Showing posts with label create. Show all posts

Tuesday, March 27, 2012

Attach db failure cause log not primary

Hi Everybody,

I need URGENT help with this. Because of space problem my collegue create a second log file on another drive. I wanted to clean the thing, made a detach, renamed the ldf into old, but now it won't attach anymore, I get the following messages:

EXEC sp_attach_db @.dbname='TARIFINFO_GEN',
@.filename1 = 'F:\Data\TARIFINFO_GEN_Data.mdf'

Server: Msg 5171 F:\Data\TARIFINFO_GEN_log.ldf is not a primary database file
Server: Msg 1813 Device activation error the physical file name 'F:\Data\TARIFINFO_GEN_log.ldf' may be incorrect

Do someone know what to do or where to look for a solution. Many thanks in advance, Isabelle.Try sp_attach_single_file_db 'TARIFINFO_GEN',
'F:\Data\TARIFINFO_GEN_Data.mdf'

Also, in many instances to clean logs you should be able to run something like:

backup log TARIFINFO_GEN with truncate_only

and then a dbcc shrinkfile.

Instead of detaching and re-attaching.

Eric

Thursday, March 22, 2012

Asynchronous / non-blocking trigger

Jon
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:5D57525F-1DED-4A3B-B00B-60342A369F5A@.microsoft.com...
> Triggers always execute synchronously in the context of a transaction. If
> you need to invoke an asynchronous process from within a trigger, consider
> using Service Broker. Asynchronous triggers are exactly the functionality
> that Service Broker provides. See the Books Online for details.
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Jon Davis" <jon@.REMOVE.ME.PLEASE.jondavis.net> wrote in message
> news:%23jWadvEjHHA.4552@.TK2MSFTNGP05.phx.gbl...
>
|||> So, then, no, because Service Broker doesn't retain the "inserted" and
> "deleted" in-memory tables.
Service Broker doesn't need access to inserted/deleted directly. The
trigger can pass insert data into the SB queue the from the inserted/deleted
pseudo tables. Here's an example:
http://www.dotnetfun.com/articles/sql/sql2005/SQL2005CreatingTSQLAsynchronousTriggers.aspx
Hope this helps.
Dan Guzman
SQL Server MVP
"Jon Davis" <jon@.REMOVE.ME.PLEASE.jondavis.net> wrote in message
news:e3kWlPNjHHA.4520@.TK2MSFTNGP02.phx.gbl...
> So, then, no, because Service Broker doesn't retain the "inserted" and
> "deleted" in-memory tables.
> Thanks anyway.

> Jon
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:5D57525F-1DED-4A3B-B00B-60342A369F5A@.microsoft.com...
>

Asynchronous / non-blocking trigger

Is it possible to create an asynchronous / non-blocking trigger in SQL
Server 2005 that will allow operations to continue asynchronously while the
trigger can still read the in-memory "inserted" and "deleted" virtual
tables?
Thanks,
JonTriggers always execute synchronously in the context of a transaction. If
you need to invoke an asynchronous process from within a trigger, consider
using Service Broker. Asynchronous triggers are exactly the functionality
that Service Broker provides. See the Books Online for details.
Hope this helps.
Dan Guzman
SQL Server MVP
"Jon Davis" <jon@.REMOVE.ME.PLEASE.jondavis.net> wrote in message
news:%23jWadvEjHHA.4552@.TK2MSFTNGP05.phx.gbl...
> Is it possible to create an asynchronous / non-blocking trigger in SQL
> Server 2005 that will allow operations to continue asynchronously while
> the trigger can still read the in-memory "inserted" and "deleted" virtual
> tables?
> Thanks,
> Jon
>|||So, then, no, because Service Broker doesn't retain the "inserted" and
"deleted" in-memory tables.
Thanks anyway. :)
Jon
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:5D57525F-1DED-4A3B-B00B-60342A369F5A@.microsoft.com...
> Triggers always execute synchronously in the context of a transaction. If
> you need to invoke an asynchronous process from within a trigger, consider
> using Service Broker. Asynchronous triggers are exactly the functionality
> that Service Broker provides. See the Books Online for details.
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Jon Davis" <jon@.REMOVE.ME.PLEASE.jondavis.net> wrote in message
> news:%23jWadvEjHHA.4552@.TK2MSFTNGP05.phx.gbl...
>> Is it possible to create an asynchronous / non-blocking trigger in SQL
>> Server 2005 that will allow operations to continue asynchronously while
>> the trigger can still read the in-memory "inserted" and "deleted" virtual
>> tables?
>> Thanks,
>> Jon
>|||> So, then, no, because Service Broker doesn't retain the "inserted" and
> "deleted" in-memory tables.
Service Broker doesn't need access to inserted/deleted directly. The
trigger can pass insert data into the SB queue the from the inserted/deleted
pseudo tables. Here's an example:
http://www.dotnetfun.com/articles/sql/sql2005/SQL2005CreatingTSQLAsynchronousTriggers.aspx
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Jon Davis" <jon@.REMOVE.ME.PLEASE.jondavis.net> wrote in message
news:e3kWlPNjHHA.4520@.TK2MSFTNGP02.phx.gbl...
> So, then, no, because Service Broker doesn't retain the "inserted" and
> "deleted" in-memory tables.
> Thanks anyway. :)
> Jon
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:5D57525F-1DED-4A3B-B00B-60342A369F5A@.microsoft.com...
>> Triggers always execute synchronously in the context of a transaction.
>> If you need to invoke an asynchronous process from within a trigger,
>> consider using Service Broker. Asynchronous triggers are exactly the
>> functionality that Service Broker provides. See the Books Online for
>> details.
>>
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Jon Davis" <jon@.REMOVE.ME.PLEASE.jondavis.net> wrote in message
>> news:%23jWadvEjHHA.4552@.TK2MSFTNGP05.phx.gbl...
>> Is it possible to create an asynchronous / non-blocking trigger in SQL
>> Server 2005 that will allow operations to continue asynchronously while
>> the trigger can still read the in-memory "inserted" and "deleted"
>> virtual tables?
>> Thanks,
>> Jon
>>
>

Asynchronous / non-blocking trigger

Jon
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:5D57525F-1DED-4A3B-B00B-60342A369F5A@.microsoft.com...
> Triggers always execute synchronously in the context of a transaction. If
> you need to invoke an asynchronous process from within a trigger, consider
> using Service Broker. Asynchronous triggers are exactly the functionality
> that Service Broker provides. See the Books Online for details.
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Jon Davis" <jon@.REMOVE.ME.PLEASE.jondavis.net> wrote in message
> news:%23jWadvEjHHA.4552@.TK2MSFTNGP05.phx.gbl...
>|||> So, then, no, because Service Broker doesn't retain the "inserted" and
> "deleted" in-memory tables.
Service Broker doesn't need access to inserted/deleted directly. The
trigger can pass insert data into the SB queue the from the inserted/deleted
pseudo tables. Here's an example:
http://www.dotnetfun.com/articles/s...br />
ers.aspx
Hope this helps.
Dan Guzman
SQL Server MVP
"Jon Davis" <jon@.REMOVE.ME.PLEASE.jondavis.net> wrote in message
news:e3kWlPNjHHA.4520@.TK2MSFTNGP02.phx.gbl...
> So, then, no, because Service Broker doesn't retain the "inserted" and
> "deleted" in-memory tables.
> Thanks anyway.

> Jon
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:5D57525F-1DED-4A3B-B00B-60342A369F5A@.microsoft.com...
>

Tuesday, March 20, 2012

Assumptions on Indexing

I create the following:
CREATE TABLE t1(c1 INT, c2 INT, c3 INT)
CREATE CLUSTERED INDEX icl ON t1(c3)
CREATE NONCLUSTERED INDEX incl ON t1(C1, c2)
When I perform a DBCC_SHOW_STATISTICS(t1, incl) the "Columns" column shows
the following columns:
c1, c2, c3
ASSUMPTION #1: I assume the reason for this is that clustered index columns
(in this case, c3) are appended to the end of nonclustered index columns as
a
row locator.
Why then, if the above assumption [ASSUMPTION #1] is true, when I perfor
m the
following:
CREATE NONCLUSTERED INDEX incl ON t1(C1, c3, c2) --Take note of the non-
sequential column order
WITH DROP_EXISTING
When I perform a DBCC_SHOW_STATISTICS(t1, incl) the "Columns" column shows
the following columns:
c1, c3, c2
ASSUMPTION #2: Based upon Assumption #1, I would expect to see the clustered
index column of c3 appended to the end of the nonclustered index column,
meaning I expected to see c1, c3, c2, c3. Since that was not the case, I
assume the reason the clustered index was not appended was due to its [t
he
clustered index] being included in the nonclustered index, and appending
column c3 [the clustered index column] to the nonclustered index in
DBCC_SHOW_STATISTICS would be redundant.
Are the above assumptions true?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200604/1> Are the above assumptions true?
Yes and yes.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"cbrichards via droptable.com" <u3288@.uwe> wrote in message news:5e86871f6f516@.uwe...[vbcol
=seagreen]
>I create the following:
> CREATE TABLE t1(c1 INT, c2 INT, c3 INT)
> CREATE CLUSTERED INDEX icl ON t1(c3)
> CREATE NONCLUSTERED INDEX incl ON t1(C1, c2)
> When I perform a DBCC_SHOW_STATISTICS(t1, incl) the "Columns" column shows
> the following columns:
> c1, c2, c3
> ASSUMPTION #1: I assume the reason for this is that clustered index column
s
> (in this case, c3) are appended to the end of nonclustered index columns a
s a
> row locator.
> Why then, if the above assumption [ASSUMPTION #1] is true, when I perf
orm the
> following:
> CREATE NONCLUSTERED INDEX incl ON t1(C1, c3, c2) --Take note of the non-
> sequential column order
> WITH DROP_EXISTING
> When I perform a DBCC_SHOW_STATISTICS(t1, incl) the "Columns" column shows
> the following columns:
> c1, c3, c2
> ASSUMPTION #2: Based upon Assumption #1, I would expect to see the cluster
ed
> index column of c3 appended to the end of the nonclustered index column,
> meaning I expected to see c1, c3, c2, c3. Since that was not the case, I
> assume the reason the clustered index was not appended was due to its [
;the
> clustered index] being included in the nonclustered index, and appending
> column c3 [the clustered index column] to the nonclustered index in
> DBCC_SHOW_STATISTICS would be redundant.
> Are the above assumptions true?
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200604/1[/vbcol]|||Yep. The clustered key(s) is always included in the nonclustered, but it
doesn't always have to be the right-most column.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:5e86871f6f516@.uwe...
>I create the following:
> CREATE TABLE t1(c1 INT, c2 INT, c3 INT)
> CREATE CLUSTERED INDEX icl ON t1(c3)
> CREATE NONCLUSTERED INDEX incl ON t1(C1, c2)
> When I perform a DBCC_SHOW_STATISTICS(t1, incl) the "Columns" column shows
> the following columns:
> c1, c2, c3
> ASSUMPTION #1: I assume the reason for this is that clustered index
> columns
> (in this case, c3) are appended to the end of nonclustered index columns
> as a
> row locator.
> Why then, if the above assumption [ASSUMPTION #1] is true, when I perf
orm
> the
> following:
> CREATE NONCLUSTERED INDEX incl ON t1(C1, c3, c2) --Take note of the non-
> sequential column order
> WITH DROP_EXISTING
> When I perform a DBCC_SHOW_STATISTICS(t1, incl) the "Columns" column shows
> the following columns:
> c1, c3, c2
> ASSUMPTION #2: Based upon Assumption #1, I would expect to see the
> clustered
> index column of c3 appended to the end of the nonclustered index column,
> meaning I expected to see c1, c3, c2, c3. Since that was not the case, I
> assume the reason the clustered index was not appended was due to its [
;the
> clustered index] being included in the nonclustered index, and appending
> column c3 [the clustered index column] to the nonclustered index in
> DBCC_SHOW_STATISTICS would be redundant.
> Are the above assumptions true?
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200604/1|||sort of.
clustered indexes are weird. they physically sort the data, and then
use the columns as a key to find the relevant rows. all of this is
inherently inefficient.
non clustered indexes use data positions in the table to locate rows.
well, if there is a clustered index on the table, then those data
positions are determined by the clustered column.
so, if we create a non clustered index on the table, the engine MUST
HAVE the data elements from the clustered column in order to find the
correct row.
to see how this all works, and make it real in your head, right down
some 5 rows of examples, then create how the index would really work.

Assumptions on Indexing

I create the following:
CREATE TABLE t1(c1 INT, c2 INT, c3 INT)
CREATE CLUSTERED INDEX icl ON t1(c3)
CREATE NONCLUSTERED INDEX incl ON t1(C1, c2)
When I perform a DBCC_SHOW_STATISTICS(t1, incl) the "Columns" column shows
the following columns:
c1, c2, c3
ASSUMPTION #1: I assume the reason for this is that clustered index columns
(in this case, c3) are appended to the end of nonclustered index columns as a
row locator.
Why then, if the above assumption [ASSUMPTION #1] is true, when I perform the
following:
CREATE NONCLUSTERED INDEX incl ON t1(C1, c3, c2) --Take note of the non-
sequential column order
WITH DROP_EXISTING
When I perform a DBCC_SHOW_STATISTICS(t1, incl) the "Columns" column shows
the following columns:
c1, c3, c2
ASSUMPTION #2: Based upon Assumption #1, I would expect to see the clustered
index column of c3 appended to the end of the nonclustered index column,
meaning I expected to see c1, c3, c2, c3. Since that was not the case, I
assume the reason the clustered index was not appended was due to its [the
clustered index] being included in the nonclustered index, and appending
column c3 [the clustered index column] to the nonclustered index in
DBCC_SHOW_STATISTICS would be redundant.
Are the above assumptions true?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200604/1> Are the above assumptions true?
Yes and yes.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message news:5e86871f6f516@.uwe...
>I create the following:
> CREATE TABLE t1(c1 INT, c2 INT, c3 INT)
> CREATE CLUSTERED INDEX icl ON t1(c3)
> CREATE NONCLUSTERED INDEX incl ON t1(C1, c2)
> When I perform a DBCC_SHOW_STATISTICS(t1, incl) the "Columns" column shows
> the following columns:
> c1, c2, c3
> ASSUMPTION #1: I assume the reason for this is that clustered index columns
> (in this case, c3) are appended to the end of nonclustered index columns as a
> row locator.
> Why then, if the above assumption [ASSUMPTION #1] is true, when I perform the
> following:
> CREATE NONCLUSTERED INDEX incl ON t1(C1, c3, c2) --Take note of the non-
> sequential column order
> WITH DROP_EXISTING
> When I perform a DBCC_SHOW_STATISTICS(t1, incl) the "Columns" column shows
> the following columns:
> c1, c3, c2
> ASSUMPTION #2: Based upon Assumption #1, I would expect to see the clustered
> index column of c3 appended to the end of the nonclustered index column,
> meaning I expected to see c1, c3, c2, c3. Since that was not the case, I
> assume the reason the clustered index was not appended was due to its [the
> clustered index] being included in the nonclustered index, and appending
> column c3 [the clustered index column] to the nonclustered index in
> DBCC_SHOW_STATISTICS would be redundant.
> Are the above assumptions true?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200604/1|||Yep. The clustered key(s) is always included in the nonclustered, but it
doesn't always have to be the right-most column.
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:5e86871f6f516@.uwe...
>I create the following:
> CREATE TABLE t1(c1 INT, c2 INT, c3 INT)
> CREATE CLUSTERED INDEX icl ON t1(c3)
> CREATE NONCLUSTERED INDEX incl ON t1(C1, c2)
> When I perform a DBCC_SHOW_STATISTICS(t1, incl) the "Columns" column shows
> the following columns:
> c1, c2, c3
> ASSUMPTION #1: I assume the reason for this is that clustered index
> columns
> (in this case, c3) are appended to the end of nonclustered index columns
> as a
> row locator.
> Why then, if the above assumption [ASSUMPTION #1] is true, when I perform
> the
> following:
> CREATE NONCLUSTERED INDEX incl ON t1(C1, c3, c2) --Take note of the non-
> sequential column order
> WITH DROP_EXISTING
> When I perform a DBCC_SHOW_STATISTICS(t1, incl) the "Columns" column shows
> the following columns:
> c1, c3, c2
> ASSUMPTION #2: Based upon Assumption #1, I would expect to see the
> clustered
> index column of c3 appended to the end of the nonclustered index column,
> meaning I expected to see c1, c3, c2, c3. Since that was not the case, I
> assume the reason the clustered index was not appended was due to its [the
> clustered index] being included in the nonclustered index, and appending
> column c3 [the clustered index column] to the nonclustered index in
> DBCC_SHOW_STATISTICS would be redundant.
> Are the above assumptions true?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200604/1|||sort of.
clustered indexes are weird. they physically sort the data, and then
use the columns as a key to find the relevant rows. all of this is
inherently inefficient.
non clustered indexes use data positions in the table to locate rows.
well, if there is a clustered index on the table, then those data
positions are determined by the clustered column.
so, if we create a non clustered index on the table, the engine MUST
HAVE the data elements from the clustered column in order to find the
correct row.
to see how this all works, and make it real in your head, right down
some 5 rows of examples, then create how the index would really work.sql

Monday, March 19, 2012

Assistance creating my first trigger

I hope you guys can help. I am looking to create a trigger that watches for
updates to the sch_status column and fires when the update changes the value
to 4. The code that will be executed is below with addition of some code to
e-mail the results. It is the actual code to create the update trigger on the
schedule table I need some help with.
select s.sch_id,
'Date Scheduled'=convert(varchar(17),s.sch_date,113),
s.e_subject,
'Number Processed'=(select count(dl.sch_id) from distribution_list dl where
dl.sch_id = s.sch_id and dl.delivery_status=2),
'Total Number'=(select count(dl.sch_id) from distribution_list dl where
dl.sch_id = s.sch_id),
'Log file'= '\\Renown\eNewsletters\Logs\SE\'+
convert(varchar(12),getdate(),112) +'.log'
from schedule s
where s.sch_status = 4
order by s.sch_idHi
All what you have to do it join the virtual table INSERTED in your query on
the primary key.
It is not recommended that you send e-mails from within a trigger, as it
hurts performance, has horrible consequences with locking and blocking, and
if the e-mail sending fails, your update will get rolled back (and you might
have sent e-mails to 1/2 your customer base). Rather write a record into a
queue type table where another process comes along a sends the e-mails.
Books On Line has some good examples on CREATE TRIGGER and how to use the
virtual tables INSERTED and DELETED.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Russell" <Russell@.discussions.microsoft.com> wrote in message
news:746A52BB-B25C-4EA7-99B9-65574C6171C3@.microsoft.com...
> I hope you guys can help. I am looking to create a trigger that watches
for
> updates to the sch_status column and fires when the update changes the
value
> to 4. The code that will be executed is below with addition of some code
to
> e-mail the results. It is the actual code to create the update trigger on
the
> schedule table I need some help with.
> select s.sch_id,
> 'Date Scheduled'=convert(varchar(17),s.sch_date,113),
> s.e_subject,
> 'Number Processed'=(select count(dl.sch_id) from distribution_list dl
where
> dl.sch_id = s.sch_id and dl.delivery_status=2),
> 'Total Number'=(select count(dl.sch_id) from distribution_list dl where
> dl.sch_id = s.sch_id),
> 'Log file'= '\\Renown\eNewsletters\Logs\SE\'+
> convert(varchar(12),getdate(),112) +'.log'
> from schedule s
> where s.sch_status = 4
> order by s.sch_id|||In case it leads to a different outcome I plan to have the select statement
and the e-mail (not sql mail) in a stored procedure and have it called by the
trigger.
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> All what you have to do it join the virtual table INSERTED in your query on
> the primary key.
> It is not recommended that you send e-mails from within a trigger, as it
> hurts performance, has horrible consequences with locking and blocking, and
> if the e-mail sending fails, your update will get rolled back (and you might
> have sent e-mails to 1/2 your customer base). Rather write a record into a
> queue type table where another process comes along a sends the e-mails.
> Books On Line has some good examples on CREATE TRIGGER and how to use the
> virtual tables INSERTED and DELETED.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Russell" <Russell@.discussions.microsoft.com> wrote in message
> news:746A52BB-B25C-4EA7-99B9-65574C6171C3@.microsoft.com...
> > I hope you guys can help. I am looking to create a trigger that watches
> for
> > updates to the sch_status column and fires when the update changes the
> value
> > to 4. The code that will be executed is below with addition of some code
> to
> > e-mail the results. It is the actual code to create the update trigger on
> the
> > schedule table I need some help with.
> >
> > select s.sch_id,
> > 'Date Scheduled'=convert(varchar(17),s.sch_date,113),
> > s.e_subject,
> > 'Number Processed'=(select count(dl.sch_id) from distribution_list dl
> where
> > dl.sch_id = s.sch_id and dl.delivery_status=2),
> > 'Total Number'=(select count(dl.sch_id) from distribution_list dl where
> > dl.sch_id = s.sch_id),
> > 'Log file'= '\\Renown\eNewsletters\Logs\SE\'+
> > convert(varchar(12),getdate(),112) +'.log'
> > from schedule s
> > where s.sch_status = 4
> > order by s.sch_id
>
>|||You still have the same issues (concurrency/performance), even if you
execute a proc in order to send email from the trigger. I suggest you heed
Mike's suggestion and employ a queue-type table so that you can send the
actual email asynchronously. It's fairly simple to implement.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Russell" <Russell@.discussions.microsoft.com> wrote in message
news:47D97573-D64B-4E22-90D7-7B7F1F70404C@.microsoft.com...
> In case it leads to a different outcome I plan to have the select
> statement
> and the e-mail (not sql mail) in a stored procedure and have it called by
> the
> trigger.
> "Mike Epprecht (SQL MVP)" wrote:
>> Hi
>> All what you have to do it join the virtual table INSERTED in your query
>> on
>> the primary key.
>> It is not recommended that you send e-mails from within a trigger, as it
>> hurts performance, has horrible consequences with locking and blocking,
>> and
>> if the e-mail sending fails, your update will get rolled back (and you
>> might
>> have sent e-mails to 1/2 your customer base). Rather write a record into
>> a
>> queue type table where another process comes along a sends the e-mails.
>> Books On Line has some good examples on CREATE TRIGGER and how to use the
>> virtual tables INSERTED and DELETED.
>> Regards
>> --
>> Mike Epprecht, Microsoft SQL Server MVP
>> Zurich, Switzerland
>> IM: mike@.epprecht.net
>> MVP Program: http://www.microsoft.com/mvp
>> Blog: http://www.msmvps.com/epprecht/
>> "Russell" <Russell@.discussions.microsoft.com> wrote in message
>> news:746A52BB-B25C-4EA7-99B9-65574C6171C3@.microsoft.com...
>> > I hope you guys can help. I am looking to create a trigger that watches
>> for
>> > updates to the sch_status column and fires when the update changes the
>> value
>> > to 4. The code that will be executed is below with addition of some
>> > code
>> to
>> > e-mail the results. It is the actual code to create the update trigger
>> > on
>> the
>> > schedule table I need some help with.
>> >
>> > select s.sch_id,
>> > 'Date Scheduled'=convert(varchar(17),s.sch_date,113),
>> > s.e_subject,
>> > 'Number Processed'=(select count(dl.sch_id) from distribution_list dl
>> where
>> > dl.sch_id = s.sch_id and dl.delivery_status=2),
>> > 'Total Number'=(select count(dl.sch_id) from distribution_list dl where
>> > dl.sch_id = s.sch_id),
>> > 'Log file'= '\\Renown\eNewsletters\Logs\SE\'+
>> > convert(varchar(12),getdate(),112) +'.log'
>> > from schedule s
>> > where s.sch_status = 4
>> > order by s.sch_id
>>|||Another possibility is to have a SQL Agent job set up to execute your stored
procedure. This does not mean you need to use SQL Agent mail--the stored
procedure can still call xp_sendmail directly--however, you can have the
trigger fire off msdb.sp_startjob. The execution of this stored procedure
circumvents some of the concurrency/consistency issues in that it makes the
call to launch the process but runs the job synchronously.
If there is a problem with starting the job execution, you will get a
failure notice and your transaction will rollback but you would not have
emailed anyone yet.
Sincerely,
Anthony Thomas
"Dan Guzman" wrote:
> You still have the same issues (concurrency/performance), even if you
> execute a proc in order to send email from the trigger. I suggest you heed
> Mike's suggestion and employ a queue-type table so that you can send the
> actual email asynchronously. It's fairly simple to implement.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Russell" <Russell@.discussions.microsoft.com> wrote in message
> news:47D97573-D64B-4E22-90D7-7B7F1F70404C@.microsoft.com...
> > In case it leads to a different outcome I plan to have the select
> > statement
> > and the e-mail (not sql mail) in a stored procedure and have it called by
> > the
> > trigger.
> >
> > "Mike Epprecht (SQL MVP)" wrote:
> >
> >> Hi
> >>
> >> All what you have to do it join the virtual table INSERTED in your query
> >> on
> >> the primary key.
> >>
> >> It is not recommended that you send e-mails from within a trigger, as it
> >> hurts performance, has horrible consequences with locking and blocking,
> >> and
> >> if the e-mail sending fails, your update will get rolled back (and you
> >> might
> >> have sent e-mails to 1/2 your customer base). Rather write a record into
> >> a
> >> queue type table where another process comes along a sends the e-mails.
> >>
> >> Books On Line has some good examples on CREATE TRIGGER and how to use the
> >> virtual tables INSERTED and DELETED.
> >>
> >> Regards
> >> --
> >> Mike Epprecht, Microsoft SQL Server MVP
> >> Zurich, Switzerland
> >>
> >> IM: mike@.epprecht.net
> >>
> >> MVP Program: http://www.microsoft.com/mvp
> >>
> >> Blog: http://www.msmvps.com/epprecht/
> >>
> >> "Russell" <Russell@.discussions.microsoft.com> wrote in message
> >> news:746A52BB-B25C-4EA7-99B9-65574C6171C3@.microsoft.com...
> >> > I hope you guys can help. I am looking to create a trigger that watches
> >> for
> >> > updates to the sch_status column and fires when the update changes the
> >> value
> >> > to 4. The code that will be executed is below with addition of some
> >> > code
> >> to
> >> > e-mail the results. It is the actual code to create the update trigger
> >> > on
> >> the
> >> > schedule table I need some help with.
> >> >
> >> > select s.sch_id,
> >> > 'Date Scheduled'=convert(varchar(17),s.sch_date,113),
> >> > s.e_subject,
> >> > 'Number Processed'=(select count(dl.sch_id) from distribution_list dl
> >> where
> >> > dl.sch_id = s.sch_id and dl.delivery_status=2),
> >> > 'Total Number'=(select count(dl.sch_id) from distribution_list dl where
> >> > dl.sch_id = s.sch_id),
> >> > 'Log file'= '\\Renown\eNewsletters\Logs\SE\'+
> >> > convert(varchar(12),getdate(),112) +'.log'
> >> > from schedule s
> >> > where s.sch_status = 4
> >> > order by s.sch_id
> >>
> >>
> >>
>
>|||In reviewing this thread, I see we forgot to mention that the proc/script to
actually send the email can be asynchronously scheduled with a SQL Agent
job. Thanks for pointing that out.
> If there is a problem with starting the job execution, you will get a
> failure notice and your transaction will rollback but you would not have
> emailed anyone yet.
If Russel doesn't want a sp_startjob failure to rollback the insertion, the
job can be scheduled to run periodically rather than on demand.
Another method is to create a startup proc that runs continuously with a
WAITFOR DELAY loop.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"AnthonyThomas" <AnthonyThomas@.discussions.microsoft.com> wrote in message
news:948EC3BF-AB7C-4669-BC74-AD81E2E0C71C@.microsoft.com...
> Another possibility is to have a SQL Agent job set up to execute your
> stored
> procedure. This does not mean you need to use SQL Agent mail--the stored
> procedure can still call xp_sendmail directly--however, you can have the
> trigger fire off msdb.sp_startjob. The execution of this stored procedure
> circumvents some of the concurrency/consistency issues in that it makes
> the
> call to launch the process but runs the job synchronously.
> If there is a problem with starting the job execution, you will get a
> failure notice and your transaction will rollback but you would not have
> emailed anyone yet.
> Sincerely,
>
> Anthony Thomas
>
> "Dan Guzman" wrote:
>> You still have the same issues (concurrency/performance), even if you
>> execute a proc in order to send email from the trigger. I suggest you
>> heed
>> Mike's suggestion and employ a queue-type table so that you can send the
>> actual email asynchronously. It's fairly simple to implement.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Russell" <Russell@.discussions.microsoft.com> wrote in message
>> news:47D97573-D64B-4E22-90D7-7B7F1F70404C@.microsoft.com...
>> > In case it leads to a different outcome I plan to have the select
>> > statement
>> > and the e-mail (not sql mail) in a stored procedure and have it called
>> > by
>> > the
>> > trigger.
>> >
>> > "Mike Epprecht (SQL MVP)" wrote:
>> >
>> >> Hi
>> >>
>> >> All what you have to do it join the virtual table INSERTED in your
>> >> query
>> >> on
>> >> the primary key.
>> >>
>> >> It is not recommended that you send e-mails from within a trigger, as
>> >> it
>> >> hurts performance, has horrible consequences with locking and
>> >> blocking,
>> >> and
>> >> if the e-mail sending fails, your update will get rolled back (and you
>> >> might
>> >> have sent e-mails to 1/2 your customer base). Rather write a record
>> >> into
>> >> a
>> >> queue type table where another process comes along a sends the
>> >> e-mails.
>> >>
>> >> Books On Line has some good examples on CREATE TRIGGER and how to use
>> >> the
>> >> virtual tables INSERTED and DELETED.
>> >>
>> >> Regards
>> >> --
>> >> Mike Epprecht, Microsoft SQL Server MVP
>> >> Zurich, Switzerland
>> >>
>> >> IM: mike@.epprecht.net
>> >>
>> >> MVP Program: http://www.microsoft.com/mvp
>> >>
>> >> Blog: http://www.msmvps.com/epprecht/
>> >>
>> >> "Russell" <Russell@.discussions.microsoft.com> wrote in message
>> >> news:746A52BB-B25C-4EA7-99B9-65574C6171C3@.microsoft.com...
>> >> > I hope you guys can help. I am looking to create a trigger that
>> >> > watches
>> >> for
>> >> > updates to the sch_status column and fires when the update changes
>> >> > the
>> >> value
>> >> > to 4. The code that will be executed is below with addition of some
>> >> > code
>> >> to
>> >> > e-mail the results. It is the actual code to create the update
>> >> > trigger
>> >> > on
>> >> the
>> >> > schedule table I need some help with.
>> >> >
>> >> > select s.sch_id,
>> >> > 'Date Scheduled'=convert(varchar(17),s.sch_date,113),
>> >> > s.e_subject,
>> >> > 'Number Processed'=(select count(dl.sch_id) from distribution_list
>> >> > dl
>> >> where
>> >> > dl.sch_id = s.sch_id and dl.delivery_status=2),
>> >> > 'Total Number'=(select count(dl.sch_id) from distribution_list dl
>> >> > where
>> >> > dl.sch_id = s.sch_id),
>> >> > 'Log file'= '\\Renown\eNewsletters\Logs\SE\'+
>> >> > convert(varchar(12),getdate(),112) +'.log'
>> >> > from schedule s
>> >> > where s.sch_status = 4
>> >> > order by s.sch_id
>> >>
>> >>
>> >>
>>

Assistance creating my first trigger

Assist needed in creating a replication with sql 2005

I tried to create a local snapshot replication from data A to database
B for sql 2005. I followed the wizard and it was created successfully.
But,nothing written to the replication folder and the job failed.
I manually executed the sqls and it always failed on
sp_addpublication_snapshot and the error was:
'DB4\Administrator' is a member of sysadmin server role and cannot be
granted to or revoked from the proxy. Members of sysadmin server role
are allowed to use any proxy.
I log in to windows 2003 as administrator and the replication account
id dbsnap. What I have to do to avoid the error?
Is there a detailed step-by=step guide to create a snapshot
replication?
Can someone provide a set of sqls that I can just use to create a local
(or remote) snapshot?
Thanks,
Andy
The scripts are:
use [T2]
exec sp_replicationdboption @.dbname = N'T2', @.optname = N'publish',
@.value = N'true'
GO
-- Adding the snapshot publication
use [T2]
exec sp_addpublication @.publication = N'T2', @.description = N'Snapshot
publication of
database ''T2'' from Publisher ''DB4''.', @.sync_method = N'native',
@.retention = 0,
@.allow_push = N'true', @.allow_pull = N'true', @.allow_anonymous = N'true',
@.enabled_for_internet = N'false', @.snapshot_in_defaultfolder = N'true',
@.compress_snapshot =
N'false', @.ftp_port = 21, @.ftp_login = N'anonymous',
@.allow_subscription_copy = N'false',
@.add_to_active_directory = N'false', @.repl_freq = N'snapshot', @.status
= N'active',
@.independent_agent = N'true', @.immediate_sync = N'true',
@.allow_sync_tran = N'false',
@.autogen_sync_procs = N'false', @.allow_queued_tran = N'false',
@.allow_dts = N'false',
@.replicate_ddl = 1
GO
exec sp_addpublication_snapshot @.publication = N'T2', @.frequency_type =
1,
@.frequency_interval = 0, @.frequency_relative_interval = 0,
@.frequency_recurrence_factor = 0,
@.frequency_subday = 0, @.frequency_subday_interval = 0,
@.active_start_time_of_day = 0,
@.active_end_time_of_day = 235959, @.active_start_date = 0,
@.active_end_date = 0, @.job_login =
N'db4\dbsnap', @.job_password = N'wenhua', @.publisher_security_mode = 0,
@.publisher_login =
N'sa', @.publisher_password = N'chang5911'
use [T2]
exec sp_addarticle @.publication = N'T2', @.article = N'RETURN_REASON',
@.source_owner =
N'dbo', @.source_object = N'RETURN_REASON', @.type = N'logbased',
@.description = null,
@.creation_script = null, @.pre_creation_cmd = N'drop', @.schema_option = 0x000000000803509D,
@.identityrangemanagementoption = N'manual', @.destination_table = N'RETURN_REASON',
@.destination_owner = N'dbo', @.vertical_partition = N'false'
GOCan you extract the code from the job and pass it to the snapshot.exe found
in c:\program files\Microsoft SQL Server\90\com?
--
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"AH" <hhhsu7a@.yahoo.com> wrote in message
news:1168824792.916342.291270@.11g2000cwr.googlegroups.com...
>I tried to create a local snapshot replication from data A to database
> B for sql 2005. I followed the wizard and it was created successfully.
> But,nothing written to the replication folder and the job failed.
> I manually executed the sqls and it always failed on
> sp_addpublication_snapshot and the error was:
> 'DB4\Administrator' is a member of sysadmin server role and cannot be
> granted to or revoked from the proxy. Members of sysadmin server role
> are allowed to use any proxy.
>
> I log in to windows 2003 as administrator and the replication account
> id dbsnap. What I have to do to avoid the error?
>
> Is there a detailed step-by=step guide to create a snapshot
> replication?
>
> Can someone provide a set of sqls that I can just use to create a local
> (or remote) snapshot?
>
> Thanks,
> Andy
>
> The scripts are:
> use [T2]
> exec sp_replicationdboption @.dbname = N'T2', @.optname = N'publish',
> @.value = N'true'
> GO
> -- Adding the snapshot publication
> use [T2]
> exec sp_addpublication @.publication = N'T2', @.description = N'Snapshot
> publication of
>
> database ''T2'' from Publisher ''DB4''.', @.sync_method = N'native',
> @.retention = 0,
>
> @.allow_push = N'true', @.allow_pull = N'true', @.allow_anonymous => N'true',
>
> @.enabled_for_internet = N'false', @.snapshot_in_defaultfolder = N'true',
> @.compress_snapshot =>
> N'false', @.ftp_port = 21, @.ftp_login = N'anonymous',
> @.allow_subscription_copy = N'false',
>
> @.add_to_active_directory = N'false', @.repl_freq = N'snapshot', @.status
> = N'active',
>
> @.independent_agent = N'true', @.immediate_sync = N'true',
> @.allow_sync_tran = N'false',
>
> @.autogen_sync_procs = N'false', @.allow_queued_tran = N'false',
> @.allow_dts = N'false',
>
> @.replicate_ddl = 1
> GO
>
> exec sp_addpublication_snapshot @.publication = N'T2', @.frequency_type => 1,
>
> @.frequency_interval = 0, @.frequency_relative_interval = 0,
> @.frequency_recurrence_factor = 0,
>
> @.frequency_subday = 0, @.frequency_subday_interval = 0,
> @.active_start_time_of_day = 0,
>
> @.active_end_time_of_day = 235959, @.active_start_date = 0,
> @.active_end_date = 0, @.job_login =>
> N'db4\dbsnap', @.job_password = N'wenhua', @.publisher_security_mode = 0,
> @.publisher_login =>
> N'sa', @.publisher_password = N'chang5911'
>
> use [T2]
> exec sp_addarticle @.publication = N'T2', @.article = N'RETURN_REASON',
> @.source_owner =>
> N'dbo', @.source_object = N'RETURN_REASON', @.type = N'logbased',
> @.description = null,
>
> @.creation_script = null, @.pre_creation_cmd = N'drop', @.schema_option => 0x000000000803509D,
>
> @.identityrangemanagementoption = N'manual', @.destination_table => N'RETURN_REASON',
>
> @.destination_owner = N'dbo', @.vertical_partition = N'false'
> GO
>|||Hilary,
I switched to use administor, instead of a windows user, and the
replication went fine. Does the windows user id require to have
administion privilege?
Andy
Hilary Cotter wrote:
> Can you extract the code from the job and pass it to the snapshot.exe found
> in c:\program files\Microsoft SQL Server\90\com?
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "AH" <hhhsu7a@.yahoo.com> wrote in message
> news:1168824792.916342.291270@.11g2000cwr.googlegroups.com...
> >I tried to create a local snapshot replication from data A to database
> > B for sql 2005. I followed the wizard and it was created successfully.
> > But,nothing written to the replication folder and the job failed.
> >
> > I manually executed the sqls and it always failed on
> > sp_addpublication_snapshot and the error was:
> > 'DB4\Administrator' is a member of sysadmin server role and cannot be
> > granted to or revoked from the proxy. Members of sysadmin server role
> > are allowed to use any proxy.
> >
> >
> > I log in to windows 2003 as administrator and the replication account
> > id dbsnap. What I have to do to avoid the error?
> >
> >
> > Is there a detailed step-by=step guide to create a snapshot
> > replication?
> >
> >
> > Can someone provide a set of sqls that I can just use to create a local
> >
> > (or remote) snapshot?
> >
> >
> > Thanks,
> > Andy
> >
> >
> > The scripts are:
> > use [T2]
> > exec sp_replicationdboption @.dbname = N'T2', @.optname = N'publish',
> > @.value = N'true'
> > GO
> > -- Adding the snapshot publication
> > use [T2]
> > exec sp_addpublication @.publication = N'T2', @.description = N'Snapshot
> > publication of
> >
> >
> > database ''T2'' from Publisher ''DB4''.', @.sync_method = N'native',
> > @.retention = 0,
> >
> >
> > @.allow_push = N'true', @.allow_pull = N'true', @.allow_anonymous => > N'true',
> >
> >
> > @.enabled_for_internet = N'false', @.snapshot_in_defaultfolder = N'true',
> >
> > @.compress_snapshot => >
> >
> > N'false', @.ftp_port = 21, @.ftp_login = N'anonymous',
> > @.allow_subscription_copy = N'false',
> >
> >
> > @.add_to_active_directory = N'false', @.repl_freq = N'snapshot', @.status
> > = N'active',
> >
> >
> > @.independent_agent = N'true', @.immediate_sync = N'true',
> > @.allow_sync_tran = N'false',
> >
> >
> > @.autogen_sync_procs = N'false', @.allow_queued_tran = N'false',
> > @.allow_dts = N'false',
> >
> >
> > @.replicate_ddl = 1
> > GO
> >
> >
> > exec sp_addpublication_snapshot @.publication = N'T2', @.frequency_type => >
> > 1,
> >
> >
> > @.frequency_interval = 0, @.frequency_relative_interval = 0,
> > @.frequency_recurrence_factor = 0,
> >
> >
> > @.frequency_subday = 0, @.frequency_subday_interval = 0,
> > @.active_start_time_of_day = 0,
> >
> >
> > @.active_end_time_of_day = 235959, @.active_start_date = 0,
> > @.active_end_date = 0, @.job_login => >
> >
> > N'db4\dbsnap', @.job_password = N'wenhua', @.publisher_security_mode = 0,
> >
> > @.publisher_login => >
> >
> > N'sa', @.publisher_password = N'chang5911'
> >
> >
> > use [T2]
> > exec sp_addarticle @.publication = N'T2', @.article = N'RETURN_REASON',
> > @.source_owner => >
> >
> > N'dbo', @.source_object = N'RETURN_REASON', @.type = N'logbased',
> > @.description = null,
> >
> >
> > @.creation_script = null, @.pre_creation_cmd = N'drop', @.schema_option => > 0x000000000803509D,
> >
> >
> > @.identityrangemanagementoption = N'manual', @.destination_table => > N'RETURN_REASON',
> >
> >
> > @.destination_owner = N'dbo', @.vertical_partition = N'false'
> > GO
> >

Assist needed in creating a replication with sql 2005

Sunday, March 11, 2012

assigning sysadmin rights to login through O-SQL

Hi,
I'm currently using MSDE 2000 & i want to create a server login with
SYSADMIN Rights.
please suggest.If you use a sql server login, you can use:
sp_addlogin 'NewUser','NewSecretPassword'
GO
sp_addsrvrolemember 'NewUser','sy

min'
HTH, jens Suessmeyer.

Assigning parameters in a function

I am trying to create a function in SQL 2000. Im rusty on function
syntax and need to also assign some variables for use within this
function.

Example:
create function blah (@.param1 int)

declare @.var1 int, @.var2 int

--it doesnt like this method of assigning values to my
variables????
select @.var1, @.var2 = (select name, phone from customer where id =
@.param1)

--Then I need to use those variables in this next statement...

DECLARE @.Zip int
SELECT @.Results = (select zip from table2 where name = @.var1 and phone
= @.var2)

return @.Results

-----------
Any help would be greatly appreciated at filling in the missing
syntax.> --it doesnt like this method of assigning values to my
> variables????
> select @.var1, @.var2 = (select name, phone from customer where id =
> @.param1)

Try:

select @.var1= name, @.var2 = phone
from customer where id = @.param1

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Dave" <funkdm1@.yahoo.com> wrote in message
news:f5174e0f.0404291539.211a5032@.posting.google.c om...
> I am trying to create a function in SQL 2000. Im rusty on function
> syntax and need to also assign some variables for use within this
> function.
> Example:
> create function blah (@.param1 int)
> declare @.var1 int, @.var2 int
> --it doesnt like this method of assigning values to my
> variables????
> select @.var1, @.var2 = (select name, phone from customer where id =
> @.param1)
> --Then I need to use those variables in this next statement...
> DECLARE @.Zip int
> SELECT @.Results = (select zip from table2 where name = @.var1 and phone
> = @.var2)
> return @.Results
> -----------
> Any help would be greatly appreciated at filling in the missing
> syntax.

Saturday, February 25, 2012

Assembly MyAssembly was not found in the SQL catalog of database MyDB

I’m trying to register my CLR UDF in SQL 2005 using this code

CREATE FUNCTION GetSomething() RETURNS INT

AS EXTERNAL NAME MyAssembly.MyFunction.MyMethod

When I run it against my DB I get this error:

Assembly MyAssembly was not found in the SQL catalog of database MyDB

I’ve successfully registered my custom assembly in the DB (I see it under Assemblies folder), and I’ve set CRL Enabled to 1 in my DB.

What am I doing wrong?

Thanks in advance

A couple of things:

1. make sure that the CREATE FUNCTION call is actually executed in the database where MyAssembly is located.
2. Make sure that you spell the name correctly of the assembly

if both of those are OK:
1. then check that you do not have any namespaces in the class name. For example, VB injects a namespace into the assembly, so the class name would be [namespace.classname].
2. make sure that the classname and method name are capitalize correctly - they are case sensitive.

Niels

|||I have same problem and I do deployment using Visual Studio .NET 2005 final release, it is as nightmare to see these outstanding bug free products ...

Assembly MyAssembly was not found in the SQL catalog of database MyDB

I’m trying to register my CLR UDF in SQL 2005 using this code

CREATE FUNCTION GetSomething() RETURNS INT

AS EXTERNAL NAME MyAssembly.MyFunction.MyMethod

When I run it against my DB I get this error:

Assembly MyAssembly was not found in the SQL catalog of database MyDB

I’ve successfully registered my custom assembly in the DB (I see it under Assemblies folder), and I’ve set CRL Enabled to 1 in my DB.

What am I doing wrong?

Thanks in advance

A couple of things:

1. make sure that the CREATE FUNCTION call is actually executed in the database where MyAssembly is located.
2. Make sure that you spell the name correctly of the assembly

Niels

|||I have same problem and I do deployment using Visual Studio .NET 2005 final release, it is as nightmare to see these outstanding bug free products ...

Friday, February 24, 2012

ASPNETDB.MDF is read-only....

Hello everyone.

I am to create a sample site using the club site example. I had a problem with the IIS but now it is solved.

Now i copyied the site to the IIS and it is running. But i cannot access ASPNETDB.MDF. It keep saying that the database is read only... It is not read only!!!

Can any one help me??

System.Data.SqlClient.SqlException: Failed to update database "C:\INETPUB\WWWROOT\NEO\APP_DATA\ASPNETDB.MDF" because the database is read-only.

Thank you in advance.

Iasonas

Try granting the NETWORK SERVICE account R/W access to the contents of your app_data folder (mdf's and ldf's)and the folder itself.

If that doesn't work, please take a look at this post:

http://forums.asp.net/thread/906040.aspx

Why not using search feature in this formus:) Maybe you issue is a common one, then you can find some possible solution by searching the formus rather than just waiting:)

Aspnetdb.mdf and aspnetdb_log script files

Hi,

Please any one can pass me Aspnetdb.mdf and aspnetdb_log scripts files in order to create those databases in sql server 2000.

thanks.....

Assumung that this is related to the Visual Web Development Kit, this can be downloaded under:
http://msdn.microsoft.com/vstudio/express/vwd/
HTH, jens Suessmeyer.

|||

Assuming that you mean the database that is created from the Visual Web Develoepr Package you can download it here. Once downloaded there should be a package to install the database within the web admin package.
http://msdn.microsoft.com/express/vwd/

HTH, Jens Suessmeyer.

ASPNETDB.mdf "...already in use..." Error and Broader Questions

When you create and host an ASP.Net 2.0 site on your local computer and then configure .Net 2.0's drop-in user registration and management system it creates a SQL file named ASPNETDB.mdf with a whole bunch of tables and stored procedures (…as most of you know). If you add your own custom tables to that database file and then try to call those tables you get an error that says that ASPNETDB.mdf is already in use. One way to get around this is to create a separate database in the form of another ".mdf" file and then put all of your own custom tables in it. You avoid the "...already in use..." errors that way but all your user accounts and the primary key structure that identifies them are in the original ASPNETDB.mdf file. This makes it impossible to do primary/foreign key relationships between those tables and those in the new separate database you created. It's kind of a catch-22 situation...unless I'm missing something that relates to releasing the ASPNETDB.mdf file from use whenever it is called so that additional tables and queries against that database file can be made without the "…already in use…" error cropping up. I am wondering if this problem is because the ASPNETDB.mdf file is not a "real" SQL database and as such imposes multiple-connectivity limitations such as those I am seeing. If this is true, migrating to a real SQL database would alleviate this? Finally, from a broader security and scalability standpoint…what are the best practices relating to use of the ASPNETDB.mdf database for all your custom tables? Should an additional database be created for all my application's custom tables (leading to the primary/foreign key problems) or should the additional tables be put into the ASPNETDB.mdf file (with some way of working around the "…already in use…" error)? A long-winded and broad question…thanks in advance for any responses.

MDCragg

The default membership provider already has an open connection to the file, which seems like the reason why you can't make your own connection. Perhaps you could extend this provider to keep its current functionality, but add your own requirements to access the other tables, as well. The default provider is the System.Web.Security.SqlMembershipProvider class. After you create this, set it as the default provider in your web.config.

Sample on how to configure a membership provider in web.config:http://msdn2.microsoft.com/en-us/library/44w5aswa.aspx

|||

That article tells how to merge the membership provider tables and stored procedures into my own database. I will try this to see if it solves the problem. I am at least a little bit doubtful if it will though because it seems like it will just shift the same persistent and exclusive connectivity problem I am having from the current user account database to my own database.

|||

This seems to have worked. I installed SQL Server Management Studio Express. I wasn't able to navigate to the existing custom database that I had created so I copied it to the default directory that SQL Server MSE utilizes. I was able to connect to it there and thus "attach" it to my PC's SQL host. Once that was done I was able to use the aspnet_regsql.exe utility to populate that database with all of .Net's Membership tables, views, stored procedures, etc. I copied the database back to the App_Data folder. I adjusted all the Membership entries in the web.config folder to "point to" the custom database instead of the ASPNETDB.mdf file (which I removed). I did some additional tweaking with things such as connection strings. Then I launched the site and everything seemed to work. I am able to connect to the .Net Membership tables as well as all my own tables...all of which exist in the one custom database file.

So, this is fixed although I don't know what the difference was between the ASPNETDB.mdf file and the custom ".mdf" file that I created. I'm sure there is a setting or two somewhere in the database instance, the connection, or something or other that led to the difficulty.

aspnet_regsqlcache tool not found in my system

hi i am going to install the aspnet_regsqlcache tool in my system to create the notification tables for my database. while i am istalling in the .net framework command prompt, it tells like this,

"'aspnet_regsqlcache' is not recognized as an internal or external command"

what is the problem in my system...please any one help to solve this issues..

thanks in advance

The utility is now called aspnet_regsql.exe. It can be found here C:\WINDOWS\Microsoft.Net\Framework\v2.0.50727.

This was also discussed on the Wrox forum (in case you read about this in a book). http://p2p.wrox.com/TopicIndex/21996.htm

Paul A. Mestemaker II
Program Manager
Microsoft SQL Server Manageability
http://blogs.msdn.com/sqlrem/

Tuesday, March 27, 2012

Attach db failure cause log not primary

Thursday, March 22, 2012

Asynchronous / non-blocking trigger

Asynchronous / non-blocking trigger

Asynchronous / non-blocking trigger

Tuesday, March 20, 2012

Assumptions on Indexing

Assumptions on Indexing

Monday, March 19, 2012

Assistance creating my first trigger

Assistance creating my first trigger

Assistance creating my first trigger

Assist needed in creating a replication with sql 2005

Assist needed in creating a replication with sql 2005

Assist needed in creating a replication with sql 2005

Sunday, March 11, 2012

assigning sysadmin rights to login through O-SQL

Assigning parameters in a function

Saturday, February 25, 2012

Assembly MyAssembly was not found in the SQL catalog of database MyDB

Assembly MyAssembly was not found in the SQL catalog of database MyDB

Friday, February 24, 2012

ASPNETDB.MDF is read-only....

Aspnetdb.mdf and aspnetdb_log script files

ASPNETDB.mdf "...already in use..." Error and Broader Questions

aspnet_regsqlcache tool not found in my system

Asp.net / sql help

Blog Archive

About Me