I am into starting a new dataware house project where i will be loading data from several sources to respective tables within databases.Are there any basic things which i need to set in doing this.
Please let me know
Hi pals,
I need some help from u.
This is datawarehousing related stuff.
I am having a source table as "test" and target table as "trg".
I need to extract the data in required format as per below loading instructions and then load the data into "trg" table.
Below sample data is only given one zipcode.There can be several codes.
drop table test
create table test
(
currentyear int,
district varchar(10),
school varchar(10),
rollno int,
zipcode varchar(10),
flag1_handicapped char(1),
flag2_disadvantaged char(1),
status varchar(10),
relation varchar(10)
)
/* inserted 11 rows */
insert into test values(2005,'D1','S1',101,'530024','Y','Y','E','R' )
insert into test values(2005,'D1','S1',101,'530024','N','N','E','R' )
insert into test values(2005,'D1','S1',101,'530024','N','N','E','NR ')
insert into test values(2005,'D1','S1',101,'530024','N','N','E','NR ')
insert into test values(2005,'D1','S1',101,'530024','N','N','E','NR ')
insert into test values(2005,'D1','S1',101,'530024','N','N','E','NR ')
insert into test values(2005,'D1','S1',101,'530024','N','N','E','NR ')
insert into test values(2005,'D1','S1',101,'530024','N','N','E','NR ')
insert into test values(2005,'D1','S1',101,'530024','N','N','E','NR ')
insert into test values(2005,'D1','S1',101,'530024','N','N','E','NR ')
insert into test values(2005,'D1','S1',101,'530024','Y','Y','E','NR ')
select * from test
--- Structure of the target table
create table trg
(
cyear int,
district varchar(10),
school varchar(10),
RollNo int,
zipcode varchar(10),
type varchar(20), /* This is an extra column with hard coded values which we need to assume as Total,flag1_handicapped,flag2_Disadvantaged.For Every unique zipcode i need to GROUP BY these 3 values.
These values never come from the source table i.e "test".But we can make use of the 2 source columns "flag1_handicapped" & "flag2_Disadvantaged"*/
actaul_cnt int,
empl_related int,
empl_not_related int,
modified_date datetime
)
-- The below table shows what values should get loaded into trg table
----------------------------------------------------------------------
trg table columnvalue to be loaded Description
-----------------------------------------------------------------------
cyear test.currentyear
districttest.district
schooltest.school
rollnotest.rollno
zipcodetest.zipcode
type /* here we to load 3 rows with 3 values
This table contains some calculated columns. such as "actual_cnt","empl_related","empl_not_related" and so on...
Every calculation should be grouped by this "type" column.For reference the you can see the bottom output rows how they should look like.
The 3 valid values for this type column is "Total","flag1_handicapped","Disadavantaged".
"Total" means = All the records which satisfies the calculation.
"flag1_handicapped" means = All the records which statisfies the calculation and have test.flag1_handicapped = 'Y'
"flag2_Disadvantaged" means = All the records which satisfies the calculation and have test.disadvanatged = 'Y'*/
actaul_cnt This is a calculated column. The calc is as follows:
count of records grouped by currentyear,district,school,zipcode,type(flag1_han dicapped,flag2_Disadvantaged,total)
empl_relatedThis is again calculated column. The calc is as follows.
count of records where status='E' and relation = 'R' grouped by currentyear,district,school,zipcode,type(flag1_han dicapped,flag2_Disadvantaged,total)
empl_not_related This is again calculated column. The calc is as follows.
count of records where status='E' and relation = 'NR' grouped by currentyear,district,school,zipcode,type(flag1_han dicapped,flag2_Disadvantaged,total)
modified_date getdate()
-----------------------------------------------------------------------------------------------
Here is the sample template which i felt like using to load the data. We need to modify this query littlt bit accordingly as per above rules.
select
currentyear as "CYear",
district as "District",
school as "School",
rollno as "RollNo",
zipcode as "zipcode",
count(*) "actaul_count",
sum(case when (status='E' and relation='R') then 1 else 0 end) "Emp_Related",
sum(case when (status='E' and relation='NR') then 1 else 0 end) "Emp_Not_Related",
getdate() "Date"
from test
group by currentyear,
district,
school,
rollno,
zipcode
/* Using the above query we need to load 3 rows into below target table whose structure is defined as follows */
------------------------------------------------------------------------------------------------------------------------
Expected Output Rows using above sample data
----------------------------------------------------
CYEAR|DISTRICT|SCHOOL|ROLLNO|ZIPCDE| TYPE |ACTUALCOUNT| EMPL_RELATED |EMPL_NOT_RELATED |MODIFIED_DT
-------------------------------------------------------------------------------------------------------------------------
2005 | D1 | S1 | 101 | 530024 | Total | 11 | 2 | 9 | 2002-01-26
2005 | D1 | S1 | 101 | 530024 | flag1_handicapped | 2 | 1 | 1 | 2002-01-26
2005 | D1 | S1 | 101 | 530024 | flag2_Disadvantaged | 2 | 1 | 1 | 2002-01-26
------------------------------------------------------------------------------------------------------------------------
But using above SELECT,i am able to get only row as output that to i am not able to show the "type" column in the output
2005 | D1 | S1 | 101 | 530024 | 11 | 2 | 1 | 2002-01-26 12:57:53.420 |
------------------------------------------------------------------------------------------------------------------------
Basically i am not getting how to build the Group by clause and displaying the type code using above rules.
Can anyone help me out in solving the problem.
Do we need to perform any UNION ALL ON test.flag1_handicapped and test.flag2_Disadvantaged columns.?
This is totally seems out of box for me.
Any help would be greatly appreciated.
Thanks in Advance.
Iam using Sql Server Integration Service to transfer the data. I have to methods to transfer the data.
Method -1
Daily cleaning Dataware house Data base and transfering the data from Source database.
Method -2
Only new rows transfering from the Source database to dataware house data base.
If i use first method, Any performence issues's will come in the future?. In future my source data will have upto 6 lacks records.
If i use second method, daily date based i can transfer the data. But if they delete any previous records from the source database how can i reflect the same in Data ware house Data base.
Can you please provide the solutions.
Regards
Hanu
hi all,
i unpacked starter site and dw.pup file and i got 34 reports provided by commerce server 2007. i want to edit these reports.
i followed these steps
1)created a "Report server project" in Visual studio 2005
2)In that project in "Shared data sources" folders i added "Startersite_Datawarehouse" of type "Microsoft SQL Server"
3)i added another shared data source name "Startersite_Datawarehouse" of the type "Microsoft SQL services analysis services"
4)later i added one of the reports to "Reports" folder by selecting "Add existing item".
But in design mode of that report when i clicked "Preview" tab i am getting the error
"an error occured during the local report processing. The item /CSDW_olap cant be found"
when i clicked "data" tab i am getting this below error
"Connection cant be made to database"
please help me in resolving this urgent issue....
Thanks in advance,
Archana Devi Papineni
Thanks in advance !
We are having a situation (PROBLEM) with our DW - SQL SERVER 2000 SP4 ENT X86, it suddenly slowed a lot.
CPU Usage : 20%
IO : AVR DISK QUEUE LENGHT Between 1.5 and 2 (rarelly spikes (20/40)).
MEM : 8 GB, 6GB to SQL SERVER.
All the code that is running against the DB is fined tuned ( Most tables are Index Seeked with Clustered Indexes ), biggest table 450 GB.
No Lock Waits ( Except the main process(spid) that is showed has locked by it's own spid ( latch_xx (sh,ix)).
I guess that the problem is related to storage, because it is only doing 1-3MB sec (sometimes 20MB/sec).
PerfMon ( PROCESS SQLSERVER ( DISK Read Bytes ( between 1-3MB sometimes 20 MB/s), Disk Bytes Write Bytes/Sec same as Reads).
No more sql server instances or other software hogging the server.
I can't understand why the SQL SERVER is never using the IO it is used to use (25MB/S).
I don't see any DISK QUEUE on the Partitions that are used to store the datafiles/logfiles)
No Pages splits in the SQL SERVER ACCESS METHODS.
Sometimes I get about 1 GB in dirty buffers.
98 % Cache Hit Ratio.
ALL points to IO contention, but the counters from windows don't indicate this.
All help is REALLY appreciated.
Best Regards.
declare @table table(ad_str1 varchar(200))
insert @table
select '123b apple avenue' union all
select '12b apple avenue' union all
select '3b apple avenue' union all
select '32 apple avenue' union all
select '322 apple avenue' union all
select '3 apple avenue'
select * from @table
how to select the records where the house number contain letter.
I have table like below
House_No
4-12-1000
4-12-55/b
4-12-1456/b/c
4-12-12
4-12-1398
4-12-23
4-12-98
4-12-1499
1-4-33
3-9-55
2-5-89/3
i want out put like select only in between houseno ' 4-12-1000' to '4-12-1500'
EX-
House_No
4-12-1000
4-12-1398
4-12-1456/b/c
4-12-1499
Hello!
My name is Dr. James Triton. I am working on a research program for an unnamed University. Without getting into too much detail, I was wondering if any lower level college (or perhaps late high-school, depending on the strength of your Computer Science program) would be interested in filling out a few SQL queries, making an ER model or DDLs for a database.
Here is some example questions:
1.List each employee's first name, middle initial, and last name, concatenated to form a single value, as well as how many years they have been working for the company. An example name would appear as "JOHN Q SMITH". Sort the output by descending number of employment years. (HINT: create a column alias.)
2.List all of the job functions (such as "CLERK"), and the last names of all employees associated with each function. Order the output alphabetically by the functions. (HINT: there are a total of seven different functions.)
3.List the last name of every manager, along with the last names of the employees that the manager manages. Create column aliases "MANAGER" and "EMPLOYEE", and order the output by manager last name.
(there are 15 such queries)
The table for these is located here:
Please email me if you are interested!
~Dr. James Triton~
Edit : Links removed.
I am trying to Extract the House Number from a address field
i want to start on the left and grab everything till i find the first space.
any help is greatly appreciated
Hi, All
I'm only have permition to query table data and with local sql server installed. Also, capable link server from local machine to live sql server (Blue). Getting error type when ran a query that commons: Column name or number of supplied values does not match table definition. Others, ambiguous column name " ". Here are the information below before i ran the query.
In house data:-Table fields contain (address,city,state,zip,zip4,fips)
-All data type fields are character
-No Primary Key
Local machine table:
-Fipscodes (table) contain data was provided by customer that I inserted into my machine
and column fields (state,zip,fips) made Zip as Primary Key.
So, I wants to able run a query to retrieve data from live server by using some kind of join table with table(FipsCodes)locate in my local machine.
Query Statement:
SELECT h.lname,h.fname,h.street,h.city,h.state,h.zip,h.zip4,h.carroute,h.gender,
h.keycode,h.purprice,h.mortamt,h.phone,h.lender,h.recorddate,h.fips,h.pubmonth,
h.pubday,h.pubyr,h.trantype,h.condocode,h.transdate,h.ratetype,h.loantype,h.birth,
h.heritage,h.estcurrval,h.estcurreq,h.dpbc,n.state,n.fips
FROM blue5.Homeowner.dbo.homeowners AS h INNER JOIN FipsCodes AS n
ON h.FIPS = n.FIPS
WHERE state ='AL' AND fips='001' AND pubDate >= '12/1/2006' AND pubDate <='1/8/2007'
Error occur:
Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'State'.
Server: Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'FIPS'.
, please help me solve this issue and thank you very much everyone.
issues with triggers in Sql Server 2014.
A few weeks ago I've done a SQL Server migration from SQL Server 2000 to SQL Server 2014.It was a bit tricky but anything worked fine.
I have some legacy VB6 (Visual Basic 6) applications written in house which worked with Databases on the old SQL server 2000.Surprisingly, these applications worked well after the upgrade to SQL Server 2014 without having to change a piece of code.
Now, some users tell me that they receive some unusual message when saving data from these legacy applications.After investing for a few hours, I discovered that triggers are not executed when those users try to save data from grids or forms in their applications.Trying to reproduce the INSERT statement in SQL Server Management Studio, the triggers run well.From the application, they don't.
These applications connect to Database Server thru OLEDB connection with the following ADO connection string :
Provider=SQLOLEDB.1;Password={password};User ID={user};Initial Catalog={db};Data Source={datasource}.the {user} is a true SQL account who have read/write/delete access in the databases.
On the web there is a lot of questions involving the same issue, but only from SSIS.I found some articles about an OLEDB connection parameter named FastLoadOptions with a value of FIRE_TRIGGERS, but nowhere how to put it in the ADO OLEDB connection string.
how to reactivate the "normal" use of triggers from an ADO OLEDB connection ?Either with some obscur parameter in the connection string or options somewhere in the SQL Server 2014.
does anybody know how I would create a connection string in asp.net 2.0
express to access a database in a different location from the web
server. I have a static IP address on the web server, so I've allowed
access from the web server through the firewall. I have a user name and
password, and I've made sure that I have the correct database. Would I
access the database using the network IP address and if so, where do I
put it. Thank you to anyone that can give me some insight to this
problem, I'm very new to asp.net so, about a weeks worth of knowledge,
so if you could explain as such I would appreciate it.
The following is my code. What I am trying to do is find all the students a teacher as assessed during a give time. Then find out which assessment was done the most recently. After that I will then be aggregating those results. I have never written any pl/sql or T-SQL... heck I don't even know what to call it!The first sql command is doing what I want it to. I can only assume the cursor is working correctly. Any help would be greatly appreciated.BryanALTER procedure Domain@UserID numeric,@StartDate datetime,@EndDate datetime
AS-- Variable DeclerationDECLARE @SessionID varchar(1000)DECLARE @EachSessionID numeric
--Cursor to find all Children that the teacher has assessed for the given timeDECLARE ChildID_cursor CURSORFORSELECT DISTINCT childID FROM capsessionWHERE userid = @UserIDAND sessiondate BETWEEN @StartDate AND @EndDate-- looping through all Children to find there most recent assessment.OPEN ChildID_cursorDECLARE @ChildID numeric FETCH NEXT FROM ChildID_cursor INTO @ChildIDWHILE (@@FETCH_STATUS <> -1)BEGIN IF (@@FETCH_STATUS <> -2) BEGIN DECLARE SessionID_cursor CURSOR FOR SELECT TOP 1 CAPSessionID FROM CapSession WHERE (ChildID = @ChildID) AND (SessionDate BETWEEN @StartDate AND @EndDate) ORDER BY SessionDate DESC END FETCH NEXT FROM ChildID_cursor INTO @ChildIDENDCLOSE ChildID_cursor
OPEN SessionID_cursorFETCH NEXT FROM SessionID_cursor into @EachSessionID SET @SessionID = ''WHILE (@@FETCH_STATUS <> -1)BEGIN IF (@@FETCH_STATUS <> -2) BEGIN SET @SessionID = @SessionID + @EachSessionID+ ',' END FETCH NEXT FROM ChildID_cursor INTO @ChildIDENDCLOSE SessionID_cursorRETURN @EachSessionID
Hi, I wanna know if one has to undergo the 'sql video tutorials' on this website before trying to understand data access tutorials, again on this site.
Are the two related by any chance. Also I am not able to find the customer database files he has created in tutorial # 5, dumb to ask, but what to do?,...so anyone willing to shed light!!!
Hello everyone,
as you might have seen, I'm a total newbie to this Site and M$ SQL.
I browsed the forum to see if there are already matching answers to my question, with no success.
My question:
As a MySQL & PHP user I ask myself if there is a possibility to use M$ SQL the way I use MySQL and PHP!?
Is there a way to generate dynamic websites with M$ SQL 2000 and a webserver running PHP sources?
A brief yes or no would really help me, if you have any links or resources on this topic, I'd be glad to hear about it.
Thanks
Tom
I get the error "General SQL Error" when reading or writing to a memo field in Sql Server through a frontend build in Delphi 4.
I'm Using the latest BDE.
I have recently installed this onto a NT enviroment in the same manner as my own enviroment but get the error message above.
On my own enviroment and a friends enviroment I do not get any errors.
Can someone please give me some help on this or a direction to run in ?
Thanks
Dave.
1. What is the SQL error you receive when you try to fetch data from a cursor after it has run out of data?
2. After a SQL statement is executed, what does a negative value in the SQLCODE variable indicate?
Hi, folks. I've a production SQL machine with more than 20 users making transactions 24 hrs in 6 days a week. I've only Sunday for maintenance. The server has fixed 2 GB RAM allocation for SQL. Is it good to Restart SQL ( or machine) to clear the Buffer-Cache( or is it good to keep the cache) .... :rolleyes:
Howdy!
:eek: I am not going to lie, I am taking a DB course and am finding it extremely difficult to understand. :o
We are doing Relational DB & ER modeling as well as table normalization.
I only have 1 hour to take an upcoming and know I will score poor on my own.
Anyone want to help me out with my quiz?
I can post the Q's here.
Thanks much
Hi! How do you query an entire db for a value?
I know how to do table queries from the SQL Analyzer, but not a full DB search.
Like, if I wanted to find a value '137.51 in a DB, what would I use?
Many thanks!
JJ
I'm not impressed with the speed/quality of responses from this forum...
Can anybody recommend a more active forum?
Hi,
is there a search function in MS SQL server 2005 to search for a name of a table or a name of field or entry in a field?
what/how should be the fastest way to do it?
thank you
Maximum Number of rows allowed in sql server 2005 tables ?
Maximum Number of columns allowed in sql server 2005 tables ?
What are all the built in string functions can be allowed for 'TEXT' DataType in sql server 2005?
Before I go into specifics, this is my problem. I've have a tablethat gets updated with large amounts of data on a monthly basis.Sometimes (rarely) identical rows of data are on one months import thatalready exist from the previous month. I can identify these rows from acombination of two fields (sampleID and testname).My question is this. Would it be an appropriate 'fix' if I created anew 'formula' field on the table comprising of a concatentation ofthese two fields and then made that an index field (no duplicates)? Myguess would be that if we then tried to import a record with a sampleIDand testname that already existed, then the import for that recordwould simply fail.Would this work? Is there a better way? My background is more withAccess so apologies if I'm not using the right terminology.Simon Harris
View 4 Replies View Relatedhi...i would like to know best practice when assigning a value to a variableexample belowsetorselectdeclare @var1 varchar(25)set @var1='abc'select @var1='abc'to meset is implicitselect implies conditionsi see both used all over the place here at my new company....so witch is better practicetksMJ
View 6 Replies View RelatedIs comp.databases.ms-sqlserver more active thanmicrosoft.public.sqlserver.server, or is it the other way around??Which NG is better for general and 'learning SQL 2005' types ofquestions??Thank you, Tom
View 2 Replies View RelatedNew to using databases that are NOT Access. New to MS SQL Server. I'dlike help understanding some concepts.Firstly, I'd like to know how to connect to a database using MS SQLServer on a remote web server using Windows XP; I know the IP and havethe username and password to connect...but what program do I use?Suggestions, appreciated.Secondly, I'd like to make changes to the remote database - anyonesuggest programs to use?Regards,OZ
View 1 Replies View RelatedI have a general question about transactions and commands that are replicated in a Transactional replication.
My screen shot says:
A total of 5 transaction(s) with 85 command(s) were delivered.
I know what a transaction is, but what is a command?
I am assuming a transaction is
begintrans
committrans, correct?
I am currently upgrading my companies database from 6.5 to 2000. Including the replication. I am curious about something. Currently we are using the commands in 6.5 to do the replication instead of stored procedures. (except for one table that requires custome filters). When I reestablished replication on 2000, the default is to create stored procs on the subscribers to hande the replication. (sp_MSins_tablename). My question whether anybody knows why it defaults to using sp's? Is there a reason or is that just the way it is. I want to know if using sp's is better than just sing the commands before I change my setup.
Thanks for any responses.
Johnny
Hello,
Can someone please answer the following questions:
1) Am I correct that SSRS 2005 can access a remote data source on SQL Server 2000? (this one I'm pretty sure about). I'm also trying to determine whether I can move up to SSRS 2008 when it is released and keep running all the same report types against SQL 2000.
2) Where is the bulk of the reporting services calculations performed (e.g. NOT the database query itself)? The Reporter Server? Or the Reporter Server Database?
3) Is it advisable to have the Report Server and Report Server Database on the same server? How do people typically handle this?
4) Is it advisable to have the Report Server and the Datasource database on the same server?
Thanks!
Michael
I have a website. It is a SAS Site. It has a structure similar to
tbClient (ID, Username, password)
tbData (ID, ClientID, info)
tbDatax(ID, tbDataID, info)
tbDatay(ID, tbDataXID, info)
This is a reporting nightmare for me. I Ihave a sollution, but i was wondering if SRS has an out of the box sollution or if anyone knows of an out of the box sollution.
It is a reporting nightmare because clients want to be able to create reports out of the system, but I can't let them see other clients data.
My sollution is give them a username CLientA
They log into The database with CLientA login
I have views that mask the data by enforcing all of the relationships through the view.
create view vutbDatax as
Select tbDatax.*
from tbDataX
Inner Join tbData on tbData.ID = tbDataX.tbDataID
Inner Join tbClient on tbClient.ID = tbDataX.ClientID
where tbCLient.Username = User
User is a Database function that returns me the Username they logged into the database as.
Imagine doing that for 100 views. And managing the security on it. I have done it and it is in the process of testing right now but Does anyone have a better sollution.
Hi, I've got the query below which works as needed, but it performs the same inner select twice at present. What is the best way of eliminating the second evaluation of the inner select statement?
SELECT TOP 1 condition FROM
(SELECT condition, COUNT(condition) AS total FROM [JCM].[dbo].[jcmresults] GROUP BY condition)
WHERE total = (SELECT MIN(total) FROM
(SELECT condition, COUNT(condition) AS total FROM [JCM].[dbo].[jcmresults] GROUP BY condition))
Thanks,
Andrew
I have a website that is probably going to hold a sizable amount of data. The data will be specific to groups of users based on login credentials. Would it be more efficient to create a whole new database for each group of users, or create new tables for the groups in the existing database?
Any thoughts on the topic would be appreciated.
Thanks