J# using JDBC for SQL Server

code:

/* Establish a test connection to remote SQLServer in J# using JDBC */
package JSharpConnTest;
import java.sql.*;
public class JSharpConnTest
{
private static ResultSet rs;
private static Connection conn = null;
private static Statement stmt = null;
private static final String sSubprotocol = "jdbc:microsoft:sqlserver://";
private static final String sServerName = "CLUSTERTEST";
private static final String sPortNumber = "1433";
private static final String sDBName = "ClientLetterWorkRequests";
private static final String sUserName = "sa";
private static final String sPassword = "1111";
private static final String sSQLQuery = "SELECT * FROM CLDatabases";
private static final String sURL = sSubprotocol + sServerName +
":" + sPortNumber + ";
databaseName=" + sDBName + ";
";
public static void main(String[] args)
{
getConnection();
}
public static void getConnection()
{
System.out.println( "Connecting to.. " + sURL );
try
{
//Register the driver
// Microsoft SQL Server 2000 Driver for JDBC
// --problem locating driver?
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
// Pass connection URL
conn = DriverManager.getConnection(sURL, sUserName, sPassword);
// Query database
stmt = conn.createStatement();
rs = stmt.executeQuery(sSQLQuery);
// Test data retrieval
while (rs.next());
{
String colOne = rs.getString("DATABASEKEY");
String colTwo = rs.getString("SERVERTYPE");
System.out.println(colOne + " " + colTwo);
}
}
catch (java.lang.ClassNotFoundException ex)
{
System.err.println("\nClassNotFoundException: " + ex.getMessage());
}
catch (SQLException ex)
{
System.err.println("\nSQLException: " + ex.getMessage());
}
catch (Exception ex)
{
System.err.println("\nException: " + ex.getMessage());
}
finally
{
// Release resources
try
{
if (conn != null)
conn.close();
if (stmt != null)
stmt.close();
}
catch (Exception ex)
{
System.err.println("\nException: " + ex.getMessage());
}
}
} // end getConnection()
} // end JSharpConnTest

This statement:
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
Causes the following exception:
[java.lang.ClassNotFoundException]{
"com.microsoft.jdbc.sqlserver.SQLServerDriver"}java.lang.ClassNotFoundExcep
tion
I installed Microsoft SQL Server 2000 Driver for JDBC
I believe the problem is with locating the driver, unlike Java, J# doesn't
use classpath enviromental variable. I am not sure how to add the driver
reference in J#.Hi
Why use JDBC when ADO.NET can do everything for you?
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"RobertStout" <
RobertStout@.discussions.microsoft.com>
wrote in message
news:4FB06586-4C07-4886-A9E5-444A85DA4F31@.microsoft.com...
>

code:

>
 /* Establish a test connection to remote SQLServer in J# using JDBC */
>
>
 package JSharpConnTest;
>
 import java.sql.*;
>
>
 public class JSharpConnTest
>
 {
>
 private static ResultSet rs;
>
 private static Connection conn = null;
>
 private static Statement stmt = null;
>
 private static final String sSubprotocol = "jdbc:microsoft:sqlserver://";
>
 private static final String sServerName = "CLUSTERTEST";
>
 private static final String sPortNumber = "1433";
>
 private static final String sDBName = "ClientLetterWorkRequests";
>
 private static final String sUserName = "sa";
>
 private static final String sPassword = "1111";
>
 private static final String sSQLQuery = "SELECT * FROM CLDatabases";
>
 private static final String sURL = sSubprotocol + sServerName +
>
 ":" + sPortNumber + ";
databaseName=" + sDBName + ";
";
>
>
 public static void main(String[] args)
>
 {
>
 getConnection();
>
 }
>
>
 public static void getConnection()
>
 {
>
 System.out.println( "Connecting to.. " + sURL );
>
>
 try
>
 {
>
 //Register the driver
>
 // Microsoft SQL Server 2000 Driver for JDBC
>
 // --problem locating driver?
>
 Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
>
>
 // Pass connection URL
>
 conn = DriverManager.getConnection(sURL, sUserName, sPassword);
>
>
 // Query database
>
 stmt = conn.createStatement();
>
 rs = stmt.executeQuery(sSQLQuery);
>
>
 // Test data retrieval
>
 while (rs.next());
>
 {
>
 String colOne = rs.getString("DATABASEKEY");
>
 String colTwo = rs.getString("SERVERTYPE");
>
 System.out.println(colOne + " " + colTwo);
>
 }
>
 }
>
 catch (java.lang.ClassNotFoundException ex)
>
 {
>
 System.err.println("\nClassNotFoundException: " + ex.getMessage());
>
 }
>
 catch (SQLException ex)
>
 {
>
 System.err.println("\nSQLException: " + ex.getMessage());
>
 }
>
 catch (Exception ex)
>
 {
>
 System.err.println("\nException: " + ex.getMessage());
>
 }
>
 finally
>
 {
>
 // Release resources
>
 try
>
 {
>
 if (conn != null)
>
 conn.close();
>
 if (stmt != null)
>
 stmt.close();
>
 }
>
 catch (Exception ex)
>
 {
>
 System.err.println("\nException: " + ex.getMessage());
>
 }
>
 }
>
>
>
 } // end getConnection()
>
 } // end JSharpConnTest
>

>
>
This statement:
>
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
>
>
Causes the following exception:
>

