Jane's Technical Stuff
Archives
May 2002March 2003
May 2003
July 2003
August 2003
June 2005
July 2005
August 2005
December 2005
January 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
December 2007
January 2008
February 2008
March 2008
April 2008
May 2008
June 2008
July 2008
August 2008
September 2008
October 2008
Thursday, July 27, 2006
DTS and Excel issue with different data types
We encountered this known issue with DTS and Excel today. Instead of importing the model for cars with purely numeric models, like the 206, it imported NULLs. After a lot of searching, we found some really useful information and with this, and a bit of investigation I worked it out.
As they say, select to Disconnected Edit the DTS package, selecct the Excel Connection and expand the OLD DB properties. In there is a property "Extender Properties". Select this, and in the right hand pane update the Value to have IMEX=1.
As they say, select to Disconnected Edit the DTS package, selecct the Excel Connection and expand the OLD DB properties. In there is a property "Extender Properties". Select this, and in the right hand pane update the Value to have IMEX=1.
Labels: Development
// posted by Jane @ 11:43 AM 
Comments:
Thursday, July 20, 2006
SQL Server 2000 - find a string in a stored procedure
I've been debugging and unit testing some involved stored procedures, and I've left a trail of PRINT statements all over the place. I was having real difficulties tracking the last 2 down, so I wrote the following SQL to help me track them down.
SELECT o.name
FROM syscomments c
INNER JOIN sysobjects o
ON c.id = o.id
WHERE o.xtype = 'P'
AND text LIKE 'PRINT @'
SELECT o.name
FROM syscomments c
INNER JOIN sysobjects o
ON c.id = o.id
WHERE o.xtype = 'P'
AND text LIKE 'PRINT @'
Labels: Development, SQLServer, SQLServer2000
// posted by Jane @ 10:28 AM
Comments:
Wednesday, July 19, 2006
Getting Things Done
Spotted this via Tom's blog. Not sure if a flag would work for me as there are so many ways that I'm interrupted, but I like the concept :-)
// posted by Jane @ 3:21 PM
Comments:
Tuesday, July 18, 2006
Code reviews...
I do code reviews on my team's code every few months or when we have a major deliverable, and I usually find small odds and ends regarding to coding standards, or offer some advice on how it could be done better. My .Net advice is not up to the standard I'd like it to be yet, but I'm trying to work on that.
Thankfully, no-one has ever presented me with code like this.
Thankfully, no-one has ever presented me with code like this.
Labels: Development
// posted by Jane @ 1:58 PM
Comments:
Thursday, July 13, 2006
Battling with TableAdapters and Connection Strings
I've been looking at a way, other than changing the App.Config settings, of ensuring that all my tableadapters have the correct connection string. The assembly I am writing will be deployed as a COM dll through a VB application, and so changing the App.Config settings isn't really an option.
I found this useful sounding advice
Modifying the Accessibility of the TableAdapter and its Connection
The default accessibility of TableAdapters is public. You can restrict access to your TableAdapters from outside components by changing the Modifier property for the TableAdapter in the Dataset Designer.
Similarly, you can share the connection that a TableAdapter uses by changing the ConnectionModifier property of the TableAdapter in the Dataset Designer. By default, the connection modifier is set to Friend. It is a good practice to leave this modifier set to the default to prevent unknown objects from using your connection (and possibly your credentials) to access the database. on MSDN : TableAdapters in Visual Studio 2005.
So, I've created a new Helper class to set up the connections for all of these objects.
public class AdapterManager
{
private static SqlConnection GetConnection()
{
AppSettings MyApp = AppSettings.GetInstance();
SqlConnection myConnection = new SqlConnection(MyApp.ConnectionString);
return myConnection;
}
public static void SetPersonConnectionString(PersonTableAdapter ta)
{
ta.Connection = GetConnection();
}
}
then from within the _Load event of my form, I call
AdapterManager.SetPersonConnectionString(this.PersonTableAdapter);
and this seems to work like a charm. I'm sure there are better ways of doing this. If you know of one, then please let me know.
I found this useful sounding advice
Modifying the Accessibility of the TableAdapter and its Connection
The default accessibility of TableAdapters is public. You can restrict access to your TableAdapters from outside components by changing the Modifier property for the TableAdapter in the Dataset Designer.
Similarly, you can share the connection that a TableAdapter uses by changing the ConnectionModifier property of the TableAdapter in the Dataset Designer. By default, the connection modifier is set to Friend. It is a good practice to leave this modifier set to the default to prevent unknown objects from using your connection (and possibly your credentials) to access the database. on MSDN : TableAdapters in Visual Studio 2005.
So, I've created a new Helper class to set up the connections for all of these objects.
public class AdapterManager
{
private static SqlConnection GetConnection()
{
AppSettings MyApp = AppSettings.GetInstance();
SqlConnection myConnection = new SqlConnection(MyApp.ConnectionString);
return myConnection;
}
public static void SetPersonConnectionString(PersonTableAdapter ta)
{
ta.Connection = GetConnection();
}
}
then from within the _Load event of my form, I call
AdapterManager.SetPersonConnectionString(this.PersonTableAdapter);
and this seems to work like a charm. I'm sure there are better ways of doing this. If you know of one, then please let me know.
Labels: Development
// posted by Jane @ 1:59 PM
Comments:
SQL Server Collations
Tony Rogerson has an interesting article about Collations in SQL Server, examples and restrictions, explaining how and why they happen and what they mean. This is a problem I've faced again and again, especially when bringing back customer's data. One of the slight annoyances I have with Visual Studio 2003 is that when generating create scripts for tables it puts the collation into the table creation scripts, so I often do a search of the database for COLLATE and remove them, taking the code from
CREATE TABLE [dbo].[Person]
( [PersonID] [int] NOT NULL ,
[Name] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
to
CREATE TABLE [dbo].[Person]
( [PersonID] [int] NOT NULL ,
[Name] [varchar] (10) NOT NULL
) ON [PRIMARY]
We let our databases take the default collation from the server, rather than forcing our own. This can still lead to issues and I've had the
Msg 468, Level 16, State 9, Line 1
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.
error when doing casts rather than joins. To get around this I've used COLLATE database_default to fix it, for example
SELECT @strName = "THE" + ISNULL(CAST(@intID AS VARCHAR(10)),'NULL') COLLATE database_default
CREATE TABLE [dbo].[Person]
( [PersonID] [int] NOT NULL ,
[Name] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
to
CREATE TABLE [dbo].[Person]
( [PersonID] [int] NOT NULL ,
[Name] [varchar] (10) NOT NULL
) ON [PRIMARY]
We let our databases take the default collation from the server, rather than forcing our own. This can still lead to issues and I've had the
Msg 468, Level 16, State 9, Line 1
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.
error when doing casts rather than joins. To get around this I've used COLLATE database_default to fix it, for example
SELECT @strName = "THE" + ISNULL(CAST(@intID AS VARCHAR(10)),'NULL') COLLATE database_default
Labels: Development, SQLServer, SQLServer2000
// posted by Jane @ 11:09 AM
Comments:
