HELP! -- SQL Server 2005 from 2000

Hello everyone,
I'm writing you because I have this situation: I need to register a (remote)
SQL Server 2005 and currently we have SQL 2000. I can't do it using
Enterprise Manager.
Is there a way to do it *WITHOUT* upgrading to SQL 2005?!?
Please let me know if it's possible with SQL2000 and/or any other tool
Thanks in advance,
SB-RSure. Connect with Query Analyzer. It can do everything that Enterprise
Manager can -- you just need to use T-SQL to do it.
By the way, it seems that some of the newsgroups are not as relevant to this
question as others (olap, for example).
--
Keith Kratochvil
"segis bata" <segisbata@.hotmail.com> wrote in message
news:OXMXBAW6GHA.4500@.TK2MSFTNGP02.phx.gbl...
> Hello everyone,
> I'm writing you because I have this situation: I need to register a
> (remote) SQL Server 2005 and currently we have SQL 2000. I can't do it
> using Enterprise Manager.
> Is there a way to do it *WITHOUT* upgrading to SQL 2005?!?
> Please let me know if it's possible with SQL2000 and/or any other tool
> Thanks in advance,
> SB-R
>|||Hi,
> I'm writing you because I have this situation: I need to register a
(remote)
> SQL Server 2005 and currently we have SQL 2000. I can't do it using
> Enterprise Manager.
> Is there a way to do it *WITHOUT* upgrading to SQL 2005?!?
> Please let me know if it's possible with SQL2000 and/or any other tool
Our database IDE "Database Workbench" supports connecting to both
SQL 2000 and SQL 2005 just fine. Download yourself a trial copy at
www.upscene.com
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle &
MS SQL Server
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com

Help writing the SqlConnectionString.

Hi.

I realize this may have been asked a thousand times before, but it's still not working for me. I would appreciate any help with it.

First, I created a GridView, and from it I created a new SqlDataSource and let it point to the database in C:\My Project\App_Data\ASPNETDB.MDF. This automatically created a connectionString in the Web.Config file, saying the following:

 "R1ASPNETDBConnectionString" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename="C:\My Project\App_Data\ASPNETDB.MDF";Integrated Security=True;Connect Timeout=30;User Instance=True" providerName="System.Data.SqlClient" />

Now when I try to programatically use that connection string, the program crashes.

Below is my code:

<script runat="server"
 String sqlConnectionString = @."Data Source=.\SQLEXPRESS;AttachDbFilename="C:\My Project\App_Data\ASPNETDB.MDF";Integrated Security=True;Connect Timeout=30;User Instance=True";protected void Page_Load(object sender, EventArgs e)
 {
using (SqlConnection conn =new SqlConnection(sqlConnectionString))
 {
 conn.Open();
const String selectQuery ="SELECT StatusName FROM Status ORDER BY StatusName";
 Label1.Text ="";
using (SqlCommand cmd =new SqlCommand(selectQuery, conn))
 {
 SqlDataReader dr = cmd.ExecuteReader();
if (dr.Read())
 {
 Label1.Text += dr[0];
while (dr.Read())
 {
 Label1.Text +=", ";
 Label1.Text += dr[0];
 }
 Label1.Text +=". ";
 }
else Label1.Text ="None.";
 }
if (conn !=null)
 conn.Close();
 }
 }

Here's the error message I'm getting:

Server Error in '/My Project' Application.

---

Keyword not supported: 'c:\my project\app_data\aspnetdb.mdf";integrated security'.

Description:Anunhandled exception occurred during the execution of the current webrequest. Please review the stack trace for more information about theerror and where it originated in the code.

Exception Details:System.ArgumentException: Keyword not supported: 'c:\my project\app_data\aspnetdb.mdf";integrated security'.

Source Error:

Line 15: protected void Page_Load(object sender, EventArgs e)
Line 16: {
Line 17: using (SqlConnection conn = new SqlConnection(sqlConnectionString))
Line 18: {
Line 19: conn.Open();

Source File: c:\My Project\test.aspx Line: 17

Stack Trace:

[ArgumentException: Keyword not supported: 'c:\my project\app_data\aspnetdb.mdf";integrated security'.]
 System.Data.Common.DbConnectionOptions.ParseInternal(Hashtable parsetable, String connectionString, Boolean buildChain, Hashtable synonyms, Boolean firstKey) +417
 System.Data.Common.DbConnectionOptions..ctor(String connectionString, Hashtable synonyms, Boolean useOdbcRules) +99
 System.Data.SqlClient.SqlConnectionString..ctor(String connectionString) +52
 System.Data.SqlClient.SqlConnectionFactory.CreateConnectionOptions(String connectionString, DbConnectionOptions previous) +25
 System.Data.ProviderBase.DbConnectionFactory.GetConnectionPoolGroup(String connectionString, DbConnectionPoolGroupOptions poolOptions, DbConnectionOptions& userConnectionOptions) +141
 System.Data.SqlClient.SqlConnection.ConnectionString_Set(String value) +38
 System.Data.SqlClient.SqlConnection.set_ConnectionString(String value) +4
 System.Data.SqlClient.SqlConnection..ctor(String connectionString) +21
 ASP.test_aspx.Page_Load(Object sender, EventArgs e) in c:\My Project\test.aspx:17
 System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e) +13
 System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) +45
 System.Web.UI.Control.OnLoad(EventArgs e) +80
 System.Web.UI.Control.LoadRecursive() +49
 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +3745

My question is: How should I write the connection string? I have a feeling the problem is with the quotes ("), but I can't figure out how to write it otherwise.

Thank you very much.

Hi

the solution is to remove the double quotation that is inside the connections string :

String sqlConnectionString = @."Data Source=.\SQLEXPRESS;AttachDbFilename=C:\My Project\App_Data\ASPNETDB.MDF;Integrated Security=True;Connect Timeout=30;User Instance=True";

Still this is not a good practice ,since you are storing an absolute path for your MDB which causes the problems when you deploy the APP.

so you need this instead:

String sqlConnectionString = @.Data Source=.\SQLExpress;Integrated Security=True;User Instance=True;AttachDBFilename=|DataDirectory|ASPNETDB.MDF;Connect Timeout=30;

Note that |DataDirectory| will be replaced automaticly with the App_Data path by the runtime .

|||

anas:

String sqlConnectionString = @."Data Source=.\SQLExpress;Integrated Security=True;User Instance=True;AttachDBFilename=|DataDirectory|ASPNETDB.MDF;Connect Timeout=30";

Awesome, it's working. Thank you very much for taking the time and explaining it.

Help writing SQL

I want to select records where a column is null, is not null or is equal to a specific number. How do I do this in one sql statement. My application is an asp.net web site with a business logic layer, a data access layer and sql server. Thank you in advance.select *
from tb
where isnull(col,'*')=isnull(@.search,'*')

help writing s.proc in sql2005

hi all.

in my sql2005 i have a function that returns a value. func(x) returns j

how can use it in a select clause inside a s.proce?

select bb, func(xx) as jj , from ....

?

You should be able to include it right in your SELECT statement, since it returns a scalar.

You will, however, need to qualify the function with the schema; SELECT dbo.func(xx) or SELECT myschema.func(xx) etc.

Help writing an If else statement

Hello I'm a newbie to programming and need help writing an if
statement.

I have a database set up in SQL with the following fields:

Category Questions Answers

I only want one category to appear for all of the questions and
answers submitted for that category. The way I have it set up now all
if a question is submitted for the same category then the category
will list twice and a one question under each other.

How do I write something if I Dim Category

If it's the same category but a different question just list that
question under that category. If it's a new category list that
category and the questions and answers under thatJJ297 (nc297@.yahoo.com) writes:

Quote:

Originally Posted by

Hello I'm a newbie to programming and need help writing an if
statement.
>
I have a database set up in SQL with the following fields:
>
Category Questions Answers
>
I only want one category to appear for all of the questions and
answers submitted for that category. The way I have it set up now all
if a question is submitted for the same category then the category
will list twice and a one question under each other.
>
How do I write something if I Dim Category
>
If it's the same category but a different question just list that
question under that category. If it's a new category list that
category and the questions and answers under that

I am afraid that I can only answer with the standard recommendation that
you post:

o CREATE TABLE statements for your table(s).
o INSERT statement with sample data.
o The desired output given the sample.

This helps to clarify what you are asking (which I currently do not
understand), and also makes it easy to develop a tested solution.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||JJ297 wrote:

Quote:

Originally Posted by

Hello I'm a newbie to programming and need help writing an if
statement.
>
I have a database set up in SQL with the following fields:
>
Category Questions Answers
>
I only want one category to appear for all of the questions and
answers submitted for that category. The way I have it set up now all
if a question is submitted for the same category then the category
will list twice and a one question under each other.
>
How do I write something if I Dim Category
>
If it's the same category but a different question just list that
question under that category. If it's a new category list that
category and the questions and answers under that

This sounds like it should be done in a separate reporting
layer, using Crystal Reports or something similar.|||Why are you formatting data in the back end? The basic principle of a
tiered architecture is that display is done in the front end and never
in the back end. This a more basic programming principle than just
SQL and RDBMS.

Help writing a stored procedure...

I'm developing a library and want to display the alphabets across the
screen. When a user clicks on one of the alphabets I want all titles
beginning with that letter to appear on the screen.

How would I write this stored procedure?

My table is called Titles

Fields:
Title ID
Titles

Thanks!

The select statement migth need some improvements, I think that would result in a full table-scan. Have a look at you execution plan and optimize as required.

CREATE PROCEDURE st_Ret_MyStuff(@.characterClickedas varChar(1))ASBEGINSET NOCOUNT ON;SELECT yourColumnsFROM titlesWHEREleft(titleCol, 1)=@.characterClicked;ENDGO

Cheers!

/Eskil

|||

Thanks that works!

Help writing a report query

OK, I have to write a SQL query for someone, for a report. There's financial values involved. There's three tables, essentially. Call them account names, account types, and transactions. I need to return the value of each transaction with the transactions, the sum of the transactions for the account, and the sum of all account transactions across all account types for the account name. I'm not sure how best to do this, I'm thinking of creating a temporary table at the bottom level ( where the actual data is ) and joining against it to do a SUM on the higher levels. To do that, I seem to need to do group by, which then means I need to specify all my non grouped column names, is that right ?

Can you post a bit more info, particularly a small sample set of data to work with and what you want the results to be? I am having a bit of trouble envisioning what you want.

|||

OK, it works something like this:

table ShareInfo

ShareInfoId int

CurrentPrice int

table SharePurchase

SharePurchaseId int

PortfolioId int

ShareInfoId int

SharesPurchased int

PricePerShare int

table SharePortfolio

PortfolioId int

and some other columns for names, etc. So, on the bottom level, I want to list all share purchases within a portfolio, and how their value has changed in each instance to toay. This data will be collapsible on the report, and the row that shows always will show the share name, and the total value of shares purchased, and amount lost/gained. This data is also collapsible, and on the top level, I show the Portfolio name, and the total value/total amount lost or gained on the portfolio to date.

|||

Hi you can use the ROLLUP operator,

Example,
Create table #Shares
(
PortfolioId int,
ShareId int,
NoOfShares int,
TimePurchased varchar(10),
SharesPerPrice int
)

Insert Into #Shares values(1,1,10,'10:00 AM',30);
Insert Into #Shares values(1,2,100,'10:00 AM',5);
Insert Into #Shares values(1,2,50,'11:00 AM',6);
Insert Into #Shares values(1,3,112,'10:00 AM',5);
Insert Into #Shares values(1,4,112,'10:00 AM',5);

Select
PortfolioId
,ShareId
,TimePurchased
,Sum(NoOfShares)
,Sum(SharesPerPrice)
,Sum(NoOfShares * SharesPerPrice)
from
#Shares
Group By PortfolioId, ShareId, TimePurchased With Rollup

OUTPUT:

PortfolioId ShareId TimePurchased NoOfShares SharesPerPrice Totalvalue
-- -- - -- -- --
1 1 10:00 AM 10 30 300
1 1 NULL 10 30 300
1 2 10:00 AM 100 5 500
1 2 11:00 AM 50 6 300
1 2 NULL 150 11 800
1 3 10:00 AM 112 5 560
1 3 NULL 112 5 560
1 4 10:00 AM 112 5 560
1 4 NULL 112 5 560
1 NULL NULL 384 51 2220
NULL NULL NULL 384 51 2220

If TimePurchased is NOT NULL then it is Down Level Data
If TimePurchased is NULL and ShareId is not null then it is One level Collopsed from the Down Level
If TimePurchased is NULL and ShareId is NULL and PortfolioId is not NULL then it is at Portfolio Level

|||

OK - that looks good, but how would I then populate my report from this ? I expected I'd return three tables, one for each level.

Thanks for helping...

|||

Here you can change the query as follow as,

Select
PortfolioId
,ShareId
,TimePurchased
,SUM(NoOfShares) NoOfShares
,SUM(SharesPerPrice) SharesPerPrice
,Sum(NoOfShares * SharesPerPrice) TotalValue
INTO #RESULT
from
Shares
Group By PortfolioId, ShareId, TimePurchased wITH ROLLUP
Select PortfolioId,ShareId,TimePurchased,NoOfShares,SharesPerPrice,TotalValue from #RESULT where TimePurchased is not null

Select PortfolioId,ShareId,NoOfShares,SharesPerPrice,TotalValue from #RESULT where TimePurchased is Null And ShareId is NOT NULL

Select PortfolioId,NoOfShares,SharesPerPrice,TotalValue from #RESULT where TimePurchased is Null And ShareId is NULL And PortfolioId is Not Null

Help writing a query.

Hello,

I am trying to write a query and getting a little confused.

My problem:
I send enquiries to partners from 12:00am to 11:59pm each day. I have ten
partners, and each partner has a different daily limit. In my partner table
(Table1), I have a columnm for the Daily Enquiry Limit called "DailyCap". I
also have a second table (Table2) which counts how many enquiries a partner
has had and assigns a date stamp.So, my query needs to:

Select PARTNER
where Table2.PartnerCount is less than Table1.DailyCap
AND where Table2.TimeStamp between 12:00am TODAY and 11:59pm TODAY.

This should hopefully then select any partner who hasent yet reached the
daily cap between midnight start and 24 hours later.

Any odeas how to write this query using proper SQL? I have tried but
failed.

Regards,

Gary.I'll assume your tables look like this:

CREATE TABLE Partners (partner_no INTEGER PRIMARY KEY, partner_name
VARCHAR(20) NOT NULL UNIQUE, dailycap INTEGER NOT NULL CHECK (dailycap>=0))

CREATE TABLE PartnerEnquiries (partner_no INTEGER REFERENCES Partners
(partner_no), enquiry_dt DATETIME, PRIMARY KEY (partner_no, enquiry_dt))

Here's the query:

SELECT P.partner_name, MAX(enquiry_dt)
FROM Partners AS P
LEFT JOIN PartnerEnquiries AS E
ON P.partner_no = E.partner_no
AND E.enquiry_dt >=CONVERT(CHAR(8),CURRENT_TIMESTAMP,112)
AND E.enquiry_dt <DATEADD(DAY,1,CONVERT(CHAR(8),CURRENT_TIMESTAMP,11 2))
GROUP BY P.partner_no, P.partner_name, P.dailycap
HAVING COUNT(E.partner_no) < P.dailycap

--
David Portas
SQL Server MVP
--|||Hi David,

Thanks for the time you have taken to reply. I am fairly new to SQL, could
you or perhaps another NG user comment on the code you have provided and let
me know what all the elements are and how they work? It looks quite
complex!!!

Thanks,

Gary.

"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:uqedne4yoJAjPLnd4p2dnA@.giganews.com...
> I'll assume your tables look like this:
> CREATE TABLE Partners (partner_no INTEGER PRIMARY KEY, partner_name
> VARCHAR(20) NOT NULL UNIQUE, dailycap INTEGER NOT NULL CHECK
(dailycap>=0))
> CREATE TABLE PartnerEnquiries (partner_no INTEGER REFERENCES Partners
> (partner_no), enquiry_dt DATETIME, PRIMARY KEY (partner_no, enquiry_dt))
> Here's the query:
> SELECT P.partner_name, MAX(enquiry_dt)
> FROM Partners AS P
> LEFT JOIN PartnerEnquiries AS E
> ON P.partner_no = E.partner_no
> AND E.enquiry_dt >=CONVERT(CHAR(8),CURRENT_TIMESTAMP,112)
> AND E.enquiry_dt
<DATEADD(DAY,1,CONVERT(CHAR(8),CURRENT_TIMESTAMP,11 2))
> GROUP BY P.partner_no, P.partner_name, P.dailycap
> HAVING COUNT(E.partner_no) < P.dailycap
> --
> David Portas
> SQL Server MVP
> --|||Here it is again with comments. You can refer to Books Online for the
meaning of particular keywords.

SELECT P.partner_name, MAX(enquiry_dt) /* latest datetime */
FROM Partners AS P
/* Left join because maybe not every partner has an enquiry: */
LEFT JOIN PartnerEnquiries AS E
ON P.partner_no = E.partner_no
/* Include rows only where Enquiry_dt is today: */
AND E.enquiry_dt >=CONVERT(CHAR(8),CURRENT_TIMESTAMP,112)
AND E.enquiry_dt <DATEADD(DAY,1,CONVERT(CHAR(8),CURRENT_TIMESTAMP,11 2))
GROUP BY P.partner_no, P.partner_name, P.dailycap
/* where the row count from the enquires table is < dailycap: */
HAVING COUNT(E.partner_no) < P.dailycap

I have assumed that you have a row in PartnerEnquiries for each enquiry and
that you want to count those rows and compare to dailycap. That part wasn't
entirely clear to me from your original post.

--
David Portas
SQL Server MVP
--

Help writing a querry

I need some help with the following
I have a view as follow
date ValueIndex Value
20060131 2001 0.0455
20060130 2001 0.0455
20060129 2001 0.0454
20060128 2001 0.0453
20060127 2001 0.0453
20060126 2001 0.0452
20060125 2001 0.0452
20060124 2001 0.0451
20060123 2001 0.0451
20060122 2001 0.045
I would like to import a file with the date and the ValueIndex once I import
that file I would like to do a look up and get the value.
Is this possiable
Thanks
Chris>> I would like to import a file with the date and the ValueIndex once I
Yes, it is possible. What exactly do you find it difficult in doing it?
Importing the file to a table? Or writing the query to get the value?
Anith|||Anith,
Both -- I have no idea where to start
Please HELP!!!!!
"Anith Sen" wrote:

> Yes, it is possible. What exactly do you find it difficult in doing it?
> Importing the file to a table? Or writing the query to get the value?
> --
> Anith
>
>|||First of all, to get the data from a file to an SQL table, you have several
options.
Perhaps the easiest one is using DTS. Check out the topic Data
Transformation Services in the SQL Server Books Online. A simple interface
in SQL Enterprise Manager can get the job done.
Alternatively, you can use command line utilities like BCP or use BULK
INSERT to get the data from a file to the table. Again, you have all the
information needed in SQL Server Books Online.
To extract the desired data from an SQL table, you issue a query, generally
in the form of a SELECT statement. Given that your table has the columns
date_column, value_index & value with date_column as the primary key, you
could retrieve the value by using a SELECT statement like:
SELECT value FROM tbl WHERE date_column = @.param ;
-- where @.param is the parameter passed to the query to look up the desired
value.
All details needed for writing SQL can be found in SQL Server Books Online.
Anith|||Anith,
Thank you for your helpfull information.
After looking at the db I do not need to import the table it is already in
the db. I need to add a column to the table/view and populate that column
with the value base on the date and valueindex combination
Thank You
"Anith Sen" wrote:

> First of all, to get the data from a file to an SQL table, you have severa
l
> options.
> Perhaps the easiest one is using DTS. Check out the topic Data
> Transformation Services in the SQL Server Books Online. A simple interface
> in SQL Enterprise Manager can get the job done.
> Alternatively, you can use command line utilities like BCP or use BULK
> INSERT to get the data from a file to the table. Again, you have all the
> information needed in SQL Server Books Online.
> To extract the desired data from an SQL table, you issue a query, generall
y
> in the form of a SELECT statement. Given that your table has the columns
> date_column, value_index & value with date_column as the primary key, you
> could retrieve the value by using a SELECT statement like:
> SELECT value FROM tbl WHERE date_column = @.param ;
> -- where @.param is the parameter passed to the query to look up the desire
d
> value.
> All details needed for writing SQL can be found in SQL Server Books Online
.
> --
> Anith
>
>|||>> I need to add a column to the table/view
Look at ALTER TABLE .. ADD .. statement in SQL Server Books Online.
You can use an UPDATE statement, something along the lines of:
UPDATE tbl
SET value = ( SELECT t1.value FROM source_tbl t1
WHERE t1.date_col = tbl.date_col
AND t1.value_idx = tbl.value_idx )
WHERE EXISTS ( SELECT * FROM source_tbl t1
WHERE t1.date_col = tbl.date_col
AND t1.value_idx = tbl.value_idx );
For more details, lookup the topic UPDATE in SQL Server Books Online.
Anith|||A couple of links that may also help you understand the SQL...
http://www.w3schools.com/sql/sql_intro.asp
http://sqlzoo.net/
There are many others, but these both provide general walkthroughs of the
basics.
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:esd6BOLPGHA.1216@.TK2MSFTNGP14.phx.gbl...
> Look at ALTER TABLE .. ADD .. statement in SQL Server Books Online.
>
> You can use an UPDATE statement, something along the lines of:
> UPDATE tbl
> SET value = ( SELECT t1.value FROM source_tbl t1
> WHERE t1.date_col = tbl.date_col
> AND t1.value_idx = tbl.value_idx )
> WHERE EXISTS ( SELECT * FROM source_tbl t1
> WHERE t1.date_col = tbl.date_col
> AND t1.value_idx = tbl.value_idx );
> For more details, lookup the topic UPDATE in SQL Server Books Online.
> --
> Anith
>

Help with writing Triggers

I have no experience with Triggers and need help to create 2. My goal is to
generate the average and Stard Devation for 2 values when they are inserted
or updated. In the first field I insert a count that I need to calculate th
e
mean and StdDev based on the values of the past 30 days. The other is the
same with the addition of the past 30 days based on wdays or wends.
I currently am doing this with a batch but it would be simpler to manage if
the rows values were calculated when the values are inserted or updated.
Below are the table design and the batch statement.
-- ========================================
===============================
CREATE TABLE [dbo].[RollingRecordCount] (
[CDATE] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[NCOUNT] [bigint] NOT NULL ,
[RCOUNT] [bigint] NOT NULL ,
[IsWday] AS (convert(char(1),case when ((datepart(wday,[CDATE]) = 7
or datepart(wday,[CDATE]) = 1)) then 'N' else 'Y' end)) ,
[MeanNc] [float] NULL ,
[StdDevNc] [float] NULL ,
[MeanRc] [float] NULL ,
[StdDevRc] [float] NULL ,
[InsertModDate] [datetime] NULL
) ON [PRIMARY]
GO
========================================
====================================
======
UPDATE RollingRecordCount
SET MeanNc = (SELECT AVG(NCOUNT)
FROM RollingRecordCount
WHERE CDATE BETWEEN (@.startdate) AND (@.enddate)),
StdDevNc = (SELECT STDEVP(NCOUNT)
FROM RollingRecordCount
WHERE CDATE BETWEEN (@.startdate) AND (@.enddate)),
MeanRc = CASE
WHEN IsWday = 'Y'
THEN (SELECT AVG(RCOUNT)
FROM RollingRecordCount
WHERE ISWday = 'Y' AND
CDATE BETWEEN (@.startdate) AND (@.enddate))
ELSE (SELECT AVG(RCOUNT)
FROM RollingRecordCount
WHERE ISWday = 'N' AND
CDATE BETWEEN (@.startdate) AND (@.enddate))
END,
StdDevRc = CASE
WHEN IsWday = 'Y'
THEN(SELECT STDEVP(RCOUNT)
FROM RollingRecordCount
WHERE IsWday = 'Y' AND
CDATE BETWEEN (@.startdate) AND (@.enddate))
ELSE (SELECT STDEVP(RCOUNT)
FROM RollingRecordCount
WHERE IsWday = 'N' AND
CDATE BETWEEN (@.startdate) AND (@.enddate))
END
FROM RollingRecordCount
WHERE CDATE = @.enddateJim Abel (JimAbel@.discussions.microsoft.com) writes:
> I have no experience with Triggers and need help to create 2. My goal
> is to generate the average and Stard Devation for 2 values when they are
> inserted or updated. In the first field I insert a count that I need to
> calculate the mean and StdDev based on the values of the past 30 days.
> The other is the same with the addition of the past 30 days based on
> wdays or wends. I currently am doing this with a batch but it
> would be simpler to manage if the rows values were calculated when the
> values are inserted or updated. Below are the table design and the batch
> statement.
Hm, I'm not that this is good for a trigger. As I understand it,
you want the values to reflect the last 30 days. But what if nothing
happens during a day? The values should still change, shouldn't they?
Of course, it may be a fair assumption that data is inserted everyday.
But how often? Recalculating everytime may be expensive?
(Basically, I say this, because I'm just about to leave, and don't
have the time to compose a trigger right now.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||I agree with Erland. A view could be used here. But it really is difficult t
o
be 100% sure without seeing the DDL and some sample data.
ML
http://milambda.blogspot.com/|||Sorry for the lack of details with this request. Here is some more
information. The 2 fields NCOUNT and RCOUNT are inserted once esch day. On
rare ocasions the counts that are entered had been calculated incorrectly at
the datasource and I need to manually edit the particular row and change the
value for one or both counts and then recalculate the means and StdDev of
that row based on the previous 30 days of that rows date. The other message
suggested using a view and that may work as well. I'm just trying to develo
p
something that takes as little management as possible, the goal being that I
need only to enter the NCOUNT and/or the RCOUNT and the mean and StdDev
columns can be autimatically generated without me needing to pull up a batch
script.
"Erland Sommarskog" wrote:

> Jim Abel (JimAbel@.discussions.microsoft.com) writes:
> Hm, I'm not that this is good for a trigger. As I understand it,
> you want the values to reflect the last 30 days. But what if nothing
> happens during a day? The values should still change, shouldn't they?
> Of course, it may be a fair assumption that data is inserted everyday.
> But how often? Recalculating everytime may be expensive?
> (Basically, I say this, because I'm just about to leave, and don't
> have the time to compose a trigger right now.)
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>|||I added some more detail to Erlands message in a reply. I thought that the
script under my 2nd ====== line was the ddl of the existing batch less the
declaration of the Start and en date parameters. the end date is set by
getting the Max date after the dauly insert occurs and the start date is 30
days less. In the case of a manual update to a previously entered row the
end date is the CDATE value and the Start date is 30 days less.
If I am ubderstanding your suggestion to use a view the mean and StdDev
fields would be calculated in the view design and they would not be necessar
y
in the underlyung table, is this chat you're suggesting?
"ML" wrote:

> I agree with Erland. A view could be used here. But it really is difficult
to
> be 100% sure without seeing the DDL and some sample data.
>
> ML
> --
> http://milambda.blogspot.com/|||Exactly. But to be certain we'd have to see more DDL (table definition) and
sample data.
ML
http://milambda.blogspot.com/|||Jim Abel (JimAbel@.discussions.microsoft.com) writes:
> Sorry for the lack of details with this request. Here is some more
> information. The 2 fields NCOUNT and RCOUNT are inserted once esch day.
> On rare ocasions the counts that are entered had been calculated
> incorrectly at the datasource and I need to manually edit the particular
> row and change the value for one or both counts and then recalculate the
> means and StdDev of that row based on the previous 30 days of that rows
> date. The other message suggested using a view and that may work as
> well. I'm just trying to develop something that takes as little
> management as possible, the goal being that I need only to enter the
> NCOUNT and/or the RCOUNT and the mean and StdDev columns can be
> autimatically generated without me needing to pull up a batch script.
If rows are inserted once per day, it makes more sense. I assume then
that CDATE is the primary key in RollingRecordCount? Wittout a primary
key, it gets difficult.
Below is a trigger. Some remarks: I've replaced the sub-selects with
joins to a derived table. This is a proprietary syntax and not very
portable. On the other hand, on SQL Server this syntax usuaally gives
better performance. I did not include the computation of StdDevRc, but
left that as an exercise. :-) You can use MeanRc as a pattern. I also
added 1E0* in some places to force a conversion to float. It's meaningless
to store the means as float, if the result is integer only. (Which it is
if you say AVG(NCOUNT) without any conversion.
CREATE TRIGGER rolling_tri FOR INSERT, UPDATE AS
UPDATE RollingRecordCount
SET MeanNc = R1.MeanNc,
StdDevNc = R1.StdDevNc,
MeanRc = CASE IsWday
WHEN 'Y' THEN R1.MeanWDay
ELSE R1.MeanWEnd
END
FROM RollingRecordCount R
JOIN (SELECT i.CDATE, MeanNc = AVG(1E0 * R.NCOUNT),
STDEVP(1E0 * R.NCOUNT
MeanWDay = SUM(1E0 * R.RCOUNT) /
SUM(CASE R.ISWday WHEN 'Y' THEN 1 ELSE 0 END),
MeanWEnd = SUM(1E0 * R.RCOUNT) /
SUM(CASE R.ISWday WHEN 'N' THEN 1 ELSE 0 END)
FROM inserted i
JOIN RolleingRecordCount ON R.CDATE
BETWEEN i.CDATE - 30 AND i.CDATE
GROUP BY i.CDATE) AS R1 ON R.CDate = R1.CDate
In lieu of sample data, the code is untested.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Help with writing sql statement

I need some help writing a query. I have a text file that will be imported on a weekly basis, with 1000+ rows and 5 columns. This i need to import into table tblECR which i have added 2 of my own columns.

The problem is the text file will arrive with current data and new data. The current data may or may not have changed (dates, status etc). How do i go about importing the new data and updating the existing data with the new fields, without deleting the data in the 2 columns i've added. I'm using vs 2005 with a sql 2005 express database.

This is the code i'm using to import the data currently. Clicking the button more than once will obviously just import all the data into the database again.

Private Sub CustomerDataToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CustomerDataToolStripMenuItem.Click

'Clear the dataset

dsimport.Clear()

'Set the file variables

Dim strFileName As String

Dim strFilePath As String

Dim sSlash As Single

'Open the file dialog and select the text file to open

Try

With OpenFileDialog1

'Set the initial dialog options

.Title = "Import Customer data file"

.InitialDirectory = "P:\Ian\"

.FileName = ""

.Filter = "File (*.csv)|*.csv|All files (*.*)|*.*"

If OpenFileDialog1.ShowDialog() <> Windows.Forms.DialogResult.Cancel Then

Else

MessageBox.Show("No file was selected", "Error", MessageBoxButtons.OK, MessageBoxIcon.Information)

Exit Sub

End If

'Strip the filename into its seperate portions

sSlash = InStrRev(.FileName, "\")

strFilePath = Mid(.FileName, 1, CInt(sSlash))

strFileName = Mid(.FileName, CInt(sSlash + 1), Len(.FileName))

End With

'Set the connection properties to read the text file

Dim strConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & strFilePath & ";" & "Extended Properties=""text;HDR=NO;FMT=Delimited"""

Dim conn As New OleDb.OleDbConnection(strConnectionString)

'Open connection with the database.

conn.Open()

'Create new OleDbCommand to return data from the text file

Dim objCmdSelect As New OleDb.OleDbCommand("SELECT * FROM [" & strFileName & "]", conn)

' Create new OleDbDataAdapter that is used to build a DataSet based on the preceding SQL SELECT statement

Dim objAdapter1 As New OleDb.OleDbDataAdapter

'Pass the Select command to the adapter

objAdapter1.SelectCommand = objCmdSelect

'Fill the DataSet with the information from the file

objAdapter1.Fill(dsimport, "Import")

objAdapter1.AcceptChangesDuringFill = False

'Clean up objects

conn.Close()

Catch ex As Exception

MsgBox(ex.Message).ToString()

Exit Sub

End Try

'Now import the data into the table

Dim sqlcn As New SqlConnection(ConnString)

Dim sqlcmd_ECR As New SqlCommand

Dim dr As DataRow

Try

sqlcn.Open()

'Setup the sql command to enter data into the ECR table

sqlcmd_ECR.Connection = sqlcn

sqlcmd_ECR.CommandText = "Insert into tblECR_Test(ECR_No,Aims_No,ECR_Type) Values(@.a,@.b,@.c)"

'Setup the sql parameters to enter data into the ECR table

sqlcmd_ECR.Parameters.Add("@.a", SqlDbType.Int)

sqlcmd_ECR.Parameters.Add("@.b", SqlDbType.Int)

sqlcmd_ECR.Parameters.Add("@.c", SqlDbType.VarChar, 255)

Try

For Each dr In dsimport.Tables(0).Rows

sqlcmd_ECR.Parameters("@.a").Value = dr(0)

sqlcmd_ECR.Parameters("@.b").Value = dr(1)

sqlcmd_ECR.Parameters("@.c").Value = dr(2).ToString()

sqlcmd_ECR.ExecuteNonQuery()

Next

Catch ex1 As SqlException

MsgBox(ex1.Message).ToString()

Exit Sub

End Try

MessageBox.Show("The text file was successfully imported.", "Customer data import", MessageBoxButtons.OK, MessageBoxIcon.Information)

Catch ex3 As Exception

MsgBox(ex3.Message).ToString()

End Try

sqlcn.Close()

End Sub

ExecuteNonQuery returns an integer that tells you how many rows were affected by the query, so you could run an update first (trying to update the record assuming it is already there) and then if the rows affected is 0 instead of 1, run the insert.

Help with writing a Stored Procedure

Hi there,
I would be really grateful with some assistance with this, i am 100% new to
SQL & Stored Procedures. I'm building an ASP web application, i need to be
able to update two fields in two related tables & could really do with some
assistance. The tables are tbCompany and tbemployee. tbemployee is related t
o
tbCompany through a field in tbemployee named CompanyId (int). The fields
that i need to update are CompanySuspendedAccount in tbCompany
EmpCompAccoutSuspended in tbemployee. Needless to say there could be multipl
e
records in tbemployee that are related to the one record in tbCompany.
Ideally i will need to write two stored procedures, the first that updates
all records to yes and the second to no.
I would be really grateful for some assitance with this - Many thanks
GarethAssuming some thing that you didn=B4t posted a SP could bve something
like this for you:
CREATE PROCEDURE UPDSuspendCode
(
@.CompanyId INT,
@.Suspend BIT
)
AS
BEGIN
DECLARE @.Error INT
BEGIN TRANSACTION
UPDATE tbCompany
SET CompanySuspendedAccount =3D @.Suspend
Where CompanyId =3D @.CompanyId
SET @.ERROR =3D @.@.Error
UPDATE tbemployee
SET EmpCompAccoutSuspended =3D @.Suspend
Where CompanyId =3D @.CompanyId
SET @.ERROR =3D @.Error + @.@.Error
IF @.Error > 0
BEGIN
RAISERROR('And Error has occured during updating the suspend
status',16,1)
ROLLBACK
END
ELSE
COMMIT
END
HTH, Jens Suessmeyer.|||Thanks Jene, it looks good to me, presumably here i am setting the value to
Suspend? So i could use @.yes BIT for the suspending of the accounts and @.no
BIT to unsuspend. Both the fields CompanySuspendedAccount &
EmpCompAccoutSuspended are nvarchar so presumably it would need to be as
above unless i change the database? Thanks for your help
Gareth
"Jens" wrote:

> Assuming some thing that you didn′t posted a SP could bve something
> like this for you:
> CREATE PROCEDURE UPDSuspendCode
> (
> @.CompanyId INT,
> @.Suspend BIT
> )
> AS
> BEGIN
> DECLARE @.Error INT
> BEGIN TRANSACTION
>
> UPDATE tbCompany
> SET CompanySuspendedAccount = @.Suspend
> Where CompanyId = @.CompanyId
> SET @.ERROR = @.@.Error
>
> UPDATE tbemployee
> SET EmpCompAccoutSuspended = @.Suspend
> Where CompanyId = @.CompanyId
> SET @.ERROR = @.Error + @.@.Error
> IF @.Error > 0
> BEGIN
> RAISERROR('And Error has occured during updating the suspend
> status',16,1)
> ROLLBACK
> END
> ELSE
> COMMIT
> END
>
> HTH, Jens Suessmeyer.
>|||"@.yes BIT " Thats what my @.Suspend is for, you don=B4t need a @.Suspende
AND @.Unsuspense because the twi BITs can never evaluate to true (can be
either switch on AND off, right ).
You don=B4t have to change the database because BIT a Integer data type
with the constraint of being 1, 0, or NULL, so implicit conversion will
be used.
DECLARE @.Suspense BIT
DECLARE @.SomeColumn NVARCHAR(200)
SET @.Suspense =3D 1
SET @.SomeColumn =3D @.Suspense
Print @.SomeColumn
HTH, jens Suessmeyer.

Help with writing a sproc

I am really new to SQL and I am hoping someone can give me some basic help
with writing a sproc.
I have 3 tables
Table 1
UserId - int
UserRole - int
Table 2
UserId - int
UserName - char
Table 3
RoleId - int
RoleName - char
I am looking for a sproc that will return a table like so
UserId UserName UserRole
1 Bob Admin
Sorry if this seems really simple, but I have really can't figure it out
Its hard to guess what you are looking for in this procedure. For instance
do you pass in a user name? UserID, RoleID?
Here is a proc that will return a list of all user ids, user names, and
their roles.
Create Proc sproc
as
select table1.userId, UserName, UserRole=RoleName
from table1, table2, table3
where table1.userID=Table2.UserID and Table1.UserRole=Table3.RoleID
Here is a proc which will accept a user id as a parameter and return a list
of all roles for that user id in the format you are looking for
Create Proc sproc (@.userID int)
as
select table1.userId, UserName, UserRole=RoleName
from table1, table2, table3
where table1.userID=Table2.UserID and Table1.UserRole=Table3.RoleID
and table1.userID=@.UserID
Here is a proc which will accept a role id as a parameter and return a list
of all roles for that user id in the format you are looking for
Create Proc sproc (@.roleID int)
as
select table1.userId, UserName, UserRole=RoleName
from table1, table2, table3
where table1.userID=Table2.UserID and Table1.UserRole=Table3.RoleID
and table1.userRole=@.roleID
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"NewGuy" <a@.a.com> wrote in message
news:eGT3T36hEHA.2340@.TK2MSFTNGP11.phx.gbl...
> I am really new to SQL and I am hoping someone can give me some basic help
> with writing a sproc.
> I have 3 tables
> Table 1
> UserId - int
> UserRole - int
> Table 2
> UserId - int
> UserName - char
> Table 3
> RoleId - int
> RoleName - char
> I am looking for a sproc that will return a table like so
> UserId UserName UserRole
> --
> 1 Bob Admin
> Sorry if this seems really simple, but I have really can't figure it out
>
|||Thanks,
I think I am trying return the results from table 1
But with the values from table 2 and 3 replacing the int from table 1...
I'll play around with what you gave me and see if anything works
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:eX5G4$7hEHA.3264@.tk2msftngp13.phx.gbl...
> Its hard to guess what you are looking for in this procedure. For instance
> do you pass in a user name? UserID, RoleID?
> Here is a proc that will return a list of all user ids, user names, and
> their roles.
> Create Proc sproc
> as
> select table1.userId, UserName, UserRole=RoleName
> from table1, table2, table3
> where table1.userID=Table2.UserID and Table1.UserRole=Table3.RoleID
>
> Here is a proc which will accept a user id as a parameter and return a
list
> of all roles for that user id in the format you are looking for
> Create Proc sproc (@.userID int)
> as
> select table1.userId, UserName, UserRole=RoleName
> from table1, table2, table3
> where table1.userID=Table2.UserID and Table1.UserRole=Table3.RoleID
> and table1.userID=@.UserID
> Here is a proc which will accept a role id as a parameter and return a
list[vbcol=seagreen]
> of all roles for that user id in the format you are looking for
> Create Proc sproc (@.roleID int)
> as
> select table1.userId, UserName, UserRole=RoleName
> from table1, table2, table3
> where table1.userID=Table2.UserID and Table1.UserRole=Table3.RoleID
> and table1.userRole=@.roleID
>
>
>
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "NewGuy" <a@.a.com> wrote in message
> news:eGT3T36hEHA.2340@.TK2MSFTNGP11.phx.gbl...
help
>