Tuesday, June 18, 2013

[SQL Server 2008 issues] String or binary data truncation.

[SQL Server 2008 issues] String or binary data truncation.

String or binary data truncation.

Posted: 17 Jun 2013 05:27 PM PDT

Can someone help me with determining why my TestTable data does not roll over into my ResultTable data? I have reviewed all of the data types but cannot figure out why I am not getting any data in the ResultTable. I have tried to put together input data for this posting into the #mytable but cannot figure out how to completely populate this table. Anyhow here is what I have:[code="sql"]--===== If the test table already exists, drop it IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL DROP TABLE #mytable--===== Create the test table with CREATE TABLE #mytable ( Year varchar(4), Month varchar(2), Day varchar(2), Hour varchar(2), Minute varchar(2), Second varchar(2), Milisecond varchar(3), Ask float, Bid float, AskVolume float, BidVolume float, Item char(2) ) INSERT INTO #mytable (Year, Month, Day, Hour, Minute, Second, Milisecond, Ask, Bid, AskVolume, BidVolume, Item)SELECT '2013', '05','04', '10', '12', '45', '200', '1.23054', '1.23052', '1', '3', 'N'SELECT '2013', '05','04', '10', '12', '46', '400', '1.23055', '1.23052', '1', '3', 'N'SELECT '2013', '05','04', '10', '12', '46', '500', '1.23055', '1.23052', '1', '3', 'N'SELECT '2013', '05','04', '10', '12', '47', '400', '1.23055', '1.23052', '1', '3', 'N'SELECT '2013', '05','04', '10', '12', '49', '400', '1.23055', '1.23052', '1', '3', 'N'DECLARE @ResultTable TABLE (curRN bigint, prevRN bigint, curBid Decimal(6,5), prevBid Decimal(6,5), Item char, Concat Varchar(20));DECLARE @TestTable TABLE (Year Varchar(4), Month Varchar(2), Day Varchar(2), Hour Varchar(2), Minute Varchar(2), Second Varchar(2), Milisecond Varchar(3), Bid Decimal(6,5), Item Char(2), Concat Varchar(20));INSERT INTO @TestTable (Year, Month, Day, Hour, Minute, Second, Milisecond, Bid, Item, Concat)SELECT Year, Month, Day, Hour, Minute, Second, Milisecond, Bid, Item, (Year+Month+Day+Hour+Minute+Second+Milisecond) AS ConcatFrom #mytable;WITH cte AS (SELECT Year, Month, Day, Hour, Minute, Second, Milisecond, Bid, Item, Concat, RN = ROW_NUMBER() OVER (ORDER BY Year, Month, Day, Hour, Minute, Second, Milisecond, Bid, Item, Concat)FROM @TestTable)INSERT INTO @ResultTable (curRN, prevRN, curBid, prevBid, Item, Concat)SELECT cur.RN AS 'Cur-RN', prev.RN AS 'Prev-RN', cur.Bid AS 'Cur-Bid', prev.Bid AS 'Prev-Bid', cur.Concat, CASE WHEN (cur.Bid - prev.Bid) > 0 THEN 'U' WHEN (cur.Bid - prev.Bid) = 0 THEN 'F' WHEN (cur.Bid - prev.Bid) < 0 THEN 'D' ELSE 'N' END FROM cte cur JOIN cte prevON cur.RN = prev.RN + 1SELECT *FROM @ResultTable[/code] (249616 row(s) affected)Msg 8152, Level 16, State 14, Line 7String or binary data would be truncated.The statement has been terminated.(0 row(s) affected)

SQL Command

Posted: 17 Jun 2013 07:12 PM PDT

what is the command to know how many users are using table emp at this moment.

Need Sub-Query ?

Posted: 17 Jun 2013 07:11 PM PDT

Create Table Table1(Sno int,Sname varchar(20)); insert into table1 values(1,'a'), (2,'b'), (1,c), (2,d)select * from Table1Sno Sname-- ------1 a2 b1 c2 di want the output as following by using the sub-queries?Sno Name1 Name21 a c2 b d

