Getting A Final Version Of A Person Into A DW

Feb 14, 2007

I have about 8 databases to integrate. All of the databases have ssno, address city...ect. I need to create a DW table with one unique record for each actual person. In other words,

Joe Smith,123 Main St, Anytown, State,....+ssno

goes into the DW table and is the same person as Joseph S. Smith,123 Main Street... and any other versions.

Could someone point me to a reference or give me an outline of how to do this in and SSIS package?

Is fuzzy logic used here?

Do I need to deduplicate the feeder systems first??

It needs to handle a situation in, for example, the Bronx New York where there could be an apartment buiding with 7 people named Jose Sanchez .

I hope I've been clear, I'm a newbie at this DW stuff, but it's fascinating. Any help would be appreciated. Thanks

View 1 Replies


ADVERTISEMENT

XMLDocument I/O With DataTables And TableAdapters (final Version?)

May 25, 2006

The test sub below operates on a SQL Server Table  with an xml-type field ("xml").  The purpose of the sub is to learn about storing and retrieving a whole xml document as a single field in a SQL Server table row.When the code saves to the xml field, it somehow automagically strips the xml.document.declaration (<?xml...>).  So when it reads the xml field back and tries to create an xmldocument from it, it halts at the xmldocument.load.I order to  get the save/retrieve from the xmlfield to work, I add the <?xml declaration to the string when I read it back in from the xml field (this is in the code below).At that point the quickwatch on the string I'm attempting to load into the xmldocument is this:-----------------------------------------------------<?xml version="1.0" encoding="utf-16" ?><Control type="TypeA"><Value1><SubVal1A>Units</SubVal1A><SubVal1Btype="TypeA">Type</SubVal1B></Value1><Value2><SubVal2A>Over</SubVal2A><SubVal2B>Load</SubVal2B></Value2></Control>-----------------------------------------------------The original xml document string is this:-----------------------------------------------------<?xml version="1.0" encoding="utf-16" ?><Control type="TypeA">     <Value1>          <SubVal1A>Units</SubVal1A>          <SubVal1B type="TypeA">Type</SubVal1B>          </Value1>     <Value2>          <SubVal2A>Over</SubVal2A>          <SubVal2B>Load</SubVal2B>     </Value2></Control>-----------------------------------------------------which seems to have all the same characters as the quickwatch result above, but clearly is formatted differently because of the indenting.THE FIRST QUESTION:  Is there a simpler way to do this whole thing using more appropriate methods that don't require adding the xml.document.declaration back in after reading the .xml field, or don't require using the memorystream to convert the .xml field in order to load it back to the XML document.THE SECOND QUESTION:  Why does the original document open in the browser with "utf-16", but when I write the second document back to disk with "utf-16" it won't open...I have to change it to "utf-8" to open the second document in the browser.Here's the test sub'============================================               Public Sub XMLDSTest()          '===========================================          Dim ColumnType As String = "XML"                    '===========================================          '----------Set up dataset, datatable, xmldocument          Dim wrkDS As New DSet1()          Dim wrkTable As New DSet1.Table1DataTable          Dim wrkAdapter As New DSet1TableAdapters.Table1TableAdapter          Dim wrkXDoc As New XmlDocument          wrkXDoc.Load(SitePath & "App_XML" & "XMLFile.xml")          Dim str1 = wrkXDoc.OuterXml          Dim wrkRow As DSet1.Table1Row          wrkRow = wrkTable.NewRow          '=======WRITE to SQL Server==============          '------ build new row          With wrkRow               Dim wrkG As Guid = System.Guid.NewGuid               TestKey = wrkG.ToString               .RecordKey = TestKey               .xml = wrkXDoc.OuterXml     '<<< maps to SQL Server xml-type field          End With          '----- add row to table and update to disk          wrkTable.Rows.Add(wrkRow)          wrkAdapter.Update(wrkTable)          wrkTable.AcceptChanges()          '----- clear table          wrkTable.Clear()          '=======READ From SQL Server ==============          '----refill table, read row,           wrkAdapter.FillBy(wrkTable, TestKey)          Dim wrkRow2 As DSet1.Table1Row = _             wrkTable.Select("RecordKey = '" & TestKey & "'")(0)          '=====  WRITE TO New .xml FILE ===========================          Dim wrkS1 As New StringBuilder          Select Case ColumnType               Case "XML"                    '---if xml build xml declaration:                      '---add this to xml from sql table   =>  <?xml version="1.0" encoding="utf-16" ?>                    wrkS1.Append("<?xml version=" & Chr(34) & "1.0" & Chr(34))                    wrkS1.Append(" encoding=" & Chr(34) & "utf-16" & Chr(34) & " ?>")                    wrkS1.Append(wrkRow2.xml)          End Select          Dim wrkBytes As Byte() = (New UnicodeEncoding).GetBytes(wrkS1.ToString)          Dim wrkXDoc2 As New XmlDocument          Dim wrkStream As New MemoryStream(wrkBytes)          wrkXDoc2.Load(wrkStream)          '===========================================          '---- this just shows that the file actually was touched           Dim wrkN2 As XmlNode = wrkXDoc2.CreateNode(XmlNodeType.Text, "ss", "TestNode2")          wrkN2 = wrkXDoc2.SelectSingleNode("//Value1/SubVal1B")          wrkN2.Attributes("type").Value = "This was from the xml field"          '----------------          '------  update the encoding....otherwise the file won't open in the browser with utf-16          Dim wrkN1 As XmlNode = wrkXDoc2.CreateNode(XmlNodeType.Element, "ss", "TestNode")          wrkN1 = wrkXDoc2.FirstChild          wrkN1.InnerText = Replace(wrkN1.InnerText, "utf-16", "utf-8")          '------------Now write the file back as an .xml file          Dim wrkFilePath As String = SitePath & "App_XML" & "XMLFile2.xml"          Dim wrkXW As XmlWriter = XmlWriter.Create(wrkFilePath)          wrkXDoc2.WriteContentTo(wrkXW)          wrkXW.Close()     End Sub===============================

View 8 Replies View Related

Release Of JDBC Driver 1.2 Final Version

Sep 12, 2007

Just wondering when the final (non-QA) version of the SQL Server 2005 driver is expected to be released.

Thanks.
Jeff

View 1 Replies View Related

Final Build?

Nov 16, 1998

Anyone know the final build # for the RTM version?

Thanks

View 2 Replies View Related

SP2 Final This Year ?

Dec 12, 2006

Does anyone know if the final SP2 will be out this year ?

Really need it for a production enviroment....

View 3 Replies View Related

Final Part - Min() Function

May 23, 2006

I have the final part of my report to complete. Yesterday I posted the most confusing set of threads ever on a public forum. So to cut the confusion, I am starting this thread again as now I know exactly what I need.

I have two tables each of which have a function that gathers data from them based around a set of parameters. This is then manipulated by a third function and the results of which are passed into a Stored Procedure. Here is the T-SQL syntax used for the SP;

SELECT fnWTRalldataReport.Areacode, fnWTRalldataReport.siteref, fnWTRalldataReport.estatename, fnWTRalldataReport.Securitised,

fnWTRalldataReport.unitref, fnWTRalldataReport.unittype, fnWTRalldataReport.unittype_count, fnWTRalldataReport.tenantname,

fnWTRalldataReport.tenantstatus, fnWTRalldataReport.tenantstatus_count, fnWTRalldataReport.unitstatus, fnWTRalldataReport.unitstatus_count,

fnWTRalldataReport.floortotal, fnWTRalldataReport.floortotocc, fnWTRalldataReport.floorspaceperc, fnWTRalldataReport.initialvacarea,

fnWTRalldataReport.initialvacnet, fnWTRalldataReport.TotalRent, fnWTRalldataReport.NetRent, fnWTRalldataReport.FinalRtLsincSC,

fnWTRalldataReport.rentrolldiscperc, fnWTRalldataReport.netrentpersqft, fnWTRalldataReport.ErvTot, fnWTRalldataReport.tenancyterm,

fnWTRalldataReport.landact, fnWTRalldataReport.datadate, fnWTRalldataReport.div_mgr, fnWTRalldataReport.portfolio_mgr,

fnWTRalldataReport.propcat, fnWTRalldataReport.budgeted_net_rent, fnWTRalldataReport.budgeted_occupancy,

fnWTRbudgetdata_1.budgeted_net_rent AS budget_rent, fnWTRbudgetdata_1.budgeted_occupancy AS budget_occ

FROM dbo.fnWTRalldataReport(@dt_src_date, @chr_div, @vch_portfolio_no, @vch_prop_cat) AS fnWTRalldataReport LEFT OUTER JOIN

dbo.fnWTRbudgetdata(@dt_src_date) AS fnWTRbudgetdata_1 ON fnWTRalldataReport.siteref = fnWTRbudgetdata_1.site_ref

The result of this SQL places a value for budget_rent and budget_occ against every row that the 1st function's result set. What I want to achieve is that where the site_ref is equal in both functions results, I want it to place the budget_rent & budget_occ value against the first row only of each site_ref only.

To explain briefly the structure. Table one has various fields including site_ref and unit_ref. There are many unit_ref's per site_ref in this table. Table 2 has only site_ref and budget info. Someone yesterday suggested that I could achieve this my using something along the lines of the Min() function e.g. Min(unit_ref).

Could someone please elaborate on this for me. I have gone through my SQL book and read about min() and also BOL, but I can't quite work the syntax out to put the budget info against only one line per site based around the lowest unit_ref per site_ref.

This might seem confusing, but it is easier to read than the other thread I assure you.

Regards

View 3 Replies View Related

Final Attemp -Sql Stored Procedure Tough Question

Mar 17, 2004

I have a procedure I need to get the values out of..I am using outputs...I have no idea why it wont work......I need all values listed in the select part of procedure....



