Problem with ListAvailableSQLServers in vb 6

Hello,
I have got a very strange problem...
In VB6, in the beginning of my application, I make a
ListAvailableSQLServers to see what MSDE servers are on the network for
my application.
The problem is that if a computer is disconnected from the network, but
has MSDE working (a laptop) then the ListAvailableSQLServers will return
no server, even if there is a local server.
Is there something to do to have it?
PS : In VB6, I use
dim ListSvr as collection
Set ListSvr = SQLDMO.ListAvailableSQLServers
and the count is 0
I have also tried with
Dim oServer As New SQLDMO.SQLServer2
Dim oNameList As SQLDMO.NameList
Set oNameList = oServer.Application.ListAvailableSQLServers '
and oNameList.Count is still 0
Can you please help me?
Thank you
Marc Allard
Allcomp
hi Marc,
"Allcomp" <marc@.nospam.allcomp.be> ha scritto nel messaggio
news:41d95753$0$329$ba620e4c@.news.skynet.be
> Hello,
> I have got a very strange problem...
> In VB6, in the beginning of my application, I make a
> ListAvailableSQLServers to see what MSDE servers are on the network
> for my application.
> The problem is that if a computer is disconnected from the network,
> but has MSDE working (a laptop) then the ListAvailableSQLServers will
> return no server, even if there is a local server.
> Is there something to do to have it?
> PS : In VB6, I use
> dim ListSvr as collection
> Set ListSvr = SQLDMO.ListAvailableSQLServers
> and the count is 0
> I have also tried with
> Dim oServer As New SQLDMO.SQLServer2
> Dim oNameList As SQLDMO.NameList
> Set oNameList = oServer.Application.ListAvailableSQLServers '
> and oNameList.Count is still 0
>
can you please verify your local MSDE instance has network protocols enabled
via Server Network Utility (svrnetcn.exe)?
if network protocols are disabled (default for MSDE installation), the
relative instance will not be enlisted in the broadcast call for server
enumeration..
as regard ListAvailableServer method provided by SQL-DMO object model, I've
found during my (little) experience, this sort of rules:
ListAvailableServer uses ODBC function SQLBrowseConnect() provided by ODBC
libraries installed by Mdac;
this is a mechanism working in broadcast calls, which result never are
conclusive and consistent, becouse results are influenced of various
servers's answer states, answer time, etc.
Until Mdac 2.5, SQLBrowseConnect function works based on a NetBIOS
broadcast, on which SQL Servers respond (Default protocol for SQL Server
7.0), while in SQL Server 2000 the rules changed, because the default client
protocol changed to TCP/IP and now a UDP broadcast is used, beside a NetBIOS
broadcast, listening on port 1434:
which is using a UDP broadcast on port 1434, if instance do not listen or
not respond on time they will not be part of the enumeration.
Some basic rules for 7.0 are:
- SQL Servers have to be running on Windows NT or Windows 2000 and have to
listen on Named Pipes, that is why in 7.0 Windows 9x SQL Servers will never
show up, because they do not listen on Named Pipes.
- The SQL Server has to be running in order to respond on the broadcast.
There is a gray window of 15 minutes after shutdown, where a browse master
in the domain may respond on the broadcast and answer.
- If you have routers in your network, that do not pass on NetBIOS
broadcasts, this might limit your scope of the broadcast.
- Only servers within the same NT domain (or trust) will get enumerated.
In SQL Server 2000 using MDAC 2.6 this changes a little, because now the
default protocol has been changed to be TCP/IP sockets and instead of a
NetBIOS broadcast, they use a TCP UDP to detect the servers. The same logic
still applies roughly.
- SQL Server that are running
- SQL Server that listening on TCP/IP
- Running on Windows NT or Windows 2000 or Windows 9x
- If you use routers and these are configured not to pass UDP broadcasts,
only machines within the same subnet show up.
Upgrading to Service Pack 2 of SQL Server 2000 is required in order to have
..ListAvailableServer method to work properly, becouse precding release of
Sql-DMO Components of Sql Server 2000 present a bug in this area.
Courtesy of Mr. Gert E.R. Drapers
further Information at
http://sqldev.net/misc.htm
The Service Pack 3a introduced some new amenity in order to prevent MSDE
2000 to be hit by Internet worms like Slammer and Saphire virus and to
increase security, so that Microsoft decided to default for disabling
SuperSockets Network Protocols on new MSDE 2000 installation.
Instances of SQL Server 2000 SP3a or MSDE 2000 SP3a will stop listening on
UDP port 1434 when they are configured to not listen on any network
protocols. This will stop enlisting these servers.
The latest problem has been added by Windows XP service pack 2, which
implements a strong protection of the local computer closing all ports for
incoming and outgoing connections, requiring to manually open the desired IP
port in order to allow external remote connections
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Hello,
My enabled networks protocols are Named pipes and Tcp/IP
With EnumSQLSvr.exe, it doesn't work (when I remove the network cable, I
have no server). I have exactly the same error with Osql -L
I use WinXP SP2 without firewall for my tests.
With SQL Server 2000, a Win98 computer is in the list
I have also seen something strange...
I make the ListAvailableSQLServers
If I have a return count of 0, then I use
the object
SQLDMO.SQLServer2
Set oServer = New SQLDMO.SQLServer2
Set ListSvr = oServer.ListInstalledInstances
will return the local computer when no network is enabled.
Thank you
Marc Allard
Allcomp
Andrea Montanari wrote:
> hi Marc,
> "Allcomp" <marc@.nospam.allcomp.be> ha scritto nel messaggio
> news:41d95753$0$329$ba620e4c@.news.skynet.be
>
> can you please verify your local MSDE instance has network protocols enabled
> via Server Network Utility (svrnetcn.exe)?
> if network protocols are disabled (default for MSDE installation), the
> relative instance will not be enlisted in the broadcast call for server
> enumeration..
> as regard ListAvailableServer method provided by SQL-DMO object model, I've
> found during my (little) experience, this sort of rules:
> ListAvailableServer uses ODBC function SQLBrowseConnect() provided by ODBC
> libraries installed by Mdac;
> this is a mechanism working in broadcast calls, which result never are
> conclusive and consistent, becouse results are influenced of various
> servers's answer states, answer time, etc.
> Until Mdac 2.5, SQLBrowseConnect function works based on a NetBIOS
> broadcast, on which SQL Servers respond (Default protocol for SQL Server
> 7.0), while in SQL Server 2000 the rules changed, because the default client
> protocol changed to TCP/IP and now a UDP broadcast is used, beside a NetBIOS
> broadcast, listening on port 1434:
> which is using a UDP broadcast on port 1434, if instance do not listen or
> not respond on time they will not be part of the enumeration.
> Some basic rules for 7.0 are:
> - SQL Servers have to be running on Windows NT or Windows 2000 and have to
> listen on Named Pipes, that is why in 7.0 Windows 9x SQL Servers will never
> show up, because they do not listen on Named Pipes.
> - The SQL Server has to be running in order to respond on the broadcast.
> There is a gray window of 15 minutes after shutdown, where a browse master
> in the domain may respond on the broadcast and answer.
> - If you have routers in your network, that do not pass on NetBIOS
> broadcasts, this might limit your scope of the broadcast.
> - Only servers within the same NT domain (or trust) will get enumerated.
> In SQL Server 2000 using MDAC 2.6 this changes a little, because now the
> default protocol has been changed to be TCP/IP sockets and instead of a
> NetBIOS broadcast, they use a TCP UDP to detect the servers. The same logic
> still applies roughly.
> - SQL Server that are running
> - SQL Server that listening on TCP/IP
> - Running on Windows NT or Windows 2000 or Windows 9x
> - If you use routers and these are configured not to pass UDP broadcasts,
> only machines within the same subnet show up.
> Upgrading to Service Pack 2 of SQL Server 2000 is required in order to have
> .ListAvailableServer method to work properly, becouse precding release of
> Sql-DMO Components of Sql Server 2000 present a bug in this area.
> Courtesy of Mr. Gert E.R. Drapers
> further Information at
> http://sqldev.net/misc.htm
> The Service Pack 3a introduced some new amenity in order to prevent MSDE
> 2000 to be hit by Internet worms like Slammer and Saphire virus and to
> increase security, so that Microsoft decided to default for disabling
> SuperSockets Network Protocols on new MSDE 2000 installation.
> Instances of SQL Server 2000 SP3a or MSDE 2000 SP3a will stop listening on
> UDP port 1434 when they are configured to not listen on any network
> protocols. This will stop enlisting these servers.
> The latest problem has been added by Windows XP service pack 2, which
> implements a strong protection of the local computer closing all ports for
> incoming and outgoing connections, requiring to manually open the desired IP
> port in order to allow external remote connections
|||If you have no network this is expected, since the enumeration is based on a
UDP (TCP) broadcast. Since this is a broadcast it normally only sees
computers in the same subnet, since most routers are configured not to pass
on UDP broadcast requests.
Besides that there is a response time issue. After the request is issued the
client only waits for a certain period of time on UDP replies from the
servers that received the UDP broadcast request.
On the local machine the API does a Registry scan, which is why local
services should show up when no network is enabled. MSDE has different
Registry entries that is why these do not show.
If you want to use the same API as SQL-DMO you need to use the
ListSQLSrv.exe tool http://sqldev.net/misc/ListSQLSvr.htm EnumSQLSvr, uses
the OLE-DB based API, which theoretically should provide the same results,
but you never know.
On Win98 I think they only enumerate local instances as far as I know.
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2004 All rights reserved.
"Allcomp" <marc@.nospam.allcomp.be> wrote in message
news:41da4402$0$316$ba620e4c@.news.skynet.be...[vbcol=seagreen]
> Hello,
> My enabled networks protocols are Named pipes and Tcp/IP
> With EnumSQLSvr.exe, it doesn't work (when I remove the network cable, I
> have no server). I have exactly the same error with Osql -L
> I use WinXP SP2 without firewall for my tests.
> With SQL Server 2000, a Win98 computer is in the list
> I have also seen something strange...
> I make the ListAvailableSQLServers
> If I have a return count of 0, then I use
> the object
> SQLDMO.SQLServer2
> Set oServer = New SQLDMO.SQLServer2
> Set ListSvr = oServer.ListInstalledInstances
> will return the local computer when no network is enabled.
>
> Thank you
> Marc Allard
> Allcomp
>
>
>
>
> Andrea Montanari wrote:
|||Hello,
In fact, I was thinking that when I remove the computer from the
network, it is able to see itself (by UDP) you can answer to yourself
(but it seem that it is not possible)?
So I think I will have to list all the local instances if I can see no
network.
PS : Win 98 can see any server (local or networked).
Thank you
Marc Allard
Allcomp
Gert E.R. Drapers wrote:
> If you have no network this is expected, since the enumeration is based on a
> UDP (TCP) broadcast. Since this is a broadcast it normally only sees
> computers in the same subnet, since most routers are configured not to pass
> on UDP broadcast requests.
> Besides that there is a response time issue. After the request is issued the
> client only waits for a certain period of time on UDP replies from the
> servers that received the UDP broadcast request.
> On the local machine the API does a Registry scan, which is why local
> services should show up when no network is enabled. MSDE has different
> Registry entries that is why these do not show.
> If you want to use the same API as SQL-DMO you need to use the
> ListSQLSrv.exe tool http://sqldev.net/misc/ListSQLSvr.htm EnumSQLSvr, uses
> the OLE-DB based API, which theoretically should provide the same results,
> but you never know.
> On Win98 I think they only enumerate local instances as far as I know.
> GertD@.SQLDev.Net
> Please reply only to the newsgroups.
> This posting is provided "AS IS" with no warranties, and confers no rights.
> You assume all risk for your use.
> Copyright SQLDev.Net 1991-2004 All rights reserved.
> "Allcomp" <marc@.nospam.allcomp.be> wrote in message
> news:41da4402$0$316$ba620e4c@.news.skynet.be...
>
>
|||If you use SQLBrowseConnect directly (not SQL-DMO) you can point it at a
single machine, which would give you this.
If you try the ListSQLSvr.exe tool with -S <your machine name> -X it will
give you a listing of your local servers only.
If you install a loopback adapter the UDP broadcast will work locally.
Good to know that Win98 does local and remote, I never touch that OS, since
NT was introduced I left DOS behind me.
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2004 All rights reserved.
"Allcomp" <marc@.nospam.allcomp.be> wrote in message
news:41da6e27$0$338$ba620e4c@.news.skynet.be...[vbcol=seagreen]
> Hello,
> In fact, I was thinking that when I remove the computer from the network,
> it is able to see itself (by UDP) you can answer to yourself (but it seem
> that it is not possible)?
> So I think I will have to list all the local instances if I can see no
> network.
> PS : Win 98 can see any server (local or networked).
> Thank you
> Marc Allard
> Allcomp
> Gert E.R. Drapers wrote:
|||Hello,
Thank you for your help
In fact, I must use Win98 because a lot of my custommers use Win98 and
don't want to change.
Marc Allard
Allcomp
Gert E.R. Drapers wrote:
> If you use SQLBrowseConnect directly (not SQL-DMO) you can point it at a
> single machine, which would give you this.
> If you try the ListSQLSvr.exe tool with -S <your machine name> -X it will
> give you a listing of your local servers only.
> If you install a loopback adapter the UDP broadcast will work locally.
> Good to know that Win98 does local and remote, I never touch that OS, since
> NT was introduced I left DOS behind me.
> GertD@.SQLDev.Net
> Please reply only to the newsgroups.
> This posting is provided "AS IS" with no warranties, and confers no rights.
> You assume all risk for your use.
> Copyright SQLDev.Net 1991-2004 All rights reserved.
> "Allcomp" <marc@.nospam.allcomp.be> wrote in message
> news:41da6e27$0$338$ba620e4c@.news.skynet.be...
>

Problem with export of a table to a text file

Dear MSSQL- experts,

I have a strange problem with SQLSERVER 2000.
I tried to export a table of about 40000 lines into a text file using
the Enterprise manager
export assitant. I was astonished to get an exported text file of about
400 MB instead 16 MB which is the normal size of that data.
By examining this file with a text editor I found that the file
included alongside the data of my table MANY zeros which caused the big
file size.

Does someone of you have an idea what could cause the export of
trillions zeros into my textfile and how to only export the significant
data of my table ?

Best regards,

DanielPosting the table definition (CREATE TABLE) would help a lot.

Roy Harvey
Beacon Falls, CT

On 24 Aug 2006 02:07:36 -0700, "Daniel Wetzler"
<Daniel.Wetzler@.sig.bizwrote:

Quote:

Originally Posted by

>Dear MSSQL- experts,
>
>I have a strange problem with SQLSERVER 2000.
>I tried to export a table of about 40000 lines into a text file using
>the Enterprise manager
>export assitant. I was astonished to get an exported text file of about
>400 MB instead 16 MB which is the normal size of that data.
>By examining this file with a text editor I found that the file
>included alongside the data of my table MANY zeros which caused the big
>file size.
>
>Does someone of you have an idea what could cause the export of
>trillions zeros into my textfile and how to only export the significant
>data of my table ?
>
>Best regards,
>
>Daniel

|||Hi Roy,

thanks for your answer.

Here's the table definition :

CREATE TABLE [dbo].[Variables] (
[ID] [varchar] (140) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Var_ref] [int] NOT NULL ,
[Group_Ref] [int] NULL ,
[Machines_Ref] [int] NULL ,
[TransfersID] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[Compatibility] [int] NULL ,
[CompatibilityL1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Category] [int] NULL ,
[CategoryL1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[AnalysisResponsible] [tinyint] NULL ,
[AnalysisPriority] [int] NULL ,
[AnalysisDelta] [int] NULL ,
[AnalysisDeltaValue] [varchar] (30) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Value] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Type] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Length] [int] NULL ,
[Address] [int] NULL ,
[Offset] [int] NULL ,
[Title1] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Title2] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Title3] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Title4] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AggregationTitle1] [nvarchar] (100) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[AggregationTitle2] [nvarchar] (100) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[AggregationTitle3] [nvarchar] (100) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[AggregationTitle4] [nvarchar] (100) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

Best regards,

Daniel

Roy Harvey schrieb:

Quote:

Originally Posted by

Posting the table definition (CREATE TABLE) would help a lot.
>
Roy Harvey
Beacon Falls, CT
>
On 24 Aug 2006 02:07:36 -0700, "Daniel Wetzler"
<Daniel.Wetzler@.sig.bizwrote:
>

Quote:

Originally Posted by

Dear MSSQL- experts,

I have a strange problem with SQLSERVER 2000.
I tried to export a table of about 40000 lines into a text file using
the Enterprise manager
export assitant. I was astonished to get an exported text file of about
400 MB instead 16 MB which is the normal size of that data.
By examining this file with a text editor I found that the file
included alongside the data of my table MANY zeros which caused the big
file size.

Does someone of you have an idea what could cause the export of
trillions zeros into my textfile and how to only export the significant
data of my table ?

Best regards,

Daniel

|||There were no surprises in the table definition. Storage of a row in
SQL Server, with all the varying length columns at their upper limit,
is around 2000 bytes, for whatever that is worth. Output of a maxed
out row to a unicode text file should be no more than a few hundred
bytes longer.

Did you save the DTS package that the export wizard created? Have you
tried running the wizard again? Did you specify fixed-width or
delimited? ASCII or UNICODE? (I expect UNICODE to handle the
NVARCHAR columns.)

I would run the wizard again, being sure to save the package. That at
least will give something to inspect on the code side if the output
file is still screwy.

Roy Harvey
Beacon Falls, CT

On 24 Aug 2006 06:11:12 -0700, "Daniel Wetzler"
<Daniel.Wetzler@.sig.bizwrote:

