I was able to create a view and convert it to excel. Now I want to it to schedule it for everyday and then email the excel file as an attachment to couple of people.
Would SSRS be an option? where I can create a report of the view and schedule it?
Does any know the process I need to follow?
Do I have to uses SSIS ? and then set it up as SQl server job?
Here below is the perfect query i made which is working fine and giving me the sql output but just only need is how to convert to excel and automate the job scheduling so that it run on everyday and send the mail with attachment .
SELECT DN, cn, displayName, mail, objectClass, sAMAccountName, Company, givenName, sn
FROM
(
SELECT DN, cn, displayName, mail, objectClass, sAMAccountName, Company, givenName, sn, 1 [ordering] FROM alpha.dbo.DCADFeed
where sAMAccountName collate SQL_Latin1_General_CP1_CI_AS in
[Code] ....
I am trying to convert the @runtime to hh/mm/ss format as it's currently printing milliseconds.
Declare @runtime varchar(100)
Select @runtime = sum(run_duration)
From msdb.dbo.sysjobs j
INNER JOIN msdb.dbo.sysjobhistory h ON j.job_id = h.job_id
where j.name = 'Accounts_From_App03'
and run_date = CONVERT(varchar(8), getdate(), 112)
Declare @Body varchar(100)
Set @Body = 'The Job completed successfully. Duration:' + @runtime
exec master.dbo.sp_SQLNotify 'App02@email.com', 'team@email.com', 'Scheduled Job Success' ,@Body
Exec Prcoess task with source : ftp
destination :ftpunzip
work directory ftpunzip
executable : c:Program FilesWinZip
i am using expressing.
It is doing the unzip but getting this error
package process on server server1 has failed within the Task 'Unzip Files' with the following errors:
>
> File/Process "WZUNZIP.EXE" does not exist in directory "c:Program FilesWinZip".
This is the error message i am getting it
How do I convert (n)(var)char to time? I tried several ways including a simple:
SELECT CONVERT(time,'143825',108)But it always gives me this error:
Conversion failed when converting date and/or time from character string.
wmic logicaldisk where "DeviceID='C:'" get FreeSpace /format:value
FreeSpace=231379468288
Why this won't work. POST_DT is nvarchar(50). I know it should be datetime but, I have no control over it. That's why I'm creating a temp table. But, I want to only insert the most recent invoice. There is code to create test data at the end.
create table #FSC589
(
GRP__2 char(1),
INV_NUM char(8),
MRN varchar(10),
POST_DT datetime
[Code] .....
I want to convert table into following xml format. Sample two records is given below.
<Row>
<Cell ss:StyleID="s22">
<Data ss:Type="String">XXXXXXXXXXXXX</Data>
</Cell>
<Cell ss:StyleID="s22">
<Data ss:Type="String">64856</Data>
[Code] ....
SELECT DISTINCTROW "01C" AS dummy, Buildings.BuildingNumber,UCASE(Buildings.BuildingName) AS BuildingName,Buildings.MasterPlanCode, Buildings.UniformBuildingCode,Buildings.FunctionalCategoryCode, Buildings.OwnershipCode,Buildings.ConditionCode, Format$([BasicGrossArea],"0000000") ASdBasicGrossArea, Format$([CoveredUnenclosedGrossArea],"0000000") ASdCoveredUnenclosedGrossArea,IIf(Month([DateOccupancy])>9,Month([DateOccupancy]),"0" &Month([DateOccupancy])) & Year([DateOccupancy]) AS dDateOccupancy,Buildings.YearConstructed, Format$([NumberLevels],"00") ASdNumberLevels, Format$([UnrelatedGrossArea],"0000000") ASdUnrelatedGrossArea, Buildings.YearLatestImprovement,UCASE(Buildings.Address) AS Address, Buildings.CityCode,CityCodes.CountyCode, Format$([Circulation],"0000000") AS dCirculation,Format$([PublicToiletArea],"0000000") AS dPublicToiletArea,Format$([Mechanical],"0000000") AS dMechanical,Format$([Custodial],"0000000") AS dCustodialFROM CityCodes INNER JOIN Buildings ON CityCodes.CityCode =Buildings.CityCodeORDER BY "01C", Buildings.BuildingNumber, Buildings.BuildingName;Please if anyone can help me in Converting the above given Access Queryto Sql Server. I don't know which function to use for format$, IIF. Iwould really appreciate your suggestions.Thanks,
View 8 Replies View RelatedHello does anybody know how to convert an SQL Server file into a DBF or an Excel sheet? I need to do this programmitically with C# or python. Preferrably C#. Also, the converted file must be placed into a different folder. Thanks.
View 4 Replies View RelatedWe have setup a report server where a db's tables are refreshed from a backup daily.
All SP and views are created in a different DB but look at the daily refresh tables
Can I setup the DB that creates the view to point at the DB with the tables so when I say new view it looks at the tables in that DB ...
I have a view made up of a few base tables and another view. I have created an INSTEAD OF trigger on this view, but it doesn't seem to fire whenever a new record shows in the view. The purpose of the trigger is to insert a sister record in a table whenever a new record shown in the view. Here's the catch, the table that the trigger is supposed to insert into is not a base table within the view and the view is not an updatable view. My question is... Do INSTEAD OF triggers only affect the base table(s) within the view and does the view itself have to be an updatable view?
View 3 Replies View RelatedThere are 2 tables
1. Table Name : TRNNUM Field Name : TRNID
2. Table Name : TRNPRD Field Name : TRNID, PRDID
Sample of tables
Table TRNNUM
TRNID
-----
1234
2565
3458
Table TRNPRD
TRNID PRDID
----- -----
1234 AA
1234 BB
1234 CC
2565 CC
2565 EE
2565 FF
2565 HH
3458 AA
3458 BB
Desired output is
TempTable
TRNID PRDID PRDSEQ
----- ----- ------
1234 AA START
1234 BB START:AA
1234 CC START:AA:BB
2565 CC START
2565 EE START:CC
2565 FF START:CC:EE
2565 HH START:CC:EE:FF
3458 AA START
3458 BB START:AA
Can I view the log cache size in SQL Server memory any DMV's which states that.
View 9 Replies View RelatedA recent SharePoint upgrade has rendered several views obsolete. I am redefining them so that our upper level executive reports show valid data.(yes, I know that doing anything to sharepoint could cause MS to deny support, having said that, this is something I've inherited and need to fix, pronto) The old view was created like so:
USE [AHMC]
GO
/****** Object: View [dbo].[vwSurgicalVolumes] Script Date: 09/04/2015 09:28:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[vwSurgicalVolumes] AS
SELECT
[code]....
As I said, this view is used in a report showing surgical minutes.SharePoint is now on a new server, which is linked differently (distributed?) I've used OPENQUERY to get my 'new' query to work;
SELECT *
FROM OPENQUERY ([PORTALWEBDB], 'SELECT
--AllLists
AL.tp_ID AS ALtpID
,AL.tp_WebID as altpwebid
,AL.tp_Title AS ALTitle
[code]....
My data (ie surgical minutes, etc) seems to be in the XML column, AUD.tp_ColumnSet . So I need to parse it out and convert it to INT to maintain consistency with the previous view. How do I do this within the context of the view definition?Here is a representation of the new and old view data copied to excel :
<datetime1>2014-08-14T04:00:00</datetime1><float1>2.000000000000000e+000</float1><float2>4.190000000000000e+002</float2><float3>1.600000000000000e+001</float3><float4>8.110000000000000e+002</float4><sql_variant1 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:sqltypes="http://schemas.microsoft.com/sqlserver/2004/sqltypes"
[Code] ....
can't format it to make it look decent. InHouseCases =2, InHouseMinutes=419, OutPatientCases =16, OutPatientMinutes=1230. This corresponds to the new data I can see in the XML column; 2.000000000000000e+000 is indeed 2 and 4.190000000000000e_002 is indeed 419.
Here's the scenario. We tried to create a full text indexing on a view, unfortunately the view was created based on Left outer join. Hence we were pushed to do the following.
We planned to create a table based on the data that is rendered in this view(this view is formed by combining 14 tables.) . Finally we were able to create a full text indexing on this new table. For simplicity let us called this new table as tableA.
Now the problem is how can we update (if any data is changed on one of the concrete tables of the view) or insert (when new data is inserted in one of the underlying tables of the view) the respective data into this new table - tableA which was created based on this view.
In other words, there should be a sync between the view and tableA. Is it possible to achieve this ???
View is formed based on different tables (not based on tableA)?
We have two databases with same schema and tables (same table names, basically main DB and a copy of the main DB). following is example of table names from 2 DBs.
CREATE TABLE #SourceDatabase (SourceColumn1 VARCHAR(50))
INSERT INTO #SourceDatabase VALUES('TABLE1') , ('TABLE2'),('TABLE3') , ('TABLE4'),('TABLE5') , ('TABLE6')
SELECT * FROM #SourceDatabase
DROP TABLE #SourceDatabase
CREATE TABLE #ArchiveDatabase (SourceColumn2 VARCHAR(50))
INSERT INTO #ArchiveDatabase VALUES('TABLE1') , ('TABLE2'),('TABLE3') , ('TABLE4'),('TABLE5') , ('TABLE6')
SELECT * FROM #ArchiveDatabase
DROP TABLE #ArchiveDatabase
We need a T_SQL statement that can create one view for each table from both the databases(assuming both databases have same number of tables and same table names). so that we can run the T_SQL on a thrid database and the third DB has all the views (one view for each table from the 2 DBs). and the name of the view should be same as the tables name. and all 3 DBs are on the same server.
the 2 temp tables are just examples, DBs have around 1700 tables each. so we ned something like following for each table.
CREATE VIEW DBO.TABLE1 AS SELECT * FROM [SourceDatabase].[dbo].[TABLE1] UNION ALL SELECT * FROM [ArchiveDatabase].[dbo].[TABLE1]
CREATE VIEW DBO.TABLE2 AS SELECT * FROM [SourceDatabase].[dbo].[TABLE2] UNION ALL SELECT * FROM [ArchiveDatabase].[dbo].[TABLE2]
CREATE VIEW DBO.TABLE3 AS SELECT * FROM [SourceDatabase].[dbo].[TABLE3] UNION ALL SELECT * FROM [ArchiveDatabase].[dbo].[TABLE3]
CREATE VIEW DBO.TABLE4 AS SELECT * FROM [SourceDatabase].[dbo].[TABLE4] UNION ALL SELECT * FROM [ArchiveDatabase].[dbo].[TABLE4]
CREATE VIEW DBO.TABLE5 AS SELECT * FROM [SourceDatabase].[dbo].[TABLE5] UNION ALL SELECT * FROM [ArchiveDatabase].[dbo].[TABLE5]
CREATE VIEW DBO.TABLE6 AS SELECT * FROM [SourceDatabase].[dbo].[TABLE6] UNION ALL SELECT * FROM [ArchiveDatabase].[dbo].[TABLE6]
I gave a user all required permission to view the SSRS report. User is able to select from the dropdown list but unable to view the data, It is showing a blank screen.
View 9 Replies View RelatedI have one view which is based on couple of tables. Here is the definition of view. Which are the options i can use to optimize the view for better performance. This is one of the view which causing issue on database.
CREATE VIEW [dbo].[V_Reqs]
WITH SCHEMABINDING
AS
SELECT purchase.Req.RequisitionID, purchase.Req.StatusCode AS Expr2, purchase.Req.CollectionDateTime,
purchase.Req.ReportDateTime, purchase.Req.ReceivedDateTime, purchase.Req.PatientName, purchase.Req.AddressOne,
purchase.Req.AddressTwo, purchase.Req.City, purchase.Req.PostalCode, purchase.Req.PhoneNumber,
[code]....
The below would work if all of the values in A.Value were numbers but they are not. So I need to restrict the view to only look at the following measures but still show all the other row.
WHERE [Measure] IN ('RTT-01','RTT-04','RTT-07')
SELECT
M.[Description]
,A.*
,M.Threshold
,M.[Threshold Direction]
[Code] ....
Is there any way that I can create a select statement in the case when to only look for them measures that I know contain numbers?
I'm creating Indexed view by JOINING multiple tables and trying to create FULL TEXT search index. Unique column is generated by concatenating to ID columns from different table. I can successfully able to create unique index however when trying to create FULLTEXT INDEX getting below error.
"A full-text search key must be a unique, non-nullable, single-column index which is not offline, is not defined on a non-deterministic or imprecise nonpersisted computed column, does not have a filter, and has maximum size of 900 bytes. Choose another index for the full-text key."
The message clearly says the column should be single-column index, non-deterministic.
I have a request to export some table data to excel and the "notes" column (varchar 255) contains multiple lines separated by CR/LF. when I export to excel, the first record with CR/LF messes up the column alignment in excel, throwing off the format from that point on. how can i export to excel so that it preserves these CR/LF. or if not, how can I remove these characters so that excel can handle it?
See attached example
Using below statement to export a table from sql server 2008 to EXCEL 2010
Insert into Openrowset
('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:ExportXLS.xlsx;' ,
'SELECT * FROM [employees$]')
SELECT name,id,group,agency FROM dbo.employees
getting below ERROR
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.
below changes also done.
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ad Hoc Distributed Queries', 1;
GO
RECONFIGURE;
GO
I have a requirement of generating an "Sales Report" excel file and mailing that file to a particular email id. I have generated the excel file through bcp command and mail is working fine.
However formatting of excel file is required by end user for eg. Cells to be merged and borders to be given for cells for which i think bcp command will not work.
I have an sql server table which serves as a criteria table for my sql server query.
i wish to update the sql server table from the excel worksheet. The intention is to allow the end user to change the values in a specific column in the sql server table via excel.
The table in question has the following fields
SELECT
[Cluster]
,[Max_Break_btw]
,[RefD_Max_Break]
,[DischD_Max_Break]
,[MaxReviewPeriods]
FROM [databseName].[dbo].[SpellClusterAssum]
I will like to change / update the values in the "[Max_Break_btw]" column.
While importing data from Excel source , some column is getting null value even though excel column has value.To Resolve the issue we tried with
HKEY_LOCAL_MACHINESOFTWAREWow6432NodeMicrosoftOffice14.0Access Connectivity EngineEnginesExcel
1.Change the Value of the Row TypeGuessRows from 8 (Default value) to 0 and ImportMixedType = text
• xls
HKEY_LOCAL_MACHINESOFTWAREMicrosoftJet4.0EnginesExcel
1.Change the Value of the Row TypeGuessRows from 8 (Default value) to 0 and ImportMixedType = text
the connection string of the excel
UPPER(REVERSE(SUBSTRING( REVERSE(@[User::VarInputExcelFile]), 1, 5) ) ) == ".XLSX" ? "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + @[User::VarInputExcelFile] + ";Extended Properties="Excel 12.0;HDR=Yes;IMEX=1";":"Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=" + @[User::VarInputExcelFile] + ";Extended Properties="EXCEL 8.0;HDR=Yes;IMEX=1";"
by doing the above setting also , the column is coming as null from excel source even though there is data in excel.
The developers in our shop have a need to explicitly grant view definition permissions to themselves on stored procedures they create in their development databases. They have dbo level permissions in these databases and although they can explicitly grant view definition permissions to other developers in the same database, they are unable to do so for themselves. When they attempt this, it appears that they are successful but when they check the stored procedure afterwards the permission is not there for themselves.
While this does not cause an issue in development, the intention is for these view definition permissions to be carried forward to the test and production databases where they only have datareader permissions.
When these stored procedures are scripted out by the dba to move to Test and Production the view definition permissions are not scripted out for the developer in question.
Is there a way that a developer with dbo rights in a database can explicitly grant themselves view definition permissions on a stored procedure they create as dbo?
I have an excel file that is stored in a sharepoint document library. I am trying to use SSIS to import it into a SQL database. I use the excel connection manager with a sharepoint UNC path. When I run it from BIDS, it runs successfully. When I run it from a job, I get an error
"It is already opened exclusively by another user, or you need permission to view and write its data"
It is definitely not open by anyone else, and I have full permissions to the file. ALSO, the SQL Agent and Service acct which the job runs under, has full permissions to the file. I have tried running it under a proxy account with my user account, but it fails with the same message. Further, I can run a DIR command from a command prompt to list the sharepoint directory contents successfully, but when I run the same command from SSMS using xp_cmdshell, it fails with access denied. Again, the SQL Service acct has full rights to the sharepoint site.
I notice when i browse to the sharepoint document library and try to open the folder with "Open with Windows Explorer", it always asks me to login and I'm thinking that is related to the problem I am having - that it doesn't automatically pick up the windows authentication.
Declare@QRYvarchar(8000)
Select@QRY='bcp "Select COL1, COl2 From table(nolock)" queryout "D: est.xls" -c -T -S ' + convert(varchar(20), serverproperty('servername'))
Select@QRY
Execmaster..xp_cmdshell@qry
The file test.xls is getting generated but when opening getting the message
"File you are trying to open is in a different format than specified by the file extension"
Is there any property that can be set to avoid this message?
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 RelatedI have an .xlsx file where I need to import the data into a table. If there is not a way to do this, is there a way to import either a tab del file or different type of .csv file into the database?
Do not want to use the SSIS or import feature from SQL2008 as I tried to save the steps and running it wont work either.
Am not able to export more than 10 lakh records to excel sheet(2007 xlsx). I'll get sucess message but all data will not get copy to excel sheet .I have tried through import wizard in sql server and also directly copy and paste to excel sheet.
View 9 Replies View RelatedI have a report that is scheduled to run a once a week. This works fine. But now I would like this report to be saved as an Excel file automatically when it runs. how / where do I do this?
View 0 Replies View Related