Manage server messages shows % instead of name of item in error

When I right click on my sql server in the enterprise manager and select all
tasks --> manage server messages I am not seeing the object the error is
referring to. Instead I see a % wherever it should be inserting the object
name like below.
Incorrect value for parameter '%s'.
What do i have to do to get the server to substitute the tablename, etc. for
the % sign. Also is there anyway to see a timestamp for the errors returned.
It would be helpful to know when the error occured. Basically I am having
issues with an ASP.net page that is doing an update on a particular table. It
is not throwing an exception on the asp.net side so i wanted to look on the
sql side to see what happened. But on the sql side i can't tell because there
is no indication which table or when a particualr message in the manage
server messages tool refers too. Is there a better way to go about this?
Server is SQL server 2000.What you are seeing in Enterprise Manager is a collection of all the error
messages, that are stored in the master..sysmessages table. They are not the
errors raised by your application. When an error occurs, SQL Server will
raise those errors by substituting the parameters with appropriate values.
If you think you are experiencing an error in your application, then you
should capture the error number and the error description via the mechanism
provided by your programming language. For example, the Err object in VB.
Alternatively you can use SQL Server Profiler to see what statements are
being executed by SQL Server. You can trace the Exception event to capture
errors.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Kevin McPhail" <KevinMcPhail@.discussions.microsoft.com> wrote in message
news:2F80D3C9-FC22-4431-9F8A-EBB2FA7EF0B0@.microsoft.com...
When I right click on my sql server in the enterprise manager and select all
tasks --> manage server messages I am not seeing the object the error is
referring to. Instead I see a % wherever it should be inserting the object
name like below.
Incorrect value for parameter '%s'.
What do i have to do to get the server to substitute the tablename, etc. for
the % sign. Also is there anyway to see a timestamp for the errors returned.
It would be helpful to know when the error occured. Basically I am having
issues with an ASP.net page that is doing an update on a particular table.
It
is not throwing an exception on the asp.net side so i wanted to look on the
sql side to see what happened. But on the sql side i can't tell because
there
is no indication which table or when a particualr message in the manage
server messages tool refers too. Is there a better way to go about this?
Server is SQL server 2000.|||Narayana:
Thanks for your suggestions. Can you point me to a resource on tracing the
message with profiler. The vb app does not seem to be throwing an exception
so i am trying to figure out what is going on. I am not sure it is even
trying to write to the db at this point.
"Narayana Vyas Kondreddi" wrote:
> What you are seeing in Enterprise Manager is a collection of all the error
> messages, that are stored in the master..sysmessages table. They are not the
> errors raised by your application. When an error occurs, SQL Server will
> raise those errors by substituting the parameters with appropriate values.
> If you think you are experiencing an error in your application, then you
> should capture the error number and the error description via the mechanism
> provided by your programming language. For example, the Err object in VB.
> Alternatively you can use SQL Server Profiler to see what statements are
> being executed by SQL Server. You can trace the Exception event to capture
> errors.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Kevin McPhail" <KevinMcPhail@.discussions.microsoft.com> wrote in message
> news:2F80D3C9-FC22-4431-9F8A-EBB2FA7EF0B0@.microsoft.com...
> When I right click on my sql server in the enterprise manager and select all
> tasks --> manage server messages I am not seeing the object the error is
> referring to. Instead I see a % wherever it should be inserting the object
> name like below.
> Incorrect value for parameter '%s'.
> What do i have to do to get the server to substitute the tablename, etc. for
> the % sign. Also is there anyway to see a timestamp for the errors returned.
> It would be helpful to know when the error occured. Basically I am having
> issues with an ASP.net page that is doing an update on a particular table.
> It
> is not throwing an exception on the asp.net side so i wanted to look on the
> sql side to see what happened. But on the sql side i can't tell because
> there
> is no indication which table or when a particualr message in the manage
> server messages tool refers too. Is there a better way to go about this?
> Server is SQL server 2000.
>
>|||Could it be that you have something like 'On Error Resume Next' in your VB
app, that is silently ignoring the error?
I am not aware of any step by step instructions for using Profiler but, it
should be quite simple and intuitive once you open it. I have some related
articles at: http://vyaskn.tripod.com/analyzing_profiler_output.htm
Apart from that do checkout SQL Server Books Online.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Kevin McPhail" <KevinMcPhail@.discussions.microsoft.com> wrote in message
news:A60CD0CE-FB4B-4CB3-B74D-0226A913D920@.microsoft.com...
Narayana:
Thanks for your suggestions. Can you point me to a resource on tracing the
message with profiler. The vb app does not seem to be throwing an exception
so i am trying to figure out what is going on. I am not sure it is even
trying to write to the db at this point.
"Narayana Vyas Kondreddi" wrote:
> What you are seeing in Enterprise Manager is a collection of all the error
> messages, that are stored in the master..sysmessages table. They are not
the
> errors raised by your application. When an error occurs, SQL Server will
> raise those errors by substituting the parameters with appropriate values.
> If you think you are experiencing an error in your application, then you
> should capture the error number and the error description via the
mechanism
> provided by your programming language. For example, the Err object in VB.
> Alternatively you can use SQL Server Profiler to see what statements are
> being executed by SQL Server. You can trace the Exception event to capture
> errors.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Kevin McPhail" <KevinMcPhail@.discussions.microsoft.com> wrote in message
> news:2F80D3C9-FC22-4431-9F8A-EBB2FA7EF0B0@.microsoft.com...
> When I right click on my sql server in the enterprise manager and select
all
> tasks --> manage server messages I am not seeing the object the error is
> referring to. Instead I see a % wherever it should be inserting the
object
> name like below.
> Incorrect value for parameter '%s'.
> What do i have to do to get the server to substitute the tablename, etc.
for
> the % sign. Also is there anyway to see a timestamp for the errors
returned.
> It would be helpful to know when the error occured. Basically I am having
> issues with an ASP.net page that is doing an update on a particular table.
> It
> is not throwing an exception on the asp.net side so i wanted to look on
the
> sql side to see what happened. But on the sql side i can't tell because
> there
> is no indication which table or when a particualr message in the manage
> server messages tool refers too. Is there a better way to go about this?
> Server is SQL server 2000.
>
>|||Narayana:
Thanks, I had already opened the profiler and you are right it is pretty
intuitive. I was able to capture the sql statement being sent from the vb app
and it did not cause an exception it just was returning 0 rows affected.
Maybe you guys could take a quick look at the statement 9hah - it is ugly)
and see if you can see the error. It is generated by ASP.Net 2.0 using a
gridview and sqldatasource. All of the code is generated by the asp controls
not me so I was expecting it to work for the simple test i was doing. here is
the generated statement with linebreaks inserted to make it more readable.
exec sp_executesql N
'UPDATE [MSDS]
SET
[MSDSName] = @.MSDSName,
[MSDSLink] = @.MSDSLink,
[MSDSSubmittedBy] = @.MSDSSubmittedBy,
[MSDSApprovedBy] = @.MSDSApprovedBy,
[ProductCategoryID] = @.ProductCategoryID,
[MSDSLanguageID] = @.MSDSLanguageID,
[MSDSApproved] = @.MSDSApproved,
[MSDSSubmittedDate] = @.MSDSSubmittedDate,
[MSDSExpired] = @.MSDSExpired
WHERE [MSDSID] = @.original_MSDSID
AND [MSDSName] = @.original_MSDSName
AND [MSDSLink] = @.original_MSDSLink
AND [MSDSSubmittedBy] = @.original_MSDSSubmittedBy
AND [MSDSApprovedBy] = @.original_MSDSApprovedBy
AND [ProductCategoryID] = @.original_ProductCategoryID
AND [MSDSLanguageID] = @.original_MSDSLanguageID
AND [MSDSApproved] = @.original_MSDSApproved
AND [MSDSSubmittedDate] = @.original_MSDSSubmittedDate
AND [MSDSExpired] = @.original_MSDSExpired',
N'@.MSDSName nvarchar(4000),
@.MSDSLink nvarchar(4000),
@.MSDSSubmittedBy nvarchar(8),
@.MSDSApprovedBy nvarchar(4000),
@.ProductCategoryID int,
@.MSDSLanguageID int,
@.MSDSApproved bit,
@.MSDSSubmittedDate nvarchar(4000),
@.MSDSExpired bit,@.original_MSDSID int,
@.original_MSDSName nvarchar(4000),
@.original_MSDSLink nvarchar(4000),
@.original_MSDSSubmittedBy nvarchar(8),
@.original_MSDSApprovedBy nvarchar(4000),
@.original_ProductCategoryID int,
@.original_MSDSLanguageID int,
@.original_MSDSApproved bit,
@.original_MSDSSubmittedDate nvarchar(4000),
@.original_MSDSExpired bit',
@.MSDSName = NULL,
@.MSDSLink = NULL,
@.MSDSSubmittedBy = N'KMcPhail',
@.MSDSApprovedBy = NULL,
@.ProductCategoryID = 5,
@.MSDSLanguageID = 1,
@.MSDSApproved = 0,
@.MSDSSubmittedDate = NULL,
@.MSDSExpired = 0,
@.original_MSDSID = 133,
@.original_MSDSName = NULL,
@.original_MSDSLink = NULL,
@.original_MSDSSubmittedBy = N'KMcPhail',
@.original_MSDSApprovedBy = NULL,
@.original_ProductCategoryID = 5,
@.original_MSDSLanguageID = 3,
@.original_MSDSApproved = 0,
@.original_MSDSSubmittedDate = NULL,
@.original_MSDSExpired = 0
"Narayana Vyas Kondreddi" wrote:
> Could it be that you have something like 'On Error Resume Next' in your VB
> app, that is silently ignoring the error?
> I am not aware of any step by step instructions for using Profiler but, it
> should be quite simple and intuitive once you open it. I have some related
> articles at: http://vyaskn.tripod.com/analyzing_profiler_output.htm
> Apart from that do checkout SQL Server Books Online.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Kevin McPhail" <KevinMcPhail@.discussions.microsoft.com> wrote in message
> news:A60CD0CE-FB4B-4CB3-B74D-0226A913D920@.microsoft.com...
> Narayana:
> Thanks for your suggestions. Can you point me to a resource on tracing the
> message with profiler. The vb app does not seem to be throwing an exception
> so i am trying to figure out what is going on. I am not sure it is even
> trying to write to the db at this point.
> "Narayana Vyas Kondreddi" wrote:
> > What you are seeing in Enterprise Manager is a collection of all the error
> > messages, that are stored in the master..sysmessages table. They are not
> the
> > errors raised by your application. When an error occurs, SQL Server will
> > raise those errors by substituting the parameters with appropriate values.
> >
> > If you think you are experiencing an error in your application, then you
> > should capture the error number and the error description via the
> mechanism
> > provided by your programming language. For example, the Err object in VB.
> >
> > Alternatively you can use SQL Server Profiler to see what statements are
> > being executed by SQL Server. You can trace the Exception event to capture
> > errors.
> > --
> > HTH,
> > Vyas, MVP (SQL Server)
> > SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
> >
> >
> > "Kevin McPhail" <KevinMcPhail@.discussions.microsoft.com> wrote in message
> > news:2F80D3C9-FC22-4431-9F8A-EBB2FA7EF0B0@.microsoft.com...
> > When I right click on my sql server in the enterprise manager and select
> all
> > tasks --> manage server messages I am not seeing the object the error is
> > referring to. Instead I see a % wherever it should be inserting the
> object
> > name like below.
> >
> > Incorrect value for parameter '%s'.
> >
> > What do i have to do to get the server to substitute the tablename, etc.
> for
> > the % sign. Also is there anyway to see a timestamp for the errors
> returned.
> > It would be helpful to know when the error occured. Basically I am having
> > issues with an ASP.net page that is doing an update on a particular table.
> > It
> > is not throwing an exception on the asp.net side so i wanted to look on
> the
> > sql side to see what happened. But on the sql side i can't tell because
> > there
> > is no indication which table or when a particualr message in the manage
> > server messages tool refers too. Is there a better way to go about this?
> > Server is SQL server 2000.
> >
> >
> >
>
>

