T-SQL (SS2K8) :: Selecting From A Remote Spreadsheet
Jun 11, 2014
I'm trying to run this code, which worked on SQL 2000, on 2K8:
SELECT [Business Unit]
,[Department]
,[Ext :]
,[F9]
,[First Name :]
,[Last Name :]
,[Mobex :]
,[Mobile :]
,[Sub Department]
FROM MyCompanyStaffPhoneList...Sheet1$
However it just returns this message..Msg 7308, Level 16, State 1, Line 1 OLE DB provider 'Microsoft.Jet.OLEDB.4.0' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.
View 2 Replies
ADVERTISEMENT
Jun 17, 2015
writing data extracted from sql server to mutliple tabs within an Excel Spreadsheet?
View 1 Replies
View Related
Mar 28, 2014
I have data like the following:
ID COUNTER DATA
1 10 BLAH
1 20 BLAH
2 10 BLAH
3 10 BLAH
2 20 BLAH
2 30 BLAH
What I want to return is:
1 20 BLAH
2 30 BLAH
3 10 BLAH
I want the top 1, having the highest counter from each ID. This is a highly simplified version of that I am pulling which also is between a date range, but same principle.
IE: SELECT * FROM Table WHERE ID in (SELECT DISTINCT ID FROM Table WHERE Date BETWEEN <date> AND <date>
I'd rather keep it in one statement if possible, but if I have to do it in multiple passes then so be it.
View 6 Replies
View Related
May 14, 2014
The situation is that we have resources (trucks) that perform shifts. Shifts consists of actions. A resource can perform multiple shifts.
For every resource we want to find the record that:
- Is 'younger' than the last realized action.
- Has actionkind pickup, deliver or clean
I have constructed a solution with CTE and row_number but I was curious if there would be other alternatives. The fact that I'm joining a CTE onto itself and subject the outcome to a partition makes me think there are sharper ways.
Note that the action id in the data below is also sorted but in practice this need not be the case. The sorting key is prevalent.
output of the query is
id_action id_resource actionKindCode
4665 4 clean
34540 96 pickup
24000 901 clean
declare @mytable table (
id_action int,
id_shift int,
[code]....
View 6 Replies
View Related
Nov 2, 2015
I have a table of data with lines of various lengths. An example is
A Smith - Give #12345# Sydney City
B Jones and S Jones - Give #876543# Washington
I am trying to work out how to create a new column with just the number between the # symbols. The number would be between 5 and 9 numbers.
View 9 Replies
View Related
Jun 4, 2014
I have 3 tables:
News:
bigint NewId
nvarchar NewTitle
datetime NewDate
nvarchar NewBrief
--------------------------
Category:
int CatId
nvarchar CatName
--------------------------
NewsRelCategory:
bigint Id
int CategoryIdFk
bigint NewsIdFk
--------------------------
I want to select NewId, NewDate and Distinct NewTitle
I tried this but NewTitle doesn't distinct:
SELECT
FROM dbo.Category INNER JOIN
NewsRelCategory ON dbo.Category.CatId = NewsRelCategory.NrcCategoryIdFk INNER JOIN
dbo.News ON NewsRelCategory.NrcNewsIdFk = dbo.News.NewId
View 9 Replies
View Related
Aug 27, 2014
I have created the following SQL snippet that is a very simple mock-up illustrating the problem (I hope!) that I am facing:
-- create table
if object_id('tempdb..#tmpdelnotes') is not null
drop table #tmpdelnotes
create table #tmpdelnotes(
DelNote int identity (1,1) ,
DelDate date not null,
Item int not null,
Customer int not null)
[code]...
What I need to retrieve is a unique list of item numbers with information about the latest (DelDate) delivery note. The "Clumsy workaround" works, but is not very pretty when doing multiple table joins. Is it really necessary to use a derived table for this kind of query? Window functions can only exist in the SELECT and ORDER BY clauses, which is understandable since the calculations take place (I would guess) after the aggregations in the HAVING clause.
View 2 Replies
View Related
Feb 28, 2007
Hello all,
I'm a bit new to SQL Server and T-SQL (my background is in DB2), so hoping you can help me with this. I'm writing a T-SQL script that's hopefully going to scan a bunch of SQL server instances and record the results. We've got a mix of SQL2K and SQL2K5 instances, and for part of my script I only want to run something if the remote instance is SQL2K.
I'd tested this on my local PC with several instances created and it worked fine. When I try and run it for instances located on other servers it barfs :eek: . From what I've found from looking up the error message it looks like I've got myself a loopback -- but I don't know how to get around it - any ideas please?
DECLARE @tempdata TABLE (scratch sql_variant)
DECLARE @debug tinyint
DECLARE @server sysname
DECLARE @version varchar(20)
SET @debug=1
SET @server='SQLSERVER_INSTANCE'
INSERT INTO @tempdata (scratch) EXEC ('[' + @server + '].master.dbo.sp_executesql N''SELECT SERVERPROPERTY(''''ProductVersion'''')''')
SELECT @version = convert(varchar(100),scratch) from @tempdata
DELETE FROM @tempdata
IF @debug>0 BEGIN PRINT @server + ' is running SQL Server version: ' + @VERSION END
IF charindex('8.00',@version) > 0 BEGIN
PRINT 'SQL2K-only code goes here'
END
The error I get is:
OLE DB provider "SQLNCLI" for linked server "SQLSERVER_INSTANCE" returned message "The transaction manager has disabled its support for remote/network transactions.".
Msg 7391, Level 16, State 2, Line 1
The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "SQLSERVER_INSTANCE" was unable to begin a distributed transaction.
I get this error running from a SQL2K5 instance against both SQL2K or SQL2K5
TIA, moff.
View 3 Replies
View Related
Apr 16, 2014
What I am trying to do: Obtain attendance percentages for schools for the last five days. The outcome would look like this:
DISTRICTGROUPING, SCHOOLNAME, 5 DAYS AGO PCTG, 4 DAYS AGO PCTG, 3 DAYS AGO PCTG, 2 DAYS AGO PCTG, 1 DAY AGO PCTG
I am using nested subqueries for each day as follows: (total enrollment-total absent/total enrollment)
,(
((SELECTCOUNT(*)--GET TOTAL ENROLLMENT COUNT FOR SPECIFIED DATE
[Code]....
The query works with the following exceptions:
My issues are:
1. Avoid the "division by zero" error. This can occur if a school is closed for a day or if a smaller school has no absences for a day.
2. Avoid weekend dates. I need the query to display only weekdays
3. Currently I am using "PERCENTAGE 5: as a column header whereas I need the actual date as the header.
View 6 Replies
View Related
Feb 26, 2015
I need to write a stored procedure or SSIS to export data to client's desktop. What is the best way to achieve it?
The requirement is to develop a web application the client can click on the button and the file will be saved to the clients' local C drive.
How should I configure the destination path here?
View 3 Replies
View Related
Jan 30, 2015
I'm trying to write a function that will retrieve the last backup date/time of a particular database on a remote server (i.e. by querying msdb.dbo.backupset). Unfortunately, you can't use sp_executesql in a function, so I can't figure out a way to pass the server name to the query and still be able to return the datetime value back to the calling TSQL code (so that rules out using EXEC().
View 3 Replies
View Related
Apr 15, 2014
I am facing a problem in writing the stored procedure for multiple search criteria.
I am trying to write the query in the Procedure as follows
Select * from Car
where Price=@Price1 or Price=@price2 or Price=@price=3
and
where Manufacture=@Manufacture1 or Manufacture=@Manufacture2 or Manufacture=@Manufacture3
and
where Model=@Model1 or Model=@Model2 or Model=@Model3
and
where City=@City1 or City=@City2 or City=@City3
I am Not sure of the query but am trying to get the list of cars that are to be filtered based on the user input.
View 4 Replies
View Related
Sep 20, 2007
I've got a big problem that I'm trying to figure out:
I have an address table out-of-which I am trying to select mailing addresses for companies UNLESS a mailing address doesn't exist; then I want to select the physical addresses for that company. If I get multiple mailing or physical addresses returned I only want the most recently edited out of those.
I don't need this for an individual ID select, I need it applied to every record from the table.
My address table has some columns that look like:
[AddressID] [int]
[LocationID] [int]
[Type] [nvarchar](10)
[Address] [varchar](50)
[City] [varchar](50)
[State] [char](2)
[Zip] [varchar](5)
[AddDate] [datetime]
[EditDate] [datetime]
AddressID is a primary-key non-null column to the address table and the LocationID is a foreign key value from a seperate Companies table.
So there will be multiple addresses to one LocationID, but each address will have it's own AddressID.
How can I do this efficiently with perfomance in mind???
Thank you in advance for any and all replies...
View 2 Replies
View Related
Dec 28, 2000
I need to take my 'table stats'every week and put them into an excel spreadsheet so that I can track the changes of my table sizes over time (basically I am watching to see how many rows are in each table). What I was planning on doing was to create a view of my table stats that I could then use DTS to transfer on a weekly basis into my excel spreadsheet. I have only used DTS a couple of times, and I have not ever tried it with excel. Now the problem: Everytime my DTS job runs it appends the data to the end of the origional columns that were created. Since my database has over 5000 tables these columns grow quite quickly. What I would like to do is set it up so that everytime the job runs it puts the new data into new columns in the same worksheet of my excel file. Is this possible? Any suggestions?
View 1 Replies
View Related
Jan 9, 2007
I'm getting an issue on a MS SQL DTS package that is doing a simple export from a MS SQL table to and Excel spreadsheet. I have three of these running but one is failing. I’m using DTSRun to run all three of these DTS packages. The only recent change was to the DTS package to fix the first step to delete the data in the spreadsheet tab named “Results”. The process works correctly in development (on different servers). The same active directory ID is being used on all three DTS packages and all three do the same i.e. export data to an excel spreadsheet in the same file location but with different names. I’ve Google’d this but only came across access issues which does not make since since it is writing the other two spreadsheets just fine. Curious.
Error I See:
Running DTS package with passed variables
...
DTSRun: Loading...
DTSRun: Executing...
DTSRun OnStart: DTSStep_DTSDynamicPropertiesTask_1
DTSRun OnFinish: DTSStep_DTSDynamicPropertiesTask_1
DTSRun OnStart: Drop table Results Step
DTSRun OnError: Drop table Results Step, Error = -2147217911 (80040E09)
Error string: Cannot modify the design of table 'Results'. It is in a read-only database.
Error source: Microsoft JET Database Engine
Help file:
Help context: 5003027
Error Detail Records:
Error: -2147217911 (80040E09); Provider Error: -538642193 (DFE4F8EF)
Error string: Cannot modify the design of table 'Results'. It is in a read-only database.
Error source: Microsoft JET Database Engine
Help file:
Help context: 5003027
Any ideas would be great.
Thanks.
Jim
View 3 Replies
View Related
Apr 4, 2007
Can somebody please tell me what I am doing wrong or need to do to resolve my issue. I having problems with one of the columns in an excel spreadsheet that I am trying to upload into the system. One of the columns contain values of both text and numbers such as 'ABC123', 'ABC124', '123456' etc. When I try uploading the sheet into SQL Server 2000 using DTS, the system removes all characters from the one column that I am having problems with. So entry 'ABC123' for example would be truncated to '123'. I tried formatting the column that I am having trouble with in excel to 'General' format as well as tried to transform the column to type varchar in SQL Server while using the DTS wizard but still had no luck. The problem is that SQL server thinks that the column is a float type column from the source and therefore truncates any characters.
View 2 Replies
View Related
Sep 13, 2005
Using an access project front-end to an MSDE database, I'm trying toimport a spreadsheet using File->Get External Data->Import.After I select the spreadsheet name, I tell it I want to import into anexisting table and select the table name from the list. The name inthe list is dbo.Cost_Data. When I tell it to finish, it says the tabledoes not exist.If I do the same steps as the database owner, the table name does notcontain the "dbo." on the front of it and it is successful.Thanks for any help.Jerry
View 1 Replies
View Related
Feb 21, 2008
Does anyone know how to get a count of columns in a spreadsheet before importing ?
Thanks in advance
View 8 Replies
View Related
Feb 23, 2006
Being new to SSIS I wish to loop through a series of excel spreadsheets and within each workbook loop through each sheet. I am aware of the For Each container but how can the each sheet in the workbook be referenced?
Steve
View 42 Replies
View Related
Feb 21, 2007
Hello,
I have some XML that I'm passing through a variable into the XML task in SSIS. I also have an xsd file that I'm using and I want to validate (I think) that XML and have the XML task output an excel document. I've got the xsd file all set up in the "second operand" part of the XML task and the XML I'm passing in as a variable and that's in the "input" part of the XML task. My question is are there any tricks to make an excel document with these two things? Is there something I need to do in the xsd file to tell it that I want excel? Below is my XML and xsd:
XML:
<person>
<person_id>117</person_id>
<last_name>Von Neumann</last_name>
<first_name>Marina</first_name>
<jobs>
<job>
<job_key>1</job_key>
<from_month>06</from_month>
<from_year>1987</from_year>
<company_id>30358</company_id>
</job>
<job>
<job_key>2</job_key>
<from_month>06</from_month>
<from_year>1985</from_year>
<company_id>30358</company_id>
</job>
<job>
<job_key>3</job_key>
<from_month>06</from_month>
<from_year>1979</from_year>
<company_id>30358</company_id>
</job>
<job>
<job_key>4</job_key>
<from_month>09</from_month>
<from_year>1992</from_year>
<company_id>8766</company_id>
</job>
<job>
<job_key>5</job_key>
<from_month>06</from_month>
<from_year>1962</from_year>
<company_id>8822</company_id>
</job>
<job>
<job_key>6</job_key>
<from_month />
<from_year />
<company_id>8822</company_id>
</job>
</jobs>
</person>
XSD:
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"><xsd:element nillable="true" name="person"><xsd:complexType><xsd:sequence minOccurs="0"><xsd:element minOccurs="0" nillable="true" type="xsd:integer" name="person_id" form="unqualified"></xsd:element><xsd:element minOccurs="0" nillable="true" type="xsd:string" name="last_name" form="unqualified"></xsd:element><xsd:element minOccurs="0" nillable="true" type="xsd:string" name="first_name" form="unqualified"></xsd:element><xsd:element minOccurs="0" nillable="true" name="jobs" form="unqualified"><xsd:complexType><xsd:sequence minOccurs="0"><xsd:element minOccurs="0" maxOccurs="unbounded" nillable="true" name="job" form="unqualified"><xsd:complexType><xsd:sequence minOccurs="0"><xsd:element minOccurs="0" nillable="true" type="xsd:integer" name="job_key" form="unqualified"></xsd:element><xsd:element minOccurs="0" nillable="true" type="xsd:string" name="from_month" form="unqualified"></xsd:element><xsd:element minOccurs="0" nillable="true" type="xsd:string"
name="from_year" form="unqualified"></xsd:element><xsd:element minOccurs="0" nillable="true" type="xsd:integer" name="company_id" form="unqualified"></xsd:element></xsd:sequence></xsd:complexType></xsd:element></xsd:sequence></xsd:complexType></xsd:element></xsd:sequence></xsd:complexType></xsd:element></xsd:schema>
Thanks,
Phil
View 7 Replies
View Related
Jan 5, 2007
Hi,
Two problems / questions I could do with some help on please......
1). How do you clear a spreadsheet before loading it ? I am sending a spreadsheet by Email on a regual basis. Each time I run my SSIS package the data is appended to that of the previous run. I need just the contents of this data load to populate the spreadsheet each time. I have tried various methods / techniques for achieveing this so I just need to know the accepted method. Sorry if this is very basic ( as it should be ?!) but I seem to be missing the point somewhere.
2). I have tried to resolve issue 1 above by having an empty 'template' spreadsheet and copying that one over the top of the previously populated one. I get an error saying that the source spreadsheet ..'is in use by another processes.' Amusingly, as a test, I can delete it but I cannot copy it !
Are there any decent workthroughs on working with destination excel speadhseets ? Im struggling.
Thanks,
Jon
View 5 Replies
View Related
Mar 26, 2008
Hi there,
I am able to import an excel spreadsheet into a table in sql server 2005 using SqlBulkCopy. The only thing that bothers me here is how to check duplicate entries and throw an error to the user regarding the duplicate entries. In the table in sql, there is no primary keys. There are five columns and the way I will have to find the duplicates is to match all those 5 columns. Since the excel spreadsheet can have 40 to 500 entries, how can I check those dupes.
View 1 Replies
View Related
Nov 6, 2003
Hi everyone,
I've got an Excel Spreadsheet with 5 columns of data which I need to get into an SQL Table. There's 13,000 rows in this Spreadsheet so manually doing it is out of the question.
How can I go about doing this?
Any help will be appreciated!
Thanks,
Andrew
View 4 Replies
View Related
Jul 16, 2001
Hey guys,
I'm exporting via dts to an excel spreadsheet. However my database has more than 65000 records. DTS croacks and shoots out that there are too many records in the spreadsheet.
What do I do? Any way to go around that? These are my daily hit logs that are recorded and they tend to get big.
Ilya Zherebetskiy
View 1 Replies
View Related
Aug 7, 2002
I am new to SQL and can do queries OK on SQLTalk. I need to know if there is a script to retrieve data and then export to an Excel spreadsheet for internal company use. Is there such a beast and is this the right place to look???
View 1 Replies
View Related
Nov 10, 2004
Ok, so my boss handed me a spreadsheet that contains about 1,000 records as retrieved by a SQL query.
My boss now wants me to run a query on ONLY the records given to me in the spreadsheet. He wants me to grab two more fields of data for each of the records in the sheet.
Naturally, my first question to my boss is, "Why don't you just give me the SQL code that was used for that query, and I'll just add a string or two to grab those additional fields? ...And then we'll run the new SQL?"
My boss says, "Isn't there a way to run a query on just the records I've given you? Someone in another department has been able to do that before."
I've never heard of this. Can anyone provide insight into how I might accomplish what my boss wants before I tell him he's full of crap?
View 6 Replies
View Related
Aug 9, 2007
Hi,
I'm trying to import an excel file into SQL sever(using an insert statement), i'm creating a DTS package (in enterprise manager) and have VB Script. When i parse it, i get no errors, but when i run the package it says that it ran successfully but nothing happens, it doesnt insert into the table, even though i tested the insert statement. Can anyone help me?? Here's the code:
'************************************************* *********************
' Visual Basic ActiveX Script
'************************************************* ***********************
Function Main()
on error resume next
Set objxl = CreateObject("Excel.Application")
objxl.Visible = False
Dim xlFile
xlFile = "C:Datafile.xls"
Set objWkb = objxl.Workbooks.Open(xlFile)
'' Connecting to SQL Server
set cn = server.CreateObject("ADODB.Connection")
Dim serverName
serverName = "myserver2"
strCS = "Provider=SQLOLEDB; Data Source=myserver2;Initial
Catalog=mycat; Integrated Security=SSPI"
cn.ConnectionString = strCS
On Error Resume Next
cn.Open
Set objsht = objWkb.Worksheets.Open("Sheet1")
Dim client_name, rb, date_rvd, LOB
Dim sql
Dim row, sequence
row = 2
client_name = Trim(objsht.Cells(row, 2).Value)
Do While IsNull(client_name) = False And client_name <> ""
'client_name = Trim(objsht.Cells(row, 2))
rb = Trim(objsht.Cells(row, 4).value)
date_rvd = Trim(objsht.Cells(row, 6).value)
LOB = "WCS"
sql = "INSERT INTO TEMP_TEST (CLIENT_NAME, RB, DATE_REVIEWED, LOB) VALUES (" & _
" '" & client_name & "' ,'" & rb & "', " & date_rvd & ", '" & LOB & "');"
row = row + 1
MsgBox (sql)
client_name = Trim(objsht.Cells(row, 2).value)
cn.Execute (sql)
Loop
if err.count = 0 then
Main = DTSTaskExecResult_Success
else
Main = DTSTaskExecResult_Failure
end if
End Function
View 14 Replies
View Related
Feb 5, 2004
Dear all,
i'm using DTS to create a procedure. what i wanna do is to pump data to the excel, however i need to re-use the same excel file everytime, ie:i need to delete all fields in the spreadsheet except the colume name and then pump all data in again. i know how to pump the data in, however i dun know how to simply clear the existing data in the spreadsheet, can anyone help me, big thx ~!
Kam2
View 3 Replies
View Related
May 12, 2008
I am trying to set up a linked server in SQL Server 2005 to link to an excel spreadsheet.
-I am selecting Jet 4.0 as the provider
-Product name is Excel
-Data Source is the path on our network to the excel file: N:Devon 5403 4.0 Engineering4.01 ProcessLinelistIFCLDT Field.xls
-Provider String is Excel 8.0
-Security | Login not defined is set to Be made using the login's current security context.
The Excel file is an Excel 2003 spreadsheet. The worksheet is titled Pages
I have a query window open in SQL Server Management Studio and the following is my select statement:
SELECT * FROM DEVON_LINE_LIST...Pages$
I get the following error message:
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "DEVON_LINE_LIST" returned message "Cannot start your application. The workgroup information file is missing or opened exclusively by another user.".
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "DEVON_LINE_LIST" reported an error. Authentication failed.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "DEVON_LINE_LIST".
I get similar error messages no matter which security settings I pick.
Any thought as to what I can try to get this to work?
View 3 Replies
View Related
Nov 4, 2013
I am looking for a way to import data from a CSV or Excel spread sheet and add the data directly into an Extended field instead of a regular field in the table. for example: let's say I have a comma delimited field with the following info:
NDC_M_FORMULARY,CUSTOM_EXTSIG,Custom EXT SIG
NDC_M_FORMULARY,DRUG_CODE,Alternate key, user defined
NDC_M_FORMULARY,CHARGE_CODE,From the Charge code table
The first column is the table name
Second Column is the Column name in the table
The third column contains the description that I would like to store in the Value in the Extended Property Name "MS_Description"
BTW,I did find the following T-SQL which returns the Extended description for a specific Extended Property
Here it is:
SELECT
[Table Name] = i_s.TABLE_NAME,
[Column Name] = i_s.COLUMN_NAME,
[Description] = s.value
FROM
INFORMATION_SCHEMA.COLUMNS i_s
LEFT OUTER JOIN
[code]...
View 1 Replies
View Related
Dec 18, 2005
Hi All,
I am new to SQL program. I did little management for SQL 2000 before.
I need to export from a table or view to excel spreadsheet for company's marketing resourece. Is there any easy simple way to do it?
Thanks all for your help.
Perry Yang
View 11 Replies
View Related
Dec 7, 2005
hi guys,i've just started woth Excel component in .NET. i manged to importthe Excel Component on windows Forms but i'm not able to load Excelfile in that. if anyone has any idea how can this be done please do letme know. i'm in great need.thanks,Lucky
View 1 Replies
View Related
Mar 7, 2008
I am using SSIS to export data from a table to an Excel spreadsheet. This all seems to work put just fine. The user would like a data in column B1 to say when the spreadsheet was created. Is there a way within SSIS to do this. I was looking at using a .NET script but it accesses the spreadsheet as a table so I do not know how to insert data above the headings in row 1. I believe the OleDB provider using column 1 as it column names for the table. Maybe I am just going about the whole think wrong?
Thanks
View 5 Replies
View Related
