i have 12 different sql server tables. all are binded to one gridview. i have kept one admin user to view the details of the 12 tables in a gridview (pagesize=5). now the admin wants to generate 12 CSV files from the 12 tables (that is, he has to choose the table name from a dropdown and then has to click "Generate" button. CSV file name should be like this "<table_name>_<date>" ). also want to put links(dynamically [after generation of those csv files]) in a separate page to download those csv files. how to do this in asp.net (C#)? its urgent.
insert into Test123 values(1,'Name','X') insert into Test123 values(1,'Age',50) insert into Test123 values(1,'Salary',1000) insert into Test123 values(2,'Name','Y') insert into Test123 values(2,'Age',30) insert into Test123 values(2,'Salary',2000) insert into Test123 values(3,'Name','Z') insert into Test123 values(3,'Age',35) insert into Test123 values(3,'Salary','One Hundred')
And I want output in below format.
AttributeValueType ================= AGE | NUMERIC NAME | ALPHABET SALARY | ALPHANUMERIC ==================
I need to convert a SQL table or SQL table data to XML format. I tried using the Import Export Wizard in SQL 2005 (used SQLXMLOLEDB and SQLXMLOLEDB 4.0 as the source). However, it didn't work. Any way you know how I can convert and obtain data in XML format?
I need to open and read a text file using C#. Once I have read all of the values from the text file, I will need to insert some of those values into a SQL Server database table. One of the values read from the text file will be a date in the format YearMonthDay. For example: 20070601 So my problem is that I don't know how to convert this date into the date format that is expected for a SQL server datetime value. Can someone help me out. How do I do this?
I am stuck at one place, where I have to convert CSV format file data into SAP IDOC format file. In SSIS we don't have any such SAP adapter (though we have .NET Data Provider for mySAP suite [SSIS SAP Adapter] but this is still not fully supported by Microsoft, plus it doesn't have feature to convert data into IDOC format) that can do this. Can someone here please provide me some pointers on any third party adapters available in market to do this job or if anyone has already developed some custom approach to achieve this task?
Your quick response on this is highly appreciated.
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.
How can we identify the Date Format from a String in SQL Server.
I might get an input from external source as "MM-DD-YYYY" or "DD-MM-YYYY" or "YYYY-MM-DD" or "YYYY-DD-MM", all i have to do is return the same with Current Date in the same format as Input.
I have a date field stored in a character type field ( this field is a spare one in an ERP package )I am trying to validate and convert to a date format using the following;
[code="CASE WHEN isdate( arc.UserField1 )= 1 then CAST( arc.UserField1 as DateTime ) ELSE CAST( '01/01/1900' as Datetime ) END"] [/code]
The results are ;
Date Result 29/09/08 1900-01-01 00:00:00.000 09/06/15 2015-09-06 00:00:00.000 18/03/13 1900-01-01 00:00:00.000 09/10/14 2014-09-10 00:00:00.000
as you can see - the date 29/09/08 is a valid UK date format - however it fails the validation. 09/06/15 converts to 6th September 2015 ( mm/dd/yy ) format...What do I need to do to force this to identify as dd/mm/yy date format ?
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?
I have to perform a bulk Import on a regular Basis and have created a script to do this. The Problem is that the .csv file has 12 Columns and the table to Import into has 14. To Workaround this discrepancy I have decided to use a Format file. The Problem is that how to create one.
I have to figure out the items that Legal Name implies individual but Legal Entity Structure indicates a incorporation type. In this sample, you can see Alexander, Justin N. is my target. But my problem is how should I use a query to figure out which one is a individual's name? How should I write a function to check the name format (Last, First Middle)?
Legal Name ////////////////////////////////////// Legal_Entity_Struct
S & H Farm Supply, Ltd.////////////////////////////Company F.M.Abbott Power Equipment,Co.///////////////Company Ray's Dixie Chopper, Inc.////////////////////////// Company Alexander, Justin N. ///////////////////////////////// Company Alameda Power Equipment, Inc.//////////////// Company
we are having the same code base in both the environment but we have different version of SQL Server Version(Env 1 - SQL 2012, Env 2 - SQL 2008). We are getting the expected result in Env - 1 but not in the ENV -2. So we tried to get the SQL trace for the web request we found that Date format getting changed.
2012 - Date Format in the SQL Trace for the request '2015-09-10 00:00:00' which is working as expected. 2008 - Date Format in the SQL Trace for the request 'Sep 10 2015 12:00:00:000AM' which is Not working.
Complete SQL Trace for Request. Evn - 1 - SQL Server 2012 exec dbo.usp_GetWorkQueue @ClientName='CLT',@HasFilter=1,@FacilityName='',@StartDate='2015-09-10 00:00:00',@EndDate='2015-09-18 00:00:00',@OrderDirection='D',@StartIndex=1,@PageSize=20
Using SQL Server 2005, I have set up a database for a school to provide timetables for students, teachers etc. The data is currently displayed in this form:
DAY LESSON YR SUBJ GRP STAFF ROOM Mon 1 7 PE T Da Gym Mon 2 7 PE T Da Gym Mon 3 7 Ma A Pr 5 Mon 4 7 Ma A Pr 5 Mon 5 7 Ma A Pr 5 Mon 6 7 Dr T Fn 36 Mon 7 7 Dr T Fn 36 Mon 8 7 En T Bn 21 Mon 9 7 En T Bn 21 Tue 1 7 De T Df 27 Tue 2 7 De T Df 27
etc.
I would like to display this as timetable with DAYS as row headings and LESSON numbers as column headings, with the remaining column info. in the above table combined into one value:
1 2 3 4 etc
MON (7 PE T Da Gym) (7 PE T Da Gym) (7 Ma A Pr 5) (7 Ma A Pr 5) TUE (7 De T Df 27) (7 De T Df 27) etc. WED THU FRI Should I be doing this with PIVOT (scares the life out of me ...) or is there some other solution? Many thanks.
I am importing a couple SAS datasets to SQL Server 2008 for a project. The dates are in a float format, they show up as DT_R8 in SSIS. How can I convert these values to SQL server datetime? I have tried dozens of methods I found on-line with no success, I keep getting 'Arithmetic overflow error converting expression to data type datetime.' errors.
When creating xml fileformat its throwing me error "invalid ordinal".
When created non-xml file format, no error, and was also able to load data file into sql table. Not sure why bcp (Version: 10.50.1600.1) is not able to create xml file format.
C:>BCP "MyGDB.dbo.Items_Import" format nul -f"C:AnkitTempBCPItemsMaster.xml" -x -w -T -S"(Local)"
SQLState = HY000, NativeError = 0 Error = [Microsoft][SQL Server Native Client 10.0][SQL Server]Invalid ordinal for field 2 in xml format file.
I have a table where hours are being loaded in a weekly basis. The YearWeek is populated when the data is loaded. The value format of the Year Week is 2015-39, 2015-41, etc. I need to calculate the total hours per Fiscal Year.For example, week '2015-39' will be return FY15 and week '2015-41' will return FY16, and so on. By extracting the year, I can do a group by and have total hours for each year.
Currently, I have it working by splitting the value into year and week and then looping through each year and week, so I can assign the totals to the corresponding FY.select sum(hours) as total, yearweek from tablename group by yearweek...Then I loop through using C#.I can return the FY using an actual date,how to do it for year-week format for any given year.
select CASE WHEN CAST(GETDATE() AS DATE) > SMALLDATETIMEFROMPARTS(DATEPART(YEAR,GETDATE()),09,30,00,000) THEN DATEPART(YEAR,GETDATE()) + 1 ELSE DATEPART(YEAR,GETDATE()) END AS FY
E.g, i have a store procedure. The start date is long date (4/15/2007 3:00pm). i want to select the start date with a particular date (short date format 4/15/2006). Thanks in advance.
I have a table that has a DATE field named. AccountingDate that is in the format YYYY-MM-DD. It's not a VARCHAR field. I simply want to convert this date field into the format MM/DD/YYYY and call it New_Accounting_Date.
I've played with various combinations of CAST & CONVERT but haven't been able to get it to work.
Below is my latest effort which returns the error:
Incorrect syntax near the keyword 'as'
What code would work to return a MM/DD/YYYY value for New_Accounting_Date?
Select GLBATCH.AccountingDate, convert(GLBATCH.AccountingDate as date),101) AS New_Accounting_Date from GLBATCH
I'm inserting from TempAccrual to VacationAccrual . It works nicely, however if I run this script again it will insert the same values again in VacationAccrual. How do I block that? IF there is a small change in one of the column in TempAccrual then allow insert. Here is my query
INSERT INTO vacationaccrual (empno, accrued_vacation, accrued_sick_effective_date, accrued_sick, import_date)
I am performing a series of calculations where accuracy is very important, so have a quick question about single vs double precision variables in SQL 2008.
I'm assuming that there is an easy way to cast a variable that is currently stored as a FLOAT as a DOUBLE prior to these calculations for reduced rounding errors, but I can't seem to find it.
I've tried CAST and CONVERT, but get errors when I try to convert to DOUBLE.
For example...
SELECT CAST(1.0/7.0 AS FLOAT) SELECT CONVERT(FLOAT, 1.0/7.0)
both give the same 6 decimal place approximation, and the 6 decimals make me think this is single precision.
But I get errors if I try to change the word FLOAT to DOUBLE in either one of those commands...
I am posting this information simply as an FYI. This is in reference to the MS-SQL command "CONVERT"
I spent over 2 hours :rolleyes: screwing around trying to find out different ways of formatting dates from MS-SQL into something that makes sense for what I needed. I googled everything I could think of and found multiple references that said the info is available on MSDN.. but I could not find it. What I did find were thousands of relatively useless references to "format codes" for converting dates, but with no references to what the different format codes would ultimately yield, or what format codes were available to use.
What I ended up doing was writing a small script to generate a list of all of the variations I could find.
Below is the script, and the output that it yielded. Now, before I get bombarded with "there is a better way" I know there probably is. But this is the way that I needed to do it this particular time. If there are technical errors in my explanation, anyone is welcome to correct them. But after 2 hours of messing with this for what should have been a super simple single .0009 second command, I am just irritated beyond belief that it had to be this complicated to find any useful information on the subject. That is why I am creating this. Hopefully it helps someone else.
The format for the MS SQL CONVERT command is :
CONVERT( length_of_output, date, format_code )
length_of_output : is exactly that . the number of characters that you want returned as your result. If you use a length of 6 you will only see the first 6 characters that are returned. I found the longest valid length to be 28 characters, but I went as high as 128 just for giggles and to see if it revealed any secrets.
date : is a valid date, I used directly the getdate() function
format_code : well.. that's the tricky part. See below.
What I did was ran a script that originally went from 1 to 20,000. It crashed at 15. Apparently the format codes are not totally sequential. So I put in an on error resume next.
What I found is that : 1) the codes are not uninterrupted sequential numbers. 2) the code output repeats every 255 3) negative numbers can be used, but its pointless. 4) useful valid codes are in the ranges of : 0-14, 20-25, 100-114, 120, 121, 126, 130 and 131 5) 0-25 typically represent "short dates" with the year being only 2 digits, but there are exceptions 6) 100 and above always returned a 4 digit year. the exception was 130 and 131, I don't know what it was trying to do.
Here is the script i ran
<% on error resume next
for iintCounter = 0 to 256 SQL = "SELECT CONVERT(CHAR(128), getdate(), " & iintCounter & " ) as TheDate" Set rsTheDateFormat = TheDatabase.Execute(SQL)
I have this stored procedure, and im trying to figured out how i can convert the date field into something i can actually sort in the right order in reporting services. Such as mm-dd-yy , whats the SQL code for doing that?