Error with linked server after failover

Hi all,

The following query that uses a linked server is giving me the error message below after I initiate a failover (ALTER DATABASE Northwind SET PARTNER FAILOVER).I have SQL Server 2005 SP2.I think that without the service pack there is another error too.

The query is run from a database other than northwind of course.

select * from DualLink.northwind.dbo.Test1

Please note that:

without a failover itworks perfectly

it always work if I try to run it a second time - only the first time it fails.

it fails the first time for each of the open connections. A new connection that was open after the failover will work fine.

A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)

A transport-level error has occurred when sending the request to the server. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.)

The linked server is defined as

EXEC master.

dbo.sp_addlinkedserver

@.server = N'DualLink',

@.srvproduct=N'',

@.provider=N'SQLOLEDB',

@.catalog=N'northwind',

@.provstr=N'Server=(local);FailoverPartner=MyIPAddr;'

EXEC master.dbo.sp_addlinkedsrvlogin

@.rmtsrvname = 'DualLink',

@.useself = false,

@.locallogin = 'sa',

@.rmtuser = 'sa',

@.rmtpassword = 'MyPwd'

Thanks,

Avi

I am having the same problem except I don't have a linked server.

I am running SQL Server 2005 sp1 and Windows 2003 using clustering.

In addition to your symptoms I have noticed the following:

1. The error occurs when I make a query through Server Manager and when using a java app that queries a database using an ASP page. However, if I bring up our website as the first request after failover it works fine.

2. If I failover from Server1 to Server2 and don't make any requests while it is on Server2 and then failover back to Server1 I do not get the error. If I make any requests while it is on Server2 then the first request I make on Server1 after failing over will cause the error.

If you have solved the problem I would like to know how. If I find a solution I will post it here.

Blake

Error with linked server

I have a "linked server" configured in my SQL Server 2000 (SP4) server, which used to work correctly. However, I had to reinstall SQL Server (I backed up and restored the master/model/etc databases, so all my settings stayed the same). Since then, I've been getting this error when I try to use the linked server:
Invalid schema or catalog specified for provider 'MSDASQL'.
OLE DB error trace [Non-interface error: Invalid schema or catalog specified for the provider.].

The linked server is a FoxPro database, which does not use catalog or schema names. So, my select syntax looks like this:
SELECT * FROM Server...Table

SQL Server is aparently expecting something like this:
SELECT * FROM Server.Catalog.Schema.Table

Does anyone know how I can fix it so that it allows the "empty dot" method to work like it used to?'

Thanks!
JoshTry creating a new linkedserver to FP and see if it works. Also, try to update to the latest Mdac to get the latest providers.|||Yeah, I already tried to create a new linked server, and it didn't work. I checked my MDAC version, and it's the newest one (2.8 SP1).|||what foxpro driver are you using? a long time ago in a galaxy far away I had a world of trouble with fox pro linked servers until I upgraded from the 7 driver to the 8 driver.

I would drop the linked server.

install the current VFP driver. maybe reinstall MDAC.

recreate said linked server.

test the connection with OPENQUERY (but do not use it in production, pushes the processing to foxpro).

test using your empty dot method.

so glad I do not have to use fox pro anymore.|||It looks like I'm running version 6... I'll try upgrading it, thanks.

Oh yeah, I've already tried the OPENQUERY method, and it works. I could just change all of my code to use that, but that would be a lot of work.|||OPENQUERY is a pig. It will process the query in foxpro instead of sql server.|||what version of VFP are using. Be careful. Try this out in a test envrionment first because you may not be able to go back.|||Ok, so I wouldn't want to use it, even if it was easy to use. :)|||I actually don't know what version it is. We have a third-party application written in FoxPro that we pull data from to display on an intranet site. My FoxPro experience is limited to using that app, and linking SQL Server to it.

So, thanks for the warning. I'll have to do more research...|||Ok, I got it. It turns out that there is a checkbox in the linked server's Provider Options window called "Level zero only". If that is not checked, the empty-dot notation doesn't work. I checked it, and it works now.

I'm not sure why it broke, unless that option is a recent addition to the provider options - maybe someone installed some update around the same time we reinstalled SQL Server...

Anyway, thanks for the help!
Josh

error with linked server

Hi
when i try to run a query using linked servers, i get the following
error.

Server: Msg 125, Level 15, State 1, Line 1
Case expressions may only be nested to level 10.

I do have more than 10 case statements, it works fine when it is less
than 10. can anyone tell me if there is a way to have more than 10
case statements. thanks alot.

Jay

my query
Select category, val, Sum(QTY) As QTY , yr
From
(
Select val, QTY2 As QTY,
KEEP = Case
When code = '004' And ((YR > 2003) Or (YR = 2003 And MON > 12))
Then 'N'
When CODE = '005' And ((YR > 2003) Or (YR = 2003 And MON > 12))
Then 'N'
When CODE = '003' And ((YR > 2003) Or (YR = 2003 And MON > 12))
Then 'N'
When CODE = '017' And ((YR > 2003) Or (YR = 2003 And MON > 12))
Then 'N'
When CODE = '007' And ((YR > 2003) Or (YR = 2003 And MON > 12))
Then 'N'
When CODE = '008' And ((YR > 2003) Or (YR = 2003 And MON > 12))
Then 'N'
When CODE = '009' And ((YR > 2003) Or (YR = 2003 And MON > 11))
Then 'N'
When CODE = '010' And ((YR > 2003) Or (YR = 2003 And MON > 12))
Then 'N'
When CODE = '038' And ((YR > 2003) Or (YR = 2003 And MON > 12))
Then 'N'
When CODE = '032' And ((YR > 2003) Or (YR = 2003 And MON > 12))
Then 'N'
When CODE = '030' And ((YR > 2003) Or (YR = 2003 And MON > 12))
Then 'N'
When CODE = '018' And ((YR > 2003) Or (YR = 2003 And MON > 12))
Then 'N'
Else 'Y' End
From
amf a Join linkedserver.source.dbo.table2 b On a.COM = b.COM
Where CATEGORY In ('1') And CODE In ('001','003','004','005')
And b.YR Between 2003 And 2004 And b.MON <= 1
) x
Where KEEP = 'Y'
Group By CATEGORY, YR"Jay" <webforum2000@.yahoo.com> wrote in message
news:9594a55e.0404230831.50bcabe0@.posting.google.c om...
> Hi
> when i try to run a query using linked servers, i get the following
> error.
> Server: Msg 125, Level 15, State 1, Line 1
> Case expressions may only be nested to level 10.
> I do have more than 10 case statements, it works fine when it is less
> than 10. can anyone tell me if there is a way to have more than 10
> case statements. thanks alot.
> Jay
> my query
> Select category, val, Sum(QTY) As QTY , yr
> From
> (
> Select val, QTY2 As QTY,
> KEEP = Case
> When code = '004' And ((YR > 2003) Or (YR = 2003 And MON > 12))
> Then 'N'
> When CODE = '005' And ((YR > 2003) Or (YR = 2003 And MON > 12))
> Then 'N'
> When CODE = '003' And ((YR > 2003) Or (YR = 2003 And MON > 12))
> Then 'N'
> When CODE = '017' And ((YR > 2003) Or (YR = 2003 And MON > 12))
> Then 'N'
> When CODE = '007' And ((YR > 2003) Or (YR = 2003 And MON > 12))
> Then 'N'
> When CODE = '008' And ((YR > 2003) Or (YR = 2003 And MON > 12))
> Then 'N'
> When CODE = '009' And ((YR > 2003) Or (YR = 2003 And MON > 11))
> Then 'N'
> When CODE = '010' And ((YR > 2003) Or (YR = 2003 And MON > 12))
> Then 'N'
> When CODE = '038' And ((YR > 2003) Or (YR = 2003 And MON > 12))
> Then 'N'
> When CODE = '032' And ((YR > 2003) Or (YR = 2003 And MON > 12))
> Then 'N'
> When CODE = '030' And ((YR > 2003) Or (YR = 2003 And MON > 12))
> Then 'N'
> When CODE = '018' And ((YR > 2003) Or (YR = 2003 And MON > 12))
> Then 'N'
> Else 'Y' End
> From
> amf a Join linkedserver.source.dbo.table2 b On a.COM = b.COM
> Where CATEGORY In ('1') And CODE In ('001','003','004','005')
> And b.YR Between 2003 And 2004 And b.MON <= 1
> ) x
> Where KEEP = 'Y'
> Group By CATEGORY, YR

Since most of your conditions are the same, have you tried something like
this?

