Field overflow and Log grow

Hi,
I am using SQL Server 2000.
One user database contains a table as following:
CREATE TABLE Events..Audit_Sub (
[RecID] [bigint] NOT NULL ,
[Name] [varchar] (100) NULL ,
[Value] [varchar] (1024) NULL
) ON [PRIMARY]
Sometimes the application writes to the table a record that overflows the
size of the Value field (actually because of an error in the new code, the
appilcation attempts to write about 5Kb to the Valaue field).
The fact is:
- No error is detected on SQL Server, data is written to tha table, it is
visible by select, it is just truncated to the field size (1Kb).
- In the meanwhile it appears that DB log begins growing: the things are not
directly dependent, just somewhat later the log begins growing, but no error
is found in SQL errorlog.
- Later on transaction log cannot be backup up, data are no more written to
DB, but then it is too late to understand the reason why.
The question is:
- In which way may the two things (overflow and log grow) be related?
- What really happens on SQL server when data overflow occurs? How does it
handle?
Thanks in advance,
MRMarco
> - In the meanwhile it appears that DB log begins growing: the things are
> not
> directly dependent, just somewhat later the log begins growing, but no
> error
> is found in SQL errorlog.
> - In which way may the two things (overflow and log grow) be related?
It does not matter whether or not overflow occured. the log file grows up
and it is not truncated unless you have SIMPLE recovery mode the database
set
> - What really happens on SQL server when data overflow occurs? How does it
> handle?
create table t (c1 tinyint, c2 varchar (5))
--owerflow on c1 column
insert into t values(4545745454545,'a')
--Server: Msg 8115, Level 16, State 2, Line 1
--Arithmetic overflow error converting expression to data type tinyint.
--The statement has been terminated.
select * from t
--(0 row(s) affected)
--now insert much more characters than you defined for c2 columnn
insert into t values(1,'asgtbvtybvtg')
--Server: Msg 8152, Level 16, State 9, Line 1
--String or binary data would be truncated.
--The statement has been terminated.
select * from t
--(0 row(s) affected)
"Marco Roda" <mrtest@.amdosoft.com> wrote in message
news:e8t656$le6$1@.ss408.t-com.hr...
> Hi,
> I am using SQL Server 2000.
> One user database contains a table as following:
> CREATE TABLE Events..Audit_Sub (
> [RecID] [bigint] NOT NULL ,
> [Name] [varchar] (100) NULL ,
> [Value] [varchar] (1024) NULL
> ) ON [PRIMARY]
> Sometimes the application writes to the table a record that overflows the
> size of the Value field (actually because of an error in the new code, the
> appilcation attempts to write about 5Kb to the Valaue field).
> The fact is:
> - No error is detected on SQL Server, data is written to tha table, it is
> visible by select, it is just truncated to the field size (1Kb).
> - In the meanwhile it appears that DB log begins growing: the things are
> not
> directly dependent, just somewhat later the log begins growing, but no
> error
> is found in SQL errorlog.
> - Later on transaction log cannot be backup up, data are no more written
> to
> DB, but then it is too late to understand the reason why.
> The question is:
> - In which way may the two things (overflow and log grow) be related?
> - What really happens on SQL server when data overflow occurs? How does it
> handle?
> Thanks in advance,
> MR
>
>
>|||Hi
At a guess you have the ANSI_WARNINGS setting off as "When OFF, data is
truncated to the size of the column and the statement succeeds. " e.g
SET ANSI_WARNINGS ON
DECLARE @.error int
CREATE TABLE #tmp ( col1 char(1) NOT NULL )
BEGIN TRANSACTION
INSERT INTO #tmp ( col1 ) values ( 'AA' )
SET @.error = @.@.ERROR
IF @.error <> 0
BEGIN
SELECT 'Transaction Rolled Back Error Status: ' + CAST(@.error as varchar(30))
ROLLBACK TRANSACTIOn
END
ELSE
BEGIN
PRINT 'Transaction Comitted'
COMMIT TRANSACTION
END
GO
SELECT * from #tmp
GO
DROP TABLE #tmp
GO
/*
Msg 8152, Level 16, State 14, Line 5
String or binary data would be truncated.
The statement has been terminated.
----
Transaction Rolled Back Error Status: 8152
(1 row(s) affected)
col1
--
(0 row(s) affected)
*/
SET ANSI_WARNINGS OFF
DECLARE @.error int
CREATE TABLE #tmp ( col1 char(1) NOT NULL )
BEGIN TRANSACTION
INSERT INTO #tmp ( col1 ) values ( 'AA' )
SET @.error = @.@.ERROR
IF @.error <> 0
BEGIN
SELECT 'Transaction Rolled Back Error Status: ' + CAST(@.error as varchar(30))
ROLLBACK TRANSACTIOn
END
ELSE
BEGIN
PRINT 'Transaction Comitted'
COMMIT TRANSACTION
END
GO
SELECT * from #tmp
GO
DROP TABLE #tmp
GO
/*
(1 row(s) affected)
Transaction Comitted
col1
--
A
(1 row(s) affected)
*/
although with your log file growing it may be that you have detected an
error and not rolled back the transaction, use DBCC OPENTRAN to view open
transactions.
John
"Marco Roda" wrote:
> Hi,
> I am using SQL Server 2000.
> One user database contains a table as following:
> CREATE TABLE Events..Audit_Sub (
> [RecID] [bigint] NOT NULL ,
> [Name] [varchar] (100) NULL ,
> [Value] [varchar] (1024) NULL
> ) ON [PRIMARY]
> Sometimes the application writes to the table a record that overflows the
> size of the Value field (actually because of an error in the new code, the
> appilcation attempts to write about 5Kb to the Valaue field).
> The fact is:
> - No error is detected on SQL Server, data is written to tha table, it is
> visible by select, it is just truncated to the field size (1Kb).
> - In the meanwhile it appears that DB log begins growing: the things are not
> directly dependent, just somewhat later the log begins growing, but no error
> is found in SQL errorlog.
> - Later on transaction log cannot be backup up, data are no more written to
> DB, but then it is too late to understand the reason why.
> The question is:
> - In which way may the two things (overflow and log grow) be related?
> - What really happens on SQL server when data overflow occurs? How does it
> handle?
> Thanks in advance,
> MR
>
>
>|||"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uozopRApGHA.4996@.TK2MSFTNGP05.phx.gbl...
> Marco
> > - In the meanwhile it appears that DB log begins growing: the things are
> > not
> > directly dependent, just somewhat later the log begins growing, but no
> > error
> > is found in SQL errorlog.
> > - In which way may the two things (overflow and log grow) be related?
>
> It does not matter whether or not overflow occured. the log file grows up
> and it is not truncated unless you have SIMPLE recovery mode the database
> set
>
> > - What really happens on SQL server when data overflow occurs? How does
it
> > handle?
>
> create table t (c1 tinyint, c2 varchar (5))
> --owerflow on c1 column
> insert into t values(4545745454545,'a')
> --Server: Msg 8115, Level 16, State 2, Line 1
> --Arithmetic overflow error converting expression to data type tinyint.
> --The statement has been terminated.
> select * from t
> --(0 row(s) affected)
> --now insert much more characters than you defined for c2 columnn
> insert into t values(1,'asgtbvtybvtg')
> --Server: Msg 8152, Level 16, State 9, Line 1
> --String or binary data would be truncated.
> --The statement has been terminated.
> select * from t
> --(0 row(s) affected)
>
The fact is: when the application attempts writing more data, data is REALLY
WRITTEN (even if truncated), and NO ERROR is thrown.
- Why did not get error?
- May the overflow be a reason why the log is growing?

