Transform collate fields

SQL Server Database Engine

Index ‹ SQL Server
- Archive
  - SQL Server
  - Architecture
  - Smart Device
  - VB
  - IE
  - Dotnet
  - Windows Forms
  - Game
  - VFP
  - Visual C#
  - VC++
  - ISV
  - SharePoint
  - Office
  - Windows Live
  - VS 2008
  - Windows Vista
  - VS Express
  - Visual Studio

- Previous
  - 1
    - Flat file to table - rows out of order Hi, I noticed something strange today. I created a pkg that reads a flat file and writes the rows to a table. In checking the data in the file against what's in the table, I noticed that the rows were inserted in a different order than they are in the file. All the rows appear to be in the table correctly, but they're just not in the same order as in the file. I've never seen this before. But I checked very carefully, and this is indeed the case. Is this normal Thanks Tag: Transform collate fields
  - 2
    - SQL Server install I am new to the Community and trying to install SQL 2005 Dev on a Vista client. Previously I had Express installed. I could not fully remove all the pieces. During the install or upgrade i keep getting errors for IIS. IIS is installed on the machine but it keeps errroring. Any comments would be greatly appreciated. Tag: Transform collate fields
  - 3
    - stored procedure parsing comments incorrectly For a second time I encountered a strange problem with a stored procedure, which is simply not parsing and executing correctly. On both occasions the problem occurred with pretty long stored procedures - the one I have now is 2000 lines long. The first time I saw this I was working on SQL Server 2000, now I have a SQL Server 2005, but the database is in SQL Server 2000(80) compatibility level. The problem is that a regular SQL comment is sometimes merged with the line under it. --some comment some SQL code is parsed as --some comment some SQL code You can see how this can be a huge problem. From what I know the contents of a sp are stored in the system table syscomments, which splits the longer stored procedures into several records. I am wondering if there isn't a parsing problem when putting together a longer stored procedure. Perhaps if a "new line" character happens to be at the records split it is ignored and the two lines of TSQL get merged. My "fix" has been to readjust/remove the comments, add additional empty rows in between or just split the stored procedure into several smaller sps. I will appreciate your feedback if you know more about parsing the stored procedure from the database engine or if you have a better work-around. Tag: Transform collate fields
  - 4
    - Using XML Source in SSIS vs Script Source to parse xml files. Hi I am having a huge xml file with nested section. i also have a xsd file for that xml. i have a destination table where the data from the xml should be loaded into. i am using the xml source transformation. But o get all the data i need to use multiple merje joins to get the data in a single row which i can insert into the destination.i was not quiet convinced with using so many joins. so i tried using the script source transformation where i am using xml objects to get the node and dynamically construction the data row. and the output is then inserted into the destination. on comparing the two approach the one using the script source is working much faster than the xml source transformation. i wanted to know is there any limitaion using the script source to parse through xml files. also i would like to know any other better way of getting the data from xml source without using the joins. Hari Tag: Transform collate fields
  - 5
    - Select Time, WHERE @filename BETWEEN @theEndTime AND @theCurTime Hi ! I am new with SQL and TSQL I want to collect rows between two a start time and a end time. The time is in a textstring, like filename_wast_erth1_20070506_1812_all.txt I cannot use any other time in the database when i collect data! The database looks like this (almost anyway) FILENAME nvarchar ( 255 ) TIME filename_wast_erth1_20070506_1812_all.txt 2007-01-16 10:36:15 filename_wast_erth1_20070506_1812_all.txt 2007-01-16 10:36:20 filename_wast_erth1_20070506_1813_all.txt 2007-01-16 10:36:25 filename_wast_erth1_20070506_1815_all.txt 2007-01-16 10:36:35 filename_wast_erth1_20070506_1815_all.txt 2007-01-16 10:36:45 filename_wast_erth1_20070506_1818_all.txt 2007-01-16 10:37:05 I trying to do like this: SELECT , @theState = CONVERT ( char ( 20 ), DATE , 20 ), @id = ID , @temp11filename = SUBSTRING ( FILENAME , 21 , 4 )+ '-' + SUBSTRING ( FILENAME , 25 , 2 )+ '-' + SUBSTRING ( FILENAME , 27 , 2 )+ ' ' + SUBSTRING ( FILENAME , 30 , 2 )+ ':' + SUBSTRING ( FILENAME , 32 , 2 )+ ':00' , @filename = FILENAME FROM DELIVERY WHERE @temp11filename BETWEEN @theEndTime AND @theCurTime ORDER BY ID DESC ; END This result in no data Anyone with a idea how to do it Tag: Transform collate fields
  - 6
    - Radio button Hi, I have one report,in my report i need to display parameters like this: one radio button :Age and another radio button is Age Ranges For the Age,I have to dispaly like two text boxes named 'From' and 'To' For the Age Ranges,I have to display one drop down list contains values :20s,30s .. In Reporting Services,Putting the Radio button is not possible,i think,If possible how to achive this. Other workaround is i think Multivalued parameter which displays the Age and Age ranges When we click on Age we need to display 2 textboxes When we select Age Ranges we have to display Drop down list If we select both :both 2 text boxes and drop down list. Putting multi valued parameters for this requirement is not good and this is not the multi valued parameter concept.Please clarify me whether iam correct or not. Thanks in advance Tag: Transform collate fields
  - 7
    - Transfer object task error. Hi, i am trying to transfer objects from SQL Server 2000 DB to SQL 2005 DB. i have copy schema to true and i am only copying tables. when i have tried first time it worked fine but in next time it start to give error. here is the error description. [Transfer SQL Server Objects Task] Error: Execution failed with the following error: "ERROR : errorCode=-1071636471 description=An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Communication link failure". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "TCP Provider: An existing connection was forcibly closed by the remote host. ". helpFile=dtsmsg.rll helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC}". Thanks, Haroon Tag: Transform collate fields
  - 8
    - MIgrating Existing WorkFlow Data using SSIS Hi, I have posted the same query in the WF group too. Thought I'll post the same here. We have an existing application which has some WorkFlow Data. We need to migrate this data using SSIS. Now the problem here is that we have our Workflow facade class which inturn calls the WorkFlow Runtime. We have created the methods which will create the new WorkFlows. TO add this DLL in our SCript Component Reference, we added a strong name to my Facade.dll and deployed it to GAC. The system.WorkFlow DLL's are also present there. Everytime I run this Package, I get the following error "System.IO.FileNotFoundException: Could not load assembly...". What am I missing Thanks Soni Tag: Transform collate fields
  - 9
    - OLAP Error - Excel Pivot Hello, I'm wondering if anyone could shed some light on the following error when selecting multiple items in a pivot table. “The database driver does not include necessary capabilities and cannot be used with Excel. Contact your database administrator or driver vendor.” Your help is appreciated! Tag: Transform collate fields
  - 10
    - Cannot connect to SQL Express from WEB Application on internet Hi, I have installed SQl express 2005 SP1 and then configured it to accept remote connections which works within my internal network. I can connect other client stations within our internal network. However, when i try to connect to the database using a VB.net web application, the application cannot connect to the database. I have a hardware firewall which configured to foward port TCP 1433 and UDP 1434 to the SQL express server. I have also carried out a telnet communication to ports 1433 and 1434 and able to communicate on port 1433 and get an error message cannot connect to host on port 1434. What am i doing wrong Thanks Tag: Transform collate fields
  - 11
    - plain text mails How can I set the default body_format in database mail. I would like to send a warning with plain text format (from the alerts), that i'll get on my mobile phone, but the message's format is html... always...and I don't get the sms-s. Tag: Transform collate fields
  - 12
    - The Device is not ready error when Executing package on the server Hi, I have a package which reads an XML file from a certain folder and parses it. I deployed my package onto the server. Copied XML files on the D:\ drive the following folder on the server. Dim reader As New Xml.XmlTextReader( "D:\Apps\Request\Clients\ClientsStatus.xml" ) is fine. But Do While (reader.Read()) generates "The Device is not ready" I think it is still trying to read it from my desktop on which I don't have a D drive. Any help is appreciated. Tag: Transform collate fields
  - 13
    - Unable to install SQL Server 2008 on Windows Vista I am unable to install the latest CTP of SQL 2008 on my Windows Vista machine. It gets through all the things that happen before the install starts, but when it tries to install I get the following error message: "The installer package could not be opened. Verify that the package exists and that you can access it, or contact the application vendor to verify that this is a valid Windows Installer package." How do I fix this Tag: Transform collate fields
  - 14
    - Query Plan in SQL Server 2005 SP2 Hello, The query included at the end of this post seems to use the 'wrong' index when executing and takes 2+ minutes to run. When I provide an index hint, it runs in under 1 second. This happens in both the Production and Development environments; both servers run SQL Server 2005 SP2. When I run both the queries together in Query Analyzer (or SSMS), the Query Cost (relative to the batch) value for the query with the index hint = 83.24%. Profiler stats for Original Query: Duration = 130484 ms CPU = 111141 ms Reads = 85470 Profiler stats for Index Hint Query: Duration = 64 ms CPU = 687 ms Reads = 5558 Statistics are updated every night (w/Fullscan) Indexes are rebuilt/defragmented daily based on fragmentation levels. What am I missing here/how do I fix this performance issue Doesn't Profiler take into account the "Subtree Cost" for the Index Seek (which Query Analyzer & SSMS consider to be so expensive) What Events/Columns do I need to include in the Profiler trace to see this statistic when the query is executing Thanks much, Smitha ------------------------------------------------------------------------ QUERY: select min(AccessLevel) from Groups-- WITH(INDEX = ByClassID) where name = 'Student Leader' and classid in (2067,2063,2069,2070,2079,2072,2073,2074,2075,2076,2077,2073,2079,2030,2039,2032,201,2034,2035,2036,2037,201,2039,2090, 202,2092,2093,2094,2095,2096,2097,2093,202,24,2909,2902,2903,2904,2905,25,2907,2903,2909,220,229,222,223,224,225,226,227,223,229,2920,2929,2922,2923,2924,22,2926,2927,2923,2929,2930,26,2932,291,2934,2935,28,2937,291, 26,2940,2949,211,2943,2944,2945,2946,2947,2943,2949,27,2959,2952,2953,2954,2955,2956,2957,2953,2959,2960,2969,2962,2963,2964,2965,2966,2967,2963,2969,2970,2979,2972,2973,2974,2975,2976,2977,2973,2979,2930,26,2932,291,2934,2935,28, 2937,291,26,220,229,222,223,224,225,226,227,223,229,2200,2209,2202,2203,2204,2205,34736,34739,34749,34742,34743,34744,34745,34746,34747,34743,34749,3471,34759,34392,34393,34396,34397,34962,34937,3491,346,3420,3429,3423,3424,35360,35695,35696,35709, 1056,1057,1223,4107,1256,1257,1259,1269,1262,1263,1265,1267,1263,1269,1270,1272,1273,1274,1275,1276,1277,1273,1279,1230,1239,1232,121,1234,1235,1236,1237,121,1290,122,1292,1293,1294,1295,1296,1293,122,1300,1309,1302,1303,1304,1305,1306, 1307,1494,1459,1469,1462,1466,1467,1469,1470,1472,1474,1475,1477,1432,141,1435,1436,1437,1439,1494,1496,1497,142,113,115,117,113,119,1590,152,1592,1593,1594,1595,1596,1593,152,1520,1529,1522,1523,1524,1525,1527,1539,151,1534,1536, 151,1540,1543,1544,1545,1547,1572,1573,1574,1576,1577,1573,1579,1539,53969,64763,67735,67736,63967,1529,1530,1532,151,1534,1535,1536,1537,151,1539,1540,1549,1542,1543,1545,1546,1547,1543,1549,151,1559,1553,1555,1557,1553,1559,1560,1569,1562, 1563,1564,1565,1566,1567,1563,1569,1570,1579,1572,1573,1574,1575,1576,1577,1573,1579,1530,1539,1532,151,1534,1535,1536,152,1609,1602,1603,1604,1605,1606,1607,1603,1609,1690,162,1692,1693,1694,1695,1696,1697,1693,162,1629,1622,1635,1637, 1640,1642,1646,1649,1652,1657,1669,1664,1667,1679,1634,1637,1693,1703,1706,172,1795,1793,1722,1726,1764,1392,1393,1394,1395,1396,132,1320,1329,1322,1323,3629,3622,3623,3624,3625,3626,3627,3623,3629,36200,36209,36202,36203,36204,36205,36206,36207, 36203,36209,36290,37093,37094,37095,37096,37097,37093,3702,37020,37029,37022,37023,37024,37025,37026,37027,37023,37029,37030,37039,37032,3701,37034,37035,37036,37037,3701,37039,37040,37049,37042,37043,37044,37045,37046,37047,37043,37049,3701,37059,37052,37053,37054,37055,37056,37057, 37053,37059,37060,37069,37062,37063,37064,37065,37066,37067,37063,37069,37070,37079,37072,37073,37074,37075,37076,37077,37073,37079,37030,37039,37032,3701,37034,37035,37036,37037,3701,37039,37090,3702,37092,37093,37094,37095,37096,37097,37093,3702,374,37909,37902,37903,37904,37905, 375,37907,37903,37909,3720,3729,3722,3723,3724,37320,37329,37322,37323,37324,37325,37326,37327,37329,3710,3719,3712,3713,3714,3715,3716,3717,3713,37340,37349,37342,37343,37344,37345,37346,37347,37343,37349,3731,37359,37352,37353,37354,37356,37357,37353,37359,37360,37369, 37362,37363,37364,37365,37366,37367,37363,37369,3715,3716,3717,3713,3719,37340,37349,37342,37343,37344,37345,37346,37347,37343,37349,3731,37359,37352,37353,37354,37355,37356,37357,37353,37359,37360,37369,37362,37363,37364,37365,37366,37367,37363,37369,37370,37379,37372,37373,37374, 37375,37376,37377,37373,37379,3710,3719,3712,3713,3714,3715,3716,3717,3713,3719,37390,3732,37392,37393,37394,37395,37396,37397,37393,3732,374,37909,37902,37903,37904,37905,375,37907,37903,37909,3720,3729,3722,3723,3724,3725,3726,3727,3723,3729,37920,37929,37922, 37923,37924,372,37926,37927,37923,37929,37930,376,37932,3791,37934,37935,378,37937,3791,376,37940,37949,3711,37943,37944,37945,37946,37947,37943,37949,377,37959,37952,37953,37954,37955,37956,37957,37953,37959,37960,37969,37962,37963,37964,37965,37966,37967,37963,37969,37970, 37979,37972,37973,37974,37975,37976,37977,37973,37979,37930,376,37932,3791,37934,37935,378,37937,3791,376,3720,3729,3722,3723,3724,3725,3726,3727,3723,3729,1000,1009,1002,1003,1004,1005,1006,1007,1003,1009,1090,102,1092,1093,1094,1095,1096,1097,1093, 102,1020,1029,1022,1023,1024,1025,1026,1027,1023,1029,1030,1039,1032,101,1034,1035,1036,1037,101,1039,1040,1049,1042,1043,1044,1045,1046,1047,1043,1049,101,1059,1052,1053,1054,1055,1056,1057,1053,191,1934,1935,18,1937,191,16,1940, 1949,111,1943,1944,1945,1946,1947,1943,1949,17,1959,1952,1953,1954,1955,1956,1957,1953,1959,1960,1969,1962,1963,1964,1965,1966,1967,1963,1969,1970,1979,1972,1973,1974,1975,1976,1977,1973,1979,1930,16,1932,191,1934,1935,18,1937,191, 16,120,129,122,123,124,125,126,127,123,129,1200,1209,1202,1203,1204,1205,1206,1207,1203,1209,1290,122,1292,1293,1294,1295,1296,1297,1293,122,1220,1229,1222,1223,1224,1225,1226,1227,1223,1229,1230,1239,1232,121,1234,1235,1236, 1237,121,1239,1240,1249,1242,1243,1244,1245,1246,1247,1243,1249,121,1259,1252,1253,1254,1255,1256,1257,1253,1259,1260,1269,1262,1263,1264,1265,1266,1267,1263,1269,1270,1279,1272,1273,1274,1275,1276,1277,1273,1279,1230,1239,1232,121,1234, 1235,1236,1237,121,1239,1290,122,1292,1293,1294,1295,1296,1297,1293,122,1300,1309,1302,1303,1304,1305,1306,1307,1303,1309,1390,132,1392,1393,1394,1395,1396,1397,1393,132,1320,1329,1322,1323,1324,1325,1326,1327,1323,1329,110,119,112, 113,114,115,116,117,113,119,1340,1349,1342,1343,1344,1345,1346,1347,1343,1349,131,1359,1352,1353,1354,1355,1356,1357,1353,1359,1360,1369,1362,1363,1364,1365,1366,1367,1363,1369,1370,1379,1372,1373,1374,1375,1377,1373,1379,110,119, 112,113,115,116,117,113,119,132,1392,1393,1729,1723,9441,94497,94493,9442,9410,9419,9412,9413,9414,9415,9416,9417,9413,9419,94590,9452,94593,94594,94595,94596,94597,95576,97396,97909,93057,4640,4653,4654,4656,4657,4653,4659,4660,4669,4662, 4663,4664,4665,4666,4669,4670,4679,4672,4673,4675,4676,4677,4639,4632,461,4634,4635,4636,4637,4639,5290,522,5294,5295,5296,5370,90742,20953,20959,2239,2234,222,2292,2293,2294,2296,2297,222,29209,29204,29205,29206, 29207,29203,29209,29292,29294,29295,29297,29223,29225,29226,29223,29229,29230,29239,2921,29234,23903,23909,2320,24393,2521,25252,25253,25256,25253,25269,25262,25264,25266,25267,25269,25270,25279,25272,25273,25274,25276,25277,25273,25230,25239,25232, 25235,25237,2521,25239,25290,25292,25293,25294,2790,2793,272,2729,2749,2745,2746,271,2759,2753,2755,2753,2759,2760,2762,2764,2766,2769,2770,2779,2772,2777,93029,8747,8743,8749,871,8759,8752,8755,8757,8769,8764,8765, 8766,8769,8770,8779,8773,8774,8775,8776,8777,8773,8779,8730,8739,8732,871,8734,8739,8790,872,8792,8795,8797,8793,872,8300,8309,8303,8304,8306,8307,8309,8394,8396,8397,832,8322,8326,8323,810,819,812,813, 814,937952,6045,6097,93203,93206,619,617,632,6395,6400,6403,6406,6490,6497,6422,6425,6423,6432,6434,6435,6437,6457,6453,6459,6460,6463,6464,6465,6466,6469,6470,6472,6473,6475,6477,6473,6430,6432,6434,6435,6437, 641,6439,6490,642,6492,6493,6494,6495,6497,6493,642,9370,9379,9372,9373,9374,9375,9376,9377,9373,9379,6590,6592,6594,6593,6520,6523,6524,6527,6523,6529,6539,651,6534,6535,6536,6539,6540,6544,6545,6547,6549, 6559,6552,6553,6555,6556,6557,6553,6560,6569,6564,6565,6567,6563,6570,6579,6572,6573,6574,6575,6577,6573,6530,651,6534,651,6590,652,6592,6594,6596,6597,6593,652,6609,6632,661,6634,6635,6636,6637,661,6647, 6643,6659,6652,6653,6654,6655,6656,6657,6653,6659,6669,6663,6664,6665,6666,6667,6663,6670,6672,6673,6675,6676,6677,6630,6632,661,6636,661,6693,6694,6695,6696,6697,6693,6700,6702,6703,6704,11439,11440,11449,11442, 11443,11444,11669,11679,11676,11677,1161,11635,11637,11639,1162,11697,11693,11709,11709,11790,11723,11727,11732,11735,11737,11749,11744,11743,1171,11300,11306,11307,11309,1132,11977,94170,94179,94172,945454,945934,946464,946465,946466,94123,94123,94139, 94134,94135,94143,9411,94153,94155,94157,7239,7232,721,7692,7694,7696,7697,7693,7703,7704,7705,7329,7324,7326,714,716,719,7359,7352,7359,7363,7367,7379,719,712,713,714,715,716,717,713,719,7390,732,7392, 7393,7394,7395,7396,7397,7393,732,74,7909,7902,7903,7929,7924,7923,7937,7947,7959,7953,7955,7957,7959,7962,7963,7964,7966,7967,7963,7969,7932,791,7935,791,722,723,9549,9543,952600,952629,952627,952629,952657,952653, 952662,952690,952697,952797,95272,952720,952724,952729,954597,954964,955669,955790,95572,955792,95629,957069) INDEX Definition: CREATE INDEX ByClassID ON Groups(ClassID) INCLUDE(Name,AccessLevel) Tag: Transform collate fields
  - 15
    - How to Locate the SSIS Installation? Part of our product will be using several SSIS packages. One of our installers wants to be able to create a .cmd file that will run DTEXEC against one of the packages, with the correct config file specified. It had been assuming that SQL Server was installed in the Program Files special directory, but that's not always the case. What is the correct way to locate the installation of SSIS In fact: What is the correct way to determine whether SSIS is installed What is the correct way to determine the version and SP that is installed (I want to require SP2) I had been using the values under HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\90\DTS\Setup until I found an SP2 machine with SSIS installed which did not have these keys. Where's the right place to look Tag: Transform collate fields
  - 16
    - Un-Pivot transform crashes BIDS (Serious Issue) I am facing a serious issue is BIDS. I have configured a Un-Pivot transform, source to unpivot is sort, when I change the source to derived column (one column name is same for both) and try to open Unpivot it gives me unreferenced lineage id (this is correct). Now When I select Delete Unreferenced Lineage Column and select Apply , it works till here. As soon as I select OK, BIDS crashes and closes. Tag: Transform collate fields
