SQL SQLExpress: following

Showing posts with label following. Show all posts

Tuesday, March 27, 2012

@@identity

I'm running the following within a For Next loop but the
@.@.identity value doesn't always show up. It only shows
for every 3rd record. Any ideas on why this might be?
Thanks,
Ben Bleser
sql = "INSERT INTO table . . . . "
conn.execute(sql)
sql = "Select @.@.Identity as Id"
set rsTmp = conn.execute(sql)
RequestID = rsTmp("Id")
rsTmp.close
set rsTmp = nothing
Response.write RequestID & " "
Try putting them in the same sql command string; i.e.,
sql = "INSERT INTO tbl ...; SELECT @.@.identity AS ID;"
set rsTmp = conn.execute(sql)
RequestID = rsTmp("Id")
Also, just a performance enhancement, but if you're looping over the INSERT
statements you might consider putting your FOR...NEXT inside of the OPEN and
CLOSE statements. Opening and closing connections can add up to some
serious overhead.
"Ben Bleser" <bbleser@.airway.com> wrote in message
news:00bf01c54449$a62c9c50$a601280a@.phx.gbl...
> I'm running the following within a For Next loop but the
> @.@.identity value doesn't always show up. It only shows
> for every 3rd record. Any ideas on why this might be?
> Thanks,
> Ben Bleser
> sql = "INSERT INTO table . . . . "
> conn.execute(sql)
> sql = "Select @.@.Identity as Id"
> set rsTmp = conn.execute(sql)
> RequestID = rsTmp("Id")
> rsTmp.close
> set rsTmp = nothing
> Response.write RequestID & " "
|||Another way could be to passed the values to insert to a stored procedure
and to get a OUTPUT as a return code back which would represent the
identity.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"Michael C#" <howsa@.boutdat.com> schrieb im Newsbeitrag
news:OOD0dCFRFHA.204@.TK2MSFTNGP15.phx.gbl...
> Try putting them in the same sql command string; i.e.,
> sql = "INSERT INTO tbl ...; SELECT @.@.identity AS ID;"
> set rsTmp = conn.execute(sql)
> RequestID = rsTmp("Id")
> Also, just a performance enhancement, but if you're looping over the
> INSERT statements you might consider putting your FOR...NEXT inside of the
> OPEN and CLOSE statements. Opening and closing connections can add up to
> some serious overhead.
> "Ben Bleser" <bbleser@.airway.com> wrote in message
> news:00bf01c54449$a62c9c50$a601280a@.phx.gbl...
>

@@identity

I'm running the following within a For Next loop but the
@.@.identity value doesn't always show up. It only shows
for every 3rd record. Any ideas on why this might be?
Thanks,
Ben Bleser
sql = "INSERT INTO table . . . . "
conn.execute(sql)
sql = "Select @.@.Identity as Id"
set rsTmp = conn.execute(sql)
RequestID = rsTmp("Id")
rsTmp.close
set rsTmp = nothing
Response.write RequestID & " "Try putting them in the same sql command string; i.e.,
sql = "INSERT INTO tbl ...; SELECT @.@.identity AS ID;"
set rsTmp = conn.execute(sql)
RequestID = rsTmp("Id")
Also, just a performance enhancement, but if you're looping over the INSERT
statements you might consider putting your FOR...NEXT inside of the OPEN and
CLOSE statements. Opening and closing connections can add up to some
serious overhead.
"Ben Bleser" <bbleser@.airway.com> wrote in message
news:00bf01c54449$a62c9c50$a601280a@.phx.gbl...
> I'm running the following within a For Next loop but the
> @.@.identity value doesn't always show up. It only shows
> for every 3rd record. Any ideas on why this might be?
> Thanks,
> Ben Bleser
> sql = "INSERT INTO table . . . . "
> conn.execute(sql)
> sql = "Select @.@.Identity as Id"
> set rsTmp = conn.execute(sql)
> RequestID = rsTmp("Id")
> rsTmp.close
> set rsTmp = nothing
> Response.write RequestID & " "|||Another way could be to passed the values to insert to a stored procedure
and to get a OUTPUT as a return code back which would represent the
identity.
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"Michael C#" <howsa@.boutdat.com> schrieb im Newsbeitrag
news:OOD0dCFRFHA.204@.TK2MSFTNGP15.phx.gbl...
> Try putting them in the same sql command string; i.e.,
> sql = "INSERT INTO tbl ...; SELECT @.@.identity AS ID;"
> set rsTmp = conn.execute(sql)
> RequestID = rsTmp("Id")
> Also, just a performance enhancement, but if you're looping over the
> INSERT statements you might consider putting your FOR...NEXT inside of the
> OPEN and CLOSE statements. Opening and closing connections can add up to
> some serious overhead.
> "Ben Bleser" <bbleser@.airway.com> wrote in message
> news:00bf01c54449$a62c9c50$a601280a@.phx.gbl...
>> I'm running the following within a For Next loop but the
>> @.@.identity value doesn't always show up. It only shows
>> for every 3rd record. Any ideas on why this might be?
>> Thanks,
>> Ben Bleser
>> sql = "INSERT INTO table . . . . "
>> conn.execute(sql)
>> sql = "Select @.@.Identity as Id"
>> set rsTmp = conn.execute(sql)
>> RequestID = rsTmp("Id")
>> rsTmp.close
>> set rsTmp = nothing
>> Response.write RequestID & " "
>|||Hello Ben,
Take a look at SCOPE_IDNTITY() too - in books online.
Cheers
SQLCatZ

@@identity

I'm running the following within a For Next loop but the
@.@.identity value doesn't always show up. It only shows
for every 3rd record. Any ideas on why this might be?
Thanks,
Ben Bleser
sql = "INSERT INTO table . . . . "
conn.execute(sql)
sql = "Select @.@.Identity as Id"
set rsTmp = conn.execute(sql)
RequestID = rsTmp("Id")
rsTmp.close
set rsTmp = nothing
Response.write RequestID & " "Try putting them in the same sql command string; i.e.,
sql = "INSERT INTO tbl ...; SELECT @.@.identity AS ID;"
set rsTmp = conn.execute(sql)
RequestID = rsTmp("Id")
Also, just a performance enhancement, but if you're looping over the INSERT
statements you might consider putting your FOR...NEXT inside of the OPEN and
CLOSE statements. Opening and closing connections can add up to some
serious overhead.
"Ben Bleser" <bbleser@.airway.com> wrote in message
news:00bf01c54449$a62c9c50$a601280a@.phx.gbl...
> I'm running the following within a For Next loop but the
> @.@.identity value doesn't always show up. It only shows
> for every 3rd record. Any ideas on why this might be?
> Thanks,
> Ben Bleser
> sql = "INSERT INTO table . . . . "
> conn.execute(sql)
> sql = "Select @.@.Identity as Id"
> set rsTmp = conn.execute(sql)
> RequestID = rsTmp("Id")
> rsTmp.close
> set rsTmp = nothing
> Response.write RequestID & " "|||Another way could be to passed the values to insert to a stored procedure
and to get a OUTPUT as a return code back which would represent the
identity.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Michael C#" <howsa@.boutdat.com> schrieb im Newsbeitrag
news:OOD0dCFRFHA.204@.TK2MSFTNGP15.phx.gbl...
> Try putting them in the same sql command string; i.e.,
> sql = "INSERT INTO tbl ...; SELECT @.@.identity AS ID;"
> set rsTmp = conn.execute(sql)
> RequestID = rsTmp("Id")
> Also, just a performance enhancement, but if you're looping over the
> INSERT statements you might consider putting your FOR...NEXT inside of the
> OPEN and CLOSE statements. Opening and closing connections can add up to
> some serious overhead.
> "Ben Bleser" <bbleser@.airway.com> wrote in message
> news:00bf01c54449$a62c9c50$a601280a@.phx.gbl...
>

