Build a Connection String Programmatically

I'm getting really frustrated with using the web.config to store all database information. It seems like you really don't have any real flexibilty when moving an application from server to server (between dev, test and prod). Seems like you have to create separate config files when you move it every time. Seems like you always run into a risk of over writing one of them if you are not careful.

Is there a good way to design the connection object to be able to tell which server it is on and determine if it's a test, dev or prod type of connection. I have used a basic three connection strings that it will compare the computer name with the key. But again, the connection string is hard coded in the web config. At least this way I am able to move all code together without the worry of overwritting the config file. I just have to beleive there is a better way to build something like this. I've heard of setting up enviroment variables on the server, but not sure how safe that is.

I'm looking everywhere on the web, but get really lame examples. If anyone knows a good method or a sample I can check out, that would be great.

Thanks!

Im not sure about building a connectionstring dynamically, but the generally accepted way to solve this is with a User.config file for you and the host. You keep all the normal crap in the webconfig, and then your connection string and any other variables in the userconfig. Here is a good example.

http://blogs.msdn.com/rprabhu/articles/433979.aspx

Build 9.0.3050

I downloaded SP2 on March 10, 2007, and applied it. The system to which I applied it reports 9.0.3050 as the version number, not 9.0.3042. Is this due to the post-SP2 fix?My system, which was updated with the bugged SP2, and then the subsequent hotfix, also reports 9.0.3050. So that seems like a reasonable assumption.|||

refer this link for latest fixes in SQL Server 2005...

http://support.microsoft.com/kb/933508

SP2 Release version was --9.00.3042.00

Latest version with this fix(Mar 05) is -- 9.00.3050.00

Madhu

Build = Error: Value cannot be null.