Select category, val, Sum(QTY) As QTY , yr
From
(
Select val, QTY2 As QTY,
KEEP = Case
When code in ('004', '005, '003', '017', /* etc. */)
And ((YR > 2003) Or (YR = 2003 And MON > 12))
Else 'Y' End
From
amf a Join linkedserver.source.dbo.table2 b On a.COM = b.COM
Where CATEGORY In ('1') And CODE In ('001','003','004','005')
And b.YR Between 2003 And 2004 And b.MON <= 1
) x

Simon|||
hi
thanks for the suggestion. I have a problem, this is one of the query
where it is all the same, in a few others it varies a lot. i want to
know if the limitaion exists in sql using linked servers ( since it
works fine if i dont use linked servers and have them in the same
server). i want to get aroud this, so that i dont have to change all my
existing queries, and would hamper my using linked server. thanks.. any
suggestion?

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Error with Jump to Javascript statement

I have the following statement being used in the Jump to command.
= "javascript:void(window.open('" &
http://domainname/ReferralForm.aspx?ReferralID= "& Fields!REF_ID.Value &
','_blank'))"
When I try to preview the report, I get the following error in the task list
The hyperlink expression for the textbox "REF_ID" contains a colon or a line
terminator. Colons and line terminators are not valid in expresssions.This should work:
="javascript:void(window.open('http://domainname/ReferralForm.aspx?ReferralID="
& Fields!REF_ID.Value & "','_blank'))"
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"scuba79" <scuba79@.discussions.microsoft.com> wrote in message
news:D6B0AAD2-44FB-48F4-9DD2-F598FFD7D52E@.microsoft.com...
>I have the following statement being used in the Jump to command.
> = "javascript:void(window.open('" &
> http://domainname/ReferralForm.aspx?ReferralID= "& Fields!REF_ID.Value &
> ','_blank'))"
>
> When I try to preview the report, I get the following error in the task
> list
> The hyperlink expression for the textbox "REF_ID" contains a colon or a
> line
> terminator. Colons and line terminators are not valid in expresssions.

Error with install of sept CTP Enterprise edition

Does SQL Server 2005 Sept CTP not support installation on XP? When I am installing, it gives me a warning that some things may not work with this OS, then when I get to the next screen I can't install anything.

Have you checked the pre-requisites such as .NET framework and IE version etc.
BTW what is the edition of XP & edition of CTP used?|||I am trying to install the september ctp enterprise edition on a machine with xp/sp2. I have reviewed the prereqs and I meet all of them. It says XP with sp2 and 512 ram; both are satisfied. I don't see any prereqs for .NET and I think that is because it installs .NET 2.0 when it installs. Any other ideas?|||Here is the warning I get when starting the install of the september CTP Enterprise version on a machine with XP/SP2 installed. If i continue to the components available for install, then the only thing enabled is 'Workstation components, ...'. All the actual database stuff is disable for install.

- SQL Server Edition Operating System Compatibility (Warning)

Messages

SQL Server Edition Operating System Compatibility

Some components of this edition of SQL Server are not supported on this operating system. For details, see 'Hardware and Software Requirements for Installing SQL Server 2005' in Microsoft SQL Server Books Online.

|||Enterprise Edition needs a server OS. You can install Express, Workgroup, Std and Developer Editions on XP, ie Desktop OS's|||I figured that was the problem. However, the download site does list XP/SP2 as a compatible OS for the enterprise edition. The bullet below was copied directly from the download page.

Supported Operating Systems: Microsoft Windows 2000 Service Pack 4; Windows Server 2003 Service Pack 1; Windows XP Service Pack 2

Error with Function and Procedures

I have just streamlined my pile of functions and reloaded the result into a Stored Procedure. I now have two different errors. here are the two FN's and the SP. This will be a long message so apologise for its length;

Function 1:-

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER FUNCTION [dbo].[fnWTRalldata]

(@.dt_src_date datetime,@.chr_div char(2), @.vch_portfolio_no tinyint,@.vch_prop_cat nvarchar(4))

RETURNS

@.WeeklyTerrierRSPI TABLE

(Areacode varchar(2),siteref nvarchar(3),estatename nvarchar(100), Securitised nvarchar(255),unitref nvarchar(15),unittype nvarchar(30),unittype_count int, tenantname nvarchar(100),tenantstatus nvarchar(25), tenantstatus_count int,unitstatus nvarchar(15), unitstatus_count int,floortotal float,floortotocc float,initialvacarea float, initialvacnet float,TotalRent float,NetRent float,FinalRtLsincSC float, ErvTot float, tenancyterm datetime, landact nvarchar(255),datadate datetime,div_mgr varchar(50),portfolio_mgr varchar(50),propcat nvarchar (4))

AS

BEGIN

INSERT @.WeeklyTerrierRSPI

SELECT src_terrier.Areacode, src_terrier.siteref, src_terrier.estatename, src_terrier.Securitised, src_terrier.unitref, src_terrier.unittype, src_terrier.unittype_count,

src_terrier.tenantname, src_terrier.tenantstatus, src_terrier.tenantstatus_count, src_terrier.unitstatus, src_terrier.unitstatus_count, src_terrier.floortotal,

src_terrier.floortotocc, src_terrier.initialvacarea, src_terrier.initialvacnet, src_terrier.TotalRent, src_terrier.NetRent, src_terrier.FinalRtLsincSC,

src_terrier.ErvTot, src_terrier.tenancyterm, src_terrier.landact, src_terrier.datadate, src_div_mgr.div_mgr,

src_portfolio_mgr.portfolio_mgr, src_centre_list.propcat

FROM src_terrier INNER JOIN

src_centre_list ON src_terrier.siteref = src_centre_list.Site_Ref AND src_terrier.Areacode = src_centre_list.Division INNER JOIN

src_div_mgr ON src_centre_list.Division = src_div_mgr.division INNER JOIN

src_portfolio_mgr ON src_centre_list.Portfolio_no = src_portfolio_mgr.portfolio_no

WHERE (src_terrier.datadate = @.dt_src_date) AND (src_terrier.Areacode = @.chr_div) AND ( src_centre_list.Portfolio_no = @.vch_portfolio_no) AND( src_centre_list.propcat = @.vch_prop_cat)

RETURN

END

GO

Function 2:-

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER FUNCTION [dbo].[fnWTRalldataReport]

(@.dt_src_date datetime,@.chr_div char(2), @.vch_portfolio_no tinyint,@.vch_prop_cat nvarchar(4))

RETURNS

@.WeeklyTerrierRSPII TABLE

(Areacode varchar(2),siteref nvarchar(3),estatename nvarchar(100), Securitised nvarchar(255),unitref nvarchar(15),unittype nvarchar(30),unittype_count int, tenantname nvarchar(100),tenantstatus nvarchar(25), tenantstatus_count int,unitstatus nvarchar(15), unitstatus_count int,floortotal float,floortotocc float,floorspaceperc float,initialvacarea float, initialvacnet float,TotalRent float,NetRent float,FinalRtLsincSC float,rentrolldiscperc float,netrentpersqft float, ErvTot float, tenancyterm datetime, landact nvarchar(255),datadate datetime,div_mgr varchar(50),portfolio_mgr varchar(50),propcat nvarchar (4))

AS

BEGIN

INSERT @.WeeklyTerrierRSPII

SELECT fnWTRalldata.Areacode, fnWTRalldata.siteref, fnWTRalldata.estatename, fnWTRalldata.Securitised, fnWTRalldata.unitref, fnWTRalldata.unittype, fnWTRalldata.unittype_count,

fnWTRalldata.tenantname, fnWTRalldata.tenantstatus, fnWTRalldata.tenantstatus_count, fnWTRalldata.unitstatus, fnWTRalldata.unitstatus_count, fnWTRalldata.floortotal,

fnWTRalldata.floortotocc, fnWTRalldata.floortotocc / fnWTRalldata.floortotal AS floorspaceperc, fnWTRalldata.initialvacarea, fnWTRalldata.initialvacnet, fnWTRalldata.TotalRent,

fnWTRalldata.NetRent, fnWTRalldata.FinalRtLsincSC,(fnWTRalldata.NetRent / fnWTRalldata.FinalRtLsincSC) - 1 AS rentrolldiscperc,

fnWTRalldata.NetRent / fnWTRalldata.floortotocc AS netrentpersqft, fnWTRalldata.ErvTot, fnWTRalldata.tenancyterm, fnWTRalldata.landact, fnWTRalldata.datadate, fnWTRalldata.div_mgr,

fnWTRalldata.portfolio_mgr, fnWTRalldata.propcat

FROM dbo.fnWTRalldata (@.dt_src_date, @.chr_div , @.vch_portfolio_no, @.vch_prop_cat)

RETURN

END

GO

STORED PROCEDURE :-

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[spWTRalldatareportsummary]

(@.dt_src_date datetime,@.chr_div char(2), @.vch_portfolio_no tinyint,@.vch_prop_cat nvarchar(4))

AS

BEGIN

--SET NOCOUNT ON;

SELECT

Areacode,siteref,estatename, Securitised,unitref,unittype,unittype_count, tenantname,tenantstatus,

tenantstatus_count,unitstatus, unitstatus_count,floortotal,floortotocc,floorspaceperc,initialvacarea, initialvacnet,TotalRent,NetRent,FinalRtLsincSC,rentrolldiscperc,netrentpersqft, ErvTot, tenancyterm, landact,datadate,div_mgr,portfolio_mgr,propcat

FROM fnWTRalldataReport (@.dt_src_date, @.chr_div , @.vch_portfolio_no, @.vch_prop_cat)

END

GO

The Problem I have is two fold. When I execure the procedure and run

USE [DashboardSQL-2K5]

GO

DECLARE @.return_value int

EXEC @.return_value = [dbo].[spWTRalldatareportsummary]

@.dt_src_date = N'28/04/2006', @.chr_div = N'SW', @.vch_portfolio_no = 4, @.vch_prop_cat = N'core'

SELECT 'Return Value' = @.return_value

GO

if I put the date in as 28/04/2006 I get an error like:-

Msg 8114, Level 16, State 1, Procedure spWTRalldatareportsummary, Line 0

Error converting data type nvarchar to datetime.

(1 row(s) affected)

If I put the date in as 04/28/2006 I get an error like :-

Msg 8134, Level 16, State 1, Procedure spWTRalldatareportsummary, Line 18

Divide by zero error encountered.

The statement has been terminated.

(1 row(s) affected)

Could anyone help me on this problem please as my whole project is now being help up by something stupid I have done.

Thanks in advance

hi,

i hope u shold try the date format as '2006-06-01' or in case u want to use your own date format which u are using

try using

@.dt_src_date = N'28/04/2006'

try convert(varchar,@.dt_src_date,103) = N'28/04/2006'

hope should work

regards

www.snktheone.com

|||

I will give that a go

Thanks

Error with for..next loop variable

I'm setting a variable called ConnectionString_vc in a for..next loop. The connectionstring property of one of my connections is then set to @.[User::ConnectionString_vc] via an expression. When running the package in a SQL agent job I'm getting the following error in my logs. I've received this sporadically when running it in debug mode in BIS, but it happens consistently in Agent.

The variable "User::ConnectionString_vc" is already on the read list. A variable may only be added once to either the read lock list or the write lock list.

Are you using that variable anywhere else? Perhaps in a script task?

It sounds as tho you've got the variable in the ReadOnlyvariables or ReadWriteVariables property of a script task and you're also calling LockOneForRead or LockOneForWrite within the script task.

-Jamie|||Not using the variable in a script task in this package. Control flow looks like this...

1. Execute SQL task queries list of connection strings that are stored in destination database. (Package basically loads data from multiple (identical) sources into one single destination. Just different instances of the same legacy system.) Execute SQL task maps Result set to variable called User::ADORecords.

2. Next step: For loop. Enumerator is "Foreach ADO Enumerator", ADO object source variable is "User::ADORecords", Enumeration mode is "Rows in the first table". Variable mapping is set for User::ConnectionString_vc

3. Within for loop there are about a dozen dataflow tasks that all reference a connection which has its connectionstring property equal to
@.[User::ConnectionString_vc] via an expression.

It runs successfully in debug mode or even when running without debugging from BI Studio. But when run from SQL Server agent I get the error.|||The issue doesn't seem to be related to the setting of the variable's value. It seems to be related to the Connection's connectionstring property being defined in the expression. I removed the expression so that as the For Loop ran it would set the variable but the connection would always be based off the initial default value of the connection instead of the expression. It ran through successfully.

Can anyone think of a better way to loop through a resultset of connection strings and modify the connection to the source system?

Thanks.|||That's bizarre. It sounds as though you've done it exactly as best practice dictates. Can you share the package?

This is annoying. It SHOULD work.

-Jamie|||

I'm having a similar problem. I'm using variables in expressions to set properties and also adding to the ReadOnlyList. I sometimes get that message even though I'm not doing any manual locking from the SQL agent but never get it in debug mode.

It also seems to happen when the expression is evaluated. It definately happens before any tasks even have run!

Anyways did you ever solve this?

|||

Kris, I'm doing this in a few packages as well and I experienced the problem when the variable that I passed in through the expression didn't exist. I changed over to a VariableDispenser and used the contains property in the script to do a check to make sure it existed before I did anything with it.

Brian Knight
Blog: http://www.whiteknighttechnology.com

|||

Are you using a script to build the value of the variables instead of using variables set by expressions?

Or are you using the VariableDispenser to just ensure it exists before continuing to other tasks?

|||

I wanted to have a general repository for variables (package configuration with steroids) so we had all the variables in a table. Then there's a stored procedure to retreive the variables for a group of package and inside a ForEach loop the Script task dynamically assigned the variables (so the variable name and value were dynamic from the table). I started much like you by using an expression to dynamically push the variable name into the ReadWriteVariables option in the script task based on the record in the ForEach collection. After seeing this bomb due to variables not matching (my recordset had a variable that didn't exist in the package), I switched to using the VariableDispenser. That way I could do a contains check to see if the value existed in the package and try catch it.

I'm sure you know this already, but here is how I set the variables using the VariableDispenser minus the error handling I added later:

http://whiteknighttechnology.com/cs/blogs/brian_knight/archive/2005/12/30/57.aspx

I hope I'm reading your question correctly though! Pardon if I went in a different direction than your question.

-- Brian

Error with for..next loop variable

I'm setting a variable called ConnectionString_vc in a for..next loop. The connectionstring property of one of my connections is then set to @.[User::ConnectionString_vc] via an expression. When running the package in a SQL agent job I'm getting the following error in my logs. I've received this sporadically when running it in debug mode in BIS, but it happens consistently in Agent.

The variable "User::ConnectionString_vc" is already on the read list. A variable may only be added once to either the read lock list or the write lock list.

Are you using that variable anywhere else? Perhaps in a script task?

It sounds as tho you've got the variable in the ReadOnlyvariables or ReadWriteVariables property of a script task and you're also calling LockOneForRead or LockOneForWrite within the script task.

-Jamie|||Not using the variable in a script task in this package. Control flow looks like this...

1. Execute SQL task queries list of connection strings that are stored in destination database. (Package basically loads data from multiple (identical) sources into one single destination. Just different instances of the same legacy system.) Execute SQL task maps Result set to variable called User::ADORecords.

2. Next step: For loop. Enumerator is "Foreach ADO Enumerator", ADO object source variable is "User::ADORecords", Enumeration mode is "Rows in the first table". Variable mapping is set for User::ConnectionString_vc

3. Within for loop there are about a dozen dataflow tasks that all reference a connection which has its connectionstring property equal to
@.[User::ConnectionString_vc] via an expression.

It runs successfully in debug mode or even when running without debugging from BI Studio. But when run from SQL Server agent I get the error.

|||The issue doesn't seem to be related to the setting of the variable's value. It seems to be related to the Connection's connectionstring property being defined in the expression. I removed the expression so that as the For Loop ran it would set the variable but the connection would always be based off the initial default value of the connection instead of the expression. It ran through successfully.

Can anyone think of a better way to loop through a resultset of connection strings and modify the connection to the source system?

Thanks.

|||That's bizarre. It sounds as though you've done it exactly as best practice dictates. Can you share the package?

This is annoying. It SHOULD work.

-Jamie|||

I'm having a similar problem. I'm using variables in expressions to set properties and also adding to the ReadOnlyList. I sometimes get that message even though I'm not doing any manual locking from the SQL agent but never get it in debug mode.

It also seems to happen when the expression is evaluated. It definately happens before any tasks even have run!

Anyways did you ever solve this?

|||

Kris, I'm doing this in a few packages as well and I experienced the problem when the variable that I passed in through the expression didn't exist. I changed over to a VariableDispenser and used the contains property in the script to do a check to make sure it existed before I did anything with it.

Brian Knight
Blog: http://www.whiteknighttechnology.com

|||

Are you using a script to build the value of the variables instead of using variables set by expressions?

Or are you using the VariableDispenser to just ensure it exists before continuing to other tasks?

|||

I wanted to have a general repository for variables (package configuration with steroids) so we had all the variables in a table. Then there's a stored procedure to retreive the variables for a group of package and inside a ForEach loop the Script task dynamically assigned the variables (so the variable name and value were dynamic from the table). I started much like you by using an expression to dynamically push the variable name into the ReadWriteVariables option in the script task based on the record in the ForEach collection. After seeing this bomb due to variables not matching (my recordset had a variable that didn't exist in the package), I switched to using the VariableDispenser. That way I could do a contains check to see if the value existed in the package and try catch it.

I'm sure you know this already, but here is how I set the variables using the VariableDispenser minus the error handling I added later:

http://whiteknighttechnology.com/cs/blogs/brian_knight/archive/2005/12/30/57.aspx

I hope I'm reading your question correctly though! Pardon if I went in a different direction than your question.

-- Brian

Error with expoting to Excel

I got the following error when exporting a report to Excel
Reporting Services Error
----
Exception of type
Microsoft.ReportingServices.ReportRendering.ReportRenderingException was
thrown. (rrRenderingError) Get Online Help
Exception of type
Microsoft.ReportingServices.ReportRendering.ReportRenderingException was
thrown.
An unexpected error occurred in Report Processing. (rsInternalError) Get
Online Help
Object reference not set to an instance of an object
--
Amila IndikaI got the same error.
I have a table in report body and grouped by, say, ColA.
In the page footer, a textbox refererences ColA by
First(ReportItems!ColA.Value).
When I export the report in PDF format, it works fine, but I got the error
when exporting to EXCEL format.
Any solution? Thanks
"Amila Indika" wrote:
> I got the following error when exporting a report to Excel
> Reporting Services Error
> ----
> Exception of type
> Microsoft.ReportingServices.ReportRendering.ReportRenderingException was
> thrown. (rrRenderingError) Get Online Help
> Exception of type
> Microsoft.ReportingServices.ReportRendering.ReportRenderingException was
> thrown.
> An unexpected error occurred in Report Processing. (rsInternalError) Get
> Online Help
> Object reference not set to an instance of an object
> --
> Amila Indika
>

Error with Execute sql task and full result set: " not been initialized before calling 'Fi

This is the first time I've tried creating an "execute sql task" with a "full result set".

I've read in the documentation that I must set the resultname to 0, which is done, and that the variable must be of type object. Also done.

[Execute SQL Task] Error: Executing the query "select * from blah" failed with the following error: "The SelectCommand property has not been initialized before calling 'Fill'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Has anyone else had success with a full result set?

Thanks,
-Lori

Yes. I have an example doing exactly this here:

SSIS Nugget: Execute SQL Task into an object variable - Shred it with a Foreach loop
(http://blogs.conchango.com/jamiethomson/archive/2005/07/04/1748.aspx)

-Jamie

|||Jamie,

The variable *is* of type Object (i believe i mentioned that).
Since this fails, I cannot move forward with the foreach loop.

I'm using 64bit sql server but debugging in Run64BitRuntime as False.

Any other ideas?

-Lori|||

lorijean44 wrote:

Jamie,

The variable *is* of type Object (i believe i mentioned that).

Yes you did. You also asked if anyone else has had any success which I have and was giving you a downloadable example of it working which I thought might be useful to you.

lorijean44 wrote:

Since this fails, I cannot move forward with the foreach loop.

I'm using 64bit sql server but debugging in Run64BitRuntime as False.

Any other ideas?

-Lori

Afraid not. Without being there its hard to say what the problem might be.

Regards

-Jamie

|||I noticed that you said in your blog, "just update the connection to point to your sql server". I thought I should mention that I'm actually trying to run this against a mysql server, with an ADO.net connection. I don't think that should matter, but who knows?

I'll play around and see if it works against a SQL Server just to try it.|||Yep, works great against a sql server (OLE DB Connection).

I will then update my question to be:
has anyone gotten a full result set to work with an ADO.net connection?|||

lorijean44 wrote:

I noticed that you said in your blog, "just update the connection to point to your sql server". I thought I should mention that I'm actually trying to run this against a mysql server, with an ADO.net connection. I don't think that should matter, but who knows?

I'll play around and see if it works against a SQL Server just to try it.

I'd bevery surprised if that was anything to do with it but who knows - give it a try.

Also note that my example used OLE DB Connection Manager rather than ADO.Net. That could be an avenue of investigation also.

-Jamie

Error with Execute sql task and full result set: " not been initialized before calling

This is the first time I've tried creating an "execute sql task" with a "full result set".

I've read in the documentation that I must set the resultname to 0, which is done, and that the variable must be of type object. Also done.

[Execute SQL Task] Error: Executing the query "select * from blah" failed with the following error: "The SelectCommand property has not been initialized before calling 'Fill'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Has anyone else had success with a full result set?

Thanks,
-Lori

Yes. I have an example doing exactly this here:

SSIS Nugget: Execute SQL Task into an object variable - Shred it with a Foreach loop
(http://blogs.conchango.com/jamiethomson/archive/2005/07/04/1748.aspx)

-Jamie

|||Jamie,

The variable *is* of type Object (i believe i mentioned that).
Since this fails, I cannot move forward with the foreach loop.

I'm using 64bit sql server but debugging in Run64BitRuntime as False.

Any other ideas?

-Lori|||

lorijean44 wrote:

Jamie,

The variable *is* of type Object (i believe i mentioned that).

Yes you did. You also asked if anyone else has had any success which I have and was giving you a downloadable example of it working which I thought might be useful to you.

lorijean44 wrote:

Since this fails, I cannot move forward with the foreach loop.

I'm using 64bit sql server but debugging in Run64BitRuntime as False.

Any other ideas?

-Lori

Afraid not. Without being there its hard to say what the problem might be.

Regards

-Jamie

|||I noticed that you said in your blog, "just update the connection to point to your sql server". I thought I should mention that I'm actually trying to run this against a mysql server, with an ADO.net connection. I don't think that should matter, but who knows?

I'll play around and see if it works against a SQL Server just to try it.|||Yep, works great against a sql server (OLE DB Connection).

I will then update my question to be:
has anyone gotten a full result set to work with an ADO.net connection?|||

lorijean44 wrote:

I noticed that you said in your blog, "just update the connection to point to your sql server". I thought I should mention that I'm actually trying to run this against a mysql server, with an ADO.net connection. I don't think that should matter, but who knows?

I'll play around and see if it works against a SQL Server just to try it.

I'd bevery surprised if that was anything to do with it but who knows - give it a try.

Also note that my example used OLE DB Connection Manager rather than ADO.Net. That could be an avenue of investigation also.

-Jamie

error with Execute package task

Hi,

We have used an execute package task in our master package to execute a child package and we have set the execute out of process=false. This master package is running fine in 32 bit server but is failing in 64 bit server. is there any settings to be done in the server or is it the problem with the property setting(execute out of process)

Vivek S

sorry for posting in SSRS category. Has been reposted in SSIS

Error with establishing a connection to the server

i cannot access my database as i am keep getting the following error:

"When connecting to SQL server 2005, this failure may be caused by the fact that under the default settings SQL server does not allow remote connections. (provider:shared memory provider, error: 40 - could not open a connection to SQL server)"

when i click on the properties window i cannot see my table nor can i make a new connection as the same error is shown, could you please advice me how i can fix this?

Make sure the SQL Server 2005 service is running.

Are you able to connect to the server through Query Analyzer ?

Visitwww.connectionstrings.com to know how to build a connection string for your scenario.

Error with Email Subscriptions

Hi,
I have configured the Email settings, and have set up a scheduled
subscription to send report links.
The SQL Server Agent is running and I have also configured SQL Mail with
the correct MAPI Profile.
The subscriptions dont work and give the following error "The e-mail address
of one or more recipients is not valid.".
The email Ids are valid.
Can anybody pls help?
Thanks & Regards
MPPWell i had the same problem.It occurs when the email is not in the domain of
the server.I still don't know what 2 do.
"MPP" wrote:
> Hi,
> I have configured the Email settings, and have set up a scheduled
> subscription to send report links.
> The SQL Server Agent is running and I have also configured SQL Mail with
> the correct MAPI Profile.
> The subscriptions dont work and give the following error "The e-mail address
> of one or more recipients is not valid.".
> The email Ids are valid.
> Can anybody pls help?
> Thanks & Regards
> MPP
>
>|||You have to set your mail server up to allow relaying
--
---
Yes, I searched first :)
"MPP" wrote:
> Hi,
> I have configured the Email settings, and have set up a scheduled
> subscription to send report links.
> The SQL Server Agent is running and I have also configured SQL Mail with
> the correct MAPI Profile.
> The subscriptions dont work and give the following error "The e-mail address
> of one or more recipients is not valid.".
> The email Ids are valid.
> Can anybody pls help?
> Thanks & Regards
> MPP
>
>

Error with Downloading MSDE

Dear All,
I am new to SQL I download "sql2ksp3" to my laptop (Window Xp Pentium III) and then I start to setup MSDE:

>> setup SAPWD=pass SECURITYMODE=SQL

After I run this command it gives me the following statment!!!

Setup failed to configure the server.Refer to the server error logs and setup error logs for more information.

Please help me what i have to do to make this stuff works!!

Sincerely
Henok::Please help me what i have to do to make this stuff works!!

I hate to tell you, but this starts with reading the error message. And following the isntructions it provides.

See:

::Setup failed to configure the server.Refer to the server error logs and setup error logs for
::more information.

Tells you to go to the sever error logs and the setup error logs and get more information.

Now, without this information, I do not really see how we could help you. Maybe if my crystal ball version 22332233 is finally delivered, but until then at least I actually do rely on you getting the errors our of your computer.

Are you sure that pass and securitymode are correct, btw? I NEVER had to run setup with any parameters.|||Have you installed MSDE before this, and you're just upgrading your current instance?

check out this tutorial on MSDE installation:
A Beginner's Guide - Installing MSDE
|||To DL the MSDE Engine I would first Download This file... Latest Update, From memory the file you listed above is the SP3 for SQL Server and not the desktop install.

<<http://www.microsoft.com/downloads/details.aspx?FamilyID=413744d1-a0bc-479f-bafa-e4b278eb9147&DisplayLang=en>>
MSDE SP3a Download.|||I had the same problem because I, too, read the installation instructions that shipped with MSDE.

I used the following command:
setup SAPWD="bubba"

If you choose to use theSECURITYMODE switch, you won't need to encloseSQL in quotations.

Good luck!
John|||David,

Thanks for pointing us (at least me) to a nice artilce. I could over come this issue and it works fine for me. Thank you.

Error with default database collation

Hello
I am using SQL Server 2000 SP4
In my stored procedure I create table variable that contains one varchar
field and join that table variable with one of the database tables. I keep
getting error "Cannot resolve collation conflict for equal to operation.".
Both fields should have same collation. Table's field due to the fact that
it's collation is set to <database default> and <database default> is
Latin1_General_CI_AS. And according to Books online table variable also has
a
collation taken from <database default> if not explicitly set to other. So
what is the problem? If table variable field explicitly assign collation
Latin1_General_CI_AS - everything works fine. Why? My server's default
collation is Cyrillic_General_CI_AS. But how that can affect? I am not
allowed to use explicit collation in my SP.
Probably it is a known bug in SQL Server... Are then any fixes for that?
Thanks in advance.Seems like SQL2K doesn't pick up the current database for the connection whe
n you create a table
variable:
CREATE DATABASE x COLLATE Cyrillic_General_CI_AS
GO
USE x
CREATE TABLE t(c1 varchar(10))
INSERT INTO t VALUES('asd')
GO
--Error on 2000 sp3, fine on 2005
DECLARE @.t table(c1 varchar(10))
INSERT INTO @.t VALUES('asd')
SELECT * FROM t INNER JOIN @.t AS t2 ON t.c1 = t2.c1
GO
--Fine on both 2000 sp3 and 2005, as expected
DECLARE @.t table(c1 varchar(10) COLLATE database_Default)
INSERT INTO @.t VALUES('asd')
SELECT * FROM t INNER JOIN @.t AS t2 ON t.c1 = t2.c1
GO
--Error as expected on both 2000 sp3 and 2005
CREATE TABLE #t (c1 varchar(10))
INSERT INTO #t VALUES('asd')
SELECT * FROM t INNER JOIN #t AS t2 ON t.c1 = t2.c1
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Alexander Korol" <AlexanderKorol@.discussions.microsoft.com> wrote in messag
e
news:C5DD7198-4122-483E-BFD6-E83AF28002F7@.microsoft.com...
> Hello
> I am using SQL Server 2000 SP4
> In my stored procedure I create table variable that contains one varchar
> field and join that table variable with one of the database tables. I keep
> getting error "Cannot resolve collation conflict for equal to operation.".
> Both fields should have same collation. Table's field due to the fact that
> it's collation is set to <database default> and <database default> is
> Latin1_General_CI_AS. And according to Books online table variable also ha
s a
> collation taken from <database default> if not explicitly set to other. So
> what is the problem? If table variable field explicitly assign collation
> Latin1_General_CI_AS - everything works fine. Why? My server's default
> collation is Cyrillic_General_CI_AS. But how that can affect? I am not
> allowed to use explicit collation in my SP.
> Probably it is a known bug in SQL Server... Are then any fixes for that?
> Thanks in advance.

error with datetime datatype

Hi Guys,

I'm having problem in loading textfile into the database. One of the columns in the textfile has a datetime datatype.

Here is a sample of the text file. All the other columns are string except for the datetime: "5/4/2006"

"1","1","ITEM","5/4/2006","10:05:04","11110",10004,"Regular Half Chicken",1,130.00,0,0

Error is shown below.

Error: 0xC0202009 at Data Flow Task, OLE DB Destination [154]: An OLE DB error has occurred. Error code: 0x80004005.

An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Invalid character value for cast specification".

Error: 0xC020901C at Data Flow Task, OLE DB Destination [154]: There was an error with input column "Transaction_Date" (1233) on input "OLE DB Destination Input" (167). The column status returned was: "The value could not be converted because of a potential loss of data.".

Error: 0xC0209029 at Data Flow Task, OLE DB Destination [154]: The "input "OLE DB Destination Input" (167)" failed because error code 0xC0209077 occurred, and the error row disposition on "input "OLE DB Destination Input" (167)" specifies failure on error. An error occurred on the specified object of the specified component.

Error: 0xC0047022 at Data Flow Task, DTS.Pipeline: The ProcessInput method on component "OLE DB Destination" (154) failed with error code 0xC0209029. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.

Error: 0xC0047021 at Data Flow Task, DTS.Pipeline: Thread "WorkThread0" has exited with error code 0xC0209029.

Please help!!!

Thanks in advance,

Larry

You'll need to throw a "convert" into the dataflow to convert that date format:

select convert(datetime, '10/31/2006',101) 'returns 2006-10-31 00:00:00
select convert(datetime, [Transaction_Date],101)

Error with databound datagrid

Help,

I have a simple app that only has on datagrid that is bound by the typical sqlconnection,sqldataadapter and dataset. But I keep getting this error:

Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details:System.Data.SqlClient.SqlException: Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.
Source Error:

Line 55: Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.LoadLine 56: 'Put user code to initialize the page hereLine 57: SqlDataAdapter1.Fill(DataSet11)Line 58: DataGrid1.DataBind()Line 59:

WHY :)

xactly what the message says..can you post the connection string ?

|||
Indeed, fix your connectionstring. Get that working before messing withdatagrids. The error indicates that you're telling ADO.NET to use atrusted connection (i.e. windows authentication) but the ASPNET useraccount probably doesn't have access to your database. I typically useSQL authentication so I can limit the damage each application can do.One account per database.