@@fetch_status in trigger

in the trigger for update and insert i have the following cursor

declare crs cursor static local for
SELECT [id] FROM inserted

open crs
fetch next from crs into @.v1

print @.@.fetch_status

while @.@.fetch_status = 0
begin
print @.v1

fetch next from crs into @.v1
end

close crs
deallocate crs

the problem is that the @.@.fetch_status is now always -1 and I'm pretty sure that it worked some time ago and i can't figure what is changed.

any ideas ?
thnx.Why would you want to do this? What's the real trigger look like?

Can you post that code?

A cursor in a trigger would more than likely perform poorly...|||I use cursor in trigger cause i can have inserts/updates from multiple sources and i don't want to break the logic.

code in attach|||fixed.

cursor threshold was 0; changed to -1

@@ERROR logic not working

Hi There

I am having a little trouble with something very simple:

I have a stored procedure with the following sql in it:

UPDATE mfsWS_CustNewCustomer SET Result_Id = 0 WHERE QueryGUID = @.Guid

IF (@.@.ERROR <> 0)

BEGIN

RAISERROR ('Error setting Result_Id', 16, 1)

UPDATE mfsWS_CustNewCustomer SET Result_Id = 1 WHERE QueryGUID = @.Guid

RETURN

END

To test if this works i SET the Result_Id = 'X' this is a smallint column so this should not work.

My issue is that all i get is the error:

cannot convert char to numeric etc etc

However the @.@.ERROR logic is not executed , my error is not raised and the update does nto happen, it si as though the entire sp aborts at the point where i try do the bogus update.

Is there something i should SET at the beginning of my sp or something ? How do i ensure that any error will be passed to the logic that checks @.@.ERROR and performs the appropriate actions ? This is SS2000, so i cannot use try catch.

Thanx

Unfortunately, you can't. There isn't a way to trap any and all kinds of errors in your T-SQL code, since some errors terminates the batch. T-SQL code after the point where the error happens, never gets executed. In your case, a conversion error are of the 'breaking' kind.

You can verify with a small example. Note that the last line never gets printed.

declare @.i int
set @.i = 'x'
print 'Error happened'
go

Server: Msg 245, Level 16, State 1, Line 2
Syntax error converting the varchar value 'x' to a column of data type int.

In order to trap all kinds of errors, you need to trap the at the client (calling side) of the procedure. (if there is a 'client' of some sorts)

You can find more info on errorhandling here.
http://www.sommarskog.se/error-handling-II.html

/Kenneth

|||

Thanx Kenneth

So what can i do ?

What is the point of doing a IF (@.@.ERROR) check after a delete update or insert then?

I have to update a certain table to set the error code to 1 if an error occurs, is there no way to do this?

Thanx

|||

Well, it's a bit of a tight spot. T-SQL errorhandling possibilites are somewhat limited pre SS2005. The one certain point that all errors go to is to the 'client'. In the case of a procedure, the 'client' is whatever executed it. If this is another proc, then you still have the same problem. If it's some other 'external' program, then that's where all errors will land.

Please take some time and read through the link about errorhandling above. It's well spent time, I assure you. Errorhandling is like any other 'project', it needs afterthought and desicions must be made about what to do in various situations. It needs to be designed with understanding in order to serve it's purpose best - to give us robust and reliable code.

In this case, I don't think that the mission is impossible, but rather not as straightforward as we would like, due to the limitations and behaviour of some errors in T-SQL. But, to grant yourself some insight, and possibly a direction to go on how you want to handle your situattion, http://www.sommarskog.se/error-handling-II.html is a good place to start. =;o)

/Kenneth

|||

Thanx Kewin

I will definately read the link.

Yes it is a problem i have an sp that calls an sp, i have written it so that if @.@.ERROR <> 0 i RETURN @.@.ERROR and handle it in the calling sp, but in this case the execution will abort and not return the @.@.ERROR to the calling sp to be handled.

But let me first read the link and i will post again.

Thank You

|||

Hi Kewin

Am i correct in saying that my specific test which has to do with char to numeric implicit cast will cause execution to abort BUT if the update or insert for example violated a primary key constriant or something more general that the execution would continue and execute the T-SQL in the IF @.@.ERROR <> 0 logic ?

|||Correct. Not all errors are transaction aborting or batch aborting errors. For example, syntax errors or compile-time errors cannot be caught with exception handling in SQL Server 2005 also. Please check out Erland's home page and the error handling topics in BOL.|||

To add a pennies worth, the big twist to error handling (even in 2005, though you have the control of try...catch) is the client. If you have a well behaved client like Query Analyzer or SSMS, then the IF@.@.error logic is very important, because it will always return control after an error if the error is not batch terminating. However, almost every client I see stops executing on an error and ends the batch itself. In those cases it is important to handle the errors with that in mind.

Things like

if @.@.error <> 0
begin
raiserror ('boom',16,1)
rollback transaction
return -100
end

Look good, but if the client never completes the message and returns control, you can be stuck with an open transaction. And even worse, you probably never reach the raiserror, rollback, or return if the error in the DML operation occurs.

But you have to be ready for it nevertheless...

|||

Thanx for all the feedback guys.

I have read the link Kewin and i know most of it but l did learn one or 2 new things, thanx.

I guess what i am really trying to ask is as far as SS2000 goes there is not anything more i can do in an stored procedure than :

IF @.@.ERROR <>0

BEGIN

RASIERROR

ROLLBACK TRAN

RETURN -1

END

These sp's are called by a .Net app i understand completely that the app must be ready for any kind of exception.

What i really want to confirm is that there is nothing more or better i could do from an sp error handling point of view?

Thanx

|||

You got it.
Basically, that's what is avaliable to us as far as errorhandling in T-SQL on SS 2000 goes.

A workaround of sorts around these limitations can be done, and when I think about it, that's pretty much what 'scrubbing' is all about. For known 'fatal' errors that you know you can't trap, and possibly could leave you in some sticky situation, it's common to try to ensure that the situation never happens, either by first validating thoroughly, or by different coding techniques.

eg, to avoid a PK violation when inserting a new row, you could first check for the existence, or (perhaps even better) do the insert based on a left joined select. In the latter case, if the PK didn't exist, 1 row would be affected, but should it already exist, 0 rows would be affected. But more importantly, it's not an error to do some DML and have zero rows affected. Approaching a problem from different angles may be of aid to circumvent some of the errorhandling limitations that we have.

/Kenneth

sql

Sunday, March 25, 2012

@@DBTS correct usage

Hi,

Suppose my Test table has a timestamp (rowversion) column, and I have the following code:

INSERT INTO Test ...

SELECT @.@.DBTS as 'TS'

