Profiler

I have a trace file that I captured from my production server that I want to replay on my dev box. The database on dev is a restore of the production backup, but the DB_ID of the two is different, which I believe is causing these errors:
No matching connection to execute this on. Will auto-create one.
and
Failed to set proper user name ('empireapi') for the connection
the empireapi username is in the Dev database. I have also dropped and re-created it.
Question:
Is the different databaseid the source of the problem? can I change it somehow? Do you have enough info to determine?
Thanks,
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
Yes. Yes. And Yes.
In books online there is a topic:
Replaying Traces
in the Administering SQL Server section. You can find the
requirements for replay traces listed there. Included is
that the database ids need to be the same.
To correct this, you can restore the master database from
the production server to the dev box.
The other work around would be to remove database ids from
the trace. Then set all the users captured in the trace to
have a default database of the target database. The commands
will then be executed in the default database so the
database id isn't an issue.
-Sue
On Thu, 10 Jun 2004 11:16:31 -0500, "Kevin3NF"
<KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote:

>I have a trace file that I captured from my production server that I want to replay on my dev box. The database on dev is a restore of the production backup, but the DB_ID of the two is different, which I believe is causing these errors:
>No matching connection to execute this on. Will auto-create one.
>and
>Failed to set proper user name ('empireapi') for the connection
>the empireapi username is in the Dev database. I have also dropped and re-created it.
>Question:
>Is the different databaseid the source of the problem? can I change it somehow? Do you have enough info to determine?
>Thanks,
|||Thanks Sue...I will get to one of your two excellent suggestions right away.
Fortunately, the DEV box is completely under my control for at least another
week, then it becomes a production box, so I can do anything I need to with
it :-)
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:d87ic0tqi2vj8g4vti340ukfa80corr6dj@.4ax.com... [vbcol=seagreen]
> Yes. Yes. And Yes.
> In books online there is a topic:
> Replaying Traces
> in the Administering SQL Server section. You can find the
> requirements for replay traces listed there. Included is
> that the database ids need to be the same.
> To correct this, you can restore the master database from
> the production server to the dev box.
> The other work around would be to remove database ids from
> the trace. Then set all the users captured in the trace to
> have a default database of the target database. The commands
> will then be executed in the default database so the
> database id isn't an issue.
> -Sue
> On Thu, 10 Jun 2004 11:16:31 -0500, "Kevin3NF"
> <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote:
to replay on my dev box. The database on dev is a restore of the production
backup, but the DB_ID of the two is different, which I believe is causing
these errors:[vbcol=seagreen]
re-created it.[vbcol=seagreen]
somehow? Do you have enough info to determine?
>

Product Level is Insufficient

I am receiving the following error when I try to execute an SSIS package that is saved to a structured file. I am using DTExec to execute the package.

Description: The task "Initialize Variables" cannot run on this edition of Integration Services. It requires a higher level edition.

After some research, I was able to find a supporting article in SQL Server 2005 BOL at http://msdn2.microsoft.com/en-us/library/aa337371.aspx

The issue was resolved by installing the Enterprise Edition of SQL Server Integration Services (Windows Service) on the local server (server where the package is saved and executed from). However, this goes against my original anticipation that the SSIS Windows Service would only need to be installed on a machine where I needed to store packages and/or monitor package execution. Not if I wanted to execute packages.

Unfortunately, my package is using advanced options (as identified in the BOL article previously referenced).

Am I missing something?

Sincerely,

Sean Fitzgerald

You are trying to execute these packages from a remote machine (such as your desktop and not the SQL Server), correct? You need to have a matching edition of the SSIS client tools loaded on your desktop to be able to run those packages remotely.|||You do need the SSIS installed where the packages runs.
It does not matter where the package is stored.

See my blog entries about this:
http://blogs.msdn.com/michen/archive/2006/08/11/package-exec-location.aspx
http://blogs.msdn.com/michen/archive/2006/11/11/ssis-product-level-is-insufficient.aspx

Product Key for MSDN - SQL Reporting