CREATE procedure dbo.Appt_Login_NET
(
@LoginName nvarchar(15),
@Password NvarChar(15),
@UserName nvarchar(15)Output,
@UserPassword nvarchar(15)Output,
@UserClinic nvarchar(3)Output,
@UserTester bit Output
)
as
select
UserName,
UserPassword,
UserClinic,
UserTester
from
Clinic_users
where
UserName = @LoginName
and
UserPassword = @Password

GO



my vb.net code to retrive this info is

Private Sub Button1_ServerClick(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.ServerClick
Dim con As New SqlConnection("Server=myserver;database=APPOINTMENTS;uid=webtest;pwd=webtest")
Dim cmd As New SqlCommand
Dim parmuser As SqlParameter
Dim parmus As SqlParameter
Dim parmpass As SqlParameter
Dim parmtest As SqlParameter
Dim struser As String
Dim strpass As String
Dim strclinic As String
Dim strnames As String
Dim tester As String
strpass = txtPass.Value
struser = txtUser.Value
cmd = New SqlCommand("Appt_Login_NET", con)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add("@LoginName", struser)
cmd.Parameters.Add("@Password", strpass)
parmus = cmd.Parameters.Add("@UserName", SqlDbType.NVarChar)
parmus.Size = 15
parmus.Direction = ParameterDirection.Output
parmuser = cmd.Parameters.Add("@UserClinic", SqlDbType.NVarChar)
parmuser.Size = 3
parmuser.Direction = ParameterDirection.Output
parmpass = cmd.Parameters.Add("@UserPassword", SqlDbType.NVarChar)
parmpass.Size = 15
parmpass.Direction = ParameterDirection.Output
parmtest = cmd.Parameters.Add("@UserTester", SqlDbType.Bit)
parmtest.Size = 1
parmtest.Direction = ParameterDirection.Output

con.Open()
cmd.ExecuteNonQuery()
If Not IsDBNull(cmd.Parameters("@UserName").Value) Then
Label1.Text = cmd.Parameters("@UserName").Value()
Else
Label1.Text = "No Results Found"
End If

con.Close()
End Sub

Why does this always show as "DBNUll" I get nothing when I debug any of my parm variables.I searched the SQl Server and in Query analyzer instead of the output variables in the procedure being just outputs they are input/outputs...................What does it take to get this working??? Do I need a conversion datatype I would prefer I gain the values and store them in variables......

View 7 Replies View Related

Update Final Table With Values Into Comma Separator?

Mar 24, 2014

I would like to update the final table with values into a comma separator as follows with examples:

I think I have the IDs set correctly for this example:

You can see from the final table that the code column can be a combination of more than one rows from the map tables...

create table #tblTax(TaxStatusID int, FullName varchar(20)
insert into #tblTax values(1, 'Taxable')
insert into #tblTax values(2, 'exempt')

create table #tblTypes(TypeID int, description varchar(100)
insert into #tblTypes values(1, 'cor')
insert into #tblTypes values(2, 'tyr')

[code]....

Looking at the above example, I would like to have the #tblMain as follows

#tblMain
ShoetNameLongNameClientNameTaxIDTypeID
======================================================================
A, B'dand, Barlow''johnson'133
G'mond''anderson'26
I'somelongcode''jacksons'21
A, B'dand, Barlow''smith'112

View 2 Replies View Related

SQL Server 2008 :: Pivot Function - Additional Final Column

Mar 11, 2015

I've managed to use the pivot function using the code below which gives me the following output:

Location_Code Gt 0-1 Gt 1-2 Gt 2-3 Gt 3-4
North 10 0 3 5
West 6 3 2 0
South 4 2 8 2

This is exactly what I want but I would like an additional final column that will total the columns by location_code and weekband.

[Location_Code] AS 'Location Code'
,[Gt 0-1], [Gt 1-2], [Gt 2-3], [Gt 3-4]
from (select [WeekBand]
,[Location_Code]
, count(*) as CountOf

[Code] ....

View 7 Replies View Related

Call Stored Procedure For Last 4 Months Then Combine Into Final Result Set Row

May 3, 2006

I want to call my stored proc for that last 4 months. Basically all I need to do is pass each month's first date and it will do the rest. Should I shove this into a UDF first? I'm not sure if I can do that. The struction is here behind my stored proc: http://www.webfound.net/storedproc.txt

EXEC IT_Get_Dashboard_Monthly '2006-05-03 12:03:43.910' <-- change to UDF or leave it? Then how can I loop and change each month to cover the last 4 months?

I also need to ensure all 4 values returned in each interation show up in one row in the final result set that is produced

View 1 Replies View Related

The Top Sales Person?

Dec 26, 2006

I am quite newbie, really grateful for some help how to create a sql sentence in Reporting Services that would return the best sales person from each shop.. The following returns all the sales persons from each shop

So how to select the top sales person from each shop?
SELECT TOP (10) shop.name, SUM(Cd.Price) AS Sales, Personal.Name AS Salesperson
FROM Purchase INNER JOIN
Personal
ON Purchase.Salesperson_id = Personal.Personal_id RIGHT OUTER JOIN shop
ON Personal.work_id = shop.shop_id FULL OUTER JOIN
Cd ON Purchase.Cd_id = Cd.Cd_id
GROUP BY Shop.Name, Personal.Name
ORDER BY Sales DESC

Or something like this? But how in Rep.Services???

...LEFT OUTER JOIN (
SELECT P.work_id, P.Name, SUM(C.Price) AS TotalSale
FROM @Personal P
INNER JOIN @Purchase B
ON B.Salesperson_id = P.Personal_id
INNER JOIN @Cd C
ON C.Cd_id = B.Cd_id
GROUP BY P.Work_id, P.Name
) D
ON D.Work_id = S.Shop_id

View 4 Replies View Related

MY HELP!!!!! Request.....ONLY 1 Person Reply.

Oct 15, 2007

I can believe it, 40 people read my sql query problem and ONLY ONE reply to my request for help.

Thanks!!!! It's great to know that we help each other in this community.

View 1 Replies View Related

Role/Person Table?

Dec 6, 2005

We're developing an application request/packaging/rollout worflowapplication for our 50 site, 40,000 user company. There is a requesttable, an engineering table, a distribution table, etc. etc. But, thecompany has a designated "Application Owner" at each site, and eachperson who will use the application must also be listed in the workflowapplication. So, we need a lookup table for the owners and users:CREATE TABLE REQUEST (RQ_ID INTEGER NOT NULL,RQ_BY_ID INTEGER NOT NULL,RQ_FOR_ID INTEGER NOT NULL,ASSIGNED_ENGINEER_ID INTEGER NOT NULL,OTHER INFO...);CREATE TABLE APP_OWNERS (RQ_ID INTEGER NOT NULL,OWNER_ID INTEGER NOT NULL);CREATE TABLE APP_USERS (RQ_ID INTEGER NOT NULL,USER_ID INTEGER NOT NULL);There are many other tables, of course, some with single person IDfields and addititional lookup tables where there are multiple peopleinvolved like testers, package distributors, etc. I began to wonder,why not just a single table to cover ALL the people involved:CREATE TABLE RQ_WORKFLOW_PEOPLE (RQ_ID INTEGER NOT NULL,PERSON_ROLE VARCHAR(20) NOT NULL,PERSON_ID INTEGER NOT NULL);INSERT INTO RQ_WORKFLOW_PEOPLE (rq_id,person_role,person_id) values(123456,'RQ BY',314159),(123456,'RQ FOR',951413),(123456,'APP OWNER',159413),(123456,'APP OWNER',413159),(123456,'USER',594131),(123456,'USER',313459),.....The real question I have is how does one evaluate options like this?The good news, I think, is that where I simply must have crossreference tables because of multiple values (application owners, users,testers, etc.) I've reduced the number of those tables to one byspecifying a single table by role. Is that a good thing or a bad thing?I've also removed similar data from several other tables where only asingle column was needed in those tables, i.e. the requested by andrequested for fields, the assigned engineer, and several others. Thereis one and only one of each for each request but the type of data, thatis an employee ID is exactly the same, so does it make more "sense" tokeep the data with the request table or the engineering table orconsolidate all ID data in an ID table?Any thoughts on this woudl be appreciated.Randy

View 2 Replies View Related

Looking For Some Guidance From A Kind Person

Apr 4, 2008

I am gonig into interview for a junior developer position. The role involves a lot of SQL based work. Training is on the job, and they know I am new to this, but they want to know what I can do with SQL server by wednesday, and obviosuly I stand a better chance if I can do a reasonable amount by then.I am assuming I can practise with offline databases, so I would like to do that. Also I was wondering if there were any simple example databases I can load up.

I have downloaded and installed the software, and would like to know how to connect and create a test database.
There will be a small test in the interview and the questions are:
1. SQL Management and Data Extraction
For this task you will need to be familiar with database tables, data types and constraints. There will be some administration work using SQL Management Studio along with some T-SQL queries. You will need to show use of the SELECT, INSERT, UPDATE and DELETE statements. If you do not have SQL Server, you can download the express edition for free at the following URL. http://msdn2.microsoft.com/en-us/express/bb410791.aspx


2. XSLT
For this task I will be asking you to produce some HTML output displaying the data from an XML file. The concept is similar to ASP.


3. Database Design
You will be given a scenario for a company that requires some database software for the smooth running of their organisation. You will need to plan and design a data structure to accommodate these requirements. You will be allowed to spend as much time on this part of the test as you wish. Key information here is going to be database normalization.




These questions dont make a whole lot of sense to be at the moment, so would appreciate a breakdown in simpler terms.





This job will be a fantastic opportunity for me to get into development, and would appreaciate any help that you guys have to offer, thanks in advance.

View 21 Replies View Related

Selecting First Transaction Per Person

Jan 17, 2008



Hi

In a transactions table, I need to get the record for the earliest transaction date for each person or account. So far I've been doing it in 2 steps (below). Is there an easier way to do this?

Thanks

Dannie

--------------------------

SELECT

Person_ID
Earliest_Transaction_Date = MIN( Transaction_Date )
INTO

Earliest_Transaction_Dates
FROM

All_Transactions
GROUP BY

Person_ID

SELECT

b.*
FROM

Earliest_Transaction_Dates a
LEFT OUTER JOIN
All_Transactions b
WHERE

a.Person_ID = b.Person_ID
and b.Transaction_Date = a.Earliest_Transaction_Date





View 3 Replies View Related

Need To Get Separate Pages For Each Person..help

Dec 21, 2006

I'm fairly new to RS and have a problem.

I have a query that pulls information on people, courses they have taken, their score and so on. I have a date parameter setup so I can run it by year. Everything works ok on the query side, I get all the information I need on all the people and the courses they have taken. However, when I run the report, I do not get a separate page for each person and their relative information. The first page shows the first name and the rest of the 700 pages list all the courses and other information, with no break. How do I render the report so that I can get a separate page(s) for each person and their specific info? I can glady provide more info/code if need be.

Bill

View 8 Replies View Related

How To Get User Name Of The Person Loged In To The System In SQL

Sep 27, 2007



Hi,

I have written a trigger for audit trail of one table in SQL and it has to get the user name who is doing the changes from the application(.net)
I have used the SQL user name and password while connecting the .net application to the database, and because of this whoever log in to the application and make changes on that table data, in the audit trail it still shows as the database user name.

I want to display the user name of the person logged in to the application

In the query i am using "System_user" for getting the user name. And the connection string in my application has a default uid and pswd.

I want to retain uid and pswd in the connection string but still want the query to get the name of the correct logged in user to the application.

Can anybody say me if this possible.

Thanks
Gayathri.

View 3 Replies View Related

Simple SQL Server Stuff For An Oracle Person...

Jul 27, 2006

I'm trying to do some basic stuff in SQL Server 2005 that I could do in Oracle.  I'm sure there's a way to do these things, I just don't know how.  My most immediate question is this:
Is there any easy way to run a SQL script and have it spool to HTML tables to a file?  The 'results to text' from SSMS is not adequate for display purposes.  I'm trying to display table/column descriptions.
Beyond that, does anyone know of any good resources that provide an 'Oracle to SQL Server' matrix so I can help figure out how to do some of these things in SQL Server?
Thanks in advance,
Mike

View 1 Replies View Related

SQL Server Triggers- New Person -Help Required-Urgent

Mar 10, 2008

Please help me in sorting out my Problem Providing me the solution .
My Problem is
I have a master table with Primary key on ID field (PatientID-(Patient-Table)) and it is an Identity field
And My child table has the same ID field(PatientID-(PatientDetails-Table)) and it has the relationship set
And the child table has its own Primary key of its own ID field(PatientdetailsID).
What I want is as soon as enter row of data into the master table (Patient-Table)and click save on my front end application(Which is ASP.Net web application)
I want to update Child Table’S (PatientDetails)ID field ( ie.,PaientID in the PatientDetailsTable) in the  Child Table   which relates the parent table ,by doing so I want to  update the Primary key field (ie.,PatientDetailsID)  & ForeignKey Field (PatientID)of the child table and to create row  in the child table  with two columns .(PatientID,&PatientDetailsID)
What I want to achieve is in my ASP.net Application as soon as I enter Master table
I want to Edit Child tables (about 15) one by one like a Wizard pages which will have The ID Field(PatientID) same in all my wizard pages .
I want to know whether I can incorporate triggers if so in which table (is it in Patient or PatientDetails) and I will be grateful If anyone gives the Script to-do  so.I am also providing my two table sripts.
Sripts:CREATE TABLE [dbo].[Patient](      [PatientID] [int] IDENTITY(1,1) NOT NULL,      [Date] [smalldatetime] NULL,      [UserID] [int] NULL,      [FirstName] [varchar](40) NOT NULL,      [Surname] [varchar](30) NOT NULL,      [DOB] [datetime] NULL,      [Age]  AS (floor(datediff(day,[DOB],getdate())/(365.25))),      [Sex] [varchar](10) NULL,      [Occupation] [varchar](30) NULL,      [Ethinicity] [varchar](60) NULL,      [HomeTel] [varchar](15) NULL,      [Mobile] [varchar](15) NULL,      [varchar](40) NULL,      [AddressLine1] [varchar](30) NULL,      [Line2] [varchar](30) NULL,      [Line3] [varchar](30) NULL,      [City] [varchar](20) NULL,      [PostCode] [varchar](15) NULL, CONSTRAINT [PK_Patient] PRIMARY KEY CLUSTERED (      [PatientID] ASC)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]) ON [PRIMARY] GOSET ANSI_PADDING OFFGOALTER TABLE [dbo].[Patient]  WITH CHECK ADD  CONSTRAINT [FK_Patient_User] FOREIGN KEY([UserID])REFERENCES [dbo].[User] ([UserID])GOALTER TABLE [dbo].[Patient] CHECK CONSTRAINT [FK_Patient_User] CREATE TABLE [dbo].[PatientDetails](      [PatientID] [int] NOT NULL,      [PatientDetID] [int] IDENTITY(1,1) NOT NULL,      [Date] [smalldatetime] NULL,      [NHSNumber] [varchar](12) NULL,      [HospitalRefID] [varchar](10) NULL,      [Ovaries] [varchar](15) NULL,      [ReportFromGP] [image] NULL,      [LMP] [datetime] NULL,      [DateStopped] [datetime] NULL,      [Comment] [varchar](150) NULL, CONSTRAINT [PK_PatientDetails_1] PRIMARY KEY CLUSTERED (      [PatientDetID] ASC)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GOSET ANSI_PADDING OFFGOALTER TABLE [dbo].[PatientDetails]  WITH CHECK ADD  CONSTRAINT [FK_PatientDetails_Patient] FOREIGN KEY([PatientID])REFERENCES [dbo].[Patient] ([PatientID])GOALTER TABLE [dbo].[PatientDetails] CHECK CONSTRAINT [FK_PatientDetails_Patient] 
I want to incorporate this through database level .
I am using SQL Server2005-Express
Although  Iam using ASP.net C# I am new and I will not be able to do this in my front end.
Please help me wth the solution.
thanks
rameshs_2000

View 4 Replies View Related

Query To Retrieve First Names Of Person Where Last Name Is John

Dec 16, 2013

I need a query which retrives the firstnames of the person where last name is john. I am considering the performance as the main factor.

Is there any better query than the following?

Select firstname from tablename where lastname='John'

Considering performance as the major factor....

View 2 Replies View Related

Cannot Add Select User Permission For A Person On A Table

Feb 24, 2008



Hello,

I am trying to grant solely the SELECT privilige to a particular user and to a particular database.I am using Sql server management studio express to do this and SQL Server 2005.

I have created a new login and set the server roles to public, then on the user mapping page, I have checked the box next to the table I wish the user to access and have selected the database membership role - public.
Next, On the Table Properties, I have added the user on the Permissions Page and Selected "Select" under the grant column

When I click on effective permissions, the select command does not show and when I try to login with that user, the table does not display.

What I am doing wrong?

Thanks

View 7 Replies View Related

Need Help With Stored Proc. Pretty Simple For An Experienced Person

Sep 6, 2007

Can someone help me rewrite this query? Basically I need to check if the name stored in "CustomerName" already exist in the table "Renter". If it does not then I ncan insert the new customer name
into the data table "Renter". If the value in "CustomerName" already exists in the database, then I need to bypass the "INSERT" and somehow return a value indicating that the insert was not performed. How can I do this. Here is the
query I currently have for performing the insert.
CREATE PROCEDURE [dbo].[VacancyGet]
(
@CustomerName nvarchar(100),
@ClientCode nvarchar(10)
)
AS
INSERT INTO Renter
(
CustomerName,
ClientCode,
)
VALUES
(
@CustomerName,
@ClientCode,
)
 

View 1 Replies View Related

Windows 2003 - SQL Server Bug? REWARD To Person Who Fixes This Problem!!!

Dec 20, 2003

Hello.

I am about at the end of my rope. My ISP (MaximumASP) has recently upgraded to Windows 2003. I have an application written in .Net / C# that has been working for almost a year now with no problems. The day they upgraded to Windows 2003, that all changed. Upon accessing a page (ANY page) that opens the database (SQL Server) an error occurs INTERMITTENTLY. The error is:

Specified SQL Server not Found

Go to this link for the full error and Stack Trace: http://www.acob.com/SQLError2.gif

Keep in mind this NEVER happened before 2003 (same exact code, no changes) AND it will NOT happen for hours at a time (same page, same code), then BOOM! If we wait about 10-15 minutes or so, the page (EVERY page that accesses the database) works again and no error. This happens about 2-3 times within a 24 hour period. I can also RESET the web application (server) and the error disappears immediately.

I have gone through ALL of my code to make sure I am EXPLICITLY closing my data connections. This problem was MUCH worse when we were using the .Net 1.1 Framework AND Windows 2003. Reverting BACK to .Net 1.0 has alleviated the problem somewhat - but not entirely.

Does ANYONE know of any bugs, problems, whatever in Windows 2003 that may cause this. It seems as though there is a Connection Pooling leak of some sort. But I cannot fix, what I cannot find. My ISP simply keeps saying - it's a coding problem. If I have code that works for over a year and then suddenly it doesn't - is it a coding problem??

I DISPOSE of the DataSet.
I DISPOSE of my Connection.
I CLOSE my Connection.
I DISPOSE of my SQLAdapter.

I do this cleanup everywhere I initiate a connection (all in one codebehind). Additionally, the code is very simplistic: Connect, Open, Select, Bind, Dispose, Close.

Does ANYONE know what this can be??

$100 to the person who solves this problem! (Server must run for one week after solution is implemented to collect reward).

Thanks!!
AtlMaddog

View 16 Replies View Related

SQL Server 2012 :: Grouping By Percentage Of Time Worked By Person

Jul 2, 2014

I have a table of People and their ID, the starting month (a fixed number of months, say 10 for this), the ending month, and the percent of work time (0-1 being 0-100%). If they have a % work of 0, I do not want to see anything. But if the % changes, from say .5 to .75, I would need the first and last month they were at .5, and the first and last month they were at .75

The Table:

/****** Object: Table [dbo].[TestProject] Script Date: 02.07.2014 10:15:08 ******/
IF OBJECT_ID('TempDB..#TestProject2','U') IS NOT NULL
DROP TABLE [dbo].[#TestProject2]
GO
CREATE TABLE [dbo].[#TestProject2](
ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,

[Code] ....

The data:

--===== All Inserts into the IDENTITY column
SET IDENTITY_INSERT #TestProject2 ON
INSERT INTO #TestProject2
("ID","PersonID", "PercentLoad","MonthID")
SELECT 1,123456,0,1 UNION ALL

[Code] ....

EXPECTED RESULT:

Person ID StartMonth EndMonth LOADPCT
123456 3 4 .5
123456 5 6 1
654321 1 2 1
654321 4 4 .5
654321 5 6 .75
654321 7 9 .5

View 5 Replies View Related

Database Of Employees - Find Highest Pay Rate For One Single Person

Jul 10, 2014

I have a database of employees and pay rates.

One employee has two pay rates for two different jobs:

Job A: Rate $10.00
Job B: Rate $15.00

I will be updating their record so that they only have one job going forward, Job C. I need Job C to equal their HIGHER of the two existing jobs.

I have a select statement to find what the higher rate is. However, I am not sure how I can apply the rate to be the new job's rate. Here's what I used to find the highest rate for one single person:

SELECT max(rate), employeeID
FROM JobsTable
inner join IDTable
on JobsID2 = IDID2

WHERE JobCode in ('JOBA','JOBB')
and EmployeeID = '12345'
GROUP BY EmployeeID

(this returns the employee ID from one table, and the highest rate from Jobs A and B from another table)

I can get it to update to add JobC -- how can I get it to assign the result from the above query to be the rate used for Job C?

View 1 Replies View Related

Reporting Services :: How To Retrieve Computer Name Of The Person Running A Report

Oct 9, 2009

How can I identify the computer of the person running an SRS report?  If I query HOST_NAME() it gives me the host of the reporting server, not the person browsing.So, for example, Melissa opens a browser from computer named AAA to the SRS and pulls up her report.  How do I get that report to show that the user is browsing from AAA?

View 12 Replies View Related

SQL Server Admin 2014 :: Restore A Database Of Higher Version To Lower Version?

Sep 1, 2014

Is there any way to restore a database of higher version to lower version.

E.g. I have created a database in sql server 2012, created some tables & procedures in that.I took Full backup of that database. Can I restore it to sql 2008r2 or any lower version.

I know direct restore is not possible, I have to use either import or export option or generating script,but i want to know is there any easy step to do so.

Vimal Lohani
SQL DBA | MCP (70-461,70-462)

View 4 Replies View Related

Can I Install A Enterprise Version Analysis Service On A Standard Version Of SQL 2005 Server?

Jul 25, 2006

Hi all,

Since some analysis services features are only available in Enterprise version , I have to upgrade my SQL 2005 server from standard edition to enterpise edition.

So I uninstall originial standard version of analysis service and install a Enterprise version. However, the analysis service is still a standard version after installation.

Is it possible to keep data engine as standard version and install a enterprise version of analysis service?

Thank you very much

Tony

View 1 Replies View Related

Stored Proc To Get Single Person From A Table Based On Earliest Datetime

Oct 13, 2005

Hi,

I'm having problems with a stored procedure, that i'm hoping someone can help me with.

I have a table with 2 columns - Username (varchar), LastAllocation (datetime)

The Username column will always have values, LastAllocation may have NULL values. Example

Username | LastAllocation
------------------------
Greg | 02 October 2005 15:30
John | 02 October 2005 18:00
Mike | <NULL>

My stored procedure needs to pull back a user name with the following criteria:

If any <NULL> dates send username of first person where date is null, sorted alphabetically, otherwise send username of person with earliest date from LastAllocation

Then update the LastAllocation column with GETDate() for that username.

This SP will be called repeatedly, so all users will eventually have a date, then will be cycled through from earliest date. I wrote an SP to do this, but it seems to be killing my server - the sp works, but I then can't view the values in the table in Enterprise Manager. SP is below - can anyone see what could be causing the problem, or have a better soln?
Thanks
Greg
------------------------------------------------------------------------------
------------------------------------------------------------------------------
CREATE PROCEDURE STP_GetNextSalesPerson AS
DECLARE @NextSalesPerson varchar(100)

BEGIN TRAN

IF (SELECT COUNT(*) FROM REF_SalesTeam WHERE LeadLastAllocated IS NULL) > 0
BEGIN
SELECT TOP 1 @NextSalesPerson = eUserName FROM REF_SalesTeam WHERE LeadLastAllocated IS NULL ORDER BY eUserName ASC
END
ELSE
BEGIN
SELECT TOP 1 @NextSalesPerson = eUserName FROM REF_SalesTeam ORDER BY LeadLastAllocated ASC
END

SELECT @NextSalesPerson
UPDATE REF_SalesTeam SET LeadLastAllocated = GETDATE() WHERE eUserName = @NextSalesPerson


COMMIT TRAN
GO

View 2 Replies View Related

What Sql Server Version To Buy? Difference In Performance Between The Workgroup And Standard Version

Feb 19, 2008

Hello,I have been searching and reading a lots of information on the microsoft  website about the different version of SQL server, but still can not make my decision.In term of performance, is there a real big difference between the workgroup and the standard version? The workgroup is limited to 3 GB of RAM while the standard is unlimited, would that really change the performance if my server has 16Gb or RAM?The price difference is pretty substantial so if could only have to buy the workgroup , it would be better.One more question, regarding the type of licence, my server has 2 processors, could I avoid buying 2 licences and get the Server plus CAL instead. I am using this server to host 4 web-application running on SQL server. Each database is about 15 MB.Thanks in adavance for your advises.Arno

View 3 Replies View Related

Changing From SQL Server Enterprise Evaluation Version To Developer Version

Oct 30, 2007

I am wondering if it is possible to change from SQL Server Enterprise Evaluation Version to Developer Version. The reason is because the Enterprise Evaluation version expires after 180 days. So if I create reports and cubes in Enterprise Evaluation I can import them into Developer Edition right? Should I remove the Enterprise Evaluation version after 180 days or leave it then install Developer Edition?

View 1 Replies View Related

The Database 'x.MDF' Cannot Be Opened Because It Is Version 611. This Server Supports Version 607 And Earlier??

Mar 11, 2006

Hi,

I have tried to attatch a database ,created by SQL server Express within a C# application , in SQL server 2005 Enterprise edition, but the following error message appears:

-----------------------------

TITLE: Microsoft SQL Server Management Studio
------------------------------

Attach database failed for Server 'MEDO'. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft SQL Server&ProdVer=9.00.1187.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Attach database+Server&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

The database 'E:X.MDF' cannot be opened because it is version 611. This server supports version 607 and earlier. A downgrade path is not supported.
Could not open new database 'E:X.MDF'. CREATE DATABASE is aborted. (Microsoft SQL Server, Error: 948)

------------------------------



so , how can I solve this problem , I need to reed the data resides in the tables of 'X' database , how can I do it?? please help me.



Thanks in advance for any help.

Aya.

View 7 Replies View Related

Ssis Error Converting Database From Version 539 To The Current Version 611

Jan 7, 2008

I have an ssis package that downloads a SQL Server 2000 database and restores it to a SQL 2005 server. Ninety-five percent of the time it runs just fine, but every so often the job fails and I get the following set of error messages in the log file. (I have had to delete some of the proprietary information for this message to satisfy my boss like putting in [Database name] in place of the actual name of the database in the error message).

OnError, Restore backup file to DB,,,1/6/2008 2:59:31 AM,1/6/2008 2:59:31 AM,0,0x,Converting database [Database name] from version 539 to the current version 611.
OnError,1/6/2008 2:59:31 AM,1/6/2008 2:59:31 AM,0,0x,Converting database [Database name] from version 539 to the current version 611.
OnError,Restore backup file to DB,,,1/6/2008 2:59:31 AM,1/6/2008 2:59:31 AM,0,0x,Database [Database name] running the upgrade step from version 539 to version 551.
OnError,1/6/2008 2:59:31 AM,1/6/2008 2:59:31 AM,0,0x,Database [Database name] running the upgrade step from version 539 to version 551.
OnError,Restore backup file to DB,,,1/6/2008 2:59:31 AM,1/6/2008 2:59:31 AM,0,0x,The transaction log for database [Database name] is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases
OnError,1/6/2008 2:59:31 AM,1/6/2008 2:59:31 AM,0,0x,The transaction log for database [Database name] is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases
OnError,SQL5,1/6/2008 2:59:31 AM,1/6/2008 2:59:31 AM,0,0x,RESTORE could not start database [Database name].
OnError,SQL5,1/6/2008 2:59:31 AM,1/6/2008 2:59:31 AM,0,0x,RESTORE could not start database [Database name].
OnError,SQL5,1/6/2008 2:59:31 AM,1/6/2008 2:59:31 AM,-1073548784,0x,Executing the query "RESTORE DATABASE [Database name]
FROM DISK = '[Path to back up file]' WITH REPLACE,
MOVE 'ASP_Live_Data' TO [Path to MDF file],
MOVE 'ASP_Live_Log' TO [Path to LDF file]" failed with the following error: "RESTORE DATABASE is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
OnError,1/6/2008 2:59:31 AM,1/6/2008 2:59:31 AM,-1073548784,0x,Executing the query "RESTORE DATABASE [Database name]
FROM DISK = '[Path to backup file]' WITH REPLACE,
MOVE 'ASP_Live_Data' TO [Path to MDF file]',
MOVE 'ASP_Live_Log' TO [Path to LDF file]" failed with the following error: "RESTORE DATABASE is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
OnTaskFailed,Restore backup file to DB,,,1/6/2008 2:59:31 AM,1/6/2008 2:59:31 AM,0,0x,(null)

Does anyone have any suggestions?

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved