I have to run a simple query (say select name from table where id = 4) and get those results and make it into a report. I am not sure I have crystal reports (do I have to install some thing for this?). So what is the easiest way to create this? May in in a spread sheet or some thing like this? I am so new to SQL Server
Thanks
how to transfer data from Excel spread sheet to table in the data base table..?
View 1 Replies View RelatedHi,
I've a problem with excel destination spread sheet.I've created a package which pulls the data from sql server and load it into excel sheet.The main thing Ive to do is I've to create different destination tables(work sheets) for different data.i.e.,The source is a sql query which pulls the data in groupwise with group by clause.So,I've to create individual work sheet for each group with that data.How it can be done.Please, advice me.
Thanks in advance.
Hi All,
Here is the scenario:
I am inserting data into a spread sheet from user interface(power builder). But at the same time some one can open that excel spread sheet to read the data. Then the process was going to fail(it won't able to write the data in to the spread sheet). How to avoid this situation? I really appreciate if anyone can shed some light.
Thanks in advance!!
Hi,
I have a spread sheet which has 4 columns called cusip, Chartheader, growthdates and NAV.. and i also have the same number of columns in the Sql server... and I want to add another column called Rownumber and set it as int indentity... and when i try to import the data to sql server i am getting this error called
Received an invalid column length from the bcp client for colid 1.
How should i fix it.
Regards
Karen
Code:
-- (1) Number of calls received for each priority of call [for a specified date range]
declare @startdate datetime,
@finishdate datetime
select RM.fldPriorityCode as 'Priority',
count(RM.fldRequestID) as 'Calls'
from tblRequestMaster RM
where RM.fldPriorityCode between 1 and 5
and RM.fldRequestDate between '01-01-2007' and '03-05-2007'
and RM.fldRequestFlag like 'D'
group by RM.fldPriorityCode
union
select
'Total' as 'Priority',
count(RM.fldRequestID) as 'Calls'
from tblRequestMaster RM
where RM.fldPriorityCode between 1 and 5
and RM.fldRequestDate between '01-01-2007' and '03-05-2007'
and RM.fldRequestFlag like 'D'
order by RM.fldPriorityCode asc
Results:
PriorityCalls
120
22912
3152
4571
54
Total3659
I would like to transfer these results to an excel sheet. For instance when the user opens up the excel worksheet and types in for a example a start date: 01-01-2007 and an end date: 03-05-2007 (into textboxes) then clicks a button say called 'Get stats' and then the results appear on the sheet.
How can this be done?
-- (1) Number of calls received for each priority of call [for a specified date range]
declare @startdate datetime,
@finishdate datetime
select RM.fldPriorityCode as 'Priority',
count(RM.fldRequestID) as 'Calls'
from tblRequestMaster RM
where RM.fldPriorityCode between 1 and 5
and RM.fldRequestDate between '01-01-2007' and '03-05-2007'
and RM.fldRequestFlag like 'D'
group by RM.fldPriorityCode
union
select
'Total' as 'Priority',
count(RM.fldRequestID) as 'Calls'
from tblRequestMaster RM
where RM.fldPriorityCode between 1 and 5
and RM.fldRequestDate between '01-01-2007' and '03-05-2007'
and RM.fldRequestFlag like 'D'
order by RM.fldPriorityCode asc
Results:
PriorityCalls
120
22912
3152
4571
54
Total3659
I would like to transfer these results to an excel sheet. For instance when the user opens up the excel worksheet and types in for a example a start date: 01-01-2007 and an end date: 03-05-2007 (into textboxes) then clicks a button say called 'Get stats' and then the results appear on the sheet.
How can this be done?
Hi everybody need help on the possibility creating a new table from the results of a view or query? below is my table named table1
ID col1 col2
1 a a
2 b d
3 c f
this would be my new table named table2
ID col1 col2 col3
1 a a aa
2 b d bd
3 c f cf
this new table has an additional column by concatenating col1+col2
tried this procedure but is not working
CREATE TABLE AS (SELECT ID, COL1, COL2, COL1+COL2) TABLE2
thanks
I am trying to fetch records from excel sheet using Select Query but I am getting the error message saying
"Msg 7302, Level 16, State 1, Line 1
Cannot create an instance of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)"."
Here is my Query,
sp_configure 'show advanced options',1
reconfigure with override
go
sp_configure 'Ad Hoc Distributed Queries',1
reconfigure with override
go
reconfigure
SELECT *
FROM OPENROWSET
('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:M2MworkedworkedBOS.xlsx;HDR=YES', 'select * from [Sheet1$]') AS A;
I pulled some examples of using a subquery pivot to build a temp table, but cannot get it to work.
IF OBJECT_ID('tempdb..#Pyr') IS NOT NULL
DROP TABLE #Pyr
GO
SELECT
vst_int_id,
[4981] AS Primary_Ins,
[4978] AS Secondary_Ins,
[code]....
The problems I am having are with the integer data being used to create temp table fields. The bracketed numbers on line 7-10 give me an invalid column name error each. In the 'FOR', I get another error "Incorrect syntax near 'FOR'. Expecting '(', or '.'.". The first integer in the "IN" gives me an "Incorrect syntax near '[4981]'. Expecting '(' or SELECT". I will post the definitions from another effort below.
CREATE TABLE #Pyr
(
vst_int_idINTEGERNOT NULL,
--ivo_int_idINTEGERNOT NULL,
--cur_pln_int_idINTEGERNULL,
--pyr_seq_noINTEGERNULL,
[code]....
SQL Server 2008 R2.
Has anyone been able to create a DTS that will create a new sheet within an existing Excel file. I want one Excel file with multiple tabs referencing the beginning of each week and growing. The name needs to be dynamic so as to not overwrite previous sheets (tabs) in the file.
I have a current ActiveX script that will move the data to a dynamic name (date), I just can't create the table (sheet/tab) to move the data to.
Any help would be appreciated.
-Mark
I am splitting data from SQL table and sending it to excel file but everytime i rerun the package ,it appends the existing data in excel file ..I tried using execute sql task with excel connection and write drop table `tablename` and then one more execute sql task with create table `tablename` (`Id` int ,`fname` varchar(100)) ....But it does not seem to work.
View 1 Replies View Relatedhow to create excel with sheetname and column name dynamically
View 4 Replies View RelatedI have a for each loop(ADO Enumerator) container which executes for each Advertiserid which is coming from database. In for each loop I have to create a new excel file with the advertiser name. So if the loop executes 7 times there should be seven excel spreadsheets with seven advertiser names.
How can i create an excel dynamically in the foreach loop container.
I'm trying to create an email report which gives a result of multiple results from multiple databases in a table format bt I'm trying to find out if there is a simple format I can use.Here is what I've done so far but I'm having troble getting into html and also with the database column:
EXEC msdb.dbo.sp_send_dbmail
@subject
= 'Job Summary',
@profile_name =
'SQL SMTP',
[code]....
My company has recently transitioned to a centralized Oracle database model.
For the sake of security, the "powers that be" have also denied any query ability to any central tables. They refuse to create views or any other open tables for people to query.
Instead, they provide a "tool" which people can use to download data - to Excel Spreadsheets.
In the past, before this "improvement" lots of users in the local plants were able to query the old system to bring data into spreadsheets for reports, analysis, etc.
Now the place is jammed to the hilt with linked spreadsheets - people do their "table joins" with linked cells and Excel VLookups (yuk). This is because the "powers that be" still demand that these reports, analysis, etc. be done.
I am trying to use SQL-DMO to create a table join between one of these Excel Data pulls and a MS SQL Server table in Excel so that I can join without VLookup. IS SQL-DMO the right way to go?
Has anyone done this? I think I am close, but I don't know how to use the SQL-DMO attached Excel table object I've created in a join. I can't see the object in MS Query. I am not adverse to doing the whole thing in VBA...
Here is another question. Most of these Data pulls using the "tool" (ball and chain, boat anchor) are done once a day or once a week. Would a better strategy be to create MS SQL server Tables that are dropped and re-written when the data is pulled into Excel? This would mean that the report worksheet does not have to import the Excel Data pull sheet to MS SQL when it updates its query.
Hi .,
Can any one guide me in exporting data from DB table to excel sheet .
Thanks,
vijay
I cant seem to reference columns within a linked Excel server
using MS SQL express
Code:
UPDATE dbo.Items
SET CCC=XL_SERVER.CCC
FROM XL_SERVER...sheet1$
WHERE BBB LIKE '7%'
i.e I want to update column CCC in my Items table from an Excel table column CCC where column BBB begins with 7
I have set up XL_SERVER correctly.
Help!
I need to make a script in SQL 2005 to import data from an Excel sheet into a SQL table.
I am using the wizard to import now.
Import from Excel 2000. First row of the excel sheet has column names.
Excel file name is: EXL.xls, sheet name is: Sheet1
Destination sql database name is: NM, table name is: Sht1
I use SQL Server Authentication to access the database.
User name: ABC and password: DEF
Database name is: DB
I am using the following setting when importing now:
- Delete rows in destination table
- Enable identity insert
i have to export the data from excel sheet to database table. for that i created linked server in sql 2000. after creating i get the SQLOLEDB error when i expand the linked server in enterprise manager.Can anyone help me in solving this issue.
View 5 Replies View RelatedHi All ,
I am new to Sql Server and all the DBA stuff. My boss wants me to export a folder containing excel sheet data to sql server table . To be more precise , he wants to automate the DTS process where instead of exporting each and individual worksheet to the DB , he wants me to maintain a separate folder of these sheets and export that folder.
Please do lemme know , how do I do this .
Thanks,
Aparna
Hi,
I am trying to find some document or code that will load an excel spreadsheet into an sqlserver database.
Can anyone please point me in the right direction.
MSSQL noob here. Sorry guys, but I really need some help.
I have a MSSQL (2000) database and a Products Table. Now, there was three columns used for pricing that I was not able to populate on the initial usage. So I exported the table as an Excel document, and got rid of everything except the UID (unique identifier column) the Name, and the Three columns, so that the customer could take their time in filling out the information.
Ok, so These are what are common to both the MSSQL database and the Excel sheet
column uid = unique id integer
column Name = text
column eBay = number
column PriceGrabber = number
column Amazon = number
So, any idea how I can update my Products Table with this?
Please HELP!
Hi,
i'm making a merge join on a excel source whit a Sql Server 2005 (OLE DB). In the SQL table i have a select over AdventureWorks.Sales.SalesOrderDetail , in a excel file i pasted the information of the AdventureWorks.Production.Product
when i'm doing the merge join of this tables,i hope obtain 121.000 registries, but when the work is finished I only have 27 registries.
So i must configure buffer properties in the merge?
JULIAN CASTIBLANCO P
Hi,I have a table as follows:HostId PurposeId====== =========1 31 51 62 12 3I'm looking for a query which will return:HostId Purposes====== ========1 3, 5, 62 1, 3Is it possible?Thanks in advance.
View 2 Replies View RelatedHow to read multiple excel sheets in same excel file with different table schema.
Basically need to load data into tables from these excel sheet.
So I know how to dynamically read multiple excel sheets in same excel file with same table schema and load into one table.
But how to do this dynamically for multiple excel sheet with different table schema and load into different tables?
Hi everybody,
I need to copy a database from one server (A) to another server (B). The database in Server A has only one datafile (which is about 42 Gb ...). I'd like to copy the database to Server B but I don't want only a datafile. I'd like to spread
on at least 2 datafiles. Exemple:
Server A Server B
C:SQLDATADB_DATA.MDF F:SQLDATADB_DATA_1.MDF
G:SQLDATADB_DATA_2.NDF
Thanks,
Alex.
Hi all,
Just can't figure this out. I'll try not to give a long-winded explanation (I hope), let's say this is the table, for example:
[Table1]
RowNumber | Value
1 | 4
2 | 6
3 | 3
4 |10
5 | 6
6 | 5
7 | 8
8 | 8
9 | 2
10 | 6
I want to write a query that creates a column whose values are similar to (ROW_NUMBER -1), and resets each time a value is present.
Using the above example, 6 will be the test value. So, I want to know how many rows it takes till 6 is repeated.
Output would be:
RowNumber | Value | Spread
1 | 4 | 1
2 | 6 | 0
3 | 3 | 1
4 |10 | 2
5 | 6 | 0
6 | 5 | 1
7 | 8 | 2
8 | 8 | 3
9 | 2 | 4
10 | 6 | 0
I think PARTITION BY can be used somewhere, with the ORDER BY on the [RowNumber] - but I'm just not sure on which ranking function to use, and how to reset the Ranking on the number 6 (above example).
Any help would be appreciated. This has been a mind-teaser for me, and I give
Thanks,
Denvas
How can i import a excel spread sheet information to a table?
Regards
Karen
I want to do a SQL sentence that saves the results from the select in a .txt file.
I get the results as a long list on the screen, and it repeats the headers several times, so it takes quite some time to copy the results out of there. It would be better if the result was put as a list directly in to a text file.
Anyone know how I can do that?
I have an issue where I have multiple rows of data and I need to reduce a dollar amount by a fixed maximum. I am going to throw some code in here to give a rudimentary idea of the data and what the final result should be.
declare @tbl table
(LineNum int,
Code varchar(2),
Amt money,
MaxAmt money
[Code] ....
I need to run an update so that the result of the following query:
select LineNum, Code, Amt, MaxAmt from
@tblLooks like this:
LineNum Code Amt MaxAmt
----------- ---- --------------------- ---------------------
1 AA 10.00 50.00
2 AA 20.00 50.00
3 AA 20.00 50.00
(3 row(s) affected)
I have tried cursors but got unexpected results or the MaxAmt always defaulted to the original even if I updated it. This seems like a simple problem but I have been banging my head against the wall for 2 days now. I've written some pretty complicated updates with less effort than this and I must have some mental block that is keeping me from figuring this out.
I have 2 Table
Authors
ID Name
1 Clint
2 Voke
Books
BookID ID BookName Price
1 1 Book1 10
2 1 Boo21 12
3 2 Book3 6
4 1 Book4 13
5 1 Book5 2
Now I want to List All Authors and only show Most Expensive book Name of each Author.
So I need this Fields :ID,Name,BookName,BookID,Price.
How could I Write SQL query For It (I want to show results in DB Without Using SP).
I want to Create NEw Views Which Shows my required Results.
thanks,
*Groan* Sorry. One more.
ERD above is what my database looks like (ignore it's in Access, database is in SQL 2005)
I have this code:
SELECT orderID, orderAmount = SUM(customerID)/customerID FROM orders
GROUP BY orderID
-- When Sum of the customer ID (and then) divided by the customer ID > 1
HAVING orderAmount > 1
which doesn't work because I never did find out how you make a column in the results to output your maths in.
orderAmount doesn't exist as a column in the database, but for this query it should show only those customers who have ordered more than once with the company.
Thanks again for any replies.