Well I seem to be a gomer when trying to understand XML. Could some one please help me? I am trying to write a script or DTS package that will read 4 XML files (Do not call list) compare each phone number to the numbers I have in my DB and if there is a match put the contactID in to another table. Or at the very least get the XML phone numbers into the database. This is what the XML doc looks like
I am producing a php report using SQL queries to show the SLA status of our calls. Each call has response, fix & completion targets. If any of these targets are breached, the whole SLA status is set as 'Breach'.
The results table should look like the one below:
CallRef.
Description
Severity
ProblemRef
Logged Date
Call Status
SLA Status
C0001
Approval for PO€™s not received
2
DGE0014
05-01-06 14:48
Resolved
Breach
C0002
PO€™s not published
2
DGE0014
06-01-06 10:21
Resolved
OK
C0003
Approval for PO€™s not received from Siebel.
2
n/a
05-01-06 14:48
Investigating
OK
Whereas I can pick the results for the first 6 columns from my Select query, the 'SLA Status' column requires the following calculation:
The problem is that my query is returning multiple entries for each stage of the call (see below), whereas I just want one entry for each call, with SLA status 'Breach' if any of the stages for the call were out of SLA.
CallRef.
Description
Severity
ProblemRef
Logged Date
Call Status
SLA Status
C0001
Approval for PO€™s not received
2
DGE0014
05-01-06 14:48
Resolved
Breach
C0001
Approval for PO€™s not received
2
DGE0014
05-01-06 14:48
Resolved
OK
C0001
Approval for PO€™s not received
2
DGE0014
05-01-06 14:48
Resolved
Breach
Any help will be much much appreciated, this issue has been bothering me for some time now!!!
A view named "Viw_Labour_Cost_By_Service_Order_No" has been created and can be run successfully on the server. I want to import the data which draws from the view to a table using SQL Server Import and Export Wizard. However, when I run the wizard on the server, it gives me the following error message and stop on the step Setting Source Connection
Operation stopped...
- Initializing Data Flow Task (Success)
- Initializing Connections (Success)
- Setting SQL Command (Success) - Setting Source Connection (Error) Messages Error 0xc020801c: Source - Viw_Labour_Cost_By_Service_Order_No [1]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "SourceConnectionOLEDB" failed with error code 0xC0014019. There may be error messages posted before this with more information on why the AcquireConnection method call failed. (SQL Server Import and Export Wizard)
Exception from HRESULT: 0xC020801C (Microsoft.SqlServer.DTSPipelineWrap)
- Setting Destination Connection (Stopped)
- Validating (Stopped)
- Prepare for Execute (Stopped)
- Pre-execute (Stopped)
- Executing (Stopped)
- Copying to [NAV_CSG].[dbo].[Report_Labour_Cost_By_Service_Order_No] (Stopped)
- Post-execute (Stopped)
Does anyone encounter this problem before and know what is happening?
I am trying to import an xlsx spreadsheet into a sql 2008 r2 database using the SSMS Import Wizard. When pointed to the spreadsheet ("choose a data source")  the Import Wizard returns this error:
"The operation could not be completed" The Microsoft ACE.OLEDB.12.0 provider is not registered on the local machine (System.Data)
How can I address that issue? (e.g. Where is this provider and how do I install it?)
I am attempting to import data from Microsoft Access databases to SQL Server 2000 using the DTS Import/Export Wizard. I have a few errors.
Error at Destination for Row number 1. Errors encountered so far in this task: 1. Insert error column 152 ('ViewMentalTime', DBTYPE_DBTIMESTAMP), status 6: Data overflow. Insert error column 150 ('VRptTime', DBTYPE_DBTIMESTAMP), status 6: Data overflow. Insert error column 147 ('ViewAppTime', DBTYPE_DBTIMESTAMP), status 6: Data overflow. Insert error column 144 ('VPreTime', DBTYPE_DBTIMESTAMP), status 6: Data overflow. Insert error column 15 ('Time', DBTYPE_DBTIMESTAMP), status 6: Data overflow. Invalid character value for cast specification. Invalid character value for cast specification. Invalid character value for cast specification. Invalid character value for cast specification. Invalid character value for cast specification.
Could you please look into this and guide me Thanks in advance venkatesh imtesh@gmail.com
I am trying to simplify a query given to me by one of my collegues written using the query designer of Access. Looking at the query there seem to be some syntax differences, so to see if this was the case I thought I would import the database to my SQL Server Developer edition.
I tried to start the wizard from within SQL Server Management Studio Express as shown in one of the articles on MSDN which did not work, but the manual method also suggested did work.
Trouble is that it gets most of the way through the import until it spews forth the following error messages:
- Prepare for Execute (Error) Messages Error 0xc0202009: {332B4EB1-AF51-4FFF-A3C9-3AEE594FCB11}: An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80004005 Description: "Could not start session. Too many sessions already active.". (SQL Server Import and Export Wizard)
Error 0xc020801c: Data Flow Task: The AcquireConnection method call to the connection manager "SourceConnectionOLEDB" failed with error code 0xC0202009. (SQL Server Import and Export Wizard)
Error 0xc004701a: Data Flow Task: component "Source 33 - ATable" (2065) failed the pre-execute phase and returned error code 0xC020801C. (SQL Server Import and Export Wizard).
There does not seem to be any method of specifying a number of sessions, so I don't see how to get round the problem.
Does anyone know how I can get the import to work?
I am not sure how to implement the following, but I believe it entails using DTS, and hopefully it is fine that I post it here b/c ultimately I will need this backend data for my frontend .aspx pages:
On a weekly basis, I need to IMPORT some data located on a remote Oracle DB into SQL Server 2k. Since there is so much data to transfer, I would only like to transfer the data that is new to the table since the last IMPORT, i.e. a week ago and leave behin the OLD data.
Is DTS the correct way to go or do I have more control via DTS with STORED PROCEDURES? Does anyone have any good references for me?
On a similar note, once this Oracle data is IMPORTED into a certain table, I would like to EXPORT some of these NEWLY acquired rows matching certain criteria into another table for auditing purposes. For this scenario, should I implement a TRIGGER UPDATE event here on the first table?
when trying to Ãmport files to our database server from a client, I keep getting an error:
- Validating (Error) Messages Error 0xc00470fe: Data Flow Task: The product level is insufficient for component "Source_txt" (1). (SQL Server Import and Export Wizard)
Error 0xc00470fe: Data Flow Task: The product level is insufficient for component "Data Conversion 1" (175). (SQL Server Import and Export Wizard)
... doing the same import when logged on the server, hasn't been giving me any errors, how come. I can from my client without trouble import tables from other DB servers but when ever it is files it won't do it.
I tried as mentioned in other threads rerun setup to re-install SSIS, but as it was already installed it wouldn't re-install. My next move would be to make a clean install, but not sure it would help, as I think this is a buck.
Hi,I am having trouble importing data from an excel spreadsheet into MSSQL Server 2000 using DTS Wizard. The DTS import process issuccessfull, no errors, but only 50 rows of approx. 1500 rows of dataare imported. I tried to remove 20 rows in the excel spreadsheet inthe interval row 0-50. When i later ran the import, only 30 rows wereimported. I deleted almost every row in the interval 0-50, with theresult of the import having 0 rows imported (but job ransuccessfully). I decided to delete rows 0-100 in the spreadsheet inorder to see if the resolved the problem, but it didn't. As Isuspected something in the excel file to be the cause, I exported theexcel spreadsheeet to a tab delimited textfile, with only one row. ADTS import resulted in importing approx 100 rows, double the amount ofthe textfile, but the other 1400 rows were not imported. The data inthe column is containing numeric values only.Please help me! What could possibly be the cause of DTS skipping rowslike that. DTS doesn't feel reliable at all :/Regards,Björn
I've written a T-SQL stored procedure that I want to call other stored procedures. The opening part of it looks like this:set ANSI_NULLS ONset QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[spArchive] @dtArchiveBefore DateTimeASEXEC spArchiveAHD @dtArchiveBeforeGOEXEC spArchiveContract_History @dtArchiveBeforeGOEXEC spArchiveContracts @dtArchiveBeforeGO But I'm getting this error repeatedly: Msg 137, Level 15, State 2, Line 2 Must declare the scalar variable "@dtArchiveBefore".I've searched for this term but can't figure out what's wrong with my code.Any ideas?Robert W.
Do you know when should I use extend sp to call a dll file, and when should I use sp_OACreate to call dll. It seems only dll created by C++ can be called from extend sp, how about VB created dll?
MS SQL 2005 I developed several SP that update tables If I execute them one by one in the SQL Sever Management Studio, they work ok
Now, I want to execute them all inside another SP I wrote it (attached code) Execute it and give me a message that runs successfully but that is not true May anyone of You tell me the right syntax to do it?
[Code] USE REPORTES GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATEPROCEDURE dbo.Astral_sp_Acumula AS -- EXECUTE [REPORTES].[dbo].[Astral_sp_AcCpasArtC] GO EXECUTE [REPORTES].[dbo].[Astral_sp_AcDevArtC] GO EXECUTE [REPORTES].[dbo].[Astral_sp_AcVtasArtV] GO -- More sp executions [Code]
Hi all, I have 2 tables like that CREATE TABLE tab1 ( id int IDENTITY (1, 1) NOT NULL , master varchar (50) NULL ) ON PRIMARY GO
CREATE TABLE tab2 ( id int IDENTITY (1, 1) NOT NULL , student_name varchar (50) NULL , master_id int NULL )
Now i want to create a Sp which is
create proc inner_sp(@s1 varchar(50)) as insert into tab1(master) values (@s1)
and i want to call it from another sp
create proc outer_sp2(@s2 varchar(50),@s1 varchar(50)) as declare @r int exec inner_sp @s1 set @r=Scope_Identity() insert into tab2(student_name,master_id) values(@s2,@r)
exec outer_sp2 'Vivek1','Test'
But in table tab2 master_id column contain NULL value.... Please fix it... Ujjwal
is it possible to call a SQL job from an ASP.NET application? if so, how? I'm using VS.net2003 and am programming in C#I've been searching for a while, but haven't found anything. It doesn't help that one of my keywords when searching is "job(s)."
I have two databases with two identical tables in seperate physical locations. I want database B tables to be updated automatically when database A tables change. Is there a way to call a web service from SQL to make this happen? Or is there a better way to do this? I would really like it to get the rows that were modified and then copy only those rows to the other database tables. If anyone knows if this can be done please let me know. Thank you.
can someone point me in the right direction? I know how to call a stored procedure, but I can't seem to find code examples on how to call user defined functions.
Is it possible for me to call a DTS package from within a stored procedure? If it is not possible to do so directly, would I at least be able to call the package through a job?
Hi, I need to call a stored procdure from a SQL statement that I am running from query analyzer.
My SQL statement will select from a master table of bills. I have a stored procedure that calculates the amount due on each bill. The sp does not use the master table in the calculation.
How do I form the SQL statement to call the sp, which needs one of the master table columns as a parm? i.e. SELECT *, EXEC sp_abc columns name AS whatever FROM tblMaster WHERE...... TIA! Dave
Hi, sorry I am kind of stuck how to do this correctly. The following is my problem statement:
There are three tables: TAB1 TAB2 TAB3
There is a 1 to many relationship between TAB2 and TAB1.
For every row in TAB2 that has a match in TAB1, they are related via TAB2.col3 = TAB1.col2
I want to delete all those rows in both tables that match, but I have to delete those rows in TAB1 first because of dependency constraints. The primary key for TAB2.col1
Therefore I need to have a select block first and store it somewhere(?)
select TAB2.col1 from TAB2 where TAB2.col3 IN (select TAB1.col2 from TAB1 where TAB1.col1 IN (select TAB3.col1 from TAB3))
Then I delete all those rows in TAB1 that has a match in TAB3
delete from TAB1 where TAB1.col1 IN (select TAB3.col1 from TAB3))
now i delete those rows in TAB2 from the result set of the first sql block.
/****** Object: Stored Procedure dbo.ostat_CDS_rpt47_2006 Script Date: 4/30/2008 2:25:41 PM ******/ if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ostat_CDS_rpt47_2006]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[ostat_CDS_rpt47_2006] GO
set @tb1= 'margin..ncds0421' set @tb2='Report.dbo.ob_apr08'
set @sql= ' insert into '+@tb2+' select yyyy,mm,dd,HH,sitecode,TrunkOut,SwitchCode,prefixCode,0 isCallback, cast(callcost as decimal (10,5)) cost,count(*) attempt, sum(case when connectflg=1 then 1 else 0 end) connected,sum(talktime) Talktime from '+@tb1+' where yyyy='+ltrim(str(@pYear))+' and mm='+ltrim(Str(@pMonth)) +' and dd between '+ltrim(Str(@pDay1))+' and '+ltrim(Str(@pDay2))+' and talktime >0 and trunkin is null group by yyyy,mm,dd,HH,sitecode,TrunkOut,SwitchCode,prefixCode,cast(callcost as decimal (10,5)) order by yyyy,mm,dd,HH,sitecode,TrunkOut,SwitchCode,prefixCode,cast(callcost as decimal (10,5)) ' --print (@sql) exec (@sql)
Question: what do we call the table that does the n to n Relation
Hi, When we have Table1 and Table2, then we link both tables using a third table Table3 that relates n records in Table1 to n records in table2, how do we call Table3? There is a name in dataBase modeling for that, right?
But when I create a SQL XML Webservice via an endpoint all I can find on the net is how to show the wsdl.
http://localhost/Contacts?wsdl
Ive created a method in the "Contacts" webservice called GetContacts. Why cant I construct a similar url to execute that method without having to write a vb.net wrapper for it?
Does anybody know if it's possible to call an object (specifically, a textbox) from the expression of another object? I would imagine it would something like 'objectname.Value' or 'objectname.ToString', but I can't get it to recognise my object names.
I have two textboxes - Last Event and Number of Weeks Since Last Event. I have an expression to calculate the date of the last event (which is rather lengthy), and currently I get the number of weeks by doing a DateDiff between Today and another copy of the lengthy LastEvent code. This works, but it would be much simpler and more efficient if I could just say:
I would like to use SP as Subquery in my query. SP returns decimal value..
Is it possible, if not do you have an idea ?
Code Blockselect id, (exec css_satis_puan2 @ay1='10.2007',@ay2='11.2007',@ay3='12.2007',@tip2=1,@servis=ss.id) as result1, (exec css_satis_puan2 @ay1='10.2007',@ay2='11.2007',@ay3='12.2007',@tip2=2,@servis=ss.id) as result2 from users as ss where uid between 1 and 200
