I am looking for a few pointers on how to achieve something. For the purpose
of my application I need to specify a SQL Server table. After this I wish to
be able to loop through column types and calculate the appropriate .NET
datatype based on the SQL Data Type, and also figure out if the column
allows NULL values.
I'm not looking for someone to do my work for me, just some pointers on what
I should be reading to achieve my aim.
The space between '1' and 'A' isn't showing up correctly.
TABLE_B [fleet] [ron] 1 A 1 A 1 A 1 A 1 A 2 2 2 2 3 B 3 B
(fleet=1, ron=A; fleet=3, ron=B; etc.etc.)
I would like to fill TABLE_B's column "ron" by referring to TABLE_A's column "dest".ac = fleet, and for each ac, I would like to loop through the 'dest' column in TABLE_A from top to bottom to get the top most value. If there are no values (like with ac = 2), then value is blank.
For 1, the value is A. For 2, the value is blank. For 3, the value is B.
Therefore, in TABLE_B, for all 'ron' for fleet=1, the value is filled A. For all 'ron' for fleet=2, the value is blank. For all 'ron' for fleet=3, the value is B.
- create variable 'v' - where ac=fleet, loop through 'dest' from top to bottom to get top-most value. variable 'v' = the value. - in TABLE_B, for each ac=fleet, insert variable 'v' into the 'ron' column
ID AMT 1001 1234.560 1001 34.560 1001 134.000 1002 45.000 1002 3456.000 1003 5678.999
I need to create a fixed length data file..For example, ID char(6) and amt num(10.3) and sum(23.3)
It should be group and order by ID : 01 Represent ID line and 02 represent amt ( there can be multiple amt records) and 03 represent sum of amt. 01 + ID 02 + AMT 03 + Sum(AMT)
The output should look like this.. How do this either using a t-sql or SSIS?
i am creating a log file, and at 1 point I record how many rows are deleted after an update process.. I want to also record which productID are actually deleted... how would I write that?!
Basically I need know how I would get the list, and I am using PRINT command... I need to somehow write a loop that works through the list and PRINTS to the file....
Group,Is there a way to assign nullability on a column when using a select into?I've tried some of the usual things like coalsce, isnull, and cast. Sincethe new table gets definition from the source table or can be somewhatadjusted with cast is there a way to cast a not null? In the example belowhow can I select into causing tableone_new..col2 to be not null. Wetypically must use an alter statement after the select into but this seemsinefficient.Thanks,Raycreate table tableone (col1 int not null,col2 int )goinsert tableone values (1, 1)insert tableone values (2, 2)insert tableone values (3, 3)goselectcol1,col2into tableone_newfrom tableonegoexec sp_help tableone_newgodrop table tableonegodrop table tableone_newgo
Is there a way to control the types for output columns of a DataReader Source? It appears that any System.String will always come out as DT_WSTR. As I have my own managed provider, and I know what went in, I can say that really it should be DT_STR. The GetSchemaTable call from my provider will always say System.String as it does not have much choice, but GetSchemaTable does contain a ProviderType which is different for my DT_STR vs DT_WSTR, or rather when I want each. I think something like MappingFiles as used by the Wizard would work, but can I do anything today?
Does anyone have a clue as to why DT_R4, DT_R8 are not allowed as join columns? This means I cannot join tables on AccountNumbers, InvoiceNumbers, etc. What a pain...
The documentation for sp_trace_generateevent states that the following arguments are available:
Arguments [ @eventid=] event_id Is the ID of the event to turn on. event_id is int, with no default. The ID must be one of the event numbers from 82 through 91, which represent user-defined events as set with sp_trace_setevent. [ @userinfo= ] 'user_info' Is the optional user-defined string identifying the reason for the event. user_info is nvarchar(128), with a default of NULL. [ @userdata= ] user_data Is the optional user-specified data for the event. user_data is varbinary(8000), with a default of NULL.
@userinfo is stored in the textdata column, which after inspecting sys.trace_columns is of type text with a max size of 1073741824.
@userdata is stored in the binarydata column whose type is image with a max size of 1073741824.
I am attempting to build a stack trace of sorts that will capture detailed information about the run-time code executed when an error (contained within a try/catch) occurred.
The sql returned from dm_exec_sql_text goes well beyond 8000 and is getting truncated.
I haven't found any hot-fixes for SQL Server 2005 that addresses this issue. Has anyone else bumped into this limitation or found any explanation on the inconsistency between the stored procedure€™s arguments and the column types that the values are stored in?
I have Two Database that exist on Two seperate servers. The two database contain same schema and contains tables and columns of same name. Some tables have slight differences in terms of data types or Data type lenght.
For example if a Table on ServerA has a column named - CustomerSale with Varchar (100, Null) and a table on ServerB has a column named CustomerSale with Varchar (60, Null), how can i find if other columns have similar differences in all tables with the same name and columns in the two servers.
I am using SQL Server 2005. And the Two Servers are Linked Servers
What Script can i use to accomplish this task. Thanks
I have a indexing problem. I have a sequence that needs to has a index number. I want to use a table data type and have a working sample BUT I cannot reseed the table when needed. How do I do this.
This works only for the first ExitCoilID then I need to RESEED.
:: REGEDIT:::Â HKEY_LOCAL_MACHINESoftwareMicrosoftOffice14.0Access Connectivity EngineEnginesExcelTypeGuessRows ::TypeGuessRows value to zero (0) IMEX=1 Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:destination.xlsx;Extended Properties="Excel 12.0 XML;HDR=YES;IMEX=1";
But SQL Table Last 39 Records Dumped as NULLÂ whichever is Alphanumeric. Why? Dynamically How Can I import without doing Text to column in Excel on that column ?
I've been writing this stuff for a while, and can't seem to come to the conclusion of how I should be retrieving data and assigning this data to variables.
Since i'm using SQL Server, I'm convinced that I should be using the datareaders GetSqlDouble (or whatever) function, but this would mean i need my local variables to be one of the SQL types. The problem with that is, that there will have to be lots of conversions done by me to be able to use a SQL type in my application.
For instance, I have a class where i'm retrieving dates. In order to retrieve them correctly (Null values included), I need to retrieve them with GetSqlDateTime(), then when it comes time to display the date in a table, i must first check for nulls, then convert to a string. This seems to be very cumbersome. Would I be better off just using GetDateTime(), and the .ToString method, and ignoring Sql Types all together?
so, basically, how are you guys using your sql server data? with the supplied sql types, and doing all of the post-processing work manually? I feel like i'm having trouble conveying my issue...hopefully someone knows what i mean....i'd just like some direction to save trouble in the long run, since i feel like there's got to be a better way...
Im having a issue. Im not sure how I am going to carry out but I have two tables in SQL server 2005 TABLES Category SubCategory (PK)CategoryName (PK) SubCategoryNameCategoryID SubCategoryIDDate Date (Just shows the date inserted) (FK)CategoryID On the front page, I need to have it querys out the CategoryName from Categorys but also querys out all....Well not all but atleast 5 subcategorys that relate to that categoryName. Once its down it moves to the next category and does the same and so on. Does anyone know the trick ?
I am following the SSIS overview video- URL...I have a flat file that i want to import the contents onto a SQL database.I created a Dataflow task, source file and oledb destination.I am getting the folliwung error -"column "A" cannot convert between unicode and non-unicode string data types".in the origin file the data type is coming as string[DT_STR] and in the destination object it is coming as "Unicode string [DT_WSTR]"I used a data conversion object in between, dosent works very well
I'm in the process of converting a rather huge VSAM database into a set of SQL tables. I am using the same data names from the mainframe (like XDB-NAME to RDB-NAME). I load the files using Import Export Data and it makes the tables with such column names as col001, col002, col003, etc... and always sets the data types to varchr(255). And I have to cut and paste the data names from the manframe side to the server side (and the data types to.) So, is there an easier way to do this? Or am I doomed to cut-n-paste my days away... Thanks for any help.
I´m exporting an ms-excel file, then I use a lookup transformation to get a field from a SQL Server 2005 table. The Lookup transformation editor, after selecting the table, shows a warning that says:
at least one mapping between a column from available input columns ans a column from available lookup columns must be defined on the columns page.
So I try to make a relationship in the Lookup transformation editor's column tab where I find the Available input columns and the available lookup columns but I get the following error:
The following columns cannot be mapped: [Department, DEP_CLEGALCODE] One or more columns do not have supported data types, or their data types do not match.
The field in SLQ Server is varchar(10) and the input field is a derived column transformation; I have tried different Data Types but I always have the same error.
The DataFlow is: ExcelSource --> Derived Column --> Lookup --> Flat file destination
I want to loop through a recordset and do inserts into another table based on each record.
The way I have been doing it is copy my key data into a temp table, Loop through temp finding the max ID Doing what I need to do, deleting the max, then finding the new max and looping until no records exist.
I know there has to be a better way. The table I am working with is millions of records. Thanks in advance, Chris Reeder
I need to loop through a set of tables and move the data through a data pump from one server to another. This set of tables is dynamic so I have greated a global recordset and the looping is working fine.
During the looping process I need to change the transformations for each table so the source, destination, and transformation of the datapump are correct for the next table in the loop. I am using a VBS to handle this right now but cannot get the transformation to change. I essentially want to auto-remap using a vbs script. Is this possible?
Hello clever people I have a table that holds duplicates that I want to change into a table that has no duplicates. The current table is this name compound_id integer name varchar(150) name_type integer
This table stores chemical names. There is no primary key in the table so there are multiple compound_id's. I think the original idea was to have four name-types 1 = chemical name 2 = a description of the chemical 3 = a synonym of the chemical 4 = a formula of the chemical
I have created a new table called compound_name with this structure
id int primary key (auto identity) compound_id int used as a foreign key compound_name varchar(150) compound_desc varchar(250) compound_synonym varchar(150) compound_formula varchar(50) compound_trade_nme varchar(50)
I have also started to populate the new table by running this code insert into compound_name(compound_id,compound_name) SELECT DISTINCT compound_id, name FROM dbo.name WHERE (name_type = 1)
Now I need to somehow loop through the name table getting distinct compound_id's, and perform a case when name_type = 2 (which is synonym name_type) Then inside the loop update compound_name.compound_synonym for each compound_id which matches name_type 2 Then case 3 do the same for name_type 3 which is the name_type for descripton Then case 4 do the same for name_type 4 which is the formula
Hi there, I am new to SQL and am having trouble looping a script. I have the following script that needs to be refreshed a large number of times, or needs to be looped indefinitely until stopped:
select df.tablespace_name "Tablespace", block_size "Block Size", (df.totalspace - fs.freespace) "Used MB", fs.freespace "Free MB", df.totalspace "Total MB", round(100 * (fs.freespace / df.totalspace)) "Pct. Free" from dba_tablespaces ts, (select tablespace_name, round(sum(bytes) / 1048576) TotalSpace from dba_data_files group by tablespace_name) df, (select tablespace_name, round(sum(bytes) / 1048576) FreeSpace from dba_free_space group by tablespace_name) fs where ts.tablespace_name = fs.tablespace_name and df.tablespace_name = fs.tablespace_name(+) ;
I know this question may have a very easy solution, but I have no idea how to solve it.
Bi Ar Ar Bi Ar Ch Bi Ar Ma Bi Au Ar Bi Au Ch Bi Au Ma As Ar Ar As Ar Ch As Ar Ma As Au Ar As Au Ch As Au Ma As Au Ma
I have 3 columns S, D, C. i have text values in it. I need to write a query such that it will check each row for distinct value.For ex, all the rows are distinct except the last one. so i need to see all the duplicate entries. can anyone help me?
Hi All, I would like to know the best way to approach the following requirement: I have an ASP.net 2 web site which gets its data from SQL 2005. I am trying to run a series of 'rules' which are SQL where statements stored in a table, against rows stored in another table. I open the 'Rules' table looping through all records. I copy each rule to a string and put it on the end of the SQL statement so that the rule will only be appended if it passes the rule... this may be a little confusing. The rules process will fire when the details have been submitted to the database. Table containg rules would contain something like: ID, RuleSQL 1, (ClientAge >18) 2, (ClientIncome>10000) 3 Etc... This a very simplified version of the table but gives the general idea. I currently use ASP.NET 2 and sqlconnections/datareaders to do this. I would like to know if there is a way of doing the same thing server side using Transact SQL because that would (I believe) speed up the time taken to perform all the tests as i wouldn't need to rely on ASP to open all recordsets and append the data. If the ASP route would be the standard way of doing it and is not likely to have a detremental effect on performance then i am fine to stick with it because i know it works. any comments or suggestions would be welcomed. Thanks, Ian
I have an array (12,2) of values plus a profile variable that I want to pass as parameters while writing to a database. I've been told that I've set up the parameters wrong, and they cannot be changed every time I loop using the method I'm using. But I have no idea how to use any other method. Please... I'm down to the wire in terms of deadline here. I have until midnight to get it uploaded and running online. [CODE]Sub WriteClasses(ByVal CreditsArray) Dim i As Integer Dim EnrollDb As SqlConnection Dim cmdEnroll As SqlCommand EnrollDb = New SqlConnection("Server=LONNASQLEXPRESS;Integrated Security=True;database=LGordonTouroReg") cmdEnroll = New SqlCommand("INSERT INTO Enrollment (SectionID, Semester, Year, ClassID, StudentID) VALUES (@SectionID, 'Fall', '2007', @ClassID, @StudentID)", EnrollDb) EnrollDb.Open() For i = 0 To 12 cmdEnroll.Parameters.AddWithValue("@SectionID", CreditsArray(i, 2)) cmdEnroll.Parameters.AddWithValue("@ClassID", CreditsArray(i, 0)) cmdEnroll.Parameters.AddWithValue("@studentID", Profile.StudentID) If Not CreditsArray(i, 0) = "" Then cmdEnroll.ExecuteNonQuery() Response.Write(CreditsArray(i, 0) & " has been added to your schedule.<br/>") End If Next i EnrollDb.Close() End Sub[/CODE]
lets say i have a stored procedure (for insert command) which i am calling in my code to execute. The user provided data is being stored in a array. My class takes the stored procedure name and also takes parameters name and types. Is there any way to loop through the parameters, (various columns in the table which is of diffrent data type ie varchar, int, etc). How to implement it?
Hoping for a little help... I'm attemting to call a stored proc, pass parameters, and display the data 1 record at a time. I need to be able to show the data in a series of lables or text boxes. So the user will see one record, pushed into the lables, click a button and go to the next record...so on and so forth.
I think I have the code to get the data correct, it's the displaying data in lables and looping through the recordset the has me clueless.
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load 'Put user code to initialize the page here If Not Page.IsPostBack Then ' IF This is the first page load Dim UserID As String = Request.QueryString("UserID") ' parameter for stored procedure Dim RoleID As String = Request.QueryString("RoleID")
Dim DS As DataSet Dim MyConnection As SqlConnection Dim MyCommand As SqlDataAdapter
MyConnection = New SqlConnection(System.Configuration.ConfigurationSettings.AppSettings("connectionString")) MyCommand = New SqlDataAdapter("getdirective", MyConnection) MyCommand.SelectCommand.CommandType = CommandType.StoredProcedure MyCommand.SelectCommand.Parameters.Add(New SqlParameter("@roleID", SqlDbType.NVarChar)).Value = RoleID
Try DS = New DataSet MyCommand.Fill(DS)
'Display data in a series of lables or highly formated datagrid
Catch ex As Exception Response.Write("<font color=red>Error: " & ex.Message & "</font>")
End Try
Else 'IF the page is being reloaded
End If
End Sub
Private Sub cmdAck_Click(...) Handles cmdAck.Click 'This need to loop through the records
I'm pretty new to T-SQL and have an *easy* problem, for you experts, that I can't get seem to get solved. I'd like to loop through a list of items in TABLE "Items". I then want to use that list to loop through and SUM SALES and QTY for each item from a TABLE called "Shipments". As I loop through each item, I want to UPDATE the "Items" table with the Summary data. So, logically I'd do something like this:
SELECT item_no FROM Items
BEGIN
SELECT SUM(sales) AS Total_Sales, SUM(qty) AS Total_Qty WHERE item_no=@item_no
UPDATE Items SET Sales=@Total_Sales, Qty=@Total_Qty WHERE item_no=@item_no
END
I've tried somewhat successfully to use cursors to create my loop query, but I cannot seem to get the SELECT and UPDATE correct in the loop itself. Can anyone steer me in the right direction (or better yet, provide a solution)?
<!--- Update the DISTANCE field on STORE table ---> <cfquery name="UpdateZips" datasource="#application.data#" username="#application.username#" password="#application.password#"> exec Stores_UpdateZipSeachInfo '#Dist#', '#zip2.zipcode#' </cfquery> </cfloop>
I am not sure if what I wish to do is possible, but I shall ask anyway;
My project examines a database log of all the pages of an online teaching tool. Once the user has completed all the pages they are to be issued a certificate. Users may complete the teaching tool in any order, and the pages are always stored whenever they are acccessed, regardless of certification. I have created a number of views that extract the data into a list of all the possible completion dates; i.e. where all the pages have been completed within any 12 month period. I need to write a query/view that uses the view to extract the first possible user completion date followed by every completion 12 months after that, then after that etc. to present day. Can I do this? Am I making sense ?
A no is acceptable in this case; I know I can do this with multiple queries from withing an application. I'd just rather not.
i have a select query that returns multiple rows (within a cursor). How do i loop through the rows to process it (in a stored proc)? I donot want to use nested cursors. a code sample is requested.
I've got one table with two columns. Column Name Data Type 1) Id Integer Identity 2) RemDate DateTime
I've to write one SP/JOB in that there will be an integer input parameter @numofday.
Say value of @numofday is 5 then.... in SP/Job I need to insert 31 - 5 = 26 records to above-mentioned table where date starting from 1st of current month.
This logic can be achieve through looping but if anyone can suggest some better way to achieve this functionality without use of looping.
