HELP!! SQL Server Express 2005 engine connection to my DB

Preface, I am just starting to learn about database and web development.

I installed VS.net 2005 which updated my web site project file and automatically attached it to a database. It connected to ".\app_data\aspnetdb.mdf". However it messed something up and the connection didn't work. If I create a project from scratch, it connects to its DB just fine, but not my upgraded project.

However, when I publish my web site, I need to use ODBC. So, my thought was to simply set up the system locally in a way that will make it easy to connect on the web site. I was told to add a system DSN to the "Microsoft ODBC Administrator". I tried to add a "SQL Server" and give it the database name but it failed to connect.

So, then I ran the SQL Server Configuration Manager and looked at the server properties and found this string under the advanced -> startup parameters.."-dc:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf;-ec:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG;-lc:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf"

My database is in an entirely different folder. The database it looks like it is servicing is a "master.mdf" database installed under the SQL server install folder. So, I figured that I found my problem. I just have to change this file to map to mine. I replaced these three strings with paths to my aspnetdb.mdf/ldf files, and created a LOG folder. Howwever, it wasn't able to connect to this database. It said something like "it doesn't exist or you don't have permission", even though I gave it the right path.

So, I'm at a loss for how to get my application to be able to use the ODBC interface to connect to its very own database on my local system.

What do I need to do? Please help!

You need to create a Windows Authentication account on the server for ASPNET with a password.

When an .aspx page loads and tries to access the server, it uses Windows Authentication unless you code the connectionstring to use SQL authentication.

After you create the ASPNET Account, make your connection string look like this:

connectionString="Data Source=ODC01;Initial Catalog=OEM;Integrated Security=True"

Adamus

|||

Thanks you for the feedback. Unfortunately I'm still a bit confused. My problem is that I don't have a complete picture of how this is supposed to work.

Are you saying that I must set up a the server to use an account that uses Windows Authentication before I can attach it to my own MDB file? In other words, it will only connect to the default master.mdb file until I create an account?

Also, using "local" or "system" isn't good enough, I have to specify a user account even on my own personal computer running XP? I am the only one who uses this system, and there is no windows server running on the network. This is my home setup.

This is the map that I understand. There are 3 connections that must be made for this to work.

mydb.mdb --1 (get server to serve my DB)--> SQL Server express 2005 --2 (tell ODBC about my DB exposed by the server)--> ODBC --3 (using the connection string)--> Website.

Right now I beleive none of the 3 connections are working, and I feel like I need to get the step 1 done before I can establish and verify step 2 and then finally use the connection string in step 3.

Are you saying that I must create a Windows Authentication user account before step 1 will work? Does that simply meen that I create a user called "SQL Server" with minimal access on the computer and log into it whenever I want it to connect?

Is there a "complete idiots guide to establishing a SQL server 2005 connection from your web site to your database" book that I could buy?

|||

I take it all back. You were right. Now I understand. I actually had the account set up correctly on the server side, but I wasn't logging in correctly.

Second, when I tried to attach the server to ODBC I was using my computer name alone, when I needed to use <computer_name>\SQLExpress.

Now I have to figure out the connection string. But I am finally over my biggest hurdle. Thanks!

HELP!! SQL Server Express 2005 engine connection to my DB

Preface, I am just starting to learn about database and web development.

I installed VS.net 2005 which updated my web site project file and automatically attached it to a database. It connected to ".\app_data\aspnetdb.mdf". However it messed something up and the connection didn't work. If I create a project from scratch, it connects to its DB just fine, but not my upgraded project.

However, when I publish my web site, I need to use ODBC. So, my thought was to simply set up the system locally in a way that will make it easy to connect on the web site. I was told to add a system DSN to the "Microsoft ODBC Administrator". I tried to add a "SQL Server" and give it the database name but it failed to connect.

So, then I ran the SQL Server Configuration Manager and looked at the server properties and found this string under the advanced -> startup parameters.."-dc:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf;-ec:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG;-lc:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf"

My database is in an entirely different folder. The database it looks like it is servicing is a "master.mdf" database installed under the SQL server install folder. So, I figured that I found my problem. I just have to change this file to map to mine. I replaced these three strings with paths to my aspnetdb.mdf/ldf files, and created a LOG folder. Howwever, it wasn't able to connect to this database. It said something like "it doesn't exist or you don't have permission", even though I gave it the right path.