Hello Everyone,
I am having a heck of a time with this report. I have five datasets,
all interacting with stored procedures in SQL Server 2005, all using
the same parameter. The datasets run fine individually. I can run the
report fine. When I try to build or deploy the report, I get this:
-- Build started: Project: JobApplication, Configuration: Debug
--
Error: Value cannot be null.
========== Build: 0 succeeded or up-to-date, 1 failed, 0 skipped
==========
Not very descriptive unfortunately :( I'm not even sure where to
look.
Any ideas? Any help would be greatly appreciated.
Have a great day,
RyanDid one of your datasets change 'mysteriously' to text, rather than Stored
Procedure? I think I have seen the error before, but I can't remember if it
is related to this problem or not.
"Ryan" wrote:
> Hello Everyone,
> I am having a heck of a time with this report. I have five datasets,
> all interacting with stored procedures in SQL Server 2005, all using
> the same parameter. The datasets run fine individually. I can run the
> report fine. When I try to build or deploy the report, I get this:
> -- Build started: Project: JobApplication, Configuration: Debug
> --
> Error: Value cannot be null.
> ========== Build: 0 succeeded or up-to-date, 1 failed, 0 skipped
> ==========> Not very descriptive unfortunately :( I'm not even sure where to
> look.
> Any ideas? Any help would be greatly appreciated.
> Have a great day,
> Ryan
>|||Just a guess, you have refered a value which is a PK and in the report
properties you have clicked "Allow Null"
Amarnath, MCTS
"Ryan" wrote:
> Hello Everyone,
> I am having a heck of a time with this report. I have five datasets,
> all interacting with stored procedures in SQL Server 2005, all using
> the same parameter. The datasets run fine individually. I can run the
> report fine. When I try to build or deploy the report, I get this:
> -- Build started: Project: JobApplication, Configuration: Debug
> --
> Error: Value cannot be null.
> ========== Build: 0 succeeded or up-to-date, 1 failed, 0 skipped
> ==========> Not very descriptive unfortunately :( I'm not even sure where to
> look.
> Any ideas? Any help would be greatly appreciated.
> Have a great day,
> Ryan
>|||Thank you both for answering . . . in case anybody is interested:
I tried both stored procedure and text type datasets . . . both with
the same result. I'll check to make sure when it is set as a stored
procedure, it stays that way :)
Hmmm, I assume the Allow Null is for a parameter? If so, there is one
the one parameter and the "Allow null value" is unchecked.
Again, thank you for the responses; I'll give them a whirl. In the
mean time, I found that uploading the report manually works fine.
Ryan
On Mar 23, 12:56 am, Amarnath <Amarn...@.discussions.microsoft.com>
wrote:
> Just a guess, you have refered a value which is a PK and in the report
> properties you have clicked "Allow Null"
> Amarnath, MCTS
> "Ryan" wrote:
> > Hello Everyone,
> > I am having a heck of a time with this report. I have five datasets,
> > all interacting with stored procedures in SQL Server 2005, all using
> > the same parameter. The datasets run fine individually. I can run the
> > report fine. When I try to build or deploy the report, I get this:
> > -- Build started: Project: JobApplication, Configuration: Debug
> > --
> > Error: Value cannot be null.
> > ========== Build: 0 succeeded or up-to-date, 1 failed, 0 skipped
> > ==========> > Not very descriptive unfortunately :( I'm not even sure where to
> > look.
> > Any ideas? Any help would be greatly appreciated.
> > Have a great day,
> > Ryan

Bugs without KB articles in Cumulative Update 5 (build 3215)

There are still a handful of bugs listed as fixed in build 3215 that don't
have KB articles. Will a KB be posted soon and how would I know in advance if
I am affected by these bugs without a KB to read?
The particular bug I'm interested in is 50002184: "You receive incorrect
results when you run a query in Microsoft SQL Server 2005."
Searches in these forums and elsewhere have come up empty for me. Based
purely on that brief bug description I have no way of knowing whether I am
impacted or not. And with hotfixes the recommendation is to not apply unless
you are explicitly affected.
Got any advice?If you notice Update 4 KB was last updated on Jan 15th, way after it ewas
released. You might get some answers if you subscribe to KBALERTZ under SQL.
Chris
"ahallock" <ahallock@.discussions.microsoft.com> wrote in message
news:24EF3CA9-52C9-4C91-AE60-C45B17DA1880@.microsoft.com...
> There are still a handful of bugs listed as fixed in build 3215 that don't
> have KB articles. Will a KB be posted soon and how would I know in advance
> if
> I am affected by these bugs without a KB to read?
> The particular bug I'm interested in is 50002184: "You receive incorrect
> results when you run a query in Microsoft SQL Server 2005."
> Searches in these forums and elsewhere have come up empty for me. Based
> purely on that brief bug description I have no way of knowing whether I am
> impacted or not. And with hotfixes the recommendation is to not apply
> unless
> you are explicitly affected.
> Got any advice?|||I don't know about this specific bug. But not every bug or bug fix will have
a public KB article.
Linchi
"ahallock" wrote:
> There are still a handful of bugs listed as fixed in build 3215 that don't
> have KB articles. Will a KB be posted soon and how would I know in advance if
> I am affected by these bugs without a KB to read?
> The particular bug I'm interested in is 50002184: "You receive incorrect
> results when you run a query in Microsoft SQL Server 2005."
> Searches in these forums and elsewhere have come up empty for me. Based
> purely on that brief bug description I have no way of knowing whether I am
> impacted or not. And with hotfixes the recommendation is to not apply unless
> you are explicitly affected.
> Got any advice?|||Also look in this blog http://blogs.technet.com/hot/default.aspx
Chris
"Chris Wood" <anonymous@.microsoft.com> wrote in message
news:uq2MuesXIHA.4896@.TK2MSFTNGP06.phx.gbl...
> If you notice Update 4 KB was last updated on Jan 15th, way after it ewas
> released. You might get some answers if you subscribe to KBALERTZ under
> SQL.
> Chris
> "ahallock" <ahallock@.discussions.microsoft.com> wrote in message
> news:24EF3CA9-52C9-4C91-AE60-C45B17DA1880@.microsoft.com...
>> There are still a handful of bugs listed as fixed in build 3215 that
>> don't
>> have KB articles. Will a KB be posted soon and how would I know in
>> advance if
>> I am affected by these bugs without a KB to read?
>> The particular bug I'm interested in is 50002184: "You receive incorrect
>> results when you run a query in Microsoft SQL Server 2005."
>> Searches in these forums and elsewhere have come up empty for me. Based
>> purely on that brief bug description I have no way of knowing whether I
>> am
>> impacted or not. And with hotfixes the recommendation is to not apply
>> unless
>> you are explicitly affected.
>> Got any advice?
>|||That looks like an excellent source of news that I didn't know about. Thank
you for bringing it to my attention!
"Chris Wood" wrote:
> Also look in this blog http://blogs.technet.com/hot/default.aspx

Bug Report: The sorting function that Report Builder have some problem.

Dear all,

Did you use the Report Builder to build report before?

When I create a report using Report Builder, it has a sorting function on the columns. (An arrow icon)

When I hit the sorting icon, the sorting function can only apply to the first column; when i hit the remaining columns, the table have no response to the sorting function.

I've tried a lots of times.

Did someone encounter the same problem?

ThankU.

Yes, am also having same problem.
but inerestingly it works sometimes and i dont see any pattern
BTW am using sql server 2005 standard edition
|||The sorting in a table is per group. It sounds like you have a table that consists of individual groups on each field, instead of a single group containing all the fields you want to display. You can tell which you have by looking at the gray group tabs when the table is selected in design view.|||Hi Bob
in my report, all fields are in separate groups . when i run report (using report builder) sort works only on first column in the report. other columns dont respond to my clicks on sort icon !!

Bug Report: The sorting function that Report Builder have some problem.

Dear all,

Did you use the Report Builder to build report before?

When I create a report using Report Builder, it has a sorting function on the columns. (An arrow icon)

When I hit the sorting icon, the sorting function can only apply to the first column; when i hit the remaining columns, the table have no response to the sorting function.

I've tried a lots of times.

Did someone encounter the same problem?

ThankU.

Yes, am also having same problem.
but inerestingly it works sometimes and i dont see any pattern
BTW am using sql server 2005 standard edition

|||The sorting in a table is per group. It sounds like you have a table that consists of individual groups on each field, instead of a single group containing all the fields you want to display. You can tell which you have by looking at the gray group tabs when the table is selected in design view.|||Hi Bob
in my report, all fields are in separate groups . when i run report (using report builder) sort works only on first column in the report. other columns dont respond to my clicks on sort icon !!

BUG in SSIS OLE DB Source Build Query

Can anyone help me with this ?

I am trying to extract data from oracle 9i server and
pushing it onto the SQL Server 2005 using Data Flow Task.
Details for OLE DB Source are :

OLE DB Source Editor Details:

OLE DB Connection Manager - Oracle Source

Data Access Mode - SQL Command

SQL Command Text -

SELECT EMPNO, EMPNAME, JOB, HIREDATE
FROM EMP_DETAILS
WHERE (HIREDATE > TRUNC(SYSDATE) - 1)

SSIS parses this query succesfully but when i build the query it shows query

SELECT EMPNO, EMPNAME, JOB, HIREDATE
FROM EMP_DETAILS
WHERE (HIREDATE > TRUNC("SYSDATE") - 1)

Please note :- SYSDATE IN " "

This query returns no Result Set.

Try using GETDATE() instead of SYSDATE, that is the Oracle function for returning the current date.|||Hi there,

i don't find any such function in oracle, getdate() is there in MS SQL Server.

Thanks.
|||

I don't think there is any. AFAIK Sysdate is the equivalent to GetDate() in Oracle. I am having the exact same problem as you using an OLE DB Source to access an Oracle DB - and it annoys me to no end that I cannot get the system date for comparison. If anyone else has a solution for this it would be much appreciated.

|||I am having the same issue in a similar situation... I change my query in the .xsd dataset to include a clause that references SYSDATE, but when VS tries to rebuild the code it ALWAYS adds the double-quotes around sysdate and it treats it as an identifier, which of course does not exist.

SELECT HZ.LOCATION_ID, HZ.DESCRIPTION, HZ.ADDRESS1, HZ.ADDRESS2
FROM AR.HZ_LOCATIONS HZ INNER JOIN
CSI.CSI_A_LOCATIONS CSI ON HZ.LOCATION_ID = CSI.LOCATION_ID
WHERE (NVL(TRUNC(CSI.ACTIVE_END_DATE), '01-JAN-4000') > TRUNC(SYSDATE))

Always becomes

SELECT HZ.LOCATION_ID, HZ.DESCRIPTION, HZ.ADDRESS1, HZ.ADDRESS2
FROM AR.HZ_LOCATIONS HZ INNER JOIN
CSI.CSI_A_LOCATIONS CSI ON HZ.LOCATION_ID = CSI.LOCATION_ID
WHERE (NVL(TRUNC(CSI.ACTIVE_END_DATE), '01-JAN-4000') > TRUNC("SYSDATE"))

Has anyone found a way to prevent VS from doing this?
|||Hi Anthony,

There is only one way around, Don't build the query !
In my application i am just parsing the query n its working fine.
you try to build the query n query builder will make sysdate famous by quoting it.

BUG in SSIS OLE DB Source Build Query

Can anyone help me with this ?

I am trying to extract data from oracle 9i server and
pushing it onto the SQL Server 2005 using Data Flow Task.
Details for OLE DB Source are :

OLE DB Source Editor Details:

OLE DB Connection Manager - Oracle Source

Data Access Mode - SQL Command

SQL Command Text -

SELECT EMPNO, EMPNAME, JOB, HIREDATE
FROM EMP_DETAILS
WHERE (HIREDATE > TRUNC(SYSDATE) - 1)

SSIS parses this query succesfully but when i build the query it shows query

SELECT EMPNO, EMPNAME, JOB, HIREDATE
FROM EMP_DETAILS
WHERE (HIREDATE > TRUNC("SYSDATE") - 1)

Please note :- SYSDATE IN " "

This query returns no Result Set.

Try using GETDATE() instead of SYSDATE, that is the Oracle function for returning the current date.|||Hi there,

i don't find any such function in oracle, getdate() is there in MS SQL Server.

Thanks.
|||

I don't think there is any. AFAIK Sysdate is the equivalent to GetDate() in Oracle. I am having the exact same problem as you using an OLE DB Source to access an Oracle DB - and it annoys me to no end that I cannot get the system date for comparison. If anyone else has a solution for this it would be much appreciated.

|||I am having the same issue in a similar situation... I change my query in the .xsd dataset to include a clause that references SYSDATE, but when VS tries to rebuild the code it ALWAYS adds the double-quotes around sysdate and it treats it as an identifier, which of course does not exist.

SELECT HZ.LOCATION_ID, HZ.DESCRIPTION, HZ.ADDRESS1, HZ.ADDRESS2
FROM AR.HZ_LOCATIONS HZ INNER JOIN
CSI.CSI_A_LOCATIONS CSI ON HZ.LOCATION_ID = CSI.LOCATION_ID
WHERE (NVL(TRUNC(CSI.ACTIVE_END_DATE), '01-JAN-4000') > TRUNC(SYSDATE))

Always becomes

SELECT HZ.LOCATION_ID, HZ.DESCRIPTION, HZ.ADDRESS1, HZ.ADDRESS2
FROM AR.HZ_LOCATIONS HZ INNER JOIN
CSI.CSI_A_LOCATIONS CSI ON HZ.LOCATION_ID = CSI.LOCATION_ID
WHERE (NVL(TRUNC(CSI.ACTIVE_END_DATE), '01-JAN-4000') > TRUNC("SYSDATE"))

Has anyone found a way to prevent VS from doing this?
|||Hi Anthony,

There is only one way around, Don't build the query !
In my application i am just parsing the query n its working fine.
you try to build the query n query builder will make sysdate famous by quoting it.

BUG in SSIS OLE DB Source Build Query

Can anyone help me with this ?

I am trying to extract data from oracle 9i server and
pushing it onto the SQL Server 2005 using Data Flow Task.
Details for OLE DB Source are :

OLE DB Source Editor Details:

OLE DB Connection Manager - Oracle Source

Data Access Mode - SQL Command

SQL Command Text -

SELECT EMPNO, EMPNAME, JOB, HIREDATE
FROM EMP_DETAILS
WHERE (HIREDATE > TRUNC(SYSDATE) - 1)

SSIS parses this query succesfully but when i build the query it shows query

SELECT EMPNO, EMPNAME, JOB, HIREDATE
FROM EMP_DETAILS
WHERE (HIREDATE > TRUNC("SYSDATE") - 1)

Please note :- SYSDATE IN " "

This query returns no Result Set.

Try using GETDATE() instead of SYSDATE, that is the Oracle function for returning the current date.|||Hi there,

i don't find any such function in oracle, getdate() is there in MS SQL Server.

Thanks.
|||

I don't think there is any. AFAIK Sysdate is the equivalent to GetDate() in Oracle. I am having the exact same problem as you using an OLE DB Source to access an Oracle DB - and it annoys me to no end that I cannot get the system date for comparison. If anyone else has a solution for this it would be much appreciated.

|||I am having the same issue in a similar situation... I change my query in the .xsd dataset to include a clause that references SYSDATE, but when VS tries to rebuild the code it ALWAYS adds the double-quotes around sysdate and it treats it as an identifier, which of course does not exist.

SELECT HZ.LOCATION_ID, HZ.DESCRIPTION, HZ.ADDRESS1, HZ.ADDRESS2
FROM AR.HZ_LOCATIONS HZ INNER JOIN
CSI.CSI_A_LOCATIONS CSI ON HZ.LOCATION_ID = CSI.LOCATION_ID
WHERE (NVL(TRUNC(CSI.ACTIVE_END_DATE), '01-JAN-4000') > TRUNC(SYSDATE))

Always becomes

SELECT HZ.LOCATION_ID, HZ.DESCRIPTION, HZ.ADDRESS1, HZ.ADDRESS2
FROM AR.HZ_LOCATIONS HZ INNER JOIN
CSI.CSI_A_LOCATIONS CSI ON HZ.LOCATION_ID = CSI.LOCATION_ID
WHERE (NVL(TRUNC(CSI.ACTIVE_END_DATE), '01-JAN-4000') > TRUNC("SYSDATE"))

Has anyone found a way to prevent VS from doing this?
|||Hi Anthony,

There is only one way around, Don't build the query !
In my application i am just parsing the query n its working fine.
you try to build the query n query builder will make sysdate famous by quoting it.

BUG in SSIS OLE DB Source Build Query

Can anyone help me with this ?

I am trying to extract data from oracle 9i server and
pushing it onto the SQL Server 2005 using Data Flow Task.
Details for OLE DB Source are :

OLE DB Source Editor Details:

OLE DB Connection Manager - Oracle Source

Data Access Mode - SQL Command

SQL Command Text -

SELECT EMPNO, EMPNAME, JOB, HIREDATE
FROM EMP_DETAILS
WHERE (HIREDATE > TRUNC(SYSDATE) - 1)

SSIS parses this query succesfully but when i build the query it shows query

SELECT EMPNO, EMPNAME, JOB, HIREDATE
FROM EMP_DETAILS
WHERE (HIREDATE > TRUNC("SYSDATE") - 1)

Please note :- SYSDATE IN " "

This query returns no Result Set.

Try using GETDATE() instead of SYSDATE, that is the Oracle function for returning the current date.|||Hi there,

i don't find any such function in oracle, getdate() is there in MS SQL Server.

Thanks.
|||

I don't think there is any. AFAIK Sysdate is the equivalent to GetDate() in Oracle. I am having the exact same problem as you using an OLE DB Source to access an Oracle DB - and it annoys me to no end that I cannot get the system date for comparison. If anyone else has a solution for this it would be much appreciated.

|||I am having the same issue in a similar situation... I change my query in the .xsd dataset to include a clause that references SYSDATE, but when VS tries to rebuild the code it ALWAYS adds the double-quotes around sysdate and it treats it as an identifier, which of course does not exist.

SELECT HZ.LOCATION_ID, HZ.DESCRIPTION, HZ.ADDRESS1, HZ.ADDRESS2
FROM AR.HZ_LOCATIONS HZ INNER JOIN
CSI.CSI_A_LOCATIONS CSI ON HZ.LOCATION_ID = CSI.LOCATION_ID
WHERE (NVL(TRUNC(CSI.ACTIVE_END_DATE), '01-JAN-4000') > TRUNC(SYSDATE))

Always becomes

SELECT HZ.LOCATION_ID, HZ.DESCRIPTION, HZ.ADDRESS1, HZ.ADDRESS2
FROM AR.HZ_LOCATIONS HZ INNER JOIN
CSI.CSI_A_LOCATIONS CSI ON HZ.LOCATION_ID = CSI.LOCATION_ID
WHERE (NVL(TRUNC(CSI.ACTIVE_END_DATE), '01-JAN-4000') > TRUNC("SYSDATE"))

Has anyone found a way to prevent VS from doing this?
|||Hi Anthony,

There is only one way around, Don't build the query !
In my application i am just parsing the query n its working fine.
you try to build the query n query builder will make sysdate famous by quoting it.

Browser as Front-End

Hi!

I'm trying to build an app. for my parents' office. They have a small
office (5 PCs), and they currently work with a very old program that's
based on DOS. The program doesn't work well under XP, so they still use
Win 98. They had their network upgraded about a year ago. They had
Microsoft SBS 2000 installed, it includes SQL Server. I've written a
few DB's in Access before, and now I'm just learning how to use SQL.
Before I start work on their app., I have a question:

I know it's possible to create an interface that can be put on their
network and accessed via a browser. How is this done exactly?

It would be very beneficial for me, because it would mean that the new
app. would be platform independent, so that they can upgrade to XP
easily. It would also save us the cost of licensing front-end software
(e.g. Access) for each machine.

Any comments would be appreciated.

Noam Nelke, Israel.When you meant place the SQL Server on a network, you meant placing it
on a central server?

If that is the case, you can have one dedicated server for the SQL
Server and an application server where you can develop your web
application to create an Interface for this DB.
How to create the Interface?
- You can have a Web Server installed on the application server (IIS or
java based web servers) and then write your web code in any of the tons
of languages.

Hope that helps.|||Noam Nelke (NoamNelke@.gmail.com) writes:
> I'm trying to build an app. for my parents' office. They have a small
> office (5 PCs), and they currently work with a very old program that's
> based on DOS. The program doesn't work well under XP, so they still use
> Win 98. They had their network upgraded about a year ago. They had
> Microsoft SBS 2000 installed, it includes SQL Server. I've written a
> few DB's in Access before, and now I'm just learning how to use SQL.
> Before I start work on their app., I have a question:
> I know it's possible to create an interface that can be put on their
> network and accessed via a browser. How is this done exactly?

That's a question that does not have much to do with SQL Server. Most
people seem to use ASP or ASP .Net. There is also Cold Fusion out there,
and probably a few more possibilities.

> It would be very beneficial for me, because it would mean that the new
> app. would be platform independent, so that they can upgrade to XP
> easily. It would also save us the cost of licensing front-end software
> (e.g. Access) for each machine.

On the other hand, browser-based interfaces are often painful to use.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx