please help me about :Unable to connect to SQL Server database.

To All Members,

When I create a new project and try to use theASP.Net web application administration :security tab, I get the following :

Thereis a problem with your selected data store. This can be caused by aninvalid server name or credentials, or by insufficient permission. Itcan also be caused by the role manager feature not being enabled. Clickthe button below to be redirected to a page where you can choose a newdata store.

The following message may help in diagnosing the problem:Unable to connect to SQL Server database.

I am usingMicrosoft Visual Web Developer 2005 Express Edition with a localMS SQL Server 2000.

I am new to all of this so a little help would be great

Thanks.

please mail me todehackers@.linuxmail.org

or izzuan@.malysia.com

Dear Izzuan,

I think the bug comes from your connection string, it should be as follows:

Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;

where

myServerAddress is the ip address or UNC name of your server (you can also use . character in place of your local server) If you are using a SQL instance, you should add it in this section: myServerAddress\instanceName|||

What I am guessing is that your DB Server does not have ASPNETDB database. First check to see if there is a database named ASPNETDB. If not, runaspnet_regsql.exe. You can find this exe in the following location:

C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727 (XP)

C:\WINNT\Microsoft.NET\Framework\v2.0.50727 (Windows Server 2003)

This exe creates ASPNETDB database in your DB Server instance.

|||

Oh thaks sago...