So, I'm at a loss for how to get my application to be able to use the ODBC interface to connect to its very own database on my local system.

What do I need to do? Please help!

You need to create a Windows Authentication account on the server for ASPNET with a password.

When an .aspx page loads and tries to access the server, it uses Windows Authentication unless you code the connectionstring to use SQL authentication.

After you create the ASPNET Account, make your connection string look like this:

connectionString="Data Source=ODC01;Initial Catalog=OEM;Integrated Security=True"

Adamus

|||

Thanks you for the feedback. Unfortunately I'm still a bit confused. My problem is that I don't have a complete picture of how this is supposed to work.

Are you saying that I must set up a the server to use an account that uses Windows Authentication before I can attach it to my own MDB file? In other words, it will only connect to the default master.mdb file until I create an account?

Also, using "local" or "system" isn't good enough, I have to specify a user account even on my own personal computer running XP? I am the only one who uses this system, and there is no windows server running on the network. This is my home setup.

This is the map that I understand. There are 3 connections that must be made for this to work.

mydb.mdb --1 (get server to serve my DB)--> SQL Server express 2005 --2 (tell ODBC about my DB exposed by the server)--> ODBC --3 (using the connection string)--> Website.

Right now I beleive none of the 3 connections are working, and I feel like I need to get the step 1 done before I can establish and verify step 2 and then finally use the connection string in step 3.

Are you saying that I must create a Windows Authentication user account before step 1 will work? Does that simply meen that I create a user called "SQL Server" with minimal access on the computer and log into it whenever I want it to connect?

Is there a "complete idiots guide to establishing a SQL server 2005 connection from your web site to your database" book that I could buy?

|||

I take it all back. You were right. Now I understand. I actually had the account set up correctly on the server side, but I wasn't logging in correctly.

Second, when I tried to attach the server to ODBC I was using my computer name alone, when I needed to use <computer_name>\SQLExpress.

Now I have to figure out the connection string. But I am finally over my biggest hurdle. Thanks!

HELP!! - TSQL Cursor problem

Hi,

I have a work project due very soon and am stuck with something.

I am using a cursor in a stored procedure to return the required data for output in an ASP/VBScript page. The problem is that (as run in MS Query Analyser) the stored procedure returns the data in individual data sets - 1 for each iteration of the cursor. It is returned in multiple frames in QA, the same as when you run multiple queries at the same time in the QA window.

I have never used cursors before so maybe this is to be expected - but what I want is for all the data to be returned in one data set. At present I can only access part of the data in my webpage - when I run the stored procedure and loop thru the data in the webpage, there is only the data from the first iteration of the cursor.

Below is a representation of a chunk of rows from the table, the stored procedure and a representation of the returned results. Can you please help me to return all the data in a single data set, or else tell me how I can access each of the data sets in my webpage.

Many thanks in advance for your help

Simon

simon.barnettnospam@.elexon.co.uk

Table

ID_col, Category_col, KeyAccountability_col, PerformanceMeasure_col, StaffID_col
1, Delivery, KeyAcc1, PerfMeas1, 3
3, Delivery, KeyAcc2, PerfMeas2, 3
7, Delivery, KeyAcc3, PerfMeas3, 3
8, Department, KeyAcc4, PerfMeas4, 3
11, Department, KeyAcc5, PerfMeas5, 3
12, Department, KeyAcc6, PerfMeas6, 3
13, Communications, KeyAcc7, PerfMeas7, 3
16, Communications, KeyAcc8, PerfMeas8, 3

Stored Procedure

declare @.var0 nchar(56)
declare @.var1 nchar(56)
declare keyaccscursor cursor for
(SELECT distinct category from
[CareerFramework].[dbo].[KeyAccountability] where jobprofileid = @.jobprofileID)
OPEN keyaccscursor
FETCH NEXT FROM keyaccscursor
INTO @.var1
WHILE @.@.FETCH_STATUS = 0
BEGIN
select distinct KeyAccountability as col1, 'keyacc' as rowtype from KeyAccountability where (category = @.var1) and (jobprofileid = @.jobprofileID)
union
select distinct category as col1, 'cat' as rowtype from KeyAccountability where (category = @.var1) and (jobprofileid = @.jobprofileID)
FETCH NEXT FROM keyaccscursor
INTO @.var1
END
CLOSE keyaccscursor
DEALLOCATE keyaccscursor