Quote:

Originally Posted by

>Hi Roy,
>
>thanks for your answer.
>
>Here's the table definition :
>
>
>CREATE TABLE [dbo].[Variables] (
>[ID] [varchar] (140) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
>[Var_ref] [int] NOT NULL ,
>[Group_Ref] [int] NULL ,
>[Machines_Ref] [int] NULL ,
>[TransfersID] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
>,
>[Compatibility] [int] NULL ,
>[CompatibilityL1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
>NULL ,
>[Category] [int] NULL ,
>[CategoryL1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
>,
>[AnalysisResponsible] [tinyint] NULL ,
>[AnalysisPriority] [int] NULL ,
>[AnalysisDelta] [int] NULL ,
>[AnalysisDeltaValue] [varchar] (30) COLLATE
>SQL_Latin1_General_CP1_CI_AS NULL ,
>[Value] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
>[Type] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
>[Length] [int] NULL ,
>[Address] [int] NULL ,
>[Offset] [int] NULL ,
>[Title1] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
>[Title2] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
>[Title3] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
>[Title4] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
>[AggregationTitle1] [nvarchar] (100) COLLATE
>SQL_Latin1_General_CP1_CI_AS NULL ,
>[AggregationTitle2] [nvarchar] (100) COLLATE
>SQL_Latin1_General_CP1_CI_AS NULL ,
>[AggregationTitle3] [nvarchar] (100) COLLATE
>SQL_Latin1_General_CP1_CI_AS NULL ,
>[AggregationTitle4] [nvarchar] (100) COLLATE
>SQL_Latin1_General_CP1_CI_AS NULL
>) ON [PRIMARY]
>GO
>
>Best regards,
>
>Daniel
>
>
>
>Roy Harvey schrieb:
>

Quote:

Originally Posted by

>Posting the table definition (CREATE TABLE) would help a lot.
>>
>Roy Harvey
>Beacon Falls, CT
>>
>On 24 Aug 2006 02:07:36 -0700, "Daniel Wetzler"
><Daniel.Wetzler@.sig.bizwrote:
>>

Quote:

Originally Posted by

>Dear MSSQL- experts,
>
>I have a strange problem with SQLSERVER 2000.
>I tried to export a table of about 40000 lines into a text file using
>the Enterprise manager
>export assitant. I was astonished to get an exported text file of about
>400 MB instead 16 MB which is the normal size of that data.
>By examining this file with a text editor I found that the file
>included alongside the data of my table MANY zeros which caused the big
>file size.
>
>Does someone of you have an idea what could cause the export of
>trillions zeros into my textfile and how to only export the significant
>data of my table ?
>
>Best regards,
>
>Daniel

problem with dynamically created RDL files and reports

Hello,

I have a strange problem with a dynamic created RDL resp. dynamic created report.

I have followed the programming tipps on the microsoft site to create programmatically a RDL file and this RDL file is accepted by the reporting server. But when the report is rendered out of that RDL some fields are somehow modified. In one field after the first line (which holds the correct value) all values in the following rows are cut after the fifth character. Other fields contain every now and then strange signs like a caret...

My report items in the RDL e.g. look like this:

- <TableCell>
- <ReportItems>
- <Textbox Name="Details_PBN">
- <Style>
<TextAlign>Left</TextAlign>
<FontFamily>Courier New</FontFamily>
- <BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
<Value>=Fields!PBN.Value</Value>
</Textbox>
</ReportItems>
</TableCell>

Do I have to do some datatype conversion in my RDL when I create it or how could this be possible?

Any help appreciated!!

I solved it myself. I write the solution down here, perhaps someone else has the same problem.

The data I that is in included in the report is queried from an oracle database. I also created the datasource for the report dynamically and there I used the provider "Oracle". This seemed to cause the problems, because after I changed it to OleDB and uses Provider="MSDAORA.1" as provider in the connection string, it worked like intended.

so... Code on, folks!!!

Problem with displaying workflow?

Hi,

I just recognized a strange displaying problem in one of my SSIS jobs.

I created job which contains a sequence container. Within the sequence container there is one "Execute SQL Task" and one "Foreach Loop Container". Within the "Foreach Loop Container" there are 4 task which are connected with precedence constraints.

Now my problem is that if I load the SSIS job and open the "sequence container" while the "Foreach Loop Container" is already open then the precedence constraint won't be displayed. If I close and open the "Foreach loop container" again then the precedence constraints will be displayed again.

My first assumption was that it might be a problem with the display drivers of the computer ....however the problem appears also on any other computer.

Does anyone know how to solve this display problem without closeing/reopening the container?

Thanks,

StSt

StSt,

You just have to lump it I'm afraid. It happens from time to time.

-Jamie

Problem with DateTime column

Hi All,
I faced this strange problem with sql server.
I have datetime column and storing value from asp.net. If the user doesnt enter any date, then we dont want any value to be stored in the database. But when we checked SQL Server, it default takes this value "01/01/1900". When users clicks on edit button, this value is fetched from database and stored in front end.
I dont want this value to be stored in database if i didnt provide value. But in 1 particular page, there are 5 different date columns. I am not sure about how to go ahead. I tried the following way, but again ended up with same problem.
insert into tablename......values (..., txtFromDate.text, txtToDate.text,...).
Since value is not there, '' is sent to database and above said value is stored. i want NULL to be stored in teh database when user didnt specify any value.
i didnt encounter this problem in Oracle.

appreciate your reply.

In the table design window for this table put a check in the column for allow nulls for the datetime field and make sure that you have not set the field with a default value. Also validate the textbox before data insertion or update like so.
if(txtFromDate.Text.Trim(' ') != "")
(
Place your database insertion code.
}
This will make sure that the textbox actually has something in it before you insert the value. Also you should have to convert the value of the textbox to a datetime before the data is inserted like this.
yadayadayada = Convert.ToDateTime(txtFromDate.Text);
I hope that this helps.

|||Check ifthisarticle helps.
|||

hi,
thanks for your reply. I thought of doing it like check the textbox value. But the problem is i am having 5 date control in that particular page.
So the condition for NULL checking, i need to try out with all probability. I believe, in that case, i should use around 25 if statement with each statement checking for all 5 condidtion.
Is there any other way to doing it.
rgds
ramu

|||you can check for nulls at the time of passing the value to the stored proc. you will have only 5 conditions. just before you pass the value to the parameter check for null. if you post some code we can help you out.|||You can just do iif(me.txtDate1.text.length=0,dbNull.value, me.txtDate1.text) if you are using parms. If not iif(me.txtDate1.text.length=0,"NULL", me.txtDate1.text)

Nick|||hi nick
dbnull.value does not seem to work. we need to use a special sqldatetime.null and import the sqltypes namespace for it.|||hi all,
once again, thanks for your reply.
since it was getting complicated and time was main constraint for me, so i tried it in the following way.
If the value is null, i let it get stored as 01/01/1900 itself.
but while fetching record for edit/view, i am checking those date columns for for above. If yes, i display null value, otherwise the stored value is displayed.
the above one temporarily solved my problem.
based on your solution, i think iif will solve my issue.
rgds
ramu|||

ndinakar wrote:

hi nick
dbnull.value does not seem to work. we need to use a special sqldatetime.null and import the sqltypes namespace for it.

Maybe its because of the way its being used? I always pass dbNull.value to my stored procs if the value is blank....

Nick|||It works for all columns except datetime columns. Leaving datetime columns empty will throw errors.

Problem with datasource on Report Builder

I've got a strange problem with ReportBuilder. I've created a DataSource name Config and a Model name Customers. We been using this model for month and everything was working fine. Now when we try to create a new report we cannot run it. When Report Builder is running i select the "Customer" model. Then i put some fields on the report. When i try to run it i've got an error saying that it cannot create the command for datasource1. What i don't understand is why the error occured in Datasource1 and not in my datasource config ?

When i open the Report Manger and click on the model "Customer" the datasource use by this model is Config. So the problem is not because they're is a mistake in the configuration. I've tried to rebuild my model and datasource then deployed them but i still have the same problem.

Any idea of what the problem is ? I can't figure outThis is the exact error i get:

An error has occurred during report processing. (rsProcessingAborted)
Cannot create command for datasource1. (rsErrorCreatingCommand)
An

attempt has been made to use a data extension 'SQL' that is not

registered for this report server. (rsSemanticQueryExtensionNotFound)|||

Can you please try restarting your IISservices (an iisreset on the Webserver hosting reporting services) to see if you get a different behavior?

Thank you.

Ramu_MSFT

This posting is provided "AS IS" with no warranties, and confers no rights.
.

|||Thanks ! I've tried to restart IIS and reboot our server but we still have the same problem.