SQL server connection: analyzer

Showing posts with label analyzer. Show all posts

Saturday, February 25, 2012

Annoying problem with SQL Server 2000 Query Analyzer

When I open a saved sql file it opens fine, but when I try to load a
different file in the same query window, QA hangs on "Loading SQL Query...".
Does anybody have any ideas on why this is happening? I already removed and
re-installed SQL Server (client tools only) and applied SP3.
Thanks
Dan
Have a look at
http://support.microsoft.com/default.aspx?kbid=830767
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Daniel Reber" <nospam@.nospam.com> wrote in message
news:eFtkS1SKEHA.2144@.TK2MSFTNGP10.phx.gbl...
> When I open a saved sql file it opens fine, but when I try to load a
> different file in the same query window, QA hangs on "Loading SQL
Query...".
> Does anybody have any ideas on why this is happening? I already removed
and
> re-installed SQL Server (client tools only) and applied SP3.
> Thanks
> Dan
>
|||Thanks for the link...I am on hold with MS right now to get the hotfix.
Terrible music...
Dan Reber
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:O7c6naTKEHA.1192@.TK2MSFTNGP11.phx.gbl...
> Have a look at
> http://support.microsoft.com/default.aspx?kbid=830767
> --
> HTH
> Jasper Smith (SQL Server MVP)
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
>
> "Daniel Reber" <nospam@.nospam.com> wrote in message
> news:eFtkS1SKEHA.2144@.TK2MSFTNGP10.phx.gbl...
> Query...".
> and
>
|||Hi Daniel,
It seems you request the hotfix mentioned in the article:
830767 FIX: SQL Query Analyzer May Stop Responding When You Close a Query
http://support.microsoft.com/?id=830767
Please contact Microsoft Product Support Services to obtain the hotfix. For
a complete list of Microsoft Product Support Services phone numbers and
information about support costs, visit the following Microsoft Web site:
http://support.microsoft.com/default.aspx?scid=fh;[LN];CNTACTMS
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
|||The hotfix corrected my issue, thanks.
Dan Reber
""Yuan Shao"" <v-yshao@.online.microsoft.com> wrote in message
news:jWUPnlUKEHA.2360@.cpmsftngxa10.phx.gbl...
> Hi Daniel,
> It seems you request the hotfix mentioned in the article:
> 830767 FIX: SQL Query Analyzer May Stop Responding When You Close a Query
> http://support.microsoft.com/?id=830767
> Please contact Microsoft Product Support Services to obtain the hotfix.
For
> a complete list of Microsoft Product Support Services phone numbers and
> information about support costs, visit the following Microsoft Web site:
> http://support.microsoft.com/default.aspx?scid=fh;[LN];CNTACTMS
> Regards,
> Michael Shao
> Microsoft Online Partner Support
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
>
|||What number do you call? I'm not spending 99 bucks for this hotfix!
BigMac
"Daniel Reber" <nospam@.nospam.com> wrote in message
news:OiS5GsTKEHA.1192@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> Thanks for the link...I am on hold with MS right now to get the hotfix.
> Terrible music...
> Dan Reber
> "Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
> news:O7c6naTKEHA.1192@.TK2MSFTNGP11.phx.gbl...
removed
>
|||How much did it cost, Daniel?
BigMac
"Daniel Reber" <nospam@.nospam.com> wrote in message
news:%23wkc66UKEHA.3472@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> The hotfix corrected my issue, thanks.
> Dan Reber
> ""Yuan Shao"" <v-yshao@.online.microsoft.com> wrote in message
> news:jWUPnlUKEHA.2360@.cpmsftngxa10.phx.gbl...
Query[vbcol=seagreen]
> For
rights.
>
|||Why is MS not providing easier access to this fix? Can people share hotfixes
to avoid customer support? (I'm legit, just hate dealing with them).
"Daniel Reber" wrote:

> The hotfix corrected my issue, thanks.
> Dan Reber
> ""Yuan Shao"" <v-yshao@.online.microsoft.com> wrote in message
> news:jWUPnlUKEHA.2360@.cpmsftngxa10.phx.gbl...
> For
>
>
|||In general, we make you call in and talk to a support engineer because we
want to track who is using which hotfix. These things aren't tested to the
same degree as service packs and if we run into a serious problem with one
of them, it's nice to be able to figure out who we need to inform. In
theory. :-) I say that because I'm not on the support team and I'm not sure
how stringently that is followed. We also want to make sure you're actually
running into the specific problem that the hotfix corrects. Again, because
they're not tested as thoroughly and we don't advise running them without
need.
Once the support engineer has confirmed that you're hitting a bug fixed by
the hotfix, they'll refund your money.
Sincerely,
Stephen Dybing
This posting is provided "AS IS" with no warranties, and confers no rights.
"Brian T" <Brian T@.discussions.microsoft.com> wrote in message
news:E70AD0EA-1241-41E1-A951-2206780E15B4@.microsoft.com...[vbcol=seagreen]
> Why is MS not providing easier access to this fix? Can people share
> hotfixes
> to avoid customer support? (I'm legit, just hate dealing with them).
> "Daniel Reber" wrote:

annoying cut and paste behavior in query analyzer

background: sql2k on nt5 box..
is there a way to get rid of all those syntax such as
'COLLATE SQL_Latin1_General_CP1_CI_AS '
'[ ]' when cut and paste table script from EM to Query Analyzer?
CREATE TABLE [analyte_property] (
[ESPKEY] [int] NOT NULL ,
[method_1] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[method_2] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[method_3] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[method_4] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[method_5] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[method_6] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[method_7] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[method_8] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[method_9] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[method_10] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
it would be ideal to have script look like this
CREATE TABLE analyte_property (
ESPKEY int NOT NULL ,
method_1 char (35) NULL ,
method_2 char (35) NULL ,
method_3 char (35) NULL ,
method_4 char (35) NULL ,
method_5 char (35) NULL ,
method_6 char (35) NULL ,
method_7 char (35) NULL ,
method_8 char (35) NULL ,
method_9 char (35) NULL ,
method_10 char (35) NULL
) ON PRIMARY
GO
Thank you.Steve,
In the 'Generate sql script' dialog box 'formatting' tab, check the option
'Script sql7 comptatible feature'.
--
Dinesh
SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com
"== Steve Pdx==" <lins@.nospam.portptld.com> wrote in message
news:Odzd0eSPEHA.252@.TK2MSFTNGP10.phx.gbl...
> background: sql2k on nt5 box..
>
> is there a way to get rid of all those syntax such as
> 'COLLATE SQL_Latin1_General_CP1_CI_AS '
> '[ ]' when cut and paste table script from EM to Query Analyzer?
>
> CREATE TABLE [analyte_property] (
> [ESPKEY] [int] NOT NULL ,
> [method_1] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [method_2] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [method_3] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [method_4] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [method_5] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [method_6] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [method_7] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [method_8] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [method_9] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [method_10] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> it would be ideal to have script look like this
> CREATE TABLE analyte_property (
> ESPKEY int NOT NULL ,
> method_1 char (35) NULL ,
> method_2 char (35) NULL ,
> method_3 char (35) NULL ,
> method_4 char (35) NULL ,
> method_5 char (35) NULL ,
> method_6 char (35) NULL ,
> method_7 char (35) NULL ,
> method_8 char (35) NULL ,
> method_9 char (35) NULL ,
> method_10 char (35) NULL
> ) ON PRIMARY
> GO
>
> Thank you.
>
>|||Thank you, but i already knew that.
that's how i got rid of 'COLLATE SQL_Latin1_General_CP1_CI_AS '
when i use EM, genrate sql script function.
i was referring highlight a table in EM, ctrl+c, and ctrl+p in QA.
besides, is there a way to get rid of those square brackets?
"Dinesh T.K" <tkdinesh@.nospam.mail.tkdinesh.com> wrote in message
news:evyaAmSPEHA.2716@.tk2msftngp13.phx.gbl...
> Steve,
> In the 'Generate sql script' dialog box 'formatting' tab, check the option
> 'Script sql7 comptatible feature'.
> --
> Dinesh
> SQL Server MVP
> --
> --
> SQL Server FAQ at
> http://www.tkdinesh.com
> "== Steve Pdx==" <lins@.nospam.portptld.com> wrote in message
> news:Odzd0eSPEHA.252@.TK2MSFTNGP10.phx.gbl...
> > background: sql2k on nt5 box..
> >
> >
> > is there a way to get rid of all those syntax such as
> > 'COLLATE SQL_Latin1_General_CP1_CI_AS '
> > '[ ]' when cut and paste table script from EM to Query Analyzer?
> >
> >
> >
> > CREATE TABLE [analyte_property] (
> > [ESPKEY] [int] NOT NULL ,
> > [method_1] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [method_2] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [method_3] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [method_4] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [method_5] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [method_6] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [method_7] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [method_8] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [method_9] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [method_10] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> > ) ON [PRIMARY]
> > GO
> >
> > it would be ideal to have script look like this
> >
> > CREATE TABLE analyte_property (
> > ESPKEY int NOT NULL ,
> > method_1 char (35) NULL ,
> > method_2 char (35) NULL ,
> > method_3 char (35) NULL ,
> > method_4 char (35) NULL ,
> > method_5 char (35) NULL ,
> > method_6 char (35) NULL ,
> > method_7 char (35) NULL ,
> > method_8 char (35) NULL ,
> > method_9 char (35) NULL ,
> > method_10 char (35) NULL
> >
> > ) ON PRIMARY
> > GO
> >
> >
> >
> > Thank you.
> >
> >
> >
>|||I've seen this kind of post so many times...
When you use a GUI interface to do programming you get garbage - square
brackets, verbose collation - whatever...
Using EM to design tables, or create indexed is ACCESS-like. EM is the
"lowest common denominator"...
Our first-commandment in our shop is to script everything. We script BULK
INSERT's, INDEX creation, table creation - STORED PROC creation.
That means we create text files, with file extensions of .SQL and use those
in QA to make changes to the database. Not doing it this way would mean we
were a single-stop shop - not developing code for the hundreds of clients we
want to meet.
When we script a SPROC creation we even add the "GRANT..." permission code -
so that the proper DB role has access to the SPROC. If you use EM to output
a script of a SPROC it doesn't contain any GRANT info - that's weak.
Having a text file for every DB change or definition means we can use VISUAL
SOURCE safe... Yadda, yadda, yadda...
Sorry for ranting...
"== Steve Pdx==" <lins@.nospam.portptld.com> wrote in message
news:OzjrgJTPEHA.3096@.TK2MSFTNGP09.phx.gbl...
> Thank you, but i already knew that.
> that's how i got rid of 'COLLATE SQL_Latin1_General_CP1_CI_AS '
> when i use EM, genrate sql script function.
> i was referring highlight a table in EM, ctrl+c, and ctrl+p in QA.
> besides, is there a way to get rid of those square brackets?
>
> "Dinesh T.K" <tkdinesh@.nospam.mail.tkdinesh.com> wrote in message
> news:evyaAmSPEHA.2716@.tk2msftngp13.phx.gbl...
> > Steve,
> >
> > In the 'Generate sql script' dialog box 'formatting' tab, check the
option
> > 'Script sql7 comptatible feature'.
> >
> > --
> > Dinesh
> > SQL Server MVP
> > --
> > --
> > SQL Server FAQ at
> > http://www.tkdinesh.com
> >
> > "== Steve Pdx==" <lins@.nospam.portptld.com> wrote in message
> > news:Odzd0eSPEHA.252@.TK2MSFTNGP10.phx.gbl...
> > > background: sql2k on nt5 box..
> > >
> > >
> > > is there a way to get rid of all those syntax such as
> > > 'COLLATE SQL_Latin1_General_CP1_CI_AS '
> > > '[ ]' when cut and paste table script from EM to Query Analyzer?
> > >
> > >
> > >
> > > CREATE TABLE [analyte_property] (
> > > [ESPKEY] [int] NOT NULL ,
> > > [method_1] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > > [method_2] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > > [method_3] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > > [method_4] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > > [method_5] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > > [method_6] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > > [method_7] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > > [method_8] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > > [method_9] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > > [method_10] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> > > ) ON [PRIMARY]
> > > GO
> > >
> > > it would be ideal to have script look like this
> > >
> > > CREATE TABLE analyte_property (
> > > ESPKEY int NOT NULL ,
> > > method_1 char (35) NULL ,
> > > method_2 char (35) NULL ,
> > > method_3 char (35) NULL ,
> > > method_4 char (35) NULL ,
> > > method_5 char (35) NULL ,
> > > method_6 char (35) NULL ,
> > > method_7 char (35) NULL ,
> > > method_8 char (35) NULL ,
> > > method_9 char (35) NULL ,
> > > method_10 char (35) NULL
> > >
> > > ) ON PRIMARY
> > > GO
> > >
> > >
> > >
> > > Thank you.
> > >
> > >
> > >
> >
> >
>|||Steve,
None that Iam aware of, except may be a mass replace of 'COLLATE ...' to ''
:-)
>> besides, is there a way to get rid of those square brackets?
Why?In case, any of the column name was like 'method 1' then without [], the
script would fail.
--
Dinesh
SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com
"== Steve Pdx==" <lins@.nospam.portptld.com> wrote in message
news:OzjrgJTPEHA.3096@.TK2MSFTNGP09.phx.gbl...
> Thank you, but i already knew that.
> that's how i got rid of 'COLLATE SQL_Latin1_General_CP1_CI_AS '
> when i use EM, genrate sql script function.
> i was referring highlight a table in EM, ctrl+c, and ctrl+p in QA.
> besides, is there a way to get rid of those square brackets?
>
> "Dinesh T.K" <tkdinesh@.nospam.mail.tkdinesh.com> wrote in message
> news:evyaAmSPEHA.2716@.tk2msftngp13.phx.gbl...
> > Steve,
> >
> > In the 'Generate sql script' dialog box 'formatting' tab, check the
option
> > 'Script sql7 comptatible feature'.
> >
> > --
> > Dinesh
> > SQL Server MVP
> > --
> > --
> > SQL Server FAQ at
> > http://www.tkdinesh.com
> >
> > "== Steve Pdx==" <lins@.nospam.portptld.com> wrote in message
> > news:Odzd0eSPEHA.252@.TK2MSFTNGP10.phx.gbl...
> > > background: sql2k on nt5 box..
> > >
> > >
> > > is there a way to get rid of all those syntax such as
> > > 'COLLATE SQL_Latin1_General_CP1_CI_AS '
> > > '[ ]' when cut and paste table script from EM to Query Analyzer?
> > >
> > >
> > >
> > > CREATE TABLE [analyte_property] (
> > > [ESPKEY] [int] NOT NULL ,
> > > [method_1] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > > [method_2] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > > [method_3] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > > [method_4] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > > [method_5] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > > [method_6] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > > [method_7] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > > [method_8] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > > [method_9] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > > [method_10] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> > > ) ON [PRIMARY]
> > > GO
> > >
> > > it would be ideal to have script look like this
> > >
> > > CREATE TABLE analyte_property (
> > > ESPKEY int NOT NULL ,
> > > method_1 char (35) NULL ,
> > > method_2 char (35) NULL ,
> > > method_3 char (35) NULL ,
> > > method_4 char (35) NULL ,
> > > method_5 char (35) NULL ,
> > > method_6 char (35) NULL ,
> > > method_7 char (35) NULL ,
> > > method_8 char (35) NULL ,
> > > method_9 char (35) NULL ,
> > > method_10 char (35) NULL
> > >
> > > ) ON PRIMARY
> > > GO
> > >
> > >
> > >
> > > Thank you.
> > >
> > >
> > >
> >
> >
>

Sunday, February 19, 2012

Analyzer Tools

Hi,
Is there any tools can help me doing the analysis using SQL Profiler (trc)
files?
Thanks,
Kenny
See if my scripts can help:
http://vyaskn.tripod.com/analyzing_profiler_output.htm
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Kenny" <keejh@.hotmail.com> wrote in message
news:uu5r9EZFFHA.3728@.TK2MSFTNGP14.phx.gbl...
Hi,
Is there any tools can help me doing the analysis using SQL Profiler (trc)
files?
Thanks,
Kenny
|||See read80trace.exe
http://support.microsoft.com/default...b;en-us;887057
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2005 All rights reserved.
"Kenny" <keejh@.hotmail.com> wrote in message
news:uu5r9EZFFHA.3728@.TK2MSFTNGP14.phx.gbl...
> Hi,
> Is there any tools can help me doing the analysis using SQL Profiler (trc)
> files?
> Thanks,
> Kenny
>
|||Kenny wrote:
> Hi,
> Is there any tools can help me doing the analysis using SQL Profiler
> (trc) files?
> Thanks,
> Kenny
You can check out our Coefficient product (14-day free trial available).
David Gugick
Imceda Software
www.imceda.com

Analyzer 2000 using 2005 database gives error

I am using Query Analyzer 2000 and pointing at a 2005 database. I keep
getting an error that I think it related to some code that is selecting data
into a temp table. The procedure runs perfect on a 2000 database.
here is the message
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionCheckForData
(CheckforData()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.
Connection Broken
Any ideas?
Thank you
RichardAnd the procedure? Can we see it?
ML
http://milambda.blogspot.com/|||CREATE PROCEDURE arLedgerListingGenAP
@.ProcMode CHOICE = NULL, -- 'R'- Report Mode, ' ' or
NULL - Customer Care Mode
@.CustomerKey SMARTKEY,
@.StartDate DATETIME = NULL,
@.EndDate DATETIME = NULL,
@.cStartDate VARCHAR(40),
@.cEndDate VARCHAR(40),
@.gsUserLanguage LANG
AS
SET NOCOUNT ON
SET ANSI_NULLS OFF
DECLARE @.BeginingBalance AMOUNT2DEC,
@.EndingBalance AMOUNT2DEC
IF @.StartDate IS NULL OR LTRIM(RTRIM(@.cStartDate)) = ''
SELECT @.StartDate = "1900-01-01 00:00:00"
IF @.EndDate IS NULL OR LTRIM(RTRIM(@.cEndDate)) = ''
SELECT @.EndDate = CAST(CAST(YEAR(@.EndDate) as varchar) + CASE
WHEN
LEN(MONTH(@.EndDate)) = 1
THEN
'0' + CAST(MONTH(@.EndDate) AS varchar)
ELSE
CAST(MONTH(@.EndDate) AS varchar)
END + CASE
WHEN LEN(DAY(@.EndDate)) = 1
THEN '0' + CAST(DAY(@.EndDate) AS varchar)
ELSE CAST(DAY(@.EndDate) AS varchar)
END
+ ' 23:59:59' AS datetime)
IF @.cStartDate IS NOT NULL AND LTRIM(RTRIM(@.cStartDate)) <> ''
SELECT @.StartDate = @.cStartDate
IF @.cEndDate IS NOT NULL AND LTRIM(RTRIM(@.cEndDate)) <> ''
SELECT @.EndDate = CAST(CAST(YEAR(@.cEndDate) as varchar) + CASE
WHEN
LEN(MONTH(@.cEndDate)) = 1
THEN
'0' + CAST(MONTH(@.cEndDate) AS varchar)
ELSE
CAST(MONTH(@.cEndDate) AS varchar)
END + CASE
WHEN LEN(DAY(@.cEndDate)) = 1
THEN '0' + CAST(DAY(@.cEndDate) AS varchar)
ELSE CAST(DAY(@.cEndDate) AS varchar)
END
+ ' 23:59:59' AS datetime)
-- The temp tables used in the Ledger Listind Stored procedures.
-- i.e. arLedgerListingGenAP.sql.
CREATE TABLE #Activity
(ActivityKey SMARTKEY IDENTITY(1,1),
ActivityDate DATETIME,
Amount AMOUNT2DEC NULL,
Discount AMOUNT2DEC NULL,
InvoiceNo INVOICENO NULL,
ContractKey SMARTKEY NULL,
CreditInvoiceNo INVOICENO NULL,
TranType VARCHAR(8) NULL,
ShortDesc CHAR(6) NULL,
ActDesc MAXCHAR NULL,
TransDescription MAXCHAR NULL,
InvoiceTotal AMOUNT2DEC NULL,
InvoiceBalance AMOUNT2DEC NULL,
AccountBalance AMOUNT2DEC NULL,
EmployeeCode CHAR(3) NULL,
PaymentReference DESCRIPTION NULL,
RecordType CHAR(5) NULL,
Target INVOICENO NULL,
TransactionGroupNo INTEGER NULL,
TransactionType VARCHAR(40) NULL,
AmtAffectingBal AMOUNT2DEC NULL,
PaymentApplicationKey SMARTKEY NULL,
InvoiceType CHOICE NULL,
ReversalReference CHOICE NULL,
DatePaid DATETIME NULL,
InvoiceTerms DESCRIPTION NULL,
TermsCode CHAR(3) NULL,
DueDate DATETIME NULL,
PastDueDays INTEGER NULL,
ForeColor INTEGER NULL,
CustomerKey SMARTKEY,
OwnerKey SMARTKEY NULL,
PaymentOrInvoice CHAR(1) NULL,
InvoiceDate DATEONLY NULL,
SortinvoiceNo INVOICENO NULL,
TranFrom CHOICE NULL)
CREATE TABLE #TempActivity (ActivityKey SMARTKEY,
InvoiceNo INVOICENO,
TransactionType VARCHAR(40),
TrxCnt INTEGER,
TransDescription MAXCHAR)
/* CreditInvoiceNo INVOICENO,
Amount AMOUNT2DEC,
Total AMOUNT2DEC,
AppliedCredits AMOUNT2DEC,
CreationDate DATETIME,*/
-- Find all Invoice Transaction for the customer. We have to go to the line
item to find out what was done, Refunds, Returns, etc.
-- Get also the pending credits and their Status.
/*
INSERT INTO #TempActivity EXEC arLedgerListingGen1AP
@.CustomerKey = @.CustomerKey,
@.StartDate = @.StartDate,
@.EndDate = @.EndDate,
@.gsUserLanguage = @.gsUserLanguage
*/
-- Store all Transaction for the customer in a temp table.
INSERT INTO #Activity EXEC arLedgerListingGen2AP
@.CustomerKey = @.CustomerKey,
@.StartDate = @.StartDate,
@.EndDate = @.EndDate,
@.gsUserLanguage = @.gsUserLanguage
--IF EXISTS (SELECT * FROM #Activity WHERE ShortDesc LIKE '%MT' OR
ShortDesc = 'DM')
BEGIN
EXEC arLedgerListingGen1AP
@.CustomerKey = @.CustomerKey,
@.StartDate = @.StartDate,
@.EndDate = @.EndDate,
@.gsUserLanguage = @.gsUserLanguage
UPDATE X
SET TransactionType = Y.TransactionType,
TransDescription = Y.TransDescription,
ShortDesc = LTRIM(RTRIM(ShortDesc)) + 'MT'
FROM #Activity X
JOIN #TempActivity Y ON X.InvoiceNo = Y.InvoiceNo
END
EXEC arLedgerListGetBegBalGenAP @.CustomerKey, @.StartDate,
@.BeginingBalance OUTPUT
IF @.BeginingBalance IS NULL
SELECT @.BeginingBalance = 0
EXEC arLedgerListGetEndBalGenAP @.CustomerKey, @.EndDate, @.EndingBalance
OUTPUT
IF @.EndingBalance IS NULL
SELECT @.EndingBalance = 0
-- Get the Customerkey, RecordType and Contract key of the record that was
transferred IN/OUT, get running balance per Account and
-- per Invoice and Update Balances.
SELECT a.ActivityKey, a.ActivityDate, a.Amount, a.Discount, a.InvoiceNo,
a.ContractKey, a.CreditInvoiceNo,
a.TranType, ShortDesc = LEFT(a.ShortDesc,5), a.ActDesc,
a.TransDescription, a.InvoiceTotal,
InvoiceBalance = CASE
WHEN a.InvoiceNo NOT IN ('CREDIT',
'ACCCRD', 'ACCDEP', 'SECDEP','PREPAY')
THEN a.AmtAffectingBal + (SELECT
ISNULL(SUM(c.AmtAffectingBal), 0.00)
FROM
#Activity c
WHERE
c.ActivityKey < a.ActivityKey
AND
a.InvoiceNo = c.InvoiceNo)
ELSE 0.00
END,
AccountBalance = a.AmtAffectingBal + @.BeginingBalance + (SELECT
ISNULL(SUM(c.AmtAffectingBal),0.00)
FROM
#Activity c
WHERE
c.ActivityKey < a.ActivityKey),
a.EmployeeCode, a.PaymentReference, a.RecordType, a.Target,
a.TransactionGroupNo,
a.TransactionType, a.AmtAffectingBal, a.PaymentApplicationKey,
a.InvoiceType, a.ReversalReference,
a.DatePaid, a.InvoiceTerms, a.TermsCode, a.DueDate,
a.PastDueDays, a.ForeColor, a.CustomerKey, a.OwnerKey,
TransferCustomerKey = b.CustomerKey, TransferRecordType =
b.RecordType, TransferContractKey = b.ContractKey,
a.PaymentOrInvoice, a.InvoiceDate, a.SortinvoiceNo, a.TranFrom
INTO #Activity2
FROM #Activity a
LEFT JOIN arPaymentApplications b ON
a.TransactionGroupNo = b.TransactionGroupNo
AND
LEFT(a.RecordType, 1) = 'X'
--This record contains
the sum of all PREPAY adjustments for all customers
AND a.RecordType <>
'XIADP'
--XOADP is a Transfer
Out of Access Deposit for PREPAY
--In this case the
CustomerKey's must match.
AND a.CustomerKey =
CASE
WHEN a.RecordType = 'XOADP'
THEN a.CustomerKey
END
--Else any other X
RecordTypes are transfers of Security Deposits
--The CustomerKey's do
not match here because we are picking up who
--the moneies are
transfered to.
AND a.CustomerKey <>
CASE
WHEN a.RecordType <> 'XOADP'
THEN b.CustomerKey
END
-- get the Descriptions.
IF @.ProcMode IS NULL OR @.ProcMode = ' '
BEGIN
EXEC arLedgerListingGen3AP
@.BeginingBalance = @.BeginingBalance,
@.EndingBalance = @.EndingBalance,
@.gsUserLanguage = @.gsUserLanguage
END
ELSE
BEGIN
EXEC arLedgerListingGen4AP
@.BeginingBalance = @.BeginingBalance,
@.EndingBalance = @.EndingBalance,
@.gsUserLanguage = @.gsUserLanguage
END
GO
"ML" <ML@.discussions.microsoft.com> wrote in message
news:8899258C-9E33-4876-B54F-408943B42727@.microsoft.com...
> And the procedure? Can we see it?
>
> ML
> --
> http://milambda.blogspot.com/|||After going through the code there's one issue that stands out - your
INSERT...EXECUTE statements lack column declarations. Try fixing that, e.g.
insert <table>
(
<column list>
)
exec <procedure> <parameter list>
Make certain that the columns in the INSERT statement match the columns in
the result set of the procedure.
ML
http://milambda.blogspot.com/

Saturday, February 25, 2012

Annoying problem with SQL Server 2000 Query Analyzer

annoying cut and paste behavior in query analyzer

annoying cut and paste behavior in query analyzer

annoying cut and paste behavior in query analyzer

Sunday, February 19, 2012

Analyzer Tools

Analyzer 2000 using 2005 database gives error

SQL server connection

Blog Archive

About Me