[java.lang.ClassNotFoundException]{
"com.microsoft.jdbc.sqlserver.SQLServerDr
iver"}java.lang.ClassNotFoundException
>
>
I installed Microsoft SQL Server 2000 Driver for JDBC
>
I believe the problem is with locating the driver, unlike Java, J# doesn't
>
use classpath enviromental variable. I am not sure how to add the driver
>
reference in J#.|||If I could use ADO.NET I would of been done with the entire app a long time
ago.
Sadly, I have to use JDBC.
Can you help?
Thanks
-Rob
I would much rather use ADO.NET but I can't, I have to use JDBC.
"Mike Epprecht (SQL MVP)" wrote:

> Hi
> Why use JDBC when ADO.NET can do everything for you?
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "RobertStout" <RobertStout@.discussions.microsoft.com> wrote in message
> news:4FB06586-4C07-4886-A9E5-444A85DA4F31@.microsoft.com...
> [java.lang.ClassNotFoundException]{"com.microsoft.jdbc.sqlserver.SQLServerDr
> iver"}java.lang.ClassNotFoundException
>
>sql

it's very urgent regarding XP_SEND MAIL

Hi,
I have a problem in sending mail from sql server 2000 which is using
windows 2000 professional and outlook express
Iam pasting my code below
/
************************************************** ************************************************** ********
Create procedure Pub_SendingMail
as
declare
@.EmailAddTO varchar(30),
@.EmailSubject varchar(130),
@.EmailText varchar(255),
@.return int,
@.Counting int
@.
begin
/* SET value */
set @.return = 0
set @.Counting = 0
set @.EmailSubject = 'TEST EMAIL'
set @.EmailText = 'This is a test email'
set @.EmailAddTO = 'nagesh@.emids.com'
/* LOOP. If e-mail is sent, break loop; ELSE WAIT 10 seconds, and
then RETRY. */
WHILE 1=1
begin
set @.Counting = @.Counting + 1
exec @.return = master.dbo.xp_sendmail
@.recipients = @.EmailAddTO,
@.message = @.EmailText ,
@.subject = @.EmailSubject
@.attachments = 'c:\attachment.txt'
/* CHECK value, break if SUCCESS */
if @.return = 0
begin
print 'EMAIL SENT'
break
end
else
begin
/* Try 1 times */
if @.Counting = 1
break
print 'EMAIL FAILED, WAIT 10 SECONDS, TRY AGAIN'
/*000 hours, 00 minutes, and 10 seconds */
waitfor delay '000:00:03'
end
end
end
************************************************** ************************************************** *************/
and when iam executing
EXEC Pub_SendingMail
it is giving the following error
Server: Msg 18030, Level 16, State 1, Line 0
xp_sendmail: Either there is no default mail client or the current
mail client cannot fulfill the messaging request. Please run Microsoft
Outlook and set it as the default mail client.
but here i need to send mail not only to out look express but also
general mail servers
I would deeply appriciate if any body can help me about this issue
and send any modifications in the above code.And also how to configure
the Microsoft Outlook Express as the default mail client.
it is very very very very very urgent
Regards
prasad
You'll need to set up a MAPI mail profile for this to work. Alternatively
you could use the XPSMTP.DLL which is free
(http://www.sqldev.net/xp/xpsmtp.htm).
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

it's very urgent regarding XP_SEND MAIL

Hi,
I have a problem in sending mail from sql server 2000 which is using
windows 2000 professional and outlook express
Iam pasting my code below
/
************************************************************************************************************
Create procedure Pub_SendingMail
as
declare
@.EmailAddTO varchar(30),
@.EmailSubject varchar(130),
@.EmailText varchar(255),
@.return int,
@.Counting int
@.
begin
/* SET value */
set @.return = 0
set @.Counting = 0
set @.EmailSubject = 'TEST EMAIL'
set @.EmailText = 'This is a test email'
set @.EmailAddTO = 'nagesh@.emids.com'
/* LOOP. If e-mail is sent, break loop; ELSE WAIT 10 seconds, and
then RETRY. */
WHILE 1=1
begin
set @.Counting = @.Counting + 1
exec @.return = master.dbo.xp_sendmail
@.recipients = @.EmailAddTO,
@.message = @.EmailText ,
@.subject = @.EmailSubject
@.attachments = 'c:\attachment.txt'
/* CHECK value, break if SUCCESS */
if @.return = 0
begin
print 'EMAIL SENT'
break
end
else
begin
/* Try 1 times */
if @.Counting = 1
break
print 'EMAIL FAILED, WAIT 10 SECONDS, TRY AGAIN'
/*000 hours, 00 minutes, and 10 seconds */
waitfor delay '000:00:03'
end
end
end
*****************************************************************************************************************/
and when iam executing
EXEC Pub_SendingMail
it is giving the following error
Server: Msg 18030, Level 16, State 1, Line 0
xp_sendmail: Either there is no default mail client or the current
mail client cannot fulfill the messaging request. Please run Microsoft
Outlook and set it as the default mail client.
but here i need to send mail not only to out look express but also
general mail servers
I would deeply appriciate if any body can help me about this issue
and send any modifications in the above code.And also how to configure
the Microsoft Outlook Express as the default mail client.
it is very very very very very urgent
Regards
prasadXp_sendmail cannot use Outlook Express. Do yourself a big favor and use xp_smtp_sendmail from
www.sqldev.net.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<shyam.yarlagadda@.gmail.com> wrote in message
news:1170414875.690570.8220@.v45g2000cwv.googlegroups.com...
> Hi,
> I have a problem in sending mail from sql server 2000 which is using
> windows 2000 professional and outlook express
> Iam pasting my code below
> /
> ************************************************************************************************************
> Create procedure Pub_SendingMail
> as
> declare
> @.EmailAddTO varchar(30),
> @.EmailSubject varchar(130),
> @.EmailText varchar(255),
> @.return int,
> @.Counting int
> @.
> begin
> /* SET value */
> set @.return = 0
> set @.Counting = 0
> set @.EmailSubject = 'TEST EMAIL'
> set @.EmailText = 'This is a test email'
> set @.EmailAddTO = 'nagesh@.emids.com'
> /* LOOP. If e-mail is sent, break loop; ELSE WAIT 10 seconds, and
> then RETRY. */
> WHILE 1=1
> begin
> set @.Counting = @.Counting + 1
> exec @.return = master.dbo.xp_sendmail
> @.recipients = @.EmailAddTO,
> @.message = @.EmailText ,
> @.subject = @.EmailSubject
> @.attachments = 'c:\attachment.txt'
> /* CHECK value, break if SUCCESS */
> if @.return = 0
> begin
> print 'EMAIL SENT'
> break
> end
> else
> begin
> /* Try 1 times */
> if @.Counting = 1
> break
> print 'EMAIL FAILED, WAIT 10 SECONDS, TRY AGAIN'
> /*000 hours, 00 minutes, and 10 seconds */
> waitfor delay '000:00:03'
> end
> end
> end
> *****************************************************************************************************************/
> and when iam executing
> EXEC Pub_SendingMail
> it is giving the following error
> Server: Msg 18030, Level 16, State 1, Line 0
> xp_sendmail: Either there is no default mail client or the current
> mail client cannot fulfill the messaging request. Please run Microsoft
> Outlook and set it as the default mail client.
> but here i need to send mail not only to out look express but also
> general mail servers
> I would deeply appriciate if any body can help me about this issue
> and send any modifications in the above code.And also how to configure
> the Microsoft Outlook Express as the default mail client.
> it is very very very very very urgent
> Regards
> prasad
>|||You'll need to set up a MAPI mail profile for this to work. Alternatively
you could use the XPSMTP.DLL which is free
(http://www.sqldev.net/xp/xpsmtp.htm).
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

items in list A that dont appear in list B (was "Simple Query...I think")

Ok, I want to write a stored procedure / query that says the following:
Code:
If any of the items in list 'A' also appear in list 'B' --return false
If none of the items in list 'A' appear in list 'B' --return true

In pseudo-SQL, I want to write a clause like this

Code:

IF
(SELECT values FROM tableA) IN(SELECT values FROM tableB)
Return False
ELSE
Return True


Unfortunately, it seems I can't do that unless my subquery before the 'IN' statement returns only one value. Needless to say, it returns a number of values.

I may have to achieve this with some kind of logical loop but I don't know how to do that.

Can anyone help?OK so it wasnt' so simple...at least MY solution isn't:

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER PROC sp_get_trolley_inconsistencies
@.grower CHAR(2),
@.load_id INT
AS

DECLARE @.ID int,
@.TrolleyList varchar(300),
@.Date DATETIME
--Get the relevant date from the DB Table
SELECT @.Date = (SELECT CONVERT(DATETIME, rl_eta, 102) FROM requi_load WHERE rl_id = @.load_id)

SET @.TrolleyList = ''
--Get the list of values into a comma delimited string
DECLARE crs_Trolleys CURSOR
FOR SELECT DISTINCT ldl_trolley
FROM load_detail_lines
WHERE ldl_requi_load_id = @.load_id

OPEN crs_Trolleys
FETCH NEXT FROM crs_Trolleys INTO @.ID

WHILE @.@.FETCH_STATUS = 0
BEGIN
SELECT @.TrolleyList = @.TrolleyList+CAST(@.ID AS varchar(5))+ ', '
FETCH NEXT FROM crs_Trolleys INTO @.ID
END

SET @.TrolleyList = SUBSTRING(@.TrolleyList,1,DATALENGTH(@.TrolleyList)-2)

CLOSE crs_Trolleys
DEALLOCATE crs_Trolleys

--Parse the string and run the 'IN' statement on each of the Parsed Values

DECLARE @.parsingList VARCHAR(300)
DECLARE @.find_comma INT
DECLARE @.trolleytocheck VARCHAR(4)

SELECT @.parsingList = @.TrolleyList

WHILE @.parsingList IS NOT NULL

BEGIN
SELECT @.find_comma = PATINDEX('%,%',@.parsingList)
IF @.find_comma <> 0
BEGIN
SELECT @.trolleytocheck = SUBSTRING(@.parsingList,1,(@.find_comma-1))
SELECT @.parsingList = LTRIM(SUBSTRING(@.parsingList,(@.find_comma+1),300))
PRINT @.trolleytocheck
IF @.trolleytocheck IN(SELECT distinct ldl_trolley
FROM load_detail_lines, requi_load
WHERE ldl_requi_load_id = rl_id
AND ldl_requi_load_id NOT LIKE @.load_id
AND rl_status = 1
AND DAY(rl_eta) = DAY(@.Date)
AND MONTH(rl_eta) = MONTH(@.Date)
AND YEAR(rl_eta) = YEAR(@.Date))
BEGIN
RAISERROR 50001 'Error! You screwed up, trolley '
END
CONTINUE
END
ELSE
BEGIN
--this block finds the last value in the trolley list
SELECT @.trolleytocheck = @.parsingList
SELECT @.parsingList = null
PRINT @.trolleytocheck
BREAK
END
END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

If anyone can suggest anything more elegant...please let me know.|||Why not join the tables and count the rows that match?|||Doh!

*Commits ritual suicide*

it will not show the data from the DB

is have this code, and i know that i have a record with the ID=1 but it will not show the data from the record..

<asp:Content ID="Main" ContentPlaceHolderID="ContentPlaceHolderMain" Runat="Server"><asp:FormView ID="form1" runat="server" DataSourceID="SqlDataSource1"></asp:FormView> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnStrAccess%>" SelectCommand="SELECT [MainID], [MainText] FROM [SiteText] WHERE ([MainID] = ?)" ProviderName="<%$ ConnectionStrings:ConnStrAccess.ProviderName%>"> <SelectParameters> <asp:SessionParameter DefaultValue="1" Name="SiteMainID" Type="Int32" /> </SelectParameters></asp:SqlDataSource></asp:Content>

Why can't it show the record !??

Hello:

A few things are not right here:

1. YourSelectCommand should be:SelectCommand="SELECT [MainID], [MainText] FROM [SiteText] WHERE ([MainID] = @.MainID)" Yours (the ? is not forSqlDataSource);

2. The SessionParameter should look like:

<asp:SessionParameter DefaultValue="1" Name="MainID" SessionField="yourSessionMainIDValue" Type="Int32" />;

3.Your FormView should include at lease the <ItemTemplate> section to show your data.

Hope you can get your Access table work here.

|||Thx now it's works

It is possible with cursor?

I need to fill a cursor with 3 columns.
A want to use a Select sprocs (for re-use de code), but this sproc return 15 columns and the 3 a need was not the 3 frist. :confused:
Do I need to map the 15 columns with 15 variables locally? Or they have a way easier?
Thankscan u post the code? some one here might come up with a better solution|||DECLARE cuTEST CURSOR FOR
ItemsSEL

OPEN cuTEST

FETCH NEXT FROM cuTEST
INTO @.col1, @.col2, @.col3, @.col4, @.col5,....

but I need only col2, col10 and col14
ItemsSEL is my Strore Procedure for my complexe Select Use many place in my application ( where a need all result columns...)|||Would it be possible to redesign the ItemsSel procedure into a view? How complex is this procedure?

Issueing a CREATE TABLE statement within a transaction

Sorry new to the newsgroups so please point me to where I need to be if this is the wrong group. Below is a chunck of code that I have written to play around with transactions in Visual Studion 2005. I want to be able to create a table, the columns in it, and add rows of data and if any of those statements fail I woudl like to rollback the entire transaction. My testing has shown that when the CREATE TABLE sql is issued it automatcially commits. In my example below the VER_INFO table is immediately created when the ExecuteNonQuery() statement is invoked. The next 2 insert statements are held correctly in the transaction, however when the second table, WADES_TEST is created that causes the 2 rows to be inserted and the second table is immediately created. So my commit statement is useless. Is this normal? Is there a way to force the system to keep the CREATE TABLE statement in the transaction so I can roll it back if needed?

OracleCommand cmd = new OracleCommand();

OracleConnection wConnection = new OracleConnection(wConnString);

wConnection.Open();

OracleTransaction trans = wConnection.BeginTransaction();

string wQry = "CREATE TABLE VER_INFO (VER NVARCHAR2(25))";

cmd.CommandText = wQry;

cmd.Connection = wConnection;

cmd.Transaction = trans;

try

{

cmd.ExecuteNonQuery();

wQry = "INSERT INTO VER_INFO VALUES ('INITIALIZED')";

OracleCommand cmd4 = new OracleCommand(wQry, wConnection, trans);

cmd4.ExecuteNonQuery();

wQry = "INSERT INTO VER_INFO VALUES ('testss')";

OracleCommand cmd2 = new OracleCommand(wQry, wConnection, trans);

cmd2.ExecuteNonQuery();

wQry = "CREATE TABLE WADES_TEST (WADE NVARCHAR2(25))";

OracleCommand cmd3 = new OracleCommand(wQry, wConnection, trans);

cmd3.ExecuteNonQuery();

trans.Commit();

wConnection.Dispose();

}

catch

{

trans.Rollback();

wConnection.Close();

}

Thanks in advance,

Wade Sharp

This might be more appropriate to be posted to an Oracle forum or newsgroup. Or to an ADO.NET forum.

Issue with T-SQL and SQL 7 to SQL2000 conversion

I'm in the process of converting a database from SQL 7 to SQL 2000 and
have come up against a problem. The following code executes correctly in
the existing SQL7 database and runs to completion in about 1 minute.
In the 2000 database, it runs until I cancel execution -- I've let it
run up to 30 minutes without showing any signs of finishing.
I am using the identical code to populate the tables in both databases
-- the data actually gets fed in from text files via a BULK INSERT
command, and that runs correctly in the both databases. The indexes and
primary keys are created via T-SQL code, and that runs correctly in both
databases.
I have deleted and re-created the stored procedure containing this code
to no avail. It will still run until I cancel execution.
Did something change between SQL7 and 2000 with the UPDATE command, or
am I missing something else?
Any help will be appreciated --
Carl
UPDATE tblProcedureHistory
SET tblProcedureHistory.Status_A = [derived].MaxPostingDate
FROM
(
SELECT T1.OFFICE_NUM,
T1.PatientID,
T1.PatientType,
T1.StudentID,
T1.ProcedureID,
T1.ProcedureSuffix,
T1.Tooth,
T1.Surface,
MAX(T1.PostingDate) AS MaxPostingDate
FROM tbl_AHSTDN AS T1
INNER JOIN tblProcedureHistory AS T2
ON T1.OFFICE_NUM = T2.OFFICE_NUM
AND T1.PatientID = T2.PatientID
AND T1.PatientType = T2.PatientType
AND T1.StudentID = T2.StudentID
AND T1.ProcedureID = T2.ProcedureID
AND T1.ProcedureSuffix = T2.ProcedureSuffix
AND T1.Tooth = T2.Tooth
AND T1.Surface = T2.Surface
AND T1.Status = 'A'
GROUP BY
T1.OFFICE_NUM,
T1.PatientID,
T1.PatientType,
T1.StudentID,
T1.ProcedureID,
T1.ProcedureSuffix,
T1.Tooth,
T1.Surface
)
AS [derived]
WHERE
tblProcedureHistory.OFFICE_NUM = [derived].OFFICE_NUM
AND tblProcedureHistory.PatientID = [derived].PatientID
AND tblProcedureHistory.PatientType = [derived].PatientType
AND tblProcedureHistory.StudentID = [derived].StudentID
AND tblProcedureHistory.ProcedureID = [derived].ProcedureID
AND tblProcedureHistory.ProcedureSuffix = [derived].ProcedureSuffix
AND tblProcedureHistory.Tooth = [derived].Tooth
AND tblProcedureHistory.Surface = [derived].SurfaceLet's see your DDL including Primary Keys and Indexes.
"Carl Imthurn" <nospam@.all.thanks> wrote in message
news:%23di7RAWlGHA.1208@.TK2MSFTNGP02.phx.gbl...
> I'm in the process of converting a database from SQL 7 to SQL 2000 and
> have come up against a problem. The following code executes correctly in
> the existing SQL7 database and runs to completion in about 1 minute.
> In the 2000 database, it runs until I cancel execution -- I've let it run
> up to 30 minutes without showing any signs of finishing.
> I am using the identical code to populate the tables in both databases --
> the data actually gets fed in from text files via a BULK INSERT command,
> and that runs correctly in the both databases. The indexes and primary
> keys are created via T-SQL code, and that runs correctly in both
> databases.
> I have deleted and re-created the stored procedure containing this code to
> no avail. It will still run until I cancel execution.
> Did something change between SQL7 and 2000 with the UPDATE command, or am
> I missing something else?
> Any help will be appreciated --
> Carl
> UPDATE tblProcedureHistory
> SET tblProcedureHistory.Status_A = [derived].MaxPostingDate
> FROM
> (
> SELECT T1.OFFICE_NUM,
> T1.PatientID,
> T1.PatientType,
> T1.StudentID,
> T1.ProcedureID,
> T1.ProcedureSuffix,
> T1.Tooth,
> T1.Surface,
> MAX(T1.PostingDate) AS MaxPostingDate
> FROM tbl_AHSTDN AS T1
> INNER JOIN tblProcedureHistory AS T2
> ON T1.OFFICE_NUM = T2.OFFICE_NUM
> AND T1.PatientID = T2.PatientID
> AND T1.PatientType = T2.PatientType
> AND T1.StudentID = T2.StudentID
> AND T1.ProcedureID = T2.ProcedureID
> AND T1.ProcedureSuffix = T2.ProcedureSuffix
> AND T1.Tooth = T2.Tooth
> AND T1.Surface = T2.Surface
> AND T1.Status = 'A'
> GROUP BY
> T1.OFFICE_NUM,
> T1.PatientID,
> T1.PatientType,
> T1.StudentID,
> T1.ProcedureID,
> T1.ProcedureSuffix,
> T1.Tooth,
> T1.Surface
> )
> AS [derived]
> WHERE
> tblProcedureHistory.OFFICE_NUM = [derived].OFFICE_NUM
> AND tblProcedureHistory.PatientID = [derived].PatientID
> AND tblProcedureHistory.PatientType = [derived].PatientType
> AND tblProcedureHistory.StudentID = [derived].StudentID
> AND tblProcedureHistory.ProcedureID = [derived].ProcedureID
> AND tblProcedureHistory.ProcedureSuffix = [derived].ProcedureSuffix
> AND tblProcedureHistory.Tooth = [derived].Tooth
> AND tblProcedureHistory.Surface = [derived].Surface|||Here's the DDL for table/PK/index creation:
The table data gets sucked out of an AS/400 every morning into text
files and fed into SQL Server via BULK INSERT. The data in tbl_AHSTDN is
not subject to updates; ie, it's a static table.
Thanks in advance --
Carl
CREATE TABLE [dbo].[tbl_AHSTDN] (
[DNHSTD] [char] (1) NULL ,
[PatientID] [int] NULL ,
[PatientType] [int] NULL ,
[DNTYY] [int] NULL ,
[DNTMM] [int] NULL ,
[DNTDD] [int] NULL ,
[DNSEQ] [int] NULL ,
[DNID] [int] NULL ,
[DNIDTY] [char] (1) NULL ,
[TicketID] [int] NULL ,
[ProcedureID] [int] NULL ,
[ProcedureSuffix] [int] NULL ,
[DNTICX] [int] NULL ,
[Discipline] [varchar] (100) NULL ,
[SessionID] [int] NULL ,
[Grade] [int] NULL ,
[DNMTHS] [int] NULL ,
[StudentID] [char] (3) NULL ,
[DNCGCD] [char] (1) NULL ,
[DOCMASID] [int] NULL ,
[DollarAmount] [money] NULL ,
[DNIN01] [int] NULL ,
[DNDAT1] [int] NULL ,
[DNIN02] [int] NULL ,
[DNDAT2] [int] NULL ,
[DNCLM_NUM] [char] (5) NULL ,
[Status] [char] (1) NULL ,
[DNFILE] [char] (1) NULL ,
[DNSEQN] [int] NULL ,
[DNBK06] [char] (6) NULL ,
[DNFLAG] [char] (1) NULL ,
[BatchID] [int] NULL ,
[Tooth] [char] (2) NULL ,
[Surface] [char] (5) NULL ,
[DNTTH2] [char] (2) NULL ,
[DNSUR2] [char] (5) NULL ,
[DNTTH3] [char] (2) NULL ,
[DNSUR3] [char] (5) NULL ,
[DNTTH4] [char] (2) NULL ,
[DNSUR4] [char] (5) NULL ,
[DNTTH5] [char] (2) NULL ,
[DNSUR5] [char] (5) NULL ,
[Location] [char] (4) NULL ,
[DNCDAT] [int] NULL ,
[User] [varchar] (10) NULL ,
[DNUQID] [int] NULL ,
[DNBL19] [varchar] (19) NULL ,
[DNPTS] [real] NULL ,
[DNGRP] [int] NULL ,
[DNCMDT] [int] NULL ,
[OFFICE_NUM] [int] NULL ,
[TransactionDate] [datetime] NULL ,
[CompletionDate] [datetime] NULL ,
[PostingDate] [datetime] NULL
) ON [PRIMARY]
-- PRIMARY KEY
ALTER TABLE [dbo].[tbl_AHSTDN] WITH NOCHECK ADD
CONSTRAINT [PK_tbl_AHSTDN] PRIMARY KEY NONCLUSTERED
(
[PatientID],
[PatientType],
[DNTYY],
[DNTMM],
[DNTDD],
[DNSEQ],
[OFFICE_NUM]
) ON [PRIMARY]
-- INDEXES
CREATE INDEX [PATIENTID] ON [dbo].[tbl_AHSTDN]([PatientID]) ON [PRIMARY]
CREATE INDEX [PATIENTTYPE] ON [dbo].[tbl_AHSTDN]([PatientType]) ON
[PRIMARY]
CREATE INDEX [TICKETID] ON [dbo].[tbl_AHSTDN]([TicketID]) ON [PRIMARY]
CREATE INDEX [PROCEDUREID] ON [dbo].[tbl_AHSTDN]([ProcedureID]) ON
[PRIMARY]
CREATE INDEX [PROCEDURESUFFIX] ON
[dbo].[tbl_AHSTDN]([ProcedureSuffix]) ON [PRIMARY]
CREATE INDEX [GRADE] ON [dbo].[tbl_AHSTDN]([Grade]) ON [PRIMARY]
CREATE INDEX [STUDENTID] ON [dbo].[tbl_AHSTDN]([StudentID]) ON [PRIMARY]
CREATE INDEX [OFFICE_NUM] ON [dbo].[tbl_AHSTDN]([OFFICE_NUM]) ON
[PRIMARY]
CREATE INDEX [DNUQID] ON [dbo].[tbl_AHSTDN]([DNUQID]) ON [PRIMARY]
CREATE INDEX [TRANSACTIONDATE] ON
[dbo].[tbl_AHSTDN]([TransactionDate]) ON [PRIMARY]
CREATE INDEX [COMPLETIONDATE] ON [dbo].[tbl_AHSTDN]([CompletionDate])
ON [PRIMARY]
CREATE INDEX [POSTINGDATE] ON [dbo].[tbl_AHSTDN]([PostingDate]) ON
[PRIMARY]
-- added on 7 December 2004 to improve performance on clinic attendance
stored procedures
CREATE INDEX ATTENDANCE_REPORT_INDEX ON dbo.tbl_AHSTDN (ProcedureID,
TransactionDate, OFFICE_NUM, StudentID, SessionID) ON [PRIMARY]
-- added on 20 January 2005 to improve performance on ticket count
reports for PBO
CREATE INDEX TICKET_COUNT_REPORT_INDEX ON dbo.tbl_AHSTDN (OFFICE_NUM,
Location, [User], TicketID, PostingDate, Status) ON [PRIMARY]|||First thing, that's a heckuva lot of nullable columns. Even your PRIMARY
KEY columns are all nullable!? :( I don't know if it's just me, but I also
don't see a clustered index on this table anywhere either :(.
Anyways, here's one suggestion - before the BULK INSERT drop all indexes
(except the clustered index should you decide to add one), do the BULK
INSERT, and then rebuild the indexes.
At the very least I would imagine the table could stand to be reindexed big
time. One more quick suggestion - check to see if the database size is near
the upper limit; i.e., is Auto-Grow likely to kick in during the BULK INSERT
process? If so, resize the database to make it bigger. And if possible use
the simple recovery model for this database.
"Carl Imthurn" <nospam@.all.thanks> wrote in message
news:%23g6sjYWlGHA.1208@.TK2MSFTNGP02.phx.gbl...
> Here's the DDL for table/PK/index creation:
> The table data gets sucked out of an AS/400 every morning into text files
> and fed into SQL Server via BULK INSERT. The data in tbl_AHSTDN is not
> subject to updates; ie, it's a static table.
> Thanks in advance --
> Carl
> CREATE TABLE [dbo].[tbl_AHSTDN] (
> [DNHSTD] [char] (1) NULL ,
> [PatientID] [int] NULL ,
> [PatientType] [int] NULL ,
> [DNTYY] [int] NULL ,
> [DNTMM] [int] NULL ,
> [DNTDD] [int] NULL ,
> [DNSEQ] [int] NULL ,
> [DNID] [int] NULL ,
> [DNIDTY] [char] (1) NULL ,
> [TicketID] [int] NULL ,
> [ProcedureID] [int] NULL ,
> [ProcedureSuffix] [int] NULL ,
> [DNTICX] [int] NULL ,
> [Discipline] [varchar] (100) NULL ,
> [SessionID] [int] NULL ,
> [Grade] [int] NULL ,
> [DNMTHS] [int] NULL ,
> [StudentID] [char] (3) NULL ,
> [DNCGCD] [char] (1) NULL ,
> [DOCMASID] [int] NULL ,
> [DollarAmount] [money] NULL ,
> [DNIN01] [int] NULL ,
> [DNDAT1] [int] NULL ,
> [DNIN02] [int] NULL ,
> [DNDAT2] [int] NULL ,
> [DNCLM_NUM] [char] (5) NULL ,
> [Status] [char] (1) NULL ,
> [DNFILE] [char] (1) NULL ,
> [DNSEQN] [int] NULL ,
> [DNBK06] [char] (6) NULL ,
> [DNFLAG] [char] (1) NULL ,
> [BatchID] [int] NULL ,
> [Tooth] [char] (2) NULL ,
> [Surface] [char] (5) NULL ,
> [DNTTH2] [char] (2) NULL ,
> [DNSUR2] [char] (5) NULL ,
> [DNTTH3] [char] (2) NULL ,
> [DNSUR3] [char] (5) NULL ,
> [DNTTH4] [char] (2) NULL ,
> [DNSUR4] [char] (5) NULL ,
> [DNTTH5] [char] (2) NULL ,
> [DNSUR5] [char] (5) NULL ,
> [Location] [char] (4) NULL ,
> [DNCDAT] [int] NULL ,
> [User] [varchar] (10) NULL ,
> [DNUQID] [int] NULL ,
> [DNBL19] [varchar] (19) NULL ,
> [DNPTS] [real] NULL ,
> [DNGRP] [int] NULL ,
> [DNCMDT] [int] NULL ,
> [OFFICE_NUM] [int] NULL ,
> [TransactionDate] [datetime] NULL ,
> [CompletionDate] [datetime] NULL ,
> [PostingDate] [datetime] NULL
> ) ON [PRIMARY]
> -- PRIMARY KEY
> ALTER TABLE [dbo].[tbl_AHSTDN] WITH NOCHECK ADD
> CONSTRAINT [PK_tbl_AHSTDN] PRIMARY KEY NONCLUSTERED
> (
> [PatientID],
> [PatientType],
> [DNTYY],
> [DNTMM],
> [DNTDD],
> [DNSEQ],
> [OFFICE_NUM]
> ) ON [PRIMARY]
> -- INDEXES
> CREATE INDEX [PATIENTID] ON [dbo].[tbl_AHSTDN]([PatientID]) ON [PRIMARY]
> CREATE INDEX [PATIENTTYPE] ON [dbo].[tbl_AHSTDN]([PatientType]) ON
> [PRIMARY]
> CREATE INDEX [TICKETID] ON [dbo].[tbl_AHSTDN]([TicketID]) ON [PRIMARY]
> CREATE INDEX [PROCEDUREID] ON [dbo].[tbl_AHSTDN]([ProcedureID]) ON
> [PRIMARY]
> CREATE INDEX [PROCEDURESUFFIX] ON [dbo].[tbl_AHSTDN]([ProcedureSuffix])
> ON [PRIMARY]
> CREATE INDEX [GRADE] ON [dbo].[tbl_AHSTDN]([Grade]) ON [PRIMARY]
> CREATE INDEX [STUDENTID] ON [dbo].[tbl_AHSTDN]([StudentID]) ON [PRIMARY]
> CREATE INDEX [OFFICE_NUM] ON [dbo].[tbl_AHSTDN]([OFFICE_NUM]) ON
> [PRIMARY]
> CREATE INDEX [DNUQID] ON [dbo].[tbl_AHSTDN]([DNUQID]) ON [PRIMARY]
> CREATE INDEX [TRANSACTIONDATE] ON [dbo].[tbl_AHSTDN]([TransactionDate])
> ON [PRIMARY]
> CREATE INDEX [COMPLETIONDATE] ON [dbo].[tbl_AHSTDN]([CompletionDate]) ON
> [PRIMARY]
> CREATE INDEX [POSTINGDATE] ON [dbo].[tbl_AHSTDN]([PostingDate]) ON
> [PRIMARY]
> -- added on 7 December 2004 to improve performance on clinic attendance
> stored procedures
> CREATE INDEX ATTENDANCE_REPORT_INDEX ON dbo.tbl_AHSTDN (ProcedureID,
> TransactionDate, OFFICE_NUM, StudentID, SessionID) ON [PRIMARY]
> -- added on 20 January 2005 to improve performance on ticket count reports
> for PBO
> CREATE INDEX TICKET_COUNT_REPORT_INDEX ON dbo.tbl_AHSTDN (OFFICE_NUM,
> Location, [User], TicketID, PostingDate, Status) ON [PRIMARY]|||Hi Mike --
Thanks for your reply. Actually, this stored procedure has been running
without a hitch for so long that I had to go back and refresh my memory
about the columns, NULLs, etc.
Here's what happens:
1) The table is dropped and recreated every morning with no indexes or
primary keys
2) The data is fed in from text files via BULK INSERT
3) The nullable columns in the primary key are modified to be NOT NULL
4) The primary key is added
5) The indexes are added
No clustered index -- I need to rectify that one. Thanks for catching
it. And, since the bulk insert is done and then indexes are added, do I
need to do a reindex?
Anyway, I appreciate your time -- I will keep at it to figure out why it
works in SQL7 but not in SQL2000
Carl
Mike C# wrote:
> First thing, that's a heckuva lot of nullable columns. Even your PRIMARY
> KEY columns are all nullable!? :( I don't know if it's just me, but I als
o
> don't see a clustered index on this table anywhere either :(.
> Anyways, here's one suggestion - before the BULK INSERT drop all indexes
> (except the clustered index should you decide to add one), do the BULK
> INSERT, and then rebuild the indexes.
> At the very least I would imagine the table could stand to be reindexed bi
g
> time. One more quick suggestion - check to see if the database size is ne
ar
> the upper limit; i.e., is Auto-Grow likely to kick in during the BULK INSE
RT
> process? If so, resize the database to make it bigger. And if possible u
se
> the simple recovery model for this database.
>|||This can't be the actual DDL. You cannot put the primary key on a NULLable
column:
Msg 8111, Level 16, State 1, Line 1
Cannot define PRIMARY KEY constraint on nullable column in table
'tbl_AHSTDN'.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.
HTH
Kalen Delaney, SQL Server MVP
"Carl Imthurn" <nospam@.all.thanks> wrote in message
news:%23g6sjYWlGHA.1208@.TK2MSFTNGP02.phx.gbl...
> Here's the DDL for table/PK/index creation:
> The table data gets sucked out of an AS/400 every morning into text files
> and fed into SQL Server via BULK INSERT. The data in tbl_AHSTDN is not
> subject to updates; ie, it's a static table.
> Thanks in advance --
> Carl
> CREATE TABLE [dbo].[tbl_AHSTDN] (
> [DNHSTD] [char] (1) NULL ,
> [PatientID] [int] NULL ,
> [PatientType] [int] NULL ,
> [DNTYY] [int] NULL ,
> [DNTMM] [int] NULL ,
> [DNTDD] [int] NULL ,
> [DNSEQ] [int] NULL ,
> [DNID] [int] NULL ,
> [DNIDTY] [char] (1) NULL ,
> [TicketID] [int] NULL ,
> [ProcedureID] [int] NULL ,
> [ProcedureSuffix] [int] NULL ,
> [DNTICX] [int] NULL ,
> [Discipline] [varchar] (100) NULL ,
> [SessionID] [int] NULL ,
> [Grade] [int] NULL ,
> [DNMTHS] [int] NULL ,
> [StudentID] [char] (3) NULL ,
> [DNCGCD] [char] (1) NULL ,
> [DOCMASID] [int] NULL ,
> [DollarAmount] [money] NULL ,
> [DNIN01] [int] NULL ,
> [DNDAT1] [int] NULL ,
> [DNIN02] [int] NULL ,
> [DNDAT2] [int] NULL ,
> [DNCLM_NUM] [char] (5) NULL ,
> [Status] [char] (1) NULL ,
> [DNFILE] [char] (1) NULL ,
> [DNSEQN] [int] NULL ,
> [DNBK06] [char] (6) NULL ,
> [DNFLAG] [char] (1) NULL ,
> [BatchID] [int] NULL ,
> [Tooth] [char] (2) NULL ,
> [Surface] [char] (5) NULL ,
> [DNTTH2] [char] (2) NULL ,
> [DNSUR2] [char] (5) NULL ,
> [DNTTH3] [char] (2) NULL ,
> [DNSUR3] [char] (5) NULL ,
> [DNTTH4] [char] (2) NULL ,
> [DNSUR4] [char] (5) NULL ,
> [DNTTH5] [char] (2) NULL ,
> [DNSUR5] [char] (5) NULL ,
> [Location] [char] (4) NULL ,
> [DNCDAT] [int] NULL ,
> [User] [varchar] (10) NULL ,
> [DNUQID] [int] NULL ,
> [DNBL19] [varchar] (19) NULL ,
> [DNPTS] [real] NULL ,
> [DNGRP] [int] NULL ,
> [DNCMDT] [int] NULL ,
> [OFFICE_NUM] [int] NULL ,
> [TransactionDate] [datetime] NULL ,
> [CompletionDate] [datetime] NULL ,
> [PostingDate] [datetime] NULL
> ) ON [PRIMARY]
> -- PRIMARY KEY
> ALTER TABLE [dbo].[tbl_AHSTDN] WITH NOCHECK ADD
> CONSTRAINT [PK_tbl_AHSTDN] PRIMARY KEY NONCLUSTERED
> (
> [PatientID],
> [PatientType],
> [DNTYY],
> [DNTMM],
> [DNTDD],
> [DNSEQ],
> [OFFICE_NUM]
> ) ON [PRIMARY]
> -- INDEXES
> CREATE INDEX [PATIENTID] ON [dbo].[tbl_AHSTDN]([PatientID]) ON [PRIMARY]
> CREATE INDEX [PATIENTTYPE] ON [dbo].[tbl_AHSTDN]([PatientType]) ON
> [PRIMARY]
> CREATE INDEX [TICKETID] ON [dbo].[tbl_AHSTDN]([TicketID]) ON [PRIMARY]
> CREATE INDEX [PROCEDUREID] ON [dbo].[tbl_AHSTDN]([ProcedureID]) ON
> [PRIMARY]
> CREATE INDEX [PROCEDURESUFFIX] ON [dbo].[tbl_AHSTDN]([ProcedureSuffix])
> ON [PRIMARY]
> CREATE INDEX [GRADE] ON [dbo].[tbl_AHSTDN]([Grade]) ON [PRIMARY]
> CREATE INDEX [STUDENTID] ON [dbo].[tbl_AHSTDN]([StudentID]) ON [PRIMARY]
> CREATE INDEX [OFFICE_NUM] ON [dbo].[tbl_AHSTDN]([OFFICE_NUM]) ON
> [PRIMARY]
> CREATE INDEX [DNUQID] ON [dbo].[tbl_AHSTDN]([DNUQID]) ON [PRIMARY]
> CREATE INDEX [TRANSACTIONDATE] ON [dbo].[tbl_AHSTDN]([TransactionDate])
> ON [PRIMARY]
> CREATE INDEX [COMPLETIONDATE] ON [dbo].[tbl_AHSTDN]([CompletionDate]) ON
> [PRIMARY]
> CREATE INDEX [POSTINGDATE] ON [dbo].[tbl_AHSTDN]([PostingDate]) ON
> [PRIMARY]
> -- added on 7 December 2004 to improve performance on clinic attendance
> stored procedures
> CREATE INDEX ATTENDANCE_REPORT_INDEX ON dbo.tbl_AHSTDN (ProcedureID,
> TransactionDate, OFFICE_NUM, StudentID, SessionID) ON [PRIMARY]
> -- added on 20 January 2005 to improve performance on ticket count reports
> for PBO
> CREATE INDEX TICKET_COUNT_REPORT_INDEX ON dbo.tbl_AHSTDN (OFFICE_NUM,
> Location, [User], TicketID, PostingDate, Status) ON [PRIMARY]|||In adddition to adding the clustered index, if this database has been
updated from SQL7 to SQL2K, you should update the statistics (see the
sp_updatestats stored procedure documentation in BOL) after the conversion.
If you have not already done this, you should. WARNING, if you have a large
database, this can take a considerable length of time.
Also, look at the query plans for the updates in both databases and see if
there is any differences. If there are, you may need different indexes on
SQL2K than you did in SQL7. If SQL2K is choosing a bad plan, you might
consider using index hints if that signifigantly improves performance.
As an aside, it seems a shame to be moving to SQL2K at this time, expecially
if you are having trouble with it. Any posibility of going directly to SQL
2005?
Tom
"Carl Imthurn" <nospam@.all.thanks> wrote in message
news:us5wC1WlGHA.1240@.TK2MSFTNGP04.phx.gbl...
> Hi Mike --
> Thanks for your reply. Actually, this stored procedure has been running
> without a hitch for so long that I had to go back and refresh my memory
> about the columns, NULLs, etc.
> Here's what happens:
> 1) The table is dropped and recreated every morning with no indexes or
> primary keys
> 2) The data is fed in from text files via BULK INSERT
> 3) The nullable columns in the primary key are modified to be NOT NULL
> 4) The primary key is added
> 5) The indexes are added
> No clustered index -- I need to rectify that one. Thanks for catching it.
> And, since the bulk insert is done and then indexes are added, do I need
> to do a reindex?
> Anyway, I appreciate your time -- I will keep at it to figure out why it
> works in SQL7 but not in SQL2000
> Carl
> Mike C# wrote:|||Tom --
Thanks for your help. I updated the statistics -- no improvement.
I will look at the query plans and check for differences.
I used the index tuning wizard in SQL2K and it suggested an additional
index on tbl_AHSTDN.Status (that column is already indexed, but only in
conjunction with other columns in a composite index). I tried that with
no success.
I will also check out index hints to see if that makes a difference.
And as far as SQL2005 goes, I would like to, but need to get a little
more up-to-speed with it first.
Thanks again -- I appreciate your time.
Carl
Tom Cooper wrote:
> In adddition to adding the clustered index, if this database has been
> updated from SQL7 to SQL2K, you should update the statistics (see the
> sp_updatestats stored procedure documentation in BOL) after the conversion
.
> If you have not already done this, you should. WARNING, if you have a lar
ge
> database, this can take a considerable length of time.
> Also, look at the query plans for the updates in both databases and see if
> there is any differences. If there are, you may need different indexes on
> SQL2K than you did in SQL7. If SQL2K is choosing a bad plan, you might
> consider using index hints if that signifigantly improves performance.
> As an aside, it seems a shame to be moving to SQL2K at this time, expecial
ly
> if you are having trouble with it. Any posibility of going directly to SQ
L
> 2005?
> Tom
>|||sp_updatestats is weak, IMHO. Try doing an UPDATE STATISTICS WITH FULLSCAN.
That made a huge difference for me.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Carl Imthurn" <nospam@.all.thanks> wrote in message
news:OYuDJOYlGHA.380@.TK2MSFTNGP05.phx.gbl...
Tom --
Thanks for your help. I updated the statistics -- no improvement.
I will look at the query plans and check for differences.
I used the index tuning wizard in SQL2K and it suggested an additional
index on tbl_AHSTDN.Status (that column is already indexed, but only in
conjunction with other columns in a composite index). I tried that with
no success.
I will also check out index hints to see if that makes a difference.
And as far as SQL2005 goes, I would like to, but need to get a little
more up-to-speed with it first.
Thanks again -- I appreciate your time.
Carl
Tom Cooper wrote:
> In adddition to adding the clustered index, if this database has been
> updated from SQL7 to SQL2K, you should update the statistics (see the
> sp_updatestats stored procedure documentation in BOL) after the
> conversion.
> If you have not already done this, you should. WARNING, if you have a
> large
> database, this can take a considerable length of time.
> Also, look at the query plans for the updates in both databases and see if
> there is any differences. If there are, you may need different indexes on
> SQL2K than you did in SQL7. If SQL2K is choosing a bad plan, you might
> consider using index hints if that signifigantly improves performance.
> As an aside, it seems a shame to be moving to SQL2K at this time,
> expecially
> if you are having trouble with it. Any posibility of going directly to
> SQL
> 2005?
> Tom
>|||Hmmm... I thought the table was static - and that new records were just
being appended to the end. If you're dropping and recreating it each time,
then no need to reindex. Just create a clustered index before you start the
BULK INSERT, and create your other indexes after the fact. I'm not sure
where the speed problem is coming from. You don't happen to be pulling the
file from across the network (mapped drive or something)? And your database
is large enough to accomodate the new data (i.e., it's not autoshrinking and
autogrowing)?
"Carl Imthurn" <nospam@.all.thanks> wrote in message
news:us5wC1WlGHA.1240@.TK2MSFTNGP04.phx.gbl...
> Hi Mike --
> Thanks for your reply. Actually, this stored procedure has been running
> without a hitch for so long that I had to go back and refresh my memory
> about the columns, NULLs, etc.
> Here's what happens:
> 1) The table is dropped and recreated every morning with no indexes or
> primary keys
> 2) The data is fed in from text files via BULK INSERT
> 3) The nullable columns in the primary key are modified to be NOT NULL
> 4) The primary key is added
> 5) The indexes are added
> No clustered index -- I need to rectify that one. Thanks for catching it.
> And, since the bulk insert is done and then indexes are added, do I need
> to do a reindex?
> Anyway, I appreciate your time -- I will keep at it to figure out why it
> works in SQL7 but not in SQL2000
> Carl
> Mike C# wrote:

