SQL Query To EXCEL - Columnnames Wiht Blanks - Howto?
Jun 26, 2006
Hiho,
I'm reading out some exelsheets to store the data in a SQL DB on a MS SQL Server.
I can connect to Excel file easily and usual querys are woriking as well.
My Probelm is, that all my Excel files have some columns with a name that has more than one word, e.g. "this is an example".
Is there any possibility to make query's to this columns? ...renaming them would leed in some trouble, i guess.
regards,
ratsche
ps: maybe this is a sql problem and not a asp.net problem.
I use Blanks in ColumnNames ( I know that this isnt very good, but alot of code and querys had to be changed if I would remove all blanksin all columnnames).When I link this tables with ODBC in my ACC97 - project, some of thetables causes an ODBC-Error.Are there possibilities to workaround this error?Thanks, Andreas Lauffer, easySoft. GmbH, Germany
I have an Excel spreadsheet with a field that may contain blanks (empty). When I filter on that field to show only the blanks, I get 4000 records returned. But if I import the spreadsheet into SQL Server, the test in there for blanks ('') returns nothing. However, if I test for Nulls in that field, I get 5000 records returned. I don't know which one (Excel or SQL Server) is giving me the right answer. Can anyone help me understand what's going on?
Hi I have a stored procedure and i am trying to add case statements to them.. but i am getting an Error. which is Msg 125, Level 15, State 3, Procedure udf_EndDate, Line 34 Case expressions may only be nested to level 10.
And This is my sproc-- ================================================ -- Template generated from Template Explorer using: -- Create Scalar Function (New Menu).SQL -- -- Use the Specify Values for Template Parameters -- command (Ctrl-Shift-M) to fill in the parameter -- values below. -- -- This block of comments will not be included in -- the definition of the function. -- ================================================ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author:<Author,,Name> -- Create date: <Create Date, ,> -- Description:<Description, ,> -- ============================================= Create FUNCTION [dbo].[udf_EndDate] (@PeriodId int) RETURNS datetime AS
BEGIN DECLARE @Month int, @Year char(4)
SELECT @Month = [Month], @Year = Cast([Year] as char(4)) FROM Period WHERE PeriodId = @PeriodId
RETURN CASE @Month WHEN 1 THEN '1/31/' + @Year ELSE CASE @Month WHEN 2 THEN '2/28/' + @Year ELSE CASE @Month WHEN 3 THEN '3/31/' + @Year ELSE CASE @Month WHEN 4 THEN '4/30/' + @Year ELSE CASE @Month When 5 Then '5/31/' + @Year ELSE CASE @Month When 6 Then '6/30/' + @Year ELSE CASE @Month When 7 Then '7/31/' + @Year ELSE CASE @Month When 8 Then '8/31/' + @Year ELSE CASE @Month When 9 Then '9/30/' + @Year ELSE CASE @Month When 10 Then '10/31/' + @Year ELSE CASE @Month When 11 Then '11/30/' + @Year ELSE CASE @Month When 12 Then '12/31/' + @Year ELSE null END END END END END END END END END END END END END
Hi All,I have two tables, one is about member infomations, the other is thecatergoriesmember_info(id,name,email,phone)member_categories(id,category)how can create a view like this (id, name, category1, category2,category3) with high performance?Thanks in advance.Joshua
I believe saving prediction query results to relational tables is possible (the BI studio does it!). I am not clear on how to do this w/o the BI studio, which means if I write a DMX query and want to store its output to a relational table, how do I do it?
Hi,A long time ago I set up a database with *very* descriptive and thereforelong table names. :(I did the same with the column names. :((The column names even repeat the table name. :(((This was done in a period in which I was still using a lot of dynamic sql:(((( and sql in the code of the app :(((((.The tables all have the prefix 'tbl' (yes, I know) :((((((There are even ghastly underscores in it too :(((((((As a result, I'm starting to develop a nasty case of carpal tunnel syndrom(aka RSI) :((((((((For example there are names like:dbo.tbl_SalesOrderLine.SalesOrderLine_SalesOrderI have to type them in QA a gazillion times a dayThe number of objects in the database has grown well beyond even beginningto think about renaming them all.Now my question: Is there an MS SQL client utility out there that hasautocomplete?Cheers,Bas
I have the following query SELECT maximum.tblSalesCall.OrgKey, dbo.tblItem.ProductDescription, maximum.tblSalesCallItems.Facing, maximum.tblSalesCallItems.Price FROM dbo.tblBrand INNER JOIN dbo.tblItem ON dbo.tblBrand.BrandKey = dbo.tblItem.BrandKey INNER JOIN maximum.tblSalesCallItems ON dbo.tblItem.ItemKey = maximum.tblSalesCallItems.ItemKey INNER JOIN maximum.tblSalesCall ON maximum.tblSalesCallItems.SalesCallKey = maximum.tblSalesCall.SalesCallKey WHERE (maximum.tblSalesCall.OrgKey = 2134 OR maximum.tblSalesCall.OrgKey = 2135) AND (maximum.tblSalesCall.SalesCallKey = '274f68f8-6aad-426c-9a66-4267fc71c8c7') AND (dbo.tblItem.ItemActive = 1) AND (dbo.tblBrand.BrandActive = 1) AND (dbo.tblBrand.BrandKey = 25) that generates the following results set:
OrgKey ProductDescription Facing Price --------------------------------------------------------------- 2134 JUICE SLAMS - AWESOME APPLE 1 2.7900 2134 JUICE SLAMS - BURSTIN BERRY 1 2.7900 2134 JUICE SLAMS - STRAWBERRY BANANA TWIST 1 2.7900 2134 JUICE SLAMS - TOTALLY TROPICAL 1 2.7900 2134 SMOOTHIES - PINEAPPLE COCONUT 0 0.0000 2135 JUICE SLAMS - AWESOME APPLE 1 2.7900 2135 JUICE SLAMS - BURSTIN BERRY 1 2.7900 2135 JUICE SLAMS - STRAWBERRY BANANA TWIST 1 2.7900 2135 JUICE SLAMS - TOTALLY TROPICAL 1 2.7900 2135 SMOOTHIES - PINEAPPLE COCONUT 0 0.0000
However, the customer wants the results to look like this:
OrgKey JUICE SLAMS - AWESOME APPLE Price JUICE SLAMS - BURSTIN BERRY Price (The rest of the items follow) --------------------------------------------------------------------------------------- 2134 1 2.7900 1 2.7900 2135 1 2.7900 1 2.7900
BTW, there is a variable number of product depending on the brandkey that the customer chooses.
Any ideas on how I can do this would be greatly appreciated.
I have a C# application that connects to a SQL Server database that is in the server (Windows server 2003 which is "NOT" configure as an active directory). I installed the application in different machines that use XP and it connects perfectly to the database but when I install the application to a machine that have Windows Vista Home Premium, I get the message "Login failed for user". The user is not associated with a trusted SQL server connection. I need some help please. Thanks.
I would like to change a column name, but before doing so would like to search all SPs and Views for occurrences of that name. I would be happy with just a list of them, but even cooler would be the ability to change all the occurrences to the new name.
Been I have stopped a lot of days to begin to use the SQL Server 2005 Express edition, that's installs Visual Studio prof 2005. I tried to inspect SQL Express installation via the windowsXP WMI tool "Microsoft Management Console" and their "Servers Microsoft SQL Server" branch, on is visible the SQL express instance recently installed named "<serverName>SQLEXPRESS". But when I try to open connect, I obtain an error about "Can't establish connection ... reason:[SQL-DMO] ... verify your registry properties on SQL Server..." (more or less it's traslation to english) I thought that is a login problem ... I make test over this think without results... Them I install the "SQL Server management studio express" utility, and YES I can connect and use the SQL server without problems ... GRRRRR !!!! a lot of dais stoped ... but I don't understand why can't open the server instance on the "windows management console" because I really have permisions correctly, like see on the "SQL Server management studio express" utility. I'm ussing windows integrated security.
Trying to setup a derived column to use an expression stored in a package variable. But it seems that variables are always evaluated as text...I need it to evaluate as a Columname sometimes.
Example: On an ETL of Products I want a new derived column that uses two other columns.
I can hardcode an expression of ProductID + " - " + ProductName and that results in dynamic output. But now I want to use that as an expression stored in a variable so I can change it when needed.
So I make that expression = @[User::Variable] and stuff into @variable ( a string param) : ProductID + " - " + ProductName My output is the literal "ProductID + " - " + ProductName", and not the actual ID's and Names
I've tried with/without brackets, quotes and braces but no change.
Any way I can get the pieces in that variable expression to evaluate as column names?
I download and install SQL Server Express release version.(Install successfully) Then I install the new tool "Management Studio Express", and I use it to connect to an instance SQL Server to manage a database.(Install successfully)
Then I close the Management Studio Express. And I install books on line on my computer. (Install successfully)
After i want to open one more time the tool Management Studio BUT ANYTHING. If I uninstall books online and then try again to open Management tool it pass.
What is the problem with new tool??
Why when i install books online I cannot open again Management Studio??
I wrote a simple data flow with an OLE DB source and destination. I do a direct mapping of the columns ( colA - > colA) with no transformations needed. I found that colA on the destinatin does not allow NULLS (required by the program that accesses that database) while colA on the source supports and has NULLs, Is there any accomodaiton for handling NULLS (like mapping them to blanks) in the direct copy approach or do I need to read each row and test colA_ISNull?
Does having a lot of blanks in a column cause errors ?
In 2 or 3 packages where I get 95% of the data in the Error Colomn I can find nothing wrong with the data at all ? it all either seems perfect or there is no data in the column in question usually I would have maybe 800 rows where data would have been inserted but in the other 40, 000 rows the column is blank
Using the "Retain null values from the source as null values in the destination" doesnt seem to make a differance.
This is the error description
The data value cannot be converted for reasons other than sign mismatch or data overflow.
Anyone know of a solution / reason why this keeps happening
I posted this scenario earlier and ndinakar said my code worked on (I guess) his own machine but has not worked on mine! Does that not sound funny? I need to ge through with this simple but frustrating stuff in time. I've implemented more complex snippets before this but I just can't figure the problem out.
Can anyone out there tell me what I could be doing wrongly? I wrote a simple stored procedure called 'proc_insert_webuser' in a database that has been moved to a remote server called LAGOS-NTS3. I executed it from SQL Query Analyser and it worked. When, however, I executed the same stored procedure from ASP.Net it gives an impression that a new record has been inserted and even displays the newly generated ID column to me. On checking the base table I found only blank columns with only the ID column having the generated IDs!
I'm perplexed. I checked the code and can't possibly spot the error. Can anyone help. Found below are snippets used.
CREATE PROCEDURE proc_insert_webuser ( @applicantidnumeric(18) output, @firstnamevarchar(18), @midname varchar(18), @lastname varchar(50), @secretcodevarchar(18), @my_errnumeric(18) output ) AS IF (SELECT COUNT(ApplicantID) FROM tbl_webuser WHERE Firstname = @firstname AND Midname = @midname AND Lastname = @lastname) = 0 BEGIN begin transaction INSERT INTO tbl_webuser (Firstname, Midname, Lastname, SecretCode) VALUES (@Firstname, @Midname, @Lastname, @SecretCode) commit transaction SET @applicantid = @@IDENTITY END ELSE BEGIN RAISERROR('Error! Execution aborted. A matching user profile exist.', 16, 1) SELECT @my_err = @@ERROR END RETURN
Then the ASP.Net stuff:
Sub PostData()
'connection string production
Dim cstring As String = "User ID = sa; Password = ; database = E-Recruitment; Server = LAGOS-NTS3; Connect Timeout = 60"
'connection instantiation
Dim cnx As SqlConnection = New SqlConnection(cstring)
Try
'open connection
cnx.Open()
'instantiates and execute a command object
Dim cmd As SqlCommand = New SqlCommand
With cmd
.Connection = cnx
.CommandText = "proc_insert_webuser"
.CommandType = CommandType.StoredProcedure
Dim param1 As New SqlClient.SqlParameter("@applicantid", 0)
param1.DbType = DbType.Single
param1.Direction = ParameterDirection.Output
.Parameters.Add(param1)
Dim param2 As New SqlClient.SqlParameter("@firstname", txtFirstNM.Text)
param2.DbType = DbType.String
param2.Direction = ParameterDirection.Input
.Parameters.Add(param2)
Dim param3 As New SqlClient.SqlParameter("@midname", txtMidNM.Text)
param3.DbType = DbType.String
param3.Direction = ParameterDirection.Input
.Parameters.Add(param3)
Dim param4 As New SqlClient.SqlParameter("@lastname", txtLastNM.Text)
param4.DbType = DbType.String
param4.Direction = ParameterDirection.Input
.Parameters.Add(param4)
Dim param5 As New SqlClient.SqlParameter("@secretcode", txtPWD.Text)
param5.DbType = DbType.String
param5.Direction = ParameterDirection.Input
.Parameters.Add(param5)
Dim param6 As New SqlClient.SqlParameter("@my_err", 0)
param6.DbType = DbType.Single
param6.Direction = ParameterDirection.Output
.Parameters.Add(param6)
.ExecuteNonQuery()
'pick the auto-number
Dim strAppID = .Parameters("@applicantid").Value
'notifies the user of record success
lblMessage.Text = "Profile created. Your ApplicantID is : " & strAppID & " Kindly take note of your UserID. You'll need it for future logins."
End With
Catch ex As Exception
'notifies the user of any error(s)
lblMessage.Text = Err.Description & " originating from " & Err.Source
Finally
cnx.Close()
End Try
End Sub ==================
I'm calling this procedure from the click event of a command button. What could be happening to the code? I have done similar things and got positive results! Any hint from anyone out there?
A table was created in version 6.5 that has 2 blank spaces and a slash '/' in it's name (i.e. Item Type w/Groups). This was done in error and now the table cannot be dropped or renamed and dropped.
Does anyone know how I can delete this table? Please let me know.
I'm trying to create a view from a table in which I want to concatenate to columns to a new column. The concatenation part works fine when data "supports" calculations, i.e. no nulls. The data however is "infected" in two ways I have Null-values and I have blanks alongside with some real information. I concatenate via a CASE statement but I can't figure out how to take the 3 situations into account (nulls, blanks and data) and create one meaningfull column in my view.
The result should be column1 + "-" if column2 is null or blank column1 + "-" + column2 if column2 has meaningfull data
Both the involved columns are text (varchar).
If anyone has been in this situation before could you please provide som code that handles the situation.
The result could be a password like "0l$IE" and I the save this password to an SQL server. The problem is however that with the generated password five blanks are also added. Instead of "0l$IE" I get "0l$IE ". I hope you understand what I mean? Is there an easy way to correct this?
When I enter a data in a table, SQL Server automatically completes the data with blanks up to length of column. this happens in a web form also in Management Studiıo, Whan am I doing wrong ? does Collation have anything with it ? SQL Server 2005 / Developer Edition
I want to load this data into SQL Server tables so that the data will be imported without the trailing spaces(blanks) after each data element . Thus I will like to trim the data of the extra spaces (blanks) before import.
So the query I am looking for would basically add some values to the results where they meet the right criteria.
I am running the query through a view so I dont actually want to add the values to the physical tables only to the view of the results if you understand what I mean?
I am having a curious problem with parameter passing between reports. I have 2 reports the first report gives employee name, Department and in the second report I have employee details. If the user clicks on the Employee name in the First report it should go to the second report and display the details. My problem comes if suppose the employee name is Don King? when the parameter gets passed it does not recognize the space between the name and throws an error saying King not recognized.
Would appreciate if anybody could suggest as to how I can pass the name with spaces in the report parameters.
With DTS, the default behavior is to import blanks ("") in a data file as nulls in the table.
However, in SSIS, the default behavior is to import blanks as blanks.
I need to ensure that the packages I'm migrating import the data in the exact same way. I am wondering what is the easiest way to convert blanks to nulls?
Right now I'm using a script to convert the blanks to nulls before they are written to the table, but just wondering if there's an easier way.
I have a report that has DateTime parameters e.g. StartDate and EndDate. When I create these report parameters the "Allow Blank" option is disabled but checked. When I run the report and do not enter anything in these fields it pops a message asking me to enter the Date. How do I use the "Allow Blank" option for date parameters on my report?
I'm working on a new project using SSIS in SQL Server 2005 and have an issue that I need resolved.
I'm loading an XML file into SSIS using the XML Source Adapter. This maps to an OLE DB destination which reads the data into a SQL table (please see below for the table structure). In between this stage is a data conversion to convert the Unicode characters from XML into non-Unicode characters.
This might sound obvious, or a newbie question, but how are trailing blanks treated by SQL2005 on varchar columns?
I have a column where two rows only differ by a trailing blank. If write a select and a where clause on the column, anly trailing blanks seem to be trimmed. I tried the ansi padding setting but it doesn't change anything. Is it a question of collation? I have default collation on the server set to SQL_Latin1_General_CP1_CI_AS...
The problem also seems to arise when I try to create a unique index on the column, where both values are considered equivalent...
I give here a sample based on the BOL for set ansi_padding. I was expecting each of the select statements below to retrun only one row...
Cany somebody please explain why they all return two rows?
PRINT 'Testing with ANSI_PADDING ON'
SET ANSI_PADDING ON;
GO
CREATE TABLE t1 (
charcol CHAR(16) NULL,
varcharcol VARCHAR(16) NULL,
varbinarycol VARBINARY(8)
);
GO
INSERT INTO t1 VALUES ('No blanks', 'No blanks', 0x00ee);
I have a report that I am building that consist of 4 multi-value selections from four different queries. When I choose "Select All" from all the drop downs and click on "View Reports", it blanks out all the selections from one of the drop downs.
Can someone please point me in the right direction on this problem. The report will run sometimes, but most of the time it will not.
