XML Source Populating CR LF To SQL2005 Table
Oct 31, 2006
In my SSIS package I used the Web Service task to populate a SSIS variable with data from an XML feed. Then in the Data Flow I used a XML Source to import that data into a table on my SQL Server. When I looked at the data i noticed that the some of the fields contained CRLF pair. These CRLF pairs are being only populated by xml nodes that contain no data and are self-terminated. Below is a sample of the XML feed that is causing the problems. the DR_M_Initial and DR_Type1 nodes are populating CRLF's to the table.
Can anybody give me some insight on this problem. I am performing a workaround using the SQL REPLACE function in an Update statement to strip out ch(10) and ch(13), but this just seems a little cluegy to me.
Sample XML
---------------------------------------------------------
<?xml
version="1.0" encoding="utf-16" ?>
- <ROOT xmlns="">
<ROWRESULT>
<DR_NUMBER>1</DR_NUMBER>
<DR_L_NAME>SMITH</DR_L_NAME>
<DR_F_NAME>JOE</DR_F_NAME>
<DR_M_INITIAL />
<DR_TYPE1 />
</ROWRESULT>
</ROOT>
Thanks,
Tim Frakes
View 1 Replies
ADVERTISEMENT
May 19, 2004
I have a table with a list of products,once I enter the data into the table and start using it on my web site as a drop down list,the list is sorted as an alphabetical list,is there are way to have a single drop down list but still be able to group the those products,in order words force them not to get sorted aphabetically.
Thanks
View 11 Replies
View Related
May 11, 2007
Hi, I have two tables:
1. RubricReportDetail with columns LocalPerf, Age
2. SppIndicator with columns Pct, Age
How can I populate the values of LocalPerf with Pct by matching
RubricReportDetail.Age = SppIndicator.Age ??
Please help me. Thanks in advance.
View 15 Replies
View Related
Apr 17, 2007
I am running a program that populates tables on my local database by querying another database.
View 2 Replies
View Related
May 14, 2008
Hi all,
I have a table that looks something like this: -
county fname sport
------ ----- -----
surrey tara squash
surrey tara hockey
surrey tara tennis
kent tom tennis
kent tom football
kent tom rugby
I want to read through the sport table and create a distinct list of sports which can be used to create a new table that would look like: -
County fname squash hockey tennis football rugby
------ ----- ------ ------ ------ -------- -----
surrey tara YES YES YES
kent tom YES YES YES
I am using the following code: -
DECLARE @sql NVARCHAR(MAX)
SELECT @sql = 'create table ey_report_temp (county nvarchar(100),fname nvarchar(100), ' +
STUFF((SELECT DISTINCT ',[' + sport + '] nvarchar(100) '
FROM ey_report FOR XML PATH('')), 1, 1, '') + ')'
SELECT @sql
exec sp_executesql @sql
SELECT * from ey_report_temp
--------------------------------------------
DECLARE @county nvarchar(max)
DECLARE @fname nvarchar(max)
DECLARE @sport nvarchar(max)
DECLARE merge_cursor CURSOR FAST_FORWARD
FOR SELECT county, fname, sport from ey_report
OPEN merge_cursor
FETCH NEXT FROM merge_cursor INTO @county, @fname, @sport
WHILE @@FETCH_STATUS = 0
BEGIN
select @sql = N'
update ey_report_temp
set ' + @sport + ' = ''YES''
where county = ''' + @county + '''
and fname = ''' + @fname + ''''
print @sql
exec sp_executesql @sql
if @@ROWCOUNT = 0
begin
select @sql = N'
insert into ey_report_temp (
county, fname, ' + @sport + '
) values ( ' + @county + ', ' + @fname + ', ' + @sport + ')'
exec sp_executesql @sql
end
FETCH NEXT FROM merge_cursor INTO @county, @fname, @sport
END
CLOSE merge_cursor
DEALLOCATE merge_cursor
select * from ey_report_temp
drop table ey_report_temp
This creates the new table fine however, when it trys to poulate I get the following EM, can anybody help? thanks in anticipation
(1 row(s) affected)
(0 row(s) affected)
update ey_report_temp
set squash = 'YES'
where county = 'surrey'
and fname = 'tara'
(0 row(s) affected)
Msg 128, Level 15, State 1, Line 4
The name "surrey" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.
update ey_report_temp
set hockey = 'YES'
where county = 'surrey'
and fname = 'tara'
(0 row(s) affected)
Msg 128, Level 15, State 1, Line 4
The name "surrey" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.
update ey_report_temp
set tennis = 'YES'
where county = 'surrey'
and fname = 'tara'
(0 row(s) affected)
Msg 128, Level 15, State 1, Line 4
The name "surrey" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.
update ey_report_temp
set tennis = 'YES'
where county = 'kent'
and fname = 'tom'
(0 row(s) affected)
Msg 128, Level 15, State 1, Line 4
The name "kent" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.
update ey_report_temp
set football = 'YES'
where county = 'kent'
and fname = 'tom'
(0 row(s) affected)
Msg 128, Level 15, State 1, Line 4
The name "kent" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.
update ey_report_temp
set rugby = 'YES'
where county = 'kent'
and fname = 'tom'
(0 row(s) affected)
Msg 128, Level 15, State 1, Line 4
The name "kent" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.
(0 row(s) affected)
View 7 Replies
View Related
Jul 9, 2006
I'm trying to do a table lookup in t-sql and runing into problems. I have two tables, City and County..
Table City:
CityID CountyID CountyName
1 3 NULL
2 2 NULL
3 1 NULL
Table County:
CountyID CountyName
1 Los Angeles
2 Contra Costs
I want to populate the NULL "CountyName" field in table City with the values from table County but I can't make it work! Any help appreciated.
Barkingdog
View 1 Replies
View Related
Feb 7, 2008
Hello,
Just when I thought I was starting to understand SSRS just a little and then I encounter a strange (maybe not too strange) of an issue.
I have a DataSet which runs a stored procedure and it requires 10 parameters. When I run it in the dataset portion itself it runs fine however, it will not work when I choose to Preview it. I refreshed my dataset and even rebooted my machine. Can anybody shed some light on this?
Thanks
View 6 Replies
View Related
Dec 30, 2007
While populating dimsnion table the requirement is that the 1st record in all dimension should be " Not Available"
and if the fact table has a null value for that dimension it shouldbe tied to " Not Available" record in dimension
Please help hwo to do both the things.
JJ
View 5 Replies
View Related
Mar 24, 2008
I have a table that I would like to update with the current dateTime of Today. The Table Field is called "DateTimeStamp" with a format of SmallDateTime -- I'm using VS 2008.
When I use the following command and then cmd.ExecuteNonQuery -- the table is correclty filled in with the exception of the DateTimeStamp field. Instead of 3/23/2008, I get the value 1/1/1900 ...
SqlCommand cmd3 = new SqlCommand("UPDATE CameraPods SET CameraBlock1_ID = 98, CameraBlock2_ID = 99, Status = 1, DateTimeStamp = 3/3/2008");
any ideas?
---Jim
View 3 Replies
View Related
Jul 12, 2004
Is there any way i could take the first 50 results of a sql query and store them into 1 table in access and take the next 50 and store them into a second table in access? Is there any SQL statement that will direct where the output gets directed
to?
OR is there a way to have the sql reults paird up with a autonumbered ID?
Doc
View 2 Replies
View Related
Oct 6, 2006
Hi,
I have a flat file with columns from a geographical hierarchy such as:
Country Zone State County City Store Sub Store , etc.
The file also has data columns for months to the right of the above columns such as:
Jul Aug Sept ......... basically 25 of these columns for two years' data for one product and another set of 25 columns for another kind of product. A typical record in the file looks like:
Country Zone State County City Store Substore
USA Southeast FL Hillsborough Tampa walmart Fletcher
May04_ProdA ....Jun06_prodA May04_ProdB...Jun06ProdB
144 160 180 158
I need to upload this data into a staging table in SQL Server 2005 using SSIS, I created a table with the geographical hierarchy columns but am trying to figure out a way to load the monthly data. I can create 50 columns for the 50 months ( 25 months for each product) but that would be very crude.
Is there a better way of inserting data from this flat file into a destination table? I need all the data in the staging table in one upload.
Thanks.
View 11 Replies
View Related
Aug 1, 2006
I am having a torrid time trying to populate a FACT table in ORACLE.
I have so far tried two approaches -
1. Identified the change type for records - meaning I mark the record as inserts or updates based on the availability of the key in the FACT table. I used a conditional split and then used two OLE DB destination tasks to load the FACT table. But this failed as I was pretty much inserting on both the conditions (inserts and updates). So this attempt would count as a batch update process attempt.
2. Tried to also write a stored procedure in ORACLE destination database and then use that stored procedure to execute record by record using OLE DB Transformation task. But this did not fly as well due to the fact that SSIS could not understand the oracle SP and could not parse the information out.
Thank you
View 1 Replies
View Related
Dec 5, 2005
Sorry I'm pretty new to SQL so I don't know if this is a simple question. I have a table, and I am trying to add a column to the table and populate this column using what would be called an 'IF' function in Excel.
Basically 'column A' has numbers in it. I want SQL to look at 'column A' and if the first 5 digits of the number in 'column A' are 00001, then put 'description A' into new column 'column B'. If the first 5 digits of the number in 'column A' are 00002, then put 'description B' into 'column A' etc.
Any ideas?
View 2 Replies
View Related
Jan 15, 2015
I have a column within a table which is already truncated/deleted all records within (Microsoft SQL 2008). I have to now populate the column with sequential numbers up to 50,000 records arbitrary numbers (doesn't mater) up to 7 characters.
what SQL statement I need to write that will automatically polulate the newly empty table with A000001,A0000002,A0000003, or any form for that matter etc so that I can sort number the records within the table.
I have approximately 50000 records which I need to sequentially entered and I really dont want to number the column manually via hand editing.
View 5 Replies
View Related
Aug 29, 2013
I have a table of id numbers that I wish to mask. My thought was to create a new column for this new id number and populate it with a unique sequential value - start at 1 and go as high as needed. My problem is that I cannot recall how to populate that column with a number...
View 4 Replies
View Related
Apr 10, 2008
I'm experienced using queries to extract data, but I'm new to actually creating tables, except through Access. I work with many records of data, so populating the data by hand is not an option. I created a test database (ValTest) and a test table within that database (ClaimTest). I created a text file (DataTest.txt) with the same layout as I defined for ClaimTest. I want to populate ClaimTest with the data from DataTest.txt. I was told the only way to do that using SQL Server Express was to use a utility called BCP. So I found a page on the Microsoft website talking about BCP. It gave some examples, so I opended up the Command Line window, pointed to the directory that contains the database and text file and typed "BCP ValTest.ClaimTest in DataTest.txt -T -c". I get 3 errors: Named Pipes Provider: Could not open a connection to SQL Server [2], Login timeout expired, and An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.
I tried looking around and finding where I could change the settings, in case that was indeed the problem, but was unable to find anything. Does anyone have any suggestions?
Thanks
View 10 Replies
View Related
Jun 10, 2014
I created a Fact Table with 3 Keys from dimension tables, like Customer Key, property key and territory key. Since I can ONLY have one Identity key on a table, what do I need to do to avoid populating NULLs on these columns..
View 3 Replies
View Related
Sep 11, 2015
How do I correctly populate a fact table with the surrogate key from the dimension table?
View 4 Replies
View Related
Jul 20, 2005
I have a combo box where users select the customer name and can eithergo to the customer's info or open a list of the customer's orders.The RowSource for the combo box was a simple pass-through query:SELECT DISTINCT [Customer ID], [Company Name], [contact name],City,Region FROM Customers ORDER BY Customers.[Company Name];This was working fine until a couple of weeks ago. Now wheneversomeone has the form open, this statement locks the entire Customerstable.I thought a pass-through query was read-only, so how does this do atable lock?I changed the code to an unbound rowsource that asks for input of thefirst few characters first, then uses this SQL statement as therowsource:SELECT [Customer ID], [Company Name], [contact name],City, Region Fromdbo_Customers WHERE [Company Name] like '" & txtInput & "*' ORDER BY[Company Name];This helps, but if someone types only one letter, it could still bepulling a few thousand records and cause a table lock.What is the best way to populate a large combo box? I have too muchdata for the ADODB recordset to use the .AddItem methodI was trying to figure out how to use an ADODB connection, so that Ican make it read-only to eliminate the locking, but I'm striking outon my own.Any ideas would be appreciated.Roy(Using Access 2003 MDB with SQL Server 2000 back end)
View 2 Replies
View Related
Feb 4, 2008
i am attempting to run phpbb using ms sql 2005 on the same box but get the following error during the setup
Could not connect to the database, see error message below.
[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
i am not sure yet if this is an issue with sql, php or phpbb
php is installed in iis and has all the modules installed which the php msi installer supported. i can run php code but in this case when i'm running the install.php file for the phphbb setup, i fill in the values for the database and got that error
phpbb detects all the required server settings and sees that i have ms sql installed
any suggestions on what that error means or how to go about configuring odbc driver?
probably a simple issue. but i'm still new with sql stuff
View 1 Replies
View Related
Apr 13, 2005
I need to populate a queue table with an orderid, and the rest of the order in an xml auto element format going into a text field.
The data is coming from another sql server table. I was going to do this is an sp and started with below
declare @request text
set @request = (select orderid,* from neworder
For XML auto,ELEMENTS)
However it errors on the statement. I can run the select by itself fine, but need to store the output.
Any ideas
View 1 Replies
View Related
Jun 25, 2007
we have a table in an oracle database containing security information that is updated several times a day. the network connection into that oracle database is not always reliable. we would like to keep a copy of that table in a sql2005 database. the table will need to be refreshed 1/hr. it is a small table so performance won't be an issue.
would ssis be ideal for setting something like this up? i have never created an ssis package before (i'm an oracle dba crossing over). any pointers on how best to set this up?
thanks!
chris
View 6 Replies
View Related
Dec 15, 2005
hi
i try to restore a bak file from another sql2005 server to my sql2005 server, but it show the error message as below :
TITLE: Microsoft SQL Server Management Studio Express
------------------------------
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.Express.ConnectionInfo)
------------------------------
ADDITIONAL INFORMATION:
Cannot open backup device 'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBackupackup.bak'. Operating system error 5(error not found).
RESTORE HEADERONLY is terminating abnormally. (Microsoft SQL Server, Error: 3201)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=3201&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
pls some one can help me ???
thanks
chaus
View 62 Replies
View Related
Feb 29, 2008
Hi,
I have an aplication (VS2005 and Sql2005 Express), where I need to lock a table when opening one form, and then close it when completing another form.
Is there a way to do this, maybe using a Stored Procedure.? I'd prefer to be still able to access the table in read only mode if possible.
I've looked at pessimistic concurrency, but the particular part of my application is not using a dataset, being based upon SqlCommand.ExecuteScalar(), so I'm fairly sure this is not an option.
Thanks
View 14 Replies
View Related
Oct 12, 2007
Can you open/use a database created in SQL2005 in SQL2005 Express?
Thanks for the help!
Max
View 4 Replies
View Related
Jul 13, 2007
Is there a possibility to import a table from sql 2000 into sql 2005 express?
I have a table exportet from sql2000 to csv, adjusted "FALSE" and "TRUE" into 0 and 1 and saved it to csv. Now I want to replace a table in the sql2005 DB by the csv. (The first row contains the header, each row contains a record.
I'm a n00b, as you can see ;)
Thanks for your help ;)
Peter
View 3 Replies
View Related
Aug 31, 2005
How to get the list of table names in a SQL2005 database?
View 6 Replies
View Related
Apr 1, 2014
I am stuck on finding a solution to transpose source data from a system via a metadata look-up table into a destination table. I need a method to transpose/pivot the source data into columns (which are by various data-types). The datatypes for each column are listed in a metadata table.
Source Data Table:
Table Name: Source
SrcID AGE City Date
01 32 London 01-01-2013
02 35 Lagos 02-01-2013
03 36 NY 03-01-2013
Metadata Table:
Table Name:Metadata
MetaID Column_Name Column_type
11 AGE col_integer
22 City col_character
33 Date col_date
Destination table:
The source data to be loaded into the destination table(as shown below):
Table Name: Destination
SrcID MetaID col_int col_char col_date
01 11 32 - -
01 22 - London -
01 33 - - 01-01-2013
02 11 35 - -
02 22 - Lagos -
02 33 - - 02-01-2013
03 11 36 - -
03 22 - NY -
03 33 - - 03-01-2013
View 7 Replies
View Related
Sep 13, 2005
I’ve got a situation where the columns in a table we’re grabbing from a source database keep changing as we need more information from that database. As new columns are added to the source table, I would like to dynamically look for those new columns and add them to our local database’s schema if new ones exist. We’re dropping and creating our target db table each time right now based on a pre-defined known schema, but what we really want is to drop and recreate it based on a dynamic schema, and then import all of the records from the source table to ours.It looks like a starting point might be EXEC sp_columns_rowset 'tablename' and then creating some kind of dynamic SQL statement based on that. However, I'm hoping someone might have a resource that already handles this that they might be able to steer me towards.Sincerely,
Bryan Ax
View 9 Replies
View Related
Jul 19, 2007
I am trying to import an Excel Spreadsheet into SQL2005. There is a column in the spreadsheet that has character values, and numbers. I have formatted the numbers as text on the spreadsheet. I have declared the column on the table as char/varchar/nchar, but whatever I do, the numbers don't get imported into the table, but show up as nulls. Any idea why?
Thanks
Mangala
View 1 Replies
View Related
Mar 15, 2007
Hi everyone
I have a problem about sysusers table in sql2005.
if you run the query in SQL2000
select * from sysusers where status = 2
and you can see the users which are created as sqlauthentication.
But in sql2005 status column equals 0 for sql authenticated users.
Is this a bug or the sturucture of the sysusers table is changed???
And how can i query the sql authenticated users in sql2005.
thanks
View 4 Replies
View Related
Oct 25, 2015
we have a table in our ERP database and we copy data from this table into another "stage" table on a nightly basis. is there a way to dynamically alter the schema of the stage table when the source table's structure is changed? in other words, if a new column is added to the source table, i would like to add the column to the stage table during the nightly refresh.
View 4 Replies
View Related