Can I be sure the TS return column value contains the value of the timestamp column of the Test table? I suspect no in general (although it will work "most of the time"), but I would like a confirmation.

If I am correct, and there may be a mismatch, is there any clever way of getting the exact value, except from doing a SELECT WHERE ...?

Thanks.

Can I be sure the TS return column value contains the value of the timestamp column of the Test table?

If the BOL said "@.@.DBTS Returns the value of the current timestamp data type for the current database", then you couldn't.

Anyway, there are some other ways to find the records have been inserted.

|||

INSERT INTO Test ...
SELECT @.@.DBTS as 'TS'

You are correct, this will not work in a lot of cases since the timestamp (better to start using the rowversion synonym too) value is shared amongst all tables. It would require a database lock to prevent this from possibly failing

Doing a SELECT is the best way, and will almost always be really fast since:

1. You will likely have the primary key value
2. The row will be in cache since you just created it.

Replication timeout

Hi all

I changed the 'sa' password and after that I got the following errors in the subscription job history:

1-Connecting to Subscriber 'SAONT045\REPORT'
2-Login failed for user 'sa'.
3-The process could not connect to Subscriber 'SAONT045\REPORT'. The step failed.
4-The job failed. The Job was invoked by User sa. The last step to run was step 1 (Run agent.).

What can I do??
Are there anything that I can do to solve that problem without recreate the replication again?

Thanks in advance

Manuel Rodriguesthis is just a hunch but is the sa password the same on both the publisher and subscriber.|||Sounds like you need to check security settings on the Distribution agent. I usually set it to impersonate using Windows authentication, since both SQLAgent services are running under the same Windows account (easier to maintain).|||Make sure the credentials used by SQL serer agent successfully logs into subscriber
Masanam

Thursday, March 22, 2012

Regarding Openrowset

In the following line of code, does anyone know what the letter N represents
that appears right after the word Bulk
FROM OPENROWSET(BULK N'C:\Text1.txt', SINGLE_BLOB) AS Document"Alvin Clark" <alvin.clark@.accudata.com> wrote in message
news:e6kl7vYEIHA.4228@.TK2MSFTNGP02.phx.gbl...
> In the following line of code, does anyone know what the letter N
> represents that appears right after the word Bulk
> FROM OPENROWSET(BULK N'C:\Text1.txt', SINGLE_BLOB) AS Document
The letter "N" preceding a string constant indicates that the string is a
unicode string. For additional information, refer to BOL (index - unicode
constants)|||This one throws me a little too. However, it has to do with all the
different character sets that SQL Server supports (coalitions).
The string:
'normal string'
uses a character set English speakers would consider normal. However, we
ain't the only ones on the planet. So SQL Server also supports UNICODE (with
the variable types of nchar, nvarchar, & ntext).
The string:
N'København'
is a Unicode/nchar string (copied from 2000 BOL - NCHAR page)
Therefore, the N'string' syntax is a way of being safe with your literal
strings and making sure some change in the character set doesn't mess you up
somewhere.
Jay
(Man I hope I got this right. About to find out...)
"Alvin Clark" <alvin.clark@.accudata.com> wrote in message
news:e6kl7vYEIHA.4228@.TK2MSFTNGP02.phx.gbl...
> In the following line of code, does anyone know what the letter N
> represents that appears right after the word Bulk
> FROM OPENROWSET(BULK N'C:\Text1.txt', SINGLE_BLOB) AS Document
>

Hello,

the examples from MSDN like the following, don't run on my computer. A System.Data.SqlServer don't exist. No SQLCommand Type, no SQLContext.GetCommand Method and so on...

Ist there something wrong with my installation or how do i execute commands
inside a CLR SQL Server Trigger?

Regards,
Markus
Imports System
Imports System.Data
Imports System.Data.Sql
Imports System.Data.SqlServer
Imports System.Data.SqlTypes
Imports System.Text.RegularExpressions

Public Class EmailTrigger

Public Shared Sub EmailAudit()
Dim triggContext As SqlTriggerContext = _
SqlContext.GetTriggerContext()
Dim userName As String
If triggContext.TriggerAction = TriggerAction.Insert Then
Dim sqlComm As SqlCommand = SqlContext.GetCommand()
Dim sqlP As SqlPipe = SqlContext.GetPipe()
sqlComm.CommandText = "SELECT Username from INSERTED"
Dim dataRecord As Object = sqlComm.ExecuteScalar
userName = dataRecord.ToString
If IsValidEMailAddress(userName) Then
sqlComm.CommandText = "INSERT Emails " & _
"VALUES ('" & userName & "')"
sqlP.Send(sqlComm.CommandText)
sqlP.Execute(sqlComm)
End If
End If
End Sub

Public Shared Function IsValidEMailAddress(ByVal email As String) _
As Boolean
Return Regex.IsMatch(email, _
"^([\w-]+\.)*?[\w-]+@.[\w-]+\.([\w-]+\.)*?[\w]+$")
End Function

End Class

It seems that something like this would work:

Dim pipe As SqlPipe = SqlContext.Pipe
Dim sqlcommand As New System.Data.SqlClient.SqlCommand

sqlcommand.CommandText = _
"Update Haushaltshilfe set Wochentag = 'Montag' where Wochentag = 'Dienstag'"
pipe.ExecuteAndSend(sqlcommand)

sqlcommand.CommandText = "Select * from inserted"
pipe.ExecuteAndSend(sqlcommand)

But how do i get the results?

Is it really so that the examples from msdn with System.Data.SQLServer don't work?|||The reason why it's not working for you is because the example you're trying is old, and they have now merged the old SqlServer provider for System.Data.SqlServer with the client side provider and created a new namespace: Microsoft.SqlServer.Server. In addition some of the methods on the SqlContext object have been replaced with properties.
Replace the Import System.Data.SqlServer with Import Microsoft.SqlServer.Server and do an extra import for System.Data.SqlClient. Then in the code where you say GetTriggerContext, use the propery TriggerContext instead. you also need to create a connection: Dim sqlConn as SqlConnection = new SqlConnection("Context Connection=true") and the open the connection. From the connection you can create the command: sqlConn.CreateCommand. Instead of GetPipe you use the Pipe property and finally you call ExecuteAndSend on the Pipe with your command as param.
Niels

|||Thank you very much Niels!
Are there web-pages where the latest changes in VS 2005 and SQL Server are documented?|||You can check Bob B's blog and mine. We're trying keep track of what's changing.

Tuesday, March 20, 2012

/Reports and /ReportServer errors