Error with Data Flow Task

I am having problems with the Data Flow task. It does not even show up in the list of items to drop into the SSIS project.

If I go to the Data Flow tab and hit create, I get the follow error. I have tried repairing and reinstalling, but nothing seems to clear up the error. Without rebuilding my machine, is there anyone who knows how to get the Data Flow Task reinstalled properly?

Thanks

Wayne

TITLE: Microsoft Visual Studio


Registration information about the Data Flow task could not be retrieved. Confirm that this task is installed properly on the computer.


ADDITIONAL INFORMATION:

TaskHost "{C3BF9DC1-4715-4694-936F-D3CFDA9E42C5}"' is not installed correctly on this computer. (Microsoft.DataTransformationServices.Design)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%u00ae+Visual+Studio%u00ae+2005&ProdVer=8.0.50727.762&EvtSrc=Microsoft.DataTransformationServices.Design.SR&EvtID=TaskHostNotInstalled&LinkId=20476


BUTTONS:

OK

Did you search the forum?

it looks like that has been asked before:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=898604&SiteID=1

Error with COUNT(CASE WHEN...)

I have a strange problem.
I have reduced the problem to this:
SELECT COUNT(CASE WHEN x0 = 'A' THEN 1 ELSE 0 END) AS a_tally
,COUNT(CASE WHEN x0 = 'B' THEN 1 ELSE 0 END) AS b_tally
FROM (SELECT 'A') AS X (x0)
I exppect this to give me the result a-tally = 1 and b-tally = 0.
However I get
a_tally b_tally
-- --
1 1
(1 row(s) affected)
How in earths name is this possible?
/kCOUNT doesn't care if the result is 1 or 0, only that there is a result. I
think you meant to use SUM as the aggregate, not COUNT.
"kurt sune" <apa@.apa.com> wrote in message
news:uCDjPki2FHA.3136@.TK2MSFTNGP09.phx.gbl...
>I have a strange problem.
> I have reduced the problem to this:
> SELECT COUNT(CASE WHEN x0 = 'A' THEN 1 ELSE 0 END) AS a_tally
> ,COUNT(CASE WHEN x0 = 'B' THEN 1 ELSE 0 END) AS b_tally
> FROM (SELECT 'A') AS X (x0)
>
> I exppect this to give me the result a-tally = 1 and b-tally = 0.
> However I get
> a_tally b_tally
> -- --
> 1 1
> (1 row(s) affected)
>
> How in earths name is this possible?
>
> /k
>|||Run this:
SELECT count(CASE WHEN x0 = 'A' THEN 10 ELSE 0 END) AS a_tally
,count(CASE WHEN x0 = 'B' THEN 10 ELSE 0 END) AS b_tally
FROM (SELECT 'A') AS X (x0)
It will still return the same results as COUNT returns the number of rows.
Change your COUNTs to SUMs and your query works just fine.
"kurt sune" <apa@.apa.com> wrote in message
news:uCDjPki2FHA.3136@.TK2MSFTNGP09.phx.gbl...
>I have a strange problem.
> I have reduced the problem to this:
> SELECT COUNT(CASE WHEN x0 = 'A' THEN 1 ELSE 0 END) AS a_tally
> ,COUNT(CASE WHEN x0 = 'B' THEN 1 ELSE 0 END) AS b_tally
> FROM (SELECT 'A') AS X (x0)
>
> I exppect this to give me the result a-tally = 1 and b-tally = 0.
> However I get
> a_tally b_tally
> -- --
> 1 1
> (1 row(s) affected)
>
> How in earths name is this possible?
>
> /k
>|||Just remove the Else part and you'll be fine.
SELECT COUNT(CASE WHEN x0 = 'A' THEN 1 END) AS a_tally
,COUNT(CASE WHEN x0 = 'B' THEN 1 END) AS b_tally
FROM (SELECT 'A') AS X (x0)
http://toponewithties.blogspot.com/...count-them.html
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"kurt sune" <apa@.apa.com> wrote in message
news:uCDjPki2FHA.3136@.TK2MSFTNGP09.phx.gbl...
>I have a strange problem.
> I have reduced the problem to this:
> SELECT COUNT(CASE WHEN x0 = 'A' THEN 1 ELSE 0 END) AS a_tally
> ,COUNT(CASE WHEN x0 = 'B' THEN 1 ELSE 0 END) AS b_tally
> FROM (SELECT 'A') AS X (x0)
>
> I exppect this to give me the result a-tally = 1 and b-tally = 0.
> However I get
> a_tally b_tally
> -- --
> 1 1
> (1 row(s) affected)
>
> How in earths name is this possible?
>
> /k
>|||I was trying this: http://www.dbazine.com/ofinterest/oi-articles/celko14
Seems like SQLserver and mr Celko disagrees about COUNT.
Thanks everyone.
/k
"kurt sune" <apa@.apa.com> wrote in message
news:uCDjPki2FHA.3136@.TK2MSFTNGP09.phx.gbl...
> I have a strange problem.
> I have reduced the problem to this:
> SELECT COUNT(CASE WHEN x0 = 'A' THEN 1 ELSE 0 END) AS a_tally
> ,COUNT(CASE WHEN x0 = 'B' THEN 1 ELSE 0 END) AS b_tally
> FROM (SELECT 'A') AS X (x0)
>
> I exppect this to give me the result a-tally = 1 and b-tally = 0.
> However I get
> a_tally b_tally
> -- --
> 1 1
> (1 row(s) affected)
>
> How in earths name is this possible?
>
> /k
>|||I am trying do do like Celko does in this article
http://www.dbazine.com/ofinterest/oi-articles/celko14
Do you any advice regarding the usage of the count in the HAVING clause?
My solution was this
HAVING COUNT(CASE WHEN x0 = 'A' THEN 1 ELSE null END)
<> COUNT(CASE WHEN x0 = 'B' THEN 1 ELSE null END)
However that means I have to set ansiwarnings off, which I dont like.
/k
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uOdJqmi2FHA.1576@.TK2MSFTNGP15.phx.gbl...
> COUNT doesn't care if the result is 1 or 0, only that there is a result.
I
> think you meant to use SUM as the aggregate, not COUNT.
>
>
> "kurt sune" <apa@.apa.com> wrote in message
> news:uCDjPki2FHA.3136@.TK2MSFTNGP09.phx.gbl...
>|||> Do you any advice regarding the usage of the count in the HAVING clause?
> My solution was this
> HAVING COUNT(CASE WHEN x0 = 'A' THEN 1 ELSE null END)
> <> COUNT(CASE WHEN x0 = 'B' THEN 1 ELSE null END)
Yes, use SUM, just like what was suggested in the SELECT clause?

error with connection with SQL SERVER

Hi

I am developing web application.

I have connected my app. with sql server using web.config as code given below using Named pipe connection

<appSettings>
<add key="constring" value="Server=ebserver;UID=sa;database=Airport-Clearance;Integrated Security=SSPI;network library=dbnmpntw;" />
</appSettings
After than i have added a new CLASS in my app.
in that, I have written as given below code.

public SqlConnection GetConnection()
{
System.Configuration.AppSettingsReader objApp = new System.Configuration.AppSettingsReader ();
SqlConnection sqlconn = new SqlConnection ();
sqlconn.ConnectionString = Convert.ToString(objApp.GetValue("constring", typeof(string)));
return sqlconn ;
}

Finally, In load event of form, I have written as below code.

Class1 objcs = new Class1() ;
SqlConnection sqlconn = objcs.GetConnection ();
SqlDataAdapter filling = new SqlDataAdapter("select * from Airport",sqlconn);
DataSet ds = new DataSet();
sqlconn.Open();
//filling.Fill(ds,"Airport");

//DataGrid dg = new DataGrid();
dg.DataSource = ds.Tables["Airport"];
dg.DataBind();
//Do what ever
sqlconn.Close ();

When i run this application

It gives error like

provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

If i use try catch then it doesnt display any error.but Even it doesnt display datagrid on form.

Please give me solution.

Even how to run app. line by line as we run in VB using F8.

Reply please,