Results (when run in MSSQL Query Analyser )

-

KeyAccountability PerformanceMeasure (column headings)

Delivery

KeyAcc1 PerfMeas1

KeyAcc2 PerfMeas2

KeyAcc3 PerfMeas3

-

KeyAccountability PerformanceMeasure (column headings)

Department

KeyAcc4 PerfMeas3

KeyAcc5 PerfMeas4

KeyAcc6 PerfMeas5

-

KeyAccountability PerformanceMeasure (column headings)

Communications

KeyAcc7 PerfMeas6

KeyAcc7 PerfMeas7

I understand you have to present the following data

3, Delivery, KeyAcc2, PerfMeas2, 3
7, Delivery, KeyAcc3, PerfMeas3, 3
8, Department, KeyAcc4, PerfMeas4, 3
11, Department, KeyAcc5, PerfMeas5, 3
12, Department, KeyAcc6, PerfMeas6, 3
13, Communications, KeyAcc7, PerfMeas7, 3
16, Communications, KeyAcc8, PerfMeas8, 3

As

Delivery

KeyAcc2, PerfMeas2
KeyAcc3, PerfMeas3

And

Department

KeyAcc4, PerfMeas4, 3
KeyAcc5, PerfMeas5, 3

KeyAcc6, PerfMeas6, 3

And so on.

I should use a DropDownList cotrol with sqlsource ?SELECT distinct category from
[CareerFramework].[dbo].[KeyAccountability] where jobprofileid = @.jobprofileID”

And a GridView control with

Select KeyAccountability, PerformanceMeasure from KeyAccountability where Category=@.Categ

And @.categ is linked with DropDownList value.

Hope this help

|||

To fix your query for your requirement,

Code Snippet

declare @.var0 nchar(56)

declare @.var1 nchar(56)

declare @.jobprofileID as int

Set @.jobprofileID = 1

declare @.result table

(

col1 varchar(1000),

rowtype varchar(10)

)

declare keyaccscursor cursor for

(select distinct [category] from [keyaccountability] where jobprofileid = @.jobprofileid)

open keyaccscursor

fetch next from keyaccscursor

into @.var1

while @.@.fetch_status = 0

begin

Insert Into @.result

select keyaccountability as col1, 'keyacc' as rowtype from keyaccountability where (category = @.var1) and (jobprofileid = @.jobprofileid)

union

select category as col1, 'cat' as rowtype from keyaccountability where (category = @.var1) and (jobprofileid = @.jobprofileid)

fetch next from keyaccscursor

into @.var1

end

close keyaccscursor

deallocate keyaccscursor

Select * from @.result

|||

You can achieve the result without cursor also, (Highly recommended)

Code Snippet

declare @.jobprofileID as int

set @.jobprofileID = 1

select keyaccountability as col1, 'keyacc' as rowtype from keyaccountability where

(jobprofileid = @.jobprofileid)

union

select category as col1, 'cat' as rowtype from keyaccountability where

(jobprofileid = @.jobprofileid)

|||

Thanks very much for this - inserting into a table during the loop gives me the single data set - exactly as required.

There are 2 problems though, which may be related.

1.

The first is that I cannot declare a table in memory.

declare @.result table

(

col1 varchar(1000),

rowtype varchar(10)

)

gives me the following error:

Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'table'.

(When I change to creating a permanent table using CREATE table, and run it in QA, the correct data populates the table.)

2.

When adding this into my stored procedure and running my webpage, the page breaks with the message that the recordset cannot be looped thru because it is closed.

When I comment out the insert statement but leave in the CREATE statement, the page works as it did before.

If you can help with this I would be very grateful.

Many thanks

Simon

|||

Hi,

I tried this also but although all the required results are returned as a single data set, KeyAccountabilities are not associated with the Categories as required.

All the Categories are listed in the top rows and then the KeyAccountabilities are listed in random order below. Looking at the SQL I can't see a way to group them as required using this method.

Would it be possible?

Many thanks

Simon

declare @.jobprofileID as int