I get the following messages in both the /Reports and /ReportServer
applications.
Server Error in '/Reports' Application
----
Configuration Error
Description: An error occurred during the processing of a configuration file
required to service this request. Please review the specific error details
below and modify your configuration file appropriately.
Parser Error Message: The XML file
c:\winnt\microsoft.net\framework\v1.1.4322\Config\machine.config could not be
loaded. Attempted to access an unloaded AppDomain.
Source Error:
[No relevant source lines]
Source File:
c:\winnt\microsoft.net\framework\v1.1.4322\Config\machine.config Line: 0
----
Version Information: Microsoft .NET Framework Version:1.1.4322.573; ASP.NET
Version:1.1.4322.573
And then:
Server Error in '/ReportServer' Application
----
Configuration Error
Description: An error occurred during the processing of a configuration file
required to service this request. Please review the specific error details
below and modify your configuration file appropriately.
Parser Error Message: The XML file
c:\winnt\microsoft.net\framework\v1.1.4322\Config\machine.config could not be
loaded. The process cannot access the file
"c:\winnt\microsoft.net\framework\v1.1.4322\Config\machine.config" because it
is being used by another process.
Source Error:
[No relevant source lines]
Source File:
c:\winnt\microsoft.net\framework\v1.1.4322\Config\machine.config Line: 0
----
Version Information: Microsoft .NET Framework Version:1.1.4322.573; ASP.NET
Version:1.1.4322.573
Any clues?I had this problem and found the solution in an earlier post: "Re: Report
Manager Config Error 8/31/2004 2:50 PM PST".
Turn off your anti-virus software and if that fixes the problem then exclude
the reporting services folder and sub-folders from scanning.
Personally I disable the virus scanning when I'm working with ASP.NET
applications as I noticed it affects performance generally.
Grahame
"Dan Fell" wrote:
> I get the following messages in both the /Reports and /ReportServer
> applications.
> Server Error in '/Reports' Application.
> ----
> Configuration Error
> Description: An error occurred during the processing of a configuration file
> required to service this request. Please review the specific error details
> below and modify your configuration file appropriately.
> Parser Error Message: The XML file
> c:\winnt\microsoft.net\framework\v1.1.4322\Config\machine.config could not be
> loaded. Attempted to access an unloaded AppDomain.
> Source Error:
> [No relevant source lines]
> Source File:
> c:\winnt\microsoft.net\framework\v1.1.4322\Config\machine.config Line: 0
> ----
> Version Information: Microsoft .NET Framework Version:1.1.4322.573; ASP.NET
> Version:1.1.4322.573
> And then:
> Server Error in '/ReportServer' Application.
> ----
> Configuration Error
> Description: An error occurred during the processing of a configuration file
> required to service this request. Please review the specific error details
> below and modify your configuration file appropriately.
> Parser Error Message: The XML file
> c:\winnt\microsoft.net\framework\v1.1.4322\Config\machine.config could not be
> loaded. The process cannot access the file
> "c:\winnt\microsoft.net\framework\v1.1.4322\Config\machine.config" because it
> is being used by another process.
> Source Error:
> [No relevant source lines]
> Source File:
> c:\winnt\microsoft.net\framework\v1.1.4322\Config\machine.config Line: 0
> ----
> Version Information: Microsoft .NET Framework Version:1.1.4322.573; ASP.NET
> Version:1.1.4322.573
> Any clues?

Sunday, March 11, 2012

.NET Runtime 2.0 Error with EventID 5000

Hi,

We are performing a SQL 2000 to SQL 2005 upgrade on a Windows 2003 SP1 server.

We encountered error in the SQL 2005 upgrade with the following error :

===================================

Common Language Runtime detected an invalid program.

===================================

Common Language Runtime detected an invalid program. (System.Xml)

Program Location:

at System.Xml.Schema.SchemaInfo..ctor()
at System.Xml.Schema.XmlSchemaSet..ctor(XmlNameTable nameTable)
at System.Xml.XmlReaderSettings.get_Schemas()
at Microsoft.SqlServer.UpgradeAdvisor.ReportViewer.UAReportController.LoadAndValidateDataFile()
at Microsoft.SqlServer.UpgradeAdvisor.ReportViewer.UAReport.ValidateDataFile()
at Microsoft.SqlServer.UpgradeAdvisor.ReportViewer.UAIssueReport.Refresh()
at Microsoft.SqlServer.UpgradeAdvisor.ReportPanel.OpenReport(String reportFile)

From the Event Viewer, under Application, we can see the following error :

EventID : 5000

Source :

EventType clr20r3, P1 bpacmd.exe, P2 2005.90.2047.0, P3 443f5935, P4 bpacmdx, P5 9.0.242.0, P6 443f5932, P7 7, P8 7a, P9 microsoft.bpa.common.bpaserver, P10 NIL.

We do urgently need some help now.

Thanks.

Mike,

I'm not sure what went wrong exactly, but seems a .net runtime issue. Wondering if you're willing to manually install .NET framework 2.0 before running the setup? You can download x86 version of .NET 2.0 from:

http://www.microsoft.com/downloads/details.aspx?familyid=0856eacb-4362-4b0d-8edd-aab15c5e04f5&displaylang=en

Thursday, March 8, 2012

.net framework and sql2000

I'm having a problem regeistering a downloaded program. All firewalls were disabled before downloading. When the CD Key is input the following message occurs:

"An error has occurred attempting to register your copy of Encompass, The underlying connection was closed: Unable to connect to remote server."

The desktop engine (MDSE) was checked and running. The CD Key is correct.

I was steered to this website because someone felt that this may be a .network connectivity issue to Encompass registration. After weeks back and forth with the software techs at Encompass they have given up.

Can someone please help me. I'm willing to try anything. Keep in mind I am not a "computer tech" person but I can read and follow directions. I'm desperate!!!

This isn't the correct forum for your question. You might try the database engine forum or the SQL Express forum (SQL Express is the successor to MSDE). Unfortunately even on those forums they may not know the details of how Encompass registration works. Perhaps you could escalate this to Encompass support management.

Saturday, February 25, 2012

.Net <-> SqlServer admin actions

Hi,
Is it possible in a programmatic way (.Net) to tell the SqlServer 2000 to do
the following things?
1.. Backup the transaction protocol
2.. Restore a database backup
3.. Run the actions from the transaction protocol
This scenario is useful for me to get the database state in case of an error
(application error and not SQL or hardware error) so that I can reproduce
and correct the error.
Any answer could help me a lot.
Sincerely,
George.Hi
Yes, there are T-SQL commands as BACKUP LOG ,RESTORE DATABASE ,BACKUP
DATABASE for more details please refer to the BOL. Personally , I prefer
calling a stored procedure from .NET for instance that does such jobs .