Field overflow and Log grow

Hi,
I am using SQL Server 2000.
One user database contains a table as following:
CREATE TABLE Events..Audit_Sub (
[RecID] [bigint] NOT NULL ,
[Name] [varchar] (100) NULL ,
[Value] [varchar] (1024) NULL
) ON [PRIMARY]
Sometimes the application writes to the table a record that overflows the
size of the Value field (actually because of an error in the new code, the
appilcation attempts to write about 5Kb to the Valaue field).
The fact is:
- No error is detected on SQL Server, data is written to tha table, it is
visible by select, it is just truncated to the field size (1Kb).
- In the meanwhile it appears that DB log begins growing: the things are not
directly dependent, just somewhat later the log begins growing, but no error
is found in SQL errorlog.
- Later on transaction log cannot be backup up, data are no more written to
DB, but then it is too late to understand the reason why.
The question is:
- In which way may the two things (overflow and log grow) be related?
- What really happens on SQL server when data overflow occurs? How does it
handle?
Thanks in advance,
MRMarco
> - In the meanwhile it appears that DB log begins growing: the things are
> not
> directly dependent, just somewhat later the log begins growing, but no
> error
> is found in SQL errorlog.

> - In which way may the two things (overflow and log grow) be related?
It does not matter whether or not overflow occured. the log file grows up
and it is not truncated unless you have SIMPLE recovery mode the database
set

> - What really happens on SQL server when data overflow occurs? How does it
> handle?
create table t (c1 tinyint, c2 varchar (5))
--owerflow on c1 column
insert into t values(4545745454545,'a')
--Server: Msg 8115, Level 16, State 2, Line 1
--Arithmetic overflow error converting expression to data type tinyint.
--The statement has been terminated.
select * from t
--(0 row(s) affected)
--now insert much more characters than you defined for c2 columnn
insert into t values(1,'asgtbvtybvtg')
--Server: Msg 8152, Level 16, State 9, Line 1
--String or binary data would be truncated.
--The statement has been terminated.
select * from t
--(0 row(s) affected)
"Marco Roda" <mrtest@.amdosoft.com> wrote in message
news:e8t656$le6$1@.ss408.t-com.hr...
> Hi,
> I am using SQL Server 2000.
> One user database contains a table as following:
> CREATE TABLE Events..Audit_Sub (
> [RecID] [bigint] NOT NULL ,
> [Name] [varchar] (100) NULL ,
> [Value] [varchar] (1024) NULL
> ) ON [PRIMARY]
> Sometimes the application writes to the table a record that overflows the
> size of the Value field (actually because of an error in the new code, the
> appilcation attempts to write about 5Kb to the Valaue field).
> The fact is:
> - No error is detected on SQL Server, data is written to tha table, it is
> visible by select, it is just truncated to the field size (1Kb).
> - In the meanwhile it appears that DB log begins growing: the things are
> not
> directly dependent, just somewhat later the log begins growing, but no
> error
> is found in SQL errorlog.
> - Later on transaction log cannot be backup up, data are no more written
> to
> DB, but then it is too late to understand the reason why.
> The question is:
> - In which way may the two things (overflow and log grow) be related?
> - What really happens on SQL server when data overflow occurs? How does it
> handle?
> Thanks in advance,
> MR
>
>
>|||Hi
At a guess you have the ANSI_WARNINGS setting off as "When OFF, data is
truncated to the size of the column and the statement succeeds. " e.g
SET ANSI_WARNINGS ON
DECLARE @.error int
CREATE TABLE #tmp ( col1 char(1) NOT NULL )
BEGIN TRANSACTION
INSERT INTO #tmp ( col1 ) values ( 'AA' )
SET @.error = @.@.ERROR
IF @.error <> 0
BEGIN
SELECT 'Transaction Rolled Back Error Status: ' + CAST(@.error as varchar(30)
)
ROLLBACK TRANSACTIOn
END
ELSE
BEGIN
PRINT 'Transaction Comitted'
COMMIT TRANSACTION
END
GO
SELECT * from #tmp
GO
DROP TABLE #tmp
GO
/*
Msg 8152, Level 16, State 14, Line 5
String or binary data would be truncated.
The statement has been terminated.
----
Transaction Rolled Back Error Status: 8152
(1 row(s) affected)
col1
--
(0 row(s) affected)
*/
SET ANSI_WARNINGS OFF
DECLARE @.error int
CREATE TABLE #tmp ( col1 char(1) NOT NULL )
BEGIN TRANSACTION
INSERT INTO #tmp ( col1 ) values ( 'AA' )
SET @.error = @.@.ERROR
IF @.error <> 0
BEGIN
SELECT 'Transaction Rolled Back Error Status: ' + CAST(@.error as varchar(30)
)
ROLLBACK TRANSACTIOn
END
ELSE
BEGIN
PRINT 'Transaction Comitted'
COMMIT TRANSACTION
END
GO
SELECT * from #tmp
GO
DROP TABLE #tmp
GO
/*
(1 row(s) affected)
Transaction Comitted
col1
--
A
(1 row(s) affected)
*/
although with your log file growing it may be that you have detected an
error and not rolled back the transaction, use DBCC OPENTRAN to view open
transactions.
John
"Marco Roda" wrote:

> Hi,
> I am using SQL Server 2000.
> One user database contains a table as following:
> CREATE TABLE Events..Audit_Sub (
> [RecID] [bigint] NOT NULL ,
> [Name] [varchar] (100) NULL ,
> [Value] [varchar] (1024) NULL
> ) ON [PRIMARY]
> Sometimes the application writes to the table a record that overflows the
> size of the Value field (actually because of an error in the new code, the
> appilcation attempts to write about 5Kb to the Valaue field).
> The fact is:
> - No error is detected on SQL Server, data is written to tha table, it is
> visible by select, it is just truncated to the field size (1Kb).
> - In the meanwhile it appears that DB log begins growing: the things are n
ot
> directly dependent, just somewhat later the log begins growing, but no err
or
> is found in SQL errorlog.
> - Later on transaction log cannot be backup up, data are no more written t
o
> DB, but then it is too late to understand the reason why.
> The question is:
> - In which way may the two things (overflow and log grow) be related?
> - What really happens on SQL server when data overflow occurs? How does it
> handle?
> Thanks in advance,
> MR
>
>
>|||"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uozopRApGHA.4996@.TK2MSFTNGP05.phx.gbl...
> Marco
>
>
> It does not matter whether or not overflow occured. the log file grows up
> and it is not truncated unless you have SIMPLE recovery mode the database
> set
>
it[vbcol=seagreen]
>
> create table t (c1 tinyint, c2 varchar (5))
> --owerflow on c1 column
> insert into t values(4545745454545,'a')
> --Server: Msg 8115, Level 16, State 2, Line 1
> --Arithmetic overflow error converting expression to data type tinyint.
> --The statement has been terminated.
> select * from t
> --(0 row(s) affected)
> --now insert much more characters than you defined for c2 columnn
> insert into t values(1,'asgtbvtybvtg')
> --Server: Msg 8152, Level 16, State 9, Line 1
> --String or binary data would be truncated.
> --The statement has been terminated.
> select * from t
> --(0 row(s) affected)
>
The fact is: when the application attempts writing more data, data is REALLY
WRITTEN (even if truncated), and NO ERROR is thrown.
- Why did not get error?
- May the overflow be a reason why the log is growing?