set @.jobprofileID = 1

select keyaccountability as col1, 'keyacc' as rowtype from keyaccountability where

(jobprofileid = @.jobprofileid)

union

select category as col1, 'cat' as rowtype from keyaccountability where

(jobprofileid = @.jobprofileid)

|||

I am really wondering how its happen. What is the version of SQL Server you are using.

To find,

Select @.@.VERSION

Even on your ASP page you can change the RecordSet behaviour as UseClient istead of UseServer. It will fix the issue.

|||

The following query may fix your group/order issue,

Code Snippet

declare @.jobprofileID as int

set @.jobprofileID = 1

Select col1,rowtype from

(

select category,keyaccountability as col1, 'keyacc' as rowtype from keyaccountability where

(jobprofileid = @.jobprofileid)

Union

select category,category as col1, 'cat' as rowtype from keyaccountability where

(jobprofileid = @.jobprofileid)

) as Data

Order By

category, Case When rowtype='cat' Then 1 Else 2 End, Col1

|||Thank you

sql

Help! Top N in SQL Server?

Hi,

I'm working on a SQL Server project right now, and I'm not sure how to
approach one part. Basically I have a table full of orders for a
software program to process, then mark with the date/time to show it's
been finished.

What's in the Q at any time could be a few orders, or several hundred
thousand. So I don't want to return the whole query; only the first
chunk, then when the program is done with that it can move on and grab
more.

That means SELECT TOP N, except that N needs to be a variable. When
CPU and network traffic are free it should grab more rows, and when
demand is high, it should have a coffee break.

I've tried:

Create Procedure vwAutoQ
@.GrabRowCount Int = 100
As

Select Top @.GrabRowCount
[...]

From
[...]

Where
[...]

Order By
[...]

And I get the following error:

Server: Msg 170, Level 15, State 1, Procedure vwAutoQ, Line 5
Line 5: Incorrect syntax near '@.GrabRowCount'.

Is this possible, what I'm trying to do? Otherwise I'll need to drop
it down to ~15 and fire the proc a bunch of times...On 22 Nov 2004 18:17:28 -0800, Thug Passion wrote:

(snip)
>That means SELECT TOP N, except that N needs to be a variable. When
>CPU and network traffic are free it should grab more rows, and when
>demand is high, it should have a coffee break.

Hi Thug,

You can't use a variable on the TOP keyword. But there is a workaround:
use SET ROWCOUNT. This will take a variable.

SET ROWCOUNT @.GrabRowCount
SELECT ...
FROM ...
WHERE ...
ORDER BY ...
SET ROWCOUNT 0

(Don't forget to set rowcount back to 0 after the query, as this is a
sticky setting: the limited rowcount remains active until you reset it or
drop the connection)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Various paging techniques discussed here:
http://www.aspfaq.com/2120

--
David Portas
SQL Server MVP
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message news:<RIydnb-xceGbSz7cRVn-hA@.giganews.com>...
> Various paging techniques discussed here:
> http://www.aspfaq.com/2120

An alternative is to use Dynamic SQL, that is, assign your SQL to an
nvarchar variable substituting in your number of rows and then use the
EXEC command to execute it

DECLARE @.sSQL NVARCHAR(500)

SELECT @.sSQL = 'SELECT TOP ' + CONVERT(NVARCHAR,@.iNoRows) + ' rest of
string ' ...

EXEC(@.sSQL)|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message news:<RIydnb-xceGbSz7cRVn-hA@.giganews.com>...
> Various paging techniques discussed here:
> http://www.aspfaq.com/2120

An alternative is to use Dynamic SQL, that is, assign your SQL to an
nvarchar variable substituting in your number of rows and then use the
EXEC command to execute it

DECLARE @.sSQL NVARCHAR(500)

SELECT @.sSQL = 'SELECT TOP ' + CONVERT(NVARCHAR,@.iNoRows) + ' rest of
string ' ...

EXEC(@.sSQL)|||> Hi Thug,

Hi!

> You can't use a variable on the TOP keyword. But there is a workaround:
> use SET ROWCOUNT. This will take a variable.

Awesome! I love it! That gets me exactly what I need, and except for
those two lines it doesn't change my SQL at all. I had no idea I
could use a variable with that type of (non-relational) command -
thanks very much!!|||> DECLARE @.sSQL NVARCHAR(500)

Hi,

Thanks for the response! I try to avoid this approach whenever
possible, it's gotten me in trouble in the past. I had a search
function in an SP that built a dynamic SQL command to take advantage
of indexes on whatever fields were passed in ( instead of a bunch of
like '%' statements ).

I declared a varchar(2000) to hold my command, and if I passed in
enough parameters, it came up to about 2300. But that was the last
thing I ever thought of to check...

Help! this is so basic for some of you!

HI! i am a college student and i need help with my PL/SQL project. we have to create a package with subprograms (1 private and 1 public).

the public function will accept a SSN as a parameter and return a formatted SSN (###-##-####) or an error message that identifies what test failed and why the SSN was not good, no testing will occur in the public function.

the public function will call a private function/procedure in the same package. the private program unit will test a parameter that is passed in by the public function:
--the parameter passed in may not contain any alphas
--may not contain all the same numbers (111-11-1111)
--will accept only the following values as good:
123456789
123-45-6789
123-456789
12345-6789

create a table called Test_SSN with one column (varchar2(11)). create a database trigger that calls your packaged function to test the SSN before inserting it in the table.

If you have ANY suggestions, PLEASE RESPOND!!! My teacher gave me a hint to use counter:=counter + 1. HELP HELP HELP!!!Post what you have managed to do yourself, and people can offer suggestions and corrections. There is no educational value in subcontracting your homework to others!|||Sorry, i wasnt trying to get someone to do it for me, ok, i have made a couple of tests, they are anonymous blocks right now, but they will be part of my functions in my package..

DECLARE
SSN VARCHAR(100) := '123-45-6789';
BEGIN
IF LENGTH(SSN) <9 OR LENGTH(SSN) >11 THEN
RAISE_APPLICATION_ERROR(-20001,'SSN MUST BE BETWEEN 9 AND 11 CHARACTERS');
ELSE
IF LENGTH(SSN) = 11 AND
SUBSTR(SSN, '-') = 4 AND
SUBSTR(SSN, '-',2) =7 THEN
DSMS_OUTPUT.PUT_LINE('SSN FORMAT CORRECT');
ELSE
IF LENGTH (SSN) = 10 AND
SUBSTR(SSN, '-') = 4 OR
SUBSTR(SSN, '-') = 6 THEN
DBMS_OUTPUT.PUT_LINE('SSN FORMAT CORRECT');
ELSE
IF LENGTH = 9 AND
SSN NOT LIKE '%-%' THEN
DBMS_OUTPUT.PUT_LINE('SSN FORMAT CORRECT');
ELSE RAISE_APPLICATION_ERROR(-20001,'HYPHEN ENTERED
INCORRECTLY');
END IF;
END IF;
END IF;
END IF;
END;
/

To test to make sure the SSN does not contain any alphas, must i repeatedly continue on like this : SSN NOT LIKE '%a%' AND SSN NOT LIKE '%b%' ...... and so on? Any help will be Extremely Appreciated!!|||Originally posted by oraculous
To test to make sure the SSN does not contain any alphas, must i repeatedly continue on like this : SSN NOT LIKE '%a%' AND SSN NOT LIKE '%b%' ...... and so on? Any help will be Extremely Appreciated!!
The TRANSLATE function will be helpful here:

TRANSLATE( ssn, 'x0123456789-', 'x' )

This will translate all digits and '-' to NULL, leaving behind any other (invalid) characters. So if the result is NOT NULL that means there were some invalid characters in the string.

The 'x' is just there as a dummy, to prevent the 3rd argument being '', which doesn't work. It gets translated to 'x', so doesn't affect the result.

You could test and display the invalid chars like this:

v_invalid VARCHAR2(11);
...
v_invalid := TRANSLATE( ssn, 'x0123456789-', 'x' );
if v_invalid is not null then
raise_application_error(-20001,'Invalid chars in SSN: '||v_invalid);
end if;|||thank you so much!! i didnt even think about translate...you are a great help...it is appreciated!!|||Does anyone know how i can use a loop if i use something like TEMP_SSN VARCHAR2 := SUBSTR(SSN,1,3)||SUBSTR(SSN,5,2)||SUBSTR(SSN,8,4) and then use a counter:=counter + 1...??

does anyone know how i could use this or if it would work here instead of using all those [SUBSTR(SSN,1) <> '-' AND].....???

CREATE PROCEDURE SSN_PROC (SSN VARCHAR2)
-- SSN VARCHAR(100) := '123-45-6789';
SSN_INVALID VARCHAR2 (11):=TRANSLATE(SSN,'x0123456789-','x');
TEMP_SSN VARCHAR2;
BEGIN
IF LENGTH(SSN) <9 OR LENGTH(SSN) >11 THEN
RAISE_APPLICATION_ERROR(-20001,'SSN Must Be Between 9 And 11 Characters');
ELSE
IF LENGTH(SSN) = 11 AND
INSTR(SSN, '-') = 4 AND
INSTR(SSN, '-',1,2) =7 AND
SUBSTR(SSN,1) <> '-' AND
SUBSTR(SSN,2) <> '-' AND
SUBSTR(SSN,3) <> '-' AND
SUBSTR(SSN,5) <> '-' AND
SUBSTR(SSN,6) <> '-' AND
SUBSTR(SSN,8) <> '-' AND
SUBSTR(SSN,9) <> '-' AND
SUBSTR(SSN,10) <> '-' AND
SUBSTR(SSN,11) <> '-' THEN
DBMS_OUTPUT.PUT_LINE('SSN Entered Correctly');
ELSE
IF LENGTH (SSN) = 10 AND
INSTR(SSN, '-') = 4 OR
INSTR(SSN, '-') = 6 THEN
DBMS_OUTPUT.PUT_LINE('SSN Entered Correctly');
ELSE
IF LENGTH = 9 AND
SSN NOT LIKE '%-%' THEN
DBMS_OUTPUT.PUT_LINE('SSN Entered Correctly');
ELSE RAISE_APPLICATION_ERROR(-20001,'Hyphen Entered Incorrectly');
IF SSN_INVALID IS NOT NULL THEN
RAISE_APPLICATION_ERROR(-20001,'Invalid Characters In SSN:'||SSN_INVALID);
END IF;
END IF;
END IF;
END IF;
END IF;
END;
/|||You can use a FOR loop:

FOR i IN 1..11 LOOP
IF i NOT IN (4,7) AND SUBSTR(SSN,i,1) = '-' THEN
RAISE_APPLICATION_ERROR(-20001,'Hyphen Entered Incorrectly');
END IF;
END LOOP;

Note the 3rd parameter to SUBSTR, i.e. the length of the substring required. You had SUBSTR(SSN,1) which is the substring from 1 to end of SSN, i.e. is equal to SSN.|||oooOOHH thanks man, you are a great help!!sql

Help! The IIF Statement in a query...

Part of the where clause in my SQL Statement is conditional. The query clause is as follows:

SELECT...

FROM...

WHERE PROJECT.COMPLETED<>-1 AND IIF(PROJECT.COST > RANGE.MINRANGE AND PROJECT.COST<RANGE.MAXRANGE , PROJECTRANGE.PROJECTRANGEID <>0 ,NULL)

I guess I didn't translate the if satement correctly because I always got errors when I tried to preview my report.

Need help analyzing the if statement for me. Thanks in advance.

What exactly are you trying to do?

Besides, the IIF you have is not syntactically correctn. IIF(<condition>, Expression if the condition is TRUE, Expression if the condition is FALSE). What you have is IIF( <condition>, <Condition>, <Value>) which is incorrect.

|||

Thanks for reply ndinakar. Here is what I am trying to do:

In the if statement, if ProjectCompleted is true(-1 means false), and if project.cost is greater than minimum range and less than max range, then the where clause should be like the following:

WHERE PROJECT.COMPLETED<>-1 ANDRANGE.PROJECTRANGEID <>0

If the Project.Cost is out of the range of minimum and max range (greater than max range or less than minimum range), then the if statement should not return anything, and the where clause will be like this:

WHERE PROJECT.COMPLETED<>-1

|||

I think I understand your question only partially. So what do you mean when you say return nothing if cost is out of the range? Do you still want to see those records or they should not be in the result set? You can probabbly put a filter on the record set accodringly.

|||Not sure if this will help but it looks to me like you are mixing your languages. IIF is for use in expressions in reporting services table cells etc. In SQL you have to use IF with BEGIN and END for your conditional statements. Have a look at this link which I found very usefulhttp://www.databasejournal.com/features/mssql/article.php/3361651sql

Help! SQL Express, Standard or Enterprise? or Access?

I'm not a developer and would like your input to compare against what a sales rep is telling me.

I'm managing a small web project that will have a database with a max of 20,000 records with less than 50 field each. It will be hit by anything from 200 to 500 people in a day (max) via Internet connection from all over with all sorts of speed.

The users will select less than 50 filters to obtain the results of the info they are looking for among the 20000 records. Most users will only choose less than 10 filters per search.

That's all that the database will do...seems to me enterprise is way too much, but since I'm not expert, need one of you to help with your input.

Thanks very much!

Hi,

you already said, thats this is a quite small web project. I would sugest using the SQL Server Express edition, also for the reason that it can be used for free. If you are experiencing speed problems which are based on the restrictions of Express (limitation in CPU and RAM) you can later easily upgrade to Standard Edition with just a backup and restore. You will have to keep in mind that if you are using Standard or Enterprise edition you will need either user CALs for named users or named machines. So as the rules of thumbs you will have to get CALs for very user connecting to the SQL Server. (Which is quite expensive if the server is accessible through the internet). The other option would be to get a processor license. Based on the per unit prices you can calculate the breakeven for each of the models. Be also aware that although you are using a web frontend where theoretically on one users accesses the database (e.g. the service account for the webserver) the licensing from above applies (multiplexing)

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

What about using a hosted solution? That would be cheapest and easiest way if it's available. You could get that for $5-10 a month.

|||

Thank you very much. We need user CALs for named machines, so I guess I need further research on cost for that.

I truly appreciate you the taking time to answer.

|||

Tricky part I found on that is that the services that seem to meet some of our need also limit what we can do. I'm still searching this possibility, but so far, we haven't found a packaged solution. We are in Denver and are scheduling to visit a couple of host sites in the next few weeks and see how well that fits us. Once all the phases of the deployment are completed, will have users throughout the state (potentially 15K-20K) so the host site can address our cpu demands...

Txs. for the response. I'll let you know if this route works...

Help! SQL Express, Standard or Enterprise? or Access?

I'm not a developer and would like your input to compare against what a sales rep is telling me.

I'm managing a small web project that will have a database with a max of 20,000 records with less than 50 field each. It will be hit by anything from 200 to 500 people in a day (max) via Internet connection from all over with all sorts of speed.

The users will select less than 50 filters to obtain the results of the info they are looking for among the 20000 records. Most users will only choose less than 10 filters per search.

That's all that the database will do...seems to me enterprise is way too much, but since I'm not expert, need one of you to help with your input.

Thanks very much!

Hi,

you already said, thats this is a quite small web project. I would sugest using the SQL Server Express edition, also for the reason that it can be used for free. If you are experiencing speed problems which are based on the restrictions of Express (limitation in CPU and RAM) you can later easily upgrade to Standard Edition with just a backup and restore. You will have to keep in mind that if you are using Standard or Enterprise edition you will need either user CALs for named users or named machines. So as the rules of thumbs you will have to get CALs for very user connecting to the SQL Server. (Which is quite expensive if the server is accessible through the internet). The other option would be to get a processor license. Based on the per unit prices you can calculate the breakeven for each of the models. Be also aware that although you are using a web frontend where theoretically on one users accesses the database (e.g. the service account for the webserver) the licensing from above applies (multiplexing)

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

What about using a hosted solution? That would be cheapest and easiest way if it's available. You could get that for $5-10 a month.

|||

Thank you very much. We need user CALs for named machines, so I guess I need further research on cost for that.

I truly appreciate you the taking time to answer.

|||

Tricky part I found on that is that the services that seem to meet some of our need also limit what we can do. I'm still searching this possibility, but so far, we haven't found a packaged solution. We are in Denver and are scheduling to visit a couple of host sites in the next few weeks and see how well that fits us. Once all the phases of the deployment are completed, will have users throughout the state (potentially 15K-20K) so the host site can address our cpu demands...

Txs. for the response. I'll let you know if this route works...

Help! Report Model Project doesn't like my primary key

I'm creating a report model in VS2005 I've created my data source fine and I have selected all the tables I want in the report model data view.

The problem is that for one of the tables it is refusing to acknowledge the promary key. If I try to create the report model it compains that the table doesn't have a primary key.

So I went into SQL Management Studio and checked the table, Lo and behold the primary key is there!!! I tried droping the primary key and recreating it but it still says there is no primary ley on the table.

Any ideas?!?Just did some fiddling and managed to find the problem.

There is a Unique Clustered Index on the table which the problem comes from, with the index it doesn't see the primary key, without it the key suddenly appears

HELP! Passing parameters

Still need help passing a criteria parameter query from a SQL Function (i.e. @.StartDate) to a report header in Access Data Project where header = 'Transactions As Of [StartDate].

If anyone knows anywhere I can get help on this, I would really appreciate it. Thanks.

Could you explain this a bit more in detail ?

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

HELP! Cannot pass GUID's through variables?

I have a project that uses GUID's througout and I'm completely stumped.

1) I create a "batch" GUID to batch the records I'm about to process.

2) I call a web service on a remote machine, and reserve the batch records by inserting the batch GUID into a string works fine

3)I call another web service that returns the rows that I just reserved as XML objects and insert into a string variable

4)I need to use the "batch"GUID variable which is typed as a string (DT_WSTR) as an added column so in a Data Flow Task I do the following:

a) use the XML string variable as the source of a XML Source Task -- works (now that I'm passing custom objects and not a dataset -- curious as to why I can't consume a dataset but thats a different question)

This is where things get tricky:
I've tried to add the BatchGUID as a derived column, as a datatype of DT_WSTR (unicode string) and convert it to uniqueidentifier in the Data Conversion task error, cannot convert unicode to uniqueidentifier (I know I can in C# and SQL Server so why not here).

I've tried to CAST the BatchGUID as a uniqueidentifier and pass that to the datasource -- again conversion error.

I've tried using the type Object and Casting to anything and that doesn't work either.

I've tried to pass the unicode all the way to the SQL Server Destination -- and insert into UniqueIdentifier field... again no go.

All help would be appreciated, at this point I can't see any way of using a UniqueIdentifier as a key field, and maintaining it through the package...
Is this a bug?

Oh and if you want to have some real fun, try returning the type of UniqueIdentifier as an output parameter using a ADO.NET connection.

Thanks!

Maybe a parameterized insert using the SQL Task and a property expression on the query to exchange the value of the GUID ID variable into the insert statement? Have you tried that?

Kirk Haselden
Author "SQL Server Integration Services"

|||

Could you elaborate more on how to implement the property expression in the query? I am doing exqactly as you said using a SQL task and trying to insert into custom logging tables via a sp call. I am using parameter mapping to map system variables to parameters in my sql call, however in my sp I am defining the guids as uniqueidentifiers but ssis system variable guids are strings. What is the easiest way to handle this and convert so it works?

Thanks!

|||

There's some examples here:

Using dynamic SQL in an OLE DB Source component
http://blogs.conchango.com/jamiethomson/archive/2005/12/09/2480.aspx

Setting Expressions
http://blogs.conchango.com/jamiethomson/archive/2006/03/11/3063.aspx

Dynamic modification of SSIS packages
http://blogs.conchango.com/jamiethomson/archive/2005/02/28/1085.aspx

-Jamie

Help with user defined functions

Dear all,

I was given a project to transfer our database into sql server database.

In our previous database we used the datatype int4 for some columns to create some views and in some queries that we used to build our datawindows. In SQLServer 2000 i created a user defined function named int4. I can execute it with the line select dbo.int4(poso) from employee .

Unfortrunately this way make me to rebuild all my datawindows and replace int4( with dbo.int4( . Is there any way to execute queries using user defined function but omitting the first part name dbo. I mean to manage execute the command select int4(poso) from employee \\let int4 be a user definded function.

If i can’t solve this, i thing it will decided than is impossible to move to sqlserver Database. Has anyone any suggestions?

Thanks in advance,

Best regards,

Hellen

Then you are out of luck, the call to functions in SQL Server always have to use the owner prefix (in your case appearantly dbo).

Jens K. Suessmeyer.

http://www.sqlserver2005.de