SQL Server 2012 :: Convert All The Column Of Table To ToBase64String?
Feb 17, 2015
I want to convert all the column of table to ToBase64String.
Public Class ScriptMain
Inherits UserComponent
Dim md5 As MD5CryptoServiceProvider = New MD5CryptoServiceProvider()
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
Dim columnContents As Byte() = UnicodeEncoding.Unicode.GetBytes(Row.Col001 + Row.Col002 + Row.Col003 + Row.Col004 + Row.Col005 + Row.Col006 + Row.Col007 + Row.Col008 + Row.Col009 + Row.Col010 + Row.Col011 + Row.Col012 + Row.Col013 + Row.Col014)
Dim hash As Byte() = md5.ComputeHash(columnContents)
Dim hashString As String = Convert.ToBase64String(hash, Base64FormattingOptions.None)
Row.RowChecksum = hashString
End Sub
End Class
SELECT TOP (100) PERCENT dbo.Filteredfs_franchise.fs_franchiseid AS FranchiseId, dbo.Filteredfs_franchise.fs_brandidname AS Brand, dbo.Filteredfs_franchise.fs_franchisetypename AS [Franchise Type], dbo.Filteredfs_franchise.fs_franchisenumber AS [Franchise Number], dbo.Filteredfs_franchise.fs_transactiontypename AS [Transaction Type], dbo.Filteredfs_franchise.fs_franchisestatusname AS [Status Code],
[Code] ....
I need to pivot this so I can get one row per franchiseID and multiple columns for [Franchisee Name Entity] and [Franchise Name Individual]. Each [Franchisee Name Entity] and [Franchise Name Individual] has associated percentage of ownership.
This has to be dynamic, because each FranchiseID can have anywhere from 1 to 12 respective owners and those can be any combination of of Entity and Individual. Please, see the attached example for Franchise Number 129 (that one would have 6 additional columns because there are 3 Individual owners with 1 respective Percentage of ownership).
The question is how do I PIVOT and preserve the percentage of ownership?
I got a sales cost and cost amount table for my budget. the sales cost table is getting updated with FOBB items which makes the total incorrect . the FOBB values needs to be moved from the sales cost column to the cost amount column. how can i do it with an SQL script.
I am trying to use a stored procedure to update a column in a sql table using the value from a variable table I getting errors because my syntax is not correct. I think table aliases are not allowed in UPDATE statements.
This is my statement:
UPDATE [dbo].[sessions_teams] stc SET stc.[Talks] = fmt.found_talks_type FROM @Find_Missing_Talks fmt WHERE stc.sessionid IN (SELECT sessionid FROM @Find_Missing_Talks) AND stc.coupleid IN (SELECT coupleid FROM @Find_Missing_Talks)
I am writing a query to update table variable. It is throwing me some error.
I have a table variable declared and inserted the data with three columns. I want to update col1 of that table variable when the second column of that table variable= one column from a physical table
update @MYtabvar set @Mytabvar.LatestDate=B.LatestDate from TableB B where @Mytabvar.id=B.ID
I have a situation that I need to add a field to a table over linked server. The specifications of this is dynamic and it is being done in TQL / Stored procedures and this can not change. My code is generating the statement just fine and if I copy paste it to a new SSMS window and execute it WORKS.. The problem is I need to dynamically generate the statement (I am doing that just fine, I THINK). THEN I need to execute the statement IN THE SPROC, this part is not working.
Here is the code:
SET @AlterSQL = @DestinationServerName + '.[' + @DestinationDBName +'].' + @DestinationSchemaName + '.sp_executesql N'' ALTER TABLE ' + @DestinationTableName + ' ADD ' + @TempColumn + ' int' + CHAR(39)
The above Creates this when I expose it via a PRINT statement:
addb15.[FSParallel].dbo.sp_executesql N' ALTER TABLE Node ADD ImportIdentity int'
After I create the statement I use:
EXEC @AlterSQL
And this returns the following error:
Msg 2812, Level 16, State 62, Procedure ETLDynamicImport, Line 244 Could not find stored procedure 'FSParallel.dbo.sp_executesql N' ALTER TABLE Node ADD ImportIdentity int''.
I have created some dynamic sql to check a temporary table that is created on the fly for any columns that do contain data. If they do the column name is added to a dynamic sql, if not they are excluded. This looks like:
If (select sum(Case when [Sat] is null then 0 else 1 end) from #TABLE) >= 1 begin set @OIL_BULK = @OIL_BULK + '[Sat]' +',' END
However, I am currently running this on over 230 columns and large tables 1.3 mil rows and it is quite slow. How I can dynamically create a sql script that only selects the columns in the table where there is data in a speedier manner. Unfortunately it has to be on the fly because the temporary table is created on the fly.
Given one table, Table1, with columns Key1 (int), Key2 (int), and Type (varchar)...
I would like to get the rows where Type is equal to 'TypeA' and Key2 is Null that do NOT have a corresponding row in the table where Type is equal to 'TypeB' and Key2 is equal to Key1 from another row
I would like to return only the row where Key1 = 4 because that row meets the criteria of Type='TypeA'/Key2=NULL and does not have a corresponding row with Type='TypeB'/Key1=Key2 from another row.
I have tried this and it doesn't work...
SELECT t1.Key1, t1.Key2, t1.Type FROM Table1 t1 WHERE t1.Key2 IS NULL AND t1.Type LIKE 'TypeA' AND t1.Key1 NOT IN (SELECT Key1 FROM Table1 t2 WHERE t1.Key1 = t2.Key2 AND t1.Key1 <> t2.Key1 AND t2.Type LIKE 'TypeB')
We have a database and have 6-7 growing tables. All the tables have Primary and foreign key relation. I want to do partition based on the date column.
I need 3 partitions
First partition has to hold present data second partition need to hold the previous year data (SAS storage) Third partition need to hold all the old data and need to be in the archive database
I understand that first we need to disable the constraints (Indexes PK & FK) Then create partition function and partition schema Then Create the Constraints again
Need to change the datatype of existing column which has huge data.
I'm performing below steps
1. Create new column with correct datatype in the same table 2. copy data into new column 3. drop indexes on column 4. <<<>>> now the existing column also has many SP dependent and I do not wish to drop them. 5. rename existing column to xxx 6. rename new column to correct column 7. drop old column 8. make required indexes
I want to return all rows in table giftregistryitems with an additional column that holds the sum of column `amount` in table giftregistrypurchases for the respective item in table giftregistryitems.
What I tried, but what returns NULL for purchasedamount:
SELECT (SELECT SUM(amount) from giftregistrypurchases gps where registryid=gi.registryid AND gp.itemid=gps.itemid) as purchasedamount,* FROM giftregistryitems gi LEFT JOIN giftregistrypurchases gp on gp.registryid=gi.id WHERE gi.registryid=2
How can I achieve what I need?
Here are my table definitions and data:
/****** Object: Table [dbo].[giftregistryitems] Script Date: 02-05-15 22:37:11 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[giftregistryitems]( [id] [int] IDENTITY(1,1) NOT NULL,
I have created NONCLUSTERED index on table but my report is taking more time that's why i created columnstore NONCLUSTERED index on the same table but i have one query, if any table have row and column level index(same columns in index) . Which index query will consider.
In a stored procedure I dynamically create a temp table by selecting the name of Applications from a regular table. Then I add a date column and add the last 12 months. See attachment.
So far so good. Now I want to update the data in columns by querying another regular table. Normally it would be something like:
UPDATE ##TempTable SET [columName] = (SELECT SUM(columName) FROM RegularTable WHERE FORMAT(RegularTable.Date,'MM/yyyy') = FORMAT(##TempMonths.x,'MM/yyyy'))
However, since I don't know what the name of the columns are at any given time, I need to do this dynamically.
how can I get the column names of a Temp table dynamically while doing an Update?
SELECT PKGCUSTOM4, [1] AS [SKU1], [2] AS [SKU2], [3] AS [SKU3], [4] AS [SKU4], [5] AS [SKU5], [6] AS [SKU6], [7] AS [SKU7], [8] AS [SKU8], [9] AS [SKU9], [10] AS [SKU10], [11] AS [SKU11], [12] AS [SKU12], [13] AS [SKU13], [14] AS [SKU14], [15] AS [SKU15], [16] AS [SKU16], [17] AS [SKU17], [18] AS [SKU18], [19] AS [SKU19],
INSERT INTO #Activities VALUES('Field phase S14-04932-01') INSERT INTO #Activities VALUES('Phase reporting') INSERT INTO #Activities VALUES('Phase running') INSERT INTO #Activities VALUES('RD1')
I'm fairly new to SQL and am just setting up a Windows 8 app using an Azure SQL server. The issue I have is looking up a part number supersession and getting the latest number. One part number can have multiple supersessions (ie RTC5756 > STC8572 > STC3765 > STC9150 > STC9191 > SFP500160 ).The data I am supplied monthly has both the superseeded items and the supersession information in both columns and is not easy to decipher - for example:
The newest part number is kept in a separate table - called "source" - which in this instance is SFP500160. I need access to the latest part number but also to the part's previous numbers, due to the fact that some people may still be stocking them as an old part number and for them to search by. Is there an easy and efficient way of doing both a lookup for the supersessions and a join on the two tables to minimize the queries on the database?
I am having one store procedure which use to load data from flat file to staging table dynamically. everything is working fine.Staging_temp table have single column.All the data stored in that single column below is the sample Data.
date item_id grade in out ------------- --------------- ------- -------- ---------- 01-01-08 001 A 10 0 02-01-08 001 O 8 0 01-02-08 002 O 1 0 03-01-08 001 T 0 10 02-01-08 003 O 20 0 02-01-08 003 T 0 10 02-01-08 003 B 0 8
Result View =======
Item_id A B O T Total
001 10 0 8 -10 8 002 0 0 1 0 1 003 0 8 20 -10 18
where grade could be any Alphabet, the column of result query could varies each grade contain sum of (in-out) of all item_id of detail table.
I am new to T-SQL development and here's what I am trying to do.
declare @cmd varchar(255) set @cmd = 'select dbid from sys.sysdatabases where dbid not in (1,2,3,4)'
while (@cmd <=13) begin insert into #dbcheck values (@cmd) Set @cmd = @cmd + 1 end
but it is giving me an error
'Msg 245, Level 16, State 1, Line 3 Conversion failed when converting the varchar value 'select dbid from sys.sysdatabases where dbid not in (1,2,3,4)' to data type int.'
I want to compare two columns in the same table called start date and end date for one clientId.if clientId is having continuous refenceid and sartdate and enddate of reference that I don't need any caseopendate but if clientID has new reference id and it's start date is not continuous to its previous reference id then I need to set that start date as caseopendate.
I have log files that I am loading into a table with duration data in the format "xx hrs xx min xx sec". Only the parts that are required will be there so if duration is only 2 seconds , the column will show "2 sec".
I am trying to get the duration into in to do some analysis on it and I have come up with this query so far which returns the correct data but i am wondering if there is a way to do what I am trying in a more readable format.
CREATE TABLE #tmp(duration VARCHAR(20))
INSERT INTO #tmp VALUES ('1 hrs 20 min 12 sec'), --4812 sec ('48 sec'), --48 sec ('39 min 1 sec'), --2341 sec ('11 hrs 1 min 1 sec'), --39661 sec ('59 min 0 sec'), --3540 sec ('2 min 50 sec') --170 sec
In VBA, CLng(Now) will return the integer portion of a date CLng(Now) returns 41928, CDate(41928) then returns 10/16/2014. Is there something equivalent in SQL Server that will allow me to convert an integer value to a date?
In short, how can I convert a 100 year date to Gregorian (any format)?
I need my code to add the varchar CPUTM field + varchar CPUZIPTIM field which both has time values to see if greater than 2 hours. How do I do this when both fields are varchar. The value in CPUTM field is 335:55:20.97 duration time. My code is below.
CPUTM = 335:55:20.97 duration time CPUZIPTM = 0:00:01.96 duration time
select * FROM [SMF_DATA].[dbo].[System_Management_Facility] WHERE ((convert(varchar(13), CONVERT(time, CPUTM) + CONVERT(time, CPUZIPTM))) > '02:00:00.00')
