Is there a way to short cut through the fields in a table when updating them or inserting?
ie. I have a table with 78 fields and could grow with time. Is there a way to run the UPDATE or INSERT TO [table](blah....) without listing all the table fields?
I am very new to SQL Server 2005. I have created a package to load data from a flat delimited file to a database table. The initial load has worked. However, in the future, I will have flat files used to update the table. Some of the records will need to be inserted and some will need to update existing rows. I am trying to do this from SSIS. However, I am very lost as to how to do this.
Any suggestions?
Hello friends,
I am new to the SQL Server 2005 development.
From last 1 week or so, i have been facing very strange problem with my sql server 2005s database
which is configured and set on the hosting web server. Right now for managing my sql server 2005 database,
i am using an web based Control Panel developed by my hosting company.
Problem i am facing is that, whenever i try to modify (i.e. add new columns) tables in the database,
it gives me error saying that,
"There is already an object named 'PK_xxx_Temp' in the database. Could not create constraint. See previous errors.
Source: .Net SqlClient Data Provider".
where xxx is the table name.
I have done quite a bit research on the problem and have also searched on the net for solution but still
the problem persist.
Thanks in advance. Any help will be appreciated.
how can i convert the varchar value to a column of data type int?
i am trying to do this
declare @w varchar(50)
select @w = col1 from myTable
select colname from mtable
where mtableID in (@w)
but the mtableID is a int
and i always got
Syntax error converting the varchar value to a column of data type int.
thanks for everyone for helping
can anyone tell me do i have to use sqldmo in asp.net C# to backup or restore database?
and how can i execute the *.sql file in asp.net C#?
thanks
When I get the results from my query, some of the columns labels are too short and look like this "ran....". How do I extend the cells??
Thanks
US Navy - We are fueled, armed, and go for launch.
Hi all. Is it possible to convert the date to shortdate?
select e.hireddate from hremployees e
it returns 2/07/2007 12:00:00 AM
How can i neglect the time?
Thanks
-Ron-
Problems Encountered Upgrading SQL 2005 SP1 to SQL 2005 SP2 (9.0.3042.1):
In Summary:
Maintenance Plans Broken
Database Mail Broken
Reporting Services Report Viewer web part displays US Format dates instead of report design English-Australian. .
In Detail:
Microsoft SQL Server 2005 SP2 was downloaded from Microsoft.com. This download occurred after 5 March 2007 and the file version was 9.0.3042.1 (SQLServer2005SP2-KB921896-x86-ENU.exe)
During the Installation of SP2 by a Domain Administrator an error occurred on the Database Engine. A Hotfix Exception was raised and the setup completed with errors.
The Error Log reported a problem:
----------------------------------------------------------------------------------
Product : Database Services (MSSQLSERVER)
Product Version (Previous): 2047
Product Version (Final) :
Status : Failure
Log File : C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGHotfixSQL9_Hotfix_KB921896_sqlrun_sql.msp.log
Error Number : 29534
Error Description : MSP Error: 29534 Service 'MSSQLSERVER' could not be started. Verify that you have sufficient privileges to start system services. The error code is (1067) The process terminated unexpectedly.
----------------------------------------------------------------------------------
The SQL Server service could not be manually started. An error was reported in the event viewer:
One or more files do not match the primary file of the database. If you are attempting to attach a database, retry the operation with the correct files. If this is an existing database, the file may be corrupted and should be restored from a backup.
It was noted that a new file mssqlsystemresource.ldf had been copied to the %data% folder.
Note: As part of separating data and log files, both the master and mssqlsystemresource databases had been previously moved to another volume %data% whilst their respective log files had been moved to a separate volume also %log%. This had occurred after SP1 was initially installed and months prior to the SP2 install using Microsoft KB articles to detach and reattach the databases. It appears that the SP2 installer does not work correctly if mssqlsystemresource mdf and ldf files are not in the same location.
Whilst the Engine could not be started €“ the new mssqlsystemresource.ldf file was manually moved to the %log% file, overwriting the previous version.
The SQL Server Service was now able to start without any errors.
Since the SQL Server Service has been started two major errors have become apparent:
The following error is recorded when trying to create a new maintenance plan:
TITLE: Microsoft SQL Server Management Studio
------------------------------
Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Invalid column name 'from_msx'.
Invalid column name 'has_targets'. (Microsoft SQL Server, Error: 207)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3042&EvtSrc=MSSQLServer&EvtID=207&LinkId=20476
Also when using the maintenance plan wizard the following error is recorded when the Wizard attempts to save the plan at the end:
TITLE: Maintenance Plan Wizard Progress
------------------------------
Saving maintenance plan failed.
------------------------------
ADDITIONAL INFORMATION:
Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476
------------------------------
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Invalid column name 'msx_job_id'. (Microsoft SQL Server, Error: 207)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3042&EvtSrc=MSSQLServer&EvtID=207&LinkId=20476
------------------------------
Database Mail is also broken on the server. The Database Mail Log reports an €œActivation Failure€? when a test email is sent. (the email does not arrive). The SQL Log and event viewer also report the following message:
The activated proc [dbo].[sp_sysmail_activate] running on queue msdb.dbo.ExternalMailQueue output the following: 'Error executing extended stored procedure: Invalid Parameter'
Another error affects Reporting Services reports that are accessed using the SharePoint V2.0 Web parts ( rsWebparts.cab) . The date picker in the Report Viewer web part now only recognises US format dates. It is important to note that the same reports run through the Report Manager web site work correctly using DD/MM/YYYY (English-Australian ) date format for the report picker.
It is noted that the rsWebparts.cab file was not updated by SQL 2005 SP2 install. However, the behaviour of the Report Viewer web part has definitely been affected by the SP2 install.
Are the first two issues related? Are they due to the fact that mssqlsystemresource mdf and ldf files had been moved to an unexpected location prior to SP2? How can this be fixed?
Are the Reporting Services SharePoint (V2) web parts going to be updated? We are not in a position to upgrade our Portal 2003/SharePoint Servcies V2 installation at the present time.
Here is a fairly basic question in which I'd like assistance. In determining a standard for a schema, for simplicity, my thinking is that using the long data type would meet our purposes.
Are there any thoughts on mapping out where we would not need to take advantage of long vs short? I realize the space but for this particular table, space won't an issue.
Thanks!
Hi All,
I have a Problem while updating one table data from another table's data using sql server 2000.
I have 2 tables named TableA(PID,SID,MinForms) , TableB(PID,SID,MinForms)
I need to update TableA with TableB's data using a single query that i have including in a stored procedure.
If the id1 will change in table1 it should also change the corresponding id1 field in table2 it does not do anything.
CREATE TRIGGER [dbo].[IDCHANGE]
ON [dbo].[table1]
AFTER UPDATE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
[Code] .....
We have two tables with names X and Y.
X has a,b columns. And Y has c,d columns.
I want to update b column in X table with the values from d column in Y table on condition X.a=Y.c.
Hello, i have database front end is Access, and backend is sql server. The front end, there are two dropbox, called 'CountyLongName', and 'CountyShortName'. I want to: when i select a countyLongName, there it will pop up the exact CountyShortName in anohter field.
For example, when i click Howard Couty in the 'CountyLongNmae', it will automatically display "HW" in the 'CountyShortName" Field. How can i do that? Thanks.
any keyword shortcut to exec a sql job?
do i have to exec step individually when there are more than 2 steps?
CREATE PROCEDURE MyBooks_Selling
(
@MemberID SMALLINT
)
AS
SELECT * FROM v_BookInfo_Sellers_Extended WHERE MemberID=@MemberID
GO
GRANT EXEC
ON MyBooks_Selling
TO bto
GO
CREATE PROCEDURE MyBooks_Buying
(
@MemberID SMALLINT
)
AS
SELECT * FROM v_BookInfo_Buyers_Extended WHERE MemberID=@MemberID
GO
GRANT EXEC
ON MyBooks_Buying
TO bto
GO
Is there a way to make it so I could combine those 2 prcedures and choose which table i would like to select from based on another input parameter? I tried it that way but it didnt work...so im asking here to make sure
thx
Hi all,
I have a view with a datetime column,
when viewing the column data the format is:
" 2006-06-12T12:00:00.0000000+03:00 "
while i'm only want to see the date part
(that is, 2006-06-12, and if possible in the format: dd/mm/yyyy)
now, when i'm using convert( varchar(10), [column_name],101)
i'm getting the format i need but the problem is that i'm
"losing" the date type, is it possible to view it as the
format i stated and still as a datetime type ??
Regards,
Pit
This is a question concerning query optimisation. Sorry if it's a bit long,but thanks to anyone who has the patience to help - This is my first posthere...If I have two tables: 'tblContact' and 'tblCategory' where categories arelike:Code Name010101 Short010102 Fat010103 StupidThe junction table 'tblConCat' has fields CctConID, CctCatCode to tell mewhich contacts have which category codes. These are nchar(6) fields, if itmakes any difference.If I need to find all people who are short, fat and stupid I can see twoways:Solution One:SELECT tblContact.* FROM tblContact WHEREConID IN (SELECT CctConID FROM tblConCat WHERE CctCatCode='010101') ANDConID IN (SELECT CctConID FROM tblConCat WHERE CctCatCode='010102') ANDConID IN (SELECT CctConID FROM tblConCat WHERE CctCatCode='010103')Solution Two:Build a helper table which contains the codes I'm looking forSELECT tblContact.* FROM tblContactWHERE ConID IN(SELECT CctConIDFROM tblConCat INNER JOIN tblHelperON tblConCat.CctCatCode = tblHelper.HlpCatCodeGROUP BY CctConID HAVING Count(*)=3)I have tried them both out and although I looked at the query analyzer itprovided more information than I knew what to do with. In practise theyboth provide similar working times (I wait about a second) but I thought thefirst looked rather inefficient and thought my helper table might help.There are about 30,000 contact records, 180 category records and 120,000junction table records.All I am looking for comments on any pros and cons of these two approaches -does one look that bad? The database was migrated from Access where thehelper table really did help, but using SQL Server I might not need it.Thanks again, if you got this far!
View 16 Replies View RelatedI have a suite of several SSRS reports that differ essentially on the stored procedure called. All have very the same one-page header block and all have a footer. Those where a large number of pages are produced, page without fault when printed or converted to PDF.
Two page reports often break down on printing, so that page 1 only has the header and a footer and all the detail lines are printed on page2 (together with the footer). However on the screen the report paged correctly.
I have checked all the obvious problems such as page dimensions exceeding that of the print medium. (A series of such problems was fixed earlier in development.)
I have observed this behaviour with SQL2005 SP1 and SP2 (i.e. SP2 did not provide any fix in this area). The host OS for development and production is Windows 2003 server. The tests were done by viewing in IE6 and the fault only became apparent when going to print preview, printing or in PDF production.
This has the appearance of a bug in SSRS hereby intermediate length reports are not handled correctly. It appears to happen when the amount of detail lines just fills page 2. As soon as the report has sufficient lines to be 3 pages long (page 1 is header plus some details lines plus footer, page 2 is full of detail lines plus footer and page 3 is remaining detail lines plus footer then the problem is no longer seen! Where the detail lines will all fit on page 1, then the problem is not seen.
I observe this variation in behaviour since I am able to switch between different sized datasets and thus switch between the different scenarios described above. I should also note that the reports are in landscape mode.
Hi
Im trying to create an update statement which references two tables (join) and has a CASE clause attached. Not sure where im going wrong...
Using T-sql!!!
update import set import.gone =
from import
inner join stat
ON stat.id = import.id
CASE
WHEN stat.A = import.field2 THEN import.gone = sec.A
WHEN stat.B = import.field2 THEN import.gone = sec.B
WHEN stat.C = import.field2 THEN import.gone = sec.C
WHEN stat.D = import.field2 THEN import.gone = sec.D
WHEN stat.E = import.field2 THEN import.gone = sec.E
WHEN stat.F = import.field2 THEN import.gone = sec.F
ELSE import.gone = null
END
Any help would be greatly appreciated
I am trying to update a table in one database with data from a temporary table which i created in the tempdb.
I want to update field1 in the table with the tempfield1 from the #temp_table
The code looks something like this:
Use master
UPDATE [dbname].dbo.table
SET [dbname].dbo.table.field1 = [tempdb].dbo.#temp_table.tempfield1
WHERE ( [dbname].dbo.table.field2= [tempdb].dbo.#temp_table.tempfield2
AND [dbname].dbo.table.field3= [tempdb].dbo.#temp_table.tempfield3
AND [dbname].dbo.table.field4= [tempdb].dbo.#temp_table.tempfield4)
I get the following error:
Msg 4104, Level 16, State 1, Line 2
The multi-part identifier "tempdb.dbo.#temp_table.tempfield2" could not be bound.
Msg 4104, Level 16, State 1, Line 2
The multi-part identifier "tempdb.dbo.#temp_table.tempfield3" could not be bound.
Msg 4104, Level 16, State 1, Line 2
The multi-part identifier "tempdb.dbo.#temp_table.tempfield4" could not be bound.
What is wrong?
Hi I am trying to create stored proc and having some performance issue.
Here is the requirement,
Stored Proc parameters A,B
A is required.
B is optional. The default value for B is 0.
Here is the code inside stored proc:
IF (B = 0)
SET B = 1
SELECT * FROM TABLE1
INNER JOIN TABLE2 ON TABLE1.Column1 = TABLE2.Column1
WHERE A= 2008
AND (table2.ColumnB = B or 1 = B)
If I am passing B as 20 I am having performance issue to pull the data.
If I replace B with any other number the query is working fine.
Hello to all,Below the sample code:declare @arg_szMsgText ntextset @arg_szMsgText = isnull(@arg_szMsgText, N'unknown message')Now the error message I get:"The assignment operator operation cannot take a ntext data type as anargument."Explanation:I'm using it in a SP. I was trying thousand things to make it running,and the only way I maked was, I get it when I set the return value fromisnull fucntion to nvarchar or varchar variable.The problem is, the field in DB, where the value of @arg_szMsgText iswritten) is ntext, so I need to use this format.In the description of isnull function (help) is written:----------------------------------------------------Argumentscheck_expressionIs the expression to be checked for NULL. check_expression can be ofany type.replacement_valueIs the expression to be returned if check_expression is NULL.replacement_value must have the same type as check_expresssion.Return TypesReturns the same type as check_expression.-----------------------------------------------------so ite means, I should be able to use this in such a way ...I know I can assign the value in other way (like by if @variable isnull ....), but if there is already function doing this, why not to useit, if it helds the code more clear?Any ideas? Maybe I'm doing something wrong.Matik
View 3 Replies View RelatedHello all, this is my first post, and I think I've found something interesting between sql 2005 and 2000. Thanks in advance for any help you can provide.
I am attempting to migrate a system from SQL 2000(v 8.00.2039 - Enterprise Edition) to SQL 2005 (v9.0.2047 - Standard Edition ), and a few of my most complex queries are generating errors. I was able to isolate the problem to a fairly simple query that works in SQL 2000, but generates an error in SQL 2005.
I believe that the problem has something to do with how short-circuiting is handled in the WHERE clause. I'm not sure if short-circuiting is really going on when these statements are parsed, but it seems to describe the situation well.
To replicate the problem, first create the following table and fill it with values:
----------------------------------------------
create table OrgView (ORGNAME varchar(100), QID int, ANSWER varchar(100))
insert into orgview (orgname, qid, answer) values('org1', 120, '1')
insert into orgview (orgname, qid, answer) values('org2', 94, '2006-06-06')
insert into orgview (orgname, qid, answer) values('org3', 98, 'free kevin')
----------------------------------------------
The second value (org2) contains a date, but the rest do not.
Under both systems, the following contrived query works fine:
SELECT orgname FROM OrgView
WHERE (QID = 94
AND CONVERT(DATETIME, Answer) = '2006-06-06')
It would seem, that when QID = 94, the expression short circuits, and the CONVERT statement doesn't run. This is good, because the CONVERT statement would throw an error whenever 'Answer' doesn't contain a date.
However, if modify the query by adding an OR to the end like this:
SELECT orgname FROM OrgView
WHERE (QID = 94
AND CONVERT(DATETIME, Answer) = '2006-06-06')
or QID=98
then the query fails on SQL 2005 with a 'Conversion failed when converting datetime from character string.' error.
On SQL 2000, the short circuiting works fine, and the above query returns 'org2' and 'org3'.
I know shortcircuiting may not be the best way to describe the set calculus going on in the background, but can anyone explain what is going on here? Is there any way to make SQL 2005 work like SQL 2000 in this specific case?
Thanks,
-Adam
Hi!We have SQL Server 2000 on virtual machine (slowly). We run a pair ofstored procedures from ADO.NET 2.0 (WebService):- first procedure: insert into table A a data row with correlationID- next procedure: search for inserted record in tab A bycorrelationID and insert value into tab B with constraint to tab A.The problem is when we try to do this in short time (few minutes)thousands times (5000/6000 pairs in 5/6 minutes) in 100 parallelthreads:query in second procedure cannot find inserted record in firstprocedure.Everything is dirty read. no transactions.Have you any ideas?edi
View 1 Replies View RelatedHello,
I feel like ssis encryption model has a serious flaw. Especially when linked to SQL Agent jobs.
I have posted and others have posted messages about this. Something is plain wrong with ssis encryption keys and password protection. Also, you do not have the choice not to protect the packages. In my case, protecting packages is completely useless.
Here is the story.
After this post
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=131340&SiteID=1&mode=1
I created config files for al my packages connections passswords.
Now, by our IT Policy, I had to change again my password and of course, all packages now return multiple errors when I open them.
Hopefully, the config file did its job and the packages are ran anyways by SQL Agent, however, having to manually retype and resave all packages not to have the errors is just a plain hassle. Not to speak about people not using the config files and the correct "Run As" sql agent account.
I stress the fact that in a real world production environment all packages are driven by SQL Agent jobs and MUST run automatically.
Here is the error I get after opening a package after changing my password:
Error 1 Error loading Constants05.dtsx: Failed to decrypt protected XML node "DTS:Password" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available. c:projectsssis packagesssis constantsConstants05.dtsx 1 1
So Why is'nt this key automatically adjusted after Windows NT Domain password Change?
How can I refresh the key, not to have to reype all the packages connections passwords and rebuilding, Checkin-in again all the stuff?
I do not think the solution is "Use an application account which password never changes when you create your ssis packages" however at this time, this is the only solution I can think of.
How do you guys deal with this problem?
I still do not understand the ssis security model I feel it is diconnected from the reality and unpracticable in a production environment like mine.
Thanks
Philippe
I think I know the answer to this but thought I'd ask anyway.
I have a conditional split to check a column for null values or empty string values. It looks like this:
(!ISNULL(Ballot)) || (LEN(TRIM(Ballot)) > 0)
My question is: Are both sides of the expression evaluated? My testing says yes, because a Null value causes an error. Is there a way to short circuit the evaluation like the || operator in C# or the (less than elegant, and seemingly threatening) OrElse operator in VB? Whats the best alternative:
A slightly more complex expression that turns a null value into an empty string
A script component
Two conditional splits
Two paths out of one condtional split
I went with the first option, here is the expression I came up with:
LEN(ISNULL(Ballot) ? "" : TRIM(Ballot)) > 0
Hi, I am novice to Full-Text funcationality.
The problem I met is that SQL server seems not woring with too short keywords (with FREETEXT syntax). The following is the detailed description.
I index two columns, JobDes(ntext) and JobTitle(nvarchar), which mainly store Traditional Chinese(Big5). If I search with
select * from JobWanted where FREETEXT(JobTitle,'asp'), it goes well. But if I replace the 'asp' with 'as', nothing returns. What is the reason? Can I eliminate this limination?
Any help would be appreciated.
Ricky.
I want to write trigger code to update values in table1 to table2.
ALTER, CREATE, DROP cannot be used in a trigger, I guess.
Tabel1
ID, Name, Asset, Date, Active
---------------------------------
1, A, 10 , 01/08/04 Y
1, B, 16 , 06/08/04 Y
1, C, 12 , 07/08/04 Y
1, D, 13 , 10/08/04 Y
2, E, 10 , 15/08/04 Y
2, F, 11 , 16/08/04 Y
2, Y, 12 , 01/08/04 N
2, G, 15 , 17/08/04 Y
3, H, 13 , 19/08/04 Y
3, I, 15 , 02/08/04 N
Table2 after update
ID, Name1, Asset1, Date1, Name2, Asset2, Date2, Name3, Asset3, Date3, Name4, Asset4, Date4
---------------------------------
1,B,16,06/08/04,D,13,10/08/04,C,12,07/08/04,A,10,01/08/04
2,F,11,16/08/04,E,10,15/08/04,blank,blank, blank..
3,H,13,19/08/04,blank, blank...
As you can see, code must select only active('Y') names in table1 arrange in descending order of asset for each ID and updates(or insert into?)table2.
Please help me with code
How do I update a table from another table which resides in a different database but on the same server. Example Update table a which is in database 1 from table b which is database 2. Please help I need this as soon as possible. Thanks in advance to anyone who can help.
View 3 Replies View Relatedi have 3 following tables
Accidents, Vehicles, Casualties. the relationship is many vehicles to an accident and many casualties to a an accident.
I have one new table which will contain all the vehicles related to all accidents. however i want to be able put in all casualties that every vehicle is related to..so within the new table, there are three severities of casualties
Vehicledetails
column severity1 severity2 severity3
For every vehicle where there is 0 or more casualties I want to be able put a (total )number for each category of casualty( severity in the VehicleDeatils table ) into the columns in vehicledriverdetails
how can i do this?? i dont want ot have to manually update every single entry one by one...
I am trying to update one table with information from another - which became corrupt. The data layout is the same. cand_id below is the key, and thus can only returnb a single value. Any help is most appreciated. Thanks. This is the attempt I took:
update mwm.jf_candidates C
set c.ed_occ = x.ed_occ
from mwm.jf_candidates C, mwm.jf_candidates_corrupt X
where c.cand_id = x.cand_id
I know this is probably really obvious but I am trying to insert values into columns into a table if a client exists and I can't for the life of me figure out the syntax. It is something like this:
table A
Client_Id Field 1 Field 2
Insert (Field1) into Table_A
Select field1 from table_B
Where Table_B.Client_Id = Table_A.Client_id
Thanks.
Whenever I run this query Iget the following error message. Can anyone tell me why. I get the same error with or without the "Distinct"
Query:
update Clients
set Clients.Siccode = (Select distinct updateSIC.siccode
from updateSIC
where Clients.accountnumber = updateSIC.accountnumber)
Error Message:
Server: Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.
Thanks