I'm just beginning to use DTS. I have a test table which has only two column, char (10) and decimal (5,0). I want to do a simple transformation by adding the second column by 100 with the following statement, but I receive a type mismatch error!
Function Main()
DTSDestination("a")=DTSSource("a")
DTSDestination("b")=DTSSource("b").Value + 100
Main = DTSTransformStat_OK
End Function
or
Function Main()
dim x
DTSDestination("a")=DTSSource("a")
x = DTSSource("b").Value + 100
DTSDestination("b").Value = x
Main = DTSTransformStat_OK
End Function
The problem is solved but still I would like to know what is going on behind the scenes. I was always thinking that the native client was involved and not oledb.
Using the Export/Import wizard you still end up going via SQL Server Agent that's where the Credential/Proxy items show up.
Hello friends I need a very ergent help!!!!!!!!!!!!! Problem: my sql server on production had everything grayed out, when I want to administer this server as a SA. eg: I can't add users, I can't backup and so on. but the database is still running and users are accessing it. This SQL database is accessed by an application(it can also create users on the backend). I don't know how it happend ( i cant even get to errorlog). I beleive somehow sa user rights were taken away from him. Can anyone tell me how to restore sa with all rights.
Please help me on this matter, because I have to do lots of important tasks on this server.
Test1 that lists all the training courses that i can count to find out the total as below.
select count (distinct Training_Course) as total from Test1
Then Test2 lists all our customers and courses they have attended. I count the courses attended and then group by their ID.
select Cust_id, count (Attended) as TotalAttend from Test2 Group by Cust_id
What i am now trying to do, without any luck, is find out which Customers have attended all training sessions by comparing the two queries and only bringing back the cust_id where it matches the total count from the Test1 query.
Make any sense? Any help/suggestions gratefully recieved.
I'm very rusty with my SQL, and could use a little assistance on building this query. Thanks for taking the time to help me. It should be a relatively simple Count() query but I'm not getting the right results for some reason and I'm hoping somebody can point out my error to me.
I'm attempting to count the number of times each m_id is returned after running this query:
SELECT m_id FROM taglink WHERE m_id <> '25' AND t_id IN ( SELECT t_id FROM taglink WHERE m_id = '25' )
which returns: m_id 33 34 34 35 35 35 36
I want to count the number of times that m_id is returned so that the results of my query will be:
m_id | count 33 | 1 34 | 2 35 | 3 36 | 1
In my attempt to do this, I run the following query:
SELECT m_id, count( m_id ) AS "count" FROM taglink WHERE m_id IN ( SELECT m_id FROM taglink WHERE m_id <> '25' AND t_id IN ( SELECT t_id FROM taglink WHERE m_id = '25' ) ) GROUP BY m_id
The problem I'm having is that the query returns:
m_id | count 33 | 3 34 | 2 35 | 3 36 | 3
It seems to return the count of "3" for fields that should count "1," but count correctly if the fields count "2" or "3."
Perhaps somebody can spot my error... I can't seem to wrap my brain around this one. Thanks so much for your time!
i try to do a simple insert using SQLDataSource and a Stored Procedure I try all night and always have this error
Object Must Implement IConvertible
I can’t understand where I wrong..
Looking around I understand is maybe a typecasting problema but I not find nothing more understeable.. Could someone tell me why Microsoft release this SQLdatasorce making more difficult a simple insert? If someone could help me to fix this problem please
Here is the code ------------------------------------------------------------------------------------------------------------
Protected Sub btnSave_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSave.Click
Try SqlDataSource1.Insert()
Catch ex As Exception Response.Write(ex.ToString) Finally SqlDataSource1.Dispose()
End Try
End Sub ------------------------------------------------------------------------------------------------------------
i have a table "employee" , i have given grant all rights to a user in sql 6.5 server . still whenever he tries to access the table from VB he gets an error "execute permission denied on table employee, owner dbo" . if i log in the server with this user name and his password i am able to interact with the table . please help.
We've tried attaching the mdf files given to us by some customers using the script they've provided, too.
I created the database with the corresponding names, and tried to run their script. However, I get the message "The media family on device 'D:DatabaseDVVAD01.mdf' is incorrectly formed. SQL Server cannot process this media family."
What could possibly have gone wrong? Is it also possible that the file was not properly detached, or what?
I am re-writing a old query that update's it's value based on values at run time. Is there anyway to accomplish the following query where column s1.AI referances the values that were updated during run-time.
I want to avoid a Loop (i.e. cursor or a CTE).
Please help!
Declare @Stage Table(StartDate datetime,BenefitInterestID INT PRIMARY KEY, Amount MONEY, InterestAmount MONEY, Interest DECIMAL(10, 4), ai DECIMAL(10, 4)) Insert Into @Stage Select convert(datetime,'2006-12-01 00:00:00.000',101) as StartDate,1 as BenefitInterestID,1701.00 as amount,79.605 as InterestAmount ,0.1000 as Interest,0.0000 as ai Union all select '2007-12-01 00:00:00.000',2,172.80,7.92,0.0500,0 Union all select '2008-12-01 00:00:00.000',4,0.00,0.00,0.0700,0
UPDATEs1 SETs1.ai = s1.Interest * coalesce((SELECT SUM(coalesce(s2.Amount,0) + coalesce(s2.InterestAmount,0)+coalesce(s2.ai,0)) FROM @Stage AS s2 WHERE s2.StartDate < s1.StartDate ),0)
First, I'm not sure what format to post the SQL query in, but I'm using it in the Pawn programming language, so it's all on one line. Sorry if it's hard to read :(
Ok, I am supplying you guys with my database that I am having problems with. Basically, I'm telling it to ORDER BY ASC but it's not ordering it correctly. It's almost correct, but not perfect. I'm not sure if SQLite is much different than SQL standard, that's why I differentiate.
Here is the SQLite database: *deleted*
The problem comes in when you execute this query:
SELECT p.alias, min(s.fin_time), s.cps, s.gcs, s.boosts, s.wpns, s.score, s.score_id FROM climb_scores s JOIN climb_players p ON s.user_id = p.user_id JOIN (SELECT user_id, fin_time, score_id FROM climb_scores WHERE map_name="j2s_4floors" AND cps = 0) g ON g.score_id=s.score_id GROUP BY s.user_id ORDER BY s.fin_time ASC LIMIT 20
The end goal is to have fin_time sorted in ascending order, but it's not working right. Can someone clue me in to the problem?
Location Tree View --- .___ Building A . .___ Room 1 . .___ Room 2 . .___ Partition 1 . .___ Building B . .___ Room 1
on tblAssetPlacement, the column PlacementDate is a unix timestamp stored as bigint. the higher of its value means it is the latest placement.
so i had created a sql to pull the data like this:
SELECT tblasset.AssetName, tblassetplacement.OfficerInChargeID, tblassetplacement.Locationid, tbllocation.LocationName FROM tblasset,tblassetplacement, tbllocation, (select tblassetplacement.AssetID,tblassetplacement.LocationID,MAX(tblassetplacement.PlacementDate) AS LatestDate from tblassetplacement group by tblassetplacement.AssetID) temptbl where tblasset.AssetID = tblassetplacement.AssetID and tblassetplacement.PlacementDate = temptbl.LatestDate and tblassetplacement.AssetID = temptbl.AssetID and tbllocation.LocationID=tblassetplacement.LocationID ;
this will output: --- AssetNameOfficerInChargeIDLocationIDLocationName IPODSusan6Room 1 Sony PS3Fred5Partition 1
i would need to have a column ParentLocationName that directly shows the name of the parent's location as below: --- AssetNameOfficerInChargeIDLocationIDLocationNameParentLocationName IPODSusan6Room 1Building B Sony PS3Fred5Partition 1Building A
is there a way to pull the parent's location name in one sql syntax?
Is there a way to supress output on one column in a SP, using data from the same row?
Like This: SELECT Last, First, DOP, dbo.fnDueDate(DOP, 3, GETDATE()) AS NextQDue, dbo.fnDueDate(DOP, 6, GETDATE()) AS NextNSPDue, DATEADD(m, 1, DOP)AS InitialNSPDue, DATEADD(m, 1, DOP) AS InitialAssessDue, DOT, DisReason, DATEADD(m, 1, DOT) AS DisSummDue, Facility, Active FROM dbo.tblResidents
But which returns null for some of the columns if DOT is not null? DOT is the Termination Date, so the only columns that have any meaning once there is data in the DOT column are DisReason and DisSummDue. Also, if DOT *is* null, then the above columns also have no meaning. I tried several variations of the following, but I can't figure it out
CREATE PROCEDURE [dbo].[spTesting] AS BEGIN SELECT Last, First, DOP, dbo.fnDueDate(DOP, 3, GETDATE()) AS NextQDue, dbo.fnDueDate(DOP, 6, GETDATE()) AS NextNSPDue, DATEADD(m, 1, DOP) AS InitialNSPDue, DATEADD(m, 1, DOP) AS InitialAssessDue, Facility FROM dbo.tblResidents A WHERE DOT IS NULL UNION SELECT Last, First, DOP, DOT, DisReason, DATEADD(m, 1, DOT) AS DisSummDue, Facility FROM dbo.tblResidents I END GO
I have an interesting problem here. I am running SQL Server 2005 and have a large database. After running some scalability tests over the weekend my MDF file size went up to 25GB and LDF file size went up to 27GB filling up all available disk space. I didn't do any backups, so it is understandable why the LDF file size is so large.
Now I am trying to shrink the LDF file. However, DBCC LOGINFO indicated that all LVFs within the LDF are active (Status = 2). I detached the database to make sure no active connections exist and ran CHECKPOINT, but still DBCC LOGINFO lists all LVFs with status 2. Since all LVFs are up to date I can't shrink my LDF file.
Why are all LVFs still active after detach/attach and checkpoint? How can I shrink the LDF file?
I have a database, where all tables and stored procedures look like this: dbo.table dbo.sp
Is this naming because of the db owner creating the tables and stored procedures?
The db runs correctly in my asp.net aplication when i use a user that is db owner, but i want it to run with a 'normal' user that is in public role and has all rights except db_owner...
How can i solve my problem!
Using the user without db_owner rights brings in my application the error: The EXECUTE permission was denied on the object 'myStoredProcedure', database 'myDatabase', schema 'dbo'.
Thanks for help!!!!
Steven****
--------------------------------------
Solution:
I have to give the User without DB_Owner Rights the schema permissions for dbo objects! This only works in sql server 2005!
Hello,We have an ADO.NET application using .NET version 1.1.4322 SP1. It iscalling stored procedures in a database that it never written to. Theonly thing the stored procedures do is a select statement on a fewtables that are joined. In the last few weeks we have experiencedissues where, two or three of these stored procedures consistentlytimeout. When we recompile the stored procedures, the problem goesaway for a period of time (anywhere between a few hours and a fewdays), then it reoccurs with the same two or three stored procedures.We have tried running the stored procedures with the same parametersagainst the same server using query analyzer with the same user andconnection settings while we are having the timeout issues, and theyperformed normally (in the 5 second range). We have also put thedatabase into "read only" mode, which has improved performanceduring normal times, but the issue continues to occur.Our trace data shows that during the timeouts periods, the storedprocedures perform the same number of reads and writes, and using thesame amount of CPU as during normal times, but the duration increasesfrom 5-6 seconds to 30 seconds (when the server receives the timeoutrequest).We have found no blocking on any of the tables (they are, after all,read only).Finally, we compared execution plans for the stored procedures when wehad timeouts to right after we recompile and alleviate the issue, andthe plans are identical.What could be causing this problem? Does recompiling a storedprocedure affect the Sql Server .Net Data provider?Thanks in advance
Sory for starting a new message, it won't let me reply to the original.Problem has been solved.osql -U sa -s localhostBALTDDoes the trick.Thanks for everyone who had a look at it anyway!Enjoy your weekend!
I have 15 websites running on my server, which also has SQL Express installed. every other day my webpages whch connect by sql connection string, give an error.
Restarting SQL Service does not resolve, however rebooting server, without making any changed sorts the problem. does my sql account need access to the master?
Is this weird or am i missing something really obvious.
Hi,As I wrote my message the solution came to me, so I thought I wouldpost anyway for others to see in case it was useful:Here is some sample DDL for this question:CREATE TABLE Source (my_value INT NOT NULL )GOINSERT INTO Source VALUES (1)INSERT INTO Source VALUES (2)INSERT INTO Source VALUES (3)GOCREATE TABLE Destination (value_type VARCHAR(10) NOT NULL,value INT )GOI would like to fill the destination with a row for the COUNT, SUM,MIN, and MAX. My own problem is of course much more complex than this,but this is the basic stumbling block for me now. So, the rows that Iwould expect to see in Destination are:value_type value---------- -----COUNT 3SUM 6MIN 1MAX 3The solution that I came up with was to add a Value_Types table:CREATE TABLE Value_Types (value_type VARCHAR(10) NOT NULL )GOINSERT INTO Value_Types VALUES ('COUNT')INSERT INTO Value_Types VALUES ('SUM')INSERT INTO Value_Types VALUES ('MAX')INSERT INTO Value_Types VALUES ('MIN')GONow the SQL is pretty simple:SELECT V.value_type,CASE V.value_typeWHEN 'COUNT' THEN COUNT(*)WHEN 'SUM' THEN SUM(S.my_value)WHEN 'MAX' THEN MAX(S.my_value)WHEN 'MIN' THEN MIN(S.my_value)ENDFROM Source SINNER JOIN Value_Types V ON 1=1-Tom.P.S. - I know that I did not include primary or foreign keys in my DDL.I'll leave it as an excercise to the reader to figure those out. Ithink the code adequately explains the concept.
I have a table which contains suppliers that can supply certain articles. There can be many suppliers to each article, and therefore there is a field that tells you if this supplier is the main supplier for the article, this field is called "arthuvudavt". However, you aren't forced to use this field in the table. As a alternative you can use priority. Each supplier has given priority where 0 means "most preferred" and any other means "less preferred" What I want to do is to pick the right supplier for a given article in one SQL-statement regardless of if you choose to use the priority or if you choose to use the main supplier field.
In the above case, I'd like the statement to return supplier 10202 for article A-10 (the one that has the arthuvudavt checked), and only one row for B-10, which one doesn't really matter, since someone has been making a fault here, this should not happen, but my SQL-statement should break because of this scenario. For C-10 supplier 10303 should be returned.
Is there any way this could be done?
The reason I ask is that I try to learn new ways of solving things, and this problem was kind'a easily solved using variables, but I was wondering if it was possible to do it without it.
Thanks in advance Jonas
Sorry for the poor formatting, I didn't have the time to make it right.
I have a report in which a developper has put a matrix which has 4 MatrixRows. I want to delete 2 of them. When I righlt click a row and click Delete there is message about rows groups and columns groups and the row is still there. When I right click the matrix and go to properties / group, i see Row group = [Static group] and I cannot modify it.
How can I delete a MatrixRow ?
a+, =) -=Clement=-
Configuration : SQL Server 2005
edit : I found the solution : click on the cell in the 2nd column, not in the first, then right click and delete. I would suggest Microsoft IT s to put less humor in their softs (you click delete in the wrong place, you get a weird message instead of a 'try another cell' )
The result is that I am able to parameterize the sql end execute with the right result. The only problem is that the value is not stored in the variable @count. I could get to the same result using managed code in sql 2005 but still I am curious to find out where the problem is ....
Yesterday, we have had a sudden load in our SQL Server 2000 which resulted in several locks. There was not too much time to investigate as we had to rush. A team member had reviewed the processes in EM, Manegement, Current Activity. Looking for blocking processes and killed them.
She told me that as soon as the blocking SPID was killed, another one arose and she had to repeat the operation a dozen of time. When done, the server activity was back to normal. She noticed that more than half of the blocking processes showed that they executed the stored Proc "P_SearchProducts".
We don't own the server and the information on what had happened at that time (batches or resource intensive operations, etc.) is not available for now.
The team suggests that we set the Transaction Isolation Level to Read UNCOMMITTED for this SP. I would like to know better about locks before I go ahead.
P_SearchProducts returns 5 recordsets each one could contains from 1 to 200 rows. To achieve the results, it creates about 10 intermediate tables (SELECT ... INTO #TableX) these temp tables are then used progressively to arrive to the final results. Roughly the volume of these temp tables could be double than the final results. The developer who wrote this SP is not a guru in SQL, there is room for improvement. But here are my questions:
Q1. Could the series SELECT ... INTO #TableX in P_SearchProducts prevent or lock another connection from executing the same SP? If yes, under which conditions?
Q2. Let's assume that P_SearchProducts has a slow execution time. Could it prevent another connection from updating the Product table? And thus leading to a deadlock situation? Something like another transaction (by User2) has obtained lock on most of Product tables, except the Product table which were being slowly read by User1 executing P_SearchProducts. But User1 cannot read the other product tables b/c there are locks by User2.
Q3. If the contention issue was provoked by the slow execution time of many request to exec P_SearchProducts (let's assume there were suddenly 50 users on the web hitting the search product feature at the same time). Could the Read Uncommitted magically resolve the contention issue, providing we accept the consequences of the dirty read.
Sorry for the long post and thank you in advance for any help.
I am receiving funny results from a query. To simplify, I have 2 tables (todayyesterday). Each tbl has the same 8 columns. My query joins the two tables then looks where either of two columns has changed. What is happening is that when checking one of the columns it seems as though sql is flipping the column, causing it to be returned in error.
result set
colA colB colC colD colE colF colG colG (from yesterday) 1 1 a b c d e m 1 1 a b c d m e
So what's happening is that the record above is actually the same record and should not be returned. There is a daily pmt column that changes but I am not using that in the query. Aside from that the two records are identicle.
I have the following situation (with a site that already works and i cannot modify the database architecture and following CrossRef tables -- you will see what i mean by CrossRef tables below)
foreach hotel, there definitely is a crossRef entry in AddressCrossRef and Address tables respectively (since every hotel has an address)
however not all hotels have thumbnail image
hence i have hotel inner join AddressXReff inner join Address ..... however i must have left outer join mediaXref left outer join media
the problem is that if there is no entry in Media or mediaXref, I don't get any results
i tried to get over it by using where (media.mediaTyple like 'thumbnail' or media.mediaType is null) but then i started getting multiple results for each hotel because media's of type movie or full_image or etc... all got returned
...when I started this endeavor. I have a previously developed Lotus Notes App. The idea was simple; as I am sefl taught on Lotus Script, I figured I'd be able to stumble my way through VB.Well, it started OK. I used VB Express to get familiar with the stuff, but decided to go with a full version of VS 2005 and try and get this thing properly developed as a web app. I purchased several reference books etc., and have become relatively familiar with the forums here.First issue I have is that I simply want to use code to update or add records to an SQL DB. I know about datagridview etc., but I want to update the DB using forms, not the tabular view those controls provide. I thought it would be relatively straight forward, but found my ignorance runs deeper than I thought. When I tried to do so I am finding I am not really clear on where to make declarations etc. in the web app. If anyone could point me in the right direction that would be great. The issue with searching these forums is most posts deal with datagridview or something similar.I have spent a ton of time trying to find relevant posts or articles, but have had no luck yet.Again, all Ireally need is a nudge in the right direction. I am more than willing to plod through reference materials or articles/posts to find what I need to know, I just can't find where to even start on the info I need. Regards, Joe