SSRS - Login failed for user error

Posted: 26 Aug 2009 06:03 AM PDT

Hello! I hope that someone may be able to help as I've been unsuccessful in getting this to work.I have several reports (using SSRS 2008) that use the same shared data source. The data source is set up to use Stored Credentials with a windows account/password. When I updated the data source through Report Manager and entered my username/password, all is OK until I click the 'Use as Windows credentials when connecting to the database' box. At that point, I get an error 'The password is not valid. Please retype the password.' I know that my password is correct as I've been able to use it successfully in other places.When I run any reports using this data source, I get the following error (probably due to the fact that the report is trying to run as a SQL account -- * An error occurred during client rendering. o An error has occurred during report processing. (rsProcessingAborted) + Cannot create a connection to data source 'XXXX'. (rsErrorOpeningConnection) # Login failed for user 'XXXX\XXXX'.*where the X's are my data source and domain\account.I have even tried deleting and redeploying to no avail. The strange thing is, is that I have another data source that works in the same manner that is working OK...unless of course I click the above mentioned box off, then I get the same error about the password being invalid.Thanks!Danielle

Table Partition

Posted: 17 Jun 2013 06:41 PM PDT

Hi All,I have partitioned five tables with same date range for all tables(with single partition function and single partition scheme) . Now i want to remove partition from one table among the five tables(Remaining 4 tables should have partition) .How i can remove partition from that singe table with out losing any data. I have 20 millions data in that table.Note: While creating partition , is it possible to create non clustered index on table partitioned column. If it possible, is there any impacts on partition.

Partitioning in SQL Server 2008

Posted: 17 Jun 2013 01:27 AM PDT

Hi All, I have got few queries on partitioning in SQL Server.- Why should we go for partition in SQL Server? - How do we decide on which column should the table get partitioned?- Can I place few tables in db on partitioned, and rest on Primary? Or should I place the rest of the tables on NonPartition?- Once I partitioned my db, what should I do on my db server i.e., do I need to run any dbcc commands etc.?- If not partitioning, do we have any options through which we can improve the performance of an db?Regards,Sai Viswanath

CTE Problem :By Shubham Saxena

Posted: 17 Jun 2013 06:01 PM PDT

Dear friends,I need to concatenate a char with in CTE for ex:with cteexp(q,col) as (select cast(1 as int) p,'X' as col union all select cast(q as int)+1, cast(col as nvarchar(50))+CAST('p' as nvarchar(50)) p from cteexp where cast(q as int)<100 )select * from cteexpbut getting errorMsg 240, Level 16, State 1, Line 1Types don't match between the anchor and the recursive part in column "col" of recursive query "cteexp".Kindly, suggest where i am wrong and what is the solution. Regards,Shubham Saxena

Group by Rollup Output Format

Posted: 17 Jun 2013 04:08 PM PDT

Dear,I execute the following query and get the result.[code="sql"]select ISNULL([State],'Whole State') AS [State], ISNULL(City,'All City') AS City, SUM([Population]) AS [Population]from tblPopulationgroup by rollup([State], City)[/code][code="plain"]State City Population-------- ---------- -------------Delhi East Delhi 9Delhi North Delhi 5.5Delhi South Delhi 8Delhi West Delhi 7.5Delhi All City 30Karnatak Bangalore 9.5Karnatak Belur 2.5Karnatak Manipal 1.5Karnatak All City 13.5Maharastra Mumbai 30Maharastra Nagpur 11Maharastra Nashik 6.5Maharastra Pune 20Maharastra All City 67.5Whole State All City 111[/code]You See in [State] column, [b]Delhi[/b] is repeated 5 times, [b]Karnatak[/b] is repeated 4 times and [b]Maharastra[/b] is repeated 5 times.But I require that Delhi, Karnatak, Maharastra will appear once on the top row. My desired output would look like the following. [code="plain"]State City Population-------- ---------- -------------Delhi East Delhi 9 North Delhi 5.5 South Delhi 8 West Delhi 7.5 All City 30Karnatak Bangalore 9.5 Belur 2.5 Manipal 1.5 All City 13.5Maharastra Mumbai 30 Nagpur 11 Nashik 6.5 Pune 20 All City 67.5Whole State All City 111[/code]Please help me to do it.Rgds,Akbar

Need Create View Script for all tables

Posted: 21 Aug 2012 06:37 PM PDT

I manage a SQL 2008 R2 Production environment for an application. We have a new requirement to create views( simple--select * from tablename) for all existing tables (182 tables) in the database.We will then provide access to these views for Reporting Service users to create their reports. This is so as not to affect database performance for App users.[b]Can someone provide me a script which generates 'create view' scripts for all existing user tables in the database?[/b]Thanks in advance.

Trying to understand this SQL Query

Posted: 17 Jun 2013 09:46 AM PDT

Hi.I am fairly new to SQL Server and have come across this SQL Server Query at work and I am trying to understand their logic here.Is there a more tidy way to re-write this query, I am not even sure if it is return the desired unique results or if it is return a cartisian product.anyway professionals here is the code I am trying to work with[code]SELECT distinct a1.Netbios_Name0, c1.SerialNumber0, a1.Operating_System_Name_and0, b1.Publisher0, b1.DisplayName0, b1.Version0, b1.InstallDate0, c1.TimeStamp FROM v_R_System a1 inner join v_add_remove_programs b1 on a1.ResourceID = b1.ResourceIDinner join v_GS_PC_BIOS c1 on a1.ResourceID = c1.ResourceIDleft outer join v_GS_CCM_RECENTLY_USED_APPS d1on b1.ResourceID = d1.ResourceIDand b1.GroupID = d1.GroupIDGROUP BY a1.Netbios_Name0, c1.SerialNumber0, a1.Operating_System_Name_and0, b1.Publisher0, b1.DisplayName0, b1.Version0, b1.InstallDate0, c1.TimeStampORDER BY 1;[/code]

Regarding how many time an USP is executed.

Posted: 17 Jun 2013 08:16 AM PDT

Is there any way to find out how many times a USP was executed and how much time it took to execute? Please advise.Thanks,SueTons.

SS2008R2 - Are Compressed DB BAckups Restored AS Regular DB's or COmpressed DB's

Posted: 17 Jun 2013 08:01 AM PDT

Under SQL Server 2008R2, if you when backing up a DB you select the option to Compress The Backup, then when that DB is restored will it be restored such that it was the same size (roughly the same size) as the orginal DB the backup was made from or will it be rrestored as a compressed copy of the DB menaing it will be as small as the compressed backup?I have to sned a 240GB DB over the wire and so I wanted to compress the backup and ideally reduce the total size of teh backup that I have to send via secure ftp. Now that I've done this and started sending the DB I'm seeing related posts/comments on the web about this that lead me to bvelieve that the COmpress option is not for the compression of just the backup but of the DB. In other words if I have a 240GB DB that I backup (with the compress option) and comrpess dowen to 1/10th that size, then when that backup is restored the resulting DB is not about 240GB but is instead about 1/10th that size and thats because the restored DB is itself compressed.Thoughts?Thanks

dynamic sql question

Posted: 17 Jun 2013 06:47 AM PDT

I am new to dynamic SQL and I have the following from my sql profiler.my understanding was the syntax wasexec Stored_Procedure ParametersI don't understand what the bold text below is. They do not look like paramaters being sent into the stored procedure, but rather results coming back from the stored procedure. The rest of the items in the list look like parameters being passed into the stored procedure. Is that what the word output is for with each of these?Is there a syntax document online somewhere?How far off am I?declare @p1 intset @p1=1declare @p2 nvarchar(255)set @p2=N'ng_add_sig_events: (Success), Sig Event Added.'exec ng_add_sig_events [b]@po_result_code=@p1 output,@po_result_message=@p2 output,[/b]@pi_practice_id=N'0011',@pi_enterprise_id=N'00021',@pi_sig_event_id='A9D57824-638',@pi_source1_id='557D78F4C',@pi_source2_id=NULL,@pi_source3_id=NULL,@pi_source4_id=NULL,@pi_event_source_type=N'4',@pi_sig_id=N'38',@pi_sig_msg=N'Employer Added',@pi_pre_mod=N'<none>',@pi_post_mod=N'Foo',@pi_user_id=154,@pi_group_id=NULL,@pi_check_sig_admin_ind=N'N',@pi_create_timestamp_tz=0select @p1, @p2

Database Mail - Could not connect (no such host)!

Posted: 17 Jun 2013 03:16 AM PDT

Hi all,Tried to find a fix on here for my issue but everything I've found re the above problem me and my team have eliminated so was wondering if someone else could suggest something?I've just installed SQL Server 2008 R2 on a new virtual server (VM Ware) running Windows Server 2008 R2 Enterprise which was installed from an image of another virtual server which I also installed SQL Server on which performs perfectly.The SQL Server install has gone fine however, I've set up database mail and used exactly the same settings and configurations as the previous server but keep getting the following error/exception message when I try to send a test email through SSMS:"The mail could not be sent to the recipients because of the mail server failure.......Exception message: Could not connect to mail server (No such host is known)"Things we've looked into:Correct spelling of everythingTested port 25 is openNo antivirus on virtual server to block connectionAntivirus on Exchange server allows connections via port 25Other servers with the same database mail setup can send and receive test emailsCan telnet to Exchange server from virtual serverCan ping Exchange server from virtual serverWe're at a bit of a loss, can anyone suggest anything please? :ermm:

how to identify which user is running query and how long is it running?

Posted: 17 Jun 2013 06:44 AM PDT

how to identify which user is running query and how long is it running?

When compressing a DB, will it cause growth first?

Posted: 17 Jun 2013 04:16 AM PDT

I know the subject isn't the clearest, but I think it'd be too long to put the entire question...I'm looking at enabling compression on a table in one DB. the table is ~313 million records, and has one Clustered Index and 3 non-clustered. I'm planning to initially enable page-level compression on the CI, as I think that's going to give me the most savings (also backed up some by the estimate space saving SP)What I'm concerned about, is *how* the compression is accomplished. Not the mechanics of the compression (I know page level is actually row-level first, then the pages,) but the how of it being done. Basically, if I enable this feature, will SQL need to hit the transaction log hard, possibly causing it to grow?The DB in question is in Simple recovery, but the disks the DB and log live on are somewhat slim on space (because of this DB, actually) In a previous posting [url=http://www.sqlservercentral.com/Forums/Topic1452717-391-1.aspx](Here)[/url] I had laid out what I was planning to do to recover some disk space from this table. The table in question had some "issues" that myself and the Dev had to work out (no CI, no PK... And he was trying to delete a couple million rows and it was taking days...) which in resolving, we didn't notice the table was originally page-compressed...Yeah, long story...Thanks,Jason

Change Tracking and Log Shipping

Posted: 17 Jun 2013 05:37 AM PDT

I am trying to test, and it is not working so I think I have the answer.Here is what I am trying to do. Primary database setup Change Tracking. Log Ship this to Secondary server in read only mode. Roll transactions forward to Secondary once a day - use Change Tracking from Secondary Read-Only database for our ETL process.Thoughts?

Back up failed

Posted: 17 Jun 2013 12:30 AM PDT

Hi AllI have one backup database schedule Every night. It went well everyday but today it failed.I check backup location, there is backup file but Job History says it failed.[code="plain"]MessageExecuted as user:XXXXXXXXX. ...00.4035.00 for 32-bit Started: 3:00:04 AM Progress: 2013-06-17 03:00:07.73 Source: {B73199AA-F4BC-4962-AFC1-B033F62D7BCD} Executing query "DECLARE @Guid UNIQUEIDENTIFIER EXECUTE msdb..sp".: 100% complete End Progress Progress: 2013-06-17 03:00:07.93 Source: Maintenance Cleanup Task Executing query "EXECUTE master.dbo.xp_delete_file 0,N'\\ServerName".: 100% complete End Progress Progress: 2013-06-17 05:07:28.18 Source: Back Up Database Task Executing query "BACKUP DATABASE [AAAAA] TO DISK = N'\\ServerName-".: 100% complete End Progress Error: 2013-06-17 05:08:07.41 Code: 0xC0024104 Source: Reporting Task for subplan-{7F0A750F-B2C0-42DD-966F-7A70E4D2FC93} Description: The Execute method on the task returned error code 0x80131904 (Timeout expired. The timeout period elapsed prior to completion of the ... The package execution fa... The step failed.[/code] Please help me to understand.

How to find dependencies in dynamic sql queries

Posted: 17 Jun 2013 12:25 AM PDT

I think it is far fetched because after searching the internet for days I found nothing.I want to find out all the object referring the columns in a table. I have a fantastic working query for the same but it does not list objects with dynamic SQL.Is there any way with which we can find dependencies in dynamic sql queries?

How to find dependencies in dynamic sql queries

Posted: 17 Jun 2013 12:25 AM PDT

I think it is far fetched because after searching the internet for days I found nothing.I want to find out all the object referring the columns in a table. I have a fantastic working query for the same but it does not list objects with dynamic SQL.Is there any way with which we can find dependencies in dynamic sql queries?

generate scripts in sql server

Posted: 16 Jun 2013 09:39 PM PDT

I have some doubts related to SQL server 2008 R2 express 'generate scripts' option.1)If i select script entire database and all database objects then all objects such as database,permission,table structure,index are created?2)If i 'select only select specific database objects', and then select only tables(all tables) then does index is created or only table will be created? Also permissions for tables set?(when i tested clustered index are created with primary key,non clustered are not created. So i am not sure what does this option do)3) If i select both table and user in 'select only select specific database objects' then tables and its permissions with users created?

what is the use of power shell in sql server 2008

Posted: 16 Jun 2013 11:14 PM PDT

what is the use of power shell in sql server 2008

PhysicalDisk perfmon counters versus LogicalDisk counters

Posted: 16 Jun 2013 10:52 PM PDT

Hi,We have a Physical disk which is partitioned into 4 drives,D,E,F,J. I am monitoring individual logical disk idle time and physical disk idle time. These are a day's average values[code="plain"]Counter ValueLogicalDisk(D:)\%idle time 84.92676725LogicalDisk(E:)\%idle time 87.60788474LogicalDisk(F:)\%idle time 94.81640539LogicalDisk(J:)\%idle time 98.49689629PhysicalDisk(1 D: E: F: J:)\% Idle Time 74.4477885[/code]I noticed PhysicalDisk valueis much lower than average of LogicalDisk values,even though LogicalDisk values look good. PhysicalDisk value is suppose to be average of LogicalDisk values right? Why this variation caused?Thank you

script in sql server 2008 for generating .ndf files

Posted: 16 Jun 2013 10:18 PM PDT

can any one give me script for generating .ndf files in sql server 2008

How to remove comma and convert in to INT

Posted: 16 Jun 2013 09:40 PM PDT

Hi,How to remove comma and how to convert this column into [numeric](17, 2) this formate.I have conevrt my this column values in this format [numeric](17, 2) NULL now this column have nvarchar datatype in my source.Please sugget[code="sql"]drop table dataconvertgocreate table dataconvert(data nvarchar(15))insert into dataconvert values('$0')insert into dataconvert values('$40,000')insert into dataconvert values('$2,350')[/code]Regards,KRaj

How to connect with SQL Server using http based API

Posted: 10 Jun 2013 11:01 PM PDT

Hi,I am new to this. I want to connect SQL SERVER 2008 with a CRM called Salesforce (without using any existing tools).Some expert suggested me that it can be done using [b]http based API of SQL Server 2008[/b].I want to know whether http based API of SQL Server is available and how it can be used.Or is there any other way by which i can connect with SQL Server (by coding)[b]Please help.[/b]Thanks
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog