When I'm designing an Integration Services package, the drag and drop doesn't work. It did at one time but doesn't now. What did I change??? Driving me nuts!!!
Also, this is a simple package. I'm exporting 6 colums from a view (db --> csv). I only want the first initial from the middle name. What data flow transformation object do I use? I know it's not the data conversion because that concerns the column type.
Thanks
HELP PLEASE! 1) Is it true that INSERT, UPDATE, and DELETE queries can be carried out on only ONE table at a time?2) I have a newly created database which is empty, and has four tables related with FK's. How can I begin to enter data, if i can only do an INSERT or UPDATE on one table. When i do a SELECT query, i don't even get an empty results set to modify. But i think that's an old fashion technique anyway. And if i can only INSERT VALUES into one table at a time, that violates the relational integrity. PLEASE HELP!DanThe only thing I can think of is the de-enforce the Relationships Constraint... Then, one at a time, fill each table's 1st record with fictious data, and then enforce the Relationships again, hoping that the identity columns will match up.
View 2 Replies View RelatedDear Friends,
I am a PHP/Delphi MySQL programmer for some years, now I am going to create some code in MS SQL and VB. So I downloaded and registered MS SQL 2005 Server Express Edition and some tools like SQL Server Studio Management Express, and VB 2005 Express.
As I have some knowledge of MySQL design and programming, I have installed MS software, rather without problems. Then, using Studio Management I created new database, new table in it with some four fields (int, varchars and smallint). Then from the same tool I opened table and inserted some data, and closed this application.
Next I opened Visual Basic and in Database Explorer I set up connection to my SQL database indicating the file it is stored in (path*.mdf). I ordered not to copy data to new file but to use original one.
Then in Data Sources I created new data source based on previously set connection. In the end I dragged and dropped my table from Data Sources into my new Form of my new Windows Application, nice grid with panel appeared. So I ran the app, I tried to put some data and then after trying to save (using floppy disk icon on panel) i got error:
Update requires a valid UpdateCommand when passed DataRow collection with modified rows.
which appears in event handler for saving in line i've indicated.
Me.Validate()
Me.LudzieBindingSource.EndEdit()
Me.LudzieTableAdapter.Update(Me.TestDataSet.ludzie ) ' <-- here highlights
I tried then to add to ID field identity specification, nothing changed. Then in query editor of management console I put some code:
exec sp_configure 'user instances enabled', 1.
Reconfigure.
As some sources mentioned. I restarted Service. Nothing Changed. So this is my problem. What to change.
I use Win XP Pro SP2.
And next one question, even more important for me is the way I connect to database. I found that in Visual Basic Creating new connection in Database Explorer I indicate the file on my local drive!!! And for example if I try to open my table alongside in Management Studio Express and in my App in VB and the error raises
Cannot open user default database. Login failed.
Login failed for user 'FS7120Mm227'.
which is not a surprise for me as in VB i am using file which cannot be shared.
In MySQL I simply put address, port and so on and I could use my database in Delphi, PHP, and another management tools with no hassle. Is it possible in VB? Or it only can be used to exclusive using database?
Please inform me if such questions shall be directed to VB forum, I am not sure, and thank you for _any_ answer.
MS goes right way giving people great possibility to use and even sell small apps using their Express Editions of SQL and VB, but it is still too confusing for people who encouraged start using their apps and have such basic problems.
Michael.
Hello, Everyone,
Consider the following:
One Table
3 Columns: StudentName, ExamSubject, FinalScore
A class with 40 students
Each student took the 3 exams and have scores.
All student took the same 3 exams.
120 rows total.
Task One:
Top Score in each subject
Task Two:
Top 3 student name in each subject
Task Three:
Top 3 total score student in the class
Task Four:
Avg. Score for each student
Thank you very much...
Jack
1. Let's say I have a SqlDataSource named Fred. Say I have a string variable, call it Barney. If I call Fred.Insert(Barney) HOW Does the insert command GET the Barney value and pass it to the SQL Insers string? 2. Lets say also in this same insert, I want the user Lets say Batty, who is logged in, to have her username automatically inserted into the table along with her Barney value. What is the method to get a hold of user name that is magically floating around in the session somewhere? All said and done, I'd love for it to work like this: "INSERT INTO [BedRock] ([UserName], [Character] ) VALUES (SomethingThatResolvesToSignedInUserName, SomehtingThatResolvesToStringValueofVariable) Table BedrockUserName CharacterBettyRubble 'Bam-Bam' THANKS inadvance fro ANY HelpDan-Simple and sleepless-Harper
View 3 Replies View RelatedI have a some raw data in a string that looks like
'1989'
I need that to become numeric 19.89
if I define a type double for rawAmount
and do this:
rawAmount = CInt(iRest.Substring(41, 4))
Row.amount2 = rawAmount / 100
where row.amount2 is a type double precision float from my script component task output, I get:
19.899999999999999
what output should i use in this data flow component to get 19.89.
thanks.
Hi all,
I need few clarifications from you experts regarding SQL server 2005 & CLR integration ( my questions might be simple and silly, please bear with me).
A web service should be invoked from the SQL Server , Is CLR stored procedure only way to do that ?
Does SQL Server uses the CLR only when the CLR support is enabled ? OR SQL Server itself runs on top of the CLR no matter it is enabled or not ?
What are the major disadvantages of using CLR stored procedures instead of T-SQL?
Thanks in advance,
DBLearner
found it
View 3 Replies View RelatedHey,
I have MS SQL database.
I have procedure:
code:--------------------------------------------------------------------------------
CREATE PROCEDURE dbo.Reg_DropTable
@ModuleId varchar(10)
AS
declare @TableName varchar, @kiek numeric
set @TableName = 'reg_'+@ModuleId
begin
DROP TABLE @TableName <- HERE I GOT ERROR
end
GO
--------------------------------------------------------------------------------
I got error when using variable with tables names.
How to do this?
Ps. Number is send to this function and it must drop table with name Reg_[That number]
1. Is it legal and OK to use a MSDN SQL copy on a production environment or is it strickly for test environments ??
2. If I own a legal copy of SQL 7 with 5 cals, can I legally use SQL MSDE and have more than 5 people access my SQL server or am I also limited to 5 users as my original ??
Sorry I am a newbie at this SQL thing.
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.
Any help is appreciated.
Hi,
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)
I have:
Master table Hotel
table AddressCrossRef (with: RefID = Hotel.ID, RefType = 'Hotel', AddrID)
joins
table Address (key = AddrID)
table MediaCrossRef (with RefID = Hotel.ID, RefType= 'Hotel', MediaID)
joins
table Media (with MediaID,mediaType = 'thumbnail')
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
any clue?
thanks
I have some tasks that I need to accomplish within T-SQL but cannot find
a means to accomplish them.
They are..
1. Check for the existance of an external text file.
2. Count the number of rows in an external text file.
3. Be able to run the BCP command from within T-SQL. I am currently using
the BULK INSERT command which works fine but it does not allow the following..
a. Error log for failed inserts.
b. A command output file.
Any information would be greatly appreciated.
Jim
I am tying to call BCP to output the contents of a table to a text file from with in a stored procedure.
The procedure will be called from an ASP page ...
My question is were does the file get created ??? I want to create the text file on server ONE and SQL server is running on server TWO and IIS is running on server THREE... do i have to have a drive letter mapped to server ONE and if so is it mapped on the SQl server or the IIS server ....
ie:
exec master..xp_cmdshell bcp db..table out h:est.out -Uxx -Pxx -Sx
thanks for any help on this subject ...
Rob
I have some tasks that I need to accomplish within T-SQL but cannot find
a means to accomplish them.
They are..
1. Check for the existance of an external text file.
2. Count the number of rows in an external text file.
3. Be able to run the BCP command from within T-SQL. I am currently using
the BULK INSERT command which works fine but it does not allow the following..
a. Error log for failed inserts.
b. A command output file.
Any information would be greatly appreciated.
Jim
I have two questions,
1) Could anyone please point me in the right direction concerning information pertaining to NT Server Enterprise Edition verses NT Server Workstations. We are having problems running SQL Server 7.0 and the Enterprise Edition together on the same machine and was wanting to find information about compatability issues, if there are any,
2) I, on a SQL Server 6.5 database shrunk it by 2 Gb. When I looked to see if SQL Server released those 2 Gb back to the hard drive, I was amazed that it didn't! Did I miss something or will SQL Server 6.5 not release the space because of the initial set-up. And why did SQL Server 6.5 automatically take the space from the Transaction logs when neither of the devices were specified? Is the Transaction Log the default area for shrinkage?
Thanks in Advance!
Daimon Russell
daimon_r@hotmail.com
Hi, does anyone know how to explain how SQL transactions get mapped into TDS and TDS gets mapped into TCP/IP packets?
Or can you please point me in the right direction? I need to figure out how TDS and TCP/IP relate?
We're using replication and are having some latency issues and I'm trying to find out how SQL handles TDS and TCP/IP etc...
Thanks for your time.
1. When we create DTS in SQL Server through DTS designer, where are they stored physically?
2. What would be the best way to modify a DTS without using DTS designer?
3. Is there any other way to create DTS apart from DTS designer and Visual Basic?
4. Is there any website which has detailed information for DTS? (which has more FAQs like above?)
In our production environment, we keep changing the servers frequently, and everytime that happens, I have to change the connection properties in all the DTS going to them one by one.
thanks,
sanjay.
I am not too familiar with SQL Server, but my supervisor gave me the task of finding out the difference between SQL Enterprise and SQL Standard. He also asked me to research the difference between processor licences and client access licences. I will use the Internet as a resource, but I would also like to hear the opinions of someone who uses these programs or is knowledgable about them. So please any suggestions or any useful links would be very helpful.
View 3 Replies View RelatedI have two questions.
1) If a database is suspect we can have that trace from sysdatabases.There is a column named status.My question is in case of suspect datatbase what will be value in the field status of sysdatabases?
2) The password of an user login(created by using sp_addlogin stored procedure or any other way) is stored in the table sysxlogins of master database.The password is stored in a varbinary format.How can I get the actual password(means in a char format)?I mean how can I convert the varbinary value to a readable format?
Hi All,
I am new in SQL SERVER 2000.I have few questions -
1) WHAT WILL I DO TO TRUNCATE THE SIZE OF A TRANSACTION LOG?
2) WHAT WILL BE THE STEPS OF BUILDING THE MASTER DATABASE?
3) WHAT WOULD BE THE PLAN OF ACTION WHEN SQL DOES NOT STARTS UP?
4) WHAT WOULD BE MY PLAN OF ACTION WHEN SQL DB GETS CORUPTED OR STARTS IN A SUSPECT MODE?
Could I do periodicity backups to another computer(mediaserver) using VDI??
I mean , Could I config a Virtual Device so that I can do backups like disk or tape, I can use
'backup database ...to virtual_device='...' ' to do backup to another computer(mediaserver)?
suppose that I have finished the interface of mediaserver.
If this is impossible,how can I do periodicity backups to another computer??
I have finished a program using VDI that can do backup to another computer,and I know how to do periodicity backups to disk or tape. but I am puzzled about the periodicity backup using VDI.
How should I do?
Thanks,All.
I am putting together a proposal for my church, the current DB software (Access) has been outgrown. One proponent of a no name brand software insists that to implement SQL could take a year and a team of programmers, is this true??
View 3 Replies View RelatedI have to judge the aptitude of a few trainees for SQL DBA training ....
Can the gurus suggest some questions for the same ....
Hi Could any one tell me the answers for these questions.
1. how do we troubleshoot a datbase if it is in suspect mode.What is the reason for a database to be in suspect mode.
2. how can we move a file from C drive to D drive, so that the file location in C drive is completely moved .
3.In a particular primary file group there are many objects.
How can we move some of the objects from this primary file group in to another file group.
4. can we install a sql server on a remote server so that it will not ask any inputs like domain name, authentication modes. .
5.If we want to implement clustering , can we use the virtual ip address on clustering as the ip address of our system or we have to use another ip address for clustering
6. In 2000 we have DTs package.can we run the same DTS package in 2005.
Hi
Im relatively new to ASP/SQL and have been thrown into the deep end by work. Ive got courses to go on, but not for another 3-6months.
Ive got an ASP file calling a database using SQL. Once you hit the "go" button, it puts this data into an Excel file, under a new window (still showing the asp file in the address bar).
It currently shows:
1)
Item numbers that end in "0" i.e. 3.10, 12.20 appear as 3.1 and 12.2 respectively in the Bill of Materials … i.e. being treated as decimals … I need them to show-up as text.
************************************************** *******************
2)
When saving the spreadsheet … it would be good to get "X" to set the default file name to:
<Quote ID>_<Customer>_<Platform>_<version>.[xls|pdf]
************************************************** *******************
3)
When a spreadsheet is displayed, it is in an editable Excel format.
Is it possible to lock the file automatically when its opened or password protect it?
************************************************** *******************
4)
If a spreadsheet window is open and you try and open a new one, the old one pops-up/is still there. Need to be able to close the old one and re-open a new one automatically.
If necessary i will post the code.
Any takers?
Thanks
Hello,
I need help with a few sql questions, but you need to look at picture containing the tables and the relationships between them. How do I post or upload the picture first?
Thanks.
Hi all,I'd like to know if it's possible to sort twice in a same SQL query.I use SQL for retrieving Data into an Excel Spreadsheet.(Excel 2000 or XP) and to paste the queries results into an Excelspreadsheet.1. I tried to sort by date and the only way was to use the serail numberinstead of the litteral nameI tried SELECT * FROM [Sheet1$] Where [Date] <= 07/20/2003"but this does not work.I then tried SELECT * FROM [Sheet1$] Where [Date] <= 37822" and itworks.Do you know a way to use 07/20/2003?2. I need to sort my data twice, first by name and second by date. Is itpossible to sort twice in one query?Something like:"SELECT * FROM [Sheet1$] Where [Date] <= 07/20/2003 ORDER BY [NAME] ASC,ORDER BY [DATE] DESC"3. Even if the first row of my Excel sheet includes the headers, I cannot perform a query other than SELECT * FROM, for exemple, SELECT [NAME]FROM, does not work. do you know how to do that?Thanks in advance for sharing your experience,Phil*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 1 Replies View Related1. If the TempDB Database is deleted from MS-SQL Server what willhappen..?2. How to insert a not null column in an existing table withrecords..?3. If a table is deleted, what will happen for the Stored proceduresand Views, which that table reffered..?
View 1 Replies View RelatedHi All,
I was asked couple of questions yesterday and I thought I had to still learn a lot. Questions sound very normal but as for as I am concerned, don't know the answers hence this forum.
Please advise.
Here the questions asked to me
1) A simple insert into the table (could be from .net application or query analyzer) takes 10 minutes and times out, does not do any thing. What could be the reason and how would you identify and resolve the problem.
2) A simple select from table taking 10 minutes( assuming there are only there columns in the table,those are,
id, name, description)
id being identity column and has got index as well. What could be the reason and how would you identify and resolve the problem?
3) How would you change the identity column value or can we change the identity value and how?
4) How would you use the shared, update, exclusive locks in the sql statement.?
Please let me know.
Regards,
nw
Q1: What is the differences between green and red connections between objects in Data Flow tab in SSIS project?
Q2: In Flat File Connection Manager --> New Button --> header row delimiter --> what is meant by {CR}-{LF}?
Thanks alot,
Bishoy
Good Morning..
i have started developing on DTS recently. and i have a few question about "How-To" issues.
Hope you guys can help
1) I noticed, whenever i want to "redirect row" whenever an error happen, i need to set my AccessMode to OpenRowSet... This mode as i noticed, cant be used when i am tranferring data into a sql table which has primary key set
e.g. Ms Access which has identity -> SQL table with PK set
Is there anyway, to capture the row error in my situation?
2) Another question is, in a data flow... can i have a source which has multiple output arrow.
Sorry for the inconvenince,Thank you in advance
The <Protect Sensitive Data Using Encryption in SQL Server 2005> said : If regenerate service master key with "FORCE" option, the keys protected by old service master key are lost forever along with the data encrypted by them.
I create a master key in database and create a certificate protected by it.Then i use the certificate to encrypt some data.
The master key also exists and the encrypted data can by decrypt after i regenertate the SMK with force option.
So i'm puzzled,I want to know what are the keys which protected by SMK , are they database master key?
thanks