Field name as a parameter to a stored procedure?

Hi,
Is it possible to pass a field name as a parameter to a stored procedure?
The user wants to select either Dollar or Euro in the UI. The field I want
to be variable is a float & is used in a simple calculation...
(a.A_Tot_Rev_For / e.USDollar)
So I want e.USDollar to be able to change to e.Euro for example when my
report needs euro values.
thanks in advance,
jpi think a UDF is a better option

field definitions for all user tables

Hello.
Thank you in advance for reading this question.
I posted this question in the newbie area, but have not had a response.
Is there a way to get all the fields for all user tables that are returned
when you do sp_help tablename? I think I have found the columns I need in
'syscolumns' but it is not clear to me how to link the table names to that
syscolumns table to get what I need.
My apolgies for the newbie question. Any help is appreciated!
DianeI just replied to the newusers post.
However, to get table names from syscolumns you can simply call the
OBJECT_NAME() function passing in the id column from syscolumns
(although it's more kosher to use the INFORMATION_SCHEMA views). Like this:
select object_name(id) as [TableName], * from dbo.syscolumns
Hope this helps.
*mike hodgson*
blog: http://sqlnerd.blogspot.com
Diane wrote:

>Hello.
>Thank you in advance for reading this question.
>I posted this question in the newbie area, but have not had a response.
>Is there a way to get all the fields for all user tables that are returned
>when you do sp_help tablename? I think I have found the columns I need in
>'syscolumns' but it is not clear to me how to link the table names to that
>syscolumns table to get what I need.
>My apolgies for the newbie question. Any help is appreciated!
>Diane
>
>|||> Is there a way to get all the fields for all user tables that are returned
> when you do sp_help tablename? I think I have found the columns I need in
> 'syscolumns'
I strongly recommend avoiding the sys tables when possible.
How about http://www.aspfaq.com/2177 ?|||my choice is
sp_msforeachtable @.command1 = "sp_help '?' "
--
Regards
R.D
--Knowledge gets doubled when shared
"Aaron Bertrand [SQL Server MVP]" wrote:

> I strongly recommend avoiding the sys tables when possible.
> How about http://www.aspfaq.com/2177 ?
>
>|||> my choice is
> sp_msforeachtable @.command1 = "sp_help '?' "
Well, sp_msforeachtable is convenient, sure. However I have a two issues
with this approach:
(a) it returns a resultset for each table, whereas with
INFORMATION_SCHEMA.COLUMNS you can easily get all the results in a single
set.
(b) it is an undocumented and unsupported feature, so its behavior can
change or it could disappear from the product altogether with a service
pack, security release or new version of SQL Server.

Fetch the Report - Server Directory (and Items) - Structure programmaticaly in C-Sharp

Good afternoon
Is it possible to fetch the Report - Server Directory (and Items) -
Structure programmaticaly in C-Sharp for the specific user? (to represent in
a Menuetree)
Can someone point me a possible way?
thanks in advance
wishes RalphHere is some code I use to return a 'List' I myself will be adding the
hierarchy soon. Perhaps this will help:
public ETReportItems GetReports()
{
//Not really of much use in our initial release, this method is intended
to provide a 'list' of all reports
//available to a consumer. At this point, it just returns a list of all
reports on the server. We need to
//decide on a folder hierarchy that will be constrasted again the
ETPrincipal object, which will return only
//those reports visible to a given consumer.
CatalogItem[] reportServerItems = null;
ETReportItems reportItems = new ETReportItems();
SearchCondition any = new SearchCondition();
any.ConditionSpecified = false; //Causes a search for all items
any.Name = "Name"; //We could search by a number of items, any one is fine
SearchCondition[] allItems = new SearchCondition[1];
allItems[0] = any;
try
{
reportServerItems = reportingService.FindItems(ReportServicesRoot,
BooleanOperatorEnum.Or, allItems);
}
catch (SoapException e)
{
//TODO: How are we supposed to report exceptions?
throw e;
}
//Now we will determine which of these are reports
if (reportServerItems != null)
{
//We have at least one report server item
ETReportItem reportItem = null;
foreach (CatalogItem catalogItem in reportServerItems)
{
if (catalogItem.Type == ItemTypeEnum.Report && !catalogItem.Hidden)
{
//This item happens to be a report
//TODO: we still need to implement authorization, filtering of
subreports (if we can't insure they will never be used)
//and add additional properties we want to return
reportItem = new ETReportItem();
reportItem.description = catalogItem.Description;
reportItem.id = catalogItem.ID;
reportItem.name = catalogItem.Name;
reportItem.path = catalogItem.Path;
reportItems.AddItem(reportItem);
}
}
}
return reportItems;
}
"Ralph Hüttenmoser" wrote:
> Good afternoon
> Is it possible to fetch the Report - Server Directory (and Items) -
> Structure programmaticaly in C-Sharp for the specific user? (to represent in
> a Menuetree)
> Can someone point me a possible way?
> thanks in advance
> wishes Ralph
>
>
>|||Thank you Herman|||Hey Herman
what do you mean about "ETReportItems"
thanks Ralph|||ETReports is just my type (a collection) of report items as opposed to
Microsoft's (which is an array) CatalogItem[].
"Ralph Hüttenmoser" wrote:
> Hey Herman
> what do you mean about "ETReportItems"
> thanks Ralph
>
>|||thanks Herman

Favorite reports

Is there any to let users have a folder of favorite reports?
I tried using "My Reports" folder, but the security required to allow a
user to see the option to create a linked report created too much of a
security risk.
Any ideas?Users could use the IE favorites, create a group there and simply add the
reports as you would any other web site...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"John Geddes" <john_g@.alamode.com> wrote in message
news:OxZbNxB9EHA.2900@.TK2MSFTNGP09.phx.gbl...
> Is there any to let users have a folder of favorite reports?
> I tried using "My Reports" folder, but the security required to allow a
> user to see the option to create a linked report created too much of a
> security risk.
> Any ideas?
>

Fastest way to delete hundreds of table triggers and hundreds of stored procedures?

How can i delete all user stored procedures and all table triggers very fast
in
a single database?

Thank youYou can run the following script in Query Analyzer. Be certain you are in
the correct database.

USE MyDatabase
DECLARE @.DropStatement nvarchar(4000)
DECLARE DropStatements CURSOR
LOCAL FAST_FORWARD READ_ONLY FOR
SELECT N'DROP ' +
CASE xtype
WHEN 'P' THEN N'PROCEDURE '
WHEN 'TR' THEN N'TRIGGER '
END +
QUOTENAME(USER_NAME(uid)) +
N'.' +
QUOTENAME(name)
FROM sysobjects
WHERE xtype IN('P', 'TR')
OPEN DropStatements
WHILE 1 = 1
BEGIN
FETCH NEXT FROM DropStatements INTO @.DropStatement
IF @.@.FETCH_STATUS = -1 BREAK
EXEC(@.DropStatement)
END
CLOSE DropStatements
DEALLOCATE DropStatements

--
Hope this helps.

Dan Guzman
SQL Server MVP

"serge" <sergea@.nospam.ehmail.com> wrote in message
news:VRP%c.25102$lP4.1520160@.news20.bellglobal.com ...
> How can i delete all user stored procedures and all table triggers very
> fast
> in
> a single database?
> Thank you|||I will try this today.

Thank you

> You can run the following script in Query Analyzer. Be certain you are in
> the correct database.
> USE MyDatabase
> DECLARE @.DropStatement nvarchar(4000)
> DECLARE DropStatements CURSOR
> LOCAL FAST_FORWARD READ_ONLY FOR
> SELECT N'DROP ' +
> CASE xtype
> WHEN 'P' THEN N'PROCEDURE '
> WHEN 'TR' THEN N'TRIGGER '
> END +
> QUOTENAME(USER_NAME(uid)) +
> N'.' +
> QUOTENAME(name)
> FROM sysobjects
> WHERE xtype IN('P', 'TR')
> OPEN DropStatements
> WHILE 1 = 1
> BEGIN
> FETCH NEXT FROM DropStatements INTO @.DropStatement
> IF @.@.FETCH_STATUS = -1 BREAK
> EXEC(@.DropStatement)
> END
> CLOSE DropStatements
> DEALLOCATE DropStatements|||"serge" <sergea@.nospam.ehmail.com> wrote in message
news:oEg0d.30007$lP4.1961638@.news20.bellglobal.com ...
> I will try this today.

As we discover Bank of America goes offline since their databases somehow
lost their entire schema in a hacker attack. :-)

> Thank you
> > You can run the following script in Query Analyzer. Be certain you are
in
> > the correct database.
> > USE MyDatabase
> > DECLARE @.DropStatement nvarchar(4000)
> > DECLARE DropStatements CURSOR
> > LOCAL FAST_FORWARD READ_ONLY FOR
> > SELECT N'DROP ' +
> > CASE xtype
> > WHEN 'P' THEN N'PROCEDURE '
> > WHEN 'TR' THEN N'TRIGGER '
> > END +
> > QUOTENAME(USER_NAME(uid)) +
> > N'.' +
> > QUOTENAME(name)
> > FROM sysobjects
> > WHERE xtype IN('P', 'TR')
> > OPEN DropStatements
> > WHILE 1 = 1
> > BEGIN
> > FETCH NEXT FROM DropStatements INTO @.DropStatement
> > IF @.@.FETCH_STATUS = -1 BREAK
> > EXEC(@.DropStatement)
> > END
> > CLOSE DropStatements
> > DEALLOCATE DropStatements|||Well, at least the tables are still there :-)

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:Xxh0d.21421$2s.7550@.twister.nyroc.rr.com...
> "serge" <sergea@.nospam.ehmail.com> wrote in message
> news:oEg0d.30007$lP4.1961638@.news20.bellglobal.com ...
>> I will try this today.
>
> As we discover Bank of America goes offline since their databases somehow
> lost their entire schema in a hacker attack. :-)
>
>>
>> Thank you
>>
>> > You can run the following script in Query Analyzer. Be certain you are
> in
>> > the correct database.
>>> > USE MyDatabase
>> > DECLARE @.DropStatement nvarchar(4000)
>> > DECLARE DropStatements CURSOR
>> > LOCAL FAST_FORWARD READ_ONLY FOR
>> > SELECT N'DROP ' +
>> > CASE xtype
>> > WHEN 'P' THEN N'PROCEDURE '
>> > WHEN 'TR' THEN N'TRIGGER '
>> > END +
>> > QUOTENAME(USER_NAME(uid)) +
>> > N'.' +
>> > QUOTENAME(name)
>> > FROM sysobjects
>> > WHERE xtype IN('P', 'TR')
>> > OPEN DropStatements
>> > WHILE 1 = 1
>> > BEGIN
>> > FETCH NEXT FROM DropStatements INTO @.DropStatement
>> > IF @.@.FETCH_STATUS = -1 BREAK
>> > EXEC(@.DropStatement)
>> > END
>> > CLOSE DropStatements
>> > DEALLOCATE DropStatements
>>
>>|||What's the fastest way to delete tables? :)

> Well, at least the tables are still there :-)|||I ran this and it was fast, it's what i was looking for.

I am trying to figure out how to delete ONLY the user objects and not the
system objects.

By changing the WHERE condition to become:

WHERE xtype IN('P', 'TR') AND OBJECTPROPERTY (Id, 'IsMSShipped') = 0

I tried running it and it seems it did not delete the Stored Procedures with
TYPE = System.

I just want to make sure this condition I am using :

AND OBJECTPROPERTY (Id, 'IsMSShipped') = 0

is the right one, that I am not screwing other things unknowingly?

Thank you

"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:%uQ%c.9387$yp2.8834@.newssvr30.news.prodigy.co m...
> You can run the following script in Query Analyzer. Be certain you are in
> the correct database.
> USE MyDatabase
> DECLARE @.DropStatement nvarchar(4000)
> DECLARE DropStatements CURSOR
> LOCAL FAST_FORWARD READ_ONLY FOR
> SELECT N'DROP ' +
> CASE xtype
> WHEN 'P' THEN N'PROCEDURE '
> WHEN 'TR' THEN N'TRIGGER '
> END +
> QUOTENAME(USER_NAME(uid)) +
> N'.' +
> QUOTENAME(name)
> FROM sysobjects
> WHERE xtype IN('P', 'TR')
> OPEN DropStatements
> WHILE 1 = 1
> BEGIN
> FETCH NEXT FROM DropStatements INTO @.DropStatement
> IF @.@.FETCH_STATUS = -1 BREAK
> EXEC(@.DropStatement)
> END
> CLOSE DropStatements
> DEALLOCATE DropStatements
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "serge" <sergea@.nospam.ehmail.com> wrote in message
> news:VRP%c.25102$lP4.1520160@.news20.bellglobal.com ...
> > How can i delete all user stored procedures and all table triggers very
> > fast
> > in
> > a single database?
> > Thank you|||>> Well, at least the tables are still there :-)

"serge" <sergea@.nospam.ehmail.com> wrote in news:BRJ0d.35921$lP4.2446119
@.news20.bellglobal.com:

> What's the fastest way to delete tables? :)

USE master
DROP DATABASE CriticalFinancialInfo
GO|||> AND OBJECTPROPERTY (Id, 'IsMSShipped') = 0

Yes, this is correct. I should have included this in the script I posted.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"serge" <sergea@.nospam.ehmail.com> wrote in message
news:tdK0d.36158$lP4.2450872@.news20.bellglobal.com ...
>I ran this and it was fast, it's what i was looking for.
> I am trying to figure out how to delete ONLY the user objects and not the
> system objects.
> By changing the WHERE condition to become:
>
> WHERE xtype IN('P', 'TR') AND OBJECTPROPERTY (Id, 'IsMSShipped') = 0
> I tried running it and it seems it did not delete the Stored Procedures
> with
> TYPE = System.
> I just want to make sure this condition I am using :
> AND OBJECTPROPERTY (Id, 'IsMSShipped') = 0
> is the right one, that I am not screwing other things unknowingly?
> Thank you
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:%uQ%c.9387$yp2.8834@.newssvr30.news.prodigy.co m...
>> You can run the following script in Query Analyzer. Be certain you are
>> in
>> the correct database.
>>
>> USE MyDatabase
>> DECLARE @.DropStatement nvarchar(4000)
>> DECLARE DropStatements CURSOR
>> LOCAL FAST_FORWARD READ_ONLY FOR
>> SELECT N'DROP ' +
>> CASE xtype
>> WHEN 'P' THEN N'PROCEDURE '
>> WHEN 'TR' THEN N'TRIGGER '
>> END +
>> QUOTENAME(USER_NAME(uid)) +
>> N'.' +
>> QUOTENAME(name)
>> FROM sysobjects
>> WHERE xtype IN('P', 'TR')
>> OPEN DropStatements
>> WHILE 1 = 1
>> BEGIN
>> FETCH NEXT FROM DropStatements INTO @.DropStatement
>> IF @.@.FETCH_STATUS = -1 BREAK
>> EXEC(@.DropStatement)
>> END
>> CLOSE DropStatements
>> DEALLOCATE DropStatements
>>
>> --
>> Hope this helps.
>>
>> Dan Guzman
>> SQL Server MVP
>>
>> "serge" <sergea@.nospam.ehmail.com> wrote in message
>> news:VRP%c.25102$lP4.1520160@.news20.bellglobal.com ...
>> > How can i delete all user stored procedures and all table triggers very
>> > fast
>> > in
>> > a single database?
>>> > Thank you
>>>>>
>>|||My quest continues, maybe i should explain what i am trying to achieve.