> 3.. Run the actions from the transaction protocol
What do you mean?
"George Homorozeanu" <george_homorozeanu@.hotmail.com> wrote in message
news:OpyWjCtOGHA.312@.TK2MSFTNGP12.phx.gbl...
> Hi,
>
> Is it possible in a programmatic way (.Net) to tell the SqlServer 2000 to
> do the following things?
>
> 1.. Backup the transaction protocol
> 2.. Restore a database backup
> 3.. Run the actions from the transaction protocol
>
> This scenario is useful for me to get the database state in case of an
> error (application error and not SQL or hardware error) so that I can
> reproduce and correct the error.
>
> Any answer could help me a lot.
>
> Sincerely,
> George.
>|||Hi,
With point 3 I wanted to say that after restoring the DB I have to restore
the transaction protocol in order to get the db state for which the error
occurred.
George.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eMGDSFtOGHA.4052@.TK2MSFTNGP15.phx.gbl...
> Hi
> Yes, there are T-SQL commands as BACKUP LOG ,RESTORE DATABASE ,BACKUP
> DATABASE for more details please refer to the BOL. Personally , I prefer
> calling a stored procedure from .NET for instance that does such jobs .
>
> What do you mean?
> "George Homorozeanu" <george_homorozeanu@.hotmail.com> wrote in message
> news:OpyWjCtOGHA.312@.TK2MSFTNGP12.phx.gbl...
>|||Hi
Ok, yes you can do that as well. There is an option of RESTORE LOG
DataBaseName FROM Disk
WITH RECOVERY, STOPAT = 'Jul 1, 2005 10:00 AM'
Agaim , much more details you can find in the BOL
"George Homorozeanu" <george_homorozeanu@.hotmail.com> wrote in message
news:O2u3xatOGHA.1088@.tk2msftngp13.phx.gbl...
> Hi,
> With point 3 I wanted to say that after restoring the DB I have to restore
> the transaction protocol in order to get the db state for which the error
> occurred.
>
> George.
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:eMGDSFtOGHA.4052@.TK2MSFTNGP15.phx.gbl...
>|||Thanks Uri,
Your answer helps me a lot. I will try to implement these things.
George.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:ua9GzgtOGHA.516@.TK2MSFTNGP15.phx.gbl...
> Hi
> Ok, yes you can do that as well. There is an option of RESTORE LOG
> DataBaseName FROM Disk
> WITH RECOVERY, STOPAT = 'Jul 1, 2005 10:00 AM'
> Agaim , much more details you can find in the BOL
>
>
> "George Homorozeanu" <george_homorozeanu@.hotmail.com> wrote in message
> news:O2u3xatOGHA.1088@.tk2msftngp13.phx.gbl...
>

.ldf, .mdf and .ndf file extensions

Hi,
Is the following interpretation correct for MS-SQL file extensions:
.ldf -> transaction log file
.mdf -> "master" data file
.ndf -> "normal" data file
Also, why is that the first data file that gets created in a database has
the extension .mdf? Is it in some way related to the "master" database?
Thanks,
- V
Here's the output of "use marathon; select * from dbo.sysfiles;" passed to
osql for an MS-SQL 2005 instance.
fileid groupid size maxsize growth status perf
name
filename
-- -- -- -- -- -- --
----
---- --
----
----
----
--
1 1 38400 51200 10 1048578 0
dataFile1
M:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\DATA\marathon_data1.mdf
2 0 12800 268435456 0 66 0
marathon_log1
M:\Program Files\Microsoft SQL
Server\MSSQL.4\MSSQL\DATA\marathon_log1.ldf
3 1 4096 -1 128 2 0
marathon_data4_444444
M:\Program Files\Microsoft SQL
Server\MSSQL.4\MSSQL\DATA\marathon_data4_444444.ndf
4 2 3968 -1 128 2 0
marathon_data2
M:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\DATA\marathon_data2.ndf
5 2 8576 -1 128 2 0
marathon_data3
M:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\DATA\marathon_data3.ndf
6 2 3840 -1 128 2 0
marathon_data5_55555555
M:\Program Files\Microsoft SQL
Server\MSSQL.4\MSSQL\DATA\marathon_data5_55555555.ndf
7 2 14208 -1 0 2 0
marathon_data6_66666666
M:\Program Files\Microsoft SQL
Server\MSSQL.4\MSSQL\DATA\marathon_data6_66666666.ndf
8 0 384 268435456 128 66 0
marathon_log2
M:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\DATA\marathon_log2.ldf
(8 rows affected)Found this in the Books Online. This answers my query.
File type File name extension
Primary data file .mdf
Secondary data file .ndf
Transaction log file .ldf
"Volcano" <volcano@.volcano.com> wrote in message
news:OjHsAb0pFHA.2504@.tk2msftngp13.phx.gbl...
Hi,
Is the following interpretation correct for MS-SQL file extensions:
.ldf -> transaction log file
.mdf -> "master" data file
.ndf -> "normal" data file
Also, why is that the first data file that gets created in a database has
the extension .mdf? Is it in some way related to the "master" database?
Thanks,
- V
Here's the output of "use marathon; select * from dbo.sysfiles;" passed to
osql for an MS-SQL 2005 instance.
fileid groupid size maxsize growth status perf
name
filename
-- -- -- -- -- -- --
----
---- --
----
----
----
--
1 1 38400 51200 10 1048578 0
dataFile1
M:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\DATA\marathon_data1.mdf
2 0 12800 268435456 0 66 0
marathon_log1
M:\Program Files\Microsoft SQL
Server\MSSQL.4\MSSQL\DATA\marathon_log1.ldf
3 1 4096 -1 128 2 0
marathon_data4_444444
M:\Program Files\Microsoft SQL
Server\MSSQL.4\MSSQL\DATA\marathon_data4_444444.ndf
4 2 3968 -1 128 2 0
marathon_data2
M:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\DATA\marathon_data2.ndf
5 2 8576 -1 128 2 0
marathon_data3
M:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\DATA\marathon_data3.ndf
6 2 3840 -1 128 2 0
marathon_data5_55555555
M:\Program Files\Microsoft SQL
Server\MSSQL.4\MSSQL\DATA\marathon_data5_55555555.ndf
7 2 14208 -1 0 2 0
marathon_data6_66666666
M:\Program Files\Microsoft SQL
Server\MSSQL.4\MSSQL\DATA\marathon_data6_66666666.ndf
8 0 384 268435456 128 66 0
marathon_log2
M:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\DATA\marathon_log2.ldf
(8 rows affected)|||Be aware that these are just conventions. SQL Server does not enforce or
require any particular file extensions for any data or log files. Still,
it is a good idea to stick to the conventions if you want to go on vacation
sometime.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Volcano" <volcano@.volcano.com> wrote in message
news:OjHsAb0pFHA.2504@.tk2msftngp13.phx.gbl...
> Hi,
> Is the following interpretation correct for MS-SQL file extensions:
> .ldf -> transaction log file
> .mdf -> "master" data file
> .ndf -> "normal" data file
> Also, why is that the first data file that gets created in a database has
> the extension .mdf? Is it in some way related to the "master" database?
> Thanks,
> - V
> Here's the output of "use marathon; select * from dbo.sysfiles;" passed to
> osql for an MS-SQL 2005 instance.
>
> fileid groupid size maxsize growth status perf
> name
> filename
> -- -- -- -- -- -- --
> ----
--
> ---- --
--
> ----
--
> ----
--
> ----
--
> --
> 1 1 38400 51200 10 1048578 0
> dataFile1
> M:\Program Files\Microsoft SQL
> Server\MSSQL.4\MSSQL\DATA\marathon_data1.mdf
> 2 0 12800 268435456 0 66 0
> marathon_log1
> M:\Program Files\Microsoft SQL
> Server\MSSQL.4\MSSQL\DATA\marathon_log1.ldf
> 3 1 4096 -1 128 2 0
> marathon_data4_444444
> M:\Program Files\Microsoft SQL
> Server\MSSQL.4\MSSQL\DATA\marathon_data4_444444.ndf
> 4 2 3968 -1 128 2 0
> marathon_data2
> M:\Program Files\Microsoft SQL
> Server\MSSQL.4\MSSQL\DATA\marathon_data2.ndf
> 5 2 8576 -1 128 2 0
> marathon_data3
> M:\Program Files\Microsoft SQL
> Server\MSSQL.4\MSSQL\DATA\marathon_data3.ndf
> 6 2 3840 -1 128 2 0
> marathon_data5_55555555
> M:\Program Files\Microsoft SQL
> Server\MSSQL.4\MSSQL\DATA\marathon_data5_55555555.ndf
> 7 2 14208 -1 0 2 0
> marathon_data6_66666666
> M:\Program Files\Microsoft SQL
> Server\MSSQL.4\MSSQL\DATA\marathon_data6_66666666.ndf
> 8 0 384 268435456 128 66 0
> marathon_log2
> M:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\DATA\marathon_log2.ldf
> (8 rows affected)
>

.ldf, .mdf and .ndf file extensions

Hi,
Is the following interpretation correct for MS-SQL file extensions:
.ldf -> transaction log file
.mdf -> "master" data file
.ndf -> "normal" data file
Also, why is that the first data file that gets created in a database has
the extension .mdf? Is it in some way related to the "master" database?
Thanks,
- V
Here's the output of "use marathon; select * from dbo.sysfiles;" passed to
osql for an MS-SQL 2005 instance.
fileid groupid size maxsize growth status perf
name
filename
-- -- -- -- -- -- --
----
---- --
----
----
----
--
1 1 38400 51200 10 1048578 0
dataFile1
M:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\DATA\marathon_data1.mdf
2 0 12800 268435456 0 66 0
marathon_log1
M:\Program Files\Microsoft SQL
Server\MSSQL.4\MSSQL\DATA\marathon_log1.ldf
3 1 4096 -1 128 2 0
marathon_data4_444444
M:\Program Files\Microsoft SQL
Server\MSSQL.4\MSSQL\DATA\marathon_data4_444444.ndf
4 2 3968 -1 128 2 0
marathon_data2
M:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\DATA\marathon_data2.ndf
5 2 8576 -1 128 2 0
marathon_data3
M:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\DATA\marathon_data3.ndf
6 2 3840 -1 128 2 0
marathon_data5_55555555
M:\Program Files\Microsoft SQL
Server\MSSQL.4\MSSQL\DATA\marathon_data5_55555555.ndf
7 2 14208 -1 0 2 0
marathon_data6_66666666
M:\Program Files\Microsoft SQL
Server\MSSQL.4\MSSQL\DATA\marathon_data6_66666666.ndf
8 0 384 268435456 128 66 0
marathon_log2
M:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\DATA\marathon_log2.ldf
(8 rows affected)Found this in the Books Online. This answers my query.
File type File name extension
Primary data file .mdf
Secondary data file .ndf
Transaction log file .ldf
"Volcano" <volcano@.volcano.com> wrote in message
news:OjHsAb0pFHA.2504@.tk2msftngp13.phx.gbl...
Hi,
Is the following interpretation correct for MS-SQL file extensions:
.ldf -> transaction log file
.mdf -> "master" data file
.ndf -> "normal" data file
Also, why is that the first data file that gets created in a database has
the extension .mdf? Is it in some way related to the "master" database?
Thanks,
- V
Here's the output of "use marathon; select * from dbo.sysfiles;" passed to
osql for an MS-SQL 2005 instance.
fileid groupid size maxsize growth status perf
name
filename
-- -- -- -- -- -- --
----
---- --
----
----
----
--
1 1 38400 51200 10 1048578 0
dataFile1
M:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\DATA\marathon_data1.mdf
2 0 12800 268435456 0 66 0
marathon_log1
M:\Program Files\Microsoft SQL
Server\MSSQL.4\MSSQL\DATA\marathon_log1.ldf
3 1 4096 -1 128 2 0
marathon_data4_444444
M:\Program Files\Microsoft SQL
Server\MSSQL.4\MSSQL\DATA\marathon_data4_444444.ndf
4 2 3968 -1 128 2 0
marathon_data2
M:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\DATA\marathon_data2.ndf
5 2 8576 -1 128 2 0
marathon_data3
M:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\DATA\marathon_data3.ndf
6 2 3840 -1 128 2 0
marathon_data5_55555555
M:\Program Files\Microsoft SQL
Server\MSSQL.4\MSSQL\DATA\marathon_data5_55555555.ndf
7 2 14208 -1 0 2 0
marathon_data6_66666666
M:\Program Files\Microsoft SQL
Server\MSSQL.4\MSSQL\DATA\marathon_data6_66666666.ndf
8 0 384 268435456 128 66 0
marathon_log2
M:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\DATA\marathon_log2.ldf
(8 rows affected)|||Be aware that these are just conventions. SQL Server does not enforce or
require any particular file extensions for any data or log files. Still,
it is a good idea to stick to the conventions if you want to go on vacation
sometime.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Volcano" <volcano@.volcano.com> wrote in message
news:OjHsAb0pFHA.2504@.tk2msftngp13.phx.gbl...
> Hi,
> Is the following interpretation correct for MS-SQL file extensions:
> .ldf -> transaction log file
> .mdf -> "master" data file
> .ndf -> "normal" data file
> Also, why is that the first data file that gets created in a database has
> the extension .mdf? Is it in some way related to the "master" database?
> Thanks,
> - V
> Here's the output of "use marathon; select * from dbo.sysfiles;" passed to
> osql for an MS-SQL 2005 instance.
>
> fileid groupid size maxsize growth status perf
> name
> filename
> -- -- -- -- -- -- --
> ----
> ---- --
> ----
> ----
> ----
> --
> 1 1 38400 51200 10 1048578 0
> dataFile1
> M:\Program Files\Microsoft SQL
> Server\MSSQL.4\MSSQL\DATA\marathon_data1.mdf
> 2 0 12800 268435456 0 66 0
> marathon_log1
> M:\Program Files\Microsoft SQL
> Server\MSSQL.4\MSSQL\DATA\marathon_log1.ldf
> 3 1 4096 -1 128 2 0
> marathon_data4_444444
> M:\Program Files\Microsoft SQL
> Server\MSSQL.4\MSSQL\DATA\marathon_data4_444444.ndf
> 4 2 3968 -1 128 2 0
> marathon_data2
> M:\Program Files\Microsoft SQL
> Server\MSSQL.4\MSSQL\DATA\marathon_data2.ndf
> 5 2 8576 -1 128 2 0
> marathon_data3
> M:\Program Files\Microsoft SQL
> Server\MSSQL.4\MSSQL\DATA\marathon_data3.ndf
> 6 2 3840 -1 128 2 0
> marathon_data5_55555555
> M:\Program Files\Microsoft SQL
> Server\MSSQL.4\MSSQL\DATA\marathon_data5_55555555.ndf
> 7 2 14208 -1 0 2 0
> marathon_data6_66666666
> M:\Program Files\Microsoft SQL
> Server\MSSQL.4\MSSQL\DATA\marathon_data6_66666666.ndf
> 8 0 384 268435456 128 66 0
> marathon_log2
> M:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\DATA\marathon_log2.ldf
> (8 rows affected)
>

.ldf, .mdf and .ndf file extensions