Manage server messages shows % instead of name of item in error

When I right click on my sql server in the enterprise manager and select all
tasks --> manage server messages I am not seeing the object the error is
referring to. Instead I see a % wherever it should be inserting the object
name like below.
Incorrect value for parameter '%s'.
What do i have to do to get the server to substitute the tablename, etc. for
the % sign. Also is there anyway to see a timestamp for the errors returned.
It would be helpful to know when the error occured. Basically I am having
issues with an ASP.net page that is doing an update on a particular table. I
t
is not throwing an exception on the asp.net side so i wanted to look on the
sql side to see what happened. But on the sql side i can't tell because ther
e
is no indication which table or when a particualr message in the manage
server messages tool refers too. Is there a better way to go about this?
Server is SQL server 2000.What you are seeing in Enterprise Manager is a collection of all the error
messages, that are stored in the master..sysmessages table. They are not the
errors raised by your application. When an error occurs, SQL Server will
raise those errors by substituting the parameters with appropriate values.
If you think you are experiencing an error in your application, then you
should capture the error number and the error description via the mechanism
provided by your programming language. For example, the Err object in VB.
Alternatively you can use SQL Server Profiler to see what statements are
being executed by SQL Server. You can trace the Exception event to capture
errors.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Kevin McPhail" <KevinMcPhail@.discussions.microsoft.com> wrote in message
news:2F80D3C9-FC22-4431-9F8A-EBB2FA7EF0B0@.microsoft.com...
When I right click on my sql server in the enterprise manager and select all
tasks --> manage server messages I am not seeing the object the error is
referring to. Instead I see a % wherever it should be inserting the object
name like below.
Incorrect value for parameter '%s'.
What do i have to do to get the server to substitute the tablename, etc. for
the % sign. Also is there anyway to see a timestamp for the errors returned.
It would be helpful to know when the error occured. Basically I am having
issues with an ASP.net page that is doing an update on a particular table.
It
is not throwing an exception on the asp.net side so i wanted to look on the
sql side to see what happened. But on the sql side i can't tell because
there
is no indication which table or when a particualr message in the manage
server messages tool refers too. Is there a better way to go about this?
Server is SQL server 2000.

Manage server messages shows % instead of name of item in error

When I right click on my sql server in the enterprise manager and select all
tasks --> manage server messages I am not seeing the object the error is
referring to. Instead I see a % wherever it should be inserting the object
name like below.
Incorrect value for parameter '%s'.
What do i have to do to get the server to substitute the tablename, etc. for
the % sign. Also is there anyway to see a timestamp for the errors returned.
It would be helpful to know when the error occured. Basically I am having
issues with an ASP.net page that is doing an update on a particular table. It
is not throwing an exception on the asp.net side so i wanted to look on the
sql side to see what happened. But on the sql side i can't tell because there
is no indication which table or when a particualr message in the manage
server messages tool refers too. Is there a better way to go about this?
Server is SQL server 2000.
What you are seeing in Enterprise Manager is a collection of all the error
messages, that are stored in the master..sysmessages table. They are not the
errors raised by your application. When an error occurs, SQL Server will
raise those errors by substituting the parameters with appropriate values.
If you think you are experiencing an error in your application, then you
should capture the error number and the error description via the mechanism
provided by your programming language. For example, the Err object in VB.
Alternatively you can use SQL Server Profiler to see what statements are
being executed by SQL Server. You can trace the Exception event to capture
errors.
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Kevin McPhail" <KevinMcPhail@.discussions.microsoft.com> wrote in message
news:2F80D3C9-FC22-4431-9F8A-EBB2FA7EF0B0@.microsoft.com...
When I right click on my sql server in the enterprise manager and select all
tasks --> manage server messages I am not seeing the object the error is
referring to. Instead I see a % wherever it should be inserting the object
name like below.
Incorrect value for parameter '%s'.
What do i have to do to get the server to substitute the tablename, etc. for
the % sign. Also is there anyway to see a timestamp for the errors returned.
It would be helpful to know when the error occured. Basically I am having
issues with an ASP.net page that is doing an update on a particular table.
It
is not throwing an exception on the asp.net side so i wanted to look on the
sql side to see what happened. But on the sql side i can't tell because
there
is no indication which table or when a particualr message in the manage
server messages tool refers too. Is there a better way to go about this?
Server is SQL server 2000.

Manage remote instances using SQL Server 2005 tools

I'm trying to manage remote instances using the following SQL Server 2005 tools:

SQL Server Configuration Manager (SSCM)

SQL Server Surface Area Configuration (SSSAC)

It seems that SSSAC is designed to manage remote instances since it has the "change computer" link. On the other hand, SSCM does not have such link or something similar. I read this MS article http://msdn2.microsoft.com/en-US/library/ms190622.aspx so it seems that this is a way to run SSCM to manage remote instance. I was having problem to show SQL Server 2005 Services by following that article. However, the problem is now gone (don't know why yet).

What are the reasons that the SSCM is not designed to manage remote instances but SSSAC is? If SSCM is really designed not to manage remote instances, what is the purpose of having the word (Local) next to the SQL Server Configuration Manager?

If I understand correctly, the only main difference between SSCM and SSSAC is that SSCM does not have the functionality to turn on and off features according to http://www.microsoft.com/technet/prodtechnol/sql/2005/mgsqlexpwssmse.mspx

So, if I don't need to turn on and off features, SSCM will be the only tool needed if it can manage remote instance.

With SQL Server Management Studio (SSMS) open and Registered Servers visible, right-click a remote reristered server and click SQL Server Configuration Manager. You will see SQL Server Configuration Manager(remote server name).|||

Hi lkh,

Thanks for the information. I wonder why MS does not implement the change computer in SSCM. Is there a way to run SSSAC in SSMS?

|||

it is a command prompt utility see it can be run in Query Editor in sqlcmd mode

See SQL Server 2005 Books Online topic:

sac Utility

http://msdn2.microsoft.com/en-us/library/ms162800.aspx

you can add in in SSMS under Tools\External Tools. Its located at C:\Program Files\Microsoft SQL Server\90\Shared\SqlSAC.exe

Manage MSDE from SQL Server

Hi everybody, I have 2 "server" one run SQL Server and other run MSDE, I nee
d use Enterprise Manager to manage MSDE, but I receive a messager error "Sql
does not exist or access denied" I have the SA password MSDE, I can only ma
nage the MSDE in the machin
e installed,
I hope your answer!
Best Regards!Thanks to Bishop for his help, this is the solution
set this in setup.ini:
DisableNetworkProtocols=0
Best Regards!

Manage MSDE from SQL Server

Hi everybody, I have 2 "server" one run SQL Server and other run MSDE, I need use Enterprise Manager to manage MSDE, but I receive a messager error "Sql does not exist or access denied" I have the SA password MSDE, I can only manage the MSDE in the machin
e installed,
I hope your answer!
Best Regards!
Thanks to Bishop for his help, this is the solution
set this in setup.ini:
DisableNetworkProtocols=0
Best Regards!

Making a .txt file from SQL server2000 table og SP.