i`ll try the solution u gave to me, but it still same eror running...

Actually in my server i have 2 localhost are running at the same time i want

to build a new localhost that using ASP.Net configuration

but it containt eror....

my previous d.base are use by MS accsess...

if i remove the MS SQL 2000 then i install WAMP application

either the d.Base (MS Access) are still running or maybe it will be crash?

|||

Hi izzuan,

Asp.net web site administration tool is indenpent of your IIS settings. Whether you have 2 localhost running at the same time or not have very little things to do with the administration tool. So i guess it's not the problem.

The web site administration tool will only connect to your application service database(aspnnetdb), make sure you have that database build up successfully. I suggest you to follow the suggestionbullpit gave you. Run aspnet_regsql first(since you are using sql2000) and then put some new providers into your web.config (another way is to add a new connection string named "localsqserver" into your web.config and direct it to the newaspnetdb which you get from asqnet_regsql).

You can refer to this link for more information:http://msdn2.microsoft.com/en-us/library/x28wfk74.aspx

Hope my suggestion helps

Please help in solving a query..

Hello,

I need help in solving a task of my project. In my project there is a table where the student fill 6 preference of branch which he would like to join. The table look like this.

Appl_no pref1 pref2 pref3 pref4 pref5 pref6

-

125 5 4 1 6 8 2

126 2 3 4 1 7 6

127 5 2 1 6 4 3

128 2 3 5 7 1 4

The preference table looks like this:

Pref1 Branch

-

1 EEE

2 ECE

3 MECH

4 BT

5 IT

6 CIVIL

7 CHEM

8 ARCH

:

:

23 etc

I need the table which displays the preferences of students like this..

Appl_no EEE ECE MECH BT IT CIVIL CHEM ARCH

125 3 6 2 1 4 5

-

126 4 1 2 3 6 5

127 3 2 6 5 1 4

--

128 5 1 2 6 3 4

Please help me......

Regards

Ram

I try to create the query by unpivot,pivot and CTE, hope this help.

I assume that pref1 - pref6' value is unique for each Appl_no,
therefore, the value of Appl_no 128/pref6 change from 5 to 4.

Code Snippet

-- CREATE TABLE [t1565585a]
CREATE TABLE [dbo].[t1565585a]
(
[Appl_no] [int] NOT NULL,
[pref1] [int] NOT NULL,
[pref2] [int] NOT NULL,
[pref3] [int] NOT NULL,
[pref4] [int] NOT NULL,
[pref5] [int] NOT NULL,
[pref6] [int] NOT NULL,
CONSTRAINT [PK_t1565585a] PRIMARY KEY CLUSTERED
(
[Appl_no] ASC
)
) ON [PRIMARY]

insert into dbo.t1565585a values (125,5,4,1,6,8,2);
insert into dbo.t1565585a values (126,2,3,4,1,7,6);
insert into dbo.t1565585a values (127,5,2,1,6,4,3);
insert into dbo.t1565585a values (128,2,3,5,7,1,4);

-- CREATE TABLE [t1565585b]
CREATE TABLE [dbo].[t1565585b]
(
[Branch_no] [int] NOT NULL,
[Branch] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_t1565585b] PRIMARY KEY CLUSTERED
(
[Branch_no] ASC
)
) ON [PRIMARY]

insert into dbo.t1565585b values (1, 'EEE');
insert into dbo.t1565585b values (2, 'ECE');
insert into dbo.t1565585b values (3, 'MECH');
insert into dbo.t1565585b values (4, 'BT');
insert into dbo.t1565585b values (5, 'IT');
insert into dbo.t1565585b values (6, 'CIVIL');
insert into dbo.t1565585b values (7, 'CHEM');
insert into dbo.t1565585b values (8, 'ARCH');

-- execute Query
with
-- CTE: [ApplPrefBranch_no] : unpivot t1565585a
[ApplPrefBranch_no] ([Appl_no], [Pref_no], [Branch_no])
as
(
select
[Appl_no],
convert(int, substring([Pref_no], 5, 1)) as [Pref_no],
[Branch_no]
from
(
select
[Appl_no],
[pref1],
[pref2],
[pref3],
[pref4],
[pref5],
[pref6]
from dbo.t1565585a
) p
unpivot
(
[Branch_no]
for [Pref_no]
in
(
[pref1],
[pref2],
[pref3],
[pref4],
[pref5],
[pref6]
)
) pvt
),
-- CTE: [ApplPrefBranch] : replace Branch_no to Branch
[ApplPrefBranch] ([Appl_no], [Pref_no], [Branch])
as
(
select
[Appl_no],
[Pref_no],
[Branch]
from [ApplPrefBranch_no] [no]
left join [t1565585b] [b]
on [no].[Branch_no] = [b].[Branch_no]
)
-- generate result by pivot
select
[Appl_no],
[EEE],
[ECE],
[MECH],
[BT],
[IT],
[CIVIL],
[CHEM],
[ARCH]
from
(
select [Appl_no], [Branch], [Pref_no]
from [ApplPrefBranch]
) p
pivot
(
max([Pref_no])
for [Branch]
in
(
[EEE],
[ECE],
[MECH],
[BT],
[IT],
[CIVIL],
[CHEM],
[ARCH]
)
) pvt

|||

If you use sql server 2005...

Code Snippet

Create Table #application (

[Appl_no] int ,

[pref1] int ,

[pref2] int ,

[pref3] int ,

[pref4] int ,

[pref5] int ,

[pref6] int

);

Insert Into #application Values('125','5','4','1','6','8','2');

Insert Into #application Values('126','2','3','4','1','7','6');

Insert Into #application Values('127','5','2','1','6','4','3');

Insert Into #application Values('128','2','3','5','7','1','5');

Create Table #branch (

[Id] int ,

[Branch] Varchar(100)

);

Insert Into #branch Values('1','EEE');

Insert Into #branch Values('2','ECE');

Insert Into #branch Values('3','MECH');

Insert Into #branch Values('4','BT');

Insert Into #branch Values('5','IT');

Insert Into #branch Values('6','CIVIL');

Insert Into #branch Values('7','CHEM');

Insert Into #branch Values('8','ARCH');

Select U.Appl_No,U.Pref,B.Branch BranchName into #Applications

From

(Select Appl_No,1 Pref,Pref1 Branch From #application

Union All

Select Appl_No,2,Pref2 From #application

Union All

Select Appl_No,3,Pref3 From #application

Union All

Select Appl_No,4,Pref4 From #application

Union All

Select Appl_No,5,Pref5 From #application

Union All

Select Appl_No,6,Pref6 From #application) as U Join #branch B On U.Branch=B.Id

Select Appl_No,[EEE],

[ECE],

[MECH],

[BT],

[IT],

[CIVIL],

[CHEM],

[ARCH] from (Select Appl_No, Pref, BranchName From #Applications) as PP

Pivot(

Min(Pref) For BranchName in

(

[EEE],

[ECE],

[MECH],

[BT],

[IT],

[CIVIL],

[CHEM],

[ARCH]

)

) as Pvt

|||

If you use sql server 2000...

Create Table #application (

[Appl_no] int ,

[pref1] int ,

[pref2] int ,

[pref3] int ,

[pref4] int ,

[pref5] int ,

[pref6] int

);

Insert Into #application Values('125','5','4','1','6','8','2');

Insert Into #application Values('126','2','3','4','1','7','6');

Insert Into #application Values('127','5','2','1','6','4','3');

Insert Into #application Values('128','2','3','5','7','1','5');

Create Table #branch (

[Id] int ,

[Branch] Varchar(100)

);

Insert Into #branch Values('1','EEE');

Insert Into #branch Values('2','ECE');

Insert Into #branch Values('3','MECH');

Insert Into #branch Values('4','BT');

Insert Into #branch Values('5','IT');

Insert Into #branch Values('6','CIVIL');

Insert Into #branch Values('7','CHEM');

Insert Into #branch Values('8','ARCH');

Select U.Appl_No,U.Pref,B.Branch BranchName into #Applications

From

(Select Appl_No,1 Pref,Pref1 Branch From #application

Union All

Select Appl_No,2,Pref2 From #application

Union All

Select Appl_No,3,Pref3 From #application

Union All

Select Appl_No,4,Pref4 From #application

Union All

Select Appl_No,5,Pref5 From #application

Union All

Select Appl_No,6,Pref6 From #application) as U Join #branch B On U.Branch=B.Id

Select [Main].Appl_No

,[EEE].Pref [EEE]

,[ECE].Pref [ECE]

,[MECH].Pref [MECH]

,[BT].Pref [BT]

,[IT].Pref [IT]

,[CIVIL].Pref [CIVIL]

,[CHEM].Pref [CHEM]

,[ARCH].Pref [ARCH]

from

#application [Main]

Left Outer Join #Applications [EEE] On main.Appl_No = [EEE].Appl_No And [EEE].BranchName='EEE'

Left Outer Join #Applications [ECE] On main.Appl_No = [ECE].Appl_No And [ECE].BranchName='ECE'

Left Outer Join #Applications [MECH] On main.Appl_No = [MECH].Appl_No And [MECH].BranchName='MECH'

Left Outer Join #Applications [BT] On main.Appl_No = [BT].Appl_No And [BT].BranchName='BT'

Left Outer Join #Applications [IT] On main.Appl_No = [IT].Appl_No And [IT].BranchName='IT'

Left Outer Join #Applications [CIVIL] On main.Appl_No = [CIVIL].Appl_No And [CIVIL].BranchName='CIVIL'

Left Outer Join #Applications [CHEM] On main.Appl_No = [CHEM].Appl_No And [CHEM].BranchName='CHEM'

Left Outer Join #Applications [ARCH] On main.Appl_No = [ARCH].Appl_No And [ARCH].BranchName='ARCH'

|||

Hello Yoshihiro Kawabata ,

Thank you very much for helping me. I'm very much impressed.

Keep smiling,

please help fix this statement

im using an access project file now and no longer a mdb file.

i used to prompt a user for the first and last name of the patient they were looking for using this statement like [forms]![main]![text0] & "*"
and like [forms]![main]![text2] & "*" then it would promt them to enter in a last name and a first name but if they only entered a last name and left the first name prompt empty it would give them all the last name they had entered .

now im on sql as my backend and i got my query working except
if i dont type anything in on the second prompt i get no records.

how can i fix this because the user doesnt always no the first name and would like to sort through the records by just the last.

my current statement that works as long as you fill in both prompts is a follows.

SELECT tblpatientinfo.*
FROM tblpatientinfo
WHERE (lname LIKE @.LastName + '%') AND (fname LIKE @.firstname + '%')
ORDER BY chartnumberSELECT tblpatientinfo.*
FROM tblpatientinfo
WHERE (lname LIKE isnull(@.LastName,'') + '%') AND (fname LIKE isnull(@.firstname,'') + '%')
ORDER BY chartnumber|||Originally posted by Enigma
SELECT tblpatientinfo.*
FROM tblpatientinfo
WHERE (lname LIKE isnull(@.LastName,'') + '%') AND (fname LIKE isnull(@.firstname,'') + '%')
ORDER BY chartnumber

thank you very much worked great , im just having a hard time with the new syntax at least the vb codes i wrote still work.sql

Please help compatiblity between VS2003 and SQL server 2005

We have an existing project developed using VS 2003 with SQL server 2000.

Now planning to switch to SQL server 2005.

Are there any compatibility issues between VS 2003 and SQL server 2005, Plus sql server reporting services 2005.

We have been developing this project over a year and don't want to mess it. Now we are at the stage of developing reports and finidng lot of problems with sql server 2000 reporting services.

Thank you very much for the information.

We moved from VS 2003 and Sql Server 2000 to VS 2005 with Sql Server 2005 and only ran into one issue. We had one stored proc that had a Union query. This query worked differently in Sql Server 2005. Everything else was fine - no problem.

Have not worked with reporting services ...

Please guys i am confused between VS 2003 and RS 2005

Is it possible to use SQL server reporting services 2005 with Visual Studio 2003

I mean when you say new project under VS 2003 does it show under project types:

Business intelligence projects using Reporting services 2005.

Since we have done lot of work using VS 2003(framework 1.1) and now really does not want to get into using Framework 2.0 (VS2005)

But want to use just the reporting services 2005 with VS 2003.

Thank you all very much for the valuable information.

You can create RS 2000 reports with VS 2003 and deploy them to a 2005 Reporting server however you won't be able to use any of the new features of RS 2005.

I would advise you to use the SQL Server Business Intelligence Development Studio to develop RS 2005 reports.

|||

Thank you very much Adam.

How about if i use SQL Server Business Intelligence Development Studio to develop RS 2005 reports to develop reports,

Will i be able to call the reports via URL or may be webreference it via WSDL reportingservices.exe from VS 2003.

We have a requirement to develop almost 75 reports, once we develop those reports using RS 2005, ultimately we have to provide an interface in VS 2003 .aspx page to call those reports. is that possible.

Thank you.

|||

Let me just repharase to make sure I understand you correctly.

You wish develop a set of reports and have them deployed to a reporting services 2005 report server. You then need to develop a front end using VS 2003 and the .NET Framework version 1.1. You want to know whether you can reference a reporting services 2005 web service from your VS 2003 project.

I see no problem with this. You can add a webreference to a web service developed with any version. The WSDL is the definition of the web service and acts like an interface or a contract.

Please Assist... Report Templates to save time in designing

In RS 2000 I was able to create a template report and put it in the Project
Items directory, then when I would select New Report Item, I could choose my
template report instead of redesigning everything from scratch.
Where is this folder, or to better phrase this question:
Where do I put my template reports that I want to show up when I choose "Add
new report item" in the designer in SQL 2005 RS?
I have checked the folders - but obviously I am missing something.
Thanks in advance!
=-ChrisI had to search through my computer for *.rdl files. Finally I think I found
the templates at
C:\Program Files\Microsoft Visual Studio
8\Common7\IDE\PrivateAssemblies\ProjectItems\ReportProject
Kaisa M. Lindahl Lervik
"Chris Conner" <Chris.Conner@.NOSPAMPolarisLibrary.com> wrote in message
news:uOtrsue%23GHA.1220@.TK2MSFTNGP05.phx.gbl...
> In RS 2000 I was able to create a template report and put it in the
> Project
> Items directory, then when I would select New Report Item, I could choose
> my
> template report instead of redesigning everything from scratch.
> Where is this folder, or to better phrase this question:
> Where do I put my template reports that I want to show up when I choose
> "Add
> new report item" in the designer in SQL 2005 RS?
> I have checked the folders - but obviously I am missing something.
> Thanks in advance!
> =-Chris
>

placing ssis packages on server

I have created my packages and i want them to place them on the server.Do i need to place the entire project of dts packages on the server or is there any option to place executables...if so please explain....

And to run these packages on the server do i need to set them as new job at sql server agent or is there any other way i need to run on the server.

I want then to run whenever the text file gets updated is it possible to set anything for my packages to run as and when the text file gets updated..

Please help me with all my questions

Thanks in advance..

If you you are using file based packages (.dtsx); you just need to move the files to a server where Integration services service is running. Then, yes, you can use SQL Server agent to schedule its execution. You could include some conditional logic in your package to check is the text file has been updated and then run the package in a periodically basis.|||

what or how can i check to see if file is updated...is there any task...Please help me with this..

and all my files are .dtsx files So i need to move all these files.Should the server have business intelligence studio installed or can i just take these dtsx packages and execute it through sql server agent..

Thanks

|||

ok, one thing at the time. BIDS is a development tool; it is not required in the server in order to execute the packages. The server needs to have a SSIS instance up and running; the you can use dtexec (package execution utility) to run the package via command line.

Additionally, if you want to schedule a package; you need to install the DB engine and SQL Server agent components on the server.

Regarding, how to include some logic inside of the package to check if the file has been 'updated'; I know there have been similar discussions n this forum about that; here you have a couple:

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

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

|||

>>The server needs to have a SSIS instance up and running; the you can use dtexec (package execution utility) to run the package via >>command line.

Could you please tell me what ssis instance mean and about package execution utility.Sorry for very basic questions.I am not experienced with this topic.

i want my package to run automatically so i want to schedule.please let me know how to install DB engine and SQL Server agent components on the server.

Thanks

|||

Run the SQL Server setup program on the server. Choose Integration Services to get SSIS installed; choose Database Engine and Agent to get the other pieces.

Books Online has excellent help for setting up the components of SQL Server, and there are a number of walkthroughs available online if you search for "Setup SQL Server 2005".

pk constraint enforcement using SSIS to import data

Note: I'm running a bottom up design on this project as I won't know what data I'm really working with until I can get it imported and analyze it. Also, I'm not a DBA or developer, so please be gentle...

I am importing 30k+ rows using SSIS (OLEDB -DB2- source to OleDB -2k5- destination). The import works fine, but I just realized that I need to set up a pk on the row emp_ids. The problem is that in the DB2 source, the emp_ids were removed (set to whitespace, but not null). So, I can't just uncheck the 'keep nulls' option and import the data.

Any suggestions or links (using SSIS) on how to identify the rows where emp_id = "whitespaces" and 1) either keep them from being imported, or 2) remove them afterwards?

(I suppose this could be done using sql statement to identify the whitespace rows, but that would present difficulties of its own due to the random spacing nature of the updates. Also, I'm hoping for a checkbox wonder solution.)

Please advise. Thanks!

- Isaac

Why not use a conditional split to look for NULLS and NULLS resulting from a TRIM() operation.

So TRIM() your data in the conditional split, and then test that for NULL. If it matches, then you can use that tagged output stream to do with it whatever you wish... You can throw them away, you can push them to their own destination (flat file, SQL server, etc...)|||

That worked perfectly. Thanks for the advice Phil!

- Isaac

|||

While experimenting, I also found that the sort transform can accomplish this task. Not only are the rows with whitespaces removed, but this task also removes duplicate ids from the list... two birds with one stone (using a sort task (with delete dups) vs a trim split).

Awesome... once again thanks!

- Isaac

|||

isaacb wrote:

While experimenting, I also found that the sort transform can accomplish this task. Not only are the rows with whitespaces removed, but this task also removes duplicate ids from the list... two birds with one stone (using a sort task (with delete dups) vs a trim split).

Awesome... once again thanks!

- Isaac

Hmm... I don't like that... I don't like that the sort transformation removes rows with spaces in them. For that matter, I don't want it to remove NULLs either. Getting rid of duplicates, yes, but I would think your resultset would be reduced to just one row with spaces, as opposed to none. Are you sure it just discarded ALL rows that were "empty"?|||

In looking at it again, it's not perfect as it does leave one duplicate whitespace row (the first one that it finds). While that shouldn't be acceptable in a real world scenario, it works for the first rough pass on my project.

The sort/delete functionality actually works rather well when you select your pk as the row to "sort" on. It only checks against the rows that you specify, so all the verified data is still there. I checked the results against a report that I pulled off the server... I eyeballed it for a few minutes, but it seems to be accurate.

Maybe I'm mis-using the functionality (?), but it works...

|||

isaacb wrote:

In looking at it again, it's not perfect as it does leave one duplicate whitespace row (the first one that it finds). While that shouldn't be acceptable in a real world scenario, it works for the first rough pass on my project.

The sort/delete functionality actually works rather well when you select your pk as the row to "sort" on. It only checks against the rows that you specify, so all the verified data is still there. I checked the results against a report that I pulled off the server... I eyeballed it for a few minutes, but it seems to be accurate.

Maybe I'm mis-using the functionality (?), but it works...

No, using the sort transformation to remove duplicates is a very valid use. And you get sorted data which helps is most cases for downstream transformations...

I was just concerned when you said it removed all of the rows with spaces, and it did what it's supposed to do which was to remove duplicates and therefore leave one row behind.