I have a tables called subsid that I need to change the datatype from
text to int.
I think I got them all but is there a query I can run that will check
all fields call subsid accross all tables that are of type text.
I need to migrate data from one sql database to another. The second DBis a newer version of the "old" database with mostly the same tablesand fieldnames. In order support some reporting queries in the "new"version I needed to change the datatype of a few fields from varchar toint(the data stored was integers already as they were lookup tables).DTS works great except in the cases of about 10 fields which I changedthe datatypes on from varchar to int.DTS seems to drop the data if the fieldname and datatype are not anexact match. Is there any way to use DTS and have it copy data from afield call subsid type varchar to a field call subsid type int?
View 3 Replies View RelatedI know there is some kind of rule against the following SQL statement, but I was wondering what to do to get around this problem (some kind of grouping). Sorry for the stupid question.
SELECT * FROM Table1, Table2 WHERE Table1.ID IS NOT NULL AND Table2.ID IS NOT NULL
Basically I want to select all records from the two tables (they have the same fields, but are just different specialties) and then output them, but there is nothing in common between the two to reference one another, and it ends up in some kind of loop. Thanks. for the help.
Here is my situation. I am building a report that has three different tables each with their own dataset. Example: Opportunities, Leads, Activities. All three of these datasets/tables have a common field - SalesID. I would like the report to show the first SalesID, then all Opportunities for that SalesID in the Opportunities table, followed by all Leads for that SalesID in the Leads table, followed by all Activities for that SalesID in the Activities table, and then rollover to the next SalesID and repeat that for all SalesIDs. Any suggestions on how I could achieve this? Thanks in advance for all help!!!
View 6 Replies View RelatedHi All.
I apologise if i have not posted this in the correct Topic before i start. But was uncertain where to post this query.
This is my first project in ASP.NET, MS Visual Web Developer 2005 Express and SQL Server 2005 Express. I have relatively little experience, so please bare with me. I have the table below:-
Column Name Data Type
OrderID intOrderNo intInvoiceNo varchar(50)PurchaseDate smalldatetimeCost moneyInsure money
I wish to have a dropdownlist on an aspx page display the OrderNo and the Cost e.g. 12345 £3.50. I have tried the SQL Statement below but that just calculates the addition of both.
SELECT OrderNo + ' ' + Cost AS Expr1
FROM [Order]
Do i have to convert them both to string/varchar first? If so, how?
Thanks for your replies.
Hello-
I have a sql query that I am using to populate a datagrid. The problem is one of the tables is a month table. and the other tables are full of data. So there is no common column name to match using a inner join "on".
How do i do this?
I want to create a table withmember id(primary key for Students,faculty and staff [Tables])and now i want to create issues[Tables] with foreign key as member idbut in references i could not able to pass on reference as orcondition for students, faculty and staff.Thank You,Chirag
View 3 Replies View RelatedConditional Update of a field from multiple tables..I have a target table with two fields: Date and ID..There three source tables: S1, S2, S3, each of them has three fields: Date, ID, and Score...Now I want to update the target table: put the ID into the ID field which has the highest Score from the three tables on each day.
View 13 Replies View RelatedI have an XML file loaded into a table with XML type column. The XML format is such:
<CsResponse>
<Reports>
<Report>
<Id>186192</Id>
<DateCreated>9/29/2015 1:19:56 PM</DateCreated>
<DateUpdated>9/29/2015 2:19:10 PM</DateUpdated>
<RequestType>Roadway Sign (Damaged/Missing)</RequestType>
[Code] ....
I am trying to create 2 tables, one (Table 1) having:
Id
DateCreated
DateUpdated
RequestType
RequestTypeId
StatusType
StatusTypeIsClosed
The other (Table 2) with:
Id
DateCreated
Key
Value
Ultimately, these 2 table will be joined on Id. Here is the SQL, Table 1 creates properly, and Table 2 creates, but right now 'Id' comes in as NULL. How do I add the Id field from the upper node into Table 2 (excuse my terminology if it is incorrect) ?
DECLARE @XML AS XML, @hDoc AS INT, @SQL NVARCHAR (MAX)
SELECT @XML = XMLData FROM pubanalysis.dbo.CS_XMLReportData
EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML
--TABLE 1
INSERT INTO pubanalysis.dbo.CS_RequestReports
[Code] ....
i want to check if one of the fields is empty. however i cant seem to get it working
string strSQL = "select * from 15Course_Listing where
Package_listingNo =''";
it does not return anything, however i have empty fields in package_listingNo
i have been trying to get around this for some time and its killing me. need help please
Hi,
I have one dymic report and one of the requirement is that,I want to check whether particular field is present there or not
For e.g In one of the case Fields!Name will come and in other case it won't.
There one dynmic column which calculated values based on all the column.
If I write in value field of this column as follows.[If status = "Name" and Field is not missing then take Name value,if field name is Consumer take value from Consumer and shows it in column.]
=Switch(Fields!portfolio_status.Value="Name",IIF(Fields!IName.IsMissing,"0",Fields!Name.Value)
,Fields!portfolio_status.Value="Consumer",IIF(Fields!Consumer.IsMissing,"0",Fields!Consumer.Value))
In case,portfolio_status doesn't give me value "consumer" then I expect "0" to get printed.Problem here is that IIF expression tries to evaluate Fields!Consumer.Value even if it is not using this value and because of that my calculated column is not working.
I tried IsMissing,IsNothing..Nothing seems work.Basically I want to check if that field is there in result set or not.
IIF(IsNothing(Fields!Consumer),"0",Fields!Consumer.Value)
IIF(Fields!Consumer.Value Is Nothing,"0",Fields!Consumer.Value)
I have a table that tracks some dates, and I am looking for an SQL statemet that will check all of the fields to see if a prespecified data range is true. For exampe you enter a start and end date and then then the SQL statement would check about 12 different fields to see if any of the dates are within that range. I am trying to use an or statement, but to no avail. So if you have any help it would be greatly appreciated.
View 1 Replies View RelatedThere was a similar question to mine a few time ago about conditional execution, but I'm having some problems with it to which I couldn't find any answer, could anyone help?
My condition is checking if a DateTime from a DB table is null. I use the Execute SQL Task to retrieve the record (SELECT TOP 1 * FROM myTable WHERE conditions = TRUE), then I map 2 fields to their respective global variables.
Then I created the green arrow connector and used a precedence expression based on sucess. I used the expression IsNull(@variable). For some reason, even if the @variable comes as null - I checked the info in the DB - it still comes out as '1999-11-30'
I've looked around and the IsNull method can be used to also change the value in the event of it being null, could it be changing my variables value to this even if I didn't pass that value as a parameter to the function? If so, how else can I test it for null? I've tried "== null", "= null" and even ".equals(null)" - which I know is absurd, but I'm running out of options here...
Could you enlighten me, please???
Thanks in advance!
i want to check a value in 2 tables. the 1st table i want to check if the value exists and from the 2ed table i samething.
i came up with this but when it's doing the second if i get error on the page.
Dim ReturnVal As Integer
Dim ReturnVal2 As IntegerDim conn As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("imacstestConnectionString").ConnectionString)
Dim cmd As SqlCommand = New SqlCommand("SELECT [ReportNumber] FROM [AppraisalSummaryBlue] WHERE ([ReportNumber] = @ReportNumber)", conn)
'To check if the # is registered.Dim cmd2 As SqlCommand = New SqlCommand("SELECT [ReportNumber] FROM [t_RegisterInfoTemp] WHERE ([ReportNumber] = @ReportNumber)", conn)
cmd.Parameters.AddWithValue("@ReportNumber", txtReport.Text)cmd2.Parameters.AddWithValue("@ReportNumber", txtReport.Text)
conn.Open()
ReturnVal = Convert.ToInt32(cmd.ExecuteScalar())
conn.Close()
'This checks if the # is correct.
If ReturnVal > 0 Then
'This checks if its registered with someone else.
conn.Open()
ReturnVal2 = Convert.ToInt32(cmd2.ExecuteScalar())
conn.Close()
If ReturnVal2 <> 0 ThenServer.Transfer("regccinfo.aspx")
Else
lblError.Text = "the # is registered with someone else."
End If
Else
lblError.Text = "the # does not exists."
txtReport.Focus()
End If
Hi! I have a general SQL CE v3.5 design question related to table/file layout. I have an system that has multiple tables that fall into categories of data access. The 3 categories of data access are:
1 is for configuration-related data. There is one application that will read/write to the data, and a second application that will read the data on startup.
1 is for high-performance temporal storage of data. The data objects are all the same type, but they are our own custom object and not just simple types.
1 is for logging where the data will be permanent - unless the configured size/recycling settings cause a resize or cleanup. There will be one application writing alot [potentially] of data depending on log settings, and another application searching/reading sections of data.
When working with data and designing the layout, I like to approach things from a data-centric mindset, because this seems to result in a better performing system. That said, I am thinking about using 3 individual SDF files for the above data access scenarios - as opposed to a single SDF with multiple tables. I'm thinking this would provide better performance in SQL CE because the query engine will not have alot of different types of queries going against the same database file. For instance, the temporal storage is basically reading/writing/deleting various amounts of data. And, this is different from the logging, where the log can grow pretty large - definitely bigger than the default 128 MB. So, it seems logical to manage them separately.
I would greatly appreciate any suggestions from the SQL CE experts with regard to my approach. If there are any tips/tricks with respect to different data access scenarios - taking into account performance, type of data access, etc. - I would love to take a look at that.
Thanks in advance for any help/suggestions,
Bob
In the olden days when I used databases that kept each table in a separate file, I used to be able to tell when a specific table was modified by the date stamp on the file.
Is there a method in SQL server to check the "last modifed date" at the table level?
I didn't see anything in sysobjects.
Hi All,
Is it possible in SQL to Restrict value in one table checking a value on anather tables.
Scenerio-1.
I have two table let say,
Teb1 and Teb2. Teb1 has a column called- Business_type and Teb2 has a coulmn called Incorporated_date. I just need to restrict If the value of Business_type column in Teb1 is "Propritory" then Incorporated_date in Teb2 should not be blank (nulll) . Otherwise it can take null value.
Scenerio -2.[/B]
I have table called [B]SIC.
This table has a two column called SIC1 anc SIC2 . Is it possible to restrict that clumn SIC1 and SIC2 should have same values( duplicate values cannot be entered in both columns.
Please Advise.
Vijay
Is there a command I can use to tell me all the tables in my schema that have a column name "Id"?
View 5 Replies View RelatedFor every table in my database there is a duplicate table with same columns. For example, employee is the name of main table, there is employee_dup table in same database.
There is only one column extra in _dup tables i.e.,idn column.
Now, I want to know all the columns present in main table which are not present in corresponding _dup table. There might be a chance of missing one or two columns in _dup tables. So i want a query to find out all the columns present in main table that are not present in hx table.
ok, ill lay it out like this, and try not to be too confusing.
I need to check my trasaction table to see that the user with a UserID bought, keeping track(adding up) of how many times they bought the Product with a productID, , then i need to selcect items from product table with the productID for the items the user bought,
i know how to return the product information, and how to check the tranaction table and i think how to add up the amount of tranastions for that item
so bacially i need to select data from a product table, for the products that the user bought tahts stored in the transaction table.
well i tried not to be confusing, but i think i failed
For every table in my database there is a duplicate table with same columns. For example, employee is the name of main table, there is employee_dup table in same database.
There is only one column extra in _dup tables i.e.,idn column.
Now, I want to know all the columns present in main table which are not present in corresponding _dup table. There might be a chance of missing one or two columns in _dup tables. So i want a query to find out all the columns present in main table that are not present in hx table.
I want to do something with error checking in my company. For this we have a selection of different tables and the data needs to meet various validation rules else it is classed as an error.
To deal with this I'm currently thinking of this approach:
1. Create a view pulling all of the various data together from the multiple tables.
2. Create an empty 'errors' data table.
3. Create an Excel file with a button to call a Check for Errors Script
Then in the the script:
1. Clear the 'errors' data table
2. Call multiple scripts, each of which uses the new view, applies the checks for that specific error and writes any erroring data into the 'errors' data table (along with a text string with the unique error code for filtering / sorting purposes).
3. After calling all the scripts, the table can be refreshed in excel when when used with a pivot table can show the various errors, and let us drill down into all the data so we can fix them.
Also.. Ideally, I'd like some way to write comments in an excel column for each entry and error code and be able to write that back into a comment table.
Hello,
I have an SSIS package that affects data in fourteen tables. This SSIS packge is supposed to insert, update, delete and apply various business rules to the data in the new database tabes in the same way that it occurs in the DTS package that affects the old tables in the former database.
After both the DTS packge and SSIS packages are run, the data in the fourteen tables from both databases need to mirror each other. I figure this is the best way that I can know that the new SSIS package is working correctly.
Is there something I can do in an SSIS package that can compare all of the tables between the two databases, record for record then (maybe as error output) produce output in the case where a mistmatch was detected between two given tables? Would a lookup transformation be helpful for this kind or thing, or should I think about doing it some other way?
The old database is on SQL Server 2000, and the new one on SQL Server 2005 64 bit. I've run into problems creating a linked server between the two to where I could just run queries to do this kind of check.
Any ideas?
Thank you for your help!
cdun2
I have a table called BidItem which has another table calledBidAddendum related to it by foreign key. I have another table calledBidFolder which is related to both BidItem and BidAddendum, based on acolumn called RefId and one called Type, i.e. type 1 is a relationshipto BidItem and type 2 is a relationship to BidAddendum.Is there any way to specify a foreign key that will allow for thedifferent types indicating which table the relationship should existon? Or do I have to have two separate tables with identical columns(and remove the type column) ?? I would prefer not to have multipleidentical tables.
View 26 Replies View RelatedI need formulating a view through which I can create a an output like below image -
Monthly Table
NUM STATUS ACTIVITYCODE
HAX603 Completed 0x45845a
HAX317 Completed 0x112z44
HAX465 Completed 0x1155x4
HAX523 Completed 0x124c69
Season Table
NUM STARTMONTH STARTDAY ENDMONTH EMDDAY
HAX603 JULY 1 OCTOBER 31
HAX317 DECEMBER 1 DECEMBER 31
HAX317 MARCH 1 MARCH 31
HAX317 July 1 July 28
[Code] ...
Final Output
NUM STATUS ACTIVITYCODE <SEASONS>
HAX603 Completed 0x45845a 1 JULY - 31 OCTOBER, 1 DECEMBER - 31 DECEMBER
HAX317 Completed 0x112z44 1 DECEMBER - 31 DECEMBER, 1 MARCH - 31 MARCH, 1 July - 30 July
HAX465 Completed 0x1155x4 1 MARCH - 31 MARCH, 1 July - 28 July, 1 August - 30 August
HAX523 Completed 0x124c69 1 November - 30 November
I have written a query to join the values of multiple field, but lacking in as how will I formulate a view which will check for the duplicate values of Num fields and merge there values in a single field like season.
select num, (CAST(startday AS VARCHAR(3)) + ' ' + startmonth + ' - ' + CAST(endday AS VARCHAR(3)) + ' ' + endmonth)AS Season from seasons;
I am trying to create a report using Reporting Services.
My problem right now is that the way the table is constructed, I am trying to pull 3 seperate values i.e. One is the number of Hours, One is the type of work, and the 3rd is the Grade, out of one column and place them in 3 seperate columns in the report.
I can currently get one value but how to get the information I need to be able to use in my reports.
So far what I've been working with SQL Reporting Services 2005 I love it and have made several reports, but this one has got me stumped.
Any help would be appreciated.
Thanks.
I might not have made my problem quite clear enough. My table has one column labeled value. The value in that table is linked through an ID field to another table where the ID's are broken down to one ID =Number of Hours, One ID = Grade and One ID= type of work.
What I'm trying to do is when using these ID's and seperate the value related to those ID's into 3 seperate columns in a query for using in Reporting Services to create the report
As you can see, I'm attempting to change the name of the same column 3 times to reflect the correct information and then link them all to the person, where one person might have several entries in the other fields.
As you can see I can change the names individually in queries and pull the information seperately, it's when roll them altogether is where I'm running into my problem
Thanks for the suggestions that were made, I apoligize for not making the problem clearer.
Here is a copy of what I'm attempting to accomplish. I didn't have it with me last night when posting.
--Pulls the Service Opportunity
SELECT cs.value AS "Service Opportunity"
FROM Cstudent cs
INNER JOIN cattribute ca ON ca.attributeid = cs.attributeid
WHERE ca.name = 'Service Opportunity'
--Pulls the Number of Hours
SELECT cs.value AS 'Number of Hours'
FROM Cstudent cs
INNER JOIN cattribute ca ON ca.attributeid =cs.attributeid
WHERE ca.name ='Num of Hours'
--Pulls the Person Grade Level
SELECT cs.value AS 'Grade'
FROM Cstudent cs
INNER JOIN cattribute ca ON ca.attributeid =cs.attributeid
WHERE ca.name ='Grade'
--Pulls the Person Number, First and Last Name and Grade Level
SELECT s.personnumber, s.lastname, s.firstname, cs.value as "Grade"
FROM student s
INNER JOIN cperson cs ON cs.personid = s.personid
INNER JOIN cattribute ca ON ca.attributeid = cs.attributeid
WHERE cs.value =(SELECT cs.value AS 'Grade'
WHERE ca.attributeid = cs.attributeid AND ca.name='Grade')
I have a requirement where in i have around 15 different flat files , filenames are fixed but folder path can be changed(i think i should use a variable for folder path). These 15 files data should go to their respective tables in the database.
Whether I need to create separate data flow task for each file or separate package? In addition to these, example : while importing product data into product table, if product ID already exists, we need to ignore it and upload only the new records.
I have an excel file that has multiple sheets and I need to import data from each separate sheet to a separate table using SSIS.
E.g. Sheet A data should go to Table A and Sheet B data should go to Table B and so on. Is it possible to do this with out using script task.
I have just taken over the job of sorting out a rather poorly designed database. It looks like it was 'upsized' from an access database to the SQL server. The SQL server is the 2000 version.
Now I am trying to generate a report of what the students in the database are owing by referencing the Receipt table and then all the available payment methods and allocations. I was wondering if there was anyway to work out data being displayed twice (Let me demonstrate)
Note1: All the tables are linked by a key of ReceiptNo. From what I can see there is a table for every payment type and allocation but no link between the two other then the receipt number.
Using the query:
SELECT T_Receipt.ReceiptNo, T_cheque.Amount AS Chq_Amount, T_credit.Amount AS Cre_Amount, StandingOrder.Amount AS Stn_Amount,
T_BankTransfer.amount AS Bnk_Amount, T_cash.TotalAmount AS Cas_Amount, T_RentPayment.AmountPayed AS Ren_Paid,
T_AdminPayment.AmountPaid AS Adm_Paid, T_InternetBilling.Total AS Int_Paid, T_Utilities.AmountPaid AS Util_Amount,
T_InvoicePayment.amountPaid AS Inv_Paid, T_OtherPayments.paymentAmount AS Oth_Paid, T_parkingBill.paymentAmount AS Prk_Paid,
T_TelephoneBills.TelephoneCredit AS Tel_Paid, T_DepositPayment.[Deposit payment] AS Dep_Amount, T_Receipt.cancelled AS Canceled,
T_Receipt.RemittanceReceiptNo AS Rec_Ref, T_Receipt.Student
FROM T_Receipt INNER JOIN
T_DepositPayment ON T_Receipt.ReceiptNo = T_DepositPayment.receiptNo LEFT OUTER JOIN
T_RentPayment ON T_Receipt.ReceiptNo = T_RentPayment.RentPaymentNo LEFT OUTER JOIN
StandingOrder ON T_Receipt.ReceiptNo = StandingOrder.ReceiptNo LEFT OUTER JOIN
T_TelephoneBills ON T_Receipt.ReceiptNo = T_TelephoneBills.ReceiptNo LEFT OUTER JOIN
T_parkingBill ON T_Receipt.ReceiptNo = T_parkingBill.ReceiptNo LEFT OUTER JOIN
T_OtherPayments ON T_Receipt.ReceiptNo = T_OtherPayments.ReceiptNo LEFT OUTER JOIN
T_InvoicePayment ON T_Receipt.ReceiptNo = T_InvoicePayment.receiptNo LEFT OUTER JOIN
T_cash ON T_Receipt.ReceiptNo = T_cash.ReceiptNo LEFT OUTER JOIN
T_AdminPayment ON T_Receipt.ReceiptNo = T_AdminPayment.ReceiptNo LEFT OUTER JOIN
T_BankTransfer ON T_Receipt.ReceiptNo = T_BankTransfer.receiptNo LEFT OUTER JOIN
T_Utilities ON T_Receipt.ReceiptNo = T_Utilities.receiptNo LEFT OUTER JOIN
T_credit ON T_Receipt.ReceiptNo = T_credit.ReceiptNo LEFT OUTER JOIN
T_cheque ON T_Receipt.ReceiptNo = T_cheque.ReceiptNo LEFT OUTER JOIN
T_InternetBilling ON T_Receipt.ReceiptNo = T_InternetBilling.ReceiptNo
GROUP BY T_Receipt.Student, T_Receipt.ReceiptNo, T_cheque.Amount, T_credit.Amount, StandingOrder.Amount, T_BankTransfer.amount, T_cash.TotalAmount,
T_AdminPayment.AmountPaid, T_InternetBilling.Total, T_Utilities.AmountPaid, T_InvoicePayment.amountPaid, T_OtherPayments.paymentAmount,
T_parkingBill.paymentAmount, T_TelephoneBills.TelephoneCredit, T_Receipt.cancelled, T_Receipt.RemittanceReceiptNo,
T_DepositPayment.[Deposit payment], T_RentPayment.AmountPayed, T_Receipt.Student
HAVING (T_Receipt.Student LIKE N'06%')
Which gives a result of:
RecNo.
30429
Cheque
250
Deposit
250
30429
679.98
250
This is fine but when I do analysis on this it appears as though the student has paid two deposit payments. I was wondering with out querying each table independently from an application if there was a criteria to specify that I only get one deposit result.
So as such say, give me all the payments but I only want one result from the other tables. I though about discrete but that wouldn't work here.
Hi!
I have 7 source databases and one target database, all using the same structure. The structure is made of 10 tables, with foreign key constraints.
I need to merge the source databases into the target (which won't have any data before that process, but will already have the correct schema), and to keep the relationships between the records.
I know how to iterate over the source databases (with SMO foreach), but I'd like to know if someone can advise the best copy method for that context in SSIS ? (I don't want to keep the primary keys, but I need to keep the relationships...)
Any pointer most welcome!
best regards and thanks
Thibaut
I have a couple of hundred flat files to import into database tables using SSIS.
The files can be divided into groups by the format they use. I understand that I could import each group of files that have a common format at the same time using a Foreach Loop Container.
However, the example for the Foreach Loop Container has multiple files all being imported into the same database table. In my case, each file needs to be imported into a different database table.
Is it possible to import each set of files with the same format into different tables in a simple loop? I can't see a way to make a Data Flow Destination item accept its table name dynamically, which seems to prevent me doing this.
I suppose I could make a different Data Flow Destination item for each file, in the Data Flow. Would that be a reasonable solution, or is there a simpler solution, or should I just resign myself to making a separate Data Flow for every single file?
Hello
I am building a survey application.
I have 8 questions.
Textbox - Call reference
Dropdownmenu - choose Support method
Radio button lists - Customer satisfaction questions 1-5
Multiline textbox - other comments.
I want to insert textbox, dropdown menu into a db table, then insert each question score into a score column with each question having an ID.
I envisage to do this I will need an insert query for the textbox and dropdownlist and then an insert for each question based on ID and score.
Please help me!
Thanks
Andrew
How to insert single row/multiple rows into multiple tables by using single insert statement.
View 1 Replies View Related