Issue with SqlUserDefinedAggregate

I am using the code below but I am getting a "zero" result for
dbo.AggredIssue('Test') user defined aggregate everytime that the query
executes parallel processing and uses the "Merge" method. It seems that my
private variable "private List<string> myList" gets nullified everytime it
goes through the "Merge".
I saw other people reporting the same issue in other forums, but nobody was
able to provide a solution or explanation.
See below a simplified version of my code (posted just after the queries)
that replicates the issue.
The query below works because it does't process the query in parallel.
SELECT GroupID, dbo.AggregIssue('Test')
FROM MyTable
where fund = 2
group by GroupID
The query below doesn't work because it process the query in parallel.
SELECT GroupID, dbo.AggregIssue('Test')
FROM MyTable
where fund <= 20
group by GroupID
[Serializable]
[SqlUserDefinedAggregate(
Format.UserDefined,
IsInvariantToNulls = true,
IsInvariantToDuplicates = false,
IsInvariantToOrder = true,
MaxByteSize = 1000)]
public class AggregIssue : IBinarySerialize {
private List<string> myList;
private int myResult;
public void Init() {
myList = new List<string>();
}
public void Accumulate(SqlString Value) {
if (Value.IsNull) { return; }
myList.Add(Value.ToString());
}
public void Merge(AggregIssue Other) {
if (Other.myList != null) {
if (myList == null) {
myList = Other.myList;
}
else {
myList.AddRange(Other.myList);
}
}
}
public SqlInt32 Terminate() {
return new SqlInt32(myResult);
}
public void Read(BinaryReader r) {
myResult = r.ReadInt32();
}
//The code below is simplified for posting in the forum.
//I do additional manipulation of the list and require
//the aggregation to be IBinarySerialize.
//But this code replicates the issue also
public void Write(BinaryWriter w) {
w.Write(myList.Count);
}
}"Fernando" <Fernando@.discussions.microsoft.com> wrote in message
news:60C66368-EFD5-4C6B-A9EF-FC363A91C1DB@.microsoft.com...
>I am using the code below but I am getting a "zero" result for
> dbo.AggredIssue('Test') user defined aggregate everytime that the query
> executes parallel processing and uses the "Merge" method. It seems that my
> private variable "private List<string> myList" gets nullified everytime it
> goes through the "Merge".
> I saw other people reporting the same issue in other forums, but nobody
> was
> able to provide a solution or explanation.
> See below a simplified version of my code (posted just after the queries)
> that replicates the issue.
> The query below works because it does't process the query in parallel.
> SELECT GroupID, dbo.AggregIssue('Test')
> FROM MyTable
> where fund = 2
> group by GroupID
>
> The query below doesn't work because it process the query in parallel.
>
Yikes! How on earth do you test the merge method of a CLR Aggregate?
Perhaps you could cook up an appropriate plan guide?
David|||Hello Fernando,
F> I am using the code below but I am getting a "zero" result for
F> dbo.AggredIssue('Test') user defined aggregate everytime that the
F> query executes parallel processing and uses the "Merge" method. It
F> seems that my private variable "private List<string> myList" gets
F> nullified everytime it goes through the "Merge".
I don't believe BinaryRead and BinaryWrite method isn't preserving your List
<T>
as you expect it does. Here's example that serializes such a between calls
to merge.
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Collections.Generic;
using System.Text;
using System.Runtime.Serialization.Formatters.Binary;
using System.IO;
[Serializable]
[SqlUserDefinedAggregate(Format.UserDefined,MaxByteSize=8000)]
public class CSVBuilder : IBinarySerialize, INullable
{
List<string> _list = null;
public void Init()
{
_list = new List<string>();
}
public void Accumulate(SqlString Value)
{
_list.Add(Value.Value);
}
public void Merge(CSVBuilder Group)
{
_list.AddRange(Group._list);
}
public SqlString Terminate()
{
StringBuilder sb = new StringBuilder(8000);
foreach (string item in _list) {
sb.Append(", ");
sb.Append(item);
}
return new SqlString(sb.ToString().Substring(2));
}
void IBinarySerialize.Read(System.IO.BinaryReader r)
{
int size = r.ReadInt32();
BinaryFormatter f = new BinaryFormatter();
_list = (List<string> )(f.Deserialize(new MemoryStream(r.ReadBytes(size))));
}
void IBinarySerialize.Write(System.IO.BinaryWriter w)
{
BinaryFormatter f = new BinaryFormatter();
MemoryStream ms = new MemoryStream();
f.Serialize(ms,_list);
Int32 size = (Int32)ms.Length;
w.Write(size);
w.Write(ms.ToArray(), 0, (int)size);
}
bool INullable.IsNull
{
get { return _list == null; }
}
}
It seems to work with this.
USE scratch
go
create table dbo.vs(v varchar(50));
insert into dbo.vs values ('Alpha')
insert into dbo.vs values ('Bravo')
insert into dbo.vs values ('Charlie')
insert into dbo.vs values ('Delta')
insert into dbo.vs values ('Echo')
insert into dbo.vs values ('Foxtrot')
insert into dbo.vs values ('Golf')
insert into dbo.vs values ('Hotel')
insert into dbo.vs values ('India')
insert into dbo.vs values ('Juliet')
insert into dbo.vs values ('Kilo')
insert into dbo.vs values ('Lima')
insert into dbo.vs values ('Mike')
insert into dbo.vs values ('November')
insert into dbo.vs values ('Oscar')
insert into dbo.vs values ('Papa')
insert into dbo.vs values ('Quebec')
insert into dbo.vs values ('Romeo')
insert into dbo.vs values ('Sierra')
insert into dbo.vs values ('Tango')
insert into dbo.vs values ('Uniform')
insert into dbo.vs values ('Victor')
insert into dbo.vs values ('Whiskey')
insert into dbo.vs values ('Yankee')
insert into dbo.vs values ('Zulu')
go
select dbo.csvbuilder(v) from dbo.vs
go
drop table dbo.vs
go
Returns:
Alpha, Bravo, Charlie, Delta, Echo, Foxtrot, Golf, Hotel, India, Juliet,
Kilo, Lima, Mike, November, Oscar, Papa, Quebec, Romeo, Sierra, Tango, Unifo
rm,
Victor, Whiskey, Yankee, Zulu
Thanks!
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||Hi Kent,
Thank you very much for your response. The reason I don't serialize the list
itself is because the list exceeds the limit of 8000 for maxbytesize.
I need the whole list to process the resolution of a non-linear equation, so
I cannot pre-aggregate values to save space before serializing. So what I am
trying to do is to solve the equation (using the required data from the list
)
just before serialization.
The weird thing is that it works if the code doesn't go through the merge
method.
If you can think of some other alternative...
Thank you very much,
Fernando|||"Fernando" <Fernando@.discussions.microsoft.com> wrote in message
news:06F1F640-4435-49F1-BB5E-B0A268648E7F@.microsoft.com...
> Hi Kent,
> Thank you very much for your response. The reason I don't serialize the
> list
> itself is because the list exceeds the limit of 8000 for maxbytesize.
> I need the whole list to process the resolution of a non-linear equation,
> so
> I cannot pre-aggregate values to save space before serializing. So what I
> am
> trying to do is to solve the equation (using the required data from the
> list)
> just before serialization.
> The weird thing is that it works if the code doesn't go through the merge
> method.
> If you can think of some other alternative...
>
As a workaround you can always prevent a parallel plan with a MAXDOP hint.
David|||David,
Thanks for your response. The option "MAXDOP 1" worked. It would be nicer if
parallelism is enabled, but for now it a good workaround.
Thanks again,
Fernando
"David Browne" wrote:

> "Fernando" <Fernando@.discussions.microsoft.com> wrote in message
> news:06F1F640-4435-49F1-BB5E-B0A268648E7F@.microsoft.com...
>
> As a workaround you can always prevent a parallel plan with a MAXDOP hint.
> David
>
>|||Hello Fernando,
F> The weird thing is that it works if the code doesn't go through the
F> merge method.
Right, I suspect the that the merge method if actually building instances
of the UDA on different CPUs and when has to marshal them together (merging
the threads if you will), that's when it calls the serialization stuff and
that's when you lose your values.
F> If you can think of some other alternative...
Don't use a UDA, use a procedure or function if possible.
Thanks!
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||Thanks Kent!

> Hello Fernando,
> F> The weird thing is that it works if the code doesn't go through the
> F> merge method.
> Right, I suspect the that the merge method if actually building instances
> of the UDA on different CPUs and when has to marshal them together (mergin
g
> the threads if you will), that's when it calls the serialization stuff and
> that's when you lose your values.
You are probably right, and that would be the reason why my code is failing.

> F> If you can think of some other alternative...
> Don't use a UDA, use a procedure or function if possible.
The solution is much simpler with UDA as the List used in the aggregation is
dynamically populated based on the items in the "Group By" of the query.
The workaround provided by David Browne (force non-parallelism) will work
for now.
Thank you very much for your help!
Fernando

>

Issue with SqlUserDefinedAggregate

I am using the code below but I am getting a "zero" result for dbo.AggredIssue('Test') user defined aggregate everytime that the query executes parallel processing and uses the "Merge" method. It seems that my private variable "private List<string> myList" gets nullified everytime it goes through the "Merge".

I saw other people reporting the same issue in other forums, but nobody was able to provide a solution or explanation.

See below a simplified version of my code (posted just after the queries) that replicates the issue.

The query below works because it does't process the query in parallel.

SELECT GroupID, dbo.AggregIssue('Test')

FROM MyTable

where fund = 2

group by GroupID

The query below doesn't work because it process the query in parallel.

SELECT GroupID, dbo.AggregIssue('Test')

FROM MyTable

where fund <= 20

group by GroupID

[Serializable]

[SqlUserDefinedAggregate(

Format.UserDefined,

IsInvariantToNulls = true,

IsInvariantToDuplicates = false,

IsInvariantToOrder = true,

MaxByteSize = 1000)]

public class AggregIssue : IBinarySerialize {

private List<string> myList;

private int myResult;

public void Init() {

myList = new List<string>();

}

public void Accumulate(SqlString Value) {

if (Value.IsNull) { return; }

myList.Add(Value.ToString());

}

public void Merge(AggregIssue Other) {

if (Other.myList != null) {

if (myList == null) {

myList = Other.myList;

}

else {

myList.AddRange(Other.myList);

}

}

}

public SqlInt32 Terminate() {

return new SqlInt32(myResult);

}

public void Read(BinaryReader r) {

myResult = r.ReadInt32();

}

//The code below is simplified for posting in the forum.

//I do additional manipulation of the list and require

//the aggregation to be IBinarySerialize.

//But this code replicates the issue also

public void Write(BinaryWriter w) {

w.Write(myList.Count);

}

}

? If you want to maintain the list properly you should serialize/deserialize the list itself -- and not its count -- in the Read and Write methods. They may be called more than one during the course of aggregation. Your current code is probably failing because it's making assumptions about how and when these will be called. You should instead do something like: public void Write(BinaryWriter w) { w.Write(myList.Count); foreach (string theString in myList) w.Write(theString); } public void Read(BinaryReader r) { this.myList = new List<string>(); int numStrings = r.ReadInt32(); for (int i = 0; i<numStrings; i++) myList.Add(r.ReadString()); } ... Then, in your Terminate method: public SqlInt32 Terminate() { return new SqlInt32(myList.Count); } -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <FernandoT@.discussions..microsoft.com> wrote in message news:e4178255-6dd9-4b48-b5ae-eb072c88e314_WBRev3_@.discussions..microsoft.com...This post has been edited either by the author or a moderator in the Microsoft Forums: http://forums.microsoft.com I am using the code below but I am getting a "zero" result for dbo.AggredIssue('Test') user defined aggregate everytime that the query executes parallel processing and uses the "Merge" method. It seems that my private variable "private List<string> myList" gets nullified everytime it goes through the "Merge". I saw other people reporting the same issue in other forums, but nobody was able to provide a solution or explanation. See below a simplified version of my code (posted just after the queries) that replicates the issue. The query below works because it does't process the query in parallel. SELECT GroupID, dbo.AggregIssue('Test') FROM MyTable where fund = 2 group by GroupID The query below doesn't work because it process the query in parallel. SELECT GroupID, dbo.AggregIssue('Test') FROM MyTable where fund <= 20 group by GroupID [Serializable] [SqlUserDefinedAggregate( Format.UserDefined, IsInvariantToNulls = true, IsInvariantToDuplicates = false, IsInvariantToOrder = true, MaxByteSize = 1000)] public class AggregIssue : IBinarySerialize { private List<string> myList; private int myResult; public void Init() { myList = new List<string>(); } public void Accumulate(SqlString Value) { if (Value.IsNull) { return; } myList.Add(Value.ToString()); } public void Merge(AggregIssue Other) { if (Other.myList != null) { if (myList == null) { myList = Other.myList; } else { myList.AddRange(Other.myList); } } } public SqlInt32 Terminate() { return new SqlInt32(myResult); } public void Read(BinaryReader r) { myResult = r.ReadInt32(); } //The code below is simplified for posting in the forum. //I do additional manipulation of the list and require //the aggregation to be IBinarySerialize. //But this code replicates the issue also public void Write(BinaryWriter w) { w.Write(myList.Count); } }|||

Hi Adam,

Thank you very much for your response.

The reason that I don't serialize the list itself is because of the MaxByteSize limitation of 8096. My list will easily go beyond that limitation. I test your solution and it works for a small list, but not for long ones.

I noticed that the list is loosing the information in the merge. If the query doesn't go through parallel threads, it works fine.

My assumption is that the serialization will happen before terminate and final output of aggregate value for each row of the result set.

Any other ideas?

Thanks!!

Fernando

|||Hi, Fernando,

I think Adam is right about you didn't serialize the List. Other people on the forum is talking about "break the 8k boundary" stuff, don't know what conclusion they have right now. But since you are using a List to join strings together, you always facing the problem.

About Merge() stuff, to my limited understanding:

If single thread (no parallel op):

Init() -> Accumulate() -> Terminate()

If multi threads (parallel plan):

T1: Init() -> Accumulate()
T2: Init() -> Accumulate() -> Merge( with T1)
T3: Init() -> Accumulate() -> Merge( with T3) -> Terminate()

This is only to illustrate the way Merge() works. Any T can be reused during this, thus why Init() must clean everything.

In Adam's book Chapter 6, p. 195, I quote:

"It is important to understand when dealing with aggregates that the intermediate result will be serialized and deserialized once per row of aggregated data. Therefore, it is imperative for performance that serialization and deserialization be as efficient as possible"

I'm a bit confused here, could Adam give some explanation for this paragraph? What's the relationship between Merge() and once per row of aggregated data?

Regards,

Dong Xie
|||? Actually, that quote from the book is not quite accurate anymore -- when I wrote it against an earlier CTP it appeared to be true, but now it does not. I believe that it has been changed in a later printing of the book, but I'll check today with Apress to make sure. Thanks for pointing it out! Anyway, the correct phrasing at this point should be "the intermediate result can be serialized and deserialized up to one time per row of aggregated data" In other words, the SQL Server engine may not do it for every row (or even nearly that often in most cases), but you need to code your aggregate as if it will. There is not necessarily a direct/stated relationship between Merge and Read/Write, but it appears that when Merge is called the engine also does a round of serialization/deserialization. I'm not sure why, though. Hopefully Steven Hemingray or one of the other dataworks guys will show up in this thread and clarify! -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <Dong Xie@.discussions.microsoft.com> wrote in message news:c5fafeee-262b-43a4-9295-10a7cb07d92a@.discussions.microsoft.com...Hi, Fernando,I think Adam is right about you didn't serialize the List. Other people on the forum is talking about "break the 8k boundary" stuff, don't know what conclusion they have right now. But since you are using a List to join strings together, you always facing the problem.About Merge() stuff, to my limited understanding:If single thread (no parallel op):Init() -> Accumulate() -> Terminate()If multi threads (parallel plan):T1: Init() -> Accumulate()T2: Init() -> Accumulate() -> Merge( with T1)T3: Init() -> Accumulate() -> Merge( with T3) -> Terminate()This is only to illustrate the way Merge() works. Any T can be reused during this, thus why Init() must clean everything.In Adam's book Chapter 6, p. 195, I quote:"It is important to understand when dealing with aggregates that the intermediate result will be serialized and deserialized once per row of aggregated data. Therefore, it is imperative for performance that serialization and deserialization be as efficient as possible"I'm a bit confused here, could Adam give some explanation for this paragraph? What's the relationship between Merge() and once per row of aggregated data?Regards,Dong Xie|||

It seems that you are right and serialization/deserialization happens when merge is called.

For now I have a workaround that was suggested in another forum to use "MAXDOP=1" and it works as merge is never executed.

It is not ideal, as parallelism cannot be leveraged, but it is a workaround.

Thanks to all for the responses!!

Fernando

|||

There seems to be a few issues within the thread:

1) Why is the private field myList nullified?

Whenever serialization takes place (Read/Write), a new instance is instantiated and it is up to your serialization code to fill the instance. Since your Write() only sets myResult, myList will be remain on the default value of null.

2) Why is MaxByteSize not always enforced?

MaxByteSize is enforced during serialization. You could cause instances to become much larger than 8k over Accumulate() calls and then not serialize out 8k.

With this said, there should not be a reason to build UDAggs that become larger than 8k but do not serialize out 8k. Read/write should serialize all necessary information for the UDAggs.

The UDAgg code within this thread is a good example of a contrived case for this. Since Terminate() only returns the count, there is not a need to accumulate the actual strings, but Accumulate() could increment a counter instead.

3) When is Read/Write called?

Within SQL Server 2005, serialization takes place during Merge() and before Terminate() is called. If the UDAgg provided accessed the myList within Terminate(), you would see a NullReferenceException from within Terminate() (since Write() does not reconstruct myList and null is its default reference).

As pointed out within this thread, Read/Write is not called for every row. However, please write your UDAgg with proper serialization semantics as if it were called every row.

Hope that helps!

-Jason

|||

Jason,

Thanks for all your explanation. It makes sense to me now how all this work.

But I still have one issue, which is the limitation of 8K. The list that I am building cannot be contrived until it is complete just before "Terminate". In the example I am passing the count and I could do that in the merge also. But in my real case, I need the complete list to be used in the resolution of a non-linear equation, so I cannot do anything with it until I pass it to a iterative algorithm in order to solve the equation. But this list can grow bigger than 8K.

The only way to avoid so far is to limit the degree of parallelism to 1, so the code doesn't go to merge.

Any thoughts on this?

Thanks,

Fernando

|||

Fernando-

Merge is also called in some more complex queries (such as GROUP BY WITH CUBE).

This is an interesting workaround to the 8k limitation for your scenario. I do worry that your workaround leaves your UDAgg prone to internal changes within SQL Server causes serialization to be more frequently performed or eliminated. For this reason, I'd recommend always using an approach where instances created from Read/Write are no different than the original.

I cannot recommend using this approach for these reasons, but limiting DOP to 1 should eliminate Merge() calls for the most part. If you absolutely must use this workaround, I'd recommend safeguarding against your assumptions (Merge never called, serialization takes place once after all Accumulate calls but before Terminate):

Always throw an exception within your Merge() call so that in the case that Merge() is invoked, you'll know what the issue is and you can try to simplify the query so that Merge is not called.|||

Jason,

Thanks for all your help and explanations.

Fernando

Issue rendering report from aspx page via WS and colspan problem

I'm using web services to render a report from the code behind in an aspx
page. Aside from images not showing (which is another issue), the page
looks OK. however, when I copy the report's html into a test html page in
VS 2003, there are some errors. one of which has to do with a colspan
attribute. here's the code for that attribute: "colSpan=22 ?". When I
delete the " ?" then its OK. can anyone tell me why SSRS is doing this and
how to remedy it? I'm using SSRS sp1. I can't install sp2 for a few more
weeks yet.
Thanks.
--
moondaddy@.nospam.nospamHello,
To understand the issue better, I' d like to know if the it occurs for each
item of the report? If it is for each item, can you reproduce the issue
with a simple report with a Textbox etc and post back the reult html source
file? You may want to take a look at this link on rendering report by using
SRS web services:
http://www.codeproject.com/useritems/SQLRSViewer.asp
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
| From: "moondaddy" <moondaddy@.nospam.nospam>
| Subject: Issue rendering report from aspx page via WS and colspan problem
| Date: Mon, 16 May 2005 15:32:03 -0500
| Lines: 17
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2900.2527
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2527
| X-RFC2646: Format=Flowed; Original
| Message-ID: <uXC9IVlWFHA.3188@.TK2MSFTNGP09.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| NNTP-Posting-Host: cpe-24-175-69-234.houston.res.rr.com 24.175.69.234
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP09.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.reportingsvcs:43974
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
|
| I'm using web services to render a report from the code behind in an aspx
| page. Aside from images not showing (which is another issue), the page
| looks OK. however, when I copy the report's html into a test html page
in
| VS 2003, there are some errors. one of which has to do with a colspan
| attribute. here's the code for that attribute: "colSpan=22 ?". When I
| delete the " ?" then its OK. can anyone tell me why SSRS is doing this
and
| how to remedy it? I'm using SSRS sp1. I can't install sp2 for a few
more
| weeks yet.
|
| Thanks.
|
|
|
| --
| moondaddy@.nospam.nospam
|
|
||||It only happens in one spefic place in the report, however, there are many
other identical elements in the report so I dont know why it just happens in
one. The link below looks like a good source. I'm already doing much of
what he talks about, but I'll go through it very carrful as there may be
some key things to pickup.
Thanks.
--
moondaddy@.nospam.nospam
"Peter Yang [MSFT]" <petery@.online.microsoft.com> wrote in message
news:0HAzW7oWFHA.2184@.TK2MSFTNGXA01.phx.gbl...
> Hello,
> To understand the issue better, I' d like to know if the it occurs for
> each
> item of the report? If it is for each item, can you reproduce the issue
> with a simple report with a Textbox etc and post back the reult html
> source
> file? You may want to take a look at this link on rendering report by
> using
> SRS web services:
> http://www.codeproject.com/useritems/SQLRSViewer.asp
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================>
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
>
> --
> | From: "moondaddy" <moondaddy@.nospam.nospam>
> | Subject: Issue rendering report from aspx page via WS and colspan
> problem
> | Date: Mon, 16 May 2005 15:32:03 -0500
> | Lines: 17
> | X-Priority: 3
> | X-MSMail-Priority: Normal
> | X-Newsreader: Microsoft Outlook Express 6.00.2900.2527
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2527
> | X-RFC2646: Format=Flowed; Original
> | Message-ID: <uXC9IVlWFHA.3188@.TK2MSFTNGP09.phx.gbl>
> | Newsgroups: microsoft.public.sqlserver.reportingsvcs
> | NNTP-Posting-Host: cpe-24-175-69-234.houston.res.rr.com 24.175.69.234
> | Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP09.phx.gbl
> | Xref: TK2MSFTNGXA01.phx.gbl
> microsoft.public.sqlserver.reportingsvcs:43974
> | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
> |
> | I'm using web services to render a report from the code behind in an
> aspx
> | page. Aside from images not showing (which is another issue), the page
> | looks OK. however, when I copy the report's html into a test html page
> in
> | VS 2003, there are some errors. one of which has to do with a colspan
> | attribute. here's the code for that attribute: "colSpan=22 ?". When I
> | delete the " ?" then its OK. can anyone tell me why SSRS is doing this
> and
> | how to remedy it? I'm using SSRS sp1. I can't install sp2 for a few
> more
> | weeks yet.
> |
> | Thanks.
> |
> |
> |
> | --
> | moondaddy@.nospam.nospam
> |
> |
> |
>|||Hello,
It is odd that the issue only occurs with a specific item. It does not seem
to be a issue on Web services. Did you double check the function to render
to report? If you re-deploy the report, does the issue still occur?
Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
| From: "moondaddy" <moondaddy@.nospam.nospam>
| References: <uXC9IVlWFHA.3188@.TK2MSFTNGP09.phx.gbl>
<0HAzW7oWFHA.2184@.TK2MSFTNGXA01.phx.gbl>
| Subject: Re: Issue rendering report from aspx page via WS and colspan
problem
| Date: Tue, 17 May 2005 00:14:19 -0500
| Lines: 87
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2900.2527
| X-RFC2646: Format=Flowed; Original
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2527
| Message-ID: <uJ4q#4pWFHA.2768@.tk2msftngp13.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| NNTP-Posting-Host: cpe-24-175-69-234.houston.res.rr.com 24.175.69.234
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!tk2msftngp13.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.reportingsvcs:44000
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
|
| It only happens in one spefic place in the report, however, there are
many
| other identical elements in the report so I dont know why it just happens
in
| one. The link below looks like a good source. I'm already doing much of
| what he talks about, but I'll go through it very carrful as there may be
| some key things to pickup.
|
| Thanks.
|
| --
| moondaddy@.nospam.nospam
| "Peter Yang [MSFT]" <petery@.online.microsoft.com> wrote in message
| news:0HAzW7oWFHA.2184@.TK2MSFTNGXA01.phx.gbl...
| > Hello,
| >
| > To understand the issue better, I' d like to know if the it occurs for
| > each
| > item of the report? If it is for each item, can you reproduce the issue
| > with a simple report with a Textbox etc and post back the reult html
| > source
| > file? You may want to take a look at this link on rendering report by
| > using
| > SRS web services:
| >
| > http://www.codeproject.com/useritems/SQLRSViewer.asp
| >
| > Best Regards,
| >
| > Peter Yang
| > MCSE2000/2003, MCSA, MCDBA
| > Microsoft Online Partner Support
| >
| > When responding to posts, please "Reply to Group" via your newsreader so
| > that others may learn and benefit from your issue.
| >
| > =====================================================| >
| >
| > This posting is provided "AS IS" with no warranties, and confers no
| > rights.
| >
| >
| >
| >
| > --
| > | From: "moondaddy" <moondaddy@.nospam.nospam>
| > | Subject: Issue rendering report from aspx page via WS and colspan
| > problem
| > | Date: Mon, 16 May 2005 15:32:03 -0500
| > | Lines: 17
| > | X-Priority: 3
| > | X-MSMail-Priority: Normal
| > | X-Newsreader: Microsoft Outlook Express 6.00.2900.2527
| > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2527
| > | X-RFC2646: Format=Flowed; Original
| > | Message-ID: <uXC9IVlWFHA.3188@.TK2MSFTNGP09.phx.gbl>
| > | Newsgroups: microsoft.public.sqlserver.reportingsvcs
| > | NNTP-Posting-Host: cpe-24-175-69-234.houston.res.rr.com 24.175.69.234
| > | Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP09.phx.gbl
| > | Xref: TK2MSFTNGXA01.phx.gbl
| > microsoft.public.sqlserver.reportingsvcs:43974
| > | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
| > |
| > | I'm using web services to render a report from the code behind in an
| > aspx
| > | page. Aside from images not showing (which is another issue), the
page
| > | looks OK. however, when I copy the report's html into a test html
page
| > in
| > | VS 2003, there are some errors. one of which has to do with a colspan
| > | attribute. here's the code for that attribute: "colSpan=22 ?".
When I
| > | delete the " ?" then its OK. can anyone tell me why SSRS is doing
this
| > and
| > | how to remedy it? I'm using SSRS sp1. I can't install sp2 for a few
| > more
| > | weeks yet.
| > |
| > | Thanks.
| > |
| > |
| > |
| > | --
| > | moondaddy@.nospam.nospam
| > |
| > |
| > |
| >
|
|
|

Issue in using Round

I am using a Round to get the Value of a Float datatype column from a database
and if the use the below code and if the value of the Column is say 0 it returns me -1. please advice why this can be
Temp = round(0* 0.95,2)
samay
I am using a Round to get the Value of a Float datatype column from a database
and if I use the below code
and if the value of the Column is say 0
It do returns a value as 0 but it stores it into a table it's -1 my column for Temp is of Float datatype
please advice why this can be
Temp = round(0* 0.95,2)

> samay
>
|||Samay,
Can you try to explain your problem again, and cut and paste the exact
statements that are not working? I don't understand what you are asking
at all. Since 0*0.95 is zero, the value of round(0*0.95,2) will be zero.
Steve Kass
Drew University
KritiVerma@.hotmail.com wrote:
[vbcol=seagreen]
> I am using a Round to get the Value of a Float datatype column from a database
> and if I use the below code
>and if the value of the Column is say 0
>It do returns a value as 0 but it stores it into a table it's -1 my column for Temp is of Float datatype
> please advice why this can be
> Temp = round(0* 0.95,2)
>
>
|||To add to Steve's response, the result of your round expression will always
be zero. I suspect your problem is due to persisting the result of a
boolean expression instead of the intended arithmetic expression result. A
VbScript True boolean value will be stored as -1 in a numeric datatype.
Hope this helps.
Dan Guzman
SQL Server MVP
"KritiVerma@.hotmail.com" <KritiVermahotmailcom@.discussions.microsoft.com>
wrote in message news:DCE60959-1658-4B62-B74A-30B1414229AB@.microsoft.com...
> I am using a Round to get the Value of a Float datatype column from a
database
> and if I use the below code
> and if the value of the Column is say 0
> It do returns a value as 0 but it stores it into a table it's -1 my column
for Temp is of Float datatype[vbcol=seagreen]
> please advice why this can be
> Temp = round(0* 0.95,2)
>

Issue in using Round

I am using a Round to get the Value of a Float datatype column from a databa
se
and if the use the below code and if the value of the Column is say 0 it ret
urns me -1. please advice why this can be
Temp = round(0* 0.95,2)
samayI am using a Round to get the Value of a Float datatype column from a databa
se
and if I use the below code
and if the value of the Column is say 0
It do returns a value as 0 but it stores it into a table it's -1 my column f
or Temp is of Float datatype
please advice why this can be
Temp = round(0* 0.95,2)

> samay
>|||Samay,
Can you try to explain your problem again, and cut and paste the exact
statements that are not working? I don't understand what you are asking
at all. Since 0*0.95 is zero, the value of round(0*0.95,2) will be zero.
Steve Kass
Drew University
KritiVerma@.hotmail.com wrote:
[vbcol=seagreen]
> I am using a Round to get the Value of a Float datatype column from a data
base
> and if I use the below code
>and if the value of the Column is say 0
>It do returns a value as 0 but it stores it into a table it's -1 my column
for Temp is of Float datatype
> please advice why this can be
> Temp = round(0* 0.95,2)
>
>|||To add to Steve's response, the result of your round expression will always
be zero. I suspect your problem is due to persisting the result of a
boolean expression instead of the intended arithmetic expression result. A
VbScript True boolean value will be stored as -1 in a numeric datatype.
Hope this helps.
Dan Guzman
SQL Server MVP
"KritiVerma@.hotmail.com" <KritiVermahotmailcom@.discussions.microsoft.com>
wrote in message news:DCE60959-1658-4B62-B74A-30B1414229AB@.microsoft.com...
> I am using a Round to get the Value of a Float datatype column from a
database
> and if I use the below code
> and if the value of the Column is say 0
> It do returns a value as 0 but it stores it into a table it's -1 my column
for Temp is of Float datatype[vbcol=seagreen]
> please advice why this can be
> Temp = round(0* 0.95,2)
>

Issue in using Round

I am using a Round to get the Value of a Float datatype column from a database
and if the use the below code and if the value of the Column is say 0 it returns me -1. please advice why this can be
Temp = round(0* 0.95,2)
samayI am using a Round to get the Value of a Float datatype column from a database
and if I use the below code
and if the value of the Column is say 0
It do returns a value as 0 but it stores it into a table it's -1 my column for Temp is of Float datatype
please advice why this can be
Temp = round(0* 0.95,2)
> samay
>|||Samay,
Can you try to explain your problem again, and cut and paste the exact
statements that are not working? I don't understand what you are asking
at all. Since 0*0.95 is zero, the value of round(0*0.95,2) will be zero.
Steve Kass
Drew University
KritiVerma@.hotmail.com wrote:
> I am using a Round to get the Value of a Float datatype column from a database
> and if I use the below code
>and if the value of the Column is say 0
>It do returns a value as 0 but it stores it into a table it's -1 my column for Temp is of Float datatype
> please advice why this can be
> Temp = round(0* 0.95,2)
>
>
>>samay
>>|||To add to Steve's response, the result of your round expression will always
be zero. I suspect your problem is due to persisting the result of a
boolean expression instead of the intended arithmetic expression result. A
VbScript True boolean value will be stored as -1 in a numeric datatype.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"KritiVerma@.hotmail.com" <KritiVermahotmailcom@.discussions.microsoft.com>
wrote in message news:DCE60959-1658-4B62-B74A-30B1414229AB@.microsoft.com...
> I am using a Round to get the Value of a Float datatype column from a
database
> and if I use the below code
> and if the value of the Column is say 0
> It do returns a value as 0 but it stores it into a table it's -1 my column
for Temp is of Float datatype
> please advice why this can be
> Temp = round(0* 0.95,2)
>
> > samay
> >

Issue during update and insert (trigger problems)

Hello,

I have created a job that fill and update a database from a source db with same structure.

INSERT code is made up comparing the pk column in the source and dest db, the missed ones are filled in the destination.

UPDATE: check col by col, if any change value exists, updated is performed with the following statement for any tables in the DB

UPDATE tableADest

SET col1=source.Col1, col2=source.col2, ... coln=source.coln

FROM tableASource source

INNER JOIN tableAdest dest

ON dest.colpk = source.colpk

The main problem is that I cannot identify the row update in the souce db, everytime I have to compare the whole equivalent tables (source and dest db), because there are not timestamp, updated cols or any cols usefuls, to have a subset of data and find any new or upadeted rows.

I tried replication, but it does not work on that db.

So I created a trigger for each table where new insert or updated row must be detected. The PKs are saved on tables.

The problem is that when I run the application it hold-on, when triggers are disabled the application run fine.

How can I use trigger on several tables without affectrunning application?

Thank

can you post your triggers?|||can you post your triggers?
|||

That's the piece of code:

INSERT INTO TempPkDB.dbo.tabella (pk) SELECT i.pk FROM inserted i

WHERE NOT EXISTS (SELECT pk FROM TempPkDB.dbo.tabella t

WHERE t.pk = i.pk)

This code is applied to each tables where tables must be moved to the destination table.

thank

Issue doing Integrated services programming using Microsoft.SqlServer.ManagedDTS.dll

Hi,

I am writing an installer code in C# to deploy the SSIS package.

I want to use Microsoft.SqlServer.ManagedDTS.dll for it.

It is mentioned in few articles available online that Microsoft.SqlServer.ManagedDTS.dll ships with SQL Server 2005.

I searched on our database server but could not get it.

Anyone having idea on this please help.

HV

Don't try and install SSIS by hand, it is not a good idea It is not supposed to be a redistributable component. There are a lot more assemblies that just that one for SSIS.

Since SSIS requires a full SQL Server license, why not use the regular SQL setup to do this for you. You can choose which servers you want.

If you have installed SSIS on your server then it will certainly be in the GAC, but it may not be on the file system outside of this, unless you installed the tools, in which case it is - C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies

|||Another question to ask: Is SSIS installed on this SQL Server box? A lot of SQL 2005 servers will be set up by their DBAs to not have any unnecessary components installed, and SSIS sometimes falls into this category.|||

Hi,

Thank you Darren.

SSIS is installed on the machine.

I checked the following folder :- C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies

but dll is not available.

Which tool installation delivers Microsoft.SqlServer.ManagedDts.dll?

Please let me know.

HV

|||

Hi,

Thank you Mathew.

SSIS is installed on the machine.

I checked the following folder :- C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies

but it's not available.

HV

|||

Hi,

I picked the Microsoft.SQLServer.ManagedDTS.dll from following folder:

C:\WINDOWS\assembly\GAC_MSIL\Microsoft.SqlServer.ManagedDTS\9.0.242.0__89845dcd8080cc91>

Similarly picked Microsoft.SqlServer.DTSRuntimeWrap.dll also.

I added it as reference in my .NET application.

When I execute the program I get below error:

Retrieving the COM class factory for component with CLSID {E44847F1-FD8C-4251-B5DA-B04BB22E236E} failed due to the following error: 80040154.

Any Clue?

How to get the RunningPackages information back to a client PC?

HV

|||

If you want running packages information, why not just use the MS tools?

Do you have SSIS tools installed on the local machine that hosts the program? The error indicates that Microsoft.SqlServer.DTSRuntimeWrap is not installed on the local PC?

|||

No SSIS is not installed on the machine which hosts program.

But that is my requirement , I want to deploy SSIS package remotely.

I have installed Microsoft.SQLServer.DTSRuntimeWrap.dll in GAC.

Let me know if there is a way out to use it on a machine where SSIS is not installed.

HV

|||

SSIS is not installed you say, and you get an error that says in cannot find a COM component. Do you think there may be a connection?

I refer you to my original post, apart from saying that a manual install was a silly idea, I also pointed out "There are a lot more assemblies that just that one for SSIS."

As a start point that DLL is just a wrapper to the COM library that does the work, the name hints at that, and the error proves that it is trying to use a COM DLL that is not there, a COM DLL with a ProgID of {E44847F1-FD8C-4251-B5DA-B04BB22E236E} perhaps. As I said before there are lots of DLLs involved in SSIS not just one, so use a proper install.

What you are asking for a not a supported scenario, you may be violating your license agreements if not careful, and at any rate will be a very complicated task to try and reverse engineer the requirements, and very slow if you cannot track a missing COM DLL down yourself.

If you think this is wrong, post feedback to MS (http://connect.microsoft.com) telling them why you think you should have some redistribuatable support, but in the mean-time you will need to run one of the MS installs to get this to work.

Why do you not want to use a MS install?