Hi all.
We use DTS and transformation manager to export data to flat .txt files.
E.g. pricelists, produkt stock information.
If we want to export many columns (more than 15 to 20) the Define columns
function in the DTS return blanks so I can't map the fields to the .txt
file. Then all EM hangs. We have tried a lot to fix this. I have attached
one select that fails.
Well, is there any other ways to automate output of .txt files for
SQLServer2000 prosedures or tables. E.g some 3.part programs that we can
scedule using SQL server agent?
Thanx all
geir
SELECT Top 10 RTrim(custItegraRptFilExport.Varenr) as ItemKey,
RTrim(custItegraRptFilExport.Varenr) as Itemid,
RTrim(custItegraRptFilExport.ProduktDesc1) as ItemName,
RTrim(custItegraRptFilExport.ProduktDesc1) + ' ' +
RTrim(custItegraRptFilExport.ProduktDesc1) as LongDesc,
RTrim(custItegraRptFilExport.GruppeLev1Txt) + ' ' +
RTrim(custItegraRptFilExport.GruppeLev2Txt) + ' ' +
RTrim(custItegraRptFilExport.GruppeLev3Txt) as Searchwords,
'' as SubItemOf,
'' as ReplacesItems,
RTrim(custItegraRptFilExport.ProdusentNavn) as Manufacturer,
RTrim(custItegraRptFilExport.LeverandProduktNr) as MfrItemID,
custItegraRptFilExport.Varenr as UNSPSC,
'13.1' as UNSPSC_Ver,
'http://www.itegra.no/aspx/prdinfo.aspx?plid=' +
Cast(custItegraRptFilExport.ProduktLagerID as varchar) as ImageURL,
'' as InfoURL,
custItegraRptFilExport.PrdBildeFileNavn as FileName,
'' as AttachmentFileName,
'' as AttachmentName,
'' as AttachmentDescription,
custItegraRptFilExport.PrdEAN as EAN,
'' as NATO_ID,
'' as Risk,
Cast(custItegraRptFilExport.Pris as decimal(13,2)) as Price,
1 as QuantityInPrice,
custItegraRptFilExport.Enhet,
Cast(custItegraRptFilExport.MvaPst as decimal(9,0)) as VAT,
1 as OrderMultiple,
1 as MinOrder,
Datediff(dd, getdate(), custItegraRptFilExport.ETADateFormat) as ETA,
'' as ETAText,
10 as Priority,
1 as InnerUnit,
Cast(custItegraRptFilExport.PrdKjopEnhAntall as decimal(9,0)) as
QuantityInUnit
FROM custItegraRptFilExport
WHERE custItegraRptFilExport.KundeID = 37177Hi
create table ww
(
col1 int,
col2 varchar(50),
col3 varchar (50)
)
insert into ww values (47,'ReadyShip','(503)888-999')
insert into ww values (48,'MyShipper','(503)1212-454')
insert into ww values (49,'ReadyShip','(45)888-999')
insert into ww values (50,'MyShipper','(545)1212-454')
--command
bcp northwind.dbo.ww out d:\test1.txt -c -t, -SSERVERName -Usa -Ppass
--QA
exec master..xp_cmdshell 'BCP northwind..ww out
d:\test1.txt -c -C850 -SServerName -Usa -Ppass'
"Geir Holme" <geir@.multicase.no> wrote in message
news:%23oumw4mnFHA.3316@.tk2msftngp13.phx.gbl...
> Hi all.
> We use DTS and transformation manager to export data to flat .txt files.
> E.g. pricelists, produkt stock information.
> If we want to export many columns (more than 15 to 20) the Define columns
> function in the DTS return blanks so I can't map the fields to the .txt
> file. Then all EM hangs. We have tried a lot to fix this. I have attached
> one select that fails.
> Well, is there any other ways to automate output of .txt files for
> SQLServer2000 prosedures or tables. E.g some 3.part programs that we can
> scedule using SQL server agent?
> Thanx all
> geir
>
> SELECT Top 10 RTrim(custItegraRptFilExport.Varenr) as ItemKey,
> RTrim(custItegraRptFilExport.Varenr) as Itemid,
> RTrim(custItegraRptFilExport.ProduktDesc1) as ItemName,
> RTrim(custItegraRptFilExport.ProduktDesc1) + ' ' +
> RTrim(custItegraRptFilExport.ProduktDesc1) as LongDesc,
> RTrim(custItegraRptFilExport.GruppeLev1Txt) + ' ' +
> RTrim(custItegraRptFilExport.GruppeLev2Txt) + ' ' +
> RTrim(custItegraRptFilExport.GruppeLev3Txt) as Searchwords,
> '' as SubItemOf,
> '' as ReplacesItems,
> RTrim(custItegraRptFilExport.ProdusentNavn) as Manufacturer,
> RTrim(custItegraRptFilExport.LeverandProduktNr) as MfrItemID,
> custItegraRptFilExport.Varenr as UNSPSC,
> '13.1' as UNSPSC_Ver,
> 'http://www.itegra.no/aspx/prdinfo.aspx?plid=' +
> Cast(custItegraRptFilExport.ProduktLagerID as varchar) as ImageURL,
> '' as InfoURL,
> custItegraRptFilExport.PrdBildeFileNavn as FileName,
> '' as AttachmentFileName,
> '' as AttachmentName,
> '' as AttachmentDescription,
> custItegraRptFilExport.PrdEAN as EAN,
> '' as NATO_ID,
> '' as Risk,
> Cast(custItegraRptFilExport.Pris as decimal(13,2)) as Price,
> 1 as QuantityInPrice,
> custItegraRptFilExport.Enhet,
> Cast(custItegraRptFilExport.MvaPst as decimal(9,0)) as VAT,
> 1 as OrderMultiple,
> 1 as MinOrder,
> Datediff(dd, getdate(), custItegraRptFilExport.ETADateFormat) as ETA,
> '' as ETAText,
> 10 as Priority,
> 1 as InnerUnit,
> Cast(custItegraRptFilExport.PrdKjopEnhAntall as decimal(9,0)) as
> QuantityInUnit
> FROM custItegraRptFilExport
> WHERE custItegraRptFilExport.KundeID = 37177
>|||Hi Uri.
Thanx a lot. This was the hint I needed. I guess I can use a prosedurename
instead of the tablename if I want to do this without CREATE and DROP a
table.
Anyway, thanx again.
-geir
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:O5yYz%23mnFHA.2472@.TK2MSFTNGP15.phx.gbl...
> Hi
> create table ww
> (
> col1 int,
> col2 varchar(50),
> col3 varchar (50)
> )
> insert into ww values (47,'ReadyShip','(503)888-999')
> insert into ww values (48,'MyShipper','(503)1212-454')
> insert into ww values (49,'ReadyShip','(45)888-999')
> insert into ww values (50,'MyShipper','(545)1212-454')
> --command
> bcp northwind.dbo.ww out d:\test1.txt -c -t, -SSERVERName -Usa -Ppass
> --QA
> exec master..xp_cmdshell 'BCP northwind..ww out
> d:\test1.txt -c -C850 -SServerName -Usa -Ppass'
>
> "Geir Holme" <geir@.multicase.no> wrote in message
> news:%23oumw4mnFHA.3316@.tk2msftngp13.phx.gbl...
>> Hi all.
>> We use DTS and transformation manager to export data to flat .txt files.
>> E.g. pricelists, produkt stock information.
>> If we want to export many columns (more than 15 to 20) the Define columns
>> function in the DTS return blanks so I can't map the fields to the .txt
>> file. Then all EM hangs. We have tried a lot to fix this. I have attached
>> one select that fails.
>> Well, is there any other ways to automate output of .txt files for
>> SQLServer2000 prosedures or tables. E.g some 3.part programs that we can
>> scedule using SQL server agent?
>> Thanx all
>> geir
>>
>> SELECT Top 10 RTrim(custItegraRptFilExport.Varenr) as ItemKey,
>> RTrim(custItegraRptFilExport.Varenr) as Itemid,
>> RTrim(custItegraRptFilExport.ProduktDesc1) as ItemName,
>> RTrim(custItegraRptFilExport.ProduktDesc1) + ' ' +
>> RTrim(custItegraRptFilExport.ProduktDesc1) as LongDesc,
>> RTrim(custItegraRptFilExport.GruppeLev1Txt) + ' ' +
>> RTrim(custItegraRptFilExport.GruppeLev2Txt) + ' ' +
>> RTrim(custItegraRptFilExport.GruppeLev3Txt) as Searchwords,
>> '' as SubItemOf,
>> '' as ReplacesItems,
>> RTrim(custItegraRptFilExport.ProdusentNavn) as Manufacturer,
>> RTrim(custItegraRptFilExport.LeverandProduktNr) as MfrItemID,
>> custItegraRptFilExport.Varenr as UNSPSC,
>> '13.1' as UNSPSC_Ver,
>> 'http://www.itegra.no/aspx/prdinfo.aspx?plid=' +
>> Cast(custItegraRptFilExport.ProduktLagerID as varchar) as ImageURL,
>> '' as InfoURL,
>> custItegraRptFilExport.PrdBildeFileNavn as FileName,
>> '' as AttachmentFileName,
>> '' as AttachmentName,
>> '' as AttachmentDescription,
>> custItegraRptFilExport.PrdEAN as EAN,
>> '' as NATO_ID,
>> '' as Risk,
>> Cast(custItegraRptFilExport.Pris as decimal(13,2)) as Price,
>> 1 as QuantityInPrice,
>> custItegraRptFilExport.Enhet,
>> Cast(custItegraRptFilExport.MvaPst as decimal(9,0)) as VAT,
>> 1 as OrderMultiple,
>> 1 as MinOrder,
>> Datediff(dd, getdate(), custItegraRptFilExport.ETADateFormat) as ETA,
>> '' as ETAText,
>> 10 as Priority,
>> 1 as InnerUnit,
>> Cast(custItegraRptFilExport.PrdKjopEnhAntall as decimal(9,0)) as
>> QuantityInUnit
>> FROM custItegraRptFilExport
>> WHERE custItegraRptFilExport.KundeID = 37177
>>
>

Making a .txt file from SQL server2000 table og SP.

Hi all.
We use DTS and transformation manager to export data to flat .txt files.
E.g. pricelists, produkt stock information.
If we want to export many columns (more than 15 to 20) the Define columns
function in the DTS return blanks so I can't map the fields to the .txt
file. Then all EM hangs. We have tried a lot to fix this. I have attached
one select that fails.
Well, is there any other ways to automate output of .txt files for
SQLServer2000 prosedures or tables. E.g some 3.part programs that we can
scedule using SQL server agent?
Thanx all
geir
SELECT Top 10 RTrim(custItegraRptFilExport.Varenr) as ItemKey,
RTrim(custItegraRptFilExport.Varenr) as Itemid,
RTrim(custItegraRptFilExport.ProduktDesc1) as ItemName,
RTrim(custItegraRptFilExport.ProduktDesc1) + ' ' +
RTrim(custItegraRptFilExport.ProduktDesc1) as LongDesc,
RTrim(custItegraRptFilExport.GruppeLev1Txt) + ' ' +
RTrim(custItegraRptFilExport.GruppeLev2Txt) + ' ' +
RTrim(custItegraRptFilExport.GruppeLev3Txt) as Searchwords,
'' as SubItemOf,
'' as ReplacesItems,
RTrim(custItegraRptFilExport.ProdusentNavn) as Manufacturer,
RTrim(custItegraRptFilExport.LeverandProduktNr) as MfrItemID,
custItegraRptFilExport.Varenr as UNSPSC,
'13.1' as UNSPSC_Ver,
'http://www.itegra.no/aspx/prdinfo.aspx?plid=' +
Cast(custItegraRptFilExport.ProduktLagerID as varchar) as ImageURL,
'' as InfoURL,
custItegraRptFilExport.PrdBildeFileNavn as FileName,
'' as AttachmentFileName,
'' as AttachmentName,
'' as AttachmentDescription,
custItegraRptFilExport.PrdEAN as EAN,
'' as NATO_ID,
'' as Risk,
Cast(custItegraRptFilExport.Pris as decimal(13,2)) as Price,
1 as QuantityInPrice,
custItegraRptFilExport.Enhet,
Cast(custItegraRptFilExport.MvaPst as decimal(9,0)) as VAT,
1 as OrderMultiple,
1 as MinOrder,
Datediff(dd, getdate(), custItegraRptFilExport.ETADateFormat) as ETA,
'' as ETAText,
10 as Priority,
1 as InnerUnit,
Cast(custItegraRptFilExport.PrdKjopEnhAntall as decimal(9,0)) as
QuantityInUnit
FROM custItegraRptFilExport
WHERE custItegraRptFilExport.KundeID = 37177
Hi
create table ww
(
col1 int,
col2 varchar(50),
col3 varchar (50)
)
insert into ww values (47,'ReadyShip','(503)888-999')
insert into ww values (48,'MyShipper','(503)1212-454')
insert into ww values (49,'ReadyShip','(45)888-999')
insert into ww values (50,'MyShipper','(545)1212-454')
--command
bcp northwind.dbo.ww out d:\test1.txt -c -t, -SSERVERName -Usa -Ppass
--QA
exec master..xp_cmdshell 'BCP northwind..ww out
d:\test1.txt -c -C850 -SServerName -Usa -Ppass'
"Geir Holme" <geir@.multicase.no> wrote in message
news:%23oumw4mnFHA.3316@.tk2msftngp13.phx.gbl...
> Hi all.
> We use DTS and transformation manager to export data to flat .txt files.
> E.g. pricelists, produkt stock information.
> If we want to export many columns (more than 15 to 20) the Define columns
> function in the DTS return blanks so I can't map the fields to the .txt
> file. Then all EM hangs. We have tried a lot to fix this. I have attached
> one select that fails.
> Well, is there any other ways to automate output of .txt files for
> SQLServer2000 prosedures or tables. E.g some 3.part programs that we can
> scedule using SQL server agent?
> Thanx all
> geir
>
> SELECT Top 10 RTrim(custItegraRptFilExport.Varenr) as ItemKey,
> RTrim(custItegraRptFilExport.Varenr) as Itemid,
> RTrim(custItegraRptFilExport.ProduktDesc1) as ItemName,
> RTrim(custItegraRptFilExport.ProduktDesc1) + ' ' +
> RTrim(custItegraRptFilExport.ProduktDesc1) as LongDesc,
> RTrim(custItegraRptFilExport.GruppeLev1Txt) + ' ' +
> RTrim(custItegraRptFilExport.GruppeLev2Txt) + ' ' +
> RTrim(custItegraRptFilExport.GruppeLev3Txt) as Searchwords,
> '' as SubItemOf,
> '' as ReplacesItems,
> RTrim(custItegraRptFilExport.ProdusentNavn) as Manufacturer,
> RTrim(custItegraRptFilExport.LeverandProduktNr) as MfrItemID,
> custItegraRptFilExport.Varenr as UNSPSC,
> '13.1' as UNSPSC_Ver,
> 'http://www.itegra.no/aspx/prdinfo.aspx?plid=' +
> Cast(custItegraRptFilExport.ProduktLagerID as varchar) as ImageURL,
> '' as InfoURL,
> custItegraRptFilExport.PrdBildeFileNavn as FileName,
> '' as AttachmentFileName,
> '' as AttachmentName,
> '' as AttachmentDescription,
> custItegraRptFilExport.PrdEAN as EAN,
> '' as NATO_ID,
> '' as Risk,
> Cast(custItegraRptFilExport.Pris as decimal(13,2)) as Price,
> 1 as QuantityInPrice,
> custItegraRptFilExport.Enhet,
> Cast(custItegraRptFilExport.MvaPst as decimal(9,0)) as VAT,
> 1 as OrderMultiple,
> 1 as MinOrder,
> Datediff(dd, getdate(), custItegraRptFilExport.ETADateFormat) as ETA,
> '' as ETAText,
> 10 as Priority,
> 1 as InnerUnit,
> Cast(custItegraRptFilExport.PrdKjopEnhAntall as decimal(9,0)) as
> QuantityInUnit
> FROM custItegraRptFilExport
> WHERE custItegraRptFilExport.KundeID = 37177
>
|||Hi Uri.
Thanx a lot. This was the hint I needed. I guess I can use a prosedurename
instead of the tablename if I want to do this without CREATE and DROP a
table.
Anyway, thanx again.
-geir
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:O5yYz%23mnFHA.2472@.TK2MSFTNGP15.phx.gbl...
> Hi
> create table ww
> (
> col1 int,
> col2 varchar(50),
> col3 varchar (50)
> )
> insert into ww values (47,'ReadyShip','(503)888-999')
> insert into ww values (48,'MyShipper','(503)1212-454')
> insert into ww values (49,'ReadyShip','(45)888-999')
> insert into ww values (50,'MyShipper','(545)1212-454')
> --command
> bcp northwind.dbo.ww out d:\test1.txt -c -t, -SSERVERName -Usa -Ppass
> --QA
> exec master..xp_cmdshell 'BCP northwind..ww out
> d:\test1.txt -c -C850 -SServerName -Usa -Ppass'
>
> "Geir Holme" <geir@.multicase.no> wrote in message
> news:%23oumw4mnFHA.3316@.tk2msftngp13.phx.gbl...
>

Making a .txt file from SQL server2000 table og SP.

Hi all.
We use DTS and transformation manager to export data to flat .txt files.
E.g. pricelists, produkt stock information.
If we want to export many columns (more than 15 to 20) the Define columns
function in the DTS return blanks so I can't map the fields to the .txt
file. Then all EM hangs. We have tried a lot to fix this. I have attached
one select that fails.
Well, is there any other ways to automate output of .txt files for
SQLServer2000 prosedures or tables. E.g some 3.part programs that we can
scedule using SQL server agent?
Thanx all
geir
SELECT Top 10 RTrim(custItegraRptFilExport.Varenr) as ItemKey,
RTrim(custItegraRptFilExport.Varenr) as Itemid,
RTrim(custItegraRptFilExport.ProduktDesc1) as ItemName,
RTrim(custItegraRptFilExport.ProduktDesc1) + ' ' +
RTrim(custItegraRptFilExport.ProduktDesc1) as LongDesc,
RTrim(custItegraRptFilExport.GruppeLev1Txt) + ' ' +
RTrim(custItegraRptFilExport.GruppeLev2Txt) + ' ' +
RTrim(custItegraRptFilExport.GruppeLev3Txt) as Searchwords,
'' as SubItemOf,
'' as ReplacesItems,
RTrim(custItegraRptFilExport.ProdusentNavn) as Manufacturer,
RTrim(custItegraRptFilExport.LeverandProduktNr) as MfrItemID,
custItegraRptFilExport.Varenr as UNSPSC,
'13.1' as UNSPSC_Ver,
'http://www.itegra.no/aspx/prdinfo.aspx?plid=' +
Cast(custItegraRptFilExport.ProduktLagerID as varchar) as ImageURL,
'' as InfoURL,
custItegraRptFilExport.PrdBildeFileNavn as FileName,
'' as AttachmentFileName,
'' as AttachmentName,
'' as AttachmentDescription,
custItegraRptFilExport.PrdEAN as EAN,
'' as NATO_ID,
'' as Risk,
Cast(custItegraRptFilExport.Pris as decimal(13,2)) as Price,
1 as QuantityInPrice,
custItegraRptFilExport.Enhet,
Cast(custItegraRptFilExport.MvaPst as decimal(9,0)) as VAT,
1 as OrderMultiple,
1 as MinOrder,
Datediff(dd, getdate(), custItegraRptFilExport.ETADateFormat) as ETA,
'' as ETAText,
10 as Priority,
1 as InnerUnit,
Cast(custItegraRptFilExport.PrdKjopEnhAntall as decimal(9,0)) as
QuantityInUnit
FROM custItegraRptFilExport
WHERE custItegraRptFilExport.KundeID = 37177Hi
create table ww
(
col1 int,
col2 varchar(50),
col3 varchar (50)
)
insert into ww values (47,'ReadyShip','(503)888-999')
insert into ww values (48,'MyShipper','(503)1212-454')
insert into ww values (49,'ReadyShip','(45)888-999')
insert into ww values (50,'MyShipper','(545)1212-454')
--command
bcp northwind.dbo.ww out d:\test1.txt -c -t, -SSERVERName -Usa -Ppass
--QA
exec master..xp_cmdshell 'BCP northwind..ww out
d:\test1.txt -c -C850 -SServerName -Usa -Ppass'
"Geir Holme" <geir@.multicase.no> wrote in message
news:%23oumw4mnFHA.3316@.tk2msftngp13.phx.gbl...
> Hi all.
> We use DTS and transformation manager to export data to flat .txt files.
> E.g. pricelists, produkt stock information.
> If we want to export many columns (more than 15 to 20) the Define columns
> function in the DTS return blanks so I can't map the fields to the .txt
> file. Then all EM hangs. We have tried a lot to fix this. I have attached
> one select that fails.
> Well, is there any other ways to automate output of .txt files for
> SQLServer2000 prosedures or tables. E.g some 3.part programs that we can
> scedule using SQL server agent?
> Thanx all
> geir
>
> SELECT Top 10 RTrim(custItegraRptFilExport.Varenr) as ItemKey,
> RTrim(custItegraRptFilExport.Varenr) as Itemid,
> RTrim(custItegraRptFilExport.ProduktDesc1) as ItemName,
> RTrim(custItegraRptFilExport.ProduktDesc1) + ' ' +
> RTrim(custItegraRptFilExport.ProduktDesc1) as LongDesc,
> RTrim(custItegraRptFilExport.GruppeLev1Txt) + ' ' +
> RTrim(custItegraRptFilExport.GruppeLev2Txt) + ' ' +
> RTrim(custItegraRptFilExport.GruppeLev3Txt) as Searchwords,
> '' as SubItemOf,
> '' as ReplacesItems,
> RTrim(custItegraRptFilExport.ProdusentNavn) as Manufacturer,
> RTrim(custItegraRptFilExport.LeverandProduktNr) as MfrItemID,
> custItegraRptFilExport.Varenr as UNSPSC,
> '13.1' as UNSPSC_Ver,
> 'http://www.itegra.no/aspx/prdinfo.aspx?plid=' +
> Cast(custItegraRptFilExport.ProduktLagerID as varchar) as ImageURL,
> '' as InfoURL,
> custItegraRptFilExport.PrdBildeFileNavn as FileName,
> '' as AttachmentFileName,
> '' as AttachmentName,
> '' as AttachmentDescription,
> custItegraRptFilExport.PrdEAN as EAN,
> '' as NATO_ID,
> '' as Risk,
> Cast(custItegraRptFilExport.Pris as decimal(13,2)) as Price,
> 1 as QuantityInPrice,
> custItegraRptFilExport.Enhet,
> Cast(custItegraRptFilExport.MvaPst as decimal(9,0)) as VAT,
> 1 as OrderMultiple,
> 1 as MinOrder,
> Datediff(dd, getdate(), custItegraRptFilExport.ETADateFormat) as ETA,
> '' as ETAText,
> 10 as Priority,
> 1 as InnerUnit,
> Cast(custItegraRptFilExport.PrdKjopEnhAntall as decimal(9,0)) as
> QuantityInUnit
> FROM custItegraRptFilExport
> WHERE custItegraRptFilExport.KundeID = 37177
>|||Hi Uri.
Thanx a lot. This was the hint I needed. I guess I can use a prosedurename
instead of the tablename if I want to do this without CREATE and DROP a
table.
Anyway, thanx again.
-geir
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:O5yYz%23mnFHA.2472@.TK2MSFTNGP15.phx.gbl...
> Hi
> create table ww
> (
> col1 int,
> col2 varchar(50),
> col3 varchar (50)
> )
> insert into ww values (47,'ReadyShip','(503)888-999')
> insert into ww values (48,'MyShipper','(503)1212-454')
> insert into ww values (49,'ReadyShip','(45)888-999')
> insert into ww values (50,'MyShipper','(545)1212-454')
> --command
> bcp northwind.dbo.ww out d:\test1.txt -c -t, -SSERVERName -Usa -Ppass
> --QA
> exec master..xp_cmdshell 'BCP northwind..ww out
> d:\test1.txt -c -C850 -SServerName -Usa -Ppass'
>
> "Geir Holme" <geir@.multicase.no> wrote in message
> news:%23oumw4mnFHA.3316@.tk2msftngp13.phx.gbl...
>

