I wonder if anyone can explain what I am doing wrong here:
I need to transport data between several tables using dataflow components. I need to wrap this in transactions to either succeed or fail completly, I do want to use my own BEGIN TRAN and COMMIT TRAN statements, not the transaction support built into SSIS.
As written in several BLOGs, I should set the "RetainConnection" property to "TRUE" to make sure each dataflow runs on the same connection as the BEGIN and COMMIT statements... and that seems to be random.
Sometimes they do run on the same connection, but I also logged (using SQL Profiler) package executions where the BEGIN TRAN was issued to SPID different than the one used in first dataflow, another SPID again for the second dataflow and finally yet another for the COMMIT... in this case COMMIT fails with "needs an open transaction".
Hi,I need to extract randomly 5 records from the table "Questions". Now I useSELECT TOP 5 FROM Questions ORDERBY NEWID()And it works. The problem is that I need an additional thing: if SQLextracts record with ID=4, then it should not extract record with ID=9,because they are similar. I mean, I'd like something to tell SQL that if itextracts some questions, then it SHOULD NOT extract other ones.How can I do it?Thanks!Luke
I have built a DTS package in SQL Server 2000 to Delete all rows from a table and then import from another table. I have scheduled it run twice a day and everytime it runs, it does not delete anything but imports all the records from another table. So when its done, I have twice the number of records in my final table. Then I go back to the server and execute the package manually and it works perfect. It deletes all and then imports new. I have enabled package logging and it shows both the steps executed perfectly (even when it executes on scheduled time). What can be the problem? Thanks, Bullpit
I am so confused. I hope someone can help! I wrote a large stored procedure that is meant to be called from a VB application. The store procedure collects data from several tables and buildsa new table of summary data. It is called with 4 parameters (date ranges and names for the new summary tables to create).
I tested the SP in SQL 7 while developing it, and never had any problems. But when I tried it in 6.5, it complained that I had too many subqueries. So I rewrote the procedure with less nestings, and then when I tried it I didn't get an error but it crashed with a DB Disconnected error.
I rewrote it again and then it finally worked (but only from Enterprise Manager); when I called from a VB application (using ADO), I get an error "protocol error in TDS stream." And looking in the SQL error log I see a whole lot of errors, none of which I understand, but the first mesage is "EXCEPTION_ACCESS_VIOLATION raised, attempting to create symptom dump.
I know that without knowing the contents of the stored procedure, there's no way anybody can tell me exactly what's going wrong, but I'm sure hoping that some people can give me some clues or directions to look. I'm desperate and have been working on this one project for too long already!
When I run this code on a column of the type float or real it's ok, but not if the column is decimal, why?
this produces an error:
SELECT IsNull(column1,'') as column1 FROM mytable
---
this code works:
SELECT IsNull(column1,0) as column1 FROM mytable
---
The problem is that this query is used in a C++ environment to build an insert into another table. The query is build together in a CString. C++ considers a CString to have ended if it encounters a NULL, therefore I need the check on all columns.
The error message I get is:
"Error converting data type varchar to numeric."
Why is it ok to use float or rel, but not decimal??
MY DATABASE FOR SHOPPING CART IS ACTING STRANGE IT HAS AUTOMATICALLY DESTOYED ALL PRIMARY KEYS. AND HAS CREATED 4 DUPLICATE RECORDS FOR EACH RECORD SO AT THIS STAGE I HAVE AROUNG 15000 RECORDS IN EACH TABLE IT MEANS 15 TIMES 4 60000 RECORDS. CAN ANYONE UPDATEME WHY THIS HAS HAPPENED AND WHAT CAN BE DONE TO DELETE EXISTING DUPLICATES. I NEED FAST REPLY IN THIS MATTER AS THIS IS URGENT. IF POSSIBLE KINDLY WRITE QUERIES TRIGGERS FOR FUTURE PROBLEMS AS WELL AS THESE DAYS I AM SICK SO MY BRAIN AINT WORKING IN THIS MATTER. URGENT URGENT URGENT THANKS ALOT I HAVE PREVIOUSLY POSTED AND HAVE GOT A GOOD RESPONSE. AND HOPE A GOOD ONE AGAIN. ONE MORE THING IS THAT I HAVE A BACKUP HERE AT MY OFFICE SYSTEM IT ALSO HAS DONE THE SAMETHING. I MADE A NEW DATABASE AND TRYED TO IMPORT DATA WITH DISTINCT KEYWORD BUUT IT IS NOT COPYING IT AS WELL. URGENT RESPONSE REQUIRED IF ANYONE CAN DO.
I have MSSQL2k SP3a on WIN2k SP4.moved a Date/log files to this server about a week ago from a SQL7serverand attached it to this new Sql2k server.everything works fine for about 24hrs and then it starts timing out !!all I have to so is restart the MSSQL service and works fine againtill the next day !there is only this one database on this server and nothing else isrunning on it.have anyone seen this before ? how could this be fixed !!thanksDon
One of my table, dbo.Customer, gives me different values of "row count" everytime I check the properties.
The dbo.Customer have around 12 000 rows but when I check the properties (or open the table) the value always differs. The table row count can have any value from ~9000 - 12000.
Its like the table are trying to load all rows but cant.
I'm using ASP and SQL Serv 2000. What I need to get from 2 tables (company & customers) is random 10 customers from random 20 comp. Anyone got an idea how to do this??? I've spent 2 days trying to get stored proc. or T-SQL to work, but nothing good came out of it. I can get 1 comp and 10 cust, but not a grouped list of 20 comp. w/ 10 cust. each.
Hi,I have a problem with the execution of a vb-procedure that should do 2 updates of databasetables.The procedure runs when the eventhandler registrates that I want make an update within the <asp:detailview..>-controll.But I get the errormessage: End-Of-Command requestedThis is the procedure:Public Sub myDetails_Update(ByVal sender As Object, ByVal e As System.EventArgs) handle DetailsView1.Databound Dim conn As New SQLConnection("Data Source=.SQLEXPRESS;AttachDbFile=C:Pferdeservice KarleApp_dataPferde.mdf;Integrated Security=True Connection Timeout=30;User Instance=True") conn.open() Dim cmd As New SqlCommand() Dim cmd1 As New SqlCommand() cmd.Connection = conn cmd.CommandText = "UPDATE News_Kultur set Header = @Header, Ueberschrift_D = @Ueberschrift_D, Ueberschrift_E = @Ueberschrift_E, Text_D = @Text_D, News_Kultur.Text_E = @Text_E"WHERE (((News_Kultur.ID)=@ArtikelID))" cmd.CommandType = CommandType.Text cmd1.CommandText = "UPDATE News set Datum= @Datum WHERE ID=@NewsID" cmd1.CommandType = CommandType.Text cmd.Parameters.add("@Headers", SqlDbType.nvarchar, 50) cmd.Parameters.add("@Ueberschrift_D", SqlDbType.nvarchar, 50) cmd.Parameters.add("@Ueberschrift_E", SqlDbType.nvarchar, 50) cmd.Parameters.add("@Text_D", SqlDbType.Text) cmd.Parameters.add("@Text_E", SqlDbType.Text) cmd.Parameters.add("@ArtikelID", SqlDbType.Int) cmd1.Parameters.add("@Datum", SqlDbType.Datetime) cmd1.Parameters.add("@NewsID", SqlDbType.Int) cmd.Parameters.add("@Headers").value = DirectCast(DetailsView1.FindControl("Headers"), TextBox).Text cmd.Parameters.add("@Ueberschrift_D").value = DirectCast(DetailsView1.FindControl("Ueberschrift_D"), TextBox).Text cmd.Parameters.add("@Ueberschrift_E").value = DirectCast(DetailsView1.FindControl("Ueberschrift_E"), TextBox).Text cmd.Parameters.add("@Text_D").value = DirectCast(DetailsView1.FindControl("datumTextbox"), TextBox).Text cmd.Parameters.add("@Text_E").value = DirectCast(DetailsView1.FindControl("datumTextbox"), TextBox).Text cmd.Parameters.add("@ArtikelID").value = DirectCast(DetailsView1.FindControl("datumTextbox"), TextBox).Text cmd.ExecuteScalar() cmd1.Parameters.add("@Datum").value = DirectCast(DetailsView1.FindControl("Headers"), TextBox).Text cmd1.Parameters.add("@NewsID").value = DirectCast(DetailsView1.FindControl("Headers"), TextBox).Text cmd1.ExecuteScalar() conn.close()End Sub Regards
I am trying to import some data from csv files. When I try it using bulk insert I get a conversion error. When I use the exact same format file and data file with an openrowset it works fine. I would prefer to use the BULK insert as I can make some generic stored procedures to handle all my imports and not have to code the column names in the SQL. Any suggestions?
BULK Insert stuff
From 'c:projects estdatalist.txt'
with
(FORMATFILE='c:projects estdatamyformat.xml')
insert into stuff (ExternalId, Description, ScheduledDate, SentDate, Name)
select *
from OPENROWSET (BULK 'c:projects estdatalist.txt',
FORMATFILE='c:projects estdatamyformat.xml')
as t1
The destination table has more columns than the data file. The Field IDs represent the ordinal position of the columns in the destination table. Column 1 in the destination table is an int identity. The conversion failure is from trying to convert column 5 to int which makes me think bulk insert is ignoring the name attributes in the XML and just trying to insert the columns into the table in order without skipping.
I am having quesion about attributes acting as both inputs and predictive outputs in a mining models, I mean if we are going to predict the outputs, then I cant actually see the effects of themselves acting as inputs as well?
I dont really see through the points of it.
Could please any experts here shed me any light on it.
I am looking forward to hearing from you shortly and thanks a lot in advance.
In my environment always on is there. Today I observed that primary server fail over to secondary server .now the secondary server acting as primary role.
Can I know when is fail over is happened and who did the fail over. Is there any script to find this?
A user was created with a limited privilege under the USERS group. Once this user loged in the Report Manager he is acting like an Admin and Content Manager, though he is not given even a browser role.
What do u think that this guy is acting like a Super User evenif he is restricted to a browser role on the Report Manager ????????????
Hi there.I need to fetch ONE random row from many. How can i get it? I try toexecute "SELECT field.table FROM table ORDER by RAND()" no effect.Thank you.
Hy, I nead to make a stored procedure in order to add a random record set. This record set is used to write in an ASP page a list which is automatically modified everytime we reload the page.
I'm trying to use the following query to select two random records from my database. Do you have any ideas of why the recrand field will not change? I am using MS Sql server 7.. Please email mark@dtdesign.com ..Cheers Mark
SELECT TOP 2 mytable.id AS RECID, RAND(mytable.id) AS recrand FROM mytable ORDER BY recrand
Has anyone noticed that if you created DTS package and try to change connection properties (i.e. change DSN or redirect DTS to different server or Database), as soon you click OK it does not save new password and hence does not work anymore. In my case to move Database from Development to Production server I would have to recreate all DTS packages. Is there any way around it?
I am trying to get random numbers to have a unique value for different processes, then I can identify each process. What happens is that I use rand() function without seed, so I got my random numbers, but after shutting down SQLServer and try to get again another random number after booting up, the same series of random numbers is given again and again. So if anyone knows how I can get unique values,even though reseting the server, and using random function or any other method which automatically provides unique values,I'll really appreciate it if you let me know it.
Is there a way to write an SQL statement to choose random values from a particular select statement..for example: select * from address I was thinking, if there was a way to use an include statement for it like: select * from address where id IN ('generates some random values')
So i've got to generate these queries that go from the first of a one month to the last day of a month. the user provides the starting and ending months.
What I was wondering was is there an easy way to set the last day of the month in sql without having to go in and hard code which months end on the 30 and which ones end on the 31.
I once came across a database in which when ever a row was inserted, a unique random code was also inserted in a column. This code generated was similar to format like {8A5FE5F1-6BDA-476C-A4DB584A7710FBF91121054625}. I was told that SQL2000 has some inbuilt functionality for this.
Any idea on how to do this using SQL2000 or coding. I feel this is much better than Identity Column as using autonumbers in quesrystring is too much risky as they easily readable.
I have a table with 3 fields SiteID (PK), SiteCode, SiteName. For each SiteCode I have several rows with different SiteName. For example: 1 327 a 2 327 b 3 327 c 4 328 aa 5 328 bb......
I need to retrieve each time SiteCode with random SiteName. For example: 327 b , 327 a , 327 c 328 aa 328 bb 328 aa.
I need it very urgent, so any ideas will be very helpful. Thanks to all,
I have a customer that wants to be able to generate a random list... ----------------------- The list is case numbers, and I can get those easy enough by saying
SELECT dbo.table_case.id_number FROM dbo.table_case
Now, what I want to do is take that list and extract 1 out of every 1000, picking out random id_numbers to display.
Hi Experts , I want to insert datas randomly to all columns of a table.The columns have different datatypes like int,varchar,datetime. What to do??? It is urgent so please help.
Hi Experts , I want to insert datas randomly to all columns of a table.The columns have different datatypes like int,varchar,datetime. How to create a procedure for the same??? please help.
I used this to generate a random password. You can change what it returns to up to an 8000 character password if you feel brave. You will need this function (http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=14924) to generate the random numbers. For some reason, occassionaly a NULL (CHAR(0)) character would get in there and mess things up so I had to remove those. You could change this to even include the non alpha numeric characters.
create procedure random_password @length smallint, @password nvarchar(256) = null output as set nocount on
declare @number int declare @lower int declare @upper int declare @part tinyint
if @length > 256 set @length = 256 set @password = ''
while len(@password) < @length begin exec random_number 0, 3, @part output
set @part = null set @number = null exec random_number @lower, @upper, @number output set @password = @password + ltrim(char(@number)) if substring(@password, len(@password), 1) = char(0) set @password = left(@password, len(@password) - 1) end go