There is a database with 3000+ Stored Procedures. We give copies to other
people
and we continue making updates to the *development* database. When we want
to give the other people our latest stored procedures, we have code that
deletes all
stored procedures one by one, thus taking maybe 30 minutes to delete.
Then we recreate all the SPs.

Now i wanted to find out if there was a way to speed this process, i
originally thought
that deleting all SPs one shot could do the trick. But the further i analyze
it, i see some complications.

For example, the other people could very well have created their own SPs,
how can
i NOT delete those SPs?

Do you or anyone else have any idea how i can accomplish this?

Thank you

> > AND OBJECTPROPERTY (Id, 'IsMSShipped') = 0
> Yes, this is correct. I should have included this in the script I posted.|||"serge" <sergea@.nospam.ehmail.com> wrote in
news:zZM0d.39176$lP4.2491585@.news20.bellglobal.com :

> My quest continues, maybe i should explain what i am trying to
> achieve.
> There is a database with 3000+ Stored Procedures. We give copies to
> other people
> and we continue making updates to the *development* database. When we
> want to give the other people our latest stored procedures, we have
> code that deletes all
> stored procedures one by one, thus taking maybe 30 minutes to delete.
> Then we recreate all the SPs.
> Now i wanted to find out if there was a way to speed this process, i
> originally thought
> that deleting all SPs one shot could do the trick. But the further i
> analyze it, i see some complications.
> For example, the other people could very well have created their own
> SPs, how can
> i NOT delete those SPs?

You *need* version control.

I suppose the simplest way without spending any money would be to add a
table with the current version number of each stored procedure. Your
"update db with new stored procedures" would have to check that version
number and decide whether to drop and recreate the stored procedure.

Taking the idea a step further, the version table would also contain the
complete source of the stored procedure. Then another stored procedure
could cursor over the table, doing the drops and creates. Distributing
your new procedures would involve exporting this table to an external file
(say an MDB) and distributing it to the other recipients.|||serge (sergea@.nospam.ehmail.com) writes:
> My quest continues, maybe i should explain what i am trying to achieve.
> There is a database with 3000+ Stored Procedures. We give copies to
> other people and we continue making updates to the *development*
> database. When we want to give the other people our latest stored
> procedures, we have code that deletes all stored procedures one by one,
> thus taking maybe 30 minutes to delete. Then we recreate all the SPs.
> Now i wanted to find out if there was a way to speed this process, i
> originally thought that deleting all SPs one shot could do the trick.
> But the further i analyze it, i see some complications.
> For example, the other people could very well have created their own
> SPs, how can i NOT delete those SPs?

First of all, does their license permit them to add their own stored
procedures?

As Ross said, you need version control.

In our shop we have all our stored procedures, triggers, tables, in short
all database objects under version control in SourceSafe, and SourceSafe
is the master for all building efforts.

To build and install we have a toolset, called AbaPerls. One tool is
DBBUILD which builds an entire database from scripts, that is tables,
stored procedures, el todo. DBBUILD is also what we add when we add
a new component, or subsystem as we call it, to the database. Then we
have another tool DBUPDGEN which reads SourceSafe, and finds all changes
between two labels and that produces an update script. (For changed tables
you get a template to move over the data, but in most cases you have to
modify the generated code.) Furthermore, AbaPerls has its own set of
tables, so we know what we have loaded into a database. There is also
a stored procedure which lists mismatches between AbaPerls and SQL Server's
own system tables. Customer-added code would end up there.

I have made AbaPerls available as freeware on http://www.abaris.se/abaperls/
But as Ross outlined, you can achieve something a lot simpler with quite
easy means, and it may be enough for your organization.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||I understand and I do agree your idea is very good. But at the moment, I am
trying
and hoping that there is a simpler way to rely on the WHERE Condition and
using
some custom field or something in the sysobjects table to decide whether
that SP
is one of our own SP and not delete it.

Thanks for your idea. I'll have to keep this in mind, I might have to use
this
approach in the future.

> You *need* version control.
> I suppose the simplest way without spending any money would be to add a
> table with the current version number of each stored procedure. Your
> "update db with new stored procedures" would have to check that version
> number and decide whether to drop and recreate the stored procedure.
> Taking the idea a step further, the version table would also contain the
> complete source of the stored procedure. Then another stored procedure
> could cursor over the table, doing the drops and creates. Distributing
> your new procedures would involve exporting this table to an external file
> (say an MDB) and distributing it to the other recipients.|||We do have SourceSafe on our end but not to go into details here,
that wouldn't be a viable solution at the time being. Maybe in the future.

Like i just replied to Ross, at the moment, I am trying and hoping that
there is a simpler way to rely on the WHERE Condition and using
some custom field or something in the sysobjects table to decide whether
that SP is one of our own SP and not delete it.

It's very appreciated that you've made your AbaPerls toolset freeware for
everyone to use. I have taken note of the link and I will have to look at
this
in great length in the future. At this moment, it's very hard to switch to
using
tools for what i require. If i can get away with a simple WHERE condition
to NOT delete SPs not created by us, I'll be very interested to use.

Due to time constraints, I don't want to add a new project to work on.
This task of deleting SPs fast is already not part of my regular work, add
to that I have to figure out ways to improve some slow SPs we have (again
not part of my regular work).

Thank you

> First of all, does their license permit them to add their own stored
> procedures?
> As Ross said, you need version control.
> In our shop we have all our stored procedures, triggers, tables, in short
> all database objects under version control in SourceSafe, and SourceSafe
> is the master for all building efforts.
> To build and install we have a toolset, called AbaPerls. One tool is
> DBBUILD which builds an entire database from scripts, that is tables,
> stored procedures, el todo. DBBUILD is also what we add when we add
> a new component, or subsystem as we call it, to the database. Then we
> have another tool DBUPDGEN which reads SourceSafe, and finds all changes
> between two labels and that produces an update script. (For changed tables
> you get a template to move over the data, but in most cases you have to
> modify the generated code.) Furthermore, AbaPerls has its own set of
> tables, so we know what we have loaded into a database. There is also
> a stored procedure which lists mismatches between AbaPerls and SQL
Server's
> own system tables. Customer-added code would end up there.
> I have made AbaPerls available as freeware on
http://www.abaris.se/abaperls/
> But as Ross outlined, you can achieve something a lot simpler with quite
> easy means, and it may be enough for your organization.|||serge (sergea@.nospam.ehmail.com) writes:
> Like i just replied to Ross, at the moment, I am trying and hoping that
> there is a simpler way to rely on the WHERE Condition and using
> some custom field or something in the sysobjects table to decide whether
> that SP is one of our own SP and not delete it.

There is no such custom field. Possibly you could add some condition which
looked in syscomments for things you recognize, but that would be completely
bizarre to do.