We have MSDN CD with SQL Reporting services 2000 on it. However, we do not
have any CD key with it. Also, there is no file in the CD which states the
product key. Where can we get it from?
Thanks in advanceI'm trying to remember but I don't think it has a key with it.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Deepti Hait" <Deepti Hait@.discussions.microsoft.com> wrote in message
news:B0619563-472E-467A-B70A-0C31A3A68219@.microsoft.com...
> We have MSDN CD with SQL Reporting services 2000 on it. However, we do not
> have any CD key with it. Also, there is no file in the CD which states the
> product key. Where can we get it from?
> Thanks in advance

Producing Text File

Hello all,
My sql skills are pretty poor, so please bear with me!

I need to take info from two tables (in Access db) and produce two txt files.

The data in the first table is listed as follows: OrderID, OrderDate, CustomerID, Total
The data in the second table is listed as follows: OrderID, Quantity, Discount, UnitPrice

The user has to be able to specify a time frame (start date and end date).

The major problem I'm having is with setting up the txt files. The header has to have the OrderID listed as "!!OrderID" (this is the way the fake company has their db set up apparently) Also, in the actual data in the txt files the OrderDate has to start w/ "#" and the CustomerID has to start w/ "&".

here's what I mean:

!!OrderID OrderDate CustomerID Total
--- --- ---- --
123456 #456789 &abcdef 987654

If you can help me at all, I'd much appreciate it!!! Thank you!Well, in MS Access you can't use "!" in column name, so you'll have to handle this using VBA coding and replace column name when writing into the text file. You didn't describe tables relationship.

SELECT
[OrderID],
"#" + [OrderDate] AS [OrderDate],
"&" + [CustomerID] AS [CustomerID],
[Total]
from
your_table;

store the result into some VBA object (I don't remember which one to use, it's long time since I used Access for last time). Open text file and read row by row from VBA object and write it into the file. Replace column name OrderID with !!OrderID|||Thanks for the help. The tables are linked through OrderID. Also, I found out that I don't have to use Access to do the project. MySQL is allowed as well. If that makes a difference w/ the !! problem, let me know.

Thanks again!

Processing NULL values

I have an input file that may have NULL's in it's fields. The fields are of a various data types.

The NULL value is represented by ? - question mark.

Is there a way to set package or file connection up in a way - so it treats ? as NULL?

The only other way I found is to use Derived column - and so I have to create derived field for most of the fields (150 of them) since a lot of them may contain ? - or NULL.

Any ideas?

You could build your own custom source adapter but it be quicker to use the derived column.

-Jamie

|||

what adds pain is that two operations must occur for each column: relace ? with null and data conversion.

So, I have to create following expression in Derived Column for most columns:

[fiel1] == "?" ? NULL : (DT_DTAE) [fiel1]

and repeat this 150 times...

Processing a new report before printing

I am running SSRS 2005, rendering reports locally using a report viewer. Rather than direct the viewer to an .rdlc file, I use an XMLDocument. A few of my reports have a large image in the background that needs to be invisible when the report prints. This was straightforward - I just use a report parameter and set the visible state of the image to the value of the parameter. The hard part is getting the report to print without an error.

Initially, I render the report with the following code:

Private Sub ShowReport()

Try

With Me.ReportViewer1

.Reset()

.ProcessingMode = Microsoft.Reporting.WinForms.ProcessingMode.Local

.LocalReport.LoadReportDefinition(New System.IO.StringReader(_Doc.OuterXml))

.LocalReport.DataSources.Add(_Item1)

.LocalReport.DataSources.Add(_Item2)

.LocalReport.SetParameters(_Param)

.RefreshReport()

End With

Catch ex As Exception

MsgBox(ex.ToString)

End Try

End Sub

This code works fine. I have hidden the print button on the report viewer, and to print, the user must press my button which runs the following code.

Private Sub PrintReport()

Try

_Param(0) = New Microsoft.Reporting.WinForms.ReportParameter("ImageVisible", "False")

ShowReport()

ReportViewer1.PrintDialog()

Catch ex As Exception

MsgBox(ex.ToString)

End Try

End Sub

Resetting the parameter and re-displaying the report works fine on its own. The PrintDialog method works fine on its own. When combined in the same Sub like this, I get the following error:

"Operation is not valid due to the current state of the object".

Does anyone know how I could get it to print without an error? I would be very grateful for any help.

Solved it. The problem is caused because the report has not finished rendering when the PrintDialog method is called. I got around it using the RenderingComplete event of the report viewer.

Process blocking DataBase [BULK-OP-DB]

Hil all,
I have a problem wiht a process that I thing is used for auto growth file
database (is automatic 10%)
This problem not finish and block all connection to database:
Process Info:
ID: xxx
Block type: DB
Mode: NULL
State: GRANT
Own: Xact
Resource: [BULK-OP-DB] and [BULK-OP-LOG]
What can I do for resolve it?
Thanks in advancedHi
Don't use autogrow. Rather Manage the Db's correctly and grow them manually,
in a controlled manner, when usage is least.
The problem is that the space created by the grow is not available to any
process until the grow is complete. Those pages are locked so it looks like
the auto grow is blocking.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Mario Barro" wrote:
> Hil all,
> I have a problem wiht a process that I thing is used for auto growth file
> database (is automatic 10%)
> This problem not finish and block all connection to database:
> Process Info:
> ID: xxx
> Block type: DB
> Mode: NULL
> State: GRANT
> Own: Xact
> Resource: [BULK-OP-DB] and [BULK-OP-LOG]
> What can I do for resolve it?
> Thanks in advanced
>
>|||Thank Mike;
I grow file data manually and problem has solved.
I haven´t problems wiht others database and automatic grow, but this
database has 24 GB.
Regards
Mario Barro
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> escribió en el mensaje
news:BBCBB823-861E-41D3-A5AD-566E3FF4A041@.microsoft.com...
> Hi
> Don't use autogrow. Rather Manage the Db's correctly and grow them
manually,
> in a controlled manner, when usage is least.
> The problem is that the space created by the grow is not available to any
> process until the grow is complete. Those pages are locked so it looks
like
> the auto grow is blocking.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "Mario Barro" wrote:
> > Hil all,
> >
> > I have a problem wiht a process that I thing is used for auto growth
file
> > database (is automatic 10%)
> >
> > This problem not finish and block all connection to database:
> >
> > Process Info:
> >
> > ID: xxx
> > Block type: DB
> > Mode: NULL
> > State: GRANT
> > Own: Xact
> > Resource: [BULK-OP-DB] and [BULK-OP-LOG]
> >
> > What can I do for resolve it?
> >
> > Thanks in advanced
> >
> >
> >|||Yes, and 10% of that is 2.4 GB! That will take some, not a lot, but some
time. I agree with Mike in that you should follow best practices and, as
the DBA, make sure the space is pre-allocated before needed. However, one
of SQL Server's strengths is its ease of administration.
As an alternative, if you know what your approximate BULK LOAD operations
size per load is, you could configure AUTOGROW at a fixed SIZE instead of a
fixed PERCENT. Something like 128, 256, or 512 MB. You'd still get a chunk
of space and would stall, but these would be on the order of 5 to 20 times
smaller, thus 1/5 th to 1/20 the duration, than the 10% AUTOGROW.
Hope this helps.
Sincerely,
Anthony Thomas
"Mario Barro" <newsss@.QUITAMEya.com> wrote in message
news:uyXpGzbYFHA.584@.TK2MSFTNGP15.phx.gbl...
Thank Mike;
I grow file data manually and problem has solved.
I haven´t problems wiht others database and automatic grow, but this
database has 24 GB.
Regards
Mario Barro
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> escribió en el mensaje
news:BBCBB823-861E-41D3-A5AD-566E3FF4A041@.microsoft.com...
> Hi
> Don't use autogrow. Rather Manage the Db's correctly and grow them
manually,
> in a controlled manner, when usage is least.
> The problem is that the space created by the grow is not available to any
> process until the grow is complete. Those pages are locked so it looks
like
> the auto grow is blocking.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "Mario Barro" wrote:
> > Hil all,
> >
> > I have a problem wiht a process that I thing is used for auto growth
file
> > database (is automatic 10%)
> >
> > This problem not finish and block all connection to database:
> >
> > Process Info:
> >
> > ID: xxx
> > Block type: DB
> > Mode: NULL
> > State: GRANT
> > Own: Xact
> > Resource: [BULK-OP-DB] and [BULK-OP-LOG]
> >
> > What can I do for resolve it?
> >
> > Thanks in advanced
> >
> >
> >