BCP Export Limited To MAX_INT Rows?
Feb 15, 2008
someone recently told me that their bcp export of a table with over MAX_INT rows failed because BCP hit an overflow in the internal counter that bcp uses to tell you how many rows it's exported. You know how it write "rows copied to host file: 40000" to stdout? That number got to MAX_INT, then went negative, then BCP crashed. So they had to start all over and break things up with the -F, -L flags.
I was rather surprised that a program that claims to handle "bulk" data would use a 32 bit int to accumulate the count. I suppose the original sybase dev figured nobody would ever want to export more than 2b rows.
just wondering, has anyone seen this before?
I am about to kick off a similar large export and am planning to break it up so as not to hit this, but that also means I won't be able to verify that it is indeed a problem.
elsasoft.org
View 20 Replies
ADVERTISEMENT
Sep 19, 2006
I have a table with entries tied to a membership database. The problemis that I want to select a limit of sixteen entries per member, perday, where some members have 16+ entries per day.I have this so far ( which I've simplified for this post)SELECT dbo.members.firstname, dbo.members.lastname,dbo.entries.gameDayFROM dbo.members INNER JOINdbo.entries ON dbo.members.memberID =dbo.entries.memberIDIf it's day 5, each member should have 80 total.How can I change this to select only 16 entries for each member, makingsure it's 16 per day based on dbo.entries.gameDay?Thanks for your help.
View 2 Replies
View Related
Apr 7, 2006
Hi,
For the SQL Express & Std Edition have rows limited....
I have try convert a 3,667,345 rows data into SQL Sever can the SQL Expree 2005 and SQL Std 2000 support this volume of rows???
View 1 Replies
View Related
May 25, 2008
hi need help
i have this stored procedure
the problem is that
i canot update like this not more than 20 - 30 rows
(i send it from a web page from check box)
it work but it limited rows for update not more than 20 - 30 rows
in one time
Code Snippet
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [sto_update_snha]
@id varchar(1000)
as
UPDATE Snha
SET fld5 = 3
WHERE charindex(','+CONVERT(varchar,[id])+',',','+@id+',') > 0 AND (fld5 = 2)
TNX
View 16 Replies
View Related
Jan 14, 2008
All,
I'm having an issue with the Flat File Data Flow Source returning only a limited set of the rows that are in the flat file. Basically, I connect to the flat file fine, it goes to retrieve the data (tab delimited file) and only returns 190 of 392 rows. Is there a limitation on the # of rows this data flow source can retrieve or something? I've look all through the settings and properties of the task as well as the connection manager and nothing is obvious as to what is causing this. Hopefully someone ou tthere has run into this before and can help me retrieve all rows. Thanks in advance!
bakerz
View 4 Replies
View Related
Dec 20, 2004
Hi
We are trying to export some data from SQL server
into an excel sheet. The data records will be like
more than 1 lakh records. I noted that Excel has
a constraint of not creating rows more than 65,000.
In this case we need to create multiple workbooks
in the excel sheet.
Could you please help me how to creat multiple
workbooks thru DTS in run time - if it is >65,000?
Thanks
View 2 Replies
View Related
Mar 13, 2007
Hi All,
Few days back we faced a problem when we were doing an Export to Excel of a report which was using sub-reports. After going thro. the knowledge base articles we came to know that SSRS currently doesn't suppot this option, an alternate is to use use List instead of tables, so we used List and did all the formatting with the list and the data was displayed correctly and it was Exporting to Excel also properly. But now we found out that after doing an Export to Excel, the Excel File is leaving blank rows between the data (i.e.,) If the report consists of two rows, then it displays the first row in the 10th row of excel and the second row in the 12th row of excel. The 11th row is blank and it appears as a small blank row between 10 and 12. The actual problem because of this is we are not able to do Auto filter in Excel, because Excel by default considers the values only until it encounters a blank row when we are doing auto filter. So is there a way to avoid this blank row while exporting to Excel, we have tried to remove the borders and all other stuffs but nothing seems to work. Have anyone encountered the same problem or is there any work around for this problem. Thanks in advance.
View 5 Replies
View Related
Apr 3, 2007
I have a Report I need to hide subtotals and total and include detail only when I am exporting to excel. So, is there a way to capture that it is exporting to Excel? Possibly a way to capture the rs:Format=Excel?
View 5 Replies
View Related
May 4, 2006
Hi...
I am new to MS SQL 2000. I need to generate reports using the SQL2000 database and exporting the data to Excel. Currently i started using the DTS services and was fine..But i have constraints that Excel can hold only 65K records..My records can go up to 80K +.Is there a way i can split the sheet depending the number of records..or is there a better way to do this...I do not have reporting services as we r using SQL 2000...Could anyone please help...
If there is any other way of doing this please guide me as to how to go about or any reference websites...
Thanks in advance
Banu
View 12 Replies
View Related
Aug 29, 2006
Just attempting to import a simple tab delimited text file into my SQL Server 2005 database using the SQL Server Import and Export wizard. Column names are specified within the first line of the file. The Header Rows to Skip field value is listed as 0, but the wizard indicates that "The field, Header rows to skip, does not contain a valid numeric value".
Why isn't zero (0) a valid numeric value? I don't want to skip any rows. PLUS, I get the same error when trying to export to a text file although the header rows to skip field does not exist. I can increase the number to 1 or more, but the wizard will skip part of my data .. unacceptable.
What am I missing here? I installed SP1 of SQL server 2005, but that did not help.
Thanks in advance.
View 1 Replies
View Related
Jan 28, 2008
Hi,
i hv made a rdl having 2 tables.My requirement is to display the tables on different sheets of excel, so i hv inserted "Page Break At End" in table1.
As a result im getting tables in different sheets but second table (in sheet2) is starting from 2nd row of the sheet i.e. 1st row is coming as hidden, and i want to remove this row as client feed's this excel file in some other application.
If anyone is aware of the solution pl. reply ASAP.
Thank u.
View 3 Replies
View Related
Sep 22, 2006
I have a search query on my page. However, since the resultset may be very large, I want to retreive only those results that are currently shown on the gridview page.Often a user won't browse through more than a couple of pages, so it's b-*** to send all the records to the client.I know that with a sp you can define that you want to retrieve the first or second x records, but I want to do this with SQL...is that possible?
View 1 Replies
View Related
Feb 19, 2007
I'd like to create a limited user on SQL Server 2005 to minimize any possible problems with my ASP.NET 2.0 website. My question is how limited can I make that user? What exactly does it need to be able to do?
(In case it makes a difference, my site utilizes membership, profiles, and roles, as well as some custom tables and stored procedures that I created.)
Thanks in advance for any advice! :-)
View 3 Replies
View Related
Feb 19, 2001
Dear all,
When I try to add the columns to 300,
But still can not over 300 column.
What's limited columns that MS SQL 7.0?
Thank you very much..
Gemmy.
View 2 Replies
View Related
Aug 29, 2005
hi all,
I have a field which name is Information
and it type is Varchar (8000),but some time data access than 8000 character, my client told me,make this field to store Unlimited data.
So how can i achive this task, i m using VS 2003 (ASP.NET with VB.NET) with SQL 2000.
Thanks
Shally
View 2 Replies
View Related
Sep 25, 2007
Hiya - this might be a bit of a simple question but please bear with me! I have looked reasonably hard for this but can not find an answer:
I have an MSSQL 2000 server running on PC-A and would like to limit certain users (e.g. admin) to be only able to logon when using the actual PC-A machine.
I am aware that you can acheive this in MySQL with the "insert into user (host, user, password) values (localhost, username, password);" command. Is there an equivalent way to do this in MSSQL?
Thanks for your help,
Alistair.
View 1 Replies
View Related
Nov 16, 2005
Hi All,
How can I give the user view rights only?
View 4 Replies
View Related
May 21, 2007
Hi,
I couldn't make a clear title without writing 2 lines.
My problem (I tried to find out through the archives):
this scenario will be used for several DBs on severals servers.
The remotes servers are not mine, I have only access to the backups files, I have no rights to setup a replication relationship.
I'm using a repository server with SQL 2005, and daily, I need to get the latest Full backup from an SQL2000 server, copy it to the repository server, restore it then delete the .bak file.
This is possible by using many different scripts (like .vbs to copy and rename the latest Full backups) then I use SQL job for daily restore.
Process is too long and the time estimated to start next task (and the backup keep growing).
Is there a way to do everything via SQL2005 script (job)?
Initially, the problem is that I have to do this with the Full backups every days (around 5 Dbs 8 Go in average). So if I can use the latest transaction log files (that would eliminate my first question).
The best way is to use log shipping as well but sql2005 is needed on both sides.
Hope I'm clear.
Thanks for any help!
View 4 Replies
View Related
Jan 21, 2008
I created a login in SQL Server 2005 for SQL Server Reporting Service training purpose. It seemed the login exceed the connection limit. There were 9 people in the training and some people got the connection errors. Sometimes the connection error went away after the user re-established the connection. Is there a default limit per login? If so, what is the default? Can I change it to unlimited? And how?
Thanks.
DanYeung
View 7 Replies
View Related
Nov 28, 2007
I want to use TRIGGER_NESTLEVEL to test if a specific trigger is on the execution stack.
Normally, the table with the trigger is accessed by stored procedures and the user does not have any direct access to the table.
I am having problems doing this because I need to use OBJECT_ID to get the ID to pass to TRIGGER_NESTLEVEL. OBJECT_ID is unable to see the table when the stored procedure is run as that limited user account.
How can I use TRIGGER_NESTLEVEL in this scenario without granting this user permissions on the table?
Here is complete code to reproduce. The output is:
Nest level is: 1 (running as admin)
Nest level is: (null) (running as limited user)
The output that I want would have a 1 instead of (null). Hard coding the specific OBJECT_ID is not the solution that I am looking for, since I want to use identical database scripts on separate installs of my database.
Code Block
IF OBJECT_ID(N'Hello') IS NOT NULL DROP TABLE Hello
IF DATABASE_PRINCIPAL_ID(N'TestUser') IS NOT NULL DROP USER TestUser
IF OBJECT_ID(N'HelloProcedure') IS NOT NULL DROP PROCEDURE HelloProcedure
GO
CREATE TABLE Hello
(
ID INT PRIMARY KEY IDENTITY,
Data INT NULL
)
GO
CREATE TRIGGER HelloTrigger1
ON Hello
FOR INSERT
AS
SET NOCOUNT ON
UPDATE Hello SET Data = 1234 WHERE ID IN (SELECT ID FROM inserted)
GO
CREATE TRIGGER HelloTrigger2
ON Hello
FOR UPDATE
AS
SET NOCOUNT ON
PRINT N'Nest level is: ' + ISNULL(CAST(TRIGGER_NESTLEVEL(OBJECT_ID(N'HelloTrigger1')) AS NVARCHAR(128)), N'(null)')
GO
CREATE PROCEDURE HelloProcedure
AS
SET NOCOUNT ON
INSERT INTO Hello VALUES (0)
GO
CREATE USER TestUser WITHOUT LOGIN
GO
GRANT EXECUTE ON HelloProcedure TO TestUser
GO
EXEC (N'HelloProcedure') -- Prints: Nest level is: 1
GO
EXEC (N'HelloProcedure') AS USER = N'TestUser' -- Prints: Nest level is: (null)
GO
View 2 Replies
View Related
Jan 21, 2008
I created a login in SQL Server 2005 for SQL Server Reporting Service training purpose. It seemed the login exceed the connection limit. There were 9 people in the training and some people got the connection errors. Sometimes the connection error went away after the user re-established the connection. Is there a default limit per login? If so, what is the default? Can I change it to unlimited? And how?
Thanks.
DanYeung
View 3 Replies
View Related
Mar 28, 2007
I got a report that seems to be limiting its displayed data to the first 8 records but not in the SQL statement itself. Although I have been working with SQL for many years I'm very new to Reporting Services so it may be something very simple, like perhaps a property that can be changed on the report for me to increase the amount of records the report is pulling from the database?
I don't seem to find any property set to 8 though.
Also the report is not pulling the records in the order the query pulls them, but in an apparent random order, I don't need to change this but it may help describe the report a bit better.
View 10 Replies
View Related
May 15, 2007
Hi all;
qrymillcgetbytype_app ( stored query in MS-Access) in my code :
MS-ACCESS Part:
query defination
PARAMETERS prmMillToolType Long;
SELECT MILLC.ID, MILLC.InsMillBdyID AS [Tool ID], MILLC.SubType, MILLC.Radius AS [End Radius], MILLC.CuttingDia AS [Tool Dia], MILLC.EffAxlCutLen AS [Effec Cut Length], MILLC.OverallLen AS [Overall Length], MILLC.HandOfCut AS [Hand Of Cut], MILLC.NoOfFlutes AS [No Of Flutes], MILLC.TmcID AS [Tool Class], MILLC.Comment, MILLC.Protrusion, MILLC.ShankDia, millC.ShoulderLen AS [Shoulder Length]
FROM MILLC
WHERE (((MILLC.[Mill Tool Type])=[prmMillToolType]) AND ((MILLC.[ON])=True))
ORDER BY MILLC.ID;
C++ PART
bstrSQL contain stored query name "qrymillcgetbytype_app"
hr = piRecordSet->put_Source(bstrSQL);
piRecordSet->Open(vNull, vNull, adOpenKeyset, adLockOptimistic, adCmdUnknown)
it opens fine
but when bstrSQL contain this query name " qrymillcgetbytype_app WHERE ID > -1 AND [Tool Dia] >= 0.000000 AND [Tool Dia] <= 5.000000 "
it displays the same output. Where clause won't work?
Pls help me out.
View 3 Replies
View Related
Dec 6, 2007
Im using the Xquery:SELECT @xmlDoc.query('
for $item in (/Collection/Content)
where $item/Html/root/DocInfo/Webinar = "White Paper"
order by $item[1]/Html[1]/root[1]/DocInfo[1]/Title[1] ascending
return $item
');
I only want to return the TOP 5 nodes (not the entire nodelist). What is the FLOWR expression or simplest way to return the limited nodeset?
Thanks
View 1 Replies
View Related
Jul 8, 2004
I am using ORDER BY NEWID() to return random record from sql database. how do i go about returning only 5 random records instead of all records.
Thanks.
View 2 Replies
View Related
Sep 3, 2002
Hi, I am trying to create a SQL Server user which would only have write permissions. This account would not be able to read, drop, alter, delete etc. It would simply be used to write data.
Is there a way to configure a user like this without scripting the permissions for each table. The DB has some dynamically generated tables, so the users privillges would ideally extend to those newly added tables without having to rerun some kind of script. Is this possible?
View 1 Replies
View Related
Sep 3, 2002
Hi, I am trying to create a SQL Server user which would only have write permissions. This account would not be able to read, drop, alter, delete etc. It would simply be used to write data.
Is there a way to configure a user like this without scripting the permissions for each table. The DB has some dynamically generated tables, so the users privillges would ideally extend to those newly added tables without having to rerun some kind of script. Is this possible?
View 1 Replies
View Related
Dec 15, 2004
Hi, we have a generic way to call DTS
CREATE procedure dbo.run_our_dts @Our_dts_namevarchar(8000)
as
declare@retcodeint
declare @run_sqlvarchar(8000)
select @run_sql = 'dtsrun /S '+@@servername+' /E /N '+@Our_dts_name
print @run_sql
exec @retcode=master..xp_cmdshell @run_sql
if @retcode<>0
begin
Raiserror('DTS RUN ERROR',16,1)
end
it works fine but ,Only users with SysAdmin privileges can execute CmdExec and ActiveScripting job steps
we don't want to disable this setting
Does anyone now extended procedure
similar to xp_cmdshell, with abilty to
start dtsrun ?
Thank you
Alex
View 1 Replies
View Related
Apr 17, 2007
Hi
Does anyone here know why xp_fixeddrives with a limited user returns an empty result set on 2005?
In essence I want to create a report which shows disk space remaining, an upgrade of an old one. It works fine on 2000, but seems to need sysadmin rights on 2k5. I would appreciate it if someone can suggest how to get it working - or an alternative solution!
Cheers
View 9 Replies
View Related
Jul 23, 2005
I am trying to insert records via ASP, with a user that has only writeaccess to the table (db_datawriter, db_denydatareader).That way, if the server is ever compromised, the access informationstored in the source code's connection string will not allow anybody toactually read the database.The problem is that I would like to use ADO methods to insert the data(to prevent SQL injections), but I can't seem to get the rightconnection. It works in plain SQL, but I'd rather not use it.My current code looks like this:connection="Provider=SQLOLEDB.1;User ID=DBwriter;Password=XXX;DataSource=MYSERVER;Initial Catalog=MYDB;"set conn=server.createobject("ADODB.Connection")conn.mode=2 ' adModeWriteconn.open connectionSet rs = Server.CreateObject ("ADODB.Recordset")rs.Open "MYTABLE", conn, adOpenKeySet, adLockPessimistic, adCmdTablers.AddNewrs.Fields("testfield") = "TESTDATA"rs.UpdateAnd the error I get is:Microsoft OLE DB Provider for SQL Server (0x80040E09)SELECT permission denied on object 'MYTABLE', database 'MYDB', owner'dbo'.(If I use a User with read privileges in the connection stringeverything works fine.)
View 3 Replies
View Related
May 5, 2006
Using System.Data.SqlClient is there a limit to the number of connections an application can have to a SQLMobile dB?
If
you have a look at the url below, it says "A device can only have a
small number of connections to an instance of SQL Server at any time"
Does this mean 2, or 8 or what?
Should a app try to use only 1 connection throughout, or can we get away with 2-3?
Is this the same on WM5.0/PPP2003?
Any advice in this area much appreciated!
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sqlce/htm/_lce_sqlclient_705.asp
p.s. I'm not interested in connection pooling!
View 1 Replies
View Related
Oct 14, 2006
hi all,
I installed sql server developer edition on
xp box. created a new ssis project and
then when i right click on control flow
then click new connections
i see only limited connection available.
how can i install the other connection option
such as oledb. etc.
thanks
joey
View 1 Replies
View Related
Apr 26, 2007
Hi all,
I have a problem just today after added some tasks. I can only get the "OnPrevalidate" logging info only in both sysdts90 and a text file. Have I messed up with some setting?
Thanks!
View 1 Replies
View Related