Hi,
Is the following interpretation correct for MS-SQL file extensions:
..ldf -> transaction log file
..mdf -> "master" data file
..ndf -> "normal" data file
Also, why is that the first data file that gets created in a database has
the extension .mdf? Is it in some way related to the "master" database?
Thanks,
- V
Here's the output of "use marathon; select * from dbo.sysfiles;" passed to
osql for an MS-SQL 2005 instance.
fileid groupid size maxsize growth status perf
name
filename
-- -- -- -- -- -- --
---- --
----
1 1 38400 51200 10 1048578 0
dataFile1
M:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\DATA\marathon_data1.mdf
2 0 12800 268435456 0 66 0
marathon_log1
M:\Program Files\Microsoft SQL
Server\MSSQL.4\MSSQL\DATA\marathon_log1.ldf
3 1 4096 -1 128 2 0
marathon_data4_444444
M:\Program Files\Microsoft SQL
Server\MSSQL.4\MSSQL\DATA\marathon_data4_444444.nd f
4 2 3968 -1 128 2 0
marathon_data2
M:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\DATA\marathon_data2.ndf
5 2 8576 -1 128 2 0
marathon_data3
M:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\DATA\marathon_data3.ndf
6 2 3840 -1 128 2 0
marathon_data5_55555555
M:\Program Files\Microsoft SQL
Server\MSSQL.4\MSSQL\DATA\marathon_data5_55555555. ndf
7 2 14208 -1 0 2 0
marathon_data6_66666666
M:\Program Files\Microsoft SQL
Server\MSSQL.4\MSSQL\DATA\marathon_data6_66666666. ndf
8 0 384 268435456 128 66 0
marathon_log2
M:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\DATA\marathon_log2.ldf
(8 rows affected)
Found this in the Books Online. This answers my query.
File type File name extension
Primary data file .mdf
Secondary data file .ndf
Transaction log file .ldf
"Volcano" <volcano@.volcano.com> wrote in message
news:OjHsAb0pFHA.2504@.tk2msftngp13.phx.gbl...
Hi,
Is the following interpretation correct for MS-SQL file extensions:
..ldf -> transaction log file
..mdf -> "master" data file
..ndf -> "normal" data file
Also, why is that the first data file that gets created in a database has
the extension .mdf? Is it in some way related to the "master" database?
Thanks,
- V
Here's the output of "use marathon; select * from dbo.sysfiles;" passed to
osql for an MS-SQL 2005 instance.
fileid groupid size maxsize growth status perf
name
filename
-- -- -- -- -- -- --
---- --
----
1 1 38400 51200 10 1048578 0
dataFile1
M:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\DATA\marathon_data1.mdf
2 0 12800 268435456 0 66 0
marathon_log1
M:\Program Files\Microsoft SQL
Server\MSSQL.4\MSSQL\DATA\marathon_log1.ldf
3 1 4096 -1 128 2 0
marathon_data4_444444
M:\Program Files\Microsoft SQL
Server\MSSQL.4\MSSQL\DATA\marathon_data4_444444.nd f
4 2 3968 -1 128 2 0
marathon_data2
M:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\DATA\marathon_data2.ndf
5 2 8576 -1 128 2 0
marathon_data3
M:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\DATA\marathon_data3.ndf
6 2 3840 -1 128 2 0
marathon_data5_55555555
M:\Program Files\Microsoft SQL
Server\MSSQL.4\MSSQL\DATA\marathon_data5_55555555. ndf
7 2 14208 -1 0 2 0
marathon_data6_66666666
M:\Program Files\Microsoft SQL
Server\MSSQL.4\MSSQL\DATA\marathon_data6_66666666. ndf
8 0 384 268435456 128 66 0
marathon_log2
M:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\DATA\marathon_log2.ldf
(8 rows affected)
|||Be aware that these are just conventions. SQL Server does not enforce or
require any particular file extensions for any data or log files. Still,
it is a good idea to stick to the conventions if you want to go on vacation
sometime.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Volcano" <volcano@.volcano.com> wrote in message
news:OjHsAb0pFHA.2504@.tk2msftngp13.phx.gbl...
> Hi,
> Is the following interpretation correct for MS-SQL file extensions:
> .ldf -> transaction log file
> .mdf -> "master" data file
> .ndf -> "normal" data file
> Also, why is that the first data file that gets created in a database has
> the extension .mdf? Is it in some way related to the "master" database?
> Thanks,
> - V
> Here's the output of "use marathon; select * from dbo.sysfiles;" passed to
> osql for an MS-SQL 2005 instance.
>
> fileid groupid size maxsize growth status perf
> name
> filename
> -- -- -- -- -- -- --
> ----
> ---- --
> ----
> ----
> ----
> --
> 1 1 38400 51200 10 1048578 0
> dataFile1
> M:\Program Files\Microsoft SQL
> Server\MSSQL.4\MSSQL\DATA\marathon_data1.mdf
> 2 0 12800 268435456 0 66 0
> marathon_log1
> M:\Program Files\Microsoft SQL
> Server\MSSQL.4\MSSQL\DATA\marathon_log1.ldf
> 3 1 4096 -1 128 2 0
> marathon_data4_444444
> M:\Program Files\Microsoft SQL
> Server\MSSQL.4\MSSQL\DATA\marathon_data4_444444.nd f
> 4 2 3968 -1 128 2 0
> marathon_data2
> M:\Program Files\Microsoft SQL
> Server\MSSQL.4\MSSQL\DATA\marathon_data2.ndf
> 5 2 8576 -1 128 2 0
> marathon_data3
> M:\Program Files\Microsoft SQL
> Server\MSSQL.4\MSSQL\DATA\marathon_data3.ndf
> 6 2 3840 -1 128 2 0
> marathon_data5_55555555
> M:\Program Files\Microsoft SQL
> Server\MSSQL.4\MSSQL\DATA\marathon_data5_55555555. ndf
> 7 2 14208 -1 0 2 0
> marathon_data6_66666666
> M:\Program Files\Microsoft SQL
> Server\MSSQL.4\MSSQL\DATA\marathon_data6_66666666. ndf
> 8 0 384 268435456 128 66 0
> marathon_log2
> M:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\DATA\marathon_log2.ldf
> (8 rows affected)
>

.fmt

Friday, February 24, 2012

.fmt

Hi,
How is it possible to create a .fmt file with the following informations if
they are required?
server1
database1
table1
Thanksbcp pubs..authors2 in
c:\new_auth.dat -fc:\authors.fmt -Sservername -Usa -Ppassword
Read Using Format File article in the bol"farshad"
<farshad@.discussions.microsoft.com> wrote in message
news:A9363EE5-807D-4BCB-9C2D-BC7ABCEDFB10@.microsoft.com...
> Hi,
> How is it possible to create a .fmt file with the following informations
> if
> they are required?
> server1
> database1
> table1
> Thanks|||Hi,
Does this mean I have to have a .dat file? How do I create it?
P.S. I run the bcp to insert using bulk insert.
Thanks
"Uri Dimant" wrote:
> bcp pubs..authors2 in
> c:\new_auth.dat -fc:\authors.fmt -Sservername -Usa -Ppassword
> Read Using Format File article in the bol"farshad"
> <farshad@.discussions.microsoft.com> wrote in message
> news:A9363EE5-807D-4BCB-9C2D-BC7ABCEDFB10@.microsoft.com...
> > Hi,
> > How is it possible to create a .fmt file with the following informations
> > if
> > they are required?
> > server1
> > database1
> > table1
> >
> > Thanks
>
>|||Hi
On SQL 2000 BCP has a format option that will create a format for you, see
Books online for more.
John
"farshad" <farshad@.discussions.microsoft.com> wrote in message
news:31F50883-0BF3-49B9-BB17-A45EB192F489@.microsoft.com...
> Hi,
> Does this mean I have to have a .dat file? How do I create it?
> P.S. I run the bcp to insert using bulk insert.
> Thanks
> "Uri Dimant" wrote:
>> bcp pubs..authors2 in
>> c:\new_auth.dat -fc:\authors.fmt -Sservername -Usa -Ppassword
>> Read Using Format File article in the bol"farshad"
>> <farshad@.discussions.microsoft.com> wrote in message
>> news:A9363EE5-807D-4BCB-9C2D-BC7ABCEDFB10@.microsoft.com...
>> > Hi,
>> > How is it possible to create a .fmt file with the following
>> > informations
>> > if
>> > they are required?
>> > server1
>> > database1
>> > table1
>> >
>> > Thanks
>>|||I would have if I could find it in books online.
Thanks
"John Bell" wrote:
> Hi
> On SQL 2000 BCP has a format option that will create a format for you, see
> Books online for more.
> John
> "farshad" <farshad@.discussions.microsoft.com> wrote in message
> news:31F50883-0BF3-49B9-BB17-A45EB192F489@.microsoft.com...
> > Hi,
> > Does this mean I have to have a .dat file? How do I create it?
> > P.S. I run the bcp to insert using bulk insert.
> > Thanks
> >
> > "Uri Dimant" wrote:
> >
> >> bcp pubs..authors2 in
> >> c:\new_auth.dat -fc:\authors.fmt -Sservername -Usa -Ppassword
> >> Read Using Format File article in the bol"farshad"
> >> <farshad@.discussions.microsoft.com> wrote in message
> >> news:A9363EE5-807D-4BCB-9C2D-BC7ABCEDFB10@.microsoft.com...
> >> > Hi,
> >> > How is it possible to create a .fmt file with the following
> >> > informations
> >> > if
> >> > they are required?
> >> > server1
> >> > database1
> >> > table1
> >> >
> >> > Thanks
> >>
> >>
> >>
>
>|||Hi
Try the online copy:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/coprompt/cp_bcp_61et.asp
John
farshad wrote:
> I would have if I could find it in books online.
> Thanks
> "John Bell" wrote:
> > Hi
> >
> > On SQL 2000 BCP has a format option that will create a format for you, see
> > Books online for more.
> >
> > John
> > "farshad" <farshad@.discussions.microsoft.com> wrote in message
> > news:31F50883-0BF3-49B9-BB17-A45EB192F489@.microsoft.com...
> > > Hi,
> > > Does this mean I have to have a .dat file? How do I create it?
> > > P.S. I run the bcp to insert using bulk insert.
> > > Thanks
> > >
> > > "Uri Dimant" wrote:
> > >
> > >> bcp pubs..authors2 in
> > >> c:\new_auth.dat -fc:\authors.fmt -Sservername -Usa -Ppassword
> > >> Read Using Format File article in the bol"farshad"
> > >> <farshad@.discussions.microsoft.com> wrote in message
> > >> news:A9363EE5-807D-4BCB-9C2D-BC7ABCEDFB10@.microsoft.com...
> > >> > Hi,
> > >> > How is it possible to create a .fmt file with the following
> > >> > informations
> > >> > if
> > >> > they are required?
> > >> > server1
> > >> > database1
> > >> > table1
> > >> >
> > >> > Thanks
> > >>
> > >>
> > >>
> >
> >
> >|||farshad wrote:
> I would have if I could find it in books online.
> Thanks
>
What is it you can't find? Try to look for bcp in Books On Line. That
will give you a number of "hits" - and also about bcp.fmt.
Regards
Steen

.fmt

Sunday, February 19, 2012

...Contains more than the maximum number of prefixes. The maximum is 3.

SQL Server 2000 8.00.760 (SP3)

I've been working on a test system and the following UDF worked fine.
It runs in the "current" database, and references another database on
the same server called 127-SuperQuote.

CREATE FUNCTION fnGetFormattedAddress(@.WorkID int)

RETURNS varchar(130)

BEGIN

DECLARE

@.Address1 As varchar(50)
@.ReturnAddress As varchar(130)

SELECT
@.Address1 = [127-SuperQuote].dbo.tblCompany.Address1
FROM
[Work] INNER JOIN
[127-SuperQuote].dbo.tblCompany ON [Work].ClientID =
[127-SuperQuote].dbo.tblCompany.CompanyID
WHERE
[Work].WorkID = @.WorkID

IF @.Address1 IS NOT NULL
SET @.ReturnAddress = @.ReturnAddress + @.Address1 + CHAR(13)+ CHAR(10)

RETURN @.ReturnAddress

END

So now the system has gone live and it turns out that the live
"SuperQuote" database is on a different server.

I've linked the server and changed the function as below, but I get an
error both in QA and when checking Syntax in the UDF builder:

The number name 'Zen.SuperQuote.dbo.tblCompany' contains more than the
maximum number of prefixes. The maximum is 3.

CREATE FUNCTION fnGetFormattedAddress(@.WorkID int)

RETURNS varchar(130)

BEGIN

DECLARE

@.Address1 As varchar(50)
@.ReturnAddress As varchar(130)

SELECT
@.Address1 = Zen.SuperQuote.dbo.tblCompany.Address1
FROM
[Work] INNER JOIN
Zen.SuperQuote.dbo.tblCompany ON [Work].ClientID =
Zen.SuperQuote.dbo.tblCompany.CompanyID
WHERE
[Work].WorkID = @.WorkID

IF @.Address1 IS NOT NULL
SET @.ReturnAddress = @.ReturnAddress + @.Address1 + CHAR(13)+ CHAR(10)

RETURN @.ReturnAddress

END

How can I get round this? By the way, I've rather simplified the
function to ease readability. Also, I haven't posted any DDL because I
don't think that's the problem!

Thanks

Edwardteddysnips@.hotmail.com wrote:
[...]

Alias, you dolt!

Sorry if I've wasted anyone's time.

Edward|||Thanks for wasting even more of everyone's time by not indicating your mistake and having this useless thread replicated to dozens of other forums!

From http://www.developmentnow.com/g/95_...ximum-is-3-.htm

Posted via DevelopmentNow.com Groups
http://www.developmentnow.com

Tuesday, March 27, 2012

@@identity

@@identity

@@identity

@@fetch_status in trigger

@@ERROR logic not working

Sunday, March 25, 2012

@@DBTS correct usage

Replication timeout

Thursday, March 22, 2012

Regarding Openrowset

Tuesday, March 20, 2012

/Reports and /ReportServer errors

Sunday, March 11, 2012

.NET Runtime 2.0 Error with EventID 5000

Thursday, March 8, 2012

.net framework and sql2000

Saturday, February 25, 2012

.Net <-> SqlServer admin actions

.ldf, .mdf and .ndf file extensions

.ldf, .mdf and .ndf file extensions

.ldf, .mdf and .ndf file extensions

.fmt

Friday, February 24, 2012

.fmt

.fmt

Sunday, February 19, 2012

...Contains more than the maximum number of prefixes. The maximum is 3.

SQL SQLExpress

Blog Archive

About Me