Is there a way to assign nullability on a column when using a select into?
I've tried some of the usual things like coalsce, isnull, and cast. Since
the new table gets definition from the source table or can be somewhat
adjusted with cast is there a way to cast a not null? In the example below
how can I select into causing tableone_new..col2 to be not null. We
typically must use an alter statement after the select into but this seems
inefficient.
Thanks,
Ray
create table tableone (
col1 int not null,
col2 int )
go
insert tableone values (1, 1)
insert tableone values (2, 2)
insert tableone values (3, 3)
go
select
col1,
col2
into tableone_new
from tableone
go
exec sp_help tableone_new
go
drop table tableone
go
drop table tableone_new
go
I have the following problem: in a data flow, if inserting new records, there are columns that take some default values. These default values are kept in a table in case the user wants to change them some day. Def. values could not be assigned at a table level because there's another dataflow that populates the same table, but the rules for the default values are different.
Since I want to extract these values only if there is at least one new row, I'm not fond of the idea to use Execute SQL Task (to save the default values in a variable) before the actual Data Flow. What are my options in getting these values in a Data Flow right before inserting? Thank you for the help.
I would like to assign the value of a variable to a column name of a table. For eg:
declare @sam varchar(100) set @sam = 'Insert' create table #temp(Sample varchar(100) not null) insert into #temp(Sample) Values(@sam) drop table #temp
I would like to generate a table whose column name should be generated dynamically. The value of @sam that is "Insert "should be set to the column name. Is there a way to assign like this. Can anyone please help me in achieving this.
The problem I'm having is described below all this code.---------------------------------------------------------My content page has a SqlDataSource: <asp:SqlDataSource ID="sqlGetUserInfo" runat="server" ConnectionString="<%$ ConnectionStrings:RemoteNotes_DataConnectionString %>" SelectCommand="SELECT [UserFirstName], [UserLastName] FROM [Users] WHERE ([UserGUID] = @UserGUID)" onselecting="sqlGetUserInfo_Selecting"> <SelectParameters> <asp:Parameter Name="UserGUID" Type="String" /> </SelectParameters> </asp:SqlDataSource> -----------------------------------------------------Inside my OnSelecting event, I have: protected void sqlGetUserInfo_Selecting(object sender, SqlDataSourceSelectingEventArgs e) { sqlGetUserInfo.SelectParameters["UserGUID"].DefaultValue = Membership.GetUser().ProviderUserKey.ToString(); } ---------------------------------------------------------------------And, inside my Page_Load, the relevant code is: //String strUserFirstName = ((DataView)sqlGetUserInfo.Select(DataSourceSelectArguments.Empty)).Table.Rows[0]["UserFirstName"].ToString(); DataView dvUserDetails = (DataView)sqlGetUserInfo.Select(DataSourceSelectArguments.Empty); if ((dvUserDetails != null) && (dvUserDetails.Count > 0)) { DataRow drUserInfo = dvUserDetails.Table.Rows[0]; lblHelloMessage.Text = "Hello, " + drUserInfo["UserFirstName"].ToString() + ((drUserInfo["UserLastName"].ToString() == "") ? "" : " " + drUserInfo["UserLastName"].ToString()); } ---------------------Now, here's the problem. My "if" statement is never executing because "dvUserDetails" is null. However, when I break the execution and put awatch on the actual Select() statement, it shows a DataView with the correct return rows! You can see the commented line where I tried tobypass the DataView thing (just as a test), but I get an object reference is null error.The weird thing is that it was working fine one minute, then started getting "funky" (working, then not, then working, then not), and now it just doesn't work at all. All thiswithout me changing one bit of my code because I was checking out some UI flow and stopping and restarting the application. I've tracked downthe temporary files directory the localhost web server runs from, deleted all those files, and cleaned my solution. I've even triedrebooting, and nothing seems to make it work again.My relevant specs are VS2008 9.0.21022.8 RTM on Vista Enterprise x64.
I think I'm just braindead or simply thick...since this shouldn't be that hard, but I'm stumped right now.
So, I'm trying to retrieve from a table, with a sql stored procedure with the sql like "select height, width, depth from products where id=@idinput"
OK, so this part is easy, but if I wanted to say, return this to my code and assign height to a variable Ht, width to Wd and depth to Dp, how could I do that?
I have written a Query to Pivot this data like below:
SELECT WAREHOUSE,ITEM, QTY
FROM
(SELECT ITEM,WAREHOUSE,FOR1,FOR2,for3,for4,for5,for6,for7,for8,for9,for10, for11,for12,for13,for14,for15,for16,for17,for18,for19,for20,for21, for22,for23,for24 FROM mvxreport.tbldmsForecasttoMovex) p
UNPIVOT
(QTY FOR tbldmsForecasttoMovex IN (FOR1,FOR2,for3,for4,for5,for6,for7, for8,for9,for10,for11,for12,for13,for14,for15,for16,for17,for18,for19, for20,for21,for22,for23,for24))AS unpvt
I would like to add some more code to the query, so for each FOR% column, i can put a numeric value in it. The value will be the numbers ,1 - 24 . One for each line as this represents Months Forward.
How to assign multiple values to emp1 variable separated by comma Ex: If ajay karthik vijay are employee names in emp table then emp1= ajay,karthik, vijay set emp1= (select employeename from emp) returns error error: subquery returns morethan one value
Hi, I'm try to get the query second column value when it is assinged to a varchar variable. Ex:In SP below is statement I wrote SET @Values = (SELECT COL1,COL2 FROM TABLE) Question 1: How to access the COL2 value from @Value2? Question 2: How to access the other row values if above returns more than a row? Please send me the solution as soon as possible. Thanks-Vikash/Bala
Will writes "I have an employees table, which gives all emp. ids. I have a second table, time_log with tasks each employee has logged:
empID date log_time duration etc ===================================
and a 3rd table - a pivot table with a single column named "i" containing consecutive integers from 0 - 1000
I need to know for each date in a series, e.g seven consecutive days, how much time each has logged. easy if everyone has logged a task for every day, but I need to include every day where they have not logged a task.
so, a cartesian join on all the dates in a series(produced using dateadd on p.i and the pivot table)
SELECT dateadd('d',p.i, #02/19/2007#), e.empID FROM pivot1 p, employees e WHERE i<no_dates
However I need to do a left join with time log where the date and employee ids are the same, and I have summed the durations for each date. The following query does this, but does not include dates and times where nothing has been logged.
SELECT empID, log_date, sum(duration) FROM time_log GROUP BY empEIN, log_date
GIVING, EVENTUALLY, ALL DATES AND EMPIDS AND THE TOTAL AMOUNT OF TIME THEY HAVE LOGGED FOR EACH DAY."
I am looking for a few pointers on how to achieve something. For the purpose of my application I need to specify a SQL Server table. After this I wish to be able to loop through column types and calculate the appropriate .NET datatype based on the SQL Data Type, and also figure out if the column allows NULL values.
I'm not looking for someone to do my work for me, just some pointers on what I should be reading to achieve my aim.
I have an Execute SQL Task that executes "select count(*) as Row_Count from xyztable" from an Oracle Server. I'm trying to assign the result to a variable. However when I try to execute I get an error: [Execute SQL Task] Error: An error occurred while assigning a value to variable "RowCount": "Unsupported data type on result set binding Row_Count.".
Which data type should I use for the variable, RowCount? I've tried Int16, Int32, Int64.
Is there a way to control the types for output columns of a DataReader Source? It appears that any System.String will always come out as DT_WSTR. As I have my own managed provider, and I know what went in, I can say that really it should be DT_STR. The GetSchemaTable call from my provider will always say System.String as it does not have much choice, but GetSchemaTable does contain a ProviderType which is different for my DT_STR vs DT_WSTR, or rather when I want each. I think something like MappingFiles as used by the Wizard would work, but can I do anything today?
Does anyone have a clue as to why DT_R4, DT_R8 are not allowed as join columns? This means I cannot join tables on AccountNumbers, InvoiceNumbers, etc. What a pain...
I would like to create a table which can store a VLOB or BLOB.. which are pictures and images.. How do I insert them into the database and how do I select them into the data base for playing in a media player
I really do not have a background on this.. much.. I just know how to connect to a database and insert texts.. and updating and stuffs...
The documentation for sp_trace_generateevent states that the following arguments are available:
Arguments [ @eventid=] event_id Is the ID of the event to turn on. event_id is int, with no default. The ID must be one of the event numbers from 82 through 91, which represent user-defined events as set with sp_trace_setevent. [ @userinfo= ] 'user_info' Is the optional user-defined string identifying the reason for the event. user_info is nvarchar(128), with a default of NULL. [ @userdata= ] user_data Is the optional user-specified data for the event. user_data is varbinary(8000), with a default of NULL.
@userinfo is stored in the textdata column, which after inspecting sys.trace_columns is of type text with a max size of 1073741824.
@userdata is stored in the binarydata column whose type is image with a max size of 1073741824.
I am attempting to build a stack trace of sorts that will capture detailed information about the run-time code executed when an error (contained within a try/catch) occurred.
The sql returned from dm_exec_sql_text goes well beyond 8000 and is getting truncated.
I haven't found any hot-fixes for SQL Server 2005 that addresses this issue. Has anyone else bumped into this limitation or found any explanation on the inconsistency between the stored procedure€™s arguments and the column types that the values are stored in?
I have Two Database that exist on Two seperate servers. The two database contain same schema and contains tables and columns of same name. Some tables have slight differences in terms of data types or Data type lenght.
For example if a Table on ServerA has a column named - CustomerSale with Varchar (100, Null) and a table on ServerB has a column named CustomerSale with Varchar (60, Null), how can i find if other columns have similar differences in all tables with the same name and columns in the two servers.
I am using SQL Server 2005. And the Two Servers are Linked Servers
What Script can i use to accomplish this task. Thanks
I have a indexing problem. I have a sequence that needs to has a index number. I want to use a table data type and have a working sample BUT I cannot reseed the table when needed. How do I do this.
This works only for the first ExitCoilID then I need to RESEED.
:: REGEDIT:::Â HKEY_LOCAL_MACHINESoftwareMicrosoftOffice14.0Access Connectivity EngineEnginesExcelTypeGuessRows ::TypeGuessRows value to zero (0) IMEX=1 Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:destination.xlsx;Extended Properties="Excel 12.0 XML;HDR=YES;IMEX=1";
But SQL Table Last 39 Records Dumped as NULLÂ whichever is Alphanumeric. Why? Dynamically How Can I import without doing Text to column in Excel on that column ?
I've been writing this stuff for a while, and can't seem to come to the conclusion of how I should be retrieving data and assigning this data to variables.
Since i'm using SQL Server, I'm convinced that I should be using the datareaders GetSqlDouble (or whatever) function, but this would mean i need my local variables to be one of the SQL types. The problem with that is, that there will have to be lots of conversions done by me to be able to use a SQL type in my application.
For instance, I have a class where i'm retrieving dates. In order to retrieve them correctly (Null values included), I need to retrieve them with GetSqlDateTime(), then when it comes time to display the date in a table, i must first check for nulls, then convert to a string. This seems to be very cumbersome. Would I be better off just using GetDateTime(), and the .ToString method, and ignoring Sql Types all together?
so, basically, how are you guys using your sql server data? with the supplied sql types, and doing all of the post-processing work manually? I feel like i'm having trouble conveying my issue...hopefully someone knows what i mean....i'd just like some direction to save trouble in the long run, since i feel like there's got to be a better way...
I am following the SSIS overview video- URL...I have a flat file that i want to import the contents onto a SQL database.I created a Dataflow task, source file and oledb destination.I am getting the folliwung error -"column "A" cannot convert between unicode and non-unicode string data types".in the origin file the data type is coming as string[DT_STR] and in the destination object it is coming as "Unicode string [DT_WSTR]"I used a data conversion object in between, dosent works very well
Example, suppose you have these 2 tables(NOTE: My example is totally different, but I'm simply trying to setupthe a simpler version, so excuse the bad design; not the point here)CarsSold {CarsSoldID int (primary key)MonthID intDealershipID intNumberCarsSold int}Dealership {DealershipID int, (primary key)SalesTax decimal}so you may have many delearships selling cars the same month, and youwanted a report to sum up totals of all dealerships per month.select cs.MonthID,sum(cs.NumberCarsSold) as 'TotalCarsSoldInMonth',sum(cs.NumberCarsSold) * d.SalesTax as 'TotalRevenue'from CarsSold csjoin Dealership d on d.DealershipID = cs.DealershipIDgroup by cs.MonthIDMy question is, is there a way to achieve something like this:select cs.MonthID,sum(cs.NumberCarsSold) as 'TotalCarsSoldInMonth',TotalCarsSoldInMonth * d.SalesTax as 'TotalRevenue'from CarsSold csjoin Dealership d on d.DealershipID = cs.DealershipIDgroup by cs.MonthIDNotice the only difference is the 3rd column in the select. Myparticular query is performing some crazy math and the only way I knowof how to get it to work is to copy and past the logic which isgetting out way out of hand...Thanks,Dave
I'm in the process of converting a rather huge VSAM database into a set of SQL tables. I am using the same data names from the mainframe (like XDB-NAME to RDB-NAME). I load the files using Import Export Data and it makes the tables with such column names as col001, col002, col003, etc... and always sets the data types to varchr(255). And I have to cut and paste the data names from the manframe side to the server side (and the data types to.) So, is there an easier way to do this? Or am I doomed to cut-n-paste my days away... Thanks for any help.
Using SQL Server 2000. How can I refer to one alias in another column?E.g., (this a contrived example but you get the idea)SELECT time, distance, (distance / time) AS speed, (speed / time) AS acceleration FROM dataNote how the speed alias is used in the definition of acceleration alias but this doesn't seem to work.
Hello All,Is there a way to run sql select statements with column numbers inplace of column names in SQLServer.Current SQL ==> select AddressId,Name,City from AddressIs this possible ==> select 1,2,5 from AddressThanks in Advance,-Sandeep
i dont't know how to select row with max column value group by another column. I have T-SQL
CREATE PROC GET_USER AS BEGIN SELECT T.USER_ID ,MAX(T.START_DATE) AS [Max First Start Date] , MAX(T.[Second Start Date]) AS [Max Second Start Date], T.PC_GRADE,T.FULL_NAME,T.COST_CENTER,T.TYPE_PERSON_NAME,T.TRANSACTION_NAME,T.DEPARTMENT_NAME ,T.BU_NAME,T.BRANCH_NAME,T.POSITION_NAME FROM (
I´m exporting an ms-excel file, then I use a lookup transformation to get a field from a SQL Server 2005 table. The Lookup transformation editor, after selecting the table, shows a warning that says:
at least one mapping between a column from available input columns ans a column from available lookup columns must be defined on the columns page.
So I try to make a relationship in the Lookup transformation editor's column tab where I find the Available input columns and the available lookup columns but I get the following error:
The following columns cannot be mapped: [Department, DEP_CLEGALCODE] One or more columns do not have supported data types, or their data types do not match.
The field in SLQ Server is varchar(10) and the input field is a derived column transformation; I have tried different Data Types but I always have the same error.
The DataFlow is: ExcelSource --> Derived Column --> Lookup --> Flat file destination