Anyone know the syntax and function to create a self incrementing index whilst extracting data? I'm using the Derived Column tool. The extract will be a full extract everytime, not incremental.
Thanks.
Hi
I know this is a nasty way of doing but I can't think of another way. I'm retrieving an ID from a treeview when it's clicked client side, and I want to pass this to a SQL Procedure and retrieve some other data relating to it and fill a list box. Can this be done?
Thanks
Lbob
I have just started in a brand new role where I need to collect a large amount of diverse data. Part of this data is whether the steps in a guideline has been followed ( a simple check box or yes/no) (but there are maybe 100 guidelines)
My IT department have suggested we use SQL to create the database, but this will obviously take time. In the meantime my boss is keen that I get on with the data collection before the database is ready. I do not want to repeat steps when I create the SQL database so is there any format you would recommend I collect my data in now that can be easily applied to the SQL database - ie if I used Excel, would that be best, and should I then try to keep potential SQL table data separate?
I am trying to create a sample table in the Azure SQL Data warehouse but its giving me a syntax error Incorrect syntax near the keyword 'CLUSTERED'.
CREATE TABLE [dbo].[FactInternetSales]
( [ProductKey] int NOT NULL
, [OrderDateKey] int NOT NULL
, [CustomerKey] int NOT NULL
, [PromotionKey] int NOT NULL
[Code] ....
what's the correct syntax
I am trying to create a clustered index on a View of a table that has an xml datatype. This indexing ran for two days and still did not complete. I tried to leave it running while continuing to use the database, but the SELECT statements where executing too slowly and the DML statements where Timing out. I there a way to control the server/cpu resources used by an indexing process. How can I determine the completion percentage or the indexing process. How can I make indexing the view with the xml data type take less time?
The table definition is displayed below.
CREATE TABLE [dbo].[AuditLogDetails](
[ID] [int] IDENTITY(1,1) NOT NULL,
[RecordID] [int] NOT NULL,
[TableName] [varchar](64) NOT NULL,
[Modifications] [xml] NOT NULL,
CONSTRAINT [PK_AuditLogDetails] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
The view definition is displayed below.
ALTER VIEW [dbo].[vwAuditLogDetails] WITH SCHEMABINDING
AS
SELECT P.ID,D.RecordID, dbo.f_GetModification(D.Modifications,P.ID) AS Modifications
FROM dbo.AuditLogParent P
INNER JOIN dbo.AuditLogDetails AS D ON dbo.f_GetIfModificationExist(D.Modifications,P.ID)=1
The definition for UDF f_GetModification
ALTER function [dbo].[f_GetModification]( @Modifications xml,@PID uniqueidentifier )
returns xml
with schemabinding
as
begin
declare @pidstr varchar(100)
SET @pidstr = LOWER(CONVERT(varchar(100), @PID))
return @Modifications.query('/Modifications/modification[@ID eq sql:variable("@pidstr")]')
end
The definition for UDF f_GetIfModificationExist
ALTER function [dbo].[f_GetIfModificationExist]( @Modifications xml,@PID uniqueidentifier )
returns Bit
with schemabinding
as
begin
declare @pidstr varchar(100)
SET @pidstr = LOWER(CONVERT(varchar(100), @PID))
return @Modifications.exist('/Modifications/modification[@ID eq sql:variable("@pidstr")]')
end
The Statement to create the index is below.
CREATE UNIQUE CLUSTERED INDEX [IX_ID_RecordID] ON [dbo].[vwAuditLogDetails]
(
[ID] ASC,
[RecordID] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
hi all,
i have a device file which i know for sure contains all the data for a database (not the log). is there any way to recover the data from this file?
thanx.
:-)
hemant
I want to extract a particular portion of primary key field data.For example- suppose there are fifty data in PK field starting from 1...50.Now I want only to extract data from 25 to 50.It is bcos I want to retrieve only the new data that is inserted after a specified time.For example-today there is data starting from 1 to 25 which I have retrieved and saved elsewhere.By tomorrow there will be new data starting from 26 to 50 or more.Now I just want to extract this additional data from the database so that I dont need to extract all data again.Could anybody pls tell me how to do this.How can I do it using common SQL bcos I have to make a program for it.
View 1 Replies View RelatedCheers
I would really apprecite if anyone could make a comment on this...
what i wanna know is....
there are two tables, called 'A' and 'B' with identical structures...
is it possible to Perform the following in a single SQL ....
* If A->Field1 = B->Field1 then A->Field2 + b->Field2
* Records of table 'A' which not in table 'B' and
* Records of table 'B' which not in table 'A'
Thanks
This is my sample table Data
table1
TagID|TagName
--------------
1|xyz
2|123abc
3|a3bc
4|arsdew
5|xyc324
..............
..............
TagID--Primary Key
table2
TopicID|Topic
----------------
1|jkkj
2|kjgg
3|jhkj
4|refc
5|huhf
8|fyhj
9|jjk
...............
.............
..........
TopicID---Primary Key
table3
DetID|TagID|TopicID
-----------------------
1|1|1
2|1|2
3|1|3
4|2|4
5|2|5
8|3|8
9|3|9
....................
...................
.................
DetID---Primary key
TagID and TopicID----Foreign key
i need Topic based on TagName.
This xyz(TagName) have a 3 Topic. i need the 3 Topic
Once i select 123abc(TagName) that particular Topic I need..
any Query in Single Line.......................
Hi,
View 8 Replies View RelatedHi!
I'm analyzing the problem of extracting data from sql server 6.5 in order to load it into oracle.
I already know about the bcp tool, but I'd like to know if you would consider any other approach based on SSIS, or any other tool like SQL Load.
regards, and thanks in advance for your help.
I am new to SSIS and everything in it. I have been reading up on it but I am stumped. I need to do the following:
Automatically open a .csv file that will have a different name each day
Extract the data
Convert it to SQL format
Append it to it to an existing SQL table in my DB
Delete or move the .csv file to a different directory
Thanks for the guidance!
Matt
hi All,
I have a string varaible passed to the SP something like :
@var = 'v1#1@v2#1,2@v3#1,3,4,5'. Now i have to extract the data from this variable in such a way that :
select * from var_data shud return like this :
ID Role
v1 1
v2 1
v2 2
v3 1
v3 3
v3 4
v3 5
Plz guide me how to achieve this result from the variable.
Thanks in advance :-)
Guys.
I have some data like this one:
'UploadfilesCompanyIDProjectCodeFilesFile1.doc'
P.S: the values may vary each time.
I need to programmatically extract from that kind of string only file1.doc
Ho can I dynamically do it? any function? statement? etc? please help.
The idea is getting only the string after the last ""
Thanks
Fr
heyI have a problem string index;Command.CommandText = "Select * from employees where LastName = ' " Index " ' ";but the problem is iam not able to pass the index value properly like it should be ( ' Index ' ) for SqlServer2000.but its either giving me an error or its not taking the value properly.Can anyone help me out with that.Thanks
View 3 Replies View RelatedCan someone point me in the right direction. I would like to grab some data from an email (not as an attachment)and load into a table, but I have no idea where to begin. Also, can this data also be retreived from a website?
Thanks
BV
Has anyone been able to solve the issue with setting up a package in EM, which connects to an Exchange(.edb) database and extracts the data into a SQL Server db. Previous message posts don't offer much insight. So far, all I have is using Access 2000, to connect & move the data, then extract from here.
Thanks in advance for any help.
BV
I recall something about Analysis Services (SQL server tool) that is able to extract data from the DB tables (select multiple tables if one wishes (and to link those tables up ??!?)) then extracted to MS Excel. It has been a while since the course was last taught, I'm not at all sure how the steps were, or how Excel were able to display them. Would someone advice or refer to a tutorial if possible?
Much thanks!
Hi,
I can't figure out how to extract just the date or just the time from a DATETIME data type? Any suggestions?
I have a table having multiple fields but I need to extract data based on the two fields.
Field1 Field2
1 | 123
1 | 213
2 | 123
2 | 3654
2 | 4758
I want the result with 2 cases
1.) distinct in Field 1 but multiple in field 2
Expected result:
Field1 column1 column2 column3
1 | 123 | 213
2 | 123 | 3654 | 4758
2.) multiple in field 1 but distinct in field 2
Expected Result :
Field 2 | Column1 | Column2
123 | 1 | 2
There may be more columns added according to the data available.
I have a description that is dynamically built for a part, with variables for this part split by line feeds. For example
100" widget
Color: White
Holes: Many
Material: Wood
This makes everything nice an neat on reports, etc, and the world is good. There are a lot of better ways to do this I am sure, but due to the limitations of the software we are using, this fits the bill best.
In SQL view, it LOOKS like the lines are all run together, but when you actually pull the data, it formats properly.
Enter a new need to identify just the first "line" of info. I would like to be able to query all characters left of the CHAR(10). In the example above, the results would be just 100" Widget. I have tried a couple ways using Left(field,charindex(char(10))-1 but nothing seems to get me data back. Is this even possible within a View?
I have an Excel 97 file that has a lot of data in it and I need to pull out pieces of that data. Here is one example of the data I have. It is in colum C in Excel.
Row Column C
1 11/25/07
2 12/01/07
3 week 1
4
5
6 9,708
7 128,353
8 -
9
10 42,691
11 3,000
12 183,751
13 $183,751
What I want to get out of this to put into a SQL table in one record is (I've included a header row here):
StartDate EndDate Week Value
11/22/07 12/1/07 1 183751
What is the best way to attach this? I have several other items I need to pull out of this Excel File, but if I can get this figured out I can adapt it to get the rest of what I want.
hello
hope someone can help with this problem. I've created a package which copies data from an oracle 10g database into excel. the package works fine when executed in BIDS, at the command line or using dtexec but fails whenever i try and schedule it as a sql agent job. The package was created as the same user that sql agent runs as. I'm getting the following error message....
Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft OLE DB Provider for Oracle" Hresult: 0x80004005 Description: "Oracle client and networking components were not found. These components are supplied by Oracle Corporation and are part of the Oracle Version 7.3.3 or later client software installation. Provider is unable to function until these components are installed.". End Error Error: 2008-02-15 14:28:03.85 Code: 0xC020801C Source: Data Flow Task OLE DB Source [1] Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "OPRD.epsenq" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed. The package execution fa... The step failed.
does oracle client tools need to be installed in a certain locaion? the server is 32 bit.
thanks
iain
I have a form in Notes that is huge. I need to extract a part/field from that form into SQL SERVER. If I use NotesSQL Thru SQL DTSPackages, I am afraid it will run very long. Is there any other option on that?
Any help is appreciated.
I am using data flow task.And data flow source uses ole db for olap 9.0 to connect my ssas. sql comment is my access mode. A mdx query extracts data. Data flow destination is sql server table. Error said Data Flow Task: OLE DB Source [579]: The output "OLE DB Source Output" (589) references an external data type that cannot be mapped to a Data Flow task data type. I guess it is a implicit data type convertion problem. But how to solve it???
I appreciate for all suggestion. Thanks.
Hello,
I'm trying to pull 1.2 million rows down from an Oracle database into SS2005. It has been painfully slow (about 20 - 30 minutes). Is there a way that I could speed this up?
While I am connecting to Oracle 9, I tried downloading the latest drivers (10g) from Oracle because I thought I could use the ODP.NET drivers. Unfortunately it did a number on my system and I could no longer even ping one of the databases.
I've seen this question sort of answered, but the only answer that I've found seems to be to use a custom connector developed by http://www.persistentsys.com. That is great that they have developed something, but I would expect that I should be able to use Oracle and/or Microsoft's drivers to get similair performance. (OK, I just don't want to pay for it )
I was thinking I could do a bulk dump from Oracle, into a flat file and then bulk load it into SQL Server, but I don't have (or know about) tools that can bulk dump from Oracle.
Thank you for the help.
-Gumbatman
I am creating an index on a table wit 35 million records but I get the error
'TT_ObjPerformance' table- Unable to create index 'IX_TT_ObjPerformance_CACode'. Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
How can I get the index created?
ThanksSQL Server newbie
Hi,
Trying to optimize a query but not sure what to do. I have this query on which I ran an exec plan,
SET NOCOUNT ON;
SELECT qaTestSuite.TestSuiteID, qaTestSuite.TestSuiteStart, qaTestSuite.TestInterface, qaTestSuite.TestVersion, qaTests.TestMachine, qaTestSuite.TestClientMachine, qaTests.TestLogin, qaTests.TestLabel,
qaTestSuite.TestCLPs, qaTestSuite.TestSuiteEnd, qaTests.TestID, qaTests.TestIDInternal, qaTests.TestStart,
qaTests.TestName, qaTests.TestTier, qaTests.TestNo, qaTests.TestWFBCalled, qaTests.TestWFBTime,
qaTests.TestSearches, qaTests.TestSearchesTime, qaTests.TestResult, qaTests.TestEnd, qaTestMssgs.TestMssgsID,
qaTestMssgs.TestMssgTime, qaTestMssgs.TestMssgType, qaTestMssgs.TestMessage, qaTestSuite.TestMode
FROM qaTestSuite with(NOLOCK) INNER JOIN
qaTests with(NOLOCK) ON qaTestSuite.TestSuiteID = qaTests.TestSuiteID INNER JOIN
qaTestMssgs with(NOLOCK) ON qaTests.TestID = qaTestMssgs.TestID
order by qaTestSuite.TestSuiteStart DESC
and it gives me the following results:
Use a Bookmark (RID or Clustering Key) to look up the corresponding row in the Table or Clustered Index.
Physical Op: Bookmark Lookup
Logical Op: Bookmark Lookup
Est. Row Count: 128
Est. Row Size: 4760
Est. I/O Cost: 0.368
Est. CPU Cost: 0.000141
Est. Execs: 1.0
Est. Cost: 0.368888(89%)
Est. Subtree Cost:.415
Argument:
BOOKMARK:([Bmk1004]), OBJECT:([QAMaster].[dbo].[qaTestMssgs]) WITH PREFETCH
I have no idea what to do with that. Anyone have any clues? What I found online was that I should make a Covering Index, but I didn't find
any patterns on how to do that. Any one have ideas of how to do this?
Thanks very much for your help!
--PhB
Hi
i am new to using this sql server 2000....this is a very simple question to all u guys.....i am just in a learning stage...so any help from u guys is really appreciable....
i need to create a table customers with the following columns...
identity column to self-populate as the primary key,
joindate, leavedate, custcode, empID.
This is the one i tried:
create table customers (id int primary key identity (1,1) not null,
joindate smalldatetime null,
leavedate smalldatetime null,
custcode varchar (10) not null,
empid int not null
)
is tht code correct only???
and i also want the below one :
Create indexes on the leavedate, custcode and empid columns.
how to create these indexes???
and wht happens when i create them(like is thr any advantage of creating indexes???)
thanks......
HiI tried the following from the help file...When you create or modify a unique index, you can set an option toignore duplicate keys. If this option is set and you attempt to createduplicate keys by adding or updating data that affects multiple rows(with the INSERT or UPDATE statement), the row that causes theduplicates is not added or, in the case of an update, discarded.For example, if you try to update "Smith" to "Jones" in a table where"Jones" already exists, you end up with one "Jones" and no "Smith" inthe resulting table. The original "Smith" row is lost because anUPDATE statement is actually a DELETE followed by an INSERT. "Smith"was deleted and the attempt to insert an additional "Jones" failed.The whole transaction cannot be rolled back because the purpose ofthis option is to allow a transaction in spite of the presence ofduplicates.But when I did it the original "Smith" row was not lost.I am doing something wrong or is the help file incorrect.Dan
View 3 Replies View RelatedHey all, WE have a document management system where by Adminstrators can upload documents, once the document is uploaded the binary data is stored on in a folder on the web server. We used to stored the documents in the actaul db table, but we found that there were to many documents and it was using alot of space on db server. So my boss has decided we are now going to upload the binary data onto the web server. Currently we are donig this with new documents which have been added or documents which are gettinguploaded when reloading, but there are many documents in the db table which have not been updated and are still embedded in the db table. So i need to figure out how to go about copying the data storewd in the db table and storing it in web servers folder location. I've tried various things for a enitre day but im going round in circiles. MemoryStream mStream = new MemoryStream((Byte[])dtrResults["file"]); BinaryReader bReader = new BinaryReader(mStream); int intFileSize = (int)mStream.Length; Byte[] byteFile = (Byte[])dtrResults["file"]; i can get to this state but then how do i create a folder on the BinaryREader to then store the binary data of the file to the location. BinaryReader bReader2 = new BinaryReader(File.Open(strDocFolder + strSavedFileName, FileMode.Create)); int count2 = bReader2.Read(byteFile, 0, intFileSize); bReader2.Close();i've also tried this but when the file gets created in the folder there is no content. i do know that the file does contain content as ive tried this and downlaoding the file from that page acctually works string strContentTpe = WValue.WStr(dtrResults["contenttype"]); int intFileSize = VValue.VInt(dtrResults["filesize"]); / Byte[] byteFile = ((Byte[])dtrResults["file"]); //Downloads the data correctly Response.ClearContent(); Response.ClearHeaders(); Response.AddHeader("Content-Disposition", "attachment; filename="" + WValue.WStr(dtrResults["docfilename"]) + """); Response.AddHeader("Content-Length", WValue.WStr(intFileSize)); Response.ContentType = strContentTpe; Response.BinaryWrite(byteFile); I hope ive made some snese andthat someone can hlep me. Have a nice dayZal
View 2 Replies View RelatedI have a coworker who needs to extract data from Oracle into SQL Server 6.5 and Sybase as part of an interface that runs daily. How is the best way to do this?
Tony
Hello,
I have been placed in the position of administering our SQL server 6.5 (Microsoft). Being new to SQL and having some knowledge of databases (used to use Foxpro 2.6 for...DOS!) I am faced with an ever increasing table of incoming call information from our Ascend MAX RAS equipment. This table increases by 900,000 records a month. The previous administrator (no longer available) was using a Visual Foxpro 5 application to archive and remove the data older than 60 days. Unfortunately he left and took with him Visual Fox and all of his project files.
My question is this: Is there an easy way to archive then remove the data older than 60 days from the table? I would like to archive it to a tape drive. We need to maintain this archive for the purposes of searching back through customer calls for IP addresses on certain dates and times. We are an ISP, and occasionally need to give this information to law enforcement agencies. So we cannot just delete it.
Sorry this is so long...
Thanks,
Brian R
WESNet Systems, NOC