Greetings all:
I am looking for a way to get detailed information about each created table (regardless of permanancy) in a stored procedure, similar to the information one received from using sp_help on an individual table.
Does anyone know of such an animal?
Thank you, and Happiest of New Years,
Jack Cole
Magellan Healthcare, Inc.
I am trying to move data from 8 different tables that are dependent on each other through foreign key relationship.
Basically they have millions of rows in each table and they have data for the past 5 years. I want to move data for the past 120 days and move it to 8 new tables in the same database. So I created the new tables along with their relationships. Now I need to move in the order (parent table first).
The child table has 50million rows data to move
The intermediate tables have 10 mil 10mil 10mil and 40 mil 50mil and 20 mil rows to move
The parent table has 10 mil rows to move
if I choose to move this data through an SSIS package what is the best way? Or is there a better way to move this data faster?
I will be doing this move only once. After that I have maintenance purge jobs that will cleanup data on a daily basis.
I have a province table in a my database. I would like to link this province table to a resource table's Province_ID. This Province_ID is an int.
Vic Valentic
CEO/President
Open Door
2 Elite Dr. #33
Hamilton, Ontario
L8W 2N3
905-389-7492
http://www.wlu.ca/next/opendoor
Following is a stored procedure that currently runs on the system (compacted version). I need to combine this data with data from another Table .. tblAdjustments. The schema for this table is fairly close to tblShipmentDet.
tblShipmentHdr --> tblShipmentDet (Key = ShipmentID)
tblAdjustments --> standalone
Result: combine tblShipmentHdr + attached tblShipmentDet records with
tblAdjustments records.
Would the best approach be to use a UNION SELECT?
@XToDate datetime = '7/31/2005' ,@XBegDate datetime = '7/1/2005'
AS
SELECT
SHPH.ProductID,
SHPH.ReceivedDate,
SHPH.ShipmentNo,
SHPD.Vendor,
SHPD.Quantity,
QRecvdDate = CASE WHEN SHPH.ReceivedDate < convert(varchar(40),@XBegDate,121)
THEN NULL ELSE SHPH.ReceivedDate
END,
QShipQty = CASE WHEN SHPD.TransCd = 'F'
THEN NULL
WHEN SHPH.ReceivedDate < convert(varchar(40),@XBegDate,121)
THEN NULL
ELSE SHPH.ShippingQty
END,
PROD.ProductName,
QOpenAccrual = CASE WHEN MEND.OpeningAccrual is Null
THEN 0 ELSE MEND.OpeningAccrual
END
FROM dbo.tblShipmentHdr SHPH
LEFT OUTER JOIN dbo.tblProducts as PROD ON Left(SHPH.ProductID,7) = Left(PROD.ProductID,7)
LEFT OUTER JOIN dbo.tblShipmentDet as SHPD ON SHPH.ShipmentID = SHPD.ShipmentID
LEFT OUTER JOIN dbo.tblMonthend as MEND ON SHPH.ProductID = MEND.ProductID And MEND.MEPeriod = convert(varchar(40),@XBegDate,121)
WHERE ((SHPH.ReceivedDate >= '7/1/2005' AND SHPH.ReceivedDate <= '7/31/2005') OR (SHPD.DatePaid >= '7/1/2005' AND SHPD.DatePaid <= '7/31/2005'))
Hello,
I have a header and detail table. I want to create a constraint on the detail table, based on a value it's linked to in the header table. If the bit is checked in header then a unique value is required , if it's not checked then a NULL value is acceptable.
Many thanks in advance.
I need to write a sql query which is a master-detail query. Here's the example structure of tables:
Master table:
ColID as longint, ColA as int, ColB as int, ColPartID as longint, ColPartName as longint
Child table -- Wheel:
ColID as longint, ColA as int, ColB as int
Child table -- Door:
ColID as longint, ColA as int, ColB as int
Child table -- Window:
ColID as longint, ColA as int, ColB as int
..... etc
From the master table, it needs to join with its child in order to get the detailed information. However, there're more than one child table for it to join. In other words, the query has to choose the correct child table to join for each row selectively. The use of correct child depends on one of the columns in its master table (ColPartName).
My question is: Does it worth of me keep finding a solution for this query or should I abandon this? I really need some advice, please.
Many thanks,
Leonard
I am listing detail transaction lines in a table sorted by account and order number.
the problem is that I only want to see the detail if the sum of a value field is zero for all the transactions in an order otherwise ignore the detail for that order.
I was trying Group by and Having but this doesn't seem to do what I need.
Being relatively new to Reporting services, any nudge in the right direction would be useful.
Hello,
I'm new to SQL and need help with a query. Not sure if this is the right place.
I have 2 tables, one MASTER and one DETAIL.
The MASTER table has a masterID, name and the DETAIL table has a detailID, masterID, and value columns.
I want to return a populated MASTER table with entries based on the DETAIL.value.
SELECT MASTER.*
FROM MASTER
WHERE DETAIL.value > 3
This is a simplified version of my problem. I can't figure out how to set the relationship between MASTER.masterID and DETAIL.masterID. If I do an INNER JOIN, the number of results are based on the number of DETAIL entries. I only want one entry per MASTER entry.
Hope this makes sense.
How can I do this?
GrkEngineer
hi everyone, i've added 2 tables one is on adding, the other one is on returning. but when i return, the dropdownlist keeps displaying those which are returned already. it shouldnt display because i've already returned.
here are the codes that i add,
"INSERT INTO record VALUES( '" + dateOfIncoming + "', '" + manifestNo + "', '" + hoName + "','" + hoRemarks + "', '" + toName + "', '" + toRemarks + "', '" + purpose + "', '" + timeEntered + "', '" + dateEntered + "', 'Out')"
here are the codes that i return
"INSERT INTO returnRecord VALUES( '" + dateOfIncoming + "', '" + manifestNo + "', '" + hoName + "','" + hoRemarks + "', '" + timeEntered + "', 'In')"
here are the codes that i wanna display in the dropdownlist which i have set the status = IN. means they have returned already so it should display. this is wad i tried what its wrong ---->
"select a.manifestNo from record a, returnRecord r where a.status = 'Out' And r.status = 'In'"
my dropdownlist cant retrieve those records that are already returned. i suspect is the select statement that is wrong. but i cant figure out how. please help me!
regards, ethan
hi,
i have a table(Measurement) with the following columns
SensorId,Maximumvalue,MinimumValue,GroupId,SystemId,DeviceId.
so in the above column SensorId is ForeignKey of the Table Sensor which havs two columns(SensorId,SensorName)
so in the above column GRoupId is ForeignKey of the Table Group which havs two columns(GroupId,GroupName)
SystemId is ForeignKey of the Table System which havs two columns(SystemId,SystemName)
DeviceId is ForeignKey of the Table Device which havs two columns(DeviceId,DeviceName)
so my result should be as like as follows:
SensorId,SensorName,Maximumvalue,MinimumValue,GroupName,SystemName,DeviceName.
instesd of displaysing GroupId, SystemId,DeviceId i need to diplay the SensorName,GroupName,SystemName,DeviceName. so please give me some example query to join multiple tables please
Hi there
I am pretty new to SQL and need some help.
I am trying to make a database that is used as a tool in a glazing company.
I don't know if I am doing this the right way or not.
I have broken down the different products into 7 different tables as I believe they should not all be togeather as they all have different information.
The tables
tblBusWindScreen
tblCarWindscreen
tblTruckWindcreen
tblFlatGlass
tblTint
tblMoulds
tblSundries
I also have a table called tblOrders. (This is where all the order information will be kept.)
My question is how do I form a relationship between the 7 tables and tblOrders?
Do I add another table which I join all the PK from each of product tables then create a join between that table and tblOrders?
Any tips would be great as I am only used to creating tables that have 1 maybe 2 different tables joined to them.
Thanks for your help.
(SQL 7 on NT Server)
I want to append a table with 1200 rows, using DTS. While I know it is better to do while no one is using the database, exactly what impact will it have if I do this while the database is online? Which leads me to my next question: Exactly what operations can I do while the db is online, and what ones should I not even think of. Most of my needs are data imports and exports. I haven't found much in Online Books about this. Any help would be appreciated very much.
Rob
I have two tables in seperate dbs that I need to match up users and update the passwords in the second db table. I know how to match up the data and I'm sure I can do this using a cursor but I was wondering if there is a way I can do this with an update statement. Any suggestions(including telling me I'm stuck using a cursor) would be great.
Thanks ahead of time
EDIT: Nevermind, figured it out :D Thanks anyway.
I am trying to do here is to load different flat files to different tables:
For example, if the file name starts with "enrollment", then it goes to table "enrollment" table;
if the file name starts with "student", then it goes to "student" table.
For now, I created a foreach loop container for the each different files. So it ended up using several foreach loop containers. I am wondering if there is a way just to use one foreach loop containters to process the loading.
Anyone shed some light on this?? Thank you very much for your help!
Hi Group,My apologies for the lengthy post, but here goes...I have the following tables:TABLE Vehicles([ID] nvarchar(5),[Make] nvarchar(20),[Model] nvarchar(20),)TABLE [Vehicle Status]([ID] int, /* this is an auto-incrementing field*/[Vehicle ID] nvarchar(5), /* foriegn key, references Vehicles.[ID] */[Status] nvarchar(20),[Status Date] datetime)Here's my problem...I have the following data in my [Vehicles] and [Vehicle Status] tables:[ID] [Make] [Model]----------------------H80 Nissan SkylineH86 Toyota Aristo[ID] [Vehicle ID] [Status] [Status Date]----------------------------------------1 H80 OK 2006-10-012 H80 Damage 2006-10-053 H86 OK 2006-10-134 H86 Dent 2006-10-155 H86 Scratched 2006-10-16I need a query that will join the two tables so that the most recentstatus of each vehicle can be determined. I've gotten as far as:SELECT Vehicle.[ID], Make, Model, [Status], [Status Date] FROM[Vehicles] INNER JOIN [Vehicle Status] ON [Vehicles].[ID] = [VehicleStatus].[Vehicle ID]Of course this produces the following results:[ID] [Make] [Model] [Status] [Status Date]--------------------------------------------H80 Nissan Skyline OK 2006-10-01H80 Nissan Skyline Damage 2006-10-05H86 Toyota Aristo OK 2006-10-13H86 Toyota Aristo Dent 2006-10-15H86 Toyota Aristo Scratched 2006-10-16How do I filter these results so that I get only the MOST RECENT vehiclestatus?i.e:[ID] [Make] [Model] [Status] [Status Date]--------------------------------------------H80 Nissan Skyline Damage 2006-10-05H86 Toyota Aristo Scratched 2006-10-16Thanks in advance,Rommel the iCeMAn*** Sent via Developersdex http://www.developersdex.com ***
View 1 Replies View RelatedI've got a lot of XML like this (simplified):
Code Snippet
... 8 MORE
... 9 MORE TIMES
I need to get this into three existing SQL Server 2005 tables, each with identity columns for their primary keys:
Code Snippet
CREATE TABLE ELEMENT1 (
[ID] INT IDENTITY
)
CREATE TABLE ELEMENT2 (
[ID] INT IDENTITY,
[ELEMENT1_ID] INT
)
CREATE TABLE ELEMENT3 (
[ID] INT IDENTITY,
[ELEMENT2_ID] INT
)
With primary and foreign keys as you'd expect, and, of course, many more columns!
How would I get this into tables through SSIS, preferably in a high-performance manner (there may be several gigabytes of XML to load).
The issue, of course, is that in order to insert an ELEMENT2 row, I need the ID from the coresponding ELEMENT1, etc.
Any ideas or pointers to articles would be welcome.
hey everyone,I'm having trouble cranking out an appropriate SQL query for what I'm trying to do. I'm trying to add a ratings system into a website, I have 2 relevant tables.Files = table listing all uploaded filesRatings = table listing all ratings for filesThe two DB's are theoretically linked by the fact that the ratings table has a field for "fileID" which matches up to "fileID" in the files table. The way the page works, is that there is a querystring in the URL "filename" where "filename" is a field within the files table. So basically I'm looking for the AVG rating.ratings, where rating.fileID = files.fileID, and the appropriate files.fileID is gained from files.fileID WHERE files.filename = request.querystring["filename"]holy crap, i hope that made sense to someone here. i'm not great at explaining this. My problem is I want the AVG ratings.rating from the table ratings.... where ratings.fileID = files.fildIDWhat I have so far, that works except for the AVG part is below. This code will pull only the ratings for the appropriate file, the problem is whenever I try to add "AVG(something)" to it, the query fails.SELECT files.FID AS Expr1, files.filename, ratings.FID, ratings.ratingFROM files INNER JOIN ratings ON files.FID = ratings.FIDWHERE (files.filename = @filename) @filename value is gained from the URL querystringany ideas?
View 1 Replies View RelatedHelp -
I need to import data into an existing table. Most import rows were unique, so I had no problem using DTS and appending. However, some import rows match existing rows except for one column/field that contains updated/new data, and I have to either replace the entire row with the imported row, or replace the individual field with the new data. How do I do that when there are many rows to import? It would take forever typing in all the data using UPDATE. Thanks in advance for your help!
rb
Hi!
There is a view in our replicated SQL-2000 database, that returns all user tables and views with replication state (0 if not included into publication, 1 if included):
Code Snippet
CREATE VIEW [dbo].[ViewREPL_PublishedObjects]
AS
SELECT TOP 100 PERCENT
CASE [xtype]
WHEN 'U' THEN 'Table'
WHEN 'V' THEN 'View'
ELSE NULL END AS [Object Type],
[name] AS [Object Name],
CASE WHEN [replinfo] = 0
THEN 0 ELSE 1
END AS [Replicated]
FROM [sysobjects]
WHERE
[xtype] in ('U', 'V')
AND [status] > 0
ORDER BY
(CASE [xtype]
WHEN 'U' THEN 1
WHEN 'V' THEN 2
ELSE 10
END),
[name]
Now we need to upgrade our database to SQL-2005, but [sysobjects] table have been changed, so neither Replicated state could be determined according on [replinfo] column value, nor User/System object according on [status].
So, I need a view with same functionality, that will work under SQL-2005 and 2008.
Please, help!
I have three columns (CWOC, C, and PWC). both CWOC and PWC are dependent on the value in C, yet C is dependent on the value in PWC.
Basically:
C = PWC * BR
PWC = MR - CWOC
CWOC = MC - C
In setting up these columns in the database how do I set up columns like these that are so dependent on each other?
This was set up previously in Excel using iteration, but now I'm recreating the application in VB.net and SQL Server.
Thanks for your suggestions.
I have a ABC service on a server1 that is dependent on MSSQLSERVER service running on server2. Does anyone know how to setup the service ABC on server1 to have a dependancy on MSSQLSERVER running on server2?
Thanks,
jgs
I am trying to query only the Max date dependant on quantity
Create Table dbo.TestParts
(Part char(30), Desc1 char(50), Desc2 char(50));
Create Table dbo.TestStructure
(Model char(30), Part char(30), EDATE smalldatetime, QtyPer float);
GO
Insert INTO dbo.TestParts Values('101111','Widget A', 'Batteries Not Included'),
[Code] ....
Looking for a return of:
PART Description EDATE QtyPer
101112 Widget B ..... 2012-12-03 3
101113 Widget C ..... 2012-12-03 5
101114 Widget D ..... 2012-12-01 1
Widget A should not show because the last date the qty was changed to Zero
Closest that I have come..........(which is pulling the part 101111 which should be incorrect)
Select ts.Part, RTRIM(tp.Desc1) + ' ' + RTRIM(tp.Desc2) as Description,
ts.EDATE, ts.QtyPer
FROM testing.dbo.TestStructure ts
Inner Join (Select Part,MAX(EDATE) as Date
FROM testing.dbo.TestStructure
WHERE QTYPER <> '0'
[Code] .....
I am trying to do a select query like below...
SELECT INVENTORY_ITEM_TAB.ITEM_NO, INVENTORY_ITEM_TAB.DESCR, INVENTORY_ITEM_TAB.STATUS_FLG, INVENTORY_ITEM_TAB.PRICINGUOM,
INVENTORY_ITEM_TAB.PURCHUOM, INVENTORY_ITEM_TAB.ITEM_CATEGORY, INVENTORY_ITEM_TAB.ICINTERNALNOTES, INVENTORY_WHS.QTY_ON_HAND,
INVENTORY_WHS.QTY_ON_ORDER, INVENTORY_WHS.QTY_ALLOCATED
FROM INVENTORY_ITEM_TAB INNER JOIN
INVENTORY_WHS ON INVENTORY_ITEM_TAB.ITEM_NO = INVENTORY_WHS.ICWHSPCODE
WHERE (INVENTORY_ITEM_TAB.ITEM_CATEGORY = 'SS') AND QTY_ALLOCATED, QTY_ON_HAND, Qty_On_ORDER <> 0
ORDER BY INVENTORY_ITEM_TAB.DESCR
But I don't want to select records if all of these fields have a 0 in them - QTY_ALLOCATED and QTY_ON_HAND and QTY_ON_ORDER.
how to do this type of query. If any of those fields doesn't have a 0 then I would want to return it. I just don't want them if all three of those fields have 0.
Suppose I have the following table:col1 col2hammet jonesjlo afflectafflect armandwills snoptarmand hammetjones smithIf someone choses armand, then I'd like to returnamand hammet jones smithThe first selection goes over to the second column, gets that valueand locates it back in column one and returns column 2 and so on.One way of doing it is to set up a separate query for each one andthen construct a new query to get them all.I'm thinking there's a more elegant way to do this. Any suggestionswould be appreciated.-David
View 2 Replies View Related
Hi,
I have a problem with a report.
in this report 1 use 2 combo boxes. The first with my organisation on different levels.
And in the second a value for every level and all.
Now i want to filter the organisation box with the second combo box.
For the first time it works but if i change after that the box it doesn't work.
I use SQL Reporting Services 2005
I was just analysing the security which can be given to different users to access respective databases. So i tried, on my local server, to deny permission to myself to access Model database. After this i am not able to connect to my local server at all. Error: Permission denied. I am using windows authentication mode.
I have also deleted the local server registration, and re-registered it, but still the condition is same. Do i need to uninstall SQL Server completly to get rid of this prob.? I also registered a new data server, and there every thing is going fine. So now what do i do to get connected to my local server.
Hi,
I have a report which uses a parameter called "Interval" with possible string values of "Daily", "Weekly", and "Monthly" and I want these values to set the default values for my StartDate and EndDate parameters. However, the default value remains static. And I have tried all of the solutions found in this forum. Any suggestions?
I am using SSRS 2005. And I have tried using an iif statement for StartDate, also tried Property->Custom Code, and also tried using stored procedure to populate Start and End Dates, but none of these provide dynamic parameter setting like I was hoping for.
And I think this should work because I know that if it is not a date that it works correctly. For example if I have one parameter called country and a subsequent one named state, every time I change the country it will let me re-pick the state. But this is not working correctly here.
Any ideas on what I am doing wrong or how to fix this?
I am not able to delete a row due to the presence of its parent table. There is no circular relationship and the child has no dependencies when I verify the Delete trigger. Any advice? See error msg below:
Msg 30010, Level 16, State 1, Procedure tD_My_child_table_name, Line 43
Cannot DELETE last My_child_table_name CI because My_parent_table_name exists.
Msg 3609, Level 16, State 1, Line 1
The transaction ended in the trigger. The batch has been aborted.
Hi there,
I'd need to generate multiple recordsets within a MS SQL stored procedure. The thing is these recordsets depend on each previous one (the first one obviously has parameters). How can I make this work? I can't create views with SPs. Thank you very, very much.
Martin
I want to execute multiple SQL Server scheduled jobs using OSQL, but I only want to execute a job if the previous job completed. Is this possible? So far I am unable to prevent all jobs from starting before any have completed.
OSQL -Sservername -E -b -Q"usp_start_job 'JobNumber1'" -o usp_start_job.out
IF ERRORLEVEL 1 GOTO ERROR
OSQL -Sservername -E -b -Q"usp_start_job 'JobNumber2'" -o usp_start_job.out
IF ERRORLEVEL 1 GOTO ERROR
OSQL -Sservername -E -b -Q"usp_start_job 'JobNumber3'" -o usp_start_job.out
IF ERRORLEVEL 1 GOTO ERROR
GOTO EXIT
:ERROR
ECHO *** ERROR *** Check Log File
:EXIT
*** JOB COMPLETED ***
Dave
We have an interesting performance issue with a distributed query. When run by a system-administrator account, the remote computer returns the requested row set. It does the same thing for small row counts (<=7) for user accounts. On larger row counts, however, the user account returns the ENTIRE rowset from the remote table, and performs the restrict operation locally. Performance-wise, this results in a difference between 11 seconds and 12 MINUTES. I'm assuming it's some sort of security issue in DTC, but the remote server is on Windows Server 2000, and there's no security button for DTC under Component Services.
Suggestions?
The query in question is:
EXEC ("INSERT INTO #XML (vin, ws_xml, lang_id)
SELECT slo.vin,
br.xml,
br.lang_id
FROM #SALE_LINEUP_ORDER slo
INNER JOIN RemoteServer.mydatabase.dbo.build_record br ON slo.vin = br.vin
INNER JOIN #LANGUAGE_IDS li ON br.lang_id = li.lang_id")
Excuse my illiteracy in this subject, I just learned about this yesterday, I managed to create a report by using business intelligence report wizard, and when I preview it seems that I can view the fields that I wanted to view.
But in addition to that I also want to add hyperlinks to some of the fields, because I am planning to embed the whole report in a web page. For example if I go on the field that I want to add hyperlink and right click->properties->navigation tab->Jump to url, I thought that I can handle it. Actuall it works fine if I link it to http://www.microsoft.com but let's assum e the name of the field is ID, and you want to link to http://somewebsite/somepage.aspx?id= (id in that cell), how can I achieve this parametric behavior, actually I tried right click cell->properties->navigation tab->Jump to url, and filled url text box http://somewebsite/somepage.aspx?id=&(concatenation operator)=Fields!ID.Value but what happens is instead of evaluating the value of ID field, it takes expression as text as text and that's why it links to totally wrong url. Is there a way to get around this problem.
And can somebody suggest me a detailed tutorial on programmatically using reports in .net web applications using c#.
If somebody can help me I'd be really glad.
Erinc
Hello
I have following data and environement is SQL Server 2000.
Hello
I have following data and environement is SQL Server 2000.
MemberID
Dependent_Empid
DependentName
Benefit
1
100
Kalim
100
2
100
Brian
100
3
100
Swadia
200
4
101
Allan
200
5
101
Cynthia
200
6
103
Noreen
200
7
103
Larry
300
8
103
Mike
300
and I am desired for the following output but I dont want to use function. Can anybody help me for script for the desired output. I will be much thankful.
Dependent_Empid
Members
100
Kalim 100, Brian 100, Swadia 200
101
Allan 200, Cynthia 200
103
Noreen 200, Larry 300, Mike 300