|
Hi,
I have 2 identical tables in 2 db's . Is there any easier way to compare the tables and give us the result?
Thanks
krishnan.s
|
|
|
|
|
If you want to find identical rows or differenting rows, have a look at EXCEPT and INTERSECT operands
Mika
The need to optimize rises from a bad design
|
|
|
|
|
i created a application that connects to sql server database.
i show data in a datagridview
i show info msg in a label that belongs to a statusstrip(about changes in datagridview, meaning database)
So, datagridview i fill with tables data and label i fill with c# validations ansers about what happened(OK: client added;ERROR: can´t delete product).
it works but,
i rather fill both (datagridview and label) with data from sql procedures.
any suggestions? maybe write a trigger, add a new table? i really don´t imagine a algorithm for that. 
(no need to post code)
nelsonpaixao@yahoo.com.br
trying to help & get help
|
|
|
|
|
We've been storing credit card expiration dates in varchar format as MMYY.
Well, before we would just create a new record and keep the old credit card number instead of deleting it (because in the same table we hold the address and it was a means of keeping customers previous addresses on file), but since we're switching to a new system we need to get rid of the old expired card numbers so we don't get charged for authorizations on cards that we KNOW are expired.
Lastly, we need this to work on a continual basis so when we upload an updated csv file to our processor, we *must* give them the date in the format of MMYY, any updates to what we're doing now need to factor that in... so when we pull that file, we're exporting directly from Enterprise Manager into a text file(csv) ready to import into their system.
So, my question is... how can we...
A. Leave the dates in varchar format and find out what cards expired and delete those records?
OR
B. Convert the varchar dates into datetime dates and delete the old card records without converting to a full date?
P.S. I'd like to add that some dates in these text fields aren't 4 digits. For example: 09/10 is in the database as 910.
* * * * Structure * * * *
==CardNum=|=CardExp==|====BillingAddress====
====int===|varchar(8)|====varchar (40)====
4444****** | 06/09 |123 Bobbaganoosh Lane
Any help would be appreciated!
"The whole world steps aside, for the man who knows where he is going. -bd"
|
|
|
|
|
Well, one way would be
declare @now4 varchar(4), @now3 varchar(4)
select @now3 = convert(varchar,datepart(month,dateadd(month,-1,getdate()))) + right(convert(varchar,datepart(year,getdate())),2)
select @now4 = right('0' + @now3,4)
you can now compare to @now3 (such as 808) or @now4 such as (0808)
Hopefully this gives you a starting point.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
that 910 thing is bad 
use datediff(),dateadd(),getdate() functions will help a lot.
i builted a movie rental application, some rents were up to 3 days, i had a report procedure when triggered counts day between rent day and delivery day or current day.
As you can guess i got a varchar that could be for exmple:
-1) meening i expect movie to be delivered tomorrow
0) meening i expect movie to be delivered today
+3) meening movie 3 days delay
in store) if movie is back to base.
nelsonpaixao@yahoo.com.br
trying to help & get help
|
|
|
|
|
Hello,
I have made a database in SQL 7 and copyied the Date.mdf and log.ldf files from the SQL\Data Directory and copied them to another system but I cannot oipen the database .
How should I open and copy Databases?
Are there any version problems?
Priya
|
|
|
|
|
The best way to do this is to perform a full backup on one database, then restore it to the other computer. Backup and restore will guarantee that the data is consistent. Opening and copying database files from the operating system could potentially cause data corruption by locking the datafile outside the scope of the database engine.
|
|
|
|
|
If one of the servers is SQL 2005 you can use Copy DataBase Wizard
|
|
|
|
|
Hello,
I was able to take the backup but restoring was giving problem. I got the backup on SQL 7 and restoring it on ver 8 .
What must be the problem?
Regards,
Pritha
|
|
|
|
|
You cannot directly copy database files (not actual files or backups) between different versions since they are not compatible.
If you want to move to a newer version, you can:
- take a backup from version 7
- use version 7 instance and restore the backup
- upgrade the instance to version 8
- take a backup from version 8
- restore the backup to desired instance of version 8 if the upgrade was done using *temporary* instance
or you can use DTS and model a transfer between servers (use DTS at the site where version is higher)
Hope this helps,
Mika
The need to optimize rises from a bad design
|
|
|
|
|
Thanks
so this was version problem
Pritha
|
|
|
|
|
I believe so. You're welcome 
Mika
The need to optimize rises from a bad design
|
|
|
|
|
|
I'm a newbie so go easy,
Here is the setup:
sharepoint services 3.0
sql 2005 server
Cognos marketing software for dashboards
I just started at this job and the marketing department would like to gather data from sharepoint. They have setup a survey using the survey template in sharepoint services 3.0(I wish they would of used infopath and a dedicated database but oh well). Because we don't have sharepoint 2007 portal and only services 3.0 they are using a product called Cognos. It creates charts and dashboards. They can collect from pretty much any type of database out there. My Question is If someone uses the default survey template inside sharepoint services, where in the world is that data being placed? I've tried to manually search and can not find any data. I have also searched for the last week or so on the net and all I can find is articles about sharepoint 2007 portal. It must be hidden in there some place. Can anyone help? 
|
|
|
|
|
humm no reply yet.. and this is a SQL database question by the way....
|
|
|
|
|
Hello,
I am using an Accordion to house textboxes on my page. The problem I am having when inserting it is saying it cannot find the control txtDescription. When writing code in the code behind I can type txtDescription so I can see it from there. For some reason it cannot find that textbox. Possibly many more since it stops at the first one and throws that error. Everything is in an Accordion besides the SQLDATASOURCE
HEre is the SqlDataSource:
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:GrantConnectionString %>"
InsertCommand="INSERT INTO [Grant_Table] ([Jurisdiction], [Input_Date], [Description], [FY], [Invoice_Number], [Inventory_Number], [Category], [Equipment_Location_Address], [Equipment_Location_City], [Equipment_Location_State], [Equipment_Location_ZIP], [Cost_PPI], [Cost_TAX], [Cost_Shipping], [Cost_TOTAL], [Received], [Equipment_Status], [AWIN_Serial], [AWIN_Operator], [AWIN_Call_Sign], [Contact_Name], [Contact_Phone], [Contact_Address], [Contact_City], [Contact_State], [Contact_ZIP], [Notes]) VALUES (@Jurisdiction, @Input_Date, @Description, @FY, @Invoice_Number, @Inventory_Number, @Category, @Equipment_Location_Address, @Equipment_Location_City, @Equipment_Location_State, @Equipment_Location_ZIP, @Cost_PPI, @Cost_TAX, @Cost_Shipping, @Cost_TOTAL, @Received, @Equipment_Status, @AWIN_Serial, @AWIN_Operator, @AWIN_Call_Sign, @Contact_Name, @Contact_Phone, @Contact_Address, @Contact_City, @Contact_State, @Contact_ZIP, @Notes)"
SelectCommand="SELECT [ID], [Jurisdiction], [Input_Date], [Description], [FY], [Invoice_Number], [Inventory_Number], [Category], [Equipment_Location_Address], [Equipment_Location_City], [Equipment_Location_State], [Equipment_Location_ZIP], [Cost_PPI], [Cost_TAX], [Cost_Shipping], [Cost_TOTAL], [Received], [Equipment_Status], [AWIN_Serial], [AWIN_Operator], [AWIN_Call_Sign], [Contact_Name], [Contact_Phone], [Contact_Address], [Contact_City], [Contact_State], [Contact_ZIP], [Notes] FROM [Grant_Table]">
<InsertParameters>
<asp:QueryStringParameter Name="Jurisdiction" QueryStringField="UserLocation"
Type="String" />
<asp:ControlParameter ControlID="txtInput_Date" Name="Input_Date"
Type="DateTime" />
<asp:ControlParameter ControlID="txtDescription" Name="Description"
PropertyName="Text" Type="String" />
<asp:QueryStringParameter Name="FY" QueryStringField="FY" Type="String" />
<asp:ControlParameter ControlID="txtInvoiceNumber" Name="Invoice_Number"
PropertyName="Text" Type="String" />
<asp:ControlParameter ControlID="txtInventoryNumber" Name="Inventory_Number"
PropertyName="Text" Type="String" />
<asp:ControlParameter ControlID="txtCategory" Name="Category"
PropertyName="Text" Type="String" />
<asp:ControlParameter ControlID="txtAddress" Name="Equipment_Location_Address"
PropertyName="Text" Type="String" />
<asp:ControlParameter ControlID="txtCity" Name="Equipment_Location_City"
PropertyName="Text" Type="String" />
<asp:ControlParameter ControlID="txtState" Name="Equipment_Location_State"
PropertyName="Text" Type="String" />
<asp:ControlParameter ControlID="txtZipCode" Name="Equipment_Location_ZIP"
PropertyName="Text" Type="String" />
<asp:ControlParameter ControlID="txtCostPPI" Name="Cost_PPI"
PropertyName="Text" Type="Decimal" />
<asp:ControlParameter ControlID="txtTax" Name="Cost_TAX" PropertyName="Text"
Type="Decimal" />
<asp:ControlParameter ControlID="txtShipping" Name="Cost_Shipping"
PropertyName="Text" Type="Decimal" />
<asp:ControlParameter ControlID="costLiteral" Name="Cost_TOTAL"
PropertyName="Text" Type="Decimal" />
<asp:ControlParameter ControlID="txtReceived" Name="Received"
PropertyName="Text" Type="DateTime" />
<asp:ControlParameter ControlID="txtEquipmentStatus" Name="Equipment_Status"
PropertyName="Text" Type="String" />
<asp:ControlParameter ControlID="txtAWINSerial" Name="AWIN_Serial"
PropertyName="Text" Type="String" />
<asp:ControlParameter ControlID="txtAWINOperator" Name="AWIN_Operator"
PropertyName="Text" Type="String" />
<asp:ControlParameter ControlID="txtAWINCallSign" Name="AWIN_Call_Sign"
PropertyName="Text" Type="String" />
<asp:ControlParameter ControlID="txtContactName" Name="Contact_Name"
PropertyName="Text" Type="String" />
<asp:ControlParameter ControlID="txtContactPhone" Name="Contact_Phone"
PropertyName="Text" Type="String" />
<asp:ControlParameter ControlID="txtContactAddress" Name="Contact_Address"
PropertyName="Text" Type="String" />
<asp:ControlParameter ControlID="txtCity" Name="Contact_City"
PropertyName="Text" Type="String" />
<asp:ControlParameter ControlID="txtContactState" Name="Contact_State"
PropertyName="Text" Type="String" />
<asp:ControlParameter ControlID="txtContactZip" Name="Contact_ZIP"
PropertyName="Text" Type="String" />
<asp:ControlParameter ControlID="txtNotes" Name="Notes" PropertyName="Text"
Type="String" />
</InsertParameters>
</asp:SqlDataSource>
Here is the error:
Source:
System.Web
Data:
System.Collections.ListDictionaryInternal
Inner Exception:
Location:
add.aspx
Message:
Could not find control 'txtDescription' in ControlParameter 'Description'.
|
|
|
|
|
I HAVE A COLUMN WHICH NAME IS "ID.ID2"
I WANT TO RENAME BY USE FOLLOWING QUERY:
SP_RENAME 'T1.ID2','ID'
BUT I GOT AN ERROR:
No item by the name of 'T1.ID2' could be found in the current database 'Zaid', given that @itemtype was input as '(null)'.
I ALSO TRY TO THIS COLUMN THROUGH THIS QUERY
ALTER TABLE T1
DROP COLUMN T1.ID2
BUT IT ALSO GIVES AN ERROR
Incorrect syntax near '.'.
please solve it
|
|
|
|
|
Its a typo
try
SP_RENAME 'T1.[ID.ID2]','ID'
(or whatever your column is called, it should be
SP_RENAME '{tablename}.{columnname}','{newcolumnname}'
Bob
Ashfield Consultants Ltd
|
|
|
|
|
thank u very much.
My problem have been solved
|
|
|
|
|
how to copy all records in one database to another database table in sql server 2005
|
|
|
|
|
insert into table2(column list) select (column list) from table1
not really difficult is it?
Bob
Ashfield Consultants Ltd
|
|
|
|
|
It can also be done via SQL Server Management Studio, Object Explorer, Tasks and SQL Import and Export Wizard. I use this method as I have around 10-13 tables and their data to be moved from one database to another.
|
|
|
|
|
Hi,
It is quite annoying problem and I couldn't figure out what is causing it. The problem is:
When I invoke a stored procedure inside a sub procedure, it is working fine. However, when I try to invoke a stored procedure when I submit the web page, none of stored procedures are working even connection string and and assigned parameters are true as it can be seen below. "I get too many arguments error"
When I use a query in the aspx page, it works but when I invoke a stored procedure I get error message. What could cause the issue related to SQL SERVERS EXPRESS 2008?
<br />
<br />
'THIS IS THE CODE INSIDE AN ASPX FILE<br />
<br />
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:HolidayDBConnectionString %>" <br />
InsertCommand='StoredProcedure1' <br />
<br />
<br />
SelectCommand="SELECT DISTINCT [ActivityName] FROM [Activities] ORDER BY [ActivityName]" <br />
InsertCommandType="StoredProcedure"><br />
<InsertParameters><br />
<asp:Parameter Name="GetActivityName" Type="String" /><br />
</InsertParameters><br />
</asp:SqlDataSource><br />
<br />
<br />
'HERE IS THE STORED PROCEDURE<br />
<br />
ALTER PROCEDURE dbo.StoredProcedure1 <br />
(<br />
@GetActivityName nvarchar(50)<br />
) <br />
AS<br />
<br />
<br />
INSERT INTO Activities VALUES (@GetActivityName)<br />
<br />
<br />
RETURN<br />
<br />
<br />
<br />
'HERE IS THE TABLE DEFINITION<br />
<br />
[PK] ActivityNumber bigint (IDENTITY COLUM) ALLOW NULLS FALSE<br />
ActivityName nvarchar(50) ALLOW NULLS FALSE<br />
<br />
<br />
Thanks.
What a curious mind needs to discover knowledge is noting else than a pin-hole.
|
|
|
|
|
First, change this line
INSERT INTO Activities VALUES (@GetActivityName)
to this
INSERT INTO Activities(ActivityName) VALUES (@GetActivityName)
and possibly try forcing your asp application to totally restart -I have seen similar problems due to caching with web apps.
Hope this helps
Bob
Ashfield Consultants Ltd
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