Regards,
ASIFTry this instead (assuming you're not using beta version):

public SqlConnection GetConnection()
{
SqlConnection sqlconn = new SqlConnection ();
sqlconn.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["constring"].ConnectionString;
return sqlconn ;
}

Error with connection

We are trying to move to a new server, due to problems with our current one.
Everything seems to be working, but we cannot seem to get the connection to
the SQL database. When we try to bring up the website that accesses the
database, we get the following error:
Microsoft OLE DB Provider for SQL Server error '80004005'
[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access
denied.
/incfiles/MainStoreFile_Real.asp, line 73
I have checked everything that I can think of (note- that I can think of),
but cannot get past this error. Does anyone have any ideas?
RickI think that
new DataBase don't take permission to access DB
Could you check your DataBase permission
<< DataBase Property--> permission >>
cheers.|||From the Web Server, ping the SQL Server.
That error indicates that SQL cannot be contacted.
And even if you can ping SQL, see if you can access the 1433 port from the
Web Server
Thanks
"Brewhaus" <Brewhaus@.discussions.microsoft.com> wrote in message
news:2FFA9C1B-DF1E-439E-BFD6-8E89094FE503@.microsoft.com...
> We are trying to move to a new server, due to problems with our current
> one.
> Everything seems to be working, but we cannot seem to get the connection
> to
> the SQL database. When we try to bring up the website that accesses the
> database, we get the following error:
> Microsoft OLE DB Provider for SQL Server error '80004005'
> [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access
> denied.
> /incfiles/MainStoreFile_Real.asp, line 73
> I have checked everything that I can think of (note- that I can think of),
> but cannot get past this error. Does anyone have any ideas?
> Rick|||Couple of things :
1. Check the firewall port ( if any ) between the App and the Database
Server is open or not .
2. Install the latest ODBC connections in your App server.
3. User Account and the Password trying to connect the Database server
having appropriater permissions and if the password is correct.
...Piku.
"Brewhaus" wrote:
> We are trying to move to a new server, due to problems with our current one.
> Everything seems to be working, but we cannot seem to get the connection to
> the SQL database. When we try to bring up the website that accesses the
> database, we get the following error:
> Microsoft OLE DB Provider for SQL Server error '80004005'
> [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access
> denied.
> /incfiles/MainStoreFile_Real.asp, line 73
> I have checked everything that I can think of (note- that I can think of),
> but cannot get past this error. Does anyone have any ideas?
> Rick|||First, thank you for your responses.
I had confirmed before that the DB permissions were the same as on the
working server, but have now also checked that the permissions for the actual
file are also the same. I had considered the password issue before, so had
already very carefully re-entered it.
I am not sure how to ping the SQL server, or check the port, but the
software that is accessing the server is on the same computer as SQL server
and the DB file, so I am not sure if either of these could be the issue-
please tell me if they could, and if so, how to check them. I am actually
getting this error in a webpage on the same computer, so I cannot imagine
that it is a firewall issue.
Does anyone have any further thoughts on this?
Rick|||Change your connectionstring to use the IP address of the sql server instead
of the server name.
"Server=127.0.0.1;Database=DBname;User
ID=username;Password=password;Trusted_Connection=False"
To find the ipaddress: go to start-run-cmd-ipconfig, if that works then you
have a DNS issue.
To find out whether 1433 is open you can use the port query tool (MS has one).
HTH
--
Rakesh Ajwani
MCSD, MCSD.NET
"Brewhaus" wrote:
> First, thank you for your responses.
> I had confirmed before that the DB permissions were the same as on the
> working server, but have now also checked that the permissions for the actual
> file are also the same. I had considered the password issue before, so had
> already very carefully re-entered it.
> I am not sure how to ping the SQL server, or check the port, but the
> software that is accessing the server is on the same computer as SQL server
> and the DB file, so I am not sure if either of these could be the issue-
> please tell me if they could, and if so, how to check them. I am actually
> getting this error in a webpage on the same computer, so I cannot imagine
> that it is a firewall issue.
> Does anyone have any further thoughts on this?
> Rick
>|||I seem to be getting a little further. When I change the IP address that the
system tries to connect on I just get a licensing error on the software
(because it is set up with the other address). When I change the IP address
back, I get the connection error. Does this mean that the SQL server is
being run specifically on one of our IP addresses only? If so, is there a
way to change which address it runs on?|||All right, Now I have a feeling it's got to do with the port. Verify this for
me:
In the Enterprise manager, Right Click on the DB Server Node and hit
Properties. On the General Tab, Click Network Configuration, it should have
two protocols enabled, named pipes and TCP/IP, Highlight TCP/IP & click
properties, It will give you the default value of the port its running the
sql server on, if it is different than 1433, Include that port number in your
connectionstring and try and connect again
change your connection string like this
Data Source=VSERVER1\instancename,2433
Just curious do you have more than one instance(or a named instance) of sql
server running on this server. If you do please look at this following
article.
http://blogs.msdn.com/sql_protocols/archive/2006/02/27/539706.aspx
Rakesh Ajwani
MCSD, MCSD.NET
"Brewhaus" wrote:
> I seem to be getting a little further. When I change the IP address that the
> system tries to connect on I just get a licensing error on the software
> (because it is set up with the other address). When I change the IP address
> back, I get the connection error. Does this mean that the SQL server is
> being run specifically on one of our IP addresses only? If so, is there a
> way to change which address it runs on?|||We have only one instance of SQL Server running on the server.
I had checked the settings before, but went back and did so again, just in
case I had missed something. The port showing is 1433.
The server has multiple public IP addresses on a single NIC, though. Would
it matter which was the main one, or which is the one used when setting up
the internet connection with the Internet Connection Wizard?
What seems odd is that we are doing the testing directly on the server that
is running SQL Server. We are not coming in from another computer via the
network or internet connection. Because of that, I would not expect it to
matter which of the IP addresses we used. Obviously it does, though.
"Rakesh Ajwani" wrote:
> All right, Now I have a feeling it's got to do with the port. Verify this for
> me:
> In the Enterprise manager, Right Click on the DB Server Node and hit
> Properties. On the General Tab, Click Network Configuration, it should have
> two protocols enabled, named pipes and TCP/IP, Highlight TCP/IP & click
> properties, It will give you the default value of the port its running the
> sql server on, if it is different than 1433, Include that port number in your
> connectionstring and try and connect again
> change your connection string like this
> Data Source=VSERVER1\instancename,2433
> Just curious do you have more than one instance(or a named instance) of sql
> server running on this server. If you do please look at this following
> article.
> http://blogs.msdn.com/sql_protocols/archive/2006/02/27/539706.aspx
>
> --
> Rakesh Ajwani
> MCSD, MCSD.NET
>
> "Brewhaus" wrote:
> > I seem to be getting a little further. When I change the IP address that the
> > system tries to connect on I just get a licensing error on the software
> > (because it is set up with the other address). When I change the IP address
> > back, I get the connection error. Does this mean that the SQL server is
> > being run specifically on one of our IP addresses only? If so, is there a
> > way to change which address it runs on?

Error with connection

We are trying to move to a new server, due to problems with our current one.
Everything seems to be working, but we cannot seem to get the connection to
the SQL database. When we try to bring up the website that accesses the
database, we get the following error:
Microsoft OLE DB Provider for SQL Server error '80004005'
[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or
access
denied.
/incfiles/MainStoreFile_Real.asp, line 73
I have checked everything that I can think of (note- that I can think of),
but cannot get past this error. Does anyone have any ideas?
RickI think that
new DataBase don't take permission to access DB
Could you check your DataBase permission
<< DataBase Property--> permission >>
cheers.|||From the Web Server, ping the SQL Server.
That error indicates that SQL cannot be contacted.
And even if you can ping SQL, see if you can access the 1433 port from the
Web Server
Thanks
"Brewhaus" <Brewhaus@.discussions.microsoft.com> wrote in message
news:2FFA9C1B-DF1E-439E-BFD6-8E89094FE503@.microsoft.com...
> We are trying to move to a new server, due to problems with our current
> one.
> Everything seems to be working, but we cannot seem to get the connection
> to
> the SQL database. When we try to bring up the website that accesses the
> database, we get the following error:
> Microsoft OLE DB Provider for SQL Server error '80004005'
> [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist o
r access
> denied.
> /incfiles/MainStoreFile_Real.asp, line 73
> I have checked everything that I can think of (note- that I can think of),
> but cannot get past this error. Does anyone have any ideas?
> Rick|||Couple of things :
1. Check the firewall port ( if any ) between the App and the Database
Server is open or not .
2. Install the latest ODBC connections in your App server.
3. User Account and the Password trying to connect the Database server
having appropriater permissions and if the password is correct.
...Piku.
"Brewhaus" wrote:

> We are trying to move to a new server, due to problems with our current on
e.
> Everything seems to be working, but we cannot seem to get the connection t
o
> the SQL database. When we try to bring up the website that accesses the
> database, we get the following error:
> Microsoft OLE DB Provider for SQL Server error '80004005'
> [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist o
r access
> denied.
> /incfiles/MainStoreFile_Real.asp, line 73
> I have checked everything that I can think of (note- that I can think of),
> but cannot get past this error. Does anyone have any ideas?
> Rick|||First, thank you for your responses.
I had confirmed before that the DB permissions were the same as on the
working server, but have now also checked that the permissions for the actua
l
file are also the same. I had considered the password issue before, so had
already very carefully re-entered it.
I am not sure how to ping the SQL server, or check the port, but the
software that is accessing the server is on the same computer as SQL server
and the DB file, so I am not sure if either of these could be the issue-
please tell me if they could, and if so, how to check them. I am actually
getting this error in a webpage on the same computer, so I cannot imagine
that it is a firewall issue.
Does anyone have any further thoughts on this?
Rick|||Change your connectionstring to use the IP address of the sql server instead
of the server name.
"Server=127.0.0.1;Database=DBname;User
ID=username;Password=password;Trusted_Co
nnection=False"
To find the ipaddress: go to start-run-cmd-ipconfig, if that works then you
have a DNS issue.
To find out whether 1433 is open you can use the port query tool (MS has one
).
HTH
--
Rakesh Ajwani
MCSD, MCSD.NET
"Brewhaus" wrote:

> First, thank you for your responses.
> I had confirmed before that the DB permissions were the same as on the
> working server, but have now also checked that the permissions for the act
ual
> file are also the same. I had considered the password issue before, so ha
d
> already very carefully re-entered it.
> I am not sure how to ping the SQL server, or check the port, but the
> software that is accessing the server is on the same computer as SQL serve
r
> and the DB file, so I am not sure if either of these could be the issue-
> please tell me if they could, and if so, how to check them. I am actually
> getting this error in a webpage on the same computer, so I cannot imagine
> that it is a firewall issue.
> Does anyone have any further thoughts on this?
> Rick
>|||I seem to be getting a little further. When I change the IP address that th
e
system tries to connect on I just get a licensing error on the software
(because it is set up with the other address). When I change the IP address
back, I get the connection error. Does this mean that the SQL server is
being run specifically on one of our IP addresses only? If so, is there a
way to change which address it runs on?|||All right, Now I have a feeling it's got to do with the port. Verify this fo
r
me:
In the Enterprise manager, Right Click on the DB Server Node and hit
Properties. On the General Tab, Click Network Configuration, it should have
two protocols enabled, named pipes and TCP/IP, Highlight TCP/IP & click
properties, It will give you the default value of the port its running the
sql server on, if it is different than 1433, Include that port number in you
r
connectionstring and try and connect again
change your connection string like this
Data Source=VSERVER1\instancename,2433
Just curious do you have more than one instance(or a named instance) of sql
server running on this server. If you do please look at this following
article.
http://blogs.msdn.com/sql_protocols.../27/539706.aspx
Rakesh Ajwani
MCSD, MCSD.NET
"Brewhaus" wrote:

> I seem to be getting a little further. When I change the IP address that
the
> system tries to connect on I just get a licensing error on the software
> (because it is set up with the other address). When I change the IP addre
ss
> back, I get the connection error. Does this mean that the SQL server is
> being run specifically on one of our IP addresses only? If so, is there a
> way to change which address it runs on?|||We have only one instance of SQL Server running on the server.
I had checked the settings before, but went back and did so again, just in
case I had missed something. The port showing is 1433.
The server has multiple public IP addresses on a single NIC, though. Would
it matter which was the main one, or which is the one used when setting up
the internet connection with the Internet Connection Wizard?
What seems odd is that we are doing the testing directly on the server that
is running SQL Server. We are not coming in from another computer via the
network or internet connection. Because of that, I would not expect it to
matter which of the IP addresses we used. Obviously it does, though.
"Rakesh Ajwani" wrote:
[vbcol=seagreen]
> All right, Now I have a feeling it's got to do with the port. Verify this
for
> me:
> In the Enterprise manager, Right Click on the DB Server Node and hit
> Properties. On the General Tab, Click Network Configuration, it should hav
e
> two protocols enabled, named pipes and TCP/IP, Highlight TCP/IP & click
> properties, It will give you the default value of the port its running the
> sql server on, if it is different than 1433, Include that port number in y
our
> connectionstring and try and connect again
> change your connection string like this
> Data Source=VSERVER1\instancename,2433
> Just curious do you have more than one instance(or a named instance) of sq
l
> server running on this server. If you do please look at this following
> article.
> http://blogs.msdn.com/sql_protocols.../27/539706.aspx
>
> --
> Rakesh Ajwani
> MCSD, MCSD.NET
>
> "Brewhaus" wrote:
>

Error with conn string

Just installed AJAX for VS2005 and started to build a AJAX enabled web site. I got the following message after creating a database (SQL) and Dataset "FOLLOWING ERROR OCCURED WHLE GETTING CONNECTION STRING INFORMATION FROM CONFIGURATION:'CANNOT GET WEB APPLICATION SERVICE'.

I didn't have this problem before,can it be related to AJAX?

Thank you in advance,

close your VS 2005 and open it again The SQLConnection error might go away.

error with calling stored proc with exec

Ok, I previously had a thread about "Column name as variable".
Here's a proc I wrote, and it compiles ok:
alter PROCEDURE rptRRTP_By_Date_Range
-- Add the parameters for the stored procedure here
@.Update_Field as sysname,
@.start_date as datetime,
@.end_date as datetime,
@.contr_Stat as varchar(2),
@.seq as int
AS
declare @.sql_stat as varchar(255)
BEGIN
set @.sql_stat = 'update RRTP_Scorecard set ' + @.update_field + ' =
(select count(sequence_no) from fnStatusPerDatesTbl(' + @.start_date +
',' + @.end_date + ')
where contract_status = ' + @.contr_stat + ' group by sequence_no)
where sequence = ' + @.seq
Execute (@.sql_stat)
END
GO
And when I try to run it:
exec rptRRTP_By_Date_Range 'current_status', '1/1/2000', getdate(),
'00',1
I get this error:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ')'.
Looks like it doesn't like that getdate() call.
Any feedback on this?You can't pass a function in as a parameter. Try:
DECLARE @.dt DATETIME;
SET @.dt = GETDATE();
EXEC rptRRTP_By_Date_Range 'current_status', '1/1/2000', @.dt, '00',1;
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"d.s." <nodamnspamok@.yahoo.com> wrote in message
news:1178047866.417792.32680@.h2g2000hsg.googlegroups.com...
> Ok, I previously had a thread about "Column name as variable".
> Here's a proc I wrote, and it compiles ok:
>
> alter PROCEDURE rptRRTP_By_Date_Range
> -- Add the parameters for the stored procedure here
> @.Update_Field as sysname,
> @.start_date as datetime,
> @.end_date as datetime,
> @.contr_Stat as varchar(2),
> @.seq as int
> AS
> declare @.sql_stat as varchar(255)
> BEGIN
> set @.sql_stat = 'update RRTP_Scorecard set ' + @.update_field + ' =
> (select count(sequence_no) from fnStatusPerDatesTbl(' + @.start_date +
> ',' + @.end_date + ')
> where contract_status = ' + @.contr_stat + ' group by sequence_no)
> where sequence = ' + @.seq
> Execute (@.sql_stat)
> END
> GO
>
> And when I try to run it:
>
> exec rptRRTP_By_Date_Range 'current_status', '1/1/2000', getdate(),
> '00',1
>
> I get this error:
> Msg 102, Level 15, State 1, Line 1
> Incorrect syntax near ')'.
> Looks like it doesn't like that getdate() call.
> Any feedback on this?
>|||On May 1, 12:35 pm, "Aaron Bertrand [SQL Server MVP]"
<ten...@.dnartreb.noraa> wrote:
> You can't pass a function in as a parameter. Try:
> DECLARE @.dt DATETIME;
> SET @.dt = GETDATE();
> EXEC rptRRTP_By_Date_Range 'current_status', '1/1/2000', @.dt, '00',1;
>
Great. Many thanks.|||On May 1, 12:35 pm, "Aaron Bertrand [SQL Server MVP]"
<ten...@.dnartreb.noraa> wrote:
> You can't pass a function in as a parameter. Try:
> DECLARE @.dt DATETIME;
> SET @.dt = GETDATE();
> EXEC rptRRTP_By_Date_Range 'current_status', '1/1/2000', @.dt, '00',1;
Ok, did that, but see error below.
[vbcol=seagreen]
> --
> Aaron Bertrand
> SQL Server MVPhttp://www.sqlblog.com/http://www.aspfaq.com/5006
> "d.s." <nodamnspa...@.yahoo.com> wrote in message
> news:1178047866.417792.32680@.h2g2000hsg.googlegroups.com...
>
>
>
>
>
>
>
>
Hmmm...I'm getting this error:
Msg 241, Level 16, State 1, Procedure rptRRTP_By_Date_Range, Line 20
Conversion failed when converting datetime from character string.
which I'm assuming is for the line of code immediately above,
specifically the @.end_date part.
[vbcol=seagreen]
>
>|||Hi d.s.
Concatenation requires string values (char, varchar, etc.). You get the
error when concatenating your datetime parameter into the table name string.
You could try declaring your parameters as type varchar, but you have to be
aware that the actual string that results will depend on your regional
settings for displaying dates. If you already have these tables created,
expecting particular date formats for the table names, you'll need to be
really careful about how your dates are converted. Look up the CONVERT
function to see all the possibilities.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"d.s." <nodamnspamok@.yahoo.com> wrote in message
news:1178049900.305992.123560@.h2g2000hsg.googlegroups.com...
> On May 1, 12:35 pm, "Aaron Bertrand [SQL Server MVP]"
> <ten...@.dnartreb.noraa> wrote:
>
> Ok, did that, but see error below.
>
>
>
> Hmmm...I'm getting this error:
> Msg 241, Level 16, State 1, Procedure rptRRTP_By_Date_Range, Line 20
> Conversion failed when converting datetime from character string.
> which I'm assuming is for the line of code immediately above,
> specifically the @.end_date part.
>
>
>
>|||On May 1, 1:30 pm, "Kalen Delaney" <replies@.public_newsgroups.com>
wrote:
> Hi d.s.
> Concatenation requires string values (char, varchar, etc.). You get the
> error when concatenating your datetime parameter into the table name strin
g.
> You could try declaring your parameters as type varchar, but you have to b
e
> aware that the actual string that results will depend on your regional
> settings for displaying dates. If you already have these tables created,
> expecting particular date formats for the table names, you'll need to be
> really careful about how your dates are converted. Look up the CONVERT
> function to see all the possibilities.
>
This is actually what I ended up doing, converting to varchar before
sending it on down the line. Thanks for your feedback.

error with calling stored proc with exec

Ok, I previously had a thread about "Column name as variable".
Here's a proc I wrote, and it compiles ok:
alter PROCEDURE rptRRTP_By_Date_Range
-- Add the parameters for the stored procedure here
@.Update_Field as sysname,
@.start_date as datetime,
@.end_date as datetime,
@.contr_Stat as varchar(2),
@.seq as int
AS
declare @.sql_stat as varchar(255)
BEGIN
set @.sql_stat = 'update RRTP_Scorecard set ' + @.update_field + ' = (select count(sequence_no) from fnStatusPerDatesTbl(' + @.start_date +
',' + @.end_date + ')
where contract_status = ' + @.contr_stat + ' group by sequence_no)
where sequence = ' + @.seq
Execute (@.sql_stat)
END
GO
And when I try to run it:
exec rptRRTP_By_Date_Range 'current_status', '1/1/2000', getdate(),
'00',1
I get this error:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ')'.
Looks like it doesn't like that getdate() call.
Any feedback on this?You can't pass a function in as a parameter. Try:
DECLARE @.dt DATETIME;
SET @.dt = GETDATE();
EXEC rptRRTP_By_Date_Range 'current_status', '1/1/2000', @.dt, '00',1;
--
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"d.s." <nodamnspamok@.yahoo.com> wrote in message
news:1178047866.417792.32680@.h2g2000hsg.googlegroups.com...
> Ok, I previously had a thread about "Column name as variable".
> Here's a proc I wrote, and it compiles ok:
>
> alter PROCEDURE rptRRTP_By_Date_Range
> -- Add the parameters for the stored procedure here
> @.Update_Field as sysname,
> @.start_date as datetime,
> @.end_date as datetime,
> @.contr_Stat as varchar(2),
> @.seq as int
> AS
> declare @.sql_stat as varchar(255)
> BEGIN
> set @.sql_stat = 'update RRTP_Scorecard set ' + @.update_field + ' => (select count(sequence_no) from fnStatusPerDatesTbl(' + @.start_date +
> ',' + @.end_date + ')
> where contract_status = ' + @.contr_stat + ' group by sequence_no)
> where sequence = ' + @.seq
> Execute (@.sql_stat)
> END
> GO
>
> And when I try to run it:
>
> exec rptRRTP_By_Date_Range 'current_status', '1/1/2000', getdate(),
> '00',1
>
> I get this error:
> Msg 102, Level 15, State 1, Line 1
> Incorrect syntax near ')'.
> Looks like it doesn't like that getdate() call.
> Any feedback on this?
>|||On May 1, 12:35 pm, "Aaron Bertrand [SQL Server MVP]"
<ten...@.dnartreb.noraa> wrote:
> You can't pass a function in as a parameter. Try:
> DECLARE @.dt DATETIME;
> SET @.dt = GETDATE();
> EXEC rptRRTP_By_Date_Range 'current_status', '1/1/2000', @.dt, '00',1;
>
Great. Many thanks.|||On May 1, 12:35 pm, "Aaron Bertrand [SQL Server MVP]"
<ten...@.dnartreb.noraa> wrote:
> You can't pass a function in as a parameter. Try:
> DECLARE @.dt DATETIME;
> SET @.dt = GETDATE();
> EXEC rptRRTP_By_Date_Range 'current_status', '1/1/2000', @.dt, '00',1;
Ok, did that, but see error below.
> --
> Aaron Bertrand
> SQL Server MVPhttp://www.sqlblog.com/http://www.aspfaq.com/5006
> "d.s." <nodamnspa...@.yahoo.com> wrote in message
> news:1178047866.417792.32680@.h2g2000hsg.googlegroups.com...
>
> > Ok, I previously had a thread about "Column name as variable".
> > Here's a proc I wrote, and it compiles ok:
> > alter PROCEDURE rptRRTP_By_Date_Range
> > -- Add the parameters for the stored procedure here
> > @.Update_Field as sysname,
> > @.start_date as datetime,
> > @.end_date as datetime,
> > @.contr_Stat as varchar(2),
> > @.seq as int
> > AS
> > declare @.sql_stat as varchar(255)
> > BEGIN
> > set @.sql_stat = 'update RRTP_Scorecard set ' + @.update_field + ' => > (select count(sequence_no) from fnStatusPerDatesTbl(' + @.start_date +
> > ',' + @.end_date + ')
Hmmm...I'm getting this error:
Msg 241, Level 16, State 1, Procedure rptRRTP_By_Date_Range, Line 20
Conversion failed when converting datetime from character string.
which I'm assuming is for the line of code immediately above,
specifically the @.end_date part.
> > where contract_status = ' + @.contr_stat + ' group by sequence_no)
> > where sequence = ' + @.seq
> > Execute (@.sql_stat)
> > END
> > GO|||Hi d.s.
Concatenation requires string values (char, varchar, etc.). You get the
error when concatenating your datetime parameter into the table name string.
You could try declaring your parameters as type varchar, but you have to be
aware that the actual string that results will depend on your regional
settings for displaying dates. If you already have these tables created,
expecting particular date formats for the table names, you'll need to be
really careful about how your dates are converted. Look up the CONVERT
function to see all the possibilities.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"d.s." <nodamnspamok@.yahoo.com> wrote in message
news:1178049900.305992.123560@.h2g2000hsg.googlegroups.com...
> On May 1, 12:35 pm, "Aaron Bertrand [SQL Server MVP]"
> <ten...@.dnartreb.noraa> wrote:
>> You can't pass a function in as a parameter. Try:
>> DECLARE @.dt DATETIME;
>> SET @.dt = GETDATE();
>> EXEC rptRRTP_By_Date_Range 'current_status', '1/1/2000', @.dt, '00',1;
>
> Ok, did that, but see error below.
>
>
>> --
>> Aaron Bertrand
>> SQL Server MVPhttp://www.sqlblog.com/http://www.aspfaq.com/5006
>> "d.s." <nodamnspa...@.yahoo.com> wrote in message
>> news:1178047866.417792.32680@.h2g2000hsg.googlegroups.com...
>>
>> > Ok, I previously had a thread about "Column name as variable".
>> > Here's a proc I wrote, and it compiles ok:
>> > alter PROCEDURE rptRRTP_By_Date_Range
>> > -- Add the parameters for the stored procedure here
>> > @.Update_Field as sysname,
>> > @.start_date as datetime,
>> > @.end_date as datetime,
>> > @.contr_Stat as varchar(2),
>> > @.seq as int
>> > AS
>> > declare @.sql_stat as varchar(255)
>> > BEGIN
>> > set @.sql_stat = 'update RRTP_Scorecard set ' + @.update_field + ' =>> > (select count(sequence_no) from fnStatusPerDatesTbl(' + @.start_date +
>> > ',' + @.end_date + ')
>
> Hmmm...I'm getting this error:
> Msg 241, Level 16, State 1, Procedure rptRRTP_By_Date_Range, Line 20
> Conversion failed when converting datetime from character string.
> which I'm assuming is for the line of code immediately above,
> specifically the @.end_date part.
>
>
>> > where contract_status = ' + @.contr_stat + ' group by sequence_no)
>> > where sequence = ' + @.seq
>> > Execute (@.sql_stat)
>> > END
>> > GO
>|||On May 1, 1:30 pm, "Kalen Delaney" <replies@.public_newsgroups.com>
wrote:
> Hi d.s.
> Concatenation requires string values (char, varchar, etc.). You get the
> error when concatenating your datetime parameter into the table name string.
> You could try declaring your parameters as type varchar, but you have to be
> aware that the actual string that results will depend on your regional
> settings for displaying dates. If you already have these tables created,
> expecting particular date formats for the table names, you'll need to be
> really careful about how your dates are converted. Look up the CONVERT
> function to see all the possibilities.
>
This is actually what I ended up doing, converting to varchar before
sending it on down the line. Thanks for your feedback.

error with calling stored proc with exec

Ok, I previously had a thread about "Column name as variable".
Here's a proc I wrote, and it compiles ok:
alter PROCEDURE rptRRTP_By_Date_Range
-- Add the parameters for the stored procedure here
@.Update_Field as sysname,
@.start_date as datetime,
@.end_date as datetime,
@.contr_Stat as varchar(2),
@.seq as int
AS
declare @.sql_stat as varchar(255)
BEGIN
set @.sql_stat = 'update RRTP_Scorecard set ' + @.update_field + ' =
(select count(sequence_no) from fnStatusPerDatesTbl(' + @.start_date +
',' + @.end_date + ')
where contract_status = ' + @.contr_stat + ' group by sequence_no)
where sequence = ' + @.seq
Execute (@.sql_stat)
END
GO
And when I try to run it:
exec rptRRTP_By_Date_Range 'current_status', '1/1/2000', getdate(),
'00',1
I get this error:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ')'.
Looks like it doesn't like that getdate() call.
Any feedback on this?
You can't pass a function in as a parameter. Try:
DECLARE @.dt DATETIME;
SET @.dt = GETDATE();
EXEC rptRRTP_By_Date_Range 'current_status', '1/1/2000', @.dt, '00',1;
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"d.s." <nodamnspamok@.yahoo.com> wrote in message
news:1178047866.417792.32680@.h2g2000hsg.googlegrou ps.com...
> Ok, I previously had a thread about "Column name as variable".
> Here's a proc I wrote, and it compiles ok:
>
> alter PROCEDURE rptRRTP_By_Date_Range
> -- Add the parameters for the stored procedure here
> @.Update_Field as sysname,
> @.start_date as datetime,
> @.end_date as datetime,
> @.contr_Stat as varchar(2),
> @.seq as int
> AS
> declare @.sql_stat as varchar(255)
> BEGIN
> set @.sql_stat = 'update RRTP_Scorecard set ' + @.update_field + ' =
> (select count(sequence_no) from fnStatusPerDatesTbl(' + @.start_date +
> ',' + @.end_date + ')
> where contract_status = ' + @.contr_stat + ' group by sequence_no)
> where sequence = ' + @.seq
> Execute (@.sql_stat)
> END
> GO
>
> And when I try to run it:
>
> exec rptRRTP_By_Date_Range 'current_status', '1/1/2000', getdate(),
> '00',1
>
> I get this error:
> Msg 102, Level 15, State 1, Line 1
> Incorrect syntax near ')'.
> Looks like it doesn't like that getdate() call.
> Any feedback on this?
>
|||On May 1, 12:35 pm, "Aaron Bertrand [SQL Server MVP]"
<ten...@.dnartreb.noraa> wrote:
> You can't pass a function in as a parameter. Try:
> DECLARE @.dt DATETIME;
> SET @.dt = GETDATE();
> EXEC rptRRTP_By_Date_Range 'current_status', '1/1/2000', @.dt, '00',1;
>
Great. Many thanks.
|||On May 1, 12:35 pm, "Aaron Bertrand [SQL Server MVP]"
<ten...@.dnartreb.noraa> wrote:
> You can't pass a function in as a parameter. Try:
> DECLARE @.dt DATETIME;
> SET @.dt = GETDATE();
> EXEC rptRRTP_By_Date_Range 'current_status', '1/1/2000', @.dt, '00',1;
Ok, did that, but see error below.
[vbcol=seagreen]
> --
> Aaron Bertrand
> SQL Server MVPhttp://www.sqlblog.com/http://www.aspfaq.com/5006
> "d.s." <nodamnspa...@.yahoo.com> wrote in message
> news:1178047866.417792.32680@.h2g2000hsg.googlegrou ps.com...
>
>
>
>
Hmmm...I'm getting this error:
Msg 241, Level 16, State 1, Procedure rptRRTP_By_Date_Range, Line 20
Conversion failed when converting datetime from character string.
which I'm assuming is for the line of code immediately above,
specifically the @.end_date part.
[vbcol=seagreen]
>
|||Hi d.s.
Concatenation requires string values (char, varchar, etc.). You get the
error when concatenating your datetime parameter into the table name string.
You could try declaring your parameters as type varchar, but you have to be
aware that the actual string that results will depend on your regional
settings for displaying dates. If you already have these tables created,
expecting particular date formats for the table names, you'll need to be
really careful about how your dates are converted. Look up the CONVERT
function to see all the possibilities.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"d.s." <nodamnspamok@.yahoo.com> wrote in message
news:1178049900.305992.123560@.h2g2000hsg.googlegro ups.com...
> On May 1, 12:35 pm, "Aaron Bertrand [SQL Server MVP]"
> <ten...@.dnartreb.noraa> wrote:
>
> Ok, did that, but see error below.
>
>
>
> Hmmm...I'm getting this error:
> Msg 241, Level 16, State 1, Procedure rptRRTP_By_Date_Range, Line 20
> Conversion failed when converting datetime from character string.
> which I'm assuming is for the line of code immediately above,
> specifically the @.end_date part.
>
>
>
|||On May 1, 1:30 pm, "Kalen Delaney" <replies@.public_newsgroups.com>
wrote:
> Hi d.s.
> Concatenation requires string values (char, varchar, etc.). You get the
> error when concatenating your datetime parameter into the table name string.
> You could try declaring your parameters as type varchar, but you have to be
> aware that the actual string that results will depend on your regional
> settings for displaying dates. If you already have these tables created,
> expecting particular date formats for the table names, you'll need to be
> really careful about how your dates are converted. Look up the CONVERT
> function to see all the possibilities.
>
This is actually what I ended up doing, converting to varchar before
sending it on down the line. Thanks for your feedback.