Hi everyone,
Is it possible to perform a SELECT/INSERT statement with two different connection types? I want to do the "SELECT" statement with data from SQL Server and "INSERT" it into an Access database all in one query.
Sanctos
Greetings,
I have an SSIS package that I need to modify. It was developed in Visual Studio 2005 and runs faithfully in production on SQL Server 2005. Suddenly, I can't open the package on my own workstation. I see a big red "X" image and a message stating "Microsoft Visual Studio is unable to load this document." The real reason the document can't be loaded appears to be:
Error loading DataWarehouseLoader.dtsx: The connection type "OLEDB" specified for connection manager "Warehouse Logging OLE DB" is not recognized as a valid connection manager type. This error is returned when an attempt is made to create a connection manager for an unknown connection type. Check the spelling in the connection type name.
Does anyone know what causes this and what I can do? The package has been in production for 9 months and I've never seen this problem before.
Thanks,
BCB
Using SQL Server 2005 Express:
I'd like to know how to do a SELECT Query using the following tables:
Miles Table:
Date/Car/Miles/MilesTypeID
===============
(some date)/Ford/20/1
(some date)Ford/20/2
(some date)Chevy/30/1
(some date)Toyota/50/3
(some date)Ford/30/3
Miles Type Table
MilesTypeID/MilesType
=================
1/City
2/Highway
3/Off-Road
I'd like the results to be like this:
Date/Car/City Miles/Highway Miles/Off-Road Miles
=====================================
(date)-Ford-20-0-0
(date)-Chevy-0-20-0
(date)-Ford-20-0-0
(date)-Toyota-0-0-50
(date)-Ford-0-0-30
Anyone? Thanks in advance!
Hi there,
I have a MS Access database (mdb) containing the following tables:
Crime
Criminal
CrimeCommitted
Hideout
CriminalType
The Criminal table contains information about each criminal and the CrimeCommitted table contains information about the specific crimes. I've written the following query to return only the latest crime committed by each criminal:
Code Snippet
SELECT Criminal.CriminalID, Criminal.Firstname, Criminal.Lastname, Criminal.Nickname, Criminal.Gender, Criminal.DOB, Criminal.Eyes, Criminal.Complexion, Criminal.Weight, Criminal.Height, Criminal.Build, Criminal.Scars, Criminal.Occupation, Criminal.CrimeOrgID, Criminal.IQ, Criminal.Hideout, Criminal.CriminalType, Max(CrimeComitted.Date) AS Last_Crime_Comitted
FROM Criminal INNER JOIN CrimeComitted ON Criminal.CriminalID=CrimeComitted.CriminalID
GROUP BY Criminal.CriminalID, Criminal.Firstname, Criminal.Lastname, Criminal.Nickname, Criminal.Gender, Criminal.DOB, Criminal.Eyes, Criminal.Complexion, Criminal.Weight, Criminal.Height, Criminal.Build, Criminal.Scars, Criminal.Occupation, Criminal.CrimeOrgID, Criminal.IQ, Criminal.Hideout, Criminal.CriminalType;
This query works fine for obtaining the Criminal table data, but once i've include CrimeCommitted.Country in the SELECT statement, the data returned contained all the crimes committed by each criminal (i just need the latest crime).
The query doesn't work when another table, other than Criminal, is selected. How can i obtain the columns in the CrimeCommitted table in this query?
I'm trying to use the SSIS Execute SQL Task to pull XML from a SQL 2005 database table. The SQL is of the following form:
SELECT
(
SELECT
MT.MessageId 'MessageId',
MT.MessageType 'MessageType',
FROM MessageTable MT
ORDER BY MT.messageid desc
FOR XML PATH('MessageStatus'), TYPE
)
FOR XML PATH('Report'), TYPE
For some reason I can only get this query to work if I use an ADO.NET connection type. If I try to use something like the OLEDB connection I get the following error:
<ROOT><?MSSQLError HResult="0x80004005" Source="Microsoft XML Extensions to SQL Server" Description="No description provided"?></ROOT>
Can anyone tell me why the SELECT ... FOR XML PATH... seems only to work with ADO.NET connections?
Thanks
Walter
Hi all,
I know that the File connection can be used in the File system task and Flatfile connection can be used in Flat File source or destination, but I don't know which Task or Source/Destination can use the Multiple File connection and Multiple Flatfile connection. Thanks
Happy Friday!
A while since I have posted a question, and this one is probably real easy.
I am trying to store numeric values from a php form in MSSQL 2000 database. However, the columns are set to float and if the value is 1.00, when entered into the table it is saved as 1
If I change the column type to money, the query fails, with an error message of conversion of datatype varchar to datatype money statement terminated.
anybody know what I need to do? do I need to do something in my query to specify that this is NOT varchar data?
I have a column that has 50 records and they are INT. I am trying to place them into a single parameter using the CHARINDEX using the IF Statement. I got an error saying convertinh from varchar to int type data.
Basically, this is what I did:
@RoomID varchar(max)
Then I declare it:
DECLARE @RoomId varchar(4)
SET @RoomID = 0
The IF part that I need to express this CHARINDEX function.
Then in Where clause I filtered it as:
WHERE r.roomid = @RoomId
Or what other way to handle INT data type and make them available for user to choose multiple value?
how do you alter multiple data type in a table?
in oracle we have "modify"
is there something similar in sql server?
I'm trying to create a report where multiple currencies are rolled up into seperate subtotals. The currency type and amount are in seperate columns..
I'd like for my report to look like this
Account currency_type ammount
I'd like for the report to look like this
001 USD 100
001 USD 150
001 EUR 100
001 EUR 100
001 AUD 100
Total USD 250
EUR 200
AUD 100
Is there a way to do this?
Hi, I am trying to connect to a shared data source file: DW.RDS
But i can't get the correct connection type going, Data Source Type & Provider.
Can Anyone Assist Please!
Regards,
I am having another problem/error/nightmare/PITA with SSIS
In FTP task: Set local file to flat file connection (that SSIS created during a dts migration)
and it comes back with the following wonderful error:
It IS a file! Anyone know why this happens?
===================================
Package Validation Error (Package Validation Error)
===================================
Error at FTP Task [FTP Task]: Type of Connection "DestinationFile" is Not a file.
Error at FTP Task: There were errors during task validation.
(Microsoft.DataTransformationServices.VsIntegration)
------------------------------
Program Location:
at Microsoft.DataTransformationServices.Project.DataTransformationsPackageDebugger.ValidateAndRunDebugger(Int32 flags, DataWarehouseProjectManager manager, IOutputWindow outputWindow, DataTransformationsProjectConfigurationOptions options)
at Microsoft.DataTransformationServices.Project.DtsPackagesFolderProjectFeature.ExecuteTaskOrPackage(ProjectItem prjItem, String taskPath)
Hi,
Very basic beginner XML question. Is it possible to have an attribute name repeated in a schema ? I received an XML and XSD file for which I'm getting the error "multiple base for derived type on dte_effective is not supported." "dte_effective" is repeated under several elements. Is there a way to resolve this error by adding something to the schema to indicate that the date is specific to each element ? Can't figure this one out. I've googled the error and found nothing to resolve this.
XSD file sample is below...
Thanks, J.
<xs:element name="Element1">
<xs:complexType>
<xs:attribute name="dte_effective" "NewDate" use="optional">
<xs:annotation>
<xs:documentation>effective date field</xs:documentation>
</xs:annotation>
</xs:attribute>
<xs:element name="Element2">
<xs:complexType>
<xs:attribute name="dte_effective" type="NewDate" use="optional">
<xs:annotation>
<xs:documentation>effective date field</xs:documentation>
</xs:annotation>
</xs:attribute>
I 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 have a table with single row like below
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
Column0 | Column1 | Column2 | Column3 | Column4|
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Value0 | Value1 | Value2 | Value3 | Value4 |
Am looking for a query to convert above table data to multiple rows having column name and its value in each row as shown below
_ _ _ _ _ _ _ _
Column0 | Value0
_ _ _ _ _ _ _ _
Column1 | Value1
_ _ _ _ _ _ _ _
Column2 | Value2
_ _ _ _ _ _ _ _
Column3 | Value3
_ _ _ _ _ _ _ _
Column4 | Value4
_ _ _ _ _ _ _ _
I have a task that uses an Excel connection manager to read in an Excel file. In the file there are 6 date columns. The date columns can have either a date or the letters NA. If I look at the External Column properties from the advanced tab, 4 of the date columns have a datatype of DT_DATE. The other 2 date columns have a datatype of DT_WSTR. The 4 columns with DT_DATE, my data looks okay as when it is read by the connection manager. The issue is that the 2 columns that are DT_WSTR, all dates are turned into NULLS, while the NA's are just fine. I have tried to change the datatypes in the advanced tab to DT_DATE, but it won't work.
I don't have any idea what to do to get this working correctly. Can anyone PLEASE provide some insight and suggestions or a solution?
How investigate type of a connection to DB( MS SQL or Oracle) during execution of a packet?
In the time of executon of packet I must determine type of connection? What do good practices exists?
Thanks In Advance.
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.
Hello,
I am wondering how it is going to work using SQL Server configuration type to store connection string in the SQL Server table. How does SSIS know what database to connect to if its connection string is store in the database? Thanks
I am making a rss script to deploy the reports and datasources.
View 2 Replies View RelatedHi Everyone,
I haven't been able to successfully use the ADO.NET connection type to use both input and output parameters in an execute sql task containing just tsql statements (no stored procedure calls). I have successfully used input parameters on their own but when i combine it with output parameters it fails on the simplest of tasks.
I would really find it beneficial if you could use the flexibility of an ADO.NET connection type as the parameter marker and parameter name can be referenced anywhere throughout the sql statement in no particular order. The addition of an output parameter would really make it great!!
Thanks
Hi all
Pls is there a way to disable users to open mutiple session of an access project file.
Assuming that there is may be a specific database properties to set for above issue
rgds
I am using Sql 2005 in our different offices at different locations. I need to connect to all my office's database in my main office server where i have installed Sql Studio express. I have VPN connection to all the locations.
If multiple locations are connected to my server (VPN) , i could not add any server(location) to the sql studio express (but i can browse that systems and access the shared folders).
if i have only one location connected thru VPN then i can connect thru Sql Studio to that locations Sql server.
Pls also note that the IP address of the locations may changes frequently as i used Dynamic IP conversion for VPN.
Howdy folks. I have a SELECT question for you. Or maybe a WHERE question. That I am not sure is part of the problem. My application is ASP.NET 2.0, and I’d like to avoid getting into stored procedures right now, if I could.
I am trying to summarize the order status for each customer in the database (SQL Server 2005, by the way). I want to provide two counts: the number of open jobs per customer, and the number of rush jobs per customer. Something like this:
CustID CustName JobOpen JobRush
----------------------------------------------------------------
22 John Deere 47 3
24 MCP 32 7
37 BlueON 6 0
In my noobness, I developed this:
SELECT CustID, MAX(CustName) AS CustName,
COUNT (*) AS JobOpen
FROM Customers
INNER JOIN Jobs ON JobCustID = CustID
WHERE (JobDone = 0)
GROUP BY CustID
As you can see, it finds from the Jobs table all jobs that are not done (JobDone is a T/F field), joins the Jobs and Customers tables, groups by CustID, and counts the totals in each CustID group. It works great in outputting the first three columns that I am looking for.
But I cannot come up with a simple way to add JobRush (also a T/F field), because it needs a different WHERE clause than the one JobOpen uses. It would need
WHERE (JobRush = 1)
So maybe my question should be: how do I use multiple WHERE clauses, each with its own COUNT?
I did mess around with Common Table Expressions, and managed to build two CTEs (one with JobOpen results and the other with JobRush results) that I joined together. It worked in Studio Manager, but my ASP.NET page didn’t like it. I guess that means I could learn stored procedures, but wow I’d love to just have a nice complete SELECT command for my page.
Thanks for reading all this. Any input is greatly appreciated.
Matt
I'm working on a DB that will be used in separate physical locations. These DBs will be identical in structure and completely independent of each other. Whilst some tables will retain similar or identical information, there is a large amount of content that will diverge.However, there is a central location that will need to manage these DBs. There will be a requirement from this central location to run reports, often comparing data between them, and to push data into each child location. There is no requirement for the child locations to 'share' information and indeed the volume concerned and the intermittent/low-bandwidth nature of their connections would make this undesirable. If an individual selection of data does need to be shared between I'm happy for it to be uploaded centrally, a decision made, and manually pushed to each child-location.
Which brings me to my real question. Should I try and include composite Primary Keys in many of the tables (to include probably an incrementing number and another field of site ID) so that when I absorb them all back centrally I can put them into one large DB. Or should I keep them as separate DBs in the central location and use some fairly 'normal' mechanisms for mirroring to sites; accepting that this will make running comparisons between them more difficult and potentially more difficult to write interactivity for centrally?
The best 'real world' comparison I can think of is transactions in retail shops. How do these transaction logs get stored centrally when they're all generating individual transaction numbers that are only unique in that location?
SQL Server 2012 Performance Dashboard Main advices me this:
Since the application is from a vendor and I have no control over its code, how can improve this sitation?
When viewing an estimated query plan for a stored procedure with multiple query statements, two things stand out to me and I wanted to get confirmation if I'm correct.
1. Under <ParameterList><ColumnReference... does the xml attribute "ParameterCompiledValue" represent the value used when the query plan was generated?
<ParameterList>
<ColumnReference Column="@Measure" ParameterCompiledValue="'all'" />
</ParameterList>
</QueryPlan>
</StmtSimple>
2. Does each query statement that makes up the execution plan for the stored procedure have it's own execution plan? And meaning the stored procedure is made up of multiple query plans that could have been generated at a different time to another part of that stored procedure?
I have two tables .. in one (containing user data, lets call it u).The important fields are:u.userName, u.userID (uniqueidentifier) and u.workgroupID (uniqueidentifier)The second table (w) has fieldsw.delegateID (uniqueidentifier), w.workgroupID (uniqueidentifier) The SP takes the delegateID and I want to gather all the people from table u where any of the workgroupID's for that delegate match in w. one delegateID may be tied to multiple workgroupID's. I know I can create a temporary table (@wgs) and do a: INSERT INTO @wgs SELECT workgroupID from w WHERE delegateID = @delegateIDthat creates a result set with all the workgroupID's .. this may be one, none or multipleI then want to get all u.userName, u.userID FROM u WHERE u.workgroupIDThis query works on an individual workgroupID (using another temp table, @users to aggregate the results was my thought, so that's included) INSERT INTO @users SELECT u.userName,u.userID FROM tableU u LEFT JOIN tableW w ON w.workgroupID = u.workgroupID WHERE u.workgroupID = @workGroupIDI'm trying to avoid looping or using a CURSOR for the performance hit (had to kick the development server after one of the cursor attempts yesterday)Essentially what I'm after is: SELECT u.userName,u.userID
FROM tableU u
LEFT JOIN tableW w ON w.workgroupID = u.workgroupID
WHERE u.workgroupID = (SELECT workgroupID from w WHERE delegateID = @delegateID) ... but that syntax does not work and I haven't found another work around yet.TIA!
I need to set multiple values for some SQL statements, for exampleSET NUMERIC_ROUNDABORT OFFGOSETANSI_PADDING,ANSI_WARNINGS,CONCAT_NULL_YIELDS_NULL ,ARITHABORT,QUOTED_IDENTIFIER,ANSI_NULLS ONGOin a .sql file, but would like to reset them to their previous settingsagain for other SQL statements. I didn't find anything in books online. Ithought just calling "set" was enough, but apparently not ....Thanks.
View 3 Replies View RelatedHey guys,
I am pretty new to reporting software and I was just wondering in reporting services 2005 when working with multiple datasets and data connection string in order to populate text boxes if the data is a number it is prefixed with SUM at the start of the expression and if it is text it is prefixed with FIRST even though it is just for one value.
I was wondering if this is normal or have I messed up somewhere?
Many thanks.
I have a simple db that I created in SQL Express 2005, and a small app that I build in Visual C# that connects to the db. The problem I've always had is that Visual C# and Management Studio Express cannot both connect to the db at the same time. One will throw an error if the other is connected. To switch, I have to disconnect the one that has the connection, restart the sql express service and connect with the other. I thought sql express could handle multiple connections? I'm running in User Instance=False mode, and my db properties include Restrict Access=Multi_User.
Does anyone have some insight?
By the way, if I'm not in Visual c# but run the release build of my app, the same problem occurs.
I get this error from Management Studio Express:
Unable to open the physical file "D:DBSHCSHC.mdf". Operating system error 32: "32(The process cannot access the file because it is being used by another process.)".
Thanks in advance.
I found a peculiar thing today while working with SQL Mobile in a multithreaded application (VS2005, application for Pocket PC 2003).
I created a class which has one SqlCeConnection object. Every time I call a function to insert/select/delete something from the local db, I open the connection, execute the query an then close the connection again.
But when I'm calling a function from the db class in thread 1 and in the meantime call a different function (from the same db class of course) in thread 2, things go wrong. Because when function 1 wants to close the connection, function 2 is still using the connection and it will crash my application with a native exception (0xC0000005: access violation).
I can see why the error is happening, but shouldn't there be a nice .NET handled exception instead of a native exception which grinds my app to a hold?
(A workaround I use now is to use multiple connection objects instead of one, but I thought I'd give this feedback anyway)
Scenario:
130 dtsx packages using 4 matching connections.
3 of those connections are stored in an SSIS Configuration table in an Operational database.
The last connection is in a shared data source and points to the Operational database so the packages can grab the others.
Problem:
It's time for deployment and all of those connections must change to production servers.
The 3 are no issue, just change the ConfiguredValue in the SSIS Configuration table on the production box to point to the other production servers.
However, the fourth one... I had made an assumption that when you changed a shared data source it filtered down throughout all the packages. We all know what assumptions do to you.... So. I need a way to change all 130 connections (and be able to change ALL packages quickly and simply for other projects in the future)
Solution:
It has been suggested that we use another package to run though all of the packages and change the connection with a script task. I can live with this (and more importantly so can our DBS's who have to deploy).
I have one snippet of code to ADD a connection using a variable holding the connection string, but we dont' want to add one, just change an existing one.
Has anyone else done this? Or had a similar problem and way to fix?
We are likely to have many projects in which the connections MUST change at deployment, and the idea of going into every package to make the change is sad at best... We would be more likely to move back to a competitor's product that has a connection repository, than continue with SSIS.
Ches Weldishofer
ETL Developer
Clear Channel Communications