- Next
  - 1
    - Msg 137, Level 15, State 2, Line 8 / Must declare the scalar variable "@Interest". Hi guys! I have written the following store procedure in SQL server express 2005. But I get the above result when I parse it. Anyone for a tip Thanks Jean-Philippe CREATE PROCEDURE procInsertClient -- Add the parameters for the stored procedure here @ClientID smallint OUTPUT , @FirstName nchar ( 20 ), @LastName nchar ( 20 ), @Phone nchar ( 15 ), @Email nchar ( 30 ), @Interest text , @Newsletter char ( 3 ) AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON ; INSERT INTO ClientHeader ( FirstName , LastName , Phone , Email ) VALUES ( @FirstName , @LastName , @Phone , @Email ) END GO BEGIN INSERT INTO ClientDetails ( Interest , Newsletter ) VALUES ( @Interest , @Newsletter ) END GO Tag: Transform collate fields
  - 2
    - logistic regression model formual hello , i am using neural network model to build logistic regression how can i see the formula of the model the paramters that came out and their coefficiant i have look at the model content but couldn't find it tx Tag: Transform collate fields
  - 3
    - How to figure out if the dataset returned any data or not while rendering the report through the SSRS Web Service In one of my projects, i have been using the SSRS web service to render a report as pdf and then manually emailing it out. I need to know whether the data set for the report returned any data or not (so that i can avoid emailing the report with a blank report body). Ofcource this can be done by executing the same query (same as the query for the report) from C# code and checking the count of the resultset, before calling the SSRS Render method. But it might not be an efficient method. Was wondering the the SSRS Web Service provides an property or method that can let me figure out whether the resultset was empty or not (after the report execution). Thanks. Tag: Transform collate fields
  - 4
    - How to format cell / data apprearecnce under a table Hello All, I uploaded custtable under the database, the data looks fine except that the name that apprears has a lot of distance e.g it should be : firstname lastname however the format appears very strange: firstname lastname firstname lastname fistname lastname Same is the case with the address, I need to adjust or format the apperance that appears on the cell. Is there a way/ sql statement to format the data under the table so that the apprearence looks okay. I will really appreciate any sort of help on this one. Thanks, Rashi Tag: Transform collate fields
  - 5
    - SSIS Script Task I want to use script task in order to dynamically filled Table A, B, C from the data Table 1. What script should i used and how can i implement this in SSIS. Ex: Table 1 Col1 Col2 Col3 A F1 123 A F2 213 A F5 456 B G1 345 C H2 897 C H5 768 Now i wanna write a script which can dynamically do this: 1) read the each row of Table1 2) Get from Col1 = the name of the table on which it needs to update 3) Get from Col2 the name of the colunm of the table from Col1 on which update has to be made 4) Update that particular colunm with the value in Col3 Results shud be Table A F1 F2 F3 F4 F5 123 - - - - - 213 - - - - - - - 456 Table B G1 G2 G3 345 - - Table C H1 H2 H3 H4 H5 - - - - 768 Tag: Transform collate fields
  - 6
    - Data Conversion Question I am using the instructions at the following link to create an update/insert load: http://blogs.conchango.com/jamiethomson/archive/2006/09/12/SSIS_3A00_-Checking-if-a-row-exists-and-if-it-does_2C00_-has-it-changed.aspx My input data has values that are 8 bit signed integers DT_I8. My output table uses BIGINT for the same fields. Now I know that the conversion in SSIS is BIGINT = DT_STR. The question I have is this. On the INSERT side of the OLE DB Command I did not have to convert the DT_I8 values to write to the table. HOWEVER on the UPDATE side, the update errored out UNTIL I did a data conversion to DT_STR of the same fields. Why is it different between the UPDATE side and the INSERT side Tag: Transform collate fields
  - 7
    - How to flush data stored in SSIS buffer Hi, I am working on my own data flow source component. Here is a fragment of this component code: public override void PrimeOutput(int outputs, int[] outputIDs, PipelineBuffer[] buffers) { PipelineBuffer selectedBuffer = buffers[0]; string message; while ((message = GetMessage()) != null) { selectedBuffer.AddRow(); selectedBuffer.SetString(0, message); // how to flush data here } selectedBuffer.SetEndOfRowset(); } private string GetMessage() { // we are retrieving some message here, this is a long-term process } When a new row is added by this component to the buffer then this row is not immediately available to the next component in data flow. It is possible to configure SSIS in that way that each row is immediately sent to the next data flow component If no also please inform me about that. Thanks, Rafal Tag: Transform collate fields
  - 8
    - How to hide URL when display reports in reporting services Hi , How can I hide the URL information when I open a report, some with parameters and some do not have parameters. Is there anyway or properties in RS that I can set to have it hide all the important information, like datasource, credentials, parameters, and display the report like a aspx page instead Or does this need to have special programming to make this happen and if so, what do I need to do thanks very much Tag: Transform collate fields
  - 9
    - Question on calculated members Hi, all experts, As we know the calculated members are only calculated at query time which means the analysis services engine wont preaggregate any thing for calculated members across different levels of dimensions I am a bit confused then what is the point to have a calculated member in a cube especially if the cube is a large cube with over millions records of data In this case, the query including any calculated members against the cube with millions of records will be extremly slow I am looking forward to hearing from you shortly for your kind advices and thanks a lot in advance. With best regards, Yours sincerely, Tag: Transform collate fields
  - 10
    - Cannot start SQL Server Browser Hello, I use SQL Server 2005 for multi-user access. In the Surface Area Configuration, I changed the protocol from TCP/IP to Named Pipes and restarted the service. Then I went to SQL Server Browser, stopped it and clicked on Start to restart. I am getting a message "An error occurred while performing this operation". I am not able to get the Browser started. Any help would be greatly appreciated. Tag: Transform collate fields
  - 11
    - Multiple Database Files: Good or neutral? I have inherited some responsibilities for which I'm not really qualified, so I'll push on through and maybe not totally fall down. Assume 10 50GB databases, each in a single MDF file. All these MDF files reside on the C drive (the only drive on the system), running SQL 2005 in a 32-bit Windows 2003 or later, 8GB RAM. The C drive is 6 physical disks in RAID 5, say about 1.0 TB or so. We have 4 dual-core processors on the box. We have limited simultaneous users, initally about 8 users doing very heavy write on all tables in any one database. Later, we have about 15 users connecting via Web interface, and doing very heavy read and light writing. Each of the 10 or so database has this lifecycle: Heavy write for about 2 weeks (load data) then heavy read for about 1 month (research and search data), then nothing ever again (db is taken offline). Of course, this is not enough information to go on, but let's just go on it anyway. My TempDB, Log (simple recovery), Index etc is all on the same RAID 5 drive (C). I have two basic questions I'd love to hear feedback on: 1. Is there any real advantage to creating 8 Data files for my database (one per processor core) 2. Given that the hardware people here REALLY don't want to change anything, what should I fight for first: a. Separate drive for LOG files b. Separate drive for TempDB c. Something else Thanks in advance. Tag: Transform collate fields
  - 12
    - ntext columns truncated to 32K when processed by Integration Services I have an Integration services application that retrieves big strings (over 32k) from an ODBC data source and stores them in a SQL server 2005 database as ntext. Somehow the strings are truncated to 32K. The app only copies the data from the ODBC datasource to the (OLEDB) target. Also, some of the special characters in the input text are not properly translated. Any ideas Tag: Transform collate fields
  - 13
    - BCP to a table partition Can any one tell me how to BCP to perticular partition of the table in SQL Server. Tag: Transform collate fields
  - 14
    - junk character within FLAT FILE sample Data: "061107" Every flat file ends with the junk character How to remove this using "Derived Component" or "Script Component" Pls provide the sample code for this task. Thank you. Tag: Transform collate fields
  - 15
    - SQL Server Licensing I have a three server network. One SQL Server, One Web Server, and a backup. The web server is ASP.NET, and uses the database. I do not track the number of users hitting the web server. What type of sql server license is needed Processor Device CAL User CAL I have read all the white papers and licensing info and can't find an aswer to this question. Please help Tag: Transform collate fields
  - 16
    - can we pass dataset dynamically??? Is it possible to pass the dataset dynamically in ssrs I am using sqlserver express edition at present. so in future it may change. is it possible to change the dataset in future or not Tag: Transform collate fields

rabbiwan

Hello,

I would like change in my data base the collate format of my fields in my tables.

Currently there is a SQL_Latin1_General_CP1_CI_AS collate and I would like change to SQL_Latin1_General_CP1_CS_AS.

There is a procedure to transform my fields without delete my data base

Regards.

Re: Transform collate fields

Jens K. Suessmeyer

Do you just want to change some columns, or do you want to change the collation of the entire database

HTH, Jens K. Suessmeyer.

---
http://www.sqlserver2005.de
---

Re: Transform collate fields

rabbiwan

My database is in SQL_Latin1_General_CP1_CS_AS collate

But when the tables are created with a SQL's script, the fields had SQL_Latin1_General_CP1_CI_AS collate and I would like transform that to be correct with the database

thank

Re: Transform collate fields

Jens K. Suessmeyer

Hi,

if you want to sync them to the database default, use the following command:

ALTER TABLE SomeTable

ALTER COLUMN Test CHAR(10) COLLATE DATABASE_DEFAULT

HTH, Jens K. Suessmeyer.

---
http://www.sqlserver2005.de
---

Re: Transform collate fields

rabbiwan

Thank you very much for your help

There isn't side effect on the data when you use this transformation

Re: Transform collate fields

rabbiwan

Is there someone to give me an answer

Re: Transform collate fields

Jasmin Azemovic

rabbiwan wrote:

Thank you very much for your help

There isn't side effect on the data when you use this transformation

There si always possibilitty to lose data when changing tables atributs (data types, colloations ..) Best is to have copy of table-s and make transformation and isolated enviroment. After that you will know is there any data loss.