SQL Server 2008 :: Export Information With Unique Numerator Per Line
Oct 27, 2015
I have 2 tables:
1 is with only one field: numerator
2ed is (for example) sales information.
I need to export (using SP) the sales information with unique numerator per line. So,
I need to get the value from table 1 (numerator),
I need to promote the numerator by 1 for every line, and
I need to update the first table (numerator) (as i need to keep it updated for the next run).
At the next run I need to get again the updated numerator from the first table, and so on...
Hi I need to Install the SQL server 2008 express with Instance name and Sa password parameter in silent mode . I tried couple of command line arguments for this. but It didn't work, I got the setup failure error message "The setting 'QB' specified is not recognized.Error code 0x84B40003." Can any one help me with this?
I've a excel spreadsheet with 650 records with unique PONumbers. I need to pull data from SQL server based on the PONumbers. I don't want to run select statement 650 times. How do I retrieve the records in efficient way?
As you can I have a uniqueidentifier (UniqueId) column which I populate with NewID() I'm trying to return this as I need it for other functionality of the website but I can't figure out how I can get it after the insert has completed?
Requirements • ALPHANUMERIC FORMAT – > AA00AA00………..ZZ99ZZ99 Last 8 bytes will alternate between 2 byte alpha/2 byte numeric • Generate from Alphabets – A through Z Numbers -0 to 9 • Generate Unique Sequence (No Duplicates). • Must Eliminate letters I and O
Output Expected • AA00AA00………..ZZ99ZZ99 • Using 24 alphabets & 10 digits , 24*24*10*10*24*24 = 3 317 760 000 records
Below is my Sql Function -
CREATE function [dbo].[SequenceComplexNEW] ( @Id BIGINT ) Returns char(8)
Select p.Id [SenderId], p.Username, up.PhotoId, CASE WHEN mr.ReadDate is null then 1 -- New message ELSE 0 -- Message has been read END AS NewMessage, p.LastLoggedIn, p.LoggedIn
[Code] ....
The above query returns me all messages (inbox functionality) that have been sent to mr.ReceipientId, the issue I have is when I send another email to the recipient the readdate field will be null, and the other emails linked to the recipient which have also been sent via me will have a readdate date. This causes duplicate rows to appear due to the case statement, I'm trying to figure out if / how it is possible to only display the one row per conversation and set newmessage to 1 if there is an un-read message otherwise show 0 ?
I'm in the process of importing a series of flat files into SQL Server. I'm using a ~ to separate the columns and the row delimiter is {CR}{LF}. One of the files has a field that contains the CRLF combination in a few places so that field is split over several rows. This is readily visible when I look at the flat file. However, when I'm importing the file, the Import and Export wizard seems to ignore them and import the files as they should with one row per record.
A common partitioning scenario is when the partition column has the same value for every record in the partition, as opposed to a range of values. Am I the only person who wonders why there isn't an option to automatically partition a table based on the unique values of the partition column? Instead of defining a partition function with constants, you ought to be able to just give it the column and be done. This would be particularly valuable for tables partitioned on a weekly or monthly date; when new data is added it could simply create a new partition if one doesn't already exist.
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?
And I always know the root ID from the first record on "table" dummy (generated with a common table expression), in this case it's ID 1, but from here, how to process this for any level of depth ?
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
Any way to bulk export / import TDE Certs? I've got a bunch of databases that need to be moved to another system. Just about every database is using TDE and was wondering if there was a way to move these certs in a bulk fashion. I've got SQL and Powershell scripts to backup and restore multiple databases, but won't do me any good without the certs.
One of my current responsibilities is to export data to 3rd party vendors. Each export can contain many csv files. The exports are all different in terms of what data is being sent.
The way I have it currently setup is each file that needs to be created is a view. An SSIS package gets the data from the view, writes to CSV, and then sftp to 3rd party vendor. This seemed like a good idea at first because the columns are static but the calculations might change. So all I have to do is ALTER VIEW and I don't have to change anything in the package.
Is there a better way of doing this? I was curious to see what other people are doing. What makes it challenging is that all the exports are so different. If they were similar I could have created generic views that cover all the exports instead of each export having its own view. Eventually I'm going to have 100's of views.
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.
I have a table which has few columns as Numeric value. I need to export the output into a csv file as a report with column headers. I have used bcp command. Here column name and Column Header name is the same. The BCP query which i have used is Below
Not seeing the Review Data Type Mapping Screen in SQL Server Import and Export Wizard?
Is there only a certain version where that screen shows up?
I am trying to import data from an MS Access application to SQL Server and all of the connections are good, but some of the data isn't and if I let it migrate using this tool it crashes on the bad data and there is no data that migrates. The Review Data Type Mapping screen will allow me to bypass the records in error and load the rest. however, I can;t do that if I cannot see the screen.
Hi there, I am trying to get a report to export to a pdf or xls file at a specific location using a command line action. I got the example for Publishing sample reports to work at the following site: http://msdn2.microsoft.com/en-us/library/ms160854.aspx. I am trying to modify this example to get it to put a file out to a directory.
The code I am using in my .rss file is:
CODE Public Sub Main()
'setting the credentials to the machine that this application is running on rs.Credentials = System.Net.CredentialCache.DefaultCredentials
Dim ResultStream() As Byte Dim filename As String = "Test.pdf"
'rendering the report as a pdf ResultStream = ReportExecutionService.Render("http://MyServer/reportserver?/AdventureWorks+Sample+Reports/Company+Sales", "PDF", Nothing, "<DeviceInfo><StreamRoot>/RSWebServiceXS/</StreamRoot></DeviceInfo>", Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing)
'creating a file to hold the stream Dim stream As FileStream = File.Create("C:whatever" + filename)
'writing the stream to the file stream.Write(ResultStream, 0, ResultStream.Length) stream.Close()
End Sub The command line is:
CODE C:Program FilesMicrosoft SQL Server90SamplesReporting ServicesScript Sampl es>rs -i ExportRenderTest.rss -s http://dbasql01/reportserver I am getting the error: C:Documents and Settingsdan.evansLocal SettingsTempc6xhwdrx.1.vb(24) : error BC30456: 'Render' is not a member of 'Microsoft.SqlServer.ReportingServices2005.ReportingService2005'.
Has anyone had any experience doing this sort of thing?
My holy grail is to create an ssis package that accepts some arguments (source rs report path, destination path, report parms, destination file format, etc...) that I can use over and over again to manage some automated report distribution.
Thanks in advance for any help you can give!!! Thanks, Dan
I created a package that exports contents from a table to a flat file but all my records are being displayed in a single record. where do i configure it to where each record has its own line. the columns in the flat file are fixed.
' File: Test.rss Dim format as string = "Excel" Dim fileName as String = "C:Export2.xls" Dim reportPath as String = "/TimeByEmployee"
Public Sub Main()
' Prepare Render arguments Dim historyID as string = Nothing Dim deviceInfo as string = Nothing Dim showHide as string = Nothing Dim results() as Byte Dim encoding as string Dim mimeType as string = "ms-excel" Dim warnings() AS Warning = Nothing Dim reportHistoryParameters() As ParameterValue = Nothing Dim streamIDs() as string = Nothing rs.Credentials = System.Net.CredentialCache.DefaultCredentials
Dim parameters(3) As ParameterValue parameters(0) = New ParameterValue() parameters(0).Name = "Employee" parameters(0).Value = " " parameters(1) = New ParameterValue() parameters(1).Name = "Departments" parameters(1).Value = " " parameters(2) = New ParameterValue() parameters(2).Name = "Week Number" parameters(2).Value = WeekNumber results = rs.Render(reportPath, format, _ Nothing, Nothing, parameters, _ Nothing, Nothing, encoding, mimeType, _ reportHistoryParameters, warnings, streamIDs)
' Open a file stream and write out the report Dim stream As FileStream = File.OpenWrite(fileName) stream.Write(results, 0, results.Length) stream.Close()
' File: Test.rss Dim format as string = "Excel" Dim fileName as String = "C:Export2.xls" Dim reportPath as String = "/TimeByEmployee"
Public Sub Main()
' Prepare Render arguments Dim historyID as string = Nothing Dim deviceInfo as string = Nothing Dim showHide as string = Nothing Dim results() as Byte Dim encoding as string Dim mimeType as string = "ms-excel" Dim warnings() AS Warning = Nothing Dim reportHistoryParameters() As ParameterValue = Nothing Dim streamIDs() as string = Nothing rs.Credentials = System.Net.CredentialCache.DefaultCredentials
Dim parameters(3) As ParameterValue parameters(0) = New ParameterValue() parameters(0).Name = "Employee" parameters(0).Value = " " parameters(1) = New ParameterValue() parameters(1).Name = "Departments" parameters(1).Value = " " parameters(2) = New ParameterValue() parameters(2).Name = "Week Number" parameters(2).Value = WeekNumber results = rs.Render(reportPath, format, _ Nothing, Nothing, parameters, _ Nothing, Nothing, encoding, mimeType, _ reportHistoryParameters, warnings, streamIDs)
' Open a file stream and write out the report Dim stream As FileStream = File.OpenWrite(fileName) stream.Write(results, 0, results.Length) stream.Close()
Msg 2601, Level 14, State 1, Procedure DFP_report_load, Line 161 Cannot insert duplicate key row in object 'dbo.DFP_Reports_History' with unique index 'ix_report_history_creative_id'.
The duplicate key value is (40736326382, 1, 2015-07-03, 67618862, 355324). Msg 3621, Level 0, State 0, Procedure DFP_report_load, Line 161
The statement has been terminated.
Exception in Task: Cannot insert duplicate key row in object 'dbo.DFP_Reports_History' with unique index 'ix_report_history_creative_id'. The duplicate key value is (40736326382, 1, 2015-07-03, 67618862, 355324).
I print 4x1 inch labels, anywhere from a few to a few thousand. It's all done from an asp.net page with a 9.0 report viewer in local processing mode.
108 Labels = 37MB or so.... this is totally unacceptable for something that when zipped becomes 1.3mb. I thought version 9.0 was supposed to have PDF compression... Is there some kind of setting I'm not seeing any documentation for somwhere? how do i enable it?
This better not just be a server processing mode feature like the print button again... I've already taken plenty of heat from the bosses just for that alone. Try explaining the difference between a local report and a server report to your boss and then try to come up with a real world explanation as to why he can print some reports in some programs and is forced to export and printer them from adobe in other programs... please don't let me die have mercy.
We have recently upgraded to SP1 of SSRS 2008. As a result, when we export a blank report to CSV, we now get a line of commas below the headings. Or found a way to not include the commas?
We're migrating from SSRS 2005 to SSRS 2008. We use itextsharp (a pdf processing library) to merge different PDF outputs into one large PDF file. This was working brilliantly in SSRS 2005, but in SSRS 2008, itextsharp *can* open and parse the PDF, but itext sees the "content" section of the PDF pages as blank. So, the resulting composite PDF ends up being a series of blank pages. Acrobat 9 and a couple other PDF tools (tested linux Evince and win GIMP) seem to be able to open the SSRS 2008-generated PDF fine, but the java-version of itext, and the .net version of itext (itextsharp) see nothing but blanks.So, I realize this points to a problem with itext, but I'm curious if merging PDFS outputed from SSRS 2008, and what PDF tools/libraries they are using.
I have a request from the customer to export a report to Word as a read-only document. I am restricted to out-of-the-box SSRS 2008, so I cannot write any custom export functionality.
The customer has the ability to export as PDF, and I suggested that, but before I tell them there's no easy solution, I wanted to get input from other Reporting Services folks.
