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...
View 3 Replies
ADVERTISEMENT
Aug 17, 2015
Is it possible to change any line breaks or newlines for a string ...
In a select statement in a store procedure, in this way I can reproduce text in a label on a webpage complete with line breaks ....
View 4 Replies
View Related
Sep 23, 2015
How do I execute below scripts using CMD:
Please Note: SQL server 2008r2, default instance with window Authentication.
1. Select * from Adventurewoks2008.Person.Person where Id = 5
2. Exec usp_getemployee (with parameter Manager_id = 100)
3. I have a folder named 'MyFolder' which contains 3 store procedures (usp_A, usp_B and usp_C).
How do I execute all 3 using CMD against AdventureWorks database.
View 9 Replies
View Related
Mar 25, 2008
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?
These are the commands I tried.
1) sqlexpr.exe /qb instancename="MACHINENAMESQLEXPRESS2008" SCCCHECKLEVEL=IncompatibleComponents:1;MDAC25Version:0 ERRORREPORTING=1 SQLAUTOSTART=1 SAPWD="PWD" SECURITYMODE=SQL DISABLENETWORKPROTOCOLS=0"
2)SQLexpr.exe /qb /ACTION=Install /FEATURES=SQL /instancename="MACHINENAMESQLEXPRESS2008" SAPWD="PWD" SECURITYMODE=SQL
Thanks in advance.
Regards,
Nav.
View 1 Replies
View Related
May 14, 2015
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?
View 9 Replies
View Related
Oct 30, 2015
Give a user table ‘MyTable’. How to know whether the table contains a non-unique clustered index by using SQL query?
View 2 Replies
View Related
Apr 3, 2015
I have the following insert statement:
INSERT INTO [User].User_Profile
(UniqueId, Username, EmailAddress,
Password, BirthDay, BirthMonth,
BirthYear, Age, AccountType, DateCreated,
DeletedDate, DeletedReason, ProfileStatus)
VALUES
(NEWID(), @Username, @EmailAddress,
@Password, @BirthDay, @BirthMonth,
@BirthYeat, @Age, 1, SYSDATETIME(),
null, null, 2)
SELECT @@IDENTITY
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?
View 3 Replies
View Related
Apr 20, 2015
GENERATE 8 CHARACTER ALPHANUMERIC SEQUENCES
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)
[Code] .....
View 9 Replies
View Related
Sep 30, 2015
Table Name: Denominator
Already has the following constraint:
PK_Denominatorclustered, unique, primary key located on PRIMARY DenominatorID
How can I add a unique key that will cover the 3 fields --> MemberID,MeasureID,TimePeriodID
I also want to know whether we can include the " WITH ( IGNORE_DUP_KEY=ON ) "
View 3 Replies
View Related
Jul 26, 2015
I have the following query:
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 ?
So instead of showing this:
2Person102015-07-26 17:07:24.9370
2Person112015-07-26 17:07:24.9370
I'm trying to get it to look like this:
2Person112015-07-26 17:07:24.9370
Or if we have no new mail (read date is not null) then it will appear like this, and I can confirm when there is no new mail it works as expected.
2Person102015-07-26 17:07:24.9370
View 1 Replies
View Related
Sep 22, 2015
I have a field I am trying to bring into a SQL statement
,ISNULL(Convert(nvarchar(50),OPP1.OriginatingLeadId),'') AS 'OriginatingLeadId'
I get this error message
Conversion failed when converting from a character string to uniqueidentifier.
the field specs' originatingleadid is attached ....
View 9 Replies
View Related
May 28, 2015
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.
View 0 Replies
View Related
Jun 17, 2015
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.
View 4 Replies
View Related
Jan 30, 2015
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
View 3 Replies
View Related
Feb 23, 2015
I am trying to output the hierarchical data of a tree to xml format.
I can query the data from the tables into a friendly format like this:
create table dummy
(
id int,
childname nvarchar(max),
parentid int,
parentname nvarchar(max)
[Code] ....
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 ?
View 6 Replies
View Related
Jun 9, 2015
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
View 2 Replies
View Related
Oct 14, 2015
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.
View 0 Replies
View Related
Oct 7, 2015
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.
View 9 Replies
View Related
Oct 21, 2013
Aim –To produce a unique line by month for the FDMSAccountNo
I have created the following query (see below) which produces the following result set
FDMSAccountNo AvgValCard_TypePence_Percentfee_wholesale_date BegMonthDate
8780000018820.012900000CreditPercent2009-07-082009-07-01 00:00:00.000
8780000018820.015700000CreditPercent2011-02-242011-02-01 00:00:00.000
8780000018820.014700000CreditPercent
2011-02-252011-02-01 00:00:00.000
SELECT
[FDMSAccountNo],
AVG([fee_retail_amt]*1.0) AS AvgVal,
Card_Type,
[Pence_Percent],
[fee_wholesale_date],
DATEADD(mm,DATEDIFF(mm,0,[fee_wholesale_date]),0) AS BegMonthDate
[Code] ....
Desired results
FDMSAccountNoAvgValCard_TypePence_Percentfee_wholesale_date
8780000018820.012900000CreditPercent2009-07-08
8780000018820.012900000CreditPercent2009-08-08
8780000018820.012900000CreditPercent2009-09-08
8780000018820.012900000CreditPercent2009-10-08
8780000018820.012900000CreditPercent2009-11-08
[Code] ....
View 1 Replies
View Related
May 26, 2015
I have table which consists of images. I am trying to export those images to a folder.
declare @path varchar(100)= 'c:images'
SELECT [PICTURE]
,@path+[PICTURE] AS Path
FROM [A].[dbo].[PICTURE]
I am getting this error: The data types varchar and varbinary(max) are incompatible in the add operator.
View 1 Replies
View Related
Aug 28, 2015
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?
View 6 Replies
View Related
Jan 9, 2013
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 Related
Aug 20, 2015
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
bcp "SelectCompanyCode,MonthId,ActualityCode,CompanyCounterpartCode,LocalProductCode,LocalCustomerBillTo,
DestinationCountryCode,LocalCostCenterCode,LocalGLAccountCode,LocalProjectCode,TXCurrencyCode,
TXAmountYTD,LocalCurrencyCode,LocalAmountYTD,SourceSystemFromTable(nolock)" queryout D: est.csv -c -t ";" -r -S -T
Is there any way to export bcp with header or any other command to generate the csv file.
View 1 Replies
View Related
Jun 5, 2015
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.
View 9 Replies
View Related
May 7, 2007
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
View 1 Replies
View Related
Jun 19, 2007
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.
View 4 Replies
View Related
Oct 26, 2007
' 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()
End Sub
'End of script
here is how i run it
rs -i RunReport.rss -s http://localhost/reportserver -v WeekNumber=90 -e mgmt2000
this is the error i get:
The value of parameter 'Parameters' is not valid. also how can i cgange this line to pdf
Dim mimeType as string = "ms-excel" i tried this but it did not
work
Dim mimeType as string = "ms-pdf"
Thanks
View 1 Replies
View Related
Oct 26, 2007
' 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()
End Sub
'End of script
here is how i run it
rs -i RunReport.rss -s http://localhost/reportserver -v WeekNumber=90 -e mgmt2000
this is the error i get:
The value of parameter 'Parameters' is not valid. also how can i cgange this line to pdf
Dim mimeType as string = "ms-excel" i tried this but it did not
work
Dim mimeType as string = "ms-pdf"
Thanks
View 1 Replies
View Related
Jul 5, 2015
This index is not unique
ix_report_history_creative_id
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).
The statement has been terminated.
View 6 Replies
View Related
Apr 15, 2008
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.
View 1 Replies
View Related
Sep 1, 2011
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?
New SSRS Output
Portfolio_Reference,Portfolio_Name,R,TR,TD,TC,D,
Old SSRS Output
Portfolio_Reference,Portfolio_Name,R,TR,TD,TC,D,
View 4 Replies
View Related
Jun 15, 2009
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.
View 7 Replies
View Related
Nov 17, 2015
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.
View 3 Replies
View Related
