Binary(8) Columns Skipped When Querying
Sep 5, 2013
I have a serious issue with transferring binary(8) data from SQL Server 2008 R2 to Excel 2010.
A standard query is made in Excel (Data -> From Other Sources -> From SQL Server) and it works... almost perfect.Almost, because in the query result I have almost all columns in destination spreadsheet, except of those columns which are binary(8) format in my table in SQL Server.
So, in SQL there are columns - let's say:
Date
Start time
End time
User ID (as binary(8) format)
Memo (as string)
...
After Excel's query is done, in Excel there are only columns:
Date
Start time
End time
Memo (as string)
...
All columns with types = binary(8) are skipped.
I tried:
1. Export from SQL Server to CSV file and then from CSV file to Excel (all columns as string) and it works, but it has to be automated so CSV is not a good idea.
2. Adding to ConnectionString additional parameters NO_BINARY_RESULT=1 - does not work.
3. Adding to ConnectionString additional parameter IMEX=1 - does not work.
View 10 Replies
ADVERTISEMENT
May 22, 2014
Server is SQL 2000
I have a table with 10 rows with a varbinary column
I wish to concatenate all the binary column into a single binary column and then write that to another table within the database. This application splits a binary file (Word or PDF document) into multiple segments (this is Column2 as below)
example as follows
TableA
Column1 Column2 Column3
aaa 001 <some binary value>
aaa 002 <some binary value>
aaa 003 <some binary value>
aaa 004 <some binary value>
aaa 005 <some binary value>
desired results in TableB
Column1 Column2
aaa <concatenated value of above binary columns>
View 9 Replies
View Related
Oct 30, 2014
I have a table (we will cal DateTable) with several (20) columns, each being a date type. Another table's (Project) PK is referenced in the DateTable.
I am trying to write a query that will pull all dates for a specific project from the DateTable if they meet certain criteria(i.e. if the date is <= 7 days from now.
I started with a normal select statement selecting each column with a join to the project and then a where clause using
(DateTable.ColumnName BETWEEN GETDATE() AND DATEADD(day, 7, GETDATE()) OR (DateTable.ColumnName BETWEEN GETDATE() AND DATEADD(day, 7, GETDATE())) ...
The rest of the columns(all with OR between them).
The problem with this is that because I am using OR once one of the dates meets the criteria it selects all the dates that are associated with the project. I ONLY want the dates that meet the criteria and don't care about the rest.
Obviously because I have all the columns in the select statement... So I need something like
Select ALL Columns
from DateTable d
Join Project p
where p.ProjectID = d.ProjectID AND only dates BETWEEN GETDATE() AND DATEADD(day, 7, GETDATE()))
View 2 Replies
View Related
Jan 29, 2014
I'm fairly new to SQL and am just setting up a Windows 8 app using an Azure SQL server. The issue I have is looking up a part number supersession and getting the latest number. One part number can have multiple supersessions (ie RTC5756 > STC8572 > STC3765 > STC9150 > STC9191 > SFP500160 ).The data I am supplied monthly has both the superseeded items and the supersession information in both columns and is not easy to decipher - for example:
Supersessions Table
----------------------
RTC5756 | STC9191
SFP500160 | STC9191
STC9191 | STC2951
STC3765 | STC9191
STC8572 | STC9191
STC9150 | STC9191
[code]...
The newest part number is kept in a separate table - called "source" - which in this instance is SFP500160. I need access to the latest part number but also to the part's previous numbers, due to the fact that some people may still be stocking them as an old part number and for them to search by. Is there an easy and efficient way of doing both a lookup for the supersessions and a join on the two tables to minimize the queries on the database?
View 9 Replies
View Related
Sep 11, 2006
I imported a table using DTS.
I run SQL statements in my original server database. It works well.
But when I run the same instructions within a stored procedure in my new workstation where I improrted the table I get this error:
Server: Msg 515, Level 16, State 2, Procedure InsertFichierPrix, Line 11
Cannot insert the value NULL into column 'Id', table 'myDBLive.dbo.FichierPrix'; column does not allow nulls. INSERT fails.
The statement has been terminated.
Here is the SQL statements that I run on the original Database:
insert into fichierprix(nomfichier, version, descriptionfr, typeclient,....) values(@NomFichier,'Actuelle', @NomFourFr, 'MembreAcheteur', ....)
And here is my SP that I run on the new imported database:
CREATE PROCEDURE InsertFichierPrix @NomFichier varchar(50), @NomFr varchar(50),@NomAn varchar(50)
AS
declare @NomFourFr varchar(50)
declare @NomFourAn varchar(50)
SET @NomFourFr='liste fournisseur ' + @NomFr
set @NomFourAn='liste fournisseur ' + @NomAn
insert into fichierprix(nomfichier, version, descriptionfr, typeclient,...) values(@NomFichier,'Actuelle', @NomFourFr, 'MembreAcheteur'...)
GO
And here is the execution of my Stored proc on the destination database:
execute insertfichierprix @NomFichier='myfilename2', @NomFr='fournifr1',@NomAn='Fourniang1'
Thank you for helping me.
View 2 Replies
View Related
Oct 22, 2007
One of my application i am using is very slow,(SQL 2005 backend).I am trying to find out the tables and add index to it.But this time my profiler is skipping the records(Trace Skipped Records).Is there any settings?,so that it shows up all the records.
Note: i dont have the code of the application,but i can find few tables holding 2-3 millions of records.I want to know the column name,to create index.
View 8 Replies
View Related
Jun 28, 2002
Can someone please help in in understanding what I have done wrong. My company is using Backup Exec 7.3 as it's back up application. I have built a server, installed SQL, Built an database and added logins. I am backing up my database and Transact Log Files to specific folders on my partioned drive. Why when I run the backup ups, It backs up everything EXCEPT for my SQL folders. It seems to Skipp all SQL Folders. Please Help, I need to have this funtion working properly. What have I done wrong or what do I need to do to make this stop. Does this perhaps have something to dwil Here is an example of the backup Message in Backup Exec that I am recieving:
Media Name: "DIFF"
Backup of "FEPTESTE$ "
Backup set #41 on storage media #1
Backup set description: "Daily 6"
Backup Type: DIFFERENTIAL - Changed Files
Backup started on 6/28/02 at 12:36:21 AM.
The item Microsoft SQL ServerDataMSSQLDataTESTDB_Data.MDF in use - skipped.
The item Microsoft SQL ServerDataMSSQLDatamaster.mdf in use - skipped.
The item Microsoft SQL ServerDataMSSQLDatamastlog.ldf in use - skipped.
The item Microsoft SQL ServerDataMSSQLDatamodel.mdf in use - skipped.
The item Microsoft SQL ServerDataMSSQLDatamodellog.ldf in use - skipped.
The item Microsoft SQL ServerDataMSSQLDatamsdbdata.mdf in use - skipped.
The item Microsoft SQL ServerDataMSSQLDatamsdblog.ldf in use - skipped.
The item Microsoft SQL ServerDataMSSQLDataorthwnd.ldf in use - skipped.
The item Microsoft SQL ServerDataMSSQLDataorthwnd.mdf in use - skipped.
The item Microsoft SQL ServerDataMSSQLDatapubs.mdf in use - skipped.
The item Microsoft SQL ServerDataMSSQLDatapubs_log.ldf in use - skipped.
The item Microsoft SQL ServerDataMSSQLDataempdb.mdf in use - skipped.
The item Microsoft SQL ServerDataMSSQLDataemplog.ldf in use - skipped.
The item Microsoft SQL ServerDataMSSQLLOGTEST_Log.LDF in use - skipped.
Backup completed on 6/28/02 at 12:37:11 AM.
Backed up 48 files in 24 directories.
14 items were skipped.
Processed 23,876,604 bytes in 50 seconds.
Throughput rate: 27.3 MB/min
View 1 Replies
View Related
Feb 11, 2008
What causes SQL Profiler to show "SQL Profiler Trace Skipped Records"?
SQL2005 SP2 64bit
Thanks.
View 3 Replies
View Related
Dec 7, 2006
We've set up transactional replication to continue on data consistency errors and we do see in the replication monitor that records are skipped at the subscriber. Our subscriber is SQL2K and our publisher is SQL2K5. How can we see the records that were skipped and why they were skipped.
View 4 Replies
View Related
May 7, 2015
I'm using a bit-wise comparison to effectively store multiple values in one column. However once the number of values increases it starts to become too big for a int data type.you also cannot perform a bitwise & on two binary datatypes. Is there a better way to store the binary data rather than int or binary?
View 9 Replies
View Related
Jul 5, 2006
Hi.
I'm working on an application that will use SQL Server 2005 Everywhere Edition for single user installations (i.e. notebooks) and SQL Server 2005 Express Edition for network installations. The single user databases will synchronize with the network databases.
The databases will have to store a large number of documents of different kinds, most of them will be less than 200 KB in size. According to the SQL Server 2005 Express Edition documentation, I should avoid using the IMAGE data type and opt for VARBINARY(MAX) instead. The current version CTP of SQL Server 2005 Everywhere Edition does not support VARBINARY(MAX) (the maximum size is 8000 bytes).
Will the final version of SQL Server 2005 Everywhere Edition support VARBINARY(MAX)? Or should I use the IMAGE data type in both schemas or IMAGE in the SQL Server 2005 Everywhere Edition database and VARBINARY(MAX) in the SQL Server 2005 Express Edition database?
Do I have to consider anything special when the databases are synchronized via replication because of the large number of rows with binary fields?
Regards,
Gerrit
View 1 Replies
View Related
Mar 15, 2007
Hi There,I am having a strange problem with my identity column...... 1). I have a table of Products that have an identity column auto-incremented by 1. 2). I have my asp program working quite well in which the Data entry operators are adding the products into my database..... and they do not have any interface through which they can delete products........ 3). My Database is running at Web server(MS SQL Server)My problem is that when i cehcked my database.... there were around 1000 records but the auto increment number have reached to 1500. and when i checked in details then i saw that Auto number column is being skipped certain numbers..... like one entry is 1478 then the next one comes to be 1482..... and 1508 to 1516........ Its happening alot of times and it seems that SQL Server is skipping some numbers............Since it is Auto-Number so i do not have control over it through my code.... So i think the coding might not be the problem...... I have set Identity seed as well as Identity Increment both to 1.Is there any thing that you can suggest me to do??(Thanx)
View 12 Replies
View Related
Nov 17, 2007
I tried to load a fixed width flat file with around 300,000 rows. However, only the first 8xxxx rows were loaded to the destineation table and the rest row were loading blank records. There was no error message showing during package execution. I've tried to split the file in half and the result was the same. So it wasn't the data file problem.
Would there be any buffering issue I need to cater for inside the package? Thanks!
View 10 Replies
View Related
Aug 27, 2015
I am copying the template with header before loading the data. I tried deleting Dataflow task, Excel Connection manager etc., nothing seems to work and there are no nulls in the data. I did this several times, workaround seems to not working this time.
View 9 Replies
View Related
Nov 8, 2007
Hi All,
I am using sql server 2005. I stuck out in a strange problem.
I am using view in my stored procedure, when I run the stored procedure some of the rows get skipped out means if select query have to return 10 rows then it is returning 5 rows or any other but not all, also the records displyaing is randomly coming, some time it is displaying reords 12345 next time 5678, other time 2468.
But if I run seperately the querys written in SP then it returns all the rows. Please give me solution why it is happening like this.
There are indexes in the tables.
Once I shrink the database and rebuild the indexes, from then this problem is happening. I have rebuild the indexes several time, also updated the statistics but nothing improving.
But nothing is improving
View 7 Replies
View Related
Jul 14, 2004
Has anyone seen the SQL Server error:
"tempdb is skipped. You cannot run a query that requires tempdb"?
We're running a .Net web application with a SQL Server 2000 backend, and we get the error intermittently. Restarting the SQL Server service seems to fix it, as it causes tempdb to be rebuilt, but this isn't a long term solution. Any direction or hints would be greatly appreciated. Thanks!
- Mike
View 11 Replies
View Related
Dec 13, 2007
Hello:
I am running into an issue with RS2k PDF export.
Case: Exporting Report to PDF/Printing/TIFF
Report: Contains 1 table with 19 Columns. 1 column is static, the other 18 are visible at the users descretion. Report when printed/exported to pdf spans 2 pages naturally, 16 on the first page, 3 on the second, and the column widths have been adjusted to provide a perfect page span .
User A elects to hide two of the columns, and show the rest. The report complies and the viewable version is perfect, the excel export is perfect.. the PDF export on the first page causes every fith column, starting with the last column that was hidden to be expanded to take up additional width. On the spanned page, it renders the first column on that page correctly, then there is a white space gap equal to the width of the hidden columns and then the rest of the cells show with the last column expanded to take up the same width that the original 2 columns were going to take up, plus its width.
We have tried several different settings to see if it helps this issue or makes it worse. So far cangrow/canshrink/keep together have made no impact. It is not possible to increase the page size due to limited page size selection availablility for the client. There are far too many combinations of what the user can elect to show or hide to put together different tables to show and hide on the same report to remove this effect.
Any help or suggestion on this issue would be appreciated
View 1 Replies
View Related
Sep 9, 2007
I'm a newbie to SQL and wanted to see if someone could help me write a query. I store historical options data in a SQL data base and I'm trying to create a query that will retrieve all ATM (at the money options).
For each unique stock symbol (STOCK_SYMBOL) in my database find the strike price (STRIKE) which is closest to the stock price (UND_LAST). This will be refined by the EXPIRATION and OPT_TYPE
The following query returns all options not just ATM options.
SELECT STOCK_SYMBOL, UND_LAST, STRIKE, EXPIRATION FROM DATA
WHERE EXPIRATION > '2007-02-01 00:00:00'
AND EXPIRATION < '2007-02-28 00:00:00'
AND OPT_TYPE = 'CALL'
Related DB fields.
STOCK_SYMBOL
STRIKE
UND_LAST
OPT_TYPE
EXPIRATION
Any help would be appreciated.
View 2 Replies
View Related
Nov 8, 2007
I am using MS SQL Server 2005 Import and Export Wizard (.net framework Data Provider for mySAP Business Suite. )
as a interface between SAP and MS SQL 2005.
Once the connection is created i can use basic query to fetch the data from SAP tables
ie., SELECT * FROM AFPO
But say I want to EXTRACT data just for month of July from AFPO.
AFPO does not have date field so you have to join AFPO to AUFK and AUFK to COEP to fetch the date as a criteria to restrict data for the month of July .
This can be done in ABAP by view or Interim Table, if you have Developer Key.
Can anyone please tell me if aforesaid is possible with MS SQL Server 2005 Import and Export Wizard query ?
View 3 Replies
View Related
Nov 9, 2006
Hello,
I want to find all the entries where the date field is a specific date; so I may have 5 entries with the date 1/1/2006, and I want to find all of those. However, datetime fields in SQL Server also have the time, so how do you handle that? Can you just say where requestdate = getdate() to get all of the entries that have records for today?
How does time affect this?
View 5 Replies
View Related
Sep 28, 2007
Hi,I am new to sql and was wondering if someone could help with this select statement. please don't laugh! SELECT * FROM product_detailsWHERE MemberId = @MemberIdINNER JOIN Member_Ratings ON product_details.MemberId = member_ratings.MemberIdSELECT COUNT(*) FROM member_ratings AS FinalHighRating WHERE member_ratings.MemberId = product_details.MemberId AND member_ratings.Rating = 5 Any questions? Thanks!
View 7 Replies
View Related
Oct 31, 2007
Hey,
What is the best way to query two datetime values to see if they are alike? I need to determine if an [AccountEndDate] is today (both values are datetime). Using "Like" does not work.
Thanks!
View 4 Replies
View Related
Jan 27, 2008
If you create a view such as CREATE VIEW TestView AS Select * FROM customers_table WHERE CustomerID between 213 AND 3443 Each time you want to select from the view - is the View going to perform the WHERE clause on the customers_table, or not? Or does the view already have the virtual rows?, meaning it does not have to perform a where clause (WHERE CustomerID between 213 AND 3443) on the customers_table, and just performs an inner join with the rows it already has?
View 5 Replies
View Related
Aug 10, 2005
I have a usage table Aaccessdate (datetime)useridpageidI need to write a sql that would do a report on a monthly basis. ( how many pagehits per month)All is fine - and my SQL looks likes thisselect count(pageid)from A.The twist here is , I do not want to count those records if the same user visits the page on the same day.For Eg02/03/2005 10:09:09.000 user1 page102/03/2005 15:09:09.000 user1 page102/03/2005 11:09:09.000 user2 page202/03/2005 16:09:09.000 user3 page3I want my count to give me back 3 instead of 4.Can this be done?ThanksRajini
View 4 Replies
View Related
Mar 20, 2006
I have a table containing prices. This table will be queried very often to provide quotes for clients.So to ease the burden on the server I want to cache the table and then just query the cached version.However it seems that I can only cache the table as a datatable. This means I have to query the datatable to get the prices for each quote.I'm not sure how to query a datatable. Is ther syntax similar to querying a SQL table?In fact is this best way to go about things?Any help would be appreciated.G
View 4 Replies
View Related
Apr 20, 2006
Hi guys
im struggling to see what im doing wrong with the following SQL, wondering if it has something to do with the subquery being a view...cant think why though
select value from table
where type = 'Extra Mailbox'
and active = 1
and value not in
(select login from view)
order value
The view returns a list of logins and the type field is also a list of logins. I want to filter the list of logins that do not appear in the subquery....
Any ideas?
Thanks in advance for any responses.
Ging
View 2 Replies
View Related
Oct 1, 2004
I have 2 different database's on the same server. I'm trying to create a stored proc that resides in Reporting database but queries against the Call database. 4 part naming convention gives me an error of 'invalid object name' What am I forgetting here?
View 5 Replies
View Related
Apr 30, 2008
Hello all,
I have a table (tbl_a) with 2 columns: itemNames | Date .
ItemNames can have duplicates.
I have another table (tbl_b) with date ranges in 2 columns: startDate | endDate.
what I would like to do is: query tbl_a where date between startDate and endDate of tbl_b.
does anyone have an idea?
thanks in advance.
View 2 Replies
View Related
May 23, 2008
This is my problem
Table A Columns:
OriginatingNumber,
TerminatingNumber
Both of these columns contain npanxx numbers which would be the first 6 digits of a phone number, example 217345 which is in illinois.
Table B Columns:
npanxx,
state
table B hold all npanxx numbers in the united states and what state it pertains to.
what i need to do is run a query to check and see what state the OriginatingNumber and the TerminatingNumber are in and decide if both numbers passed are in the same state or not.
so for example in Table A Row 1: OriginatingNumber is 217345 and the TerminatingNumber is 309454. I need to check both of these numbers against table B and it would return that both numbers are in IL. Basically I need to return a result of yes or no. yes they are in the same state or no they are not in the same state.
Any help would be great
Thanks for reading,
Nick
View 3 Replies
View Related
Jun 12, 2008
how to write a query to find the no of tables present in that database.
Any suggestions.
View 4 Replies
View Related
Jun 12, 2008
Hi All
Thx in advanced for any help anyone can offer me.
I have 2 tables Staff and Class which are as follows
Staff
ID (Primary Key)
FirstName
MiddleName
Surname
Etc. . .
Class
ID (Primary key)
LessonTemplateID
TeacherID
TermID
AssistantID
LessonTemplateID and TermID link to other tables which i don't need right now.
The Class.TeacherID and Class.AssistantID both link to the Staff.ID
I am trying to create a query where i can say i want to select a class.ID of lets say 12 and display both the Teachers name and the Assistants Name.
If i do just one join between Staff.ID and lets say Class.TeacherID all is fine and i can display the teacher.FirstName for a particular class.ID. However my problem is when im trying to display both teacher and assistant. Every time i try to create a query to do this the query comes back blank.
Im really stuck on this and just can't figure it out (is it even possible?).
Thx for any help Scott.
View 2 Replies
View Related
Jan 23, 2007
Hi there,
Is there a way where I can run a query or a store procedure where i can retrieve all the contraints attached to the tables in the database?
thanks
View 1 Replies
View Related
Apr 21, 2007
Hi All,
I am very new to sql and I am not really sure of what I am doing.
I have a bit of a complicated stored procedure that I am trying out. There is 3 parts to it to search for new articles,updates and discontinued articles for a particular Supplier. Right now I am just trying to get the first part working - I am trying to query another database that has 4 tables, 2 are joined ActiveArticle & ActiveArticlePrice and the other 2 are also joined UpdateArticle & UpdateArticlePrice. But all tables also hold data for ALL Suppliers, they are recognised by a GUID - SupplierGuid.
I want to compare them to each other to see if there are any new articles added to the Update tables. This is what I have so far, I was hoping someone could tell me if they see some obvious mistakes(I'm sure theres many!). For the first select, I tried adding a couple of parameters Flag and Text to use so that I can order it all once i have the 3 parts working. But I don't know the syntax is right, because then I also want to select all fields in both tables?.
CREATE PROCEDURE Portal_GetPriceListUpdates
(
@SupplierGUID uniqueidentifier
)
AS
SELECT 'A' AS FLAG, 'New Product' AS TEXT, * FROM SupplierUnits.UpdateArticle UA, SupplierUnits.UpdateArticlePrice UAP WHERE EXISTS
(SELECT * FROM UA, UAP WHERE UA.SupplierGuid=@SupplierGUID AND UA.SupplierArticleNumber NOT IN (SELECT SupplierArticleNumber FROM SupplierUnits.ActiveArticle.SupplierArticleNumber))
GO
I would appreciate any help!
Thanks,
pp
View 2 Replies
View Related