Major problem with Forms Auth and WMI

I am getting following errors more and more often, when trying to sign-in to
Report Manager on the server hosting RS:
- "provider load failure"
- "Cound not read the Report Manager configuration file. Check to ensure
your Reporting Services WMI provider is working properly."
I don't have a clue where these error messages are coming from. IIS?
What is this WMI provider, and how can I verify if it is working properly'
Where should I start investigating the problem?
Until now, the only workaround is to reboot the server :(((
SQL Server 2000 SP3, RS SP1, Win 2000 Server SP 4
We're using Forms Auth as per the MSDN article.At a guess - Windows Management Instrumentation service is not running/has
stopped. Go to Administrative Tools,Services and find out.
Charles Kangai, MCT, MCDBA
"James" wrote:
> I am getting following errors more and more often, when trying to sign-in to
> Report Manager on the server hosting RS:
> - "provider load failure"
> - "Cound not read the Report Manager configuration file. Check to ensure
> your Reporting Services WMI provider is working properly."
> I don't have a clue where these error messages are coming from. IIS?
> What is this WMI provider, and how can I verify if it is working properly'
> Where should I start investigating the problem?
> Until now, the only workaround is to reboot the server :(((
> SQL Server 2000 SP3, RS SP1, Win 2000 Server SP 4
> We're using Forms Auth as per the MSDN article.|||Thanks Charles,
I had restarted the server, so I cannot reproduce the "WMI provider" problem
right now. I'll make sure I check the WMI service and the WMI Driver
Extensions service next time I get the error.
Anybody can contribute on the "provider load failure" problem? This one
comes and goes. I try twice to log-on to RS Manager and get the error twice,
and the 3rd time I can log-on. Sometimes I can log-on straight away.
I'd like to know which is the application generating these messages, and
what I can do to find the source of the problem.
"Charles Kangai" wrote:
> At a guess - Windows Management Instrumentation service is not running/has
> stopped. Go to Administrative Tools,Services and find out.
> Charles Kangai, MCT, MCDBA
> "James" wrote:
> > I am getting following errors more and more often, when trying to sign-in to
> > Report Manager on the server hosting RS:
> > - "provider load failure"
> > - "Cound not read the Report Manager configuration file. Check to ensure
> > your Reporting Services WMI provider is working properly."
> >
> > I don't have a clue where these error messages are coming from. IIS?
> > What is this WMI provider, and how can I verify if it is working properly'
> > Where should I start investigating the problem?
> >
> > Until now, the only workaround is to reboot the server :(((
> >
> > SQL Server 2000 SP3, RS SP1, Win 2000 Server SP 4
> > We're using Forms Auth as per the MSDN article.|||I've been experiencing the same problem on a reports server with the forms
extension set up. The problem seems to appear sporadically. Sometimes every
couple of weeks, sometimes every couple of hours, and the only solution has
been to restart the WMI service. Anyone got any pointers on how to avoid
this?
Cheers,
Si
"James" wrote:
> Thanks Charles,
> I had restarted the server, so I cannot reproduce the "WMI provider" problem
> right now. I'll make sure I check the WMI service and the WMI Driver
> Extensions service next time I get the error.
> Anybody can contribute on the "provider load failure" problem? This one
> comes and goes. I try twice to log-on to RS Manager and get the error twice,
> and the 3rd time I can log-on. Sometimes I can log-on straight away.
> I'd like to know which is the application generating these messages, and
> what I can do to find the source of the problem.
>
> "Charles Kangai" wrote:
> > At a guess - Windows Management Instrumentation service is not running/has
> > stopped. Go to Administrative Tools,Services and find out.
> >
> > Charles Kangai, MCT, MCDBA
> >
> > "James" wrote:
> >
> > > I am getting following errors more and more often, when trying to sign-in to
> > > Report Manager on the server hosting RS:
> > > - "provider load failure"
> > > - "Cound not read the Report Manager configuration file. Check to ensure
> > > your Reporting Services WMI provider is working properly."
> > >
> > > I don't have a clue where these error messages are coming from. IIS?
> > > What is this WMI provider, and how can I verify if it is working properly'
> > > Where should I start investigating the problem?
> > >
> > > Until now, the only workaround is to reboot the server :(((
> > >
> > > SQL Server 2000 SP3, RS SP1, Win 2000 Server SP 4
> > > We're using Forms Auth as per the MSDN article.|||I've been effected by this bug as well, also after implementing the
Forms Authentication per the MSFT sample (and its Teo Lachev derivative
on devx.com). I can't say with any authority that the Forms Auth
causes it, because implementing the custom security extension is just
about the first thing I did after installing Reporting Services.
The bug happens infrequently but not rarely.
There are a couple entries that show up in the Application Event Log
when the "Provider Load Failure" message appears:
Event Type: Error
Event Source: WinMgmt
Event Category: None
Event ID: 20
Description:
Failed to CoGetClassObject for provider "ReportingServicesWMI". Error
code 0x800703E6 was returned.
Event Type: Error
Event Source: Schedule and Delivery Processor
Event Category: Management
Event ID: 107
Description:
Schedule and Delivery Processor cannot connect to the report server
database.
Like others here, the only (temporary) fix I've found is restarting the
WMI service. When in this state, the services control panel shows WMI
as running but, when shutdown, creates this popup:
WinMgmt.exe - Application Error : The instruction at "0x77fcc823"
referenced memory at "0x041747f0". The memory could not be "read".
Once you click OK the service does shutdown, and on restart the whole
thing works again ... until it doesn't.|||can you explain this...The instruction at "0x77fcc823"referenced memeory at "0x000074b1". The memeory counld not be read...Pls i need answer via e-mail..thanks..
From http://www.developmentnow.com/g/115_2004_11_0_0_453374/Major-problem-with-Forms-Auth-and-WMI.ht
Posted via DevelopmentNow.com Group
http://www.developmentnow.com

Major Problem - Please Help!

Hello Experts,
When I Open Enterprise Manager and click on my server,
following msg appears:
SQL Server Enterprise Manager
----
A Connection could not be established to (LOCAL).
Reason: Data source name not found and no default driver
specified.
Please Verify SQL Server is running...bla, bla.
----
What Cause this:
1) I have Windows XP with SP1
2) I have had installed MDAC 2.8
3) I uninstall MDAC 2.8 using "dasetup.exe /U" command,
setup found that MDAC 2.6 is referenced to MDAC 2.8 an
had to uninstall this too, I pressed OK and then the
uninstall failed because the default of Windows XP MDAC
is 2.7 SP1 and setup propably found that some other MDAC
had to be the default.
Any way, I ReInstall MDAC 2.6 (Latest), MDAC 2.7 Refresh
and Q323263_MDAC27_x86_en.
What do I have to do having as default "MDAC 2.7 SP1 on
Windows XP SP1" and ODBC Drivers work?
Thanks in advance.Make sure shared memory is enabled in client network utility (start | run |
cliconfg)
Try editing the registration property for the server in Enterprise Manager
(Right click on the server and choose to edit registration properties). Use
the server name instead of (local)
Thanks
Deepali
This posting is provided "AS IS" with no warranties, and confers no rights|||Thanks for your response, I will do that and let you know.

>--Original Message--
>Make sure shared memory is enabled in client network
utility (start | run |
>cliconfg)
>Try editing the registration property for the server in
Enterprise Manager
>(Right click on the server and choose to edit
registration properties). Use
>the server name instead of (local)
>Thanks
>Deepali
>This posting is provided "AS IS" with no warranties, and
confers no rights
>.
>

major bug in SQL 2000

Hi,
today I discovered a major and dangerous bug in SQL server. I've
created a view with many fields. When looking in the design manager
everything looks fine. However when I use the view, one of the column
headings is missing and the column names are shift to the left. The
values however are still on their own place. This results in a major
issue that the values appear under the wrong column. In design view
everything looks fine, but when you open the query it display the
wrong values. Thus:
In design view it looks like this:
Field1 Field2 Field3 Field4
1 2 3 4
When opening the query it looks like this:
Field1 Field3 Field4
1 2 3
How the hell is this possible. Changing one thing in the view saving
it again and then opneing it, everything is fine. But how do I know
which views are affected ?
Franc.I have screenshots available fot those interested. So far I've found 4
views which were affected. They were all based on the same tables. I
ran a dbcc checkdb and no error are reported on the database.
Franc.
>Hi,
>today I discovered a major and dangerous bug in SQL server. I've
>created a view with many fields. When looking in the design manager
>everything looks fine. However when I use the view, one of the column
>headings is missing and the column names are shift to the left. The
>values however are still on their own place. This results in a major
>issue that the values appear under the wrong column. In design view
>everything looks fine, but when you open the query it display the
>wrong values. Thus:
>In design view it looks like this:
>Field1 Field2 Field3 Field4
>1 2 3 4
>When opening the query it looks like this:
>Field1 Field3 Field4
>1 2 3
>How the hell is this possible. Changing one thing in the view saving
>it again and then opneing it, everything is fine. But how do I know
>which views are affected ?
>Franc.|||Can we see your code please.
J
>--Original Message--
>I have screenshots available fot those interested. So far
I've found 4
>views which were affected. They were all based on the
same tables. I
>ran a dbcc checkdb and no error are reported on the
database.
>Franc.
>>Hi,
>>today I discovered a major and dangerous bug in SQL
server. I've
>>created a view with many fields. When looking in the
design manager
>>everything looks fine. However when I use the view, one
of the column
>>headings is missing and the column names are shift to
the left. The
>>values however are still on their own place. This
results in a major
>>issue that the values appear under the wrong column. In
design view
>>everything looks fine, but when you open the query it
display the
>>wrong values. Thus:
>>In design view it looks like this:
>>Field1 Field2 Field3 Field4
>>1 2 3 4
>>When opening the query it looks like this:
>>Field1 Field3 Field4
>>1 2 3
>>How the hell is this possible. Changing one thing in the
view saving
>>it again and then opneing it, everything is fine. But
how do I know
>>which views are affected ?
>>Franc.
>.
>|||Hi Franc
Are you altering the objects underneath the view? This can cause the
behaviour you're describing. That SQL Server lets you simply change objects
underneath views without warning is not great, but this isn't a bug as such.
You should look into using the SCHEMABINDING option in your CREATE VIEW
statements. This was designed to ensure that the objects that views
reference are not permitted to change underneath those views. That way, you
don't have to trawl through lists of dependent objects to make sure
everything is in synch..
Regards,
Greg Linwood
SQL Server MVP
"Franc v/d Westelaken" <fvdwestelaken@.hotmail.com> wrote in message
news:40a34498.84859734@.msnews.microsoft.com...
> Hi,
> today I discovered a major and dangerous bug in SQL server. I've
> created a view with many fields. When looking in the design manager
> everything looks fine. However when I use the view, one of the column
> headings is missing and the column names are shift to the left. The
> values however are still on their own place. This results in a major
> issue that the values appear under the wrong column. In design view
> everything looks fine, but when you open the query it display the
> wrong values. Thus:
> In design view it looks like this:
> Field1 Field2 Field3 Field4
> 1 2 3 4
> When opening the query it looks like this:
> Field1 Field3 Field4
> 1 2 3
> How the hell is this possible. Changing one thing in the view saving
> it again and then opneing it, everything is fine. But how do I know
> which views are affected ?
> Franc.|||Hi,
this is the SQL statement I'm using:
SELECT TOP 100 PERCENT dbo.tblWoningen.*, dbo.Huren.*,
dbo.tblPuntenTotaal.*,
dbo.Woningen.Perceel AS Expr1
FROM dbo.Woningen LEFT OUTER JOIN
dbo.Huren ON
dbo.Woningen.Perceel = dbo.Huren.Perceelnr# LEFT
OUTER JOIN
dbo.tblPuntenTotaal ON dbo.Woningen.Perceel =dbo.tblPuntenTotaal.VHE_nummer
ORDER BY dbo.Woningen.Perceel
Franc.
>Can we see your code please.
>J
>
>>--Original Message--
>>I have screenshots available fot those interested. So far
>I've found 4
>>views which were affected. They were all based on the
>same tables. I
>>ran a dbcc checkdb and no error are reported on the
>database.
>>Franc.
>>Hi,
>>today I discovered a major and dangerous bug in SQL
>server. I've
>>created a view with many fields. When looking in the
>design manager
>>everything looks fine. However when I use the view, one
>of the column
>>headings is missing and the column names are shift to
>the left. The
>>values however are still on their own place. This
>results in a major
>>issue that the values appear under the wrong column. In
>design view
>>everything looks fine, but when you open the query it
>display the
>>wrong values. Thus:
>>In design view it looks like this:
>>Field1 Field2 Field3 Field4
>>1 2 3 4
>>When opening the query it looks like this:
>>Field1 Field3 Field4
>>1 2 3
>>How the hell is this possible. Changing one thing in the
>view saving
>>it again and then opneing it, everything is fine. But
>how do I know
>>which views are affected ?
>>Franc.
>>.|||Hi Greg,
no I haven't altered the tables below the views, only added data to
it. What I don't get is, why it looks correctly when run from design
view, but as soon as you run the view it goes wrong. After saving the
view from design view it displays the correct values again. Looks like
SQL is recompiling the view. Is there a way to force recompilation of
all view to ensure that everything is correct. Last week everything
worked fine, today we noticed that the wrong values appeared in field
on our mailing letters.
Franc.
On Thu, 13 May 2004 20:40:12 +1000, "Greg Linwood"
<g_linwoodQhotmail.com> wrote:
>Hi Franc
>Are you altering the objects underneath the view? This can cause the
>behaviour you're describing. That SQL Server lets you simply change objects
>underneath views without warning is not great, but this isn't a bug as such.
>You should look into using the SCHEMABINDING option in your CREATE VIEW
>statements. This was designed to ensure that the objects that views
>reference are not permitted to change underneath those views. That way, you
>don't have to trawl through lists of dependent objects to make sure
>everything is in synch..
>Regards,
>Greg Linwood
>SQL Server MVP
>"Franc v/d Westelaken" <fvdwestelaken@.hotmail.com> wrote in message
>news:40a34498.84859734@.msnews.microsoft.com...
>> Hi,
>> today I discovered a major and dangerous bug in SQL server. I've
>> created a view with many fields. When looking in the design manager
>> everything looks fine. However when I use the view, one of the column
>> headings is missing and the column names are shift to the left. The
>> values however are still on their own place. This results in a major
>> issue that the values appear under the wrong column. In design view
>> everything looks fine, but when you open the query it display the
>> wrong values. Thus:
>> In design view it looks like this:
>> Field1 Field2 Field3 Field4
>> 1 2 3 4
>> When opening the query it looks like this:
>> Field1 Field3 Field4
>> 1 2 3
>> How the hell is this possible. Changing one thing in the view saving
>> it again and then opneing it, everything is fine. But how do I know
>> which views are affected ?
>> Franc.
>|||Hi Franc
Using * in views is a bad idea. If the columns in the tables underneath the
view change, the view is not automatically updated. schemabinding would at
least help you by forcing the person who alters the columns in the
underlying tables to recompile the view.
Regards,
Greg Linwood
SQL Server MVP
"Franc v/d Westelaken" <fvdwestelaken@.hotmail.com> wrote in message
news:40a35050.87859203@.msnews.microsoft.com...
> Hi,
> this is the SQL statement I'm using:
> SELECT TOP 100 PERCENT dbo.tblWoningen.*, dbo.Huren.*,
> dbo.tblPuntenTotaal.*,
> dbo.Woningen.Perceel AS Expr1
> FROM dbo.Woningen LEFT OUTER JOIN
> dbo.Huren ON
> dbo.Woningen.Perceel = dbo.Huren.Perceelnr# LEFT
> OUTER JOIN
> dbo.tblPuntenTotaal ON dbo.Woningen.Perceel => dbo.tblPuntenTotaal.VHE_nummer
> ORDER BY dbo.Woningen.Perceel
> Franc.
> >Can we see your code please.
> >
> >J
> >
> >
> >>--Original Message--
> >>I have screenshots available fot those interested. So far
> >I've found 4
> >>views which were affected. They were all based on the
> >same tables. I
> >>ran a dbcc checkdb and no error are reported on the
> >database.
> >>
> >>Franc.
> >>
> >>Hi,
> >>
> >>today I discovered a major and dangerous bug in SQL
> >server. I've
> >>created a view with many fields. When looking in the
> >design manager
> >>everything looks fine. However when I use the view, one
> >of the column
> >>headings is missing and the column names are shift to
> >the left. The
> >>values however are still on their own place. This
> >results in a major
> >>issue that the values appear under the wrong column. In
> >design view
> >>everything looks fine, but when you open the query it
> >display the
> >>wrong values. Thus:
> >>
> >>In design view it looks like this:
> >>
> >>Field1 Field2 Field3 Field4
> >>1 2 3 4
> >>
> >>When opening the query it looks like this:
> >>
> >>Field1 Field3 Field4
> >>1 2 3
> >>
> >>How the hell is this possible. Changing one thing in the
> >view saving
> >>it again and then opneing it, everything is fine. But
> >how do I know
> >>which views are affected ?
> >>
> >>Franc.
> >>
> >>.
> >>
>|||Hi Franc
Sorry for repeating a little in this thread, but this is what schemabinding
is for. It forces the person who's altering the table / view underneath the
view to recompile the view at the time the underlying object is altered.
Regards,
Greg Linwood
SQL Server MVP
"Franc v/d Westelaken" <fvdwestelaken@.hotmail.com> wrote in message
news:40a35148.88107390@.msnews.microsoft.com...
> Hi Greg,
> no I haven't altered the tables below the views, only added data to
> it. What I don't get is, why it looks correctly when run from design
> view, but as soon as you run the view it goes wrong. After saving the
> view from design view it displays the correct values again. Looks like
> SQL is recompiling the view. Is there a way to force recompilation of
> all view to ensure that everything is correct. Last week everything
> worked fine, today we noticed that the wrong values appeared in field
> on our mailing letters.
> Franc.
> On Thu, 13 May 2004 20:40:12 +1000, "Greg Linwood"
> <g_linwoodQhotmail.com> wrote:
> >Hi Franc
> >
> >Are you altering the objects underneath the view? This can cause the
> >behaviour you're describing. That SQL Server lets you simply change
objects
> >underneath views without warning is not great, but this isn't a bug as
such.
> >
> >You should look into using the SCHEMABINDING option in your CREATE VIEW
> >statements. This was designed to ensure that the objects that views
> >reference are not permitted to change underneath those views. That way,
you
> >don't have to trawl through lists of dependent objects to make sure
> >everything is in synch..
> >
> >Regards,
> >Greg Linwood
> >SQL Server MVP
> >
> >"Franc v/d Westelaken" <fvdwestelaken@.hotmail.com> wrote in message
> >news:40a34498.84859734@.msnews.microsoft.com...
> >> Hi,
> >>
> >> today I discovered a major and dangerous bug in SQL server. I've
> >> created a view with many fields. When looking in the design manager
> >> everything looks fine. However when I use the view, one of the column
> >> headings is missing and the column names are shift to the left. The
> >> values however are still on their own place. This results in a major
> >> issue that the values appear under the wrong column. In design view
> >> everything looks fine, but when you open the query it display the
> >> wrong values. Thus:
> >>
> >> In design view it looks like this:
> >>
> >> Field1 Field2 Field3 Field4
> >> 1 2 3 4
> >>
> >> When opening the query it looks like this:
> >>
> >> Field1 Field3 Field4
> >> 1 2 3
> >>
> >> How the hell is this possible. Changing one thing in the view saving
> >> it again and then opneing it, everything is fine. But how do I know
> >> which views are affected ?
> >>
> >> Franc.
> >
> >
>|||If you post a complete repro script, we can try it on our SQL Servers and see if it reproduces...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Franc v/d Westelaken" <fvdwestelaken@.hotmail.com> wrote in message
news:40a35050.87859203@.msnews.microsoft.com...
> Hi,
> this is the SQL statement I'm using:
> SELECT TOP 100 PERCENT dbo.tblWoningen.*, dbo.Huren.*,
> dbo.tblPuntenTotaal.*,
> dbo.Woningen.Perceel AS Expr1
> FROM dbo.Woningen LEFT OUTER JOIN
> dbo.Huren ON
> dbo.Woningen.Perceel = dbo.Huren.Perceelnr# LEFT
> OUTER JOIN
> dbo.tblPuntenTotaal ON dbo.Woningen.Perceel => dbo.tblPuntenTotaal.VHE_nummer
> ORDER BY dbo.Woningen.Perceel
> Franc.
> >Can we see your code please.
> >
> >J
> >
> >
> >>--Original Message--
> >>I have screenshots available fot those interested. So far
> >I've found 4
> >>views which were affected. They were all based on the
> >same tables. I
> >>ran a dbcc checkdb and no error are reported on the
> >database.
> >>
> >>Franc.
> >>
> >>Hi,
> >>
> >>today I discovered a major and dangerous bug in SQL
> >server. I've
> >>created a view with many fields. When looking in the
> >design manager
> >>everything looks fine. However when I use the view, one
> >of the column
> >>headings is missing and the column names are shift to
> >the left. The
> >>values however are still on their own place. This
> >results in a major
> >>issue that the values appear under the wrong column. In
> >design view
> >>everything looks fine, but when you open the query it
> >display the
> >>wrong values. Thus:
> >>
> >>In design view it looks like this:
> >>
> >>Field1 Field2 Field3 Field4
> >>1 2 3 4
> >>
> >>When opening the query it looks like this:
> >>
> >>Field1 Field3 Field4
> >>1 2 3
> >>
> >>How the hell is this possible. Changing one thing in the
> >view saving
> >>it again and then opneing it, everything is fine. But
> >how do I know
> >>which views are affected ?
> >>
> >>Franc.
> >>
> >>.
> >>
>|||Hi Greg,
ok, I'll alter the view by using schemabindings. However, I don't get
why SQL just moves the column names and not the data. If it would
remove the data also it's fine. Now you get the wrong data in the
wrong columns whcih is much more dangerous.
Franc.
>Hi Franc
>Sorry for repeating a little in this thread, but this is what schemabinding
>is for. It forces the person who's altering the table / view underneath the
>view to recompile the view at the time the underlying object is altered.
>Regards,
>Greg Linwood
>SQL Server MVP
>"Franc v/d Westelaken" <fvdwestelaken@.hotmail.com> wrote in message
>news:40a35148.88107390@.msnews.microsoft.com...
>> Hi Greg,
>> no I haven't altered the tables below the views, only added data to
>> it. What I don't get is, why it looks correctly when run from design
>> view, but as soon as you run the view it goes wrong. After saving the
>> view from design view it displays the correct values again. Looks like
>> SQL is recompiling the view. Is there a way to force recompilation of
>> all view to ensure that everything is correct. Last week everything
>> worked fine, today we noticed that the wrong values appeared in field
>> on our mailing letters.
>> Franc.
>> On Thu, 13 May 2004 20:40:12 +1000, "Greg Linwood"
>> <g_linwoodQhotmail.com> wrote:
>> >Hi Franc
>> >
>> >Are you altering the objects underneath the view? This can cause the
>> >behaviour you're describing. That SQL Server lets you simply change
>objects
>> >underneath views without warning is not great, but this isn't a bug as
>such.
>> >
>> >You should look into using the SCHEMABINDING option in your CREATE VIEW
>> >statements. This was designed to ensure that the objects that views
>> >reference are not permitted to change underneath those views. That way,
>you
>> >don't have to trawl through lists of dependent objects to make sure
>> >everything is in synch..
>> >
>> >Regards,
>> >Greg Linwood
>> >SQL Server MVP
>> >
>> >"Franc v/d Westelaken" <fvdwestelaken@.hotmail.com> wrote in message
>> >news:40a34498.84859734@.msnews.microsoft.com...
>> >> Hi,
>> >>
>> >> today I discovered a major and dangerous bug in SQL server. I've
>> >> created a view with many fields. When looking in the design manager
>> >> everything looks fine. However when I use the view, one of the column
>> >> headings is missing and the column names are shift to the left. The
>> >> values however are still on their own place. This results in a major
>> >> issue that the values appear under the wrong column. In design view
>> >> everything looks fine, but when you open the query it display the
>> >> wrong values. Thus:
>> >>
>> >> In design view it looks like this:
>> >>
>> >> Field1 Field2 Field3 Field4
>> >> 1 2 3 4
>> >>
>> >> When opening the query it looks like this:
>> >>
>> >> Field1 Field3 Field4
>> >> 1 2 3
>> >>
>> >> How the hell is this possible. Changing one thing in the view saving
>> >> it again and then opneing it, everything is fine. But how do I know
>> >> which views are affected ?
>> >>
>> >> Franc.
>> >
>> >
>|||I'm not sure & couldn't answer that precisely with the info at hand. Maybe
someone else might chip in with an explanation on that bit. Hopefully it's
clear for you now what schemabinding's about & why it's important to the
stability of views though.
Regards,
Greg Linwood
SQL Server MVP
"Franc v/d Westelaken" <fvdwestelaken@.hotmail.com> wrote in message
news:40a36040.91940250@.msnews.microsoft.com...
> Hi Greg,
> ok, I'll alter the view by using schemabindings. However, I don't get
> why SQL just moves the column names and not the data. If it would
> remove the data also it's fine. Now you get the wrong data in the
> wrong columns whcih is much more dangerous.
> Franc.
> >Hi Franc
> >
> >Sorry for repeating a little in this thread, but this is what
schemabinding
> >is for. It forces the person who's altering the table / view underneath
the
> >view to recompile the view at the time the underlying object is altered.
> >
> >Regards,
> >Greg Linwood
> >SQL Server MVP
> >
> >"Franc v/d Westelaken" <fvdwestelaken@.hotmail.com> wrote in message
> >news:40a35148.88107390@.msnews.microsoft.com...
> >> Hi Greg,
> >>
> >> no I haven't altered the tables below the views, only added data to
> >> it. What I don't get is, why it looks correctly when run from design
> >> view, but as soon as you run the view it goes wrong. After saving the
> >> view from design view it displays the correct values again. Looks like
> >> SQL is recompiling the view. Is there a way to force recompilation of
> >> all view to ensure that everything is correct. Last week everything
> >> worked fine, today we noticed that the wrong values appeared in field
> >> on our mailing letters.
> >>
> >> Franc.
> >>
> >> On Thu, 13 May 2004 20:40:12 +1000, "Greg Linwood"
> >> <g_linwoodQhotmail.com> wrote:
> >>
> >> >Hi Franc
> >> >
> >> >Are you altering the objects underneath the view? This can cause the
> >> >behaviour you're describing. That SQL Server lets you simply change
> >objects
> >> >underneath views without warning is not great, but this isn't a bug as
> >such.
> >> >
> >> >You should look into using the SCHEMABINDING option in your CREATE
VIEW
> >> >statements. This was designed to ensure that the objects that views
> >> >reference are not permitted to change underneath those views. That
way,
> >you
> >> >don't have to trawl through lists of dependent objects to make sure
> >> >everything is in synch..
> >> >
> >> >Regards,
> >> >Greg Linwood
> >> >SQL Server MVP
> >> >
> >> >"Franc v/d Westelaken" <fvdwestelaken@.hotmail.com> wrote in message
> >> >news:40a34498.84859734@.msnews.microsoft.com...
> >> >> Hi,
> >> >>
> >> >> today I discovered a major and dangerous bug in SQL server. I've
> >> >> created a view with many fields. When looking in the design manager
> >> >> everything looks fine. However when I use the view, one of the
column
> >> >> headings is missing and the column names are shift to the left. The
> >> >> values however are still on their own place. This results in a major
> >> >> issue that the values appear under the wrong column. In design view
> >> >> everything looks fine, but when you open the query it display the
> >> >> wrong values. Thus:
> >> >>
> >> >> In design view it looks like this:
> >> >>
> >> >> Field1 Field2 Field3 Field4
> >> >> 1 2 3 4
> >> >>
> >> >> When opening the query it looks like this:
> >> >>
> >> >> Field1 Field3 Field4
> >> >> 1 2 3
> >> >>
> >> >> How the hell is this possible. Changing one thing in the view saving
> >> >> it again and then opneing it, everything is fine. But how do I know
> >> >> which views are affected ?
> >> >>
> >> >> Franc.
> >> >
> >> >
> >>
> >
> >
>|||SQL Server is not moving anything. It is the client application that is
just not displaying it properly.
Rand
This posting is provided "as is" with no warranties and confers no rights.|||Hi,
no that's not the case. I can prove it to you with some screen shots.
Those clearly show that when run from the design manger it looks fine
but as soon as I use query analyzer the wrong data is displayed. And
the wrong data is also presented to our intranet application which
uses the view and a word mailing document which also uses the view.
It's definitely something wrong with SQL server. I'm not using a
client application to fill or manipulate the data.
Franc.
>SQL Server is not moving anything. It is the client application that is
>just not displaying it properly.
>Rand
>This posting is provided "as is" with no warranties and confers no rights.|||Hi Franc,
Would it be possible to send me screen shots in .jpeg
format.
My email is little_flowery_me@.hotmail.com
J
>--Original Message--
>Hi,
>no that's not the case. I can prove it to you with some
screen shots.
>Those clearly show that when run from the design manger
it looks fine
>but as soon as I use query analyzer the wrong data is
displayed. And
>the wrong data is also presented to our intranet
application which
>uses the view and a word mailing document which also
uses the view.
> It's definitely something wrong with SQL server. I'm
not using a
>client application to fill or manipulate the data.
>Franc.
>>SQL Server is not moving anything. It is the client
application that is
>>just not displaying it properly.
>>Rand
>>This posting is provided "as is" with no warranties and
confers no rights.
>.
>

Maintenance-Backup question

Hi,
I have a SQl database, when I view its properties from Enterprise manager, it's size is about 159 MB. I'm pretty sure I don't have that mucj data in it.

I know that Transaction log files grow over time, but I don't know how to control its growth? Is there a way to delete logs that are unecessary?
By backing up Transaction logs in a maintenance plan will the problem be solved?if you don't need transaction logs change your recovery model to Simple, that way the transaction logs will be truncated at checkpoint.

If you need transaction logs then you should be backing them up on a regular basis. After backup you can truncate the logs. See BOL and maintenance plan for syntax.|||Thanks for your reply,
I have read BOL's explanation of backups and recovery models, however I'm still confused.

I need to be able to have point in time recovery, therefor I've set the backup mode to FULL.
I have a maintenance plan that backs up both Log files and the database on a daily bases.

My confusion is mainly on the issue of Full versus other types of recovery models.

I want to have point in time recovery therefore I need the FULL Recovery mode, but at the same time I am able to determine at a specific point in time if I'm sure I don't need the transaction logs before that specific date and the previous daily backups won't be usfull, therefore I have came up with the below solution which I'm not sure if its correct:

"I will use the Daily log and data backup with the FULL Recovery mode. Then at any given time when I determine I won't need the Log files, I will temporary change the Rocovery mode to "SIMPLE" and manually backup the Log and data files, then after the manual back I will change the Recovery mode to FULL so that the daily scheduled backups will be in the FULL mode. This Way I get rid of the useless transaction logs and keep the new logs untill the next manual backup when I know that I'm safe."

Is this strategy correct?

Thanks|||Ah no.

Set your recovery model to full and DON'T change it unless you are doing big time data/server maintance.

My suggestion is to create a schedule of full backups and transaction log backups.

All transaction logs can be deleted AFTER a full backup is again taken.

Lets say to make a full backup each night at 01:30. Your system doesn't see much activity untill 07:00, is failry heavily used till 19:00 and little activity till 01:30 the next morning. I would take a log dump at 07:00, 19:00 and maybe 20:00. Between 07:00 and 19:00 you might want to take a log dump every hour of half hour. Regardless of the number of log dumps taken you can get rid of them after the next sucessful full backup.

You can specify al this in the maintance plan.

Think about this and poste back.|||Also, lookup "differential backups" in BOL, another good example of a backup strategy.|||A log dump is what I wanted to do, how do I specify a log dump?
Is a log dump another term for backing up transactions logs?


Originally posted by Paul Young
Ah no.

Set your recovery model to full and DON'T change it unless you are doing big time data/server maintance.

My suggestion is to create a schedule of full backups and transaction log backups.

All transaction logs can be deleted AFTER a full backup is again taken.

Lets say to make a full backup each night at 01:30. Your system doesn't see much activity untill 07:00, is failry heavily used till 19:00 and little activity till 01:30 the next morning. I would take a log dump at 07:00, 19:00 and maybe 20:00. Between 07:00 and 19:00 you might want to take a log dump every hour of half hour. Regardless of the number of log dumps taken you can get rid of them after the next sucessful full backup.

You can specify al this in the maintance plan.

Think about this and poste back.|||After backing up, use dbcc shrink file to shrink log file. Since DBCC shrinkfile only shrink from the end of the log file, sometime you have to intentionally do some operations to move the log to the start of the log file. Normally, after two times of backup and dbcc shrink, the log file will be shrinked dramatiscally.

Search google for Andrew Zanevsky, he wrote a good article and script of how to autamatically do the above steps.

Here's header of this script.
use master
go
if object_id( 'sp_force_shrink_log' ) is not null drop proc sp_force_shrink_log
go
create proc sp_force_shrink_log
/*
************************************************** ***********
Name: sp_force_shrink_log
Description:
Shrink transaction log of the current database in SQL Server 7.0.
Switch context to proper db to execute.
Usage: exec sp_force_shrink_log <target_percent>, <target MB>, <iterations>, <backup options>
exec pubs..sp_force_shrink_log

Author: Andrew Zanevsky, AZ Databases, Inc., 12/25/1999, v5 - 08/18/2000
zanevsky@.azdatabases.com|||Originally posted by Sia
A log dump is what I wanted to do, how do I specify a log dump?
Is a log dump another term for backing up transactions logs?

Yes, refer to BOL for Tlog backups.|||Sia, finally found the article in BOL... look up "transaction logs, truncating" This should help you out.

Yes, log dump = backing up transaction log.

You will need to follow jzhu suggestion on shrinking the log files if yo uhave auto shrink turned off. IMNotSoHO I would wathc the log files and only shrink is space were a problem. If you consistantly need 1 gig of log space why go through the cycle of shrinking and growing? I shrink my logs only once a month.|||Thanks for your replay Paul.
I think I got it!|||Paul,
Thanks for your recent replys.

I have scheduled a Database and Log backup every day at 1 and 2 am.

The thing is that, before I do the Log backup my database had the following properties:
Space alloacted for data file was 50MB
Space alloacted for Log file was about 100 MB
availble space was about 70MB.

The I did a manual database and Log backup which resulted in the following db properties

Space alloacted for data file =50MB
Space alloacted for Log file =50MB
availble space= 100MB

Its now about 2 days that I'm doing the Log and Data backup using a scheduled maintenance plan.
I have the following DB Properties:

Space alloacted for data file =50MB
Space alloacted for Log file =2 MB
availble space= 14MB

=>My questions:

Why did the Log File size reduced dramatically after 2 backups with the maintenance plan but was not reduced that much when I did the first 2 backups manually.

Why is the "availble space" reduced!
My guess is that the availble space is calculated using the allocated space and since I have autogrowth it woud increase itself when the file reaches the limit.

I would appreciate if you let me know I'm on the right track or not.

Thanks

Originally posted by Sia
Thanks for your reply,
I have read BOL's explanation of backups and recovery models, however I'm still confused.

I need to be able to have point in time recovery, therefor I've set the backup mode to FULL.
I have a maintenance plan that backs up both Log files and the database on a daily bases.

My confusion is mainly on the issue of Full versus other types of recovery models.

I want to have point in time recovery therefore I need the FULL Recovery mode, but at the same time I am able to determine at a specific point in time if I'm sure I don't need the transaction logs before that specific date and the previous daily backups won't be usfull, therefore I have came up with the below solution which I'm not sure if its correct:

"I will use the Daily log and data backup with the FULL Recovery mode. Then at any given time when I determine I won't need the Log files, I will temporary change the Rocovery mode to "SIMPLE" and manually backup the Log and data files, then after the manual back I will change the Recovery mode to FULL so that the daily scheduled backups will be in the FULL mode. This Way I get rid of the useless transaction logs and keep the new logs untill the next manual backup when I know that I'm safe."

Is this strategy correct?

Thanks|||Okay, just because you truncated the log does not mean you will automatically reduce the size of the log file. When you dup the log file you are backing up all COMMITED transactions and then removing all the transactions you just backed up. There may have been several long running un-commited transactions still in the log. These transaction many cover several segmants in the log file and there for the log file can not be shrunk. It's kind of like a fragmented hard drive.

As for your 2nd question, your are on the right track. I have read all about this one in BOL but it gave me a head ache. Available space depens on the total size of db and log space and how much of each is used.

Bottom line is if you see your new maintance plan is working for you try it out for a month, you will probably need to make a few adjustments but you won't know till you let it run for a while|||Thank you for your help!

Originally posted by Paul Young
Okay, just because you truncated the log does not mean you will automatically reduce the size of the log file. When you dup the log file you are backing up all COMMITED transactions and then removing all the transactions you just backed up. There may have been several long running un-commited transactions still in the log. These transaction many cover several segmants in the log file and there for the log file can not be shrunk. It's kind of like a fragmented hard drive.

As for your 2nd question, your are on the right track. I have read all about this one in BOL but it gave me a head ache. Available space depens on the total size of db and log space and how much of each is used.

Bottom line is if you see your new maintance plan is working for you try it out for a month, you will probably need to make a few adjustments but you won't know till you let it run for a while

Maintenance Plans and Integrity checks

Just inherited a couple os SQL V7 servers and am reviewing the backups they
have been doing. New to SQL server.
In Enterprise Manager in the plan window I see two intregrity options
1) check database integrity (which is not checked)
2) verify the intergrity of the backup upon completion (checked)
What is number 2 doing?
If #1 is not checked, it seems to me their is no DBCC being run. So far I
cannot get their ops dept to send me the job logs for these backups so at
this point want to make sure from what I can see in Enterprise Manager that
they are running consistancy checks.
Thanks
Mike Lackey
Database Adminstration
Sybase/Oracle and enthusiastically trying to learn MSSQL
If #1 is not checked and there are no other jobs doing this that means there
are no DBCC CHECKDB's being run.
#2 refers to issuing a Verify against the backup after it completes and does
not pertain to DBCC's.
Andrew J. Kelly SQL MVP
"Mike Lackey" <MikeLackey@.discussions.microsoft.com> wrote in message
news:15060A70-AAE2-43D4-86C5-CCC0B08483A8@.microsoft.com...
> Just inherited a couple os SQL V7 servers and am reviewing the backups
they
> have been doing. New to SQL server.
> In Enterprise Manager in the plan window I see two intregrity options
> 1) check database integrity (which is not checked)
> 2) verify the intergrity of the backup upon completion (checked)
> What is number 2 doing?
> If #1 is not checked, it seems to me their is no DBCC being run. So far I
> cannot get their ops dept to send me the job logs for these backups so at
> this point want to make sure from what I can see in Enterprise Manager
that
> they are running consistancy checks.
> Thanks
> --
> Mike Lackey
> Database Adminstration
> Sybase/Oracle and enthusiastically trying to learn MSSQL

Maintenance Plans and Integrity checks

Just inherited a couple os SQL V7 servers and am reviewing the backups they
have been doing. New to SQL server.
In Enterprise Manager in the plan window I see two intregrity options
1) check database integrity (which is not checked)
2) verify the intergrity of the backup upon completion (checked)
What is number 2 doing?
If #1 is not checked, it seems to me their is no DBCC being run. So far I
cannot get their ops dept to send me the job logs for these backups so at
this point want to make sure from what I can see in Enterprise Manager that
they are running consistancy checks.
Thanks
--
Mike Lackey
Database Adminstration
Sybase/Oracle and enthusiastically trying to learn MSSQLIf #1 is not checked and there are no other jobs doing this that means there
are no DBCC CHECKDB's being run.
#2 refers to issuing a Verify against the backup after it completes and does
not pertain to DBCC's.
--
Andrew J. Kelly SQL MVP
"Mike Lackey" <MikeLackey@.discussions.microsoft.com> wrote in message
news:15060A70-AAE2-43D4-86C5-CCC0B08483A8@.microsoft.com...
> Just inherited a couple os SQL V7 servers and am reviewing the backups
they
> have been doing. New to SQL server.
> In Enterprise Manager in the plan window I see two intregrity options
> 1) check database integrity (which is not checked)
> 2) verify the intergrity of the backup upon completion (checked)
> What is number 2 doing?
> If #1 is not checked, it seems to me their is no DBCC being run. So far I
> cannot get their ops dept to send me the job logs for these backups so at
> this point want to make sure from what I can see in Enterprise Manager
that
> they are running consistancy checks.
> Thanks
> --
> Mike Lackey
> Database Adminstration
> Sybase/Oracle and enthusiastically trying to learn MSSQL