Am a fresher and I got a scenario like i have to take create script of some tables of a database in sql server and I generated script to them and i have to add four new columns to the script and have to delete 2 columns from existing script for multiple tables at a time. It will take more time to do them individually.Is there any way to do this at a time and with in short period of time????
View 3 Replieshi,
in the front end i have two fields one is dropdown list another is checboxlist say dropdownlist consist of title name and checkbox list consists of sub title name.on selecting title name from dropdownlist i will be getting respective title name in checkboxlist. here when i select checkboxlist(suppose if there are 6 items in checkboxlist n if i select only 3)then my databse column should generate one dropdownlist selected item and 3 checkboxlist selected item.
Eg: if dropdownlist contains( title1,title2,title3) and checkboxlist contains(chk1,chk2,chk3,chk4,chk5,chk6). if i select title1 from dropdownlist i will be geeting 6 item in checkbox list) if i select only 3 items of checkbox list. then i should be able to create column name by title1,chk1,chk2,chk3
help is appreciated
I am using SQL express and Visual web developer on windows Vista.
When I try to create a new database the following message appears.
CREATE DATABASE permission denied in database master (error 262)
I log on to my computer as an administrator.
Help appreciated
Prontonet
I need to start encrypting several fields in a database and have been doing some testing with a test database first. I've run into problems when attempting to restore the database on either the same server (but different database) or to a separate server.
First, here's how i created the symmetric key and encrypted data in the original database:
create master key
encryption by password = 'testAppleA3';
create certificate test
with subject = 'test certificate',
EXPIRY_DATE = '1/1/2010';
create symmetric key sk_Test
with algorithm = triple_des
encryption by certificate test;
open symmetric key sk_Test decryption by certificate test;
insert into employees values (101,'Jane Doe',encryptbykey(key_guid('sk_Test'),'$200000'));
insert into employees values(102,'Bob Jones',encryptbykey(key_guid('sk_Test'),'$500000'));
select * from employees
--delete from employees
select id,name,cast(decryptbykey(salary) as varchar(10)) as salary from employees
close all symmetric keys
Next I backup up this test database and restore it to a new database on a different server (same issue if restore to different database but on same server).
Then if i attempt to open the key in the new database and decrypt:
open symmetric key sk_Test decryption by certificate test;
I get the error: An error occurred during decryption.
Ok, well not unexpected, so reading the forums, i try doing the below first in the new database:
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
Then I try opening the key again and get the error again:
An error occurred during decryption.
So then it occurs to me, maybe i need to drop and recreate it so i do
drop symmetric key sk_test
then
create symmetric key sk_Test
with algorithm = triple_des
encryption by certificate test;
and then try to open it.
Same error!
So then i decide, let's drop everything, the master key, the certificate and then symmetric key:
drop symmetric key sk_test
drop certificate test
drop master key
Then recreate the master key:
create master key
encryption by password = 'testAppleA3';
Restore the certificate from a backup i had made to a file:
CREATE CERTIFICATE test
FROM FILE = 'c:storedcertsencryptiontestcert'
Recreate the symmetric key again:
create symmetric key sk_Test
with algorithm = triple_des
encryption by certificate test;
And now open the key only to get the error:
Cannot decrypt or encrypt using the specified certificate, either because it has no private key or because the password provided for the private key is incorrect.
So what am I doing wrong here? In this scenario I would appear to have lost all access to decrypt the data in the database despite restoring from a backup which restored the symmetric key and certificate and i obviously know the password for the master key.
I also tried running the command
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
again but this does not resolve the issue.
Thx.
I have a colm day and colm sales, I would like to have a column for each day of week i think i can use when then but not sure how
View 3 Replies View Relatedi have been trying to create new columns in a tabinvoice details using row data from another table and additional input from another form. Problem is i get no sql errors m database conection is ok usually works but doesn't write any new columns or values to the tblinvoicedetails here is the code i am using i hope someone ca tell me where i am going wrong.
<table width="100%" cellspacing="0" cellpadding="2" border="0">
<form name="form2" method="post" action="index.php" onsubmit="return v.exec()">
<?
$cid = mysql_real_escape_string($_GET['cid']);
$query = "select * from tblcustomerdetails where intCustomerID = '$cid'";
$result = mysql_query($query);
$row = mysql_fetch_array($result);
$cid = $row["intCustomerID"];
?>
<tr>
<td><?print_r( $row['vcCCompanyName'])?></td><td align="left">Tel: <?print_r( $row['vcCDPhone'])?></td>
</tr>
<tr>
<td><?print_r( $row['vcCStreetNo'])?> <?print_r( $row['vcCStreet'])?></td><td align="left">Fax: <?print_r( $row['vcCFaxNo'])?></td>
</tr>
<tr>
<td><?print_r( $row['vcCCity'])?></td><td align="left">Email: <?print_r( $row['vcCEmail'])?></td>
</tr>
<tr>
<td><?print_r( $row['vcCState'])?></td><td> </td>
</tr>
<tr>
<td><?print_r( $row['vcCPostalCode'])?></td><td> </td>
</tr>
<tr>
<td><?print_r( $row['vcCCountry'])?></td><td> </td>
</tr>
</table>
<br />
<table width="100%" cellspacing="0" cellpadding="2" border="0">
<tr>
<td> </td><td><b>Code</b></td><td><b>Name</b></td><td><b>Description</b></td><td><b>Price</b></td><td colspan="2" align="center"><b>Action</b></td>
</tr>
<?
$accountsql="select * from tblpricedetails where intCustomerID=$cid order by intProductID asc";
$tempdata=$DB_site->query($accountsql);
$count =$DB_site->num_rows($tempdata);
while($rowdata=$DB_site->fetch_array($tempdata))
{
$intPriceID = $row["intPriceID"];
$intProductID = $row["intProductID"];
$vcProductCode = $row["vcProductCode"];
$vcProductName = $row["vcProductName"];
$vcProductDescription = $row["vcProductDescription"];
?>
<tr>
<td class="accountsumb">
<?=$rowdata["intProductID"]?>
</td>
<td class="accountsume">
<?=$rowdata["vcProductCode"]?>
</td>
<td class="accountsumc">
<?=$rowdata["vcProductName"]?>
</td>
<td class="accountsumc">
<?=$rowdata["vcProductDescription"]?>
</td>
<td class="accountsumc">
<?=number_format($rowdata["ftAmount"],2,".",",");?>
</td>
<td class="accountsumc" align="right">
<input type="text" size="10" name="Quantity" />
</td>
<td class="accountsumc" align="center">
<input type="text" size="10" name="total" />
</td>
</tr>
<?
}
?>
<? if($count==0)
{
?>
<tr bgcolor="#EFEFEF" class="smallblack">
<td colspan="7" align="center" bgcolor="#EFEFEF" class="redtext">This user has no product data. </td>
</tr>
<?
$insertdate = mysql_query("INSERT INTO tblinvoicedetails (intProductID,vcProductCode,vcProductName,vcProductDescription,ftAmount) VALUES(".$row['intProductID'].",".$row['vcProductCode'].",".$row['vcProductName'].",".$row['vcProductDescription'].",".$row['ftAmount'].")") or die(mysql_error());
}
?>
<tr>
<td colspan="7" align="center"><br />
<input name="submit" type="submit" class="button" value="Submit" />
</td>
</tr>
<tr>
<td colspan="7" align="center">
<input class="button" type="reset" name="reset" value="Reset" />
</td>
</tr>
</table>
</form>
Absolute beginner to SQL here...
I'm try to create an array of columns and then refer to these columns at the asp.net level.
Can't seem to find this in the SQL literature...so I'm probably looking at this all wrong.
Thanks in advance.
Larry
the subject pretty much says it all, I want to be able to do the following in in VB.net code):
{[If [table with this name] already exists [in this sql database] then [ don't create another one] else [create it and populate it with these values]}
How would I do this?
Can I dynamically (from a stored procedure) generatea create table script of all tables in a given database (with defaults etc)a create view script of all viewsa create function script of all functionsa create index script of all indexes.(The result will be 4 scripts)Arno de Jong,The Netherlands.
View 1 Replies View RelatedHello.
Could anyone tell me why it is not possible to create a foreign key on two columns those references on 2 columns in another table? Those 2 columns have each a unique constraint.
I have:
CREATE TABLE T_PK (ID1 INT CONSTRAINT CHK_UNIQUE1 UNIQUE,ID2 INT CONSTRAINT CHK_UNIQUE2 UNIQUE)
CREATE TABLE T_FK (ID1 INT, ID2 INT)
And I want to do:
ALTER TABLE T_FK ADD CONSTRAINT CHK_FK FOREIGN KEY (ID1, ID2) REFERENCES T_PK (ID1,ID2)
I see no reason why this is not working because always
a row in the table T_FK referencing only one row in table T_PK.
Thank you.
Have a nice day.
I need to create a view that counts in one of the columns.
The tricky part for me is that it should only count to three, then start on 1 again and count to three again.
Example
1 - car
2 - bike
3 - Motorbike
1 - Boat
2 - Airplane
3 - Motorboat
1 - Bicycle
I have a table that I cannot allow a computed field to exist on (due to a 3rd party software), so I am thinking I could create a view with a computed field that is persistent, is that possible?
the syntax below will not work, I am not even sure if this is possible, but if it can work, that would be great.
I am wanting to get the sum of jetfoot1, 2 & 3 and have the total added up as "total"
create view ViewSumReport as
select JETFOOT1,JETFOOT2,JETFOOT3,(JETFOOT1+JETFOOT2+JETFOOT3)as [total] persisted
from dbo.fielddata
GO
Hi,I have a table with the following columns:ID INTEGEDR,Name VARCHAR(32),Surname VARCHAR(32),GroupID INTEGER,SubGroupOneID INTEGER,SubGroupTwoID INTEGERHow can I create a rule/default/check which update SubGroupOneID &SubGroupTwoID columns when GroupID for example is equal 15 onMSSQL2000.It is imposible to make changes on client, so I need to checkinserted/updated value of GroupID column and automaticly updateSubGroupOneID & SubGroupTwoID columns.Sincerely,Rustam Bogubaev
View 4 Replies View RelatedHi,
how to dynamically create columns for a table
Hi!
I have a select question that look like this:
DECLARE @ID uniqueidentifier;
SET @ID = '40bd3052-60f4-414a-99df-ca882c128493';
SELECT
rp.ID AS ReportPackId
,rp.SupplierPartyIdentifier AS SupplierPartyIdentifier
,rpap.AdditionalPartyIdentifier AS AdditionalPartyIdentifier
FROM
ReportPack rp
INNER JOIN ReportPackAdditionalParty rpap
ON rpap.fk_ReportPack = rp.ID
WHERE rp.ID = @ID
The result a get when running the select question is:
ReportPackId
SupplierPartyIdentifier
AdditionalPartyIdentifier
40BD3052-60F4-414A-99DF-CA882C128493
String
addPartyId1
40BD3052-60F4-414A-99DF-CA882C128493
String
addPartyId2
My problem is that the result I want is the following:
ReportPackId
SupplierPartyIdentifier
AdditionalPartyIdentifier
AdditionalPartyIdentifier
40BD3052-60F4-414A-99DF-CA882C128493
String
addPartyId1
addPartyId2
I always know that the ReportPackId and SupplierPartyIdentifier will be identical for all rows because of the Where condition, and therefore I want all AdditionalPartyIdentifiers to be in columns instead of a new row. Is this possible?
I need to create table in sql server 2005 database, I have plenty of columns. Well I know how to create them manually. but does any body know or tried before to create columns automatically, or through some batch process. I have column names already typed in TXT file (comma delimited).
all I am trying to avoid that typing work.
any advice.
Thanks
I have some code that dynamically creates a database (name is @FullName) andthen creates a table within that database. Is it possible to wrap thesethings into a transaction such that if any one of the following fails, thedatabase "creation" is rolledback. Otherwise, I would try deleting on errordetection, but it could get messy.IF @Error = 0BEGINSET @ExecString = 'CREATE DATABASE ' + @FullNameEXEC sp_executesql @ExecStringSET @Error = @@ErrorENDIF @Error = 0BEGINSET @ExecString = 'CREATE TABLE ' + @FullName + '.[dbo].[Image] ( [ID][int] IDENTITY (1, 1) NOT NULL, [Blob] [image] NULL , [DateAdded] [datetime]NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]'EXEC sp_executesql @ExecStringSET @Error = @@ErrorENDIF @Error = 0BEGINSET @ExecString = 'ALTER TABLE ' + @FullName + '.[dbo].[Image] WITHNOCHECK ADD CONSTRAINT [PK_Image] PRIMARY KEY CLUSTERED ( [ID] ) ON[PRIMARY]'EXEC sp_executesql @ExecStringSET @Error = @@ErrorEND
View 2 Replies View Related
I'm new to using SSIS and have been reading and learning slowly how to use it. I'm trying to create an identical copy of our database for reporting. I've used the Import/Export wizard, but have had some issues with foreign keys and with sql_variant columns.
I've tried searching for anything but haven't had any luck as of yet. I guess I don't even know where to start or what to look for.
Any help would be appreciated. Thanks!
Hello, I will explain myself further. I want to make my table in such a way that no two colums have the same value for example:
Row 1 - Column 1 = "cool"
Row 1 - Column 3 = 91
Row 3 - Column 1 = "cool"
Row 3 - Column 3 = 91
I dont care about one column having duplicate values, I want to protect against Column 1 and 3 having the same values on other rows. Is this possible to do in sql server?
I have a very large SQL Server table and want to pull all 50 columns that are in a certain row because something in that row has invalid varchar and is causing runtime errors. It is row 9054378701 and I am not sure how to create a query to pull that specific row and all 50 columns.
View 3 Replies View Related
Hi,
I have a sproc that returns somevalues and everything is working fine... and in my reports i am assigning the header data (in a detail column) based on the some feilds in the sproc... and there around 20 feilds that i want to show... but at a given time i am pretty sure that there wont be more than 10 fields that will have data.
So is it possible that show only the columns that have data in it and sometimes if there is less that 5 - 6 fields.. i want to realign the widths in those tables..
any help is appreciated..
Regards
Karen
I am trying to create a Unique Constraint on a SQL Server 2005 table where the uniqueness is based on 2 columns.
Could anybody provided some help on how I could enforce this on an existing table (link, or example) I have been looking around without luck.
Thanks in advance
John.
Hi everyone,
I need to create temporary table in one of the SP.The problem is that number of columns in table will vary depanding on input in SP.
How can i create table with variable number of columns?
Thanks,
Alex
Hi there,
i have a requirement that a temporary table contains dynamic columns depending on where condition.
my actual table is like
Key
Value
X1
x
X3
x
X5
x
Y1
y
Y2
y
when user select x, the input variable passed to stored proc and the result is shown like
column names
X1 X3 X5 as column headers.
the select query is from temporary table.
these out put is based on the user selection. so the temporary table created with columns dynamically.
please help me out.
please let me know if you didn't understand.
thanks
Praveen.
I found something related to row coloring at :: [URL] ....
I went through all the properties for column but cannot achieve my goal. SO in my case .. the no of rows are fixed .. but columns vary .. so i want the alternate columns to be of different color (say white and silver)
I have a simple script where I want to pull GLAcct, GLDesc and Amounts by Period. I want my results to look like attached snip.
I tried playing around with the dates; however, I'm receiving errors. Just to note that when I ran for 07/01/14 - 07/31/14 with the transaction date in where clause I was able to retrieve the correct results. Now I want to expand to get a view set up for the whole year....automation!
select
gl_account.id as GLAcct,
gl_account.descr as GLDesc,
sum(gl_ledger.amount_n) as Net
from gl_account
[Code] ....
I am looking to create a script that will go through a table a pick out the necessary columns to create a unique record. Some of the tables that I am working with have 200 plus columns and I am not sure if I would have to list every column name in the script or if they could be dynamically referenced. I am working with a SQL server that has little next to no documentation and everytime I type to mere some tables, I get too many rows back.
View 4 Replies View RelatedI have the following query that displays 2 values. I want to add a column with the percentage ([Providers With Security]
/ProviderTotal) * 100
SELECT (SELECT COUNT(DISTINCT NPI) FROM HS140_Rpt_Tmp_ForSummary WHERE Market = s.Market) AS ProviderTotal,COUNT(DISTINCT NPI) AS [Providers With Security]
FROM HS140_Rpt_Tmp_ForSummary s
WHERE s.[Security] = 'Yes'
GROUP BY Market
How can I do this?
hai, iam new to ssrs please help me,
my report having 10 columns, i can explain my need with example that is, assume like this customer id is first column of my table customer id is 101 it having 3 departments a1,b1,c1,perticular department that is a1 having emp1,b1 having emp2,c1 having emp 3.
i want output like this when clicking + customer id driildwon it display 3 departments taht is a1,b1,c1, when +a1 drill down clicking i need to dispaly emp1, corresponding b1 to emp2 , c1 to emp 3.
above explanation is only one column of the table, like that iam also displaying this driiling procedure for remaining different columns.
and i need to display customer information by weekly,daily,monthly,yearly at bottom of the report
please give which logic used in creating format like above drilldown report which having multiple drilldowns for all columns in a table
if any body give procedure for creating fromat for drilldown report which having multiple drilldowns for all columns in a table is appriciate.
thanks
jacks
I'm having a tad bit of trouble getting output from an asynchronous component that I've written and am looking for some insight.
This component takes in a name string passed from upstream and parses the name components into standardized output fields. I'm using an asynchronous component because if the name string contains two names ("Fred & Wilma Flintstone") I'm outputting one row for Fred and one for Wilma. I've gotten it to run and with debugging have observed what appeared to me to be proper execution, but zero rows are flowing out of it.
In my ProvideComponentProperties method, I add the three fields and there associated metadata to the OutputColumnCollection. Is this method where this should occur? It's before the PrimeOutput method, so I didn't know if I should be creating the output columns in ProcessInput (i.e., after the output buffer is provided by PrimeOutput.)
In ProcessInput, I'm using AddRow for each input row and another if it contains a second name, setting the value for each index using the buffer's SetString method, to no avail. I can observe it to this point, but then don't know what's in that output buffer (if I'm using the wrong buffer index value, etc)
Thanks.
I'm trying to create a fairly simple custom transform component (because I've read that's the easiest one to create) which will take one column from a flat file source and based on the first row create the output columns.
I'm actually trying to write a component that will solve the now well known problem with parsing CSV files in SSIS. I have a lot of source files and all have many columns so a component that can read in the first line from the CSV file and create the output columns automatically will save me lots of time when migrating the old DTS packages.
I have the basic component set up but I'm stuck when trying to override the OnInputPathAttached method because I don't know how to use the inputID to get the first line from the input (the buffer).
Are there any good examples for creating output columns dynamically based on the input buffer?
Should I just give up on on the transform and create a custom source component instead?
I'm wanting to create reports in SSDT 2012 which is connected to a 2008R2 database. I want to have parameters on for my reports where a user is able to select a year such as 2014. Unfortunate my table containing the data has two columns with a date value. the first is of the int type and contains an epoch formatted date. The second is a varchar type and shows the date as 2015-07-01 08:00:00. I would like to be able to write a query to return the year, monthnumber and daynumber from either of these columns.
View 5 Replies View RelatedI 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.