You would have to have list of known procedures to delete. Then again, that
is not very difficult to make effecient:

CREATE PROCEDURE drop_till_you_bop @.procs ntext AS
DECLARE @.proc sysname
DECLARE drop_cur INSENSITIVE CURSOR FOR
SELECT nstr FROM iter_charlist_to_tbl(@.procs, DEFAULT) i
JOIN sysobjects o ON i.nstr = o.name
WHERE o.xtype = 'P'
OPEN drop_cur
WHILE 1 = 1
FETCH drop_cur INTO @.proc
IF @.@.fetch_status <> 0
BREAK
EXEC ('DROP PROCEDURE ' + @.proc)
END
DEALLOCATE drop_cur

iter_charlist_to_tbl is on
http://www.sommarskog.se/arrays-in-...list-of-strings

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||As already mentioned, a manifest and/or version control to probably the best
long term solution. In the interim. you might consider adding extended
properties to the objects you own so that you can more easily identify
these. You'll need to include the extended properties in your DDL scripts.

CREATE PROC MyProcedure
AS
SELECT 'MyProcedure'
GO

EXEC sp_addextendedproperty
'OwnedBy',
'MyApplication',
'USER',
'dbo',
'PROCEDURE',
'MyProcedure'
GO

--this
SELECT *
FROM sysobjects o
JOIN ::fn_listextendedproperty(
'OwnedBy',
'USER',
'dbo',
'PROCEDURE',
NULL,
NULL,
NULL
) ep ON o.name = ep.objname
WHERE o.xtype = 'P' AND
ep.value = 'MyApplication'

--
Hope this helps.

Dan Guzman
SQL Server MVP

"serge" <sergea@.nospam.ehmail.com> wrote in message
news:zZM0d.39176$lP4.2491585@.news20.bellglobal.com ...
> My quest continues, maybe i should explain what i am trying to achieve.
> There is a database with 3000+ Stored Procedures. We give copies to other
> people
> and we continue making updates to the *development* database. When we want
> to give the other people our latest stored procedures, we have code that
> deletes all
> stored procedures one by one, thus taking maybe 30 minutes to delete.
> Then we recreate all the SPs.
> Now i wanted to find out if there was a way to speed this process, i
> originally thought
> that deleting all SPs one shot could do the trick. But the further i
> analyze
> it, i see some complications.
> For example, the other people could very well have created their own SPs,
> how can
> i NOT delete those SPs?
> Do you or anyone else have any idea how i can accomplish this?
> Thank you
>
>> > AND OBJECTPROPERTY (Id, 'IsMSShipped') = 0
>>
>> Yes, this is correct. I should have included this in the script I
>> posted.|||Thanks again for the post. I'll have to look into this more closely
in the next few days (hopefully).

"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns95636012ECE1Yazorman@.127.0.0.1...
> serge (sergea@.nospam.ehmail.com) writes:
> > Like i just replied to Ross, at the moment, I am trying and hoping that
> > there is a simpler way to rely on the WHERE Condition and using
> > some custom field or something in the sysobjects table to decide whether
> > that SP is one of our own SP and not delete it.
> There is no such custom field. Possibly you could add some condition which
> looked in syscomments for things you recognize, but that would be
completely
> bizarre to do.
> You would have to have list of known procedures to delete. Then again,
that
> is not very difficult to make effecient:
> CREATE PROCEDURE drop_till_you_bop @.procs ntext AS
> DECLARE @.proc sysname
> DECLARE drop_cur INSENSITIVE CURSOR FOR
> SELECT nstr FROM iter_charlist_to_tbl(@.procs, DEFAULT) i
> JOIN sysobjects o ON i.nstr = o.name
> WHERE o.xtype = 'P'
> OPEN drop_cur
> WHILE 1 = 1
> FETCH drop_cur INTO @.proc
> IF @.@.fetch_status <> 0
> BREAK
> EXEC ('DROP PROCEDURE ' + @.proc)
> END
> DEALLOCATE drop_cur
> iter_charlist_to_tbl is on
> http://www.sommarskog.se/arrays-in-...list-of-strings
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||Looks interesting. Having extended properties to the SP.
I'll have to investigate this further, hopefully soon.

Thank you again.

"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:rnh1d.10373$yp2.9988@.newssvr30.news.prodigy.c om...
> As already mentioned, a manifest and/or version control to probably the
best
> long term solution. In the interim. you might consider adding extended
> properties to the objects you own so that you can more easily identify
> these. You'll need to include the extended properties in your DDL
scripts.
> CREATE PROC MyProcedure
> AS
> SELECT 'MyProcedure'
> GO
> EXEC sp_addextendedproperty
> 'OwnedBy',
> 'MyApplication',
> 'USER',
> 'dbo',
> 'PROCEDURE',
> 'MyProcedure'
> GO
> --this
> SELECT *
> FROM sysobjects o
> JOIN ::fn_listextendedproperty(
> 'OwnedBy',
> 'USER',
> 'dbo',
> 'PROCEDURE',
> NULL,
> NULL,
> NULL
> ) ep ON o.name = ep.objname
> WHERE o.xtype = 'P' AND
> ep.value = 'MyApplication'

Failure to insert multiple rows into SQL database

I have an app that imports data from a csv file into a dataset. The user views the dataset and then decides to import the data into the database by clicking the code below.

I am getting the error message below when the app gets to the line 'objCommand.ExecuteNonQuery()'

"Message="The variable name '@.PartNumber' has already been declared. Variable names must be unique within a query batch or stored procedure.
Incorrect syntax near '?'."

My questions are:

1) Why is the code failing at this point ? ( i have hard coded the part number and part name to test my code)

2) How do i pass the actual value of PartNumber and PartName from each datarow into the parameter ?

Private Sub btnImport_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnImport.Click

Dim objCommand As SqlCommand = New SqlCommand

Dim datatable As DataTable

DataTable = dsimport.Tables(0)

Dim dataRow As DataRow

'Setup SQLcommand

objCommand.Connection = objConnection

objCommand.CommandText = "INSERT into Part (PartNumber,PartName) VALUES (?,?)"

objCommand.CommandType = CommandType.Text

For Each dataRow In dsimport.Tables(0).Rows

'Parameter for the PartNumber field...

objCommand.Parameters.AddWithValue("PartNumber", "2R8T-14A005-AA")

'Parameter for the PartName field...

objCommand.Parameters.AddWithValue("PartName", "Test3")

Next

'Open the connection...

objConnection.Open()

'Execute the SqlCommand object to update the data...

objCommand.ExecuteNonQuery()

'Close the connection...

objConnection.Close()

objCommand = Nothing

objConnection = Nothing

End Sub

This article talks about where you can use (?) placeholder.

http://authors.aspalliance.com/aspxtreme/adonet/usingstoredprocedureswithcommand.aspx

In your case you can use OleDbCommand to accomplish the same thing. I have an example below in which I read couple of rows from an excel spreadsheet and insert them into SQL Database.

-

Dim cn As New OleDbConnection, cn1 As New OleDbConnection
Dim adapter As New OleDbDataAdapter
Dim dtset As New DataSet
Dim cmd As New OleDbCommand
Dim dr As DataRow

cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data source= C:\Test.xls;" + "Extended Properties=""Excel 8.0;HDR=Yes;"""
cn1.ConnectionString = "Provider=SQLOLEDB;Data Source=.;Integrated Security=SSPI;"

cn.Open()
cn1.Open()

cmd.Connection = cn
cmd.CommandText = "Select * from TestTable"
adapter.SelectCommand = cmd
adapter.Fill(dtset)

cmd.Connection = cn1
cmd.CommandText = "Insert into Parts (PartNumber,PartName) Values(?,?)"
cmd.CommandType = CommandType.Text

cmd.Parameters.Add("PartNumber", OleDbType.VarChar, 20)
cmd.Parameters.Add("PartName", OleDbType.VarChar, 20)
For Each dr In dtset.Tables(0).Rows
cmd.Parameters("PartNumber").Value = dr(0).ToString()
cmd.Parameters("PartName").Value = dr(1).ToString()
cmd.ExecuteNonQuery()
Next

cn.Close()
cn1.Close()

-

Hope this helps

|||

Using SqlCommand you can use this example:

Dim cn As New OleDbConnection
Dim sqlcn As New SqlConnection
Dim adapter As New OleDbDataAdapter
Dim dtset As New DataSet
Dim cmd As New OleDbCommand
Dim sqlcmd As New SqlCommand
Dim dr As DataRow

cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data source= C:\Test.xls;" + "Extended Properties=""Excel 8.0;HDR=Yes;"""
sqlcn.ConnectionString = "Data Source=.;Integrated Security=SSPI;"

cn.Open()
sqlcn.Open()

cmd.Connection = cn
cmd.CommandText = "Select * from TestTable"
adapter.SelectCommand = cmd
adapter.Fill(dtset)

sqlcmd.Connection = sqlcn
sqlcmd.CommandText = "Insert into Parts (PartNumber,PartName) Values(@.a,@.b)"
sqlcmd.CommandType = CommandType.Text

sqlcmd.Parameters.Add("@.a", SqlDbType.VarChar, 20)
sqlcmd.Parameters.Add("@.b", SqlDbType.VarChar, 20)

For Each dr In dtset.Tables(0).Rows
sqlcmd.Parameters("@.a").Value = dr(0).ToString()
sqlcmd.Parameters("@.b").Value = dr(1).ToString()
sqlcmd.ExecuteNonQuery()
Next

cn.Close()
sqlcn.Close()

Hope this helps

Failure setting security rights on user account SQLServer2005BrowserUser${computerName}

I'm trying to install SQL Server 2005 Express on a Windows 2000 server, but I'm getting the following error message:

"Failure setting security rights on user account SQLServer2005BrowserUser${computerName}"

Can anyone help me please?

Help!! Need to get the sql server installed so I can demo to my client!

Failure Sending Mail

Hello all,
I have a particular problem with subscriptions when the subscription is
created by a user who is not an administrator. The problem points to a
permissions issue but I've tried several ways to resolve this without any
luck.
If the subscription is run only selecting a link to the report the
subscription works fine. I've gave the user the same role based permissions
as myself within the report server and set them up with Read & Execute, List
Folder Content and Read access on the Reports and ReportServer within IIS and
the folders are set to use Integrated Windows authentication.
The user can run the report OK and export to any of teh available options
from the list. Any help with this would be much appreciated.
Regards
MikeOn Feb 8, 6:52 am, MikeD <M...@.discussions.microsoft.com> wrote:
> Hello all,
> I have a particular problem with subscriptions when the subscription is
> created by a user who is not an administrator. The problem points to a
> permissions issue but I've tried several ways to resolve this without any
> luck.
> If the subscription is run only selecting a link to the report the
> subscription works fine. I've gave the user the same role based permissions
> as myself within the report server and set them up with Read & Execute, List
> Folder Content and Read access on the Reports and ReportServer within IIS and
> the folders are set to use Integrated Windows authentication.
> The user can run the report OK and export to any of teh available options
> from the list. Any help with this would be much appreciated.
> Regards
> Mike
I had an application that would create a subscription, and what I
found was this:
To create a subscription programmatically the user you are using to
create the subscription must have Content Manager permissions. This
is a System level role that is available under Site Settings on the
Home page, as opposed to the standard roles on the folders.|||Thanks for the info. The user in question has Content Manager role set but
the subscription is not being set programatically, the user is creating the
subscription via the Report Manager web page. As mentioned it only fails when
an attachment is done, it works OK when only a link to th report is included.
"DMcM" wrote:
> On Feb 8, 6:52 am, MikeD <M...@.discussions.microsoft.com> wrote:
> > Hello all,
> >
> > I have a particular problem with subscriptions when the subscription is
> > created by a user who is not an administrator. The problem points to a
> > permissions issue but I've tried several ways to resolve this without any
> > luck.
> >
> > If the subscription is run only selecting a link to the report the
> > subscription works fine. I've gave the user the same role based permissions
> > as myself within the report server and set them up with Read & Execute, List
> > Folder Content and Read access on the Reports and ReportServer within IIS and
> > the folders are set to use Integrated Windows authentication.
> >
> > The user can run the report OK and export to any of teh available options
> > from the list. Any help with this would be much appreciated.
> >
> > Regards
> >
> > Mike
> I had an application that would create a subscription, and what I
> found was this:
> To create a subscription programmatically the user you are using to
> create the subscription must have Content Manager permissions. This
> is a System level role that is available under Site Settings on the
> Home page, as opposed to the standard roles on the folders.
>

failure in starting the process for the user instance

High everybody,

I have a set of winforms (.NET 2.0 assemblies) ( 6 total ) that each tries to open a connection to the same Database file using the same connection string. In the connection string, UserInstance is set to true.

If each winform is started manually after opening the session on the computer, the connection to the database performs well for every winform.

If the same winforms are automatically started at the opening of the user session, the the following error is reported by each application when trying to open the connection :

Failed to generate a user instance of SQL Server due to a failure in starting the process for the user instance. The connection will be closed.

The only way to overcome this problem is :

to stop the winforms, to stop the SQLExpress service, to kill the remaining sqlserv.exe running task, then restarting SQLExpress service, and finally manualy start the winforms.

Any help would be greatly appreciated.

Hi Henri,

When you say "a set of winforms" do you mean a single applications or six different applications?

Are all these forms being opened by the same user?

Is the database embedded in the application or is it located somewhere else? (If somewhere else, where?)

You can frequently find more detailed information about this error in the User Instance error log which is located in the user profile directories of the user who is starting the user instance: C:\Documents and Settings\<user name>\Local Settings\Application Data\Microsoft\Microsoft SQL Server Data\SQLEXPRESS. Check out this file and let us know what it says.

Regards,

Mike Wachal
SQL Express team

-
Check out my tips for getting your answer faster and how to ask a good question: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=307712&SiteID=1

|||

I get a similar problem. I get this message anytime I try to open the database from within VB Express. I was following Lesson 08 by trying to Add a SQL Server Item Template to my project.

Here is what I found in my logs.

2006-04-08 17:11:44.62 Server Dedicated administrator connection support was not started because it is not available on this edition of SQL Server. This is an informational message only. No user action is required.

2006-04-08 17:11:44.07 spid4s Starting up database 'mssqlsystemresource'.
2006-04-08 17:11:44.32 spid4s Error: 15466, Severity: 16, State: 1.
2006-04-08 17:11:44.32 spid4s An error occurred during decryption.
2006-04-08 17:11:44.50 spid4s The current master key cannot be decrypted. The error was ignored because the FORCE option was specified.

|||

Does this only happen when connecting to the user instance? Can you connect to the main instance?

Thanks
Laurentiu

|||

Hi Mike,

Thank you for your help and sorry for the late answer, but I am still on holidays. I will try to get the User Instance log at the customer site.

my application consists of six different exe that are started at the same time when opening the user session ( on a windows 2000 server). Each of these exe makes a connection to the same database file using the same connection string. When the user session is opened, there are other softwares that are automatically started which makes the CPU usage about 100% for a few minutes.

However, when each exe is started manually one after the other, the connection to the User Instance is done without error.

regards,

Henri d'Orgeval

|||

Hi Mike,

here is the error log content :

2006-04-13 10:41:30.09 Server Microsoft SQL Server 2005 - 9.00.1314.06 (Intel X86)
Sep 2 2005 21:10:31
Copyright (c) 1988-2005 Microsoft Corporation
Express Edition on Windows NT 5.0 (Build 2195: Service Pack 4)

2006-04-13 10:41:30.09 Server (c) 2005 Microsoft Corporation.
2006-04-13 10:41:30.09 Server All rights reserved.
2006-04-13 10:41:30.09 Server Server process ID is 2468.
2006-04-13 10:41:30.09 Server Logging SQL Server messages in file 'C:\Documents and Settings\AdminIt\Local Settings\Application Data\Microsoft\Microsoft SQL Server Data\SQLEXPRESS\error.log'.
2006-04-13 10:41:30.09 Server Registry startup parameters:
2006-04-13 10:41:30.09 Server -d C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf
2006-04-13 10:41:30.09 Server -e C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG
2006-04-13 10:41:30.09 Server -l C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf
2006-04-13 10:41:30.09 Server Command Line Startup Parameters:
2006-04-13 10:41:30.09 Server -d C:\Documents and Settings\AdminIt\Local Settings\Application Data\Microsoft\Microsoft SQL Server Data\SQLEXPRESS\master.mdf
2006-04-13 10:41:30.09 Server -l C:\Documents and Settings\AdminIt\Local Settings\Application Data\Microsoft\Microsoft SQL Server Data\SQLEXPRESS\mastlog.ldf
2006-04-13 10:41:30.09 Server -e C:\Documents and Settings\AdminIt\Local Settings\Application Data\Microsoft\Microsoft SQL Server Data\SQLEXPRESS\error.log
2006-04-13 10:41:30.09 Server -c
2006-04-13 10:41:30.09 Server -S SQLEXPRESS
2006-04-13 10:41:30.09 Server -s F2DAFDAF-4081-43
2006-04-13 10:41:30.09 Server -w 60
2006-04-13 10:41:30.09 Server SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
2006-04-13 10:41:30.09 Server Detected 2 CPUs. This is an informational message; no user action is required.
2006-04-13 10:41:30.46 Server Using dynamic lock allocation. Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node. This is an informational message only. No user action is required.
2006-04-13 10:41:30.79 Server Database mirroring has been enabled on this instance of SQL Server.
2006-04-13 10:41:30.81 spid5s Starting up database 'master'.
2006-04-13 10:41:30.90 spid5s Recovery is writing a checkpoint in database 'master' (1). This is an informational message only. No user action is required.
2006-04-13 10:41:31.04 spid5s SQL Trace ID 1 was started by login "sa".
2006-04-13 10:41:31.10 spid5s Starting up database 'mssqlsystemresource'.
2006-04-13 10:41:31.21 Server Server local connection provider is ready to accept connection on [ \\.\pipe\F2DAFDAF-4081-43\tsql\query ].
2006-04-13 10:41:31.21 spid7s Starting up database 'model'.
2006-04-13 10:41:31.21 spid5s Server name is 'IT-SHAPER1\F2DAFDAF-4081-43'. This is an informational message only. No user action is required.
2006-04-13 10:41:31.21 Server Dedicated administrator connection support was not started because it is not available on this edition of SQL Server. This is an informational message only. No user action is required.
2006-04-13 10:41:31.21 Server SQL Server is now ready for client connections. This is an informational message; no user action is required.
2006-04-13 10:41:31.34 spid5s Starting up database 'msdb'.
2006-04-13 10:41:31.48 spid7s Clearing tempdb database.
2006-04-13 10:41:32.12 spid7s Starting up database 'tempdb'.
2006-04-13 10:41:32.18 spid5s Recovery is complete. This is an informational message only. No user action is required.
2006-04-13 10:41:32.18 spid10s The Service Broker protocol transport is disabled or not configured.
2006-04-13 10:41:32.18 spid10s The Database Mirroring protocol transport is disabled or not configured.
2006-04-13 10:41:32.34 spid10s Service Broker manager has started.
2006-04-13 10:41:32.54 spid51 Starting up database 'C:\PROGRAM FILES\W.A.I.S. TECHNOLOGY\DISTRIBUTION MANAGER\DISTRIBUTIONMANAGER.MDF'.

There are other error logs named errorN.log that contains the same content

Best regards,

Henri d'Orgeval

|||

This appears to be a successful errorlog. Can you post the errorlog that contains the errors you have extracted and posted in your earlier post?

Also, based on your description of the problem, have you considered starting the six instances of your program in succession, with a small delay between them. This might be a workaround until we determine the cause of this failure.

Thanks
Laurentiu

|||

Hi Henri,

I'm still not clear on when you're getting the error. You state that when each exe is started one after another the connection is done without error, when do you get the error?

As for the error log, could you confirm that you pulled that error log for the User Instance and not the main instance? The User Instance error log would be located at C:\Documents and Settings\<user>\Local Settings\Application Data\Microsoft\Microsoft SQL Server Data\SQLEXPRESS where <user> is the user that experienced the problem.

Regards,

Mike Wachal
SQL Express team

-
Please mark your thread as Answered when you get your solution.

|||

Hi Henri,

Could you give a bit more information as requested by both Laurentiu and me? It's still not clear to use how your application is actually starting and where the error is occuring.

Have you tried the process that Laurentiu suggests and did it work for you?

Regards,

Mike Wachal
SQL Express team

-
Check out my tips for getting your answer faster and how to ask a good question: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=307712&SiteID=1

|||

Hi Mike and Laurentiu,

I confirm that if each exe is started one after the other, waiting a little bit between each launch, everything is right. This is the only workaround I found that works every time.

If the 6 exes are started at the same time at the opening of the user session ( ie the 6 exes are started from the Startup folder of the User Session), then each exe reports the same error which is the one I mentioned at my first post.

To reproduce the problem the following is done :

1°) the station is rebooted,

2°) as soon as the Login/Password screen appears on the screen, the operator opens the session, which starts the 6 exe at the same time.

3°) then each exe immediately tries to open the connection by attaching the same database file and using the same connection string.

About the error log file I posted :

There are many error log files in the User Instance SQL folder, but the content of each of these error log files is similar to what I posted.

I agree with you, surprisingly it does not look like an error has occured !

I am currently rewriting the software in order to have only one exe that will start 6 different threads one after the other.

Best regards

Henri d'Orgeval

|||

I actually expect that only 5 exes are failing and the 6th succeeds. Can you please confirm this?

The process of connecting to the user instance works like this (this is a high level description):

1. if the instance exists, connect to it
2. otherwise start up the instance
3. connect to the instance

I expect all exe's try step 1 and find that there's no instance, then they all try to start it up, but only one will succeed and the others will fail. This is to be expected and can be resolved in several ways:

(a) start the exe's in succession. This was already proposed and verified to be a valid solution.
(b) start one exe first and after some delay start all the others.
(c) implement code in the exe to handle the connection failure and attempt to reconnect for several times with some delay in between.

Thanks
